The purpose of modifying rename database by Information_schema information
- MySQL does not have rename database command, can only modify the table in disguise to the table in the target library to achieve:
- Stitching Reanme Tables Execution statements
[Email protected] [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)
- Improvements: You want the stitching statement to be exported directly to the specified file, then import the bulk execution
[Email protected] [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[ema Il protected] [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) [email protected] [Information_schema]>set Global secure_file_priv= '/tmp '; ERROR 1238 (HY000): Variable ' Secure_file_priv ' is a read only variable[email protected] [information_schema]> shutdown; #vi/3506/my.cnf//Add the following parameters: Secure_file_priv=/tmp#/usr/local/mysql57/bin/mysqld--defaultS-FILE=/3506/MY3506.CNF &//Start again: [2] 22558[[email protected]_14:04:44/tmp] #mysql--login-path=p3506[email protected] [(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) [email protected] [(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) Q Uery 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 row S 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. XX sec) [email protected] [(none)]>use sysbench_testdata;database changed[email protected] [Sysbench_ Testdata]>showTables Empty Set (0.00 sec) [email protected] [Sysbench_testdata]>use wenyz;database changed[email protected] [ Wenyz]>show tables;+-----------------+| Tables_in_wenyz |+-----------------+| Sbtest1 | | sbtest10 | | Sbtest2 | | Sbtest3 | | Sbtest4 | | Sbtest5 | | Sbtest6 | | Sbtest7 | | Sbtest8 | | Sbtest9 | | T2 |+-----------------+11 rows in Set (0.00 sec)
MySQL Database modified name