Mysql主从同步

213123

六、Mysql主从同步
■1.配置主从同步
原理：mysql master被使用后，其中master后台IO线程会写Binlog;slave有一个Relay Log线程同步binlog日志，同时有另一个Extractor线程会读取相应的Binlog，在Slave进行相应的同样的操作。
__________________________________________________________
A、主从服务器分别做一下操作：
■ 版本一致
■ 初始化表，并在后台启动mysql
■ 修改root的密码

B、修改主服务器master

1 2 3 4 5 6

[iyunv@lab-1-C6 ~]#  vim /etc/my.cnf [mysqld] log-bin=mysql-bin   #[必须]启用二进制日志 server-id=201   #服务器ID必须唯一，默认是1，一般取IP最后一段 binlog-ignore-db=test       #指定不同步test库，同步其他 [iyunv@lab-1-C6 ~]#  /etc/init.d/mysqld restart

C、修改从服务器slave

1 2 3 4 5 6

[iyunv@lab-2-C6 ~]# vim /etc/my.cnf [mysqld] log-bin=mysql-bin   #[不是必须]启用二进制日志 server-id=202         #服务器ID必须唯一，一般取IP最后一段 binlog-ignore-db=test       #指定不同步test库，同步其他；或者binlog-do-db=mysql指定要同步的库 [iyunv@lab-2-C6 ~]# /etc/init.d/mysqld restart

D、在主服务器上建立账号并授权slave

1 2 3

[iyunv@lab-1-C6 ~]# mysql -uroot -p111qqq... mysql> grant replication slave on *.* to 'szk'@'192.168.1.202' identified by 'szk'; #一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.1.202，加强安全

E、登录主服务器的mysql，查询master的状态

1 2 3 4 5 6 7

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

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

F、配置从服务器slave同步参数

1 2 3

[iyunv@lab-2-C6 ~]# mysql -uroot -p111qqq... mysql> change master to master_host='192.168.1.201', master_port=3306,master_user='szk',master_password='szk',master_log_file='mysql-master.000001',master_log_pos=329;              #设定同步参数      mysql> start slave;         #启动从服务器复制功能

G、检查从服务器复制功能状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Connecting to master                   Master_Host: 192.168.1.201        #主服务器地址                   Master_User: szk    #授权帐户名，尽量避免使用root                   Master_Port: 3306   #数据库端口，部分版本没有此行                 Connect_Retry: 60               Master_Log_File: mysql-master.000001           Read_Master_Log_Pos: 329               #同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos                Relay_Log_File: lab-2-C6-relay-bin.000001                 Relay_Log_Pos: 4         Relay_Master_Log_File: mysql-master.000001              Slave_IO_Running: Yes                         Slave_SQL_Running: Yes

H、主从同步测试

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

#主服务器创建一个szk库 mysql> create database szk; Query OK, 1 row affected (0.02 sec) mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | szk                | | test               | +--------------------+ 5 rows in set (0.00 sec) #从服务器查看库 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | szk                | | test               | +--------------------+ 5 rows in set (0.00 sec)

I、主从同步错误解决方法

错误一：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

# Slave_IO_Running: No，这个错误就是IO 进程没连接上  ，想办法连接上把 把与主的POS 号和文件一定要对，然后重新加载下数据 ___________________________________________________________ mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 192.168.1.201                   Master_User: szk                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-master.000001           Read_Master_Log_Pos: 329                Relay_Log_File: lab-2-C6-relay-bin.000003                 Relay_Log_Pos: 4         Relay_Master_Log_File: mysql-master.000001              Slave_IO_Running: No             Slave_SQL_Running: Yes mysql>  stop slave; mysql> show master status;   #到主库上查询POS 号和文件 mysql>  change master to master_host='192.168.1.201', master_port=3306,master_user='szk',master_password='szk',master_log_file='mysql-bin.000002',master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.201                   Master_User: szk                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000002           Read_Master_Log_Pos: 120                Relay_Log_File: lab-2-C6-relay-bin.000002                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000002              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

错误二：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

#Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' ，由于主服务器以外重启导致，找到最接近错误标记627655136的一个position是4，再回到slave机器上change master, 将postion指向这个位置. mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 192.168.1.201                   Master_User: szk                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-master.000005           Read_Master_Log_Pos: 120                Relay_Log_File: lab-2-C6-relay-bin.000001                 Relay_Log_Pos: 4         Relay_Master_Log_File: mysql-master.000005              Slave_IO_Running: No             Slave_SQL_Running: Yes #到主服务器找错误 mysqlbinlog /usr/local/mysql/data/mysql-bin.000005 > /tmp/mysqllog.txt tail /tmp/mysqllog.txt BINLOG ' nUkHVw/JAAAAdAAAAHgAAAABAAQANS42LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 4 #160408 14:03:09 server id 201  end_log_pos 120 CRC32 0xb218fd2f Start: binlog v 4, server v 5.6.29-log created 160408 14:03:09 at startup # Warning: this binlog is either in use or was not closed properly. #从服务器重新设置position mysql> stop slave; mysql>  change master to master_log_file='mysql-bin.000005',master_log_pos=4; mysql> start slave; mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.201                   Master_User: szk                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000005           Read_Master_Log_Pos: 120                Relay_Log_File: lab-2-C6-relay-bin.000002                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000005              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

错误三：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

#Slave_SQL_Running: No，mysql主从同步出现异常语句，跳过错误处理解决 __________________________________________________________ Error 'Can't drop database 'szk'; database doesn't exist' on query. Default database: 'szk'. Query: 'drop database szk' mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.201                   Master_User: szk                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           Read_Master_Log_Pos: 643                Relay_Log_File: lab-2-C6-relay-bin.000002                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: No mysql> stop slave; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave; mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.201                   Master_User: szk                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           Read_Master_Log_Pos: 643                Relay_Log_File: lab-2-C6-relay-bin.000003                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

J、主从同步小结

1 2 3 4 5

主从复制是异步的逻辑SQL语句复制 复制时，主库有一个I/O线程，即I/O和SQL线程 实现主从复制的必要条件是主库开启记录binlog功能 作为复制的所有Mysql节点的Server-id都不能相同 binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（如select、show）语句