三、数据迁移

文章目录

1. 1. 1. 使用mysqldump进行数据迁移
   1. 1.1. 备份
   2. 1.2. 还原
   3. 1.3. 使用binlog增量还原
2. 2. 2. 使用load data批量导入

涉及到数据迁移，即原数据库的数据导出，以及新数据库的导入，这里主要是使用mysqldump进行数据迁移，mycat即使用mycat端口号，提前配置号分库分表规则，来初始化数据即可。对于非mysql的原数据源，可以使用文件方式通过load data批量导入形式来进行数据迁移。

1. 使用mysqldump进行数据迁移

备份

源数据库全库备份：

./mysqldump -uroot -p -h10.87.20.62 -c --skip-add-locks --flush-logs --single-transaction --master-data=2  --set-gtid-purged=OFF customer_center > /data/mysqldump/customer_center2.sql

当然也可以迁移一个库中的某几个表:

mysqldump -c -–skip-add-locks databaseName table1 table2> /root/someTables.sql

也可迁移一个表中的部分数据，加参数–where实现：

mysqldump -c -–skip-add-locks databaseName tableName --where=" id > 900 " > /root/onetableDataWithCondition.sql

参数详细说明：
迁移前确保mysql库和mycat库中的表名一样（mycat库中只需要有表名配置在schema.xml文件中即可）
-c参数不可少，-c, 全称为–complete-insert 表示使用完整的insert语句(用列名字)。 不带列名，mycat导入会报错。mycat需要根据列名来分库分表。
-–skip-add-locks表示导数据时不加锁，如果加锁涉及多分片时容易导致死锁。
–flush-logs表示备份之前刷新bin_log 后面的增量备份就从新的bin-log开始
–set-gtid-purged=OFF时，在会记录binlog日志，如果不加，不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加–set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，从库则不会被同步
-B 表示仅复制testdb并且包含create database testdb
–single-transaction innodb下保证数据一致性
–master-data 该参数有两个值1和2，默认为1，mysqldump导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

备份存储过程触发器及事件

    --events: 事件调度，默认为开启，显示关闭使用--skip-events 
    --routines: 存储过程及函数, 默认为开启，显示关闭使用--skip-routines 
    --triggers: 触发器，默认为开启，关闭使用--skip-triggers

只备份表结构或数据

--no-data：只导出建表语句。 
--no-create-info：只导出数据，不导出表结构。

更详解参数：https://www.cnblogs.com/chenmh/p/5300370.html

还原

连接mycat：
mysql -uusername -ppassword -h172.17.xxx.xxx -P8066
切换到指定的数据库： use databaseName;
导入脚本： source /root/databaseName.sql;

使用binlog增量还原

此方式对于基于mycat的增量导入没成功，仅实验mysql到mysql的增量迁移成功，且仅限迁移前后数据库名必须相同。

mysqldump后查看binlog日记状态，并记录文件名
将后续新增的binlog文件拷贝到目标数据库服务器，使用mysqlbinlog来读取binlog二进制文件，并导入到目标库

mysqlbinlog --database=testdb /var/backup/mysql-bin.000003 | mysql -uroot -pPassword.123 -v testdb

补充：mysqlbinlog使用

简单了解binlog
MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
===========================================================
DDL
   - Data Definition Language 数据库定义语言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DML
   - Data Manipulation Language 数据操纵语言
主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
===========================================================

mysqlbinlog常见的选项有以下几个：
--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样
--start-position：从二进制日志中读取指定position 事件位置作为开始。
--stop-position：从二进制日志中读取指定position 事件位置作为事件截至

===========================================================

一般来说开启binlog日志大概会有1%的性能损耗。

binlog日志有两个最重要的使用场景
1）MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到
master-slave数据一致的目的。
2）自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。

binlog日志包括两类文件
1）二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件
2）二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

