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'；

 

mysql> show variables like 'log_bin';                   #binlog已经开启

 

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；

 

mysql> show grants for rep@'10.0.0.%';

 

3.查看当前主库状态

mysql> show master status;

 

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;

 

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                                       #查看开启状态

 

主从复制是否成功，最关键的三项状态参数：

[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Running|Behind_Master"

 

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;

解决办法二：

[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语句过多

问题四.  主从复制的设计问题

问题五.  主从库之间的网络延迟

问题六.  主库读写压力大，导致复制延迟