MYSQL---基于mysql多实例数据库创建主从复制
(一)MASTER主库
1. MASTER主库操作设置
[root@db02 data]# mysql -uroot -p -S /data/3306/mysql.sock
Enter password: #登陆主库3306
mysql> show variables like 'server_id';
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095038931-1631830933.png)
mysql> show variables like 'log_bin'; #binlog已经开启
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095107693-2041448785.png)
2.添加rep@10.0.0.%用户并授权从库访问权限
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
mysql> select user.host from mysql.user;
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095147355-400790934.png)
mysql> show grants for rep@'10.0.0.%';
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095211119-711483986.png)
3.查看当前主库状态
mysql> show master status;
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095225480-530850039.png)
4.跨服务器情况下需要锁表操作,
mysql> flush table with read lock; #锁表操作
mysql> unlock tables; #解锁操作
默认解锁时间参考一下信息
mysql> show status like "%timeout%";
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+------------------------------+-----------+
5.锁表后进行数据文件迁移(大于50G需要停库打包迁移)
[root@db02 ~]# mkdir /server/backup -P
[root@db02 ~]# mysqldump -uroot -p'123456' -S /data/3306/mysql.sock --event -A -B -x --master-data=1|gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
[root@db02 backup]# ls -l
-rw-r--r-- 1 root root 144406 Sep 3 16:03 mysql_bak.2016-09-03.sql.gz
#注意: -A 表示备份所有库 -B 表示增加use DB 和drop等(导库时会直接覆盖原有的库 -x 和 --master-data=1参数作用为在从库执行change master to语句内无需binlog文件及对应位置点)
#记录下maser的信息状态,以便在从库上操作
mysql> show master status;
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095722551-1550393792.png)
6.跨服务器在从库内导入主库mysqldump出的数据
[root@db02 ~]# cd /server/backup
[root@db02 backup]# gzip -d mysql_bak.2016-09-03.sql.gz #解压后删除源文件
[root@db02 backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock <mysql_bak.2016-09-03.sql #把数据还原到3307的实例命令
(----------------------------后跟slave从库操作------------------------------)
(二)SLAVE从库
1.从库配置复制参数,生成master.info文件
[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock << EOF
CHANGE MASTER TO
MASTER_HOST='10.0.0.52', #主库IP地址
MASTER_PORT=3306, #主库端口
MASTER_USER='rep', #主库建立用于复制的账号
MASTER_PASSWORD='123456', #主库建立账号的密码
MASTER_LOG_FILE='mysql-bin.000002', #master status二进制文件
MASTER_LOG_POS=735; #master status日志偏移量
EOF
文件在/data/3307/data目录文件下
[root@db02 data]# find /data -type f -name "master.info"
/data/3307/data/master.info
2.启动从库同步开关,测试主从复制配置
[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "start slave" #开启同步命令
[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G #查看开启状态
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095750656-551838542.png)
主从复制是否成功,最关键的三项状态参数:
[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Running|Behind_Master"
![](https://images2018.cnblogs.com/blog/1234079/201803/1234079-20180308095844652-1580282159.png)
MYSQL主从复制应用技巧实践
1. 从库停止复制故障案例
show slave status:报错且状态为:
Slave_IO_Running: Yes
Slave_SQL_Running:No
Seconds_Behind_Master: NULL
解决办法一:
mysql> slave stop;
mysql> set global sql_slave_skip_counter=n; #忽略执行N个更新
mysql> slave start;
mysql> set global sql_slave_skip_counter=n; #忽略执行N个更新
mysql> slave start;
解决办法二:
[root@db02 ~]# grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032.,1062,1007
2.让mysql从库记录binlog日志的方法
在从库my.cnf中加入以下参数重启服务生效即可
log-slave-updates #必须要有这个参数
log-bin = /data/3307/mysql-bin
expire_log_days = 7 #等于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 |xargs rm -f
3.主从复制延迟问题的原因及解决方案
问题一. 从库太多,导致复制延迟
问题二. 从库硬件比主库差,导致复制延迟
问题三. 慢SQL语句过多
问题四. 主从复制的设计问题
问题五. 主从库之间的网络延迟
问题六. 主库读写压力大,导致复制延迟