MairaDB&Mysql 主从同步之主从从和半同步的设置

软件准备:

在MySQL官网上下载搭建集群所需软件MySQL:

https://www.mysql.com/downloads/

正文:

步骤目录:
步骤一:服务器列表
1.1 服务器列表
1.2 服务器列表讲解

步骤二:准备环境

步骤三:配置db51主服务器192.168.1.51
3.1 在db51主服务器上生成数据同步账户zmy
3.2 启用db51主服务器的binlog日志,修改/etc/my.cnf配置,重新启动MariaDB(或者MySQL)服务程序
3.2.1 指定db51主服务器ID号、允许日志同步
3.2.2 重启db51主服务器MariaDB(或者MySQL)服务
3.2.3 确保db51主服务器/var/lib/mysql下面有两个文件
3.3 查看db51主服务正在使用的日志信息并记录下当前的日志文件名、偏移的位置(下面SLAVE发起复制时需要用到)

步骤四:配置db52从服务器192.168.1.52
4.1 在db52从服务器上生成数据同步账户ymz
4.2 修改db52从服务器的/etc/my.cnf配置,启用binlog日志,指定server_id并允许级联复制,之后重启MariaDB(或者MySQL)
4.2.1 指定db52主服务器ID号、允许日志同步并启用级联复制
4.2.2 重启db52从服务器MariaDB(或者MySQL)服务
4.2.3 确保db52从服务器/var/lib/mysql下面有两个文件
4.2.4 查看db52从服务器正在使用的日志信息
4.3 在db52从服务器上验证主库的授权用户
4.4 db52从服务器通过change master语句指定master服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置(参考master上的状态输出)
4.5 启动db52从服务器的slave进程
4.6 查看db52从服务器进程状态信息,通过show slave status语句可查看从服务器状态,确认其中的IO线程、SQL线程正常运行,才能成功同步,IO线程和SQL线程必须是Yes

步骤五:配置db53从服务器192.168.4.53
5.1 在db53从服务器上验证db52从服务器的授权用户
5.2 指定db53从服务器的server_id
5.3 重新启动db53的MariaDB(或者MySQL)服务
5.4 在db53上将数据库的主库指向db52
5.5 启动db52从服务器的slave进程
5.6 查看db53从服务器进程状态信息,通过show slave status语句可查看从服务器状态,确认其中的IO线程、SQL线程正常运行,才能成功同步,IO线程和SQL线程必须是Yes

步骤六:使用客户端验证配置MariaDB(或者MySQL)主主从集群,这里就直接用db52数据库代替
6.1 在主服务器db51上在主库上授权访问gamedb库的用户
6.2 客户端使用授权用户连接主库,建库、表、插入记录
6.3 客户端使用授权用户连接2台从库时,也可以看到主库上新的库表记录

步骤七:主从同步常见问题解决
7.1 常见问题之Slave_IO 线程没有运行
7.1.1 错误现象
7.1.2 原因分析
7.1.3 解决思路
7.2 常见问题之Slave_SQL 线程没有运行
7.2.1 错误现象
7.2.2 原因分析
7.2.3 解决方法

步骤八:MairaDB(或者MySQL)常用配置选项补充
8.1 适用于Master服务器的常用配置选项
8.2 适用于Slave服务器的常用配置选项

步骤九:其他主从同步结果介绍
步骤十:配置MariaDB(或者MySQL)的半同步配置
10.1 MariaDB(或者MySQL)的同步方式介绍
10.1.1 异步复制(Asynchronous replication)
10.1.2 全同步复制(Fully synchronous replication)
10.1.3 半同步复制(Semisynchronous replication)
10.2 查看是否允许动态加载模块
10.3 命令行加载插件,用户需有SUPER权限
10.3.1 主库上面操作
10.3.2 从库上面操作
10.3.3 查看系统库下的表,模块是否安装成功
10.4 启用半同步复制,在安装完插件后,半同步复制默认是关闭的
10.4.1 主库上面执行
10.4.2 从库上面执行
10.4.3 查看半同步复制模式是否启用
10.5 永久启用半同步复制
10.5.1 主库配置
10.5.2 从库配置
10.5.3 在高可用架构下,master和slave需同时启动,以便在切换后能继续使用半同步复制

