MySQL主从同步配置及存量数据同步方案

实践准备：

准备两台服务器：

主：192.168.8.10
备：192.168.8.11

MySQL的版本最好保持一致。

步骤一： 授权给从服务器

创建一个专门的同步账号：

GRANT REPLICATION SLAVE ON . to 'repl'@'192.168.8.11' identified by 'passwd';

步骤二： 查看主服务器的日志名和偏移量

用于后面设置从库同步的起始点：

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000028 |    26642 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

步骤三： 修改从服务器的server-id

server-id默认都是1，如果要设置主从必须要保存两台的server-id不同，因此只需改从服务器就行。保存重启：

vim /etc/my.cnfserver-id=2

service mysql restart

步骤四： 设置同步命令

在从服务器上设置同步（MASTER_LOG_FILE和MASTER_LOG_POS是步骤二查询的结果）：

CHANGE MASTER TO MASTER_HOST='172.18.135.185',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000028',MASTER_LOG_POS=5408;

步骤五：启动slave进程

在从服务器上执行：

mysql> start slave;Query OK, 0 rows affected, 1 warning (0.00 sec)

步骤六：查看slave同步状态

在从服务器上执行：

mysql> show slave status\GQuery OK, 0 rows affected, 1 warning (0.00 sec)

如果下面两项值为YES，则表示配置正确：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

步骤七：验证

在主库上执行一个update或insert操作，看从库是否同步过来了。

一个重要问题：如何同步存量的数据

上面的操作过程中忽略了一个很重要的问题：刚开始同步时，历史数据如何同步到从库，有两种方案：
方法一： 让从库从第一个binlog文件开始重放： 只需将上述步骤四中的命令改为：

CHANGE MASTER TO MASTER_HOST='172.18.135.185',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;

这种方法的好处是不用对主库进行停用或重启操作，不影响主库，弊端是如果主库的数据量相当庞大，那么从库需要同步完所需的时间会特别长，多则长达几个小时，甚至几天，比较影响从库能提供对外服务的时间。

方法二： 把主库的数据全量mysqldump出来，然后load file进从库。这种方法有个致命的弊端是得先把主库的更新操作先停止，会直接影响线上服务。
具体操作步骤如下：

主库操作：1. 停止主库的数据更新操作：mysql> flush tables with read lock;2.新开窗口，选择需备份库：mysqldump -h 127.0.0.1 -uroot -proot --skip-comments --databases --compact -C -q -f db1 db2  db3 >> back.sql3. 记录日志和偏移量mysql> show master status 4. 主库解锁mysql> unlock tables;从库操作：1. 导入数据 $ mysql -uroot -proot <back.sql2.设置刚记录的主库日志和偏移量信息：CHANGE MASTER TO MASTER_HOST='172.18.135.185',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000028',MASTER_LOG_POS=4032; 3.开启同步mysql> start slave;

一些高级配置

• 只同步某一个库，或者是某一张表，或者是不同步某一个库，不同步某一张表，改怎么设置呢？ 以上说的都能实现，参考MySQL官网：http://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html
  举例忽略不同步某一张表(db1.mytable)：

mysql> stop slave;mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db1.mytable);mysql> start slave;

• 查看主从延时多大
  通过show slave status可以看到： Seconds_Behind_Master 主从同步的延时时长，用从库的当前时间戳减去binlog的时间戳。

• 延时同步
  设置延时同步，单位秒（延迟10s）

CHANGE MASTER TO MASTER_DELAY = 10;

参考：

• mysql主从同步配置(http://www.cnblogs.com/zhoujie/p/mysql1.html)
• MySQL官网(http://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html)