MYSQL5.6 主从复制

1.主服务器配置

1) 登陆mysql数据库

# mysql -hlocalhost -uroot -proot test

2) 给从服务器设置授权用户

# mysql>grant replication slave on *.*  to user1@192.168.84.138 identified by 'user1';
或者
# mysql>grant all on *.* to user1@192.168.84.138 identified by 'user1';

3) 修改主数据库服务器的配置文件my.cnf,开启binlog,设置server-id的值

# vim /etc/my.cnf
    log-bin=mysql-bin
    server-id=1

4) 在主服务器设置锁定有效，确保没有数据库操作，以便获得一个一致性的快照：（选做）

# mysql>flush tables with read lock;

5) 目前主数据服务器已经停止了更新操作，生成主数据库的备份，有两种方式：

(1) cp全部的数据
(2) mysqldump备份数据方法

# mysqldump -uroot -proot test -l -F >/tmp/test.sql 
-F 即 flush logs
-l 即 读锁

# cat /tmp/test.sql 
# scp /tmp/test.sql 192.168.84.138:/tmp (复制到从服务器)

-如果主数据库的服务可以停止，那么直接cp数据文件应该是最快的生成快照的方法
-把数据库的一致性备份恢复到从数据库上，可以压缩data文件夹后复制到从数据库。

6) 主数据库备份完毕后，主数据库可以恢复写操作，剩下的操作只需在从服务器上去执行：

# mysql>unlock tables;

2.从服务器配置

1) 修改从数据库的server-id，注意server-id的值必须唯一，也不能和主数据库配置相同

# vim /etc/my.cnf
    server-id=2

---

命令窗口输入：

mysql>change master to master_host=’192.168.84.130’
mysql>change master to master_user=’user1’
mysql>change master to master_password=’user1’

mysql>reset slave;
mysql>start slave;
mysql>show slave status\G

2) 执行主服务器的test.sql文件

# mysql -uuser1 -puser1 test </tmp/test.sql

3) 重启mysql

# service mysqld restart

---

3.数据库无法同步情况

1）查看相应的主从复制进程方法有两种：

（1）processlist

mysql>show processlist\G
如出现state：waiting for master to send event
//连接主数据为成功，而且成功获取bin-log
state:has read all ready log;waiting for the slave i/o thread to update it
//成功执行bin-log日志，正在等待着去再次连接主数据库并更新获取bin-log日志；

（2）status;

mysql>show slave status\G
如出现：
Slave_IO_Running:Yes
//此进程负责从服务器从主服务器上读取binlog日志，并写入从服务器的中继日志。
Slave_SQL_Running:Yes
//此进程负责读取并且执行中继日志中的binlog日志，
\#注以上两个都为Yes则表明成功，只要一个是no，则表示复制进程停止，错误原因从“last_error”字段的值中看到。

2）从数据库常用命令：

（1）start slave #启动复制线程
（2）stop slave #停止复制线程
（3）show master status #查看从数据库状态
（4）show master logs #查看主数据库bin-log日志
（5）change master to #动态改变主服务器的配置
（6）show processlist #查看从数据库运行进程

3）从数据库无法同步

Show slave status 显示Slave_SQL_Running为No,Second_Behind_Master为null
原因：
a.程序可能在slave上进行了写操作
b.也可能slave机器重启后，事务回滚造成的

解决：方法一

Mysql>slave stop;
Mysql>set GLOBAL SQL_SLAVE_SKIPCOUNTER=1;
Mysql>slave start;

方法二

• Slave库，MySQL>slave stop –停掉slave服务
• Master库，MySQL>show master status;
  得到主服务器上当前的二进制日志名和偏移量
  
• 查看状态，然后到slave服务器上执行手动同步
  mysql>change master to
  master_host=”192.168.84.130”,
  master_user=”user1”,
  master_password=”user1”,
  master_port=3306,
  master_log_file=”mysql-bin.000001”,
  master_log_pos=442;
• 启动slave服务，
  Mysql>slave start；
• 通过show slave status 查看Slave_SQL_Running为Yes，Second_Behind_Master为0即为正常