具体的操作步骤:
步骤一:服务器列表
1.1 服务器列表
db51 ip:192.168.1.51 作用:主数据库服务器
db52 ip:192.168.1.52 作用:主数据库服务器
db53 ip:192.168.1.53 作用:主数据库服务器

1.2 服务器列表讲解
192.168.4.51是MySQL主服务器,负责提供同步源;另一台192.168.4.52作为192.168.4.51从服务器,最后一台192.168.4.53作为192.168.4.52从服务器,通过调取主服务器上的binlog日志,客户端访问主库51 时 创建库表记录 在52 和53 数据库服务器都可以看到


步骤二:准备环境

系统环境centos7,按照服务器列表配置好ip地址和主机名,安装好MariaDB(或者主从同步),为了在启用binlog日志及同步之前保持主、从库的一致性,主从同步未配置之前,要保证从库上要有主库上的数据,禁用selinux,关闭防火墙服务,保证物理连接正常,修改所有数据库的密码策略让数据库账户可以使用"123456"作为自己的登录密码,如果是mysql则要统一降数据库的登录密码修改成123456

步骤三:配置db51主服务器192.168.1.51
3.1 在db51主服务器上生成数据同步账户zmy
[root@db51 ~]# mysql -uroot -p123456
mysql> grant   replication  slave   on  *.*  to  zmy@"%"   identified    by  "123456";
Query OK, 0 rows affected, 1 warning (0.03 sec)

3.2 启用db51主服务器的binlog日志,修改/etc/my.cnf配置,重新启动MariaDB(或者MySQL)服务程序
3.2.1 指定db51主服务器ID号、允许日志同步
[root@db51 ~]# vim /etc/my.cnf
[mysqld]
log_bin=db51                      #启用binlog日志,并指定文件名前缀
server_id=51                        #指定服务器ID号
binlog-format="mixed"              #指定binlog日志格式

3.2.2 重启db51主服务器MariaDB(或者MySQL)服务
[root@db51 ~]# systemctl  restart   mysqld

3.2.3 确保db51主服务器/var/lib/mysql下面有两个文件
[root@db51 ~]#  ls  /var/lib/mysql/db51.*
/var/lib/mysql/db51.000001  /var/lib/mysql/db51.index

3.3 查看db51主服务正在使用的日志信息并记录下当前的日志文件名、偏移的位置(下面SLAVE发起复制时需要用到)
    mysql> show  master  status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db51.000002 |      435 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

步骤四:配置db52从服务器192.168.1.52
4.1 在db52从服务器上生成数据同步账户ymz
[root@db52 ~]# mysql -u root -p123456
mysql> grant   replication  slave  on  *.*  to   ymz@"192.168.4.53"  identified  by "654321";
Query OK, 0 rows affected, 1 warning (0.00 sec)

4.2 修改db52从服务器的/etc/my.cnf配置,启用binlog日志,指定server_id并允许级联复制,之后重启MariaDB(或者MySQL)
4.2.1 指定db52主服务器ID号、允许日志同步并启用级联复制
[root@db52 ~]# vim /etc/my.cnf 
[mysqld]
server_id=52                   
log-bin=db52
binlog-format="mixed"
log_slave_updates                          #允许级联复制

4.2.2 重启db52从服务器MariaDB(或者MySQL)服务
[root@db52 ~]# systemctl restart mysqld

4.2.3 确保db52从服务器/var/lib/mysql下面有两个文件
[root@db52 ~]# ls /var/lib/mysql/db52.*
/var/lib/mysql/db52.000001  /var/lib/mysql/db52.index