常用举例：
1）binlog内容输出到终端或文本文件 
mysqlbinlog binlog_files | more
mysqlbinlog binlog_files > tmpfile

2）多个binlog log日志的还原最好将所有文件使用一个连接完成，如果使用不同连接的话有时会导致不安全 ，例如：
[root@localhost /]# mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
[root@localhost /]# mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

如果第一个日志包含创建临时表语句CREATE TEMPORARY TABLE，第二个日志要使用该临时表，第一个导入binlog日志的进程退出后临时表会被删除，执行第二个日志文件要使用临时表时会因找不到而报 “unknown table.” 

建议的方法： 
方法1： 所有二进制文件放在单个连接里
[root@localhost /]# mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

方法2： 将所有二进制文件写在一个文件里执行 
[root@localhost /]# mysqlbinlog binlog.000001 >  /tmp/statements.sql
[root@localhost /]# mysqlbinlog binlog.000002 >> /tmp/statements.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/statements.sql"

使用方法二如果二进制文件里包含GTID信息需要过滤掉
[root@localhost /]# mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
[root@localhost /]# mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/dump.sql"


3）通过事件的时间来恢复 
我们可以通过参数--start-datetime 和 --stop-datetime指定恢复binlog日志的起止时间点，时间使用DATETIME格式。 比如在时间点2005-04-20 10:00:00我们删除掉一个库，我们要恢复该时间点前的所有日志 
[root@localhost /]# mysqlbinlog --stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root -p

4）通过事件的位置来恢复
我们可以通过参数--start-position 和 --stop-position指定恢复binlog日志的起止位置点，通过位置的恢复需要我们有更加精细的操作，例如在某个时间点我们执行了错误的语句，且这个时间点前后都有大并发操作，要确定破坏性sql的时间点，我们可以先导出大致的时间段的日志到文件以缩小查找范围，再去分析和确定，确定好需要跳过的位置之后，我们就可以进行恢复了
[root@localhost /]# mysqlbinlog --stop-position=368312 /usr/local/mysql/data/binlog.123456 | mysql -u root -p
[root@localhost /]# mysqlbinlog --start-position=368315 /usr/local/mysql/data/binlog.123456 | mysql -u root -p

注：mysqlbinlog工具的输出会在每条sql语句前增加 SET TIMESTAMP语句，恢复的数据及mysql日志反映当前时间。

2. 使用load data批量导入

导出为CSV文件

select * from sys_user into outfile '/var/lib/mysql-files/sys_user.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

导入CSV文件数据

load data infile '/opt/data/dump/sys_user.csv' REPLACE INTO TABLE SYS_USER FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (USER_ID,DEPT_ID,USER_NAME,NICK_NAME,USER_TYPE,EMAIL,PHONENUMBER,SEX,AVATAR,PASSWORD,STATUS,DEL_FLAG,LOGIN_IP,LOGIN_DATE,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,REMARK);

注意：如果数据中可能包含一些特殊字符，比如分割符转义符等，建议用引号扩起来，通过OPTIONALLY ENCLOSED BY ‘”’指定。如果这样还不行，可以把字段值中的引号替换成\”。
如果指定local关键词，则表明从客户端主机读文件。如果local没指定，文件必须位于mycat所在的服务器上。
可以通过fields terminated by指定字符之间的分割符号，默认值为\t
通过lines terminated by可以指定行之间的换行符。默认为\n,这里注意有些windows上的文本文件的换行符可能为\r\n，由于是不可见字符，所以请小心检查。
character set 指定文件的编码，建议跟mysql的编码一致，否则可能乱码。其中字符集编码必须用引号扩起来，否则会解析出错。
还可以通过replace | ignore指定遇到重复记录是替换还是忽略。
目前列名必须指定，且必须包括分片字段，否则没办法确定路由。
标准load data语句： LOAD DATA语句,同样被记录到binlog,不过是内部的机制。

参考：https://blog.csdn.net/kimva/article/details/78875154