MySQL5.5数据库主从（Master/Slave）同步配置详解

一、概述

Mysql Replication（复制） 即 主从同步（Master/Slave），主要用于数据库的备份，负载均衡，读写分离等。

1、数据复制技术有以下一些特点：
(1)    数据分布
(2)    负载平衡(load balancing)，读写分离，主写从读
(3)    备份
(4)    高可用性(high availability)和容错

2、复制如何工作
从高层来看，复制分成三步：
(1)    master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2)    slave将master的binary log events拷贝到它的中继日志(relay log)；
(3)    slave重做中继日志中的事件，将改变反映它自己的数据。

3、本文数据库版本：

Centos7 、Mariadb5.5

Mariadb5.5 等同于 Mysql5.5，不同的数据库版本配置不同，应参考官方文档，

https://dev.mysql.com/doc/refman/5.5/en/replication.html

二、数据备份还原

在做主从同步之前首先需要对主库进行数据备份，恢复到所有的从数据库，数据库备份有冷备和热备，冷备即拷贝所有的数据文件及日志文件到从服务器，

这里使用mysqldump工具做在线热备

步骤:（这里需要备份的数据库为 db_test）

1、Master锁定库，使只能读取不能写入

mysql > flush tables with read lock;

2、Master导出备份

~$ mysqldump --master-data -uroot -p db_test>db_test.sql

说明：--master-data参数在生成的dump 文件中产生一条 CHANGE MASTER TO 命令，查看可知master当前使用的binlog文件名

3、Slave导入备份

~$ mysql -uroot -p db_test<db_test.sql

4、最后配置好同步以后，Master解除写锁定

mysql > unlock tables;

三、配置参考

1、网络配置

1主2从，主从数据库处于同一网段内，可互相访问。

主数据库master：192.168.56.101

从数据库slave1：192.168.56.102

从数据库slave2：192.168.56.103

2、master配置

my.cnf文件mysqld段：

[mysqld]
server-id=1
log-bin=mysql-bin

说明：必需配置，server-id指定服务器唯一id，不可重复，log-bin开启binlog日志

设置复制账号：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%' IDENTIFIED BY '123456';

说明：添加一个 test 账号在任何机器上使用 123456 这个密码对任何数据库行使 replication slave 权限

完成重启mysql服务：

~$ systemctl restart mariadb

查看Master状态：

mysql > show master status;

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000022 |     1184 |              |                  |
+------------------+----------+--------------+------------------+

3、Slave配置

my.cnf文件配置:

[mysqld]
server-id=2
log-bin=mysql-bin

连接配置：

#关闭slave服务

mysql> stop slave; 

#配置连接信息
mysql> change master to master_host='192.168.56.101',master_user='test',master_password='123456',master_log_file='mysql-bin.000022';

#开启slave服务
mysql> start slave; 

#查看slave状态
mysql> show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000022
          Read_Master_Log_Pos: 1184
               Relay_Log_File: mariadb-relay-bin.000053
                Relay_Log_Pos: 1468
        Relay_Master_Log_File: mysql-bin.000022
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1184
              Relay_Log_Space: 2048
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

核对host、user、master_log_file是否正确，

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

这两项yes，说明配置成功。

最后别忘了，Master解除写锁定：

mysql > unlock tables;

注意：如果Master的mysql服务重启会生成新的bin log日志，这时候，Slave也需要重启一下服务或者stop slave - start slave，

如果slave服务不重启，则可以修改 mysql - replication -slave 自动生成的配置文件：/var/lib/mysql/master.info

18
mysql-bin.000022
1184
192.168.56.101
test
123456
3306
60

查看第二行 mysq-bin文件名是否跟Master上对应，如果不对应可直接修改；

四、常见错误

1、master发生故障，经修复后启动后，slave无法与master同步

报错：Got fatal error 1236 from master when reading data from binary log

原因：master重启后，mysql的binlog会重新生成，相应的记录位置会改变

解决方法：

-master：

mysql > flush logs;

mysql > show master status;

记录下File和Position值

-slave：

mysql > stop slave;

mysql > CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000049',MASTER_LOG_POS=1359;

mysql > start slave;

mysql > show slave status\G;

2、slave发生故障，设置正确，但是无法初始化

报错：ERROR 1201 (HY000): Could not initialize master

解决方法：

-master：

mysql > flush logs;

mysql > show master status;

记录下File和Position值

-slave：

mysql > reset slave;

mysql > change master to master_host='192.168.10.100',master_user='test',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=106;

mysql > start slave;

mysql > show slave status\G;

完

本次系统环境：CentOS7.4 + MariaDB5.5

Mysql5.5官方文档：https://dev.mysql.com/doc/refman/5.5/en/replication.html