MySQL数据库主从同步

MySQL数据库主从同步

概述

MYSQL主从同步架构是目前使用最多的数据库架构之一，主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一台服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

配置

master配置

• 数据库授权：

mysql > grant replication slave on *.* to 'slave_user'@"%" identified by "password";

• 开启binlog日志：

$ vim /etc/my.conf

[mysqld]
server_id=1
log-bin=mysql-bin
#以下配置根据需要开启
#binlog-do-db=db1 二进制日志中记录的数据库（多个用逗号，隔开）
#binlog-ignore-db=db2 二进制日志中忽略数据库 （多个用逗号，隔开）

• 查看master状态

mysql > show master status;

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| master.000001 |      850 | master       |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

slave配置

• 配置server_id

$ vim /etc/my.conf

[mysqld]
server_id=2
#以下配置根据需要开启
#replicate-do-db 设定需要复制的数据库（多个用逗号，隔开）
#replicate-ignore-db 设定需要忽略的复制数据库 （多个用用逗号，隔开）
#replicate-do-table 设定需要复制的表
#replicate-ignore-table 设定需要忽略的复制表
#replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符
#replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

• 配置主数据库信息

mysql > change master to master_host='192.168.10.66',master_port=3306,master_user='slave_user',master_password='password',master_log_file='master.000001',master_log_pos=850;

master_log_file为master状态中的File
master_log_pos为master状态中的Position
具体见上面 master配置 - 查看master状态 表格

• 开启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.10.66
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000001
          Read_Master_Log_Pos: 850
               Relay_Log_File: localhost-relay-bin.000007
                Relay_Log_Pos: 800
        Relay_Master_Log_File: master.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: master
                   ......

看到如下结果，则表示主从同步配置正常：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证

对master数据库进行操作，slave数据库数据同步更新成功，则MySQL主从配置成功。

原理

• 从数据库的IO线程读取主数据库的binlog日志，写入到从数据库的中继日志文件中，从数据库的SQL线程从本机的中继日志中读取SQL命令在本机执行，完成数据同步；
• 如果主从数据库配置前数据不一致，SQL线程就会报错；如果在主数据库创建授权用户出错，导致从数据库无法连接主数据库，IO线程就会报错；
• 如果从数据库的SQL线程执行中继日志里面的SQL命令失败的时候，SQL线程会宕掉；
• 配置MySQL主从数据库之前，确保从数据库的数据不能多于主数据库的数据。

参考资料

Mysql主从同步
mysql主从只同步部分库或表
MySQL Got fatal error 1236原因和解决方法