基于日志点的MySQL 主从复制

最新推荐文章于 2024-02-01 15:03:00 发布

@LuckY BoY

最新推荐文章于 2024-02-01 15:03:00 发布

阅读量311

点赞数

分类专栏： SQL 文章标签：数据库

本文链接：https://blog.csdn.net/m0_37264220/article/details/105617292

版权

SQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

http://www.cnblogs.com/chenmh

环境

主：mysql（8.0.17 ），linux:CentOS Linux release 8.0.1905 (Core) ,ip:192.168.143.154

从：mysql（8.0.17 ），linux:CentOS Linux release 8.0.1905 (Core) ,ip:192.168.143.155

原理和概念

主从复制原理
1)主库在事务提交时会把变更作为事件记录（Events）到二进制文件（Binlog）当中

2)主库将二进制文件中的事件推送到从库的中继日志文件中（Relay-bin）,从库根据中继日志中事件做变更操作。

线程
Binlog Dump线程：该线程运行在主库上，当主从都配置好后，从库运行START SLAVE启动复制后，会在主库上生成一个Binlog Dump线程，该线程的主要作用就是读取主库Binlog事件发送到从库（从库的I/O线程）。

I/O线程：该线程运行在从库上，I/O线程的作用是向主数据库要数据并且将主库发送过来的变更事件写入到从库的中继日志中。

SQL线程：该线程运行在从库上，该线程的主要作用是读取中继日志中的变更事件并更新从库。
LOVE

步骤

主库
在这里主库是运行的，主库的配置文件也是已经配置好了的。

1.配置my.cnf

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
server-id=6
log-bin=/var/lib/mysql/mysql-bin
max_binlog_size=100M
sync_binlog=0
binlog-format=MIXED

server-id必须是唯一的，默认设置当前IP主机

log-bin是开启binlog且配置路径，默认是不开启的

max_binlog_size设置binlog文件的最大值，这里设置最大为100M，当达到这个值会自动生成一个新的binlog文件，当然生成环境会设置的比这个大一点。

sync_binlog:配置是否每次事务提交都需要刷新binlog到磁盘，默认0是不每次刷新，有文件系统自己控制，如果设置为1默认每次事务提交都会刷新binlog到磁盘，这样的好处是当系统突然down掉了系统损伤的会少一点，因为binlog也有缓存，默认事务提交是先写缓存这样当系统突然down掉了就有可能会丢失缓存中的记录，但是如果每次事务提交都写磁盘会对性能造成影响，可以通过半同步复制解决因系统突然down掉导致binlog缓存数据丢失的问题。

binlog-format:二进制日志记录的方法，有三种方式：row(记录每一行的变更操作，优点：对复制的兼容性高，缺点：日志记录量大，对IO的影响也很大，也不容易用来做分析)，STATEMENT(记录操作的sql语句，这也是默认的格式，优点：日志量小，便于用来做分析，IO影响小，缺点：可能会导致复制出错例如有时候使用的某些函数)，MIXED(混合了上面两种格式,默认采用STATEMENT记录，当出现不确定函数时就采取row记录例如curret_user(),now()等)

2.在主库上执行，创建用户repl，授予192.168.143.154服务器使用用户repl的REPLICATION SLAVE权限。

mysql> create user repl@'192.168.143.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.143.%';
Query OK, 0 rows affected (0.00 sec)

3.刷新表并设置数据库只读

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

4.记录主库二进制文件名和偏移量

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

5.创建一个测试数据库和表

mysql> desc tt;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
| phone | char(20)     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

6.备份主库

[root@localhost ~]# mysqldump --single-transaction --master-data --triggers --routines --all-databases -uroot > /home/test1/all.sql

备份的方法有很多种：1.如果主库是在线不能停止服务，可以通过热备份方式，使用dump、ibbackup、xtrabackup等热备份工具备份数据库然后到从库还原。

2.如果主库允许停止服务那么可以直接cp主库数据目录下的所有文件到从库的路径下，可以使用xftp工具比较方便。

从库

1.配置my.cnf

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
server-id=7
log-bin=/var/lib/mysql/mysql-bin
relay_log=mysql-relay-bin  	#默认是主机名，如果主机名被修改就会找不到中继日志
read_only=on
max_binlog_size=100M
sync_binlog=0
binlog-format=MIXED

注意server-id不能和主相同

2.从库导入数据库

[root@localhost ~]# mysql -uroot </home/test2/all.sql

3.登入mysql

mysql> change master to master_host='192.168.143.154', master_user='repl', master_password='123', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=1489;

其中“MASTER_LOG_FILE=‘binlog.000003’, MASTER_LOG_POS=1489; ”的参数vim在all.sql里面输入关键字查找 或者在主数据库"SHOW MASTER STATUS"

4.启动从库slave线程

mysql> start slave;      
Query OK, 0 rows affected (0.01 sec)

此外还有停止和重置slave；
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

5.检查
在从库上执行

mysql> start slave;      
Query OK, 0 rows affected (0.01 sec)

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 4452
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 13
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 3. row ***************************
     Id: 14
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 102
  State: Waiting to reconnect after a failed registration on master
   Info: NULL
*************************** 4. row ***************************
     Id: 15
   User: system user
   Host: 
     db: NULL
Command: Query
   Time: 102
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
4 rows in set (0.01 sec)

表明已经连接上面了master

注意线程3中的Time字段：该时间表示上次执行的语句在主库二进制文件中记录的时间和更新到从库的时候的当前时间的时间差，如果主库更新非常频繁而从库又跟不上主库更新的速度的时候该时间差值会增大（影响的因素有：从库的硬件和主库的差距、网络传输、早期版本的从库sql线程是单线程写而主库应用前端的写的多线程并发写）。

测试
在主库插入一条数据库，看看从库是否同步成功，如果在从库插入是不会同步到主库的。

从库复制状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting to reconnect after a failed registration on master
                  Master_Host: 192.168.143.154
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: backup-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: Connecting
            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: 0
              Relay_Log_Space: 155
              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: 13120
                Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'repl'@'192.168.143.1%' (using password: YES) (Errno: 1045)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 6
                  Master_UUID: 69132615-8186-11ea-9c53-000c292c0c18
             Master_Info_File: mysql.slave_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: 200419 17:22:01
     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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

Slave_IO_State: 线程已经连接上主服务器，正等待二进制日志事件到达
Master_Host: 主服务器ip
Master_User: 连接主服务器使用的用户
Master_Port: 主服务器的端口
Connect_Retry: 当重新建立主从连接时，如果连接建立失败，间隔多久后重试，默认60s。
Master_Log_File: I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos: 在当前的主服务器二进制日志中，I/O线程已经读取的位置。
Relay_Log_File: SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos: SQL线程在当前的中继日志中已读取和执行的位置。
Relay_Master_Log_File: SQL线程执行的主服务器二进制文件
Slave_IO_Running: I/O线程是否运行并成功地连接到主服务器上。
Slave_SQL_Running: SQL线程是否运行。
Replicate_Do_DB:用于复制的数据库，必须在配置文件中配置了
Replicate_Ignore_DB:不用来复制的数据库
Replicate_Do_Table:复制表
Replicate_Ignore_Table:不复制的表
Replicate_Wild_Do_Table: 限制复制更新的表匹配指定的数据库和表名模式的语句
Replicate_Wild_Ignore_Table: 不要复制表匹配给出的通配符模式的语句
Last_Errno:错误代码
Last_Error:错误信息
Skip_Counter: SQL_SLAVE_SKIP_COUNTER的值
Exec_Master_Log_Pos: 主服务器上一个被执行的位置
Relay_Log_Space: 中继日志文件大小
Until_Condition: 在START SLAVE语句的UNTIL子句中指定的值
Until_Log_File: 用于指示日志文件名
Until_Log_Pos: 位置值
Master_SSL_Allowed: 如果允许对主服务器进行SSL连接，则值为Yes
否则NO
Master_SSL_CA_File:下面的这些都是SSL连接的一些信息
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 本字段是从属服务器落后多少的一个指示(这个状态是一个很重要的性能指标，正常为0，如果从服务器的I/O线程无法连接主服务器显示null)
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 最近的IO线程错误代码，其中2003代表I/o线程无法连接主服务器
Last_IO_Error: 最近的IO线程错误信息（例如：error reconnecting to master 'repl@192.168.1.6:3306' - retry-time: 60  retries: 3）
Last_SQL_Errno: 最近的SQL线程错误代码
Last_SQL_Error: 最近的SQL线程错误信息
Replicate_Ignore_Server_Ids:
Master_Server_Id: 主服务器的服务器ID
Master_UUID: 主服务器的UUID值
Master_Info_File: 从服务器的master.info文件路径
SQL_Delay: 正数表明slave有延迟了
SQL_Remaining_Delay: 整数表明延迟时间
Slave_SQL_Running_State: SQL线程运行状态（SQL线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。
）
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:最近的I/O线程错误时间
Last_SQL_Error_Timestamp:最近的SQL线程报错时间
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

其中需要注意比较重要的状态：Slave_SQL_Running之前的这十几个状态再加上Last_IO_Errno，Last_IO_Error，Last_SQL_Errno，Last_SQL_Error，SQL_Delay，SQL_Remaining_Delay，Slave_SQL_Running_State，Last_IO_Error_Timestamp，Last_SQL_Error_Timestamp，Seconds_Behind_Master

文件

在从库的数据库路径下会发现生成了三个文件：master.info,relay-log.info,relay-bin

master.info:用来记录从库的I/O线程当前读取到主库的binglog的位置。

relay-log.info：用来记录从库的SQL线程当前读取到中继日志(relay-bin)的位置。

relay-bin:中继日志，中继日志记录的格式和主库的二进制日志是一样的，但是中继日志在SQL线程执行完当前中继日志中的事件之后会删除中继日志中的内容。

@LuckY BoY

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
基于日志点的MySQL 主从复制

http://www.cnblogs.com/chenmh环境主：mysql（8.0.17 ），linux:CentOS Linux release 8.0.1905 (Core) ,ip:192.168.143.154从：mysql（8.0.17 ），linux:CentOS Linux release 8.0.1905 (Core) ,ip:192.168.143.155原理和...
复制链接

扫一扫

专栏目录