当前位置：首页 > 有云笔记 > RDBMS2 > 正文内容

MySQL主从同步、主从同步模式

小白3年前 (2022-03-21)RDBMS2155200

第一种同步结构 (一主一从同步结构)

数据库服务器 192.168.4.51 配置为master服务器

数据库服务器 192.168.4.52 配置为 slave服务器

第一步配置master服务器 192.168.4.51

具体步骤如下：

1) 启用binlog日志文件

vim /etc/my.cnf
[mysqld]
server_id=51
log_bin=master51   #日志默认存储在数据库目录下
:wq
]# systemctl  restart mysqld

2）用户授权

]# mysql -uroot  -p密码
replication slave 让用户有复制命令权限
mysql> grant  replication slave on *.*  to  repluser@"%"
identified by  "123qqq...A";

3）查看日志信息

#查看到的日志名和偏移量是给从服务器使用的

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

第二步配置slave服务器 192.168.4.52

具体步骤如下：

1) 指定server_id 并重启mysqld服务

vim /etc/my.cnf
[mysqld]
server_id=52  #自己添加的
:wq
systemctl  restart mysqld

2）确保数据一致（如果一致的此步骤可以省略）

3）指定主服务器信息

]#mysql -uroot  -p密码

mysql> show slave status \G  #不是从数据库服务器
Empty set (0.00 sec)

mysql> change master to  master_host="192.168.4.51",
master_user="repluser" , master_password="123qqq...A",
master_log_file="master51.000001" , master_log_pos=441 ;

4）启动slave进程

mysql> start slave;

5）查看状态（IO线程和 SQL线程必须同时是YES 状态）

mysql> show slave status \G  
  Master_Host: 192.168.4.51
                  Master_User: repluser
                  Master_Port: 3306
  Master_Log_File: master51.000001
  Slave_IO_Running: Yes    #IO线程
                  Slave_SQL_Running: Yes   #SQL线程

根据 IO线程和 SQL线程的报错信息排错

mysql> show slave status \G        
Last_IO_Error:  IO线程的报错信息 
Last_SQL_Error:  SQL线程的报错信息

测试主从同步的配置

1）在主服务器host51 建库建表插入记录

mysql> create database db1;
mysql> create  table db1.t1(id int);
mysql> insert into db1.t1 values(119);
mysql> insert into db1.t1 values(120);
mysql> insert into db1.t1 values(114);

2）在从服务器host52 可以查看到同样的数据

]#mysql -uroot -p123qqq...A   -e 'select * from db1.t1'

正常的排错方法：根据 IO线程和 SQL线程的报错信息排错

mysql> show slave status \G        
Last_IO_Error:  IO线程的报错信息 
Last_SQL_Error:  SQL线程的报错信息

报错示例1 及解决办法

Last_IO_Error: Got fatal error 1236 from master

when reading data from binary log: 'Could not find first log file name in binary log index file'

在从服务器执行如下操作：

mysql> stop slave;

在主服务器查看日志名和偏移量

host51  show  master status;

在从服务器重新指定日志名和偏移量

mysql> change  master to  master_log_file="日志名" , master_log_pos=偏移量；
mysql> start slave;
mysql> show  slave status \G

错误示例2 及解决办法

Last_IO_Error: Fatal error:

The slave I/O thread stops because

master and slave have equal MySQL server UUIDs;

these UUIDs must be different for replication to work

在从服务器做如下操作：

vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=2dac4865-4770-11ec-9103-6462be92a06e   修改一个字母（字母个数不能少）
:wq
systemctl   restart  mysqld

错误示例3 及解决办法

Last_IO_Error: error connecting to master 'repluser@192.168.4.51:3306' retry-time: 60 retries: 2

在从服务器做如下操作：

MySQl> stop  slave;
mysql> change master to  master_user="repluser",master_password="123qqq...A";
MySQL>  start slave;
mysql> show slave status \G

所有错误的统一解决办法：

把slave角色的服务器恢复为独立的数据库服务器，重新配置为从服务器。

在从服务器执行如下操作：

systemctl  stop   mysqld 
cd  /var/lib/mysql/
rm  -rf master.info
rm -rf   *-relay-bin.*
rm -rf relay-log.info
systemctl   start  mysqld

第二种同步结构 (一主多从同步结构)

创建1台新的数据库服务器配置地址是 192.168.4.53

实验要求：把数据库服务器192.168.4.53也配置为192.168.4.51 的从服务器

在数据库服务器host53 做如下配置：

具体步骤如下：

1) 指定server_id 并重启mysqld服务

[root@host53 ~]# vim /etc/my.cnf
[mysqld]
server_id=53  #添加
:wq
[root@host53 ~]# systemctl  restart mysqld

2）确保数据一致（如果一致的此步骤可以省略）

具体操作如下：

第1步：在主服务器host51 对db1库做完全备份，然后把

