（转）[mysqldump]不停止MySQL服务的情况下增加从库

转自：http://blog.csdn.net/orangleliu/article/details/60587385

MySQL主从是数据库高可用的最简单做法之一，结合每天的定时冷备份，基本上能满足小公司的数据备份要求。添加从库是经常要做的的一个操作，下面是一种常见的不停止主库实现从库的方法（很多时候我们不能直接开启读锁，那样会对业务影响比较大)，主要的工具是 mysqldump.

逻辑原理

很多情况下是给一个有数据，或者说运行了一段时间的数据库来添加从库，从逻辑上来说需要把主库在某个时间的 日志位置记录下来, 然后把这个时间之前的数据导入到从库中, 从库从这个日志位置开始同步主库的数据。

主库中查看日志的位置

mysql> show master status \G*************************** 1. row ***************************             File: mysql-bin.000002         Position: 258814     Binlog_Do_DB: Binlog_Ignore_DB:Executed_Gtid_Set: a4e5dad2-7e20-11e6-8951-1df67d8f54f5:1-8471 row in set (0.00 sec)
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8

mysqldump 是mysql中自带的一个备份工具，它可以在导出数据文件的同时记录下数据库当时的 position，数据dump的过程中数据库锁定，导出的数据正好是这个 postiion之前的数据。 只要把导出的数据导入到从库，然后从导出的文件中找到主库的 position 并开始同步就行了。

操作案例

环境 Centos6.x ，MySQL 5.6.x

检查配置

主库配置 一般是 /etc/my.cnf 或者 /etc/mysql/my.cnf

server-id=1binlog-format=mixedlog-bin=mysql-bindatadir=/var/lib/mysqlinnodb_flush_log_at_trx_commit=1sync_binlog=1
1
2
3
4
5
6
1
2
3
4
5
6

注意从库配置中如果有 server-id 不要和主库相同

创建从库用户

主库中建立一个用户给从库复制用

CREATE USER replicant@<<slave-server-ip>>;GRANT REPLICATION SLAVE ON *.* TO replicant@<<slave-server-ip>> IDENTIFIED BY '<<choose-a-good-password>>';FLUSH PRIVILEGES;
1
2
3
1
2
3

主库数据导出

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > ~/dump.sql
1
1

能导出 position的选项就是 --master-data， 下面是这个选项的解释

Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave. If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

-A 导出所有的 databases， 导出某些 databases 可以用 -B, 具体看 mysqldump 的 help文档就好了。

查看下主库的 position, 通过查找 MASTER_LOG_FILE and MASTER_LOG_POS

head dump.sql -n80 | grep "MASTER_LOG_POS"
1
1

压缩数据

gzip ~/dump.sql
1
1

数据传到从库服务器

方式多种多样了，这里用scp，用ftp啊都行

scp ~/dump.sql.gz mysql-user@<<slave-server-ip>>:~/
1
1

从库配置

server-id= 101binlog-format=mixedlog_bin=mysql-binrelay-log=mysql-relay-binlog-slave-updates=1read-only=1
1
2
3
4
5
6
1
2
3
4
5
6

注意read-only 哈，配置之后就是个只读库了。

过滤库的选项, 这两个参数使用需要注意 为什么mysql的binlog-do-db选项是危险的

replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可replicate-ignore-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
1
2
1
2

数据导入从库

gunzip ~/dump.sql.gzmysql -u root -p < ~/dump.sql
1
2
1
2

建立主从关系

mysql> CHANGE MASTER TO MASTER_HOST='<<master-server-ip>>',MASTER_USER='replicant',MASTER_PASSWORD='<<slave-server-password>>', MASTER_LOG_FILE='<<value from above>>', MASTER_LOG_POS=<<value from above>>;START SLAVE;
1
2
1
2

检查主从情况

SHOW SLAVE STATUS \G
1
1

如果没什么问题的话，Last_Error 应该是空的，Slave_IO_State 应该会显示 “Waiting for master to send event”。

还会看到

Slave_IO_Running: YesSlave_SQL_Running: Yes
1
2
1
2

有时候可能会出错，下面是如何重新建立主从关系的方法

STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;
1
1

Setting up MySQL replication using mysqldump without the downtime