4.2.4 查看db52从服务器正在使用的日志信息
[root@db52 ~]# mysql -uroot -p123456
mysql>  show  master  status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db52.000001 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)    //查看日志文件名、偏移的位置

4.3 在db52从服务器上验证主库的授权用户
[root@db52 ~]# mysql -h192.168.4.51 -uzmy -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>                                #验证成功

4.4 db52从服务器通过change master语句指定master服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置(参考master上的状态输出)
[root@db52 ~]# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.1.51",
-> master_user="zmy",
-> master_password="123456",
-> master_log_file="db51.000002",
-> master_log_pos=437;
Query OK, 0 rows affected, 2 warnings (0.43 sec)

4.5 启动db52从服务器的slave进程
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

4.6 查看db52从服务器进程状态信息,通过show slave status语句可查看从服务器状态,确认其中的IO线程、SQL线程正常运行,才能成功同步,IO线程和SQL线程必须是Yes
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: yaya
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db51.000002
Read_Master_Log_Pos: 437
Relay_Log_File: db52-relay-bin.000002
Relay_Log_Pos: 315
Relay_Master_Log_File: db51.000002
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: 437
Relay_Log_Space: 521
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: 81a13101-aa66-11e8-ad11-525400019e62
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)

步骤五:配置db53从服务器192.168.4.53
5.1 在db53从服务器上验证db52从服务器的授权用户
[root@db53 ~]# mysql  -h192.168.1.52  -ymz  -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>                  #验证成功

5.2 指定db53从服务器的server_id
[root@db53 ~]# vim/etc/my.cnf
[mysqld]
validate_password_policy=0
validate_password_length=6
server_id=53

5.3 重新启动db53的MariaDB(或者MySQL)服务
[root@db53 ~]# systemctl restart mysqld

5.4 在db53上将数据库的主库指向db52
[root@db53 ~]# mysql -uroot -p123456
mysql> change  master  to 
    -> master_host="192.168.4.52",
    -> master_user="ymz",
    -> master_password="123456",
    -> master_log_file=" db52.000001",
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.37 sec)

5.5 启动db52从服务器的slave进程
mysql>  start  slave;
Query OK, 0 rows affected (0.04 sec)

5.6 查看db53从服务器进程状态信息,通过show slave status语句可查看从服务器状态,确认其中的IO线程、SQL线程正常运行,才能成功同步,IO线程和SQL线程必须是Yes
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.52
                  Master_User: user53
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db52.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: db53-relay-bin.000003
                Relay_Log_Pos: 315
        Relay_Master_Log_File: db52.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

步骤六:使用客户端验证配置MariaDB(或者MySQL)主主从集群,这里就直接用db52数据库代替
6.1 在主服务器db51上在主库上授权访问gamedb库的用户
[root@db51 ~]# mysql -uroot  -p123456
mysql> grant  all  on  ec.*   to   zhu@"%"  identified by  "123456";
Query OK, 0 rows affected, 1 warning (0.03 sec)

6.2 客户端使用授权用户连接主库,建库、表、插入记录
[root@db52 ~]# mysql  -h192.168.4.51  -zhu  -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>   #验证成功
MySQL [(none)]> create  database  gamedb;   #创建测试库
Query OK, 1 row affected (0.04 sec)
MySQL [(none)]> create  table  gamedb.t1(id int);   #在gamedb下创建t1表
Query OK, 0 rows affected (0.17 sec)
MySQL [(none)]> insert into  gamedb.t1 values(8888);   #在t1表中插入数值
Query OK, 1 row affected (0.22 sec)

6.3 客户端使用授权用户连接2台从库时,也可以看到主库上新的库表记录
[root@db52 ~]# mysql  -h192.168.4.52  -uzhu  -p123456  #验证52主机的状态
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select  * from gamedb.t1;         #查询插入的表格
+------+
| id   |
+------+ 
| 8888 |
+------+
1 row in set (0.00 sec)
MySQL [(none)]> exit

