通过information_schema信息修改rename database的目的
- mysql 没有rename database 命令,只能变相修改表到目标库里的表来实现:
- 拼接reanme tables的执行语句
root@localhost [information_schema]>select concat('rename table sysbench_testdata.',table_name,' to wenyz.',table_name,';') from information_schema.tables where table_sschema='sysbench_testdata';
+----------------------------------------------------------------------------------+
| concat('rename table sysbench_testdata.',table_name,' to wenyz.',table_name,';') |
+----------------------------------------------------------------------------------+
| rename table sysbench_testdata.sbtest10 to wenyz.sbtest10; |
| rename table sysbench_testdata.sbtest2 to wenyz.sbtest2; |
| rename table sysbench_testdata.sbtest3 to wenyz.sbtest3; |
| rename table sysbench_testdata.sbtest4 to wenyz.sbtest4; |
| rename table sysbench_testdata.sbtest5 to wenyz.sbtest5; |
| rename table sysbench_testdata.sbtest6 to wenyz.sbtest6; |
| rename table sysbench_testdata.sbtest7 to wenyz.sbtest7; |
| rename table sysbench_testdata.sbtest8 to wenyz.sbtest8; |
| rename table sysbench_testdata.sbtest9 to wenyz.sbtest9; |
+----------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
- 改进:希望拼接语句直接导出到指定文件,再导入批量执行
root@localhost [information_schema]>select concat('rename table sysbench_testdata.',table_name,' to wenyz.',table_name,';') from information_schema.tables where table_schema='sysbench_testdata' into outfile '/tmp/1.sql' ;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
root@localhost [information_schema]>show variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | NULL |
+--------------------------+-------+
3 rows in set (0.01 sec)
root@localhost [information_schema]>set global secure_file_priv='/tmp';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
root@localhost [information_schema]>shutdown;
#vi /3506/my.cnf //增加以下参数:
secure_file_priv=/tmp
#/usr/local/mysql57/bin/mysqld --defaults-file=/3506/my3506.cnf & //再次启动:
[2] 22558
[root@db210_14:04:44 /tmp]
#mysql --login-path=p3506
root@localhost [(none)]>select concat('rename table sysbench_testdata.',table_name,' to wenyz.',table_name,';') from information_schema.tables where table_schema='sysbench_testdata' into outfile '/tmp/1.sql' ;
Query OK, 9 rows affected (0.00 sec)
root@localhost [(none)]>source /tmp/1.sql;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: *** NONE ***
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]>use sysbench_testdata;
Database changed
root@localhost [sysbench_testdata]>show tables;
Empty set (0.00 sec)
root@localhost [sysbench_testdata]>use wenyz;
Database changed
root@localhost [wenyz]>show tables;
+-----------------+
| Tables_in_wenyz |
+-----------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
| t2 |
+-----------------+
11 rows in set (0.00 sec)