mariadb主从复制

采用一主一从，读写分离结构
Master: 172.16.11.31/16
Slave: 172.16.11.32/16

服务器配置

Master服务器配置

修改数据库配置文件

1
2
3
4
5
6
7
8
9
10
vim /etc/my.conf
#添加配置
[mysqld]
log-bin=mysql-bin   #[必须]启用二进制日志
server-id=31         #[必须]服务器唯一ID，默认是1(从数据库ID要比主数据库ID大)
#read-only=0    #是否只读,1代表只读,0代表读写
#忽略的数据,指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

配置完成重启数据库以使更改生效。

主库配置

登陆mysql,创建远程连接账号，并授予主从复制权限。

1
2
3
4
5
6
7
8
9
10
#创建同步用户，设置密码
#CREATE USER 'replication_user'@'slave_ip_address' IDENTIFIED BY 'password';
#分配权限
#GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_ip_address';
#刷新权限
#FLUSH PRIVILEGES;
#创建sync用户用于同步,并设置密码,该用户可在任意主机连接该MYSQL服务,分配权限并刷新权限。
CREATE USER 'sync'@'172.16.11.32' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'sync'@'172.16.11.32';
FLUSH PRIVILEGES;

Master服务器配置完毕

Slave服务器配置

修改数据库配置文件

1
2
3
4
vim /etc/my.conf
#添加配置
[mysqld]
server-id=32         #[必须]服务器唯一ID，默认是1(从数据库ID要比主数据库ID大)

配置完成重启数据库以使更改生效。
初始化从服务器：将主服务器的数据复制到从服务器上。在从服务器上执行以下命令(错误案例)：

1
2
3
4
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='172.16.11.31', MASTER_USER='sync',MASTER_PASSWORD='password';
START SLAVE;

Slave服务器配置完毕

验证复制状态：在主服务器上进行一些数据更改，然后在从服务器上执行以下命令

1
SHOW SLAVE STATUS\G

IO和SQL线程默认处于关闭状态，需要手动开启
确保输出中的”Slave_IO_Running”和”Slave_SQL_Running”字段都显示为”YES”，并且”Seconds_Behind_Master”字段的值不断增长。这表示从服务器正在成功地复制主服务器上的数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> SHOW SLAVE STATUS\G ;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.16.11.31
                   Master_User: sync
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 942
                Relay_Log_File: mysql01-relay-bin.000005
                 Relay_Log_Pos: 627
         Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
             ······

发现错误

1
2
#这里显示NO,
Slave_SQL_Running: No

问题定位:
在从服务器上CHANGE MASTER TO语句中没有手动备份

解决方法

有两种方法解决:

方法1: (在mariadb中不支持MASTER_AUTO_POSITION = 1,故本方法不可用)
注意:只能在mysql中使用

重新构建主从关系

1
2
3
4
stop slave;
CHANGE MASTER TO MASTER_HOST='172.16.11.31',MASTER_USER='sync',MASTER_PASSWORD='password',MASTER_AUTO_POSITION = 1;
start slave;
show slave status\G;
方法2:

在配置时,手动配置从服务器复制主服务器的指定位置.

主服务器查看状态:(二进制保存位置position

1
2
3
4
5
6
7
8
9
show master status;

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      639 |              |                  |
+------------------+----------+--------------+------------------+

重新构建主从关系,从服务器(slave)复制主服务器(master)指定位置

1
2
3
4
5
stop slave;
reset slave;
CHANGE MASTER TO MASTER_HOST='172.16.11.31',MASTER_USER='sync',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=639;
start slave;
show slave status\G;

master_host 为要更改主库的ip地址
master_password 为要修改的密码
master_log_file 为二进制文件
master_log_pos 为要复制的节点位置 (在master查的)

使用方法2后
1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主从复制完成

取消主从关系
1
2
3
4
5
#注意: ' '之间有空格，里面必须有内容，即使为空，也应该用空格代替，而不能什么都不写。
change master to master_user=' ',master_host=' ',master_password=' ';
slave stop;
reset slave;
show slave status\G;

主从数据库数据同步成功

参考
https://blog.csdn.net/weixin_30670965/article/details/101483425
https://blog.csdn.net/jesseyoung/article/details/41942809