MySQL 主从复制 | blank的编程之路

MySQL的主从复制实现原理:

主库会将所有的更新记录保存到 Binarylog 文件。
每当有从库连接到主库的时候，主库都会创建一个 log dump 线程发送 Binarylog 文件到从库。
当从库复制开始的时候，从库就会创建两个线程进行处理，一个 I/O 线程，一个 SQL 线程。
I/O 线程去请求主库的 Binarylog文件，并将得到的 Binarylog 文件写到 Relaylog 文件中。
SQL 线程会读取 Relaylog 文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。

工具

OS CentOS 7
MySQL 版本 5.7.25
Master 服务器：192.168.10.51
Slave 服务器：192.168.10.52

准备工作

主从安装MySQL参考
关闭主从服务器防火墙
修改主服务器配置文件
master 服务器(192.168.10.51)

vim /etc/my.cnf

[mysqld]
#master 服务器
#开启bin-log，并指定文件保存目录和文件名前缀。
#log-bin=/home/mysql/logs/binlog/bin-log 
# 如果只指定文件前缀而不指定路径 则默认保存在/var/lib/mysql
log_bin=mysql-bin
#服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。
server_id=51
 #每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。
max_binlog_size = 500M
#日志缓存大小
binlog_cache_size = 128K
#需要同步的数据库名字，如果是多个，就以此格式在写一行即可。不配置表示所有库
#binlog-do-db = dbName
#不需要同步的数据库名字，如果是多个，就以此格式在写一行
binlog-ignore-db = mysql 
#当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新
log-slave-updates
#设置bin-log日志文件格式为：MIXED，可以防止主键重复。
binlog_format="MIXED"

修改从服务器配置文件
slave 服务器(192.168.10.52)

vim /etc/my.cnf

[mysqld]
#slave 服务器地址
## 192.168.10.52(slave)
#这个选项控制重试间隔，默认为60秒。
master-connect-retry=30
#这个是在同步过程中忽略掉的错误，这些错误不会影响数据的完整性，有事经常出现的错误，一般设置忽略。其中1062为主键重复错误。
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

重启主从MySQL

systemctl restart mysqld

master 上创建一个测试数据库(test)如已有数据库这步可跳过：

mysql>
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id主键',
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

主从复制
在master服务器(192.168.10.51)上的操作：

创建copy用户，指定该用户只能在从库 192.168.10.52 上使用 123465 密码登录

mysql>  grant replication slave on *.* to 'copy'@'192.168.10.52' identified by '123465';
刷新权限
mysql> flush privileges;
查看用户
mysql> select user,host from mysql.user;
## 查看 master 状态
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

PS 这里的 mysql-bin.000002(master_log_file)和154 (master_log_pos) 配置slave时需要用到

使 slave 与 master 建立连接，从而同步：
```mysql
mysql> change master to
    -> master_host='192.168.10.51',
    -> master_user='copy',
    -> master_password='123465',
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;

检查同步状态

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.51
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 154
              Relay_Log_Space: 531
              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: 51
                  Master_UUID: 1d419c66-4566-11e9-838b-000c295fc69f
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表明同步成功

到此MySQL 的主从同步已经OK