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