AD同步Mysql_Mysql主从同步

六、Mysql主从同步

■1.配置主从同步

原理：mysql master被使用后，其中master后台IO线程会写Binlog;slave有一个Relay Log线程同步binlog日志，同时有另一个Extractor线程会读取相应的Binlog，在Slave进行相应的同样的操作。

__________________________________________________________

A、主从服务器分别做一下操作：

■版本一致

■初始化表，并在后台启动mysql

■修改root的密码

B、修改主服务器master[root@lab-1-C6 ~]#  vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin   #[必须]启用二进制日志

server-id=201   #服务器ID必须唯一，默认是1，一般取IP最后一段

binlog-ignore-db=test       #指定不同步test库，同步其他

[root@lab-1-C6 ~]#  /etc/init.d/mysqld restart

C、修改从服务器slave[root@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指定要同步的库

[root@lab-2-C6 ~]# /etc/init.d/mysqld restart

D、在主服务器上建立账号并授权slave[root@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的状态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同步参数[root@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、检查从服务器复制功能状态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、主从同步测试#主服务器创建一个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、主从同步错误解决方法

错误一：# 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

错误二：#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

错误三：#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、主从同步小结主从复制是异步的逻辑SQL语句复制

复制时，主库有一个I/O线程，即I/O和SQL线程

实现主从复制的必要条件是主库开启记录binlog功能

作为复制的所有Mysql节点的Server-id都不能相同

binlog文件只记录对数据库有更改的SQL语句(来自主数据库内容的变更)，不记录任何查询(如select、show)语句