MySQL-线上数据迁移实战记录

1. 迁移背景和限制条件

　　随着功能的迭代或者数据表中数据量的增加，将现有数据进行迁移已是工作中经常遇到的事情。通常我们在平时迁移数据数据的时候，只需要用mysqldump、mysqlimport指令就能完成迁移功能，但在实际工作中，作为开发者的我们往往没有这么大的权限（例如写权限）来操作线上数据，只能想办法根据已有的权限（通常是从库的读权限）生成插入数据的sql文件，再将文件交给dba进行执行，从而完成迁移工作。

2. 数据迁移记录

• 在只有读权限的情况下，可以通过mysqldump命令导出数据库中的数据：

mysqldump -h127.0.0.1 -uadmin -proot database table > /home/work/data.sql;

• 但是mysql下需要lock tables权限才能使用mysqldump，可以使用以下方式解决：

mysqldump -h127.0.0.1 -uadmin -proot database table --skip-lock-tables > /home/work/data.sql

 

• 如果需要对导出的数据添加筛选条件：

mysqldump -h127.0.0.1 -uadmin -proot database table --where="id<100" --skip-lock-tables > /home/work/data.sql

• 如果要对字段进行筛选，从而达到迁移部分字段的目的，可以用以下方式：

mysql -h127.0.0.1 -uadmin -proot database -e "select id, name from table into outfile '/home/work/data.sql'"

• but，往往对于线上数据库我们是没有写权限的，所以上面那条语句往往不能执行，我们可以先将数据生成到本地：

mysql -h127.0.0.1 -uadmin -proot -ne "use table; select id, name from table;" > /home/work/data.txt

• 然后在本地创建相同的库和对应字段的表，并将导出的数据文件导入到本地的数据库中：

load data infile '/home/work/data.txt' into table table_name

• 如果报以下错：error 13 (hy000): can't get stat of '/home/work/data.txt' (errcode: 13)，则：

load data local infile '/home/work/data.txt' into table table_name

• 最后，再用mysqldump生成可执行的sql文件（因为是本地，所以可以用root账户，不需要加 skip-lock-tables 参数）：

mysqldump -h127.0.0.1 -uroot -proot database table > /home/work/data.sql 

• 加上 no-create-info 则不会生成创建表的语句，加上 default-character-set 可以指定字符集：

mysqldump -h127.0.0.1 -uroot -proot --no-create-info --default-character-set=utf8 database table > /home/work/data.sql

• 接着我们就可以拿着生成好的sql文件交给dba执行迁移工作啦～

 

tips：在创建数据库的时候，如果不指定字符集，则默认是latin1，此时用mysqldump进行导出时，需要指定字符集为latin1才不会乱码，可以使用命令：show variables like 'character_set_%'; 来查看数据库的编码方式（关注 character_set_database 的值）