Mysql导表

根据业务的需求,整合数据,想把 test 库所有的表(表结构 + 表数据)导入到主库 dev 中,并且这些表的前缀都为 dev_

导表

备份 test 库数据

利用 mysqldump 工具,备份 test 库的数据。

1
# mysqldump -uroot -p test > test_lastest.sql

修改备份文件中的表名

利用 sed 工具,在命令行修改备份文件里的表名,加上前缀 dev_,以下几项都必须修改,否则导入表时会出错。( test 库的表是没有表前缀的。)

1
2
3
4
5
# sed -i 's@^DROP TABLE IF EXISTS `@DROP TABLE IF EXISTS `dev_@g' test_lastest.sql
# sed -i 's@^CREATE TABLE `@CREATE TABLE `dev_@g' test_lastest.sql
# sed -i 's@^LOCK TABLES `@LOCK TABLES `dev_@g' test_lastest.sql
# sed -i 's@^INSERT INTO `@INSERT INTO `dev_@g' test_lastest.sql
# sed -i 's@40000 ALTER TABLE `@40000 ALTER TABLE `dev_@g' test_lastest.sql

还要加上以下这行,在第6行(只要在第一条建表语句之前,无论哪一行可以)加入 USE master;,方便导入数据。

1
# sed -i 'N;6iUSE dev;' test_lastest.sql

查看给表加入前缀是否成功:

1
2
3
# grep 'DROP TABLE' $sql_name
# grep 'CREATE' $sql_name
# grep 'LOCK TABLES `' $sql_name

导入表

1
# mysql -uroot -p < test_lastest.sql

检查数据。

shell脚本版

把以上的操作写成脚本,迁移时方便些。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/bin/bash
sql_name="test_lastest.sql"
mysqldump -uroot -hlocalhost -pxxx test > $sql_name
sed -i 's@^DROP TABLE IF EXISTS `@DROP TABLE IF EXISTS `dev_@g' $sql_name
sed -i 's@^CREATE TABLE `@CREATE TABLE `dev_@g' $sql_name
sed -i 's@^LOCK TABLES `@LOCK TABLES `dev_@g' $sql_name
sed -i 's@^INSERT INTO `@INSERT INTO `dev_@g' $sql_name
sed -i 's@40000 ALTER TABLE `@40000 ALTER TABLE `dev_@g' $sql_name
sed -i 'N;6iUSE dev;' $sql_name
mysql -uroot -hlocalhost -pxxx < $sql_name

扩展:MySQL批量修改表前缀

本方法通过命令的方式实现批量修改表前缀。
假设有数据库 test ,原来表前缀是 test_ ,现在全部换成 dev_

生成替换命令

1
2
3
mysql> SELECT CONCAT( 'ALTER TABLE ', table_name, ' RENAME TO dev_', substring(table_name, 6), ';')
FROM information_schema.tables
WHERE table_schema='test' and table_name LIKE 'test_%';
  • substring(table_name, 6) :从 test_后面字符(第6个字符)开始替换
  • table_schema :指定数据库名

批量替换

上一步命令执行完,会生成替换表名的sql语句,复制到编辑器中,只保留sql语句(把 | 替换成空格就行了),如:

1
2
3
4
5
ALTER TABLE test_thread RENAME TO dev_thread;
ALTER TABLE test_announcement RENAME TO dev_announcement;
ALTER TABLE test_article RENAME TO dev_article;
...
...

将上面结果保存为 test2dev.sql 文件,在到MySQL服务器上导入修改:

1
# mysql -uroot -p test < test2dev.sql

扩展:改数据库名

注意:改数据库名是很危险的操作,改名之前一定记得先备份数据库数据。

方法1:重命名所有的表

可以利用 RENMAE 命令来修改,核心语句如下:

1
2
3
4
5
6
7
8
9
10
# 创建新数据库
CREATE DATABASE new_db_name;
# 逐一修改所有的表
RENAME TABLE db_name.table1 TO new_db_name.table1
RENAME TABLE db_name.table2 TO new_db_name.table3;
...
# 删除旧数据库
DROP DATABASE db_name;

写成shell脚本,则如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/bin/bash
mysqlconn="mysql -uxxx -hxxx -pxxx -S /xxx/mysql.sock"
olddb="test"
newdb="test_dev"
${mysqlconn} -e "CREATE DATABASE $newdb"
# 查出旧数据库的所有表名
params=$(${mysqlconn} -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='${olddb}' ;")
# 循环修改每个表
for name in $params
do
$mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name"
done
${mysqlconn} -e "DROP DATABASE $olddb"

方法2:利用mysqldump导出数据再导入

1
2
3
4
# mysqldump -uxxxx -pxxxx -h xxxx db_name > db_name_dump.sql
# mysql -uxxxx -pxxxx -h xxxx -e “CREATE DATABASE new_db_name”
# mysql -uxxxx -pxxxx -h xxxx new_db_name < db_name_dump.sql
# mysql -uxxxx -pxxxx -h xxxx -e “DROP DATABASE db_name”