Mysql 5.7.21 设置主从库同步

主从复制条件：

Mysql 单机多实例安装参考Mysql 5.7.21 设置主从库同步
下面的操作是多实例主从复制，3306为主库，3307为从库。

1. 主库要开启log-bin，主库和从库的server-id要不一样，修改/etc/my.cnf配置

[mysqld_multi] 
mysqld    = /usr/local/mysql/bin/mysqld  
mysqladmin = /usr/local/mysql/bin/mysqladmin
log        = /tmp/mysql_multi.log 
user = mysql
pass = 123456
[mysqld1] 
   datadir = /usr/local/data
   socket = /tmp/mysql.sock1 
   port = 3306 
   user = mysql 
   performance_schema = off 
   innodb_buffer_pool_size = 32M 
   bind_address = 0.0.0.0 
   skip-name-resolve = 0 
   server-id=1
   log-bin=/usr/local/data/mysql-bin
[mysqld2] 
   datadir = /usr/local/data3307
   socket = /tmp/mysql.sock2 
   port = 3307 
   user = mysql 
   performance_schema = off 
   innodb_buffer_pool_size = 32M 
   bind_address = 0.0.0.0 
   skip-name-resolve = 0 
   server-id=2 

2. 在主库上面创建同步用户：

mysql>grant replication slave on *.* to 'rep'@'%' identified by '123456';    #后面的密码要设置复杂些。
mysql> flush privileges;

3. 导出主库数据文件：
   3.1 常规方法，进入主库：

mysql> flush table with read lock;     #先锁表，锁表后不能退出mysql窗口，否则失效  
mysql> show master status;   #记录bin-log的位置信息  
+------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| mysql-bin.000004 |      560 |              |                  |  
+------------------+----------+--------------+------------------+  
mysql> exit

mysqldump -uroot -p -S /data/3306/mysql.sock --events -A -B|gzip >/tmp/bak_$(date +%F).sql.gz  
mysql> unlock tables;   #解锁数据库  

3.2 快捷方法：

mysqldump -uroot -p -S /data/3306/mysql.sock --events -A -B -F --master-data=2 -x|gzip >/tmp/bak_f_$(date +%F).sql.gz     
 -x 自动锁表  
 -F --master-data=2    以备注形式记录bin-log的位置。（可以打开文件看头部备注信息）  

4. 还原备份内容到从库：

gzip -d bak_f_2018-03-26.sql.gz
mysql -uroot -p -S /tmp/mysql.sock2 < bak_f_2018-03-26.sql

5. 连接从库，并执行：mysql -uroot -p123456 -S /tmp/mysql.sock2

CHANGE MASTER TO    
MASTER_HOST='172.16.1.214',   
MASTER_PORT=3306,  
MASTER_USER='rep',   
MASTER_PASSWORD='Root1234',   
MASTER_LOG_FILE='mysql-bin.000004',  
MASTER_LOG_POS=560;  

mysql> start slave;        #开启同步  
mysql> show slave status \G ;    #查看同步状态信息  

6. 监控mysql主从复制的状态指标：

mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status \G ;" | egrep -i "_Running|_Behind"

        Slave_IO_Running: Yes                             #同步进程是否启动
        Slave_SQL_Running: Yes                          #sql进程是否启动
        Seconds_Behind_Master: 0                       #从库同步完成需要的时间