[root@db52 ~]# mysql  -h192.168.4.53  -uzhu  -p123456 #验证53主机的状态
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select  * from gamedb.t1;
+------+
| id   |
+------+
| 8888 |
+------+
1 row in set (0.00 sec)

步骤七:主从同步常见问题解决
7.1 常见问题之Slave_IO 线程没有运行
7.1.1 错误现象
报错:Slave_IO_Running:No
mysql > show slave status \G;
    Slave_IO_Running:No
    Last_IO_Error:报错信息.......

7.1.2 原因分析
连接不上 master数据库服务器

7.1.3 解决思路
检查物理链接看是不是ping得通
检查主库授权的用户是否存在,密码是否正确
禁用防火墙、关闭SELinux
或是binlog日志文件制定错误(日志名或pos节点)
mysql> stop slave;
mysql> change master to 选项=值;
mysql> start slave;

7.2 常见问题之Slave_SQL 线程没有运行
7.2.1 错误现象
报错:Slave_SQL_Running:No
mysql> show slave status \G;
  Slave_SQL_Running:No
  Last_SQL_Error:报错信息......

7.2.2 原因分析
执行本级中继日志里的sql命令时,sql命令使用的库、表或记录在本机不存在

7.2.3 解决方法
mysql> stop slave;
创建或恢复需要用到的库或表
mysql> start slave;

步骤八:MairaDB(或者MySQL)常用配置选项补充
8.1 适用于Master服务器的常用配置选项
binlog_do_db=name #设置Master对哪些库记日志
binlog_ignore_db=name #设置Master对哪些库不记日志

8.2 适用于Slave服务器的常用配置选项
log_slave_updates #记录从库更新,允许链式复制(A-B-C)
relay_log=dbsvr2-relay-bin #指定中继日志文件名
replicate_do_db=mysql #仅复制制定库,其他库将被忽略,此选项可设置多条(省略复制其他所有库)
replicate_ignore_db=test #不复制哪些库,其他库将被忽略,ignore-db与do-db只需选用其中一种

步骤九:其他主从同步结果介绍
基本应用类有:一主一从
扩展应用类有:主从从、两台数据库互为主从、一主多从

步骤十:配置MariaDB(或者MySQL)的半同步配置
10.1 MariaDB(或者MySQL)的同步方式介绍
10.1.1 异步复制(Asynchronous replication)
主库执行完一次事务后,立即将结果返给客户端,并不关心从库是否已经接接收并处理

10.1.2 全同步复制(Fully synchronous replication)
当主库执行完一次事务,且所有从库都执行了该事务后才返回给客户端

10.1.3 半同步复制(Semisynchronous replication)
介于异步复制和全同步复制之间
主库在执行完一次事务后,等待至少一个从库接收到并写到relay log中才返回给客户端

10.2 查看是否允许动态加载模块
(查看是否允许动态加载模块默认允许)
mysql> show  variables  like  'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.01 sec)

10.3 命令行加载插件,用户需有SUPER权限
10.3.1 主库上面操作
mysql> INSTALL PLUGIN rpl_semi_sync_master   SONAME   'semisync_master.so';

10.3.2 从库上面操作
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME  'semisync_slave.so';

10.3.3 查看系统库下的表,模块是否安装成功
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        |
+----------------------+---------------+
2 rows in set (0.00 sec)

10.4 启用半同步复制,在安装完插件后,半同步复制默认是关闭的
10.4.1 主库上面执行
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

10.4.2 从库上面执行
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

10.4.3 查看半同步复制模式是否启用
mysql>  show  variables  like  "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON    |
| rpl_semi_sync_slave_enabled  | ON    |
+------------------------------+-------+
2 rows in set (0.00 sec)

10.5 永久启用半同步复制
10.5.1 主库配置
[root@master51 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

10.5.2 从库配置
[root@slave52 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

10.5.3 在高可用架构下,master和slave需同时启动,以便在切换后能继续使用半同步复制
[root@master51 ~]# vim /etc/my.cnf
[mysqld]
plugin-load \
="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1