MySQL 新增库同步到Slave

为什么80%的码农都做不了架构师？>>>   

由于Master无法停机，并且master同时存在大量INNODB和MyISAM引擎的表，

而且总体数据量很大，因此无法通过常规的手段来重做Slave。

现在需要新增某个库的同步，刚好这个库在Slave上设置了replicate-ignore-db（历史原因），

所幸这个库里的表全部是INNODB，可以通过如下方法来实现。

• 首先在Master上将该库导出，使用mysqldump不加锁导出或者使用percona-xtrabackup实现：

slave=1.1.1.1
db='new_db'
mysqldump -S /tmp/mysql.sock --single-transaction --master-data=2 -aqC --compact --skip-add-drop-table --default-character-set=UTF8 -B $db | gzip -c | ssh $slave "gzip -cd > /tmp/$db.sql"

注意由于导出时加了master-data=2 参数，所以导出文件的第一行是被注释掉的change master 信息，

从中可以知道导出时刻的binlog位置，也就是起始位置。

• 然后ssh登入Slave，将上述导出的文件导入到Slave中并停止同步：

mysql -S /tmp/mysql.sock < /tmp/$db.sql
mysql -S /tmp/mysql.sock -e 'STOP SLAVE;SHOW SLAVE STATUS;' | egrep 'Relay_Master_Log_File|Exec_Master_Log_Pos'

并记录下当前Slave同步到的Master的binlog位置，

注意一定要停止同步后再记录，否则很有可能会漏掉部分事务，导致同步无法重启。

• 接下来最关键的一步是将导出新库到Slave同步停止这一段时间新库产生的事务从binlog中解析出来：

mysqlbinlog --base64-output=decode-rows -vv -d $db --set-charset=UTF8 -S /tmp/mysql.sock --start-position=$master_data --stop-position=$Exec_Master_Log_Pos /data/mysql/data/$Relay_Master_Log_File | gzip -c | ssh $slave "gzip -cd > /tmp/$db.last.binlog.sql"

上面语句中的三个变量master_data、Exec_Master_Log_Pos、Relay_Master_Log_File分别就是前几步记录的。

传送到Slave后再导入MySQL，但是我在操作中遇到执行导入命令时MySQL报语法错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server...

但是看binlog解析的SQL文件又没找到问题，于是只好手工过滤：

egrep -i 'insert|update|delete|replace' /tmp/$db.last.binlog.sql | sed 's/$/;/' | mysql -S /tmp/mysql.sock

实际上这样操作会有问题，对于使用参数替换的动态SQL会被过滤掉，因此最好是去掉注释行后直接导入.

• 最后再Slave上START SLAVE; 即大功告成。

转载于:https://my.oschina.net/cwalet/blog/643648