通过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 statementroot@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 variableroot@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=p3506root@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 awayNo connection. Trying to reconnect...Connection id:    3Current 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 changedroot@localhost [sysbench_testdata]>show tables;Empty set (0.00 sec)root@localhost [sysbench_testdata]>use wenyz;Database changedroot@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)