备份文件拷贝给host53 主机

--master-data 做完全备份数据时，在备份文件里记录使用的日志名和偏移量

[root@host51 ~]# mysqldump -uroot -pNSD2107...a --master-data -B db1 > /root/db1.sql
[root@host51 ~]# scp /root/db1.sql  192.168.4.53:/opt/

第2步：host53 主机使用备份文件恢复数据

[root@host53 ~]# mysql -uroot -p123qqq...A  <  /opt/db1.sql                          
[root@host53 ~]# mysql -uroot -p123qqq...A -e 'show databases'
[root@host53 ~]# mysql -uroot -p123qqq...A -e 'select count(*) from db1.t1'

3）指定主服务器信息

#在备份文件里查看日志名和偏移量

[root@host53 ~]# grep master51 /opt/db1.sql 
CHANGE MASTER TO MASTER_LOG_FILE='master51.000001', MASTER_LOG_POS=2000;

#管理员root 登录指定主服务器信息

[root@host53 ~]# mysql -uroot -p123qqq...A

mysql> change master to master_host="192.168.4.51" , master_user="repluser",
master_password="123qqq...A",master_log_file="master51.000001",
master_log_pos=2000;

4）启动slave进程

mysql> start slave;

5）查看状态（IO线程和 SQL线程必须同时是YES 状态）

mysql> show slave status \G
   Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

6) 查看数据

mysql> select  * from  db1.t1;

测试一主多从的配置

#在主服务器插入新数据

mysql> insert into db1.t1 values(99988);

# 两台从服务器可以看到一样的数据

[root@host52 mysql]# mysql -uroot -p123qqq...A -e 'select * from db1.t1'
[root@host53 mysql]# mysql -uroot -p123qqq...A -e 'select * from db1.t1'

mysql 主从从同步结构实验

主从从结构 : 给一主一从结构中的从服务器也配置从服务器

环境准备：

目的1 掌握如何把从服务器恢复为独立的数据库服务器

目的2 节省时间(用恢复为独立数据库服务器的host53 与新的数据库服务器 host54 和 host55 配置主从从结构实验)

把主机host53 恢复为独立的数据库服务器

cd /var/lib/mysql
rm -rf master.info 
rm -rf host53-relay-bin.*
rm -rf relay-log.info 
systemctl  restart mysqld

准备2台新的数据库服务器 ip地址分别为 192.168.4.54 和 192.168.4.55

具体配置步骤如下：

第1步：配置主数据库服务器 192.168.4.53

1）启用binlog日志

vim /etc/my.cnf
[mysqld]
server_id=53
log_bin=master53
:wq
systemctl restart  mysqld

2）用户授权

host53~]# mysql  -uroot  -p123qqq...A

mysql> grant replication slave on *.*  to repluser@"%" identified by "123qqq...A";

3）查看日志信息

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master53.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

第2步：配置主机192.168.4.54

说明：因为host54主机同时是2种角色，所以2种角色的配置都要有

vim /etc/my.cnf
[mysqld]
server_id=54
log_bin=master54
log_slave_updates #允许级联复制，host54主机把自己主服务器的数据

拷贝给自己的从服务器。

:wq
[root@host54 ~]# systemctl  restart mysqld
[root@host54 ~]# mysql -uroot -p123qqq...A

mysql> grant  replication slave on *.*  to repluser@"%" identified by "123qqq...A";
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master54.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#指定主服务器信息,日志名偏移量要做host53主机查看后填写

[root@host54 ~]# mysql -uroot -p123qqq...A

mysql> change master to master_host="192.168.4.53",master_user="repluser",master_password="123qqq...A",
    -> master_log_file="master53.000001",master_log_pos=441;
mysql> start slave;
mysql> show slave status \G
 Master_Host: 192.168.4.53
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

第3步：配置主机192.168.4.55 (做host54主机的从服务器)

1) 指定server_id 并重启mysqld服务

2）指定主服务器信息（binlog日志名和偏移量要做host54主机查看后填写）

3）启动slave进程

4）查看进程状态

]# vim /etc/my.cnf
[mysqld]
server_id=55
:wq
systemctl restart  mysqld

]# mysql  -uroot  -p123qqq...A

mysql> change master to master_host="192.168.4.54",master_user="repluser",master_password="123qqq...A",
    -> master_log_file="master54.000001",master_log_pos=441;
mysql> start slave;
mysql> show slave status \G
Master_Host: 192.168.4.54
Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

第4步：验证主从从结构的配置

#在主服务器Host53主机创建的数据在主机host54 和 host55 都能看到

[root@host53 ~]# mysql -uroot -p123qqq...A -e 'create database  gamedb'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host53 ~]# mysql -uroot -p123qqq...A -e 'create table  gamedb.a(id int)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host53 ~]# 
[root@host53 ~]# mysql -uroot -p123qqq...A -e 'insert into gamedb.a values(888)'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host53 ~]# mysql -uroot -p123qqq...A -e 'select * from gamedb.a'

mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  888 |
+------+
[root@host53 ~]#

[root@host54 ~]# mysql -uroot -p123qqq...A -e 'select * from gamedb.a'

mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  888 |
+------+
[root@host55 ~]#

[root@host55 ~]# mysql -uroot -p123qqq...A -e 'select * from gamedb.a'

mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  888 |
+------+
[root@host55 ~]#

第四种同步结构 主主结构

主主结构也叫互为主从。

准备2台新数据库服务器，IP地址192.168.4.68 和 192.168.4.69

2台数据库服务器分别做彼此的从服务器和主服务器，

2台服务器2种角色的配置都要有

第1步: 配置数据库服务 host68

vim /etc/my.cnf
[mysqld]
server_id=68
log_bin=master68
:wq
systemctl  restart  mysqld

]# mysql  -uroot  -p123qqq...A  
mysql> grant  replication slave on *.*  to  repluser@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master68.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) 
mysql>

第2步: 配置数据库服务 host69

[root@host69 ~]# vim /etc/my.cnf
[mysqld]
server_id=69
log_bin=master69
:wq
systemctl  restart  mysqld

mysql> grant replication slave on *.*  to repluser@"%"  identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.04 sec)
 
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master69.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

mysql>
#把自己配置为host68的slave服务器，日志名和偏移量要在host68主机查看后填写
mysql> change master to  master_host="192.168.4.68",master_user="repluser",master_password="123qqq...A",
    -> master_log_file="master68.000001",master_log_pos=441;
mysql> start slave;
mysql> show slave status \G
Master_Host: 192.168.4.68
Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

第3步：在host68 主机数据库管理员登录服务后

#把自己指定为host69 主机的slave 服务器

[root@host68 ~]# mysql -uroot -p123qqq...A

mysql> change master to  master_host="192.168.4.69" , master_user="repluser" , master_password="123qqq...A",
    -> master_log_file="master69.000001",master_log_pos=441;
mysql> start slave;
mysql> show slave status \G
Master_Host: 192.168.4.69
Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

第4步：测试主主结构的配置

先在host68主机健库表插入记录，在host69 主机可以看到同样的数据

[root@host68 ~]# mysql -uroot -p123qqq...A -e 'create database db1'
[root@host68 ~]# mysql -uroot -p123qqq...A -e 'create table db1.a(id int)'
[root@host68 ~]# mysql -uroot -p123qqq...A -e 'insert into db1.a values(666)'
[root@host69 ~]# mysql -uroot -p123qqq...A -e 'select * from db1.a'

mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  666 |
+------+
[root@host69 ~]#

然后再在host69 库下的表插入新记录，在host68主机可以看到同样的数据

[root@host69 ~]# mysql -uroot -p123qqq...A -e 'insert into db1.a values(777)' 
[root@host68 ~]# mysql -uroot -p123qqq...A -e 'select * from db1.a'

mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  666 |
|  777 |
+------+
[root@host68 ~]#

mysql 主从同步复制数据工作模式

1）主从同步复制模式可以在任意数据库服务器启用半同步服务模式

支持的工作方式：

第1种异步复制模式（默认）

主服务器执行完一次事务后，立即将结果返给客户端，

不关心从服务器是否已经同步数据。

第2种半同步复制模式

主服务器在执行完一次事务后，等待至少一台从服务器同步数据完成，

才将结果返回给客户端。

统一在 192.168.4.50 主机做实验

方法1 命令行设置

好处：不用重启服务马上生效

安装模块

#安装master模块

MySQL> INSTALL  PLUGIN  rpl_semi_sync_master   SONAME  "semisync_master.so";

#安装slave模块

MySQL> INSTALL  PLUGIN  rpl_semi_sync_slave   SONAME  "semisync_slave.so";

查看模块是否安装

mysql> SELECT  plugin_name, plugin_status FROM  information_schema.plugins  
WHERE plugin_name LIKE "%semi%"; 
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+

启用模块

#启用master模块
mysql> SET  GLOBAL rpl_semi_sync_master_enabled=1;
         
#启用slave模块
mysql> SET  GLOBAL rpl_semi_sync_slave_enabled=1;

查看模块是否启用

MySQL> SHOW  VARIABLES  LIKE  "rpl_semi_sync_%_enabled";         
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON    |
| rpl_semi_sync_slave_enabled  | ON    |
+------------------------------+-------+

方法2 永久配置编辑主配置文件

vim  /etc/my.cnf
[mysqld]
#安装模块   plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#启用模块

rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_enabled=1
:wq

重启服务模块依然是启用状态

systemctl   restart mysqld

MySQL> SHOW  VARIABLES  LIKE  "rpl_semi_sync_%_enabled";       +------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON    |
| rpl_semi_sync_slave_enabled  | ON    |