MySQL数据库重命名方法汇总

技术背景

在MySQL中，直接使用RENAME {DATABASE | SCHEMA} db_name TO new_db_name; 来重命名数据库存在问题，该语句仅在少数版本中存在，且会产生不良影响，总体而言不是一个好的选择。尤其是对于使用InnoDB存储引擎的数据库，其存储方式与MyISAM不同，需要更合适的重命名方法。

实现步骤

方法一：逐个重命名表

1. 创建新的空数据库：使用CREATE DATABASE new_db; 语句创建新的数据库。
2. 重命名每个表：使用RENAME TABLE old_db.table TO new_db.table; 语句将旧数据库中的表逐个重命名到新数据库中。
3. 调整权限：重命名完成后，需要手动调整新数据库的权限。

示例脚本：

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

方法二：使用mysqldump 和mysqladmin

1. 创建新数据库：使用mysqladmin -u username -p create newdatabase 命令创建新数据库。
2. 导出旧数据库：使用mysqldump -u username -p -v olddatabase > olddbdump.sql 命令将旧数据库导出到一个SQL文件中。
3. 导入到新数据库：使用mysql -u username -p newdatabase < olddbdump.sql 命令将导出的SQL文件导入到新数据库中。
4. 删除旧数据库：确认新数据库导入成功后，使用mysqladmin -u username -p drop olddatabase 命令删除旧数据库。

示例脚本：

mysqladmin -u username -p create newdatabase
mysqldump -u username -v olddatabase -p | mysql -u username -p -D newdatabase

方法三：使用phpMyAdmin

1. 选择数据库：在phpMyAdmin中选择要重命名的数据库。
2. 进入操作页面：点击“Operations”标签，进入操作页面。
3. 重命名数据库：在“Rename database to”字段中输入新的数据库名称，然后点击“Go”按钮。
4. 确认操作：根据提示确认删除旧数据库和重新加载表数据。

方法四：生成SQL脚本

1. 创建新数据库：使用CREATE DATABASE new_db; 语句创建新的数据库。
2. 生成重命名脚本：使用以下SQL语句生成重命名脚本：

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO new_db.', table_name, '; ')
FROM information_schema.TABLES 
WHERE table_schema='old_db';

3. 执行脚本：将生成的脚本复制到MySQL客户端中执行。

方法五：使用Percona脚本

1. 下载脚本：从https://gist.github.com/ryantm/76944318b0473ff25993ef2a7186213d 下载脚本。
2. 修改脚本权限：使用chmod +x rename_db 命令将脚本设置为可执行。
3. 执行脚本：使用./rename_db localhost old_db new_db 命令执行脚本。

核心代码

生成重命名脚本的SQL代码

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO new_db.', table_name, '; ')
FROM information_schema.TABLES 
WHERE table_schema='old_db';

重命名数据库的Shell脚本

#!/bin/bash
set -e # terminate execution on command failure

mysqlconn="mysql -u root -proot"
olddb=$1
newdb=$2
$mysqlconn -e "CREATE DATABASE $newdb"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
                           WHERE table_schema='$olddb'")
for name in $params; do
      $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
done;
$mysqlconn -e "DROP DATABASE $olddb"

最佳实践

• 备份数据：在进行数据库重命名操作之前，务必备份重要的数据，以防止数据丢失。
• 测试环境验证：在生产环境中进行重命名操作之前，先在测试环境中进行验证，确保操作的正确性。
• 注意权限问题：重命名数据库后，需要确保用户对新数据库具有正确的权限。
• 处理特殊对象：如果数据库中包含视图、触发器、存储过程等特殊对象，需要特别处理，确保这些对象在重命名后仍然可用。

常见问题

• 触发器问题：如果表中存在触发器，使用RENAME TABLE 方法会导致Trigger in wrong schema 错误。解决方法是先使用mysqldump -d -t -R -E 命令将触发器、事件和存储过程导出到一个文件中，然后删除触发器，重命名表后再导入这些对象。
• 视图问题：RENAME TABLE 方法无法直接重命名视图，需要使用DROP VIEW 和CREATE VIEW 语句手动处理。
• 性能问题：对于非常大的数据库，使用mysqldump 和mysql 方法可能会非常耗时，建议使用逐个重命名表的方法。
• 权限问题：如果用户没有足够的权限，重命名操作可能会失败。确保用户具有创建数据库、重命名表和删除数据库的权限。