MySQL如何修改库名

  在MySQL数据库中，可以使用rename table对表进行重命名，但是无法使用rename database命令(老版本支持（5.1.7到5.1.23），但是在新版本中去除，因为存在安全风险)；但是我们依旧可以用一些其他的方法实现这个逻辑。

  具体分为以下三种方法：

mysqldump

#创建目标库
root@mysql8 13:00:  [test]> create database test_bak;
Query OK, 1 row affected (0.01 sec)
#确认源端数据
root@mysql8 13:00:  [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.10 sec)

#mysqldump导出数据
[root@zijie data]# /usr/local/mysql/mysql8/bin/mysqldump --defaults-file=/etc/my.cnf -uroot -p970125 --socket=/data/mysql8/data/mysql8.sock --default-character-set=utf8mb4 --single-transaction --master-data=2 --skip-add-drop-table -e --skip-tz-utc --flush-logs --set-gtid-purged=OFF --databases test --tables t1 t2>test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#导入数据到新库

[root@zijie data]# mysql -uroot -p970125 test_bak<test.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
#确认目标端数据
root@mysql8 13:16:  [test_bak]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.09 sec)

#确认无误后删除源库

  因为使用mysqldump逻辑备份，所以在数据量很大的时候会比较慢。

传输表空间

1.目标端创建同样的表结构
root@mysql8 13:21:  [test_bak]> create table test_bak.t1 like test.t1;
Query OK, 0 rows affected (0.06 sec)
2.目标端卸载表空间
alter table test_bak.t1 discard tablespace;    
3.源端对表加一个读锁(会话不要关)
flush table test.t1 for export;
4.拷贝.cfg和.ibd文件到目标端数据文件位置
[root@zijie test]# ll
总用量 40M
-rw-r----- 1 mysql mysql 1.3K 6月   3 13:24 t1.cfg
-rw-r----- 1 mysql mysql  17M 6月   3 13:21 t1.ibd
-rw-r----- 1 mysql mysql  22M 6月   3 13:21 t2.ibd
[root@zijie test]# cp t1.* ../test_bak/
[root@zijie test]# cd ../test
[root@zijie test]# ll
总用量 40M
-rw-r----- 1 mysql mysql 1.3K 6月   3 13:24 t1.cfg
-rw-r----- 1 mysql mysql  17M 6月   3 13:21 t1.ibd
-rw-r----- 1 mysql mysql  22M 6月   3 13:21 t2.ibd
5.源端释放锁
unlock tables;
6.目标端文件赋予权限
chown -R mysql:mysql *
chmod -R 755 *
7.目标端导入表
root@mysql8 13:28:  [test_bak]> alter table test_bak.t1 import tablespace;
Query OK, 0 rows affected (1.39 sec)
8.确认数据
root@mysql8 13:28:  [test_bak]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.12 sec)

rename脚本方式

  使用脚本方式，rename table更改表名的命令，但是如果新表名后面加数据库，将会将原来的数据库的表移动到新的数据库

#!/bin/bash
time=20200602
list_database=$(mysql -uroot -p970125 -Nse "select distinct(table_schema) from information_schema.TABLES where TABLE_SCHEMA not in ('sys','information_schema','mysql','performance_schema')" 2>/dev/null)
for database in ${list_database}
do
    mysql -uroot -p970125 -e "create database if not exists ${list_database}${time}"
done
list_table=$(mysql -uroot -p970125 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA not in ('sys','information_schema','mysql','performance_schema')" 2>/dev/null)
for database in ${list_database}
do
    for table in ${list_table}
    do
        mysql -uroot -p970125 -e "rename table ${database}.${table} to ${database}${time}.${table}"
    done
done