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 database; 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 的值）