企业实战（9）Mysql数据库实现主从同步，看这一篇就够了！

非著名运维

发布于 2022-06-22 14:30:56

1.4K0

发布于 2022-06-22 14:30:56

文章被收录于专栏：非著名运维

环境介绍：

CentOS 7.5

Mysql 5.7.29

Mysql主服务器：192.168.2.128

Mysql从服务器：192.168.2.129

Mysql主从同步原理：

当master服务器上的数据发生改变时(增、删、改)，则将其改变写入二进制binlog日志中；slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开启一个I/O 线程请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从库本地的中继日志中，从库(从节点)将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后IO线程和SQL线程将进入睡眠状态，等待下一次被唤醒。

注意几点：

1.master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。

2.slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和 master数据保持一致了。

3.Mysql主从复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

4.Mysql主从复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）

5.master和slave两节点间时间需同步。

在这里插入图片描述 如上图所示：

Mysql复制过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

第二部分就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

构建主从同步：

主从同步介绍

-实现数据自动同步的服务结构

-主服务器: 接受客户端访问连接

-从服务器: 自动同步主服务器数据

Master(主库) -启用binlog日志
Slave(从库) `-Slave_IO线程:复制master主机binlog日志文件里的SQL命令到本机的relay-log文件里。` `-Slave_SQL线程:执行本机relay-log文件里的SQL语句,实现与Master数据一致。`

构建思路

配置主库 -启用binlog日志、授权用户、查看 binlog日志信息
配置从服务器 -`确保与主服务器数据一致。` -`设置server_id、指定主库信息、启动slave程序`

配置Mysql主服务器

1.修改主配置文件

-启用binlog日志并重启服务

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=db128      //启用binlog日志,指定日志名以db128开头
server_id=128     //指定服务器ID号
...

[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# ls /var/lib/mysql/db128.*
/var/lib/mysql/db128.000001  /var/lib/mysql/db128.index

2.授权用户

-用户权限为 replication slave

-用户名自定义

-客户端地址允许从库连接

-密码自定义

注意： replication slave权限代表: 允许slave主机通过此用户连接master以便建立主从复制关系。

[root@localhost ~]# mysql -uroot -p123qqq...A
...
Server version: 5.7.29-log MySQL Community Server (GPL)
...
mysql> grant replication slave on *.* to mysqluser@"%" identified by "123qqq...A";
  //为主数据库授权用户mysqluser,权限为replication、slave,允许客户端地址为所有主机,允许访问所有库所有表*.*,授权用户密码为123qqq...A

3.查看日志信息

[root@localhost ~]# mysql -uroot -p123qqq...A
...
Server version: 5.7.29-log MySQL Community Server (GPL)
...
mysql> show master status;  //查看binlog记录日志信息的偏移量position
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| db128.000001 |   704787 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+

配置从服务器

1.修改主配置文件

-指定server_id，不允许与主库的server_id值相同

[root@test2 ~]# vim /etc/my.cnf
[mysqld]
server_id=129
...
[root@test2 ~]# systemctl restart mysqld

2.确保与主库数据一致

-在主库上备份数据，备份文件拷贝给从库

-在从库上使用备份文件恢复数据

-从库查看备份数据对应的binlog日志信息

主数据库操作：

--master-data：在备份文件中添加这次备份的数据对应的binlog日志名以及备份后数据的节点编号(偏移量),以便从库同步数据时,可以知道从哪个节点开始同步数据,保证主库与从库数据完全一致.

[root@localhost ~]# mysqldump -uroot -p123qqq...A --master-data test > /root/test.sql
 //在主库上备份数据库test，并且记录当前备份数据对应的binlog日志信息，备份文件名为test.sql

[root@localhost ~]# ls test.sql
test.sql

[root@localhost ~]# scp test.sql root@192.168.2.129:/root/

从数据库操作：

[root@test2 ~]# ls test.sql
test.sql

[root@test2 ~]# mysql -uroot -p123qqq...A;
mysql> create database test;   //从库上必须有需要恢复的库，因为要恢复test库，所以先创建空库test

[root@test2 ~]# mysql -uroot -p123qqq...A test < /root/test.sql

[root@test2 ~]# mysql -uroot -p123qqq...A;
mysql> use test;
+----------------+
| Tables_in_test |
+----------------+
| lss            |
| money          |
+----------------+

3.指定主库信息

命令格式： change master to master_host="主库IP地址", master_user="用户名", master_password="密码", master_log_file="binlog日志文件名", master_log_poss=偏移量;

主数据库查看binlog日志名及偏移量：

[root@localhost ~]# mysql -uroot -p123qqq...A
...
mysql> show master status;  //主库查看binlog记录日志信息的日志名及偏移量
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| db128.000001 |  2261338 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+

从库指定主库信息：

[root@test2 ~]# mysql -uroot -p123qqq...A;
...
mysql> show slave status;
Empty set (0.00 sec)

mysql> change master to
    -> master_host="192.168.2.128",    //指定主库IP地址
    -> master_user="mysqluser",       //主库授权用户
    -> master_password="123qqq...A",    //授权用户的密码
    -> master_log_file="db128.000001",   //主库binlog日志文件名
    -> master_log_pos=2261338;       //备份文件的日志偏移量
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;      //启动slave进程

注意：

1.指定的master信息会自动保存到/var/lib/mysql/master.info文件中。

2.若要更改指定的主库信息，需先执行stop slave，修改完成后在执行start slave。

4.查看slave状态

-确定IO线程、SQL线程都是Yes状态

在这里插入图片描述 报错解决：

UUID问题报错解决：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs…

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.128    //主库IP地址
                  Master_User: mysqluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db128.000002
          Read_Master_Log_Pos: 452757169
               Relay_Log_File: test2-relay-bin.000002
                Relay_Log_Pos: 3205508
        Relay_Master_Log_File: db128.000002
             Slave_IO_Running: Yes    //IO线程已运行
            Slave_SQL_Running: Yes    //SQL线程已运行
              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: 452757169
              Relay_Log_Space: 3205715
              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: 128
                  Master_UUID: e46c9961-5780-11ea-bf2f-000c128a8b6b
             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)

5.相关文件

-存放在从库数据库目录下

master.info                  //主库信息

relay-log.info               //中继日志信息

主机名-relay-bin.xxxx         //中继日志

主机名-relay-bin.index       //索引文件

查看从库服务器数据库目录下配置从库时生成的4种文件

[root@test2 ~]# ls /var/lib/mysql
...
test2-relay-bin.index   master.info    relay-log.info      
test2-relay-bin.000001  test2-relay-bin.000002

验证主从同步效果

主数据库操作：

在主数据库服务器中创建一个新表test并赋值，再去从服务器上查看数据是否同步。

[root@localhost ~]# mysql -uroot -p123qqq...A
mysql: [Warning] Using a password on the comm
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| zabbix             |
+--------------------+

mysql> use test;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| money          |
+----------------+

mysql> create table test(name varchar(25),city varchar(30),age int);

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| money          |
| test           |
+----------------+

mysql> insert into test.test values("mysql","china",11);

mysql> select * from test.test;
+-------+-------+------+
| name  | city  | age  |
+-------+-------+------+
| mysql | china |   11 |
+-------+-------+------+

从数据库操作：

查看主服务器上新增的数据是否已经同步到从服务器上。

[root@test2 ~]# mysql -uroot -p123qqq...A
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| zabbix             |
+--------------------+

mysql> use test;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| money          |
| test           |
+----------------+

mysql> select * from test.test;
+-------+-------+------+
| name  | city  | age  |
+-------+-------+------+
| mysql | china |   11 |
+-------+-------+------+

可以看到我们在主数据库服务器上新增的数据已经成功同步到从服务器上了。

延伸：

MySql主从同步的延迟问题(如何产生):

主库针对写操作，顺序写binlog日志，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作，主库对所有增、删、改产生binlog日志，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的增、删、改操作在slave实施。增、删、改的IO操作是随机的，不是顺序的，成本高很多，还可能与slave上的其他查询操作产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个增、删、改操作卡住了，需要执行10分钟，那么所有之后的增、删、改操作会等待这个增、删、改操作执行完才会继续执行，这就导致了延时。(主库是多进程，从库单进程（回放relaylog），所以在高并发时，会出现延迟。)

MySQL主从同步延迟怎么解决？

把主从同步配置为异步模式，保证至少有1台数据库服务器与主服务器数据一致。