Mysql主从复制

Author： admin
发布时间：September 21, 2018
873views
No comments
25271 words
Categories： Linux 数据库

Mysql主从简介

mysql的主从复制，是用来建立一个和主数据库完全一样的数据库环境，
从库会同步主库得所有数据，可轻松实现故障转移。

MySQL主从主要作用

实现数据库的备份；
基于数据库备份，可以实现故障转移；
基于数据库备份，可以实现读写分离；

MySQL主从工作原理

MySQL主从配置选项

Master
写在主库配置文件里参数（对所有从库都有效）

应包括希望同步- 的所有库
对采用 MyISAM 的库,可离线备份

选项	用途
binlog_do_db=name	设置 Master 对哪些库记日志
binlog_ignore_db=nane	设置 Master 对哪些库不计日志

Slave
写在从库配置文件里参数（只针对从库本机有效）

离线导入由 Master 提供的备份
清空同名库(若有的话

选项	用途
log_slave_updates	记录从库更新，允许链式复制（A-B-C）
relay_log=dbsvr2-relay-bin	指定中继日志文件名
replicate_do_db=mysql	仅复制指定库，其他库将被忽略，此选项可设置多条（省略时复制所有库）
replicate_ignore_db=test	不复制指定库，其他库将被忽略（ignore与do选项只需选用其中一种）

slave相关文件

文件名	说明
master.info	主库信息
relay-log.info	中继日志信息
主机名-relay-bin.xxxxxx	中继信息
主机名-relay-bin.index	索引文件

MySQL一主一从

mysql主从同步配置步骤

配置主库
启用binlog日志
用户授权
查看当前正在使用的binlog日志
配置从库
指定server_id
指定主库信息
启动slave程序
查看slave程序的状态信息

	主	从
IP地址	192.168.1.11	192.168.1.22

master端

安装MySQL

[root@localhost ~]# yum -y install mariadb mariadb-server

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]        # 在[mysqld]字段下，添加以下行
log-bin=node4-bin
log-slave-updates=true
server-id=1

启动MySQL

[root@localhost ~]# systemctl start mariadb

检查MySQL运行状态

# 检查MySQL进程
[root@localhost ~]# ps -ef | grep mysql
mysql     14832      1  0 10:52 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     15018  14832  1 10:52 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      15068   8998  0 10:52 pts/0    00:00:00 grep --color=auto mysql

# 检查MySQL端口
[root@localhost ~]# netstat -anpt | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      15018/mysqld

设置MySQL登录密码并登录

# 设置密码
[root@localhost ~]# mysqladmin -uroot -p password "123456"
# 登录数据库
[root@localhost ~]# mysql -uroot -p123456

验证二进制日志功能是否开启

MariaDB [(none)]> show variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
| sql_log_bin   | ON    |
+---------------+-------+
2 rows in set (0.00 sec)

授权给slave

MariaDB [(none)]> grant replication slave on *.* to "myslave"@"192.168.1.22" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

# 刷新
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

查看master最新状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| node4-bin.000001 |      401 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

slave端

安装MySQL

[root@localhost ~]# yum -y install mariadb mariadb-server

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]        # 在[mysqld]字段下，添加以下行
server-id=2

启动MySQL

[root@localhost ~]# systemctl start mariadb

设置MySQL登录密码并登录

# 设置密码
[root@localhost ~]# mysqladmin -uroot -p password "123456"
# 登录数据库
[root@localhost ~]# mysql -uroot -p123456

指定master

MariaDB [(none)]> change master to master_host="192.168.1.11", master_user="myslave", master_password="123456", master_log_file="node4-bin.000001", master_log_pos=401;
Query OK, 0 rows affected (0.07 sec)

启动slave

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: node4-bin.000001
          Read_Master_Log_Pos: 401
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: node4-bin.000001
             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: 539
              Relay_Log_Space: 245
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'myslave@192.168.1.11:3306' - retry-time: 60  retries: 86400  message: Can't connect to MySQL server on '192.168.1.11' (113)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

发现错误：Slave_IO_Running: Connecting

解决思路：

导致lave_IO_Running 为connecting 的原因主要有以下 3 个方面：

1、网络不通
2、密码不对
3、pos不对

首先我确定MySQL登录密码和pos码不可能出错，可以排除；
网络我通过ping测试发现可以通，会不会是其它原因呢？
然后我还是准备关闭防火墙，再来测试一下

[root@localhost ~]# systemctl stop firewalld.service

再次查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: node4-bin.000001
          Read_Master_Log_Pos: 401
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: node4-bin.000001
             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: 340
              Relay_Log_Space: 825
              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: 1
1 row in set (0.00 sec)

如果遇到的问题是：Slave_SQL_Running：no

MariaDB [(none)]> stop slave;
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;     # 让mysql跳过一次错误，继续执行
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

测试一主一从同步复制

在master创建数据库shuaiguoer

MariaDB [(none)]> create database shuaiguoer;
Query OK, 1 row affected (0.01 sec)

在slave查看数据库是否已复制

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shuaiguoer         |
| test               |
+--------------------+
5 rows in set (0.00 sec)

如果想要彻底结束主从复制关系，可以

# 在从数据库上执行，清除该从服务器和主服务器之间的关系
stop slave;
reset slave all;

# 如果主服务器也不维护关系了，则在主服务器上执行
reset master;

MySQL主主（互为主从）

以下环境是以MySQL主从复制为基础的环境下部署的

	MySQL1	MySQL2
IP地址	192.168.1.11	192.168.1.22

MySQL1

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin       # 修改为MySQL-bin
log-slave-updates=true
server-id=1
auto-increment-increment=10     # 自增长为10，也就是递增10
auto-increment-offset=1     # 从1开始，也就是全是奇数ID
binlog-do-db=shuaiguoer
replicate-do-db=shuaiguoer
log_slave_updates=on

重启MySQL数据库

[root@localhost ~]# systemctl restart mariadb

授权给MySQL2

# MySQL1到MySQL2的主从（其实上面的MySQL主从复制环境已经授权了）
MariaDB [(none)]> grant replication slave on *.* to "myslave"@"192.168.1.22" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

# 刷新
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

查看master最新状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 | shuaiguoer   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MySQL2

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin       # 修改为MySQL-bin
auto-increment-increment=10     # 自增长为10，也就是递增10
auto-increment-offset=2     # 从2开始，也就是插入偶数ID
binlog-do-db=shuaiguoer
replicate-do-db=shuaiguoer
log-slave-updates=true
log_slave_updates=on

重启MySQL数据库

[root@localhost ~]# systemctl restart mariadb

停止slave

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

指定master

# MySQL2指定MySQL1为master
MariaDB [(none)]> change master to master_host="192.168.1.11", master_user="myslave", master_password="123456", master_log_file="mysql-bin.000001", master_log_pos=245;
Query OK, 0 rows affected (0.05 sec)

启动slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: shuaiguoer
          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: 245
              Relay_Log_Space: 825
              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: 1
1 row in set (0.00 sec)

授权给MySQL1

MariaDB [(none)]> grant replication slave on *.* to "myslave2"@"192.168.1.11" identified by "123456";
Query OK, 0 rows affected (0.00 sec)
# 刷新
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看master状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      970 | shuaiguoer   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

切换回MySQL1

指定master

# MySQL1指定MySQL2为master
MariaDB [(none)]> change master to master_host="192.168.1.22", master_user="myslave2", master_password="123456", master_log_file="mysql-bin.000001", master_log_pos=970;
Query OK, 0 rows affected (0.04 sec)

启动slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.22
                  Master_User: myslave2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 970
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: shuaiguoer
          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: 970
              Relay_Log_Space: 825
              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: 2
1 row in set (0.00 sec)

测试主主复制

切换到MySQL2

# 查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shuaiguoer         |
| test               |
+--------------------+
5 rows in set (0.00 sec)

# 进入 shuaiguoer 数据库
MariaDB [(none)]> use shuaiguoer
Database changed

# 创建名为 demo 的表
MariaDB [shuaiguoer]> create table demo(id int(6),name varchar(15));
Query OK, 0 rows affected (0.00 sec)

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

在切换回MySQL1

# 进入 shuaiguoer 数据库
MariaDB [shuaiguoer]> use shuaiguoer;
Database changed

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

# 想 demo 表里插入值
MariaDB [shuaiguoer]> insert into demo values(666,'shuai');
Query OK, 1 row affected (0.01 sec)

# 查询表内容
MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
+------+-------+
1 row in set (0.00 sec)

再次切换到MySQL2

MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
+------+-------+
1 row in set (0.00 sec)

MySQL1可以查看到MySQL创建的表，MySQL2可以查询到MySQL1插入的值，所以MySQL已经互为主从。

MySQL一主多从

	master	slave1	slave2
IP地址	192.168.1.11	192.168.1.22	192.168.1.33

master端

授权slave

MariaDB [(none)]> grant replication slave on *.* to "myslave"@"192.168.1.33" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

查看master最新状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      245 | shuaiguoer   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

slave端

指定master

MariaDB [(none)]> change master to master_host="192.168.1.11", master_user="myslave", master_password="123456", master_log_file="mysql-bin.000002", master_log_pos=245;
Query OK, 0 rows affected (0.07 sec)

启动slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 486
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 770
        Relay_Master_Log_File: mysql-bin.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: 486
              Relay_Log_Space: 1066
              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: 1
1 row in set (0.00 sec)

测试一主多从同步复制

master

备份 shuaiguoer 数据库

[root@localhost ~]# mysqldump -uroot -p123456 shuaiguoer > /root/shuaiguoer.sql

远程拷贝到slave2上面

[root@localhost ~]# scp shuaiguoer.sql 192.168.1.33:/root/
root@192.168.1.33's password:
shuaiguoer.sql                                               100% 1841   120.6KB/s   00:00

slave2

创建 shuaiguoer 数据库

MariaDB [(none)]> create database shuaiguoer;
Query OK, 1 row affected (0.00 sec)

导入数据

MariaDB [(none)]> use shuaiguoer
Database changed
MariaDB [shuaiguoer]> source /root/shuaiguoer.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

# 查询表内容
MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
+------+-------+
1 row in set (0.01 sec)

master

插入数据

# 进入数据库
MariaDB [(none)]> use shuaiguoer
Database changed

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

# 插入数据
MariaDB [shuaiguoer]> insert into demo values(888,'guoer');
Query OK, 1 row affected (0.01 sec)

# 查询数据
MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
|  888 | guoer |
+------+-------+
2 rows in set (0.05 sec)

slave2

查询数据

MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
|  888 | guoer |
+------+-------+
2 rows in set (0.00 sec)

slave2表内的数据和master插入的数据一致，说明MySQL一主多从已同步复制。

mysql主从同步数据复制模式

复制模式介绍

异步复制( Asynchronous replication )
主库在执行完客户端提交的事务后会立即将结果返给客户端,并不关心从库是否已经接收并处理。
全同步复制( Fully synchronous replication )
当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。
半同步复制( Semisynchronous replication )
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到 relay log 中才返回给客户端

配置半同步复制

查看当前的数据库服务器是否支持动态加载模块

MariaDB [shuaiguoer]> show variables  like  'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.02 sec)

使用命令安装模块

主库半同步复制模块

MariaDB [shuaiguoer]> install plugin  rpl_semi_sync_master  soname  'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)

# 查看模块状态
MariaDB [(none)]> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
2 rows in set (0.00 sec)

从库半同步复制模块

MariaDB [shuaiguoer]> install plugin  rpl_semi_sync_slave  soname  'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)

# 查看模块状态
MariaDB [shuaiguoer]> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+---------------------+---------------+
| plugin_name         | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.01 sec)

启用半同步复制

# 在安装完插件后，半同步复制默认是关闭的
主：riaDB [shuaiguoer]> set global  rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

# 查看是否启用成功
MariaDB [shuaiguoer]> show  variables like 'rpl_semi_sync_%_enabled';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON    |
+------------------------------+-------+
1 row in set (0.00 sec)

从：riaDB [shuaiguoer]> set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

# 查看是否启用成功
MariaDB [shuaiguoer]> show  variables like 'rpl_semi_sync_%_enabled';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| rpl_semi_sync_slave_enabled | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

把配置写进配置文件使其永久生效

写在主配置文件 /etc/my.cnf 的 [mysqld] 下方
重启MySQL服务

Master

[root@localhost ~]# vim /etc/my.cnf
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

[root@localhost ~]# systemctl restart mariadb

slave

[root@localhost ~]# vim /etc/my.cnf
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

[root@localhost ~]# systemctl restart mariadb

在有的高可用架构下, master 和 slave 需同时启动

以便在切换后能继续使用半同步复制

[root@localhost ~]# vim /etc/my.cnf
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
[root@localhost ~]# systemctl restart mariadb

# 查看是否启用成功
MariaDB [(none)]> 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)

Last modification：July 24th, 2020 at 12:13 am

如果觉得我的文章对你有用，请随意赞赏

Mysql主从复制

admin • 2018 年 09 月 21 日

Mysql主从简介

mysql的主从复制，是用来建立一个和主数据库完全一样的数据库环境，
从库会同步主库得所有数据，可轻松实现故障转移。

MySQL主从主要作用

实现数据库的备份；
基于数据库备份，可以实现故障转移；
基于数据库备份，可以实现读写分离；

MySQL主从工作原理

MySQL主从配置选项

Master
写在主库配置文件里参数（对所有从库都有效）

应包括希望同步- 的所有库
对采用 MyISAM 的库,可离线备份

选项	用途
binlog_do_db=name	设置 Master 对哪些库记日志
binlog_ignore_db=nane	设置 Master 对哪些库不计日志

Slave
写在从库配置文件里参数（只针对从库本机有效）

离线导入由 Master 提供的备份
清空同名库(若有的话

选项	用途
log_slave_updates	记录从库更新，允许链式复制（A-B-C）
relay_log=dbsvr2-relay-bin	指定中继日志文件名
replicate_do_db=mysql	仅复制指定库，其他库将被忽略，此选项可设置多条（省略时复制所有库）
replicate_ignore_db=test	不复制指定库，其他库将被忽略（ignore与do选项只需选用其中一种）

slave相关文件

文件名	说明
master.info	主库信息
relay-log.info	中继日志信息
主机名-relay-bin.xxxxxx	中继信息
主机名-relay-bin.index	索引文件

MySQL一主一从

mysql主从同步配置步骤

配置主库
启用binlog日志
用户授权
查看当前正在使用的binlog日志
配置从库
指定server_id
指定主库信息
启动slave程序
查看slave程序的状态信息

	主	从
IP地址	192.168.1.11	192.168.1.22

master端

安装MySQL

[root@localhost ~]# yum -y install mariadb mariadb-server

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]        # 在[mysqld]字段下，添加以下行
log-bin=node4-bin
log-slave-updates=true
server-id=1

启动MySQL

[root@localhost ~]# systemctl start mariadb

检查MySQL运行状态

# 检查MySQL进程
[root@localhost ~]# ps -ef | grep mysql
mysql     14832      1  0 10:52 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     15018  14832  1 10:52 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      15068   8998  0 10:52 pts/0    00:00:00 grep --color=auto mysql

# 检查MySQL端口
[root@localhost ~]# netstat -anpt | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      15018/mysqld

设置MySQL登录密码并登录

# 设置密码
[root@localhost ~]# mysqladmin -uroot -p password "123456"
# 登录数据库
[root@localhost ~]# mysql -uroot -p123456

验证二进制日志功能是否开启

MariaDB [(none)]> show variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
| sql_log_bin   | ON    |
+---------------+-------+
2 rows in set (0.00 sec)

授权给slave

MariaDB [(none)]> grant replication slave on *.* to "myslave"@"192.168.1.22" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

# 刷新
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

查看master最新状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| node4-bin.000001 |      401 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

slave端

安装MySQL

[root@localhost ~]# yum -y install mariadb mariadb-server

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]        # 在[mysqld]字段下，添加以下行
server-id=2

启动MySQL

[root@localhost ~]# systemctl start mariadb

设置MySQL登录密码并登录

# 设置密码
[root@localhost ~]# mysqladmin -uroot -p password "123456"
# 登录数据库
[root@localhost ~]# mysql -uroot -p123456

指定master

MariaDB [(none)]> change master to master_host="192.168.1.11", master_user="myslave", master_password="123456", master_log_file="node4-bin.000001", master_log_pos=401;
Query OK, 0 rows affected (0.07 sec)

启动slave

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: node4-bin.000001
          Read_Master_Log_Pos: 401
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: node4-bin.000001
             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: 539
              Relay_Log_Space: 245
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'myslave@192.168.1.11:3306' - retry-time: 60  retries: 86400  message: Can't connect to MySQL server on '192.168.1.11' (113)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

发现错误：Slave_IO_Running: Connecting

解决思路：

导致lave_IO_Running 为connecting 的原因主要有以下 3 个方面：

1、网络不通
2、密码不对
3、pos不对

[root@localhost ~]# systemctl stop firewalld.service

再次查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: node4-bin.000001
          Read_Master_Log_Pos: 401
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: node4-bin.000001
             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: 340
              Relay_Log_Space: 825
              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: 1
1 row in set (0.00 sec)

如果遇到的问题是：Slave_SQL_Running：no

MariaDB [(none)]> stop slave;
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;     # 让mysql跳过一次错误，继续执行
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

测试一主一从同步复制

在master创建数据库shuaiguoer

MariaDB [(none)]> create database shuaiguoer;
Query OK, 1 row affected (0.01 sec)

在slave查看数据库是否已复制

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shuaiguoer         |
| test               |
+--------------------+
5 rows in set (0.00 sec)

如果想要彻底结束主从复制关系，可以

# 在从数据库上执行，清除该从服务器和主服务器之间的关系
stop slave;
reset slave all;

# 如果主服务器也不维护关系了，则在主服务器上执行
reset master;

MySQL主主（互为主从）

以下环境是以MySQL主从复制为基础的环境下部署的

	MySQL1	MySQL2
IP地址	192.168.1.11	192.168.1.22

MySQL1

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin       # 修改为MySQL-bin
log-slave-updates=true
server-id=1
auto-increment-increment=10     # 自增长为10，也就是递增10
auto-increment-offset=1     # 从1开始，也就是全是奇数ID
binlog-do-db=shuaiguoer
replicate-do-db=shuaiguoer
log_slave_updates=on

重启MySQL数据库

[root@localhost ~]# systemctl restart mariadb

授权给MySQL2

# MySQL1到MySQL2的主从（其实上面的MySQL主从复制环境已经授权了）
MariaDB [(none)]> grant replication slave on *.* to "myslave"@"192.168.1.22" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

# 刷新
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

查看master最新状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 | shuaiguoer   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MySQL2

修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin       # 修改为MySQL-bin
auto-increment-increment=10     # 自增长为10，也就是递增10
auto-increment-offset=2     # 从2开始，也就是插入偶数ID
binlog-do-db=shuaiguoer
replicate-do-db=shuaiguoer
log-slave-updates=true
log_slave_updates=on

重启MySQL数据库

[root@localhost ~]# systemctl restart mariadb

停止slave

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

指定master

# MySQL2指定MySQL1为master
MariaDB [(none)]> change master to master_host="192.168.1.11", master_user="myslave", master_password="123456", master_log_file="mysql-bin.000001", master_log_pos=245;
Query OK, 0 rows affected (0.05 sec)

启动slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: shuaiguoer
          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: 245
              Relay_Log_Space: 825
              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: 1
1 row in set (0.00 sec)

授权给MySQL1

MariaDB [(none)]> grant replication slave on *.* to "myslave2"@"192.168.1.11" identified by "123456";
Query OK, 0 rows affected (0.00 sec)
# 刷新
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看master状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      970 | shuaiguoer   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

切换回MySQL1

指定master

# MySQL1指定MySQL2为master
MariaDB [(none)]> change master to master_host="192.168.1.22", master_user="myslave2", master_password="123456", master_log_file="mysql-bin.000001", master_log_pos=970;
Query OK, 0 rows affected (0.04 sec)

启动slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.22
                  Master_User: myslave2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 970
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: shuaiguoer
          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: 970
              Relay_Log_Space: 825
              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: 2
1 row in set (0.00 sec)

测试主主复制

切换到MySQL2

# 查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shuaiguoer         |
| test               |
+--------------------+
5 rows in set (0.00 sec)

# 进入 shuaiguoer 数据库
MariaDB [(none)]> use shuaiguoer
Database changed

# 创建名为 demo 的表
MariaDB [shuaiguoer]> create table demo(id int(6),name varchar(15));
Query OK, 0 rows affected (0.00 sec)

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

在切换回MySQL1

# 进入 shuaiguoer 数据库
MariaDB [shuaiguoer]> use shuaiguoer;
Database changed

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

# 想 demo 表里插入值
MariaDB [shuaiguoer]> insert into demo values(666,'shuai');
Query OK, 1 row affected (0.01 sec)

# 查询表内容
MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
+------+-------+
1 row in set (0.00 sec)

再次切换到MySQL2

MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
+------+-------+
1 row in set (0.00 sec)

MySQL1可以查看到MySQL创建的表，MySQL2可以查询到MySQL1插入的值，所以MySQL已经互为主从。

MySQL一主多从

	master	slave1	slave2
IP地址	192.168.1.11	192.168.1.22	192.168.1.33

master端

授权slave

MariaDB [(none)]> grant replication slave on *.* to "myslave"@"192.168.1.33" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

查看master最新状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      245 | shuaiguoer   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

slave端

指定master

MariaDB [(none)]> change master to master_host="192.168.1.11", master_user="myslave", master_password="123456", master_log_file="mysql-bin.000002", master_log_pos=245;
Query OK, 0 rows affected (0.07 sec)

启动slave

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 486
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 770
        Relay_Master_Log_File: mysql-bin.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: 486
              Relay_Log_Space: 1066
              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: 1
1 row in set (0.00 sec)

测试一主多从同步复制

master

备份 shuaiguoer 数据库

[root@localhost ~]# mysqldump -uroot -p123456 shuaiguoer > /root/shuaiguoer.sql

远程拷贝到slave2上面

[root@localhost ~]# scp shuaiguoer.sql 192.168.1.33:/root/
root@192.168.1.33's password:
shuaiguoer.sql                                               100% 1841   120.6KB/s   00:00

slave2

创建 shuaiguoer 数据库

MariaDB [(none)]> create database shuaiguoer;
Query OK, 1 row affected (0.00 sec)

导入数据

MariaDB [(none)]> use shuaiguoer
Database changed
MariaDB [shuaiguoer]> source /root/shuaiguoer.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

# 查询表内容
MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
+------+-------+
1 row in set (0.01 sec)

master

插入数据

# 进入数据库
MariaDB [(none)]> use shuaiguoer
Database changed

# 查看表
MariaDB [shuaiguoer]> show tables;
+----------------------+
| Tables_in_shuaiguoer |
+----------------------+
| demo                 |
+----------------------+
1 row in set (0.00 sec)

# 插入数据
MariaDB [shuaiguoer]> insert into demo values(888,'guoer');
Query OK, 1 row affected (0.01 sec)

# 查询数据
MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
|  888 | guoer |
+------+-------+
2 rows in set (0.05 sec)

slave2

查询数据

MariaDB [shuaiguoer]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|  666 | shuai |
|  888 | guoer |
+------+-------+
2 rows in set (0.00 sec)

slave2表内的数据和master插入的数据一致，说明MySQL一主多从已同步复制。

mysql主从同步数据复制模式

复制模式介绍

异步复制( Asynchronous replication )
主库在执行完客户端提交的事务后会立即将结果返给客户端,并不关心从库是否已经接收并处理。
全同步复制( Fully synchronous replication )
当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。
半同步复制( Semisynchronous replication )
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到 relay log 中才返回给客户端

配置半同步复制

查看当前的数据库服务器是否支持动态加载模块

MariaDB [shuaiguoer]> show variables  like  'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.02 sec)

使用命令安装模块

主库半同步复制模块

MariaDB [shuaiguoer]> install plugin  rpl_semi_sync_master  soname  'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)

# 查看模块状态
MariaDB [(none)]> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
2 rows in set (0.00 sec)

从库半同步复制模块

MariaDB [shuaiguoer]> install plugin  rpl_semi_sync_slave  soname  'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)

# 查看模块状态
MariaDB [shuaiguoer]> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+---------------------+---------------+
| plugin_name         | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.01 sec)

启用半同步复制

# 在安装完插件后，半同步复制默认是关闭的
主：riaDB [shuaiguoer]> set global  rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

# 查看是否启用成功
MariaDB [shuaiguoer]> show  variables like 'rpl_semi_sync_%_enabled';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON    |
+------------------------------+-------+
1 row in set (0.00 sec)

从：riaDB [shuaiguoer]> set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

# 查看是否启用成功
MariaDB [shuaiguoer]> show  variables like 'rpl_semi_sync_%_enabled';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| rpl_semi_sync_slave_enabled | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

把配置写进配置文件使其永久生效

写在主配置文件 /etc/my.cnf 的 [mysqld] 下方
重启MySQL服务

Master

[root@localhost ~]# vim /etc/my.cnf
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

[root@localhost ~]# systemctl restart mariadb

slave

[root@localhost ~]# vim /etc/my.cnf
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

[root@localhost ~]# systemctl restart mariadb

在有的高可用架构下, master 和 slave 需同时启动

以便在切换后能继续使用半同步复制

[root@localhost ~]# vim /etc/my.cnf
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
[root@localhost ~]# systemctl restart mariadb

# 查看是否启用成功
MariaDB [(none)]> 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)

Mysql主从简介

MySQL主从主要作用

MySQL主从工作原理

MySQL主从配置选项

MySQL一主一从

mysql主从同步配置步骤

master端

安装MySQL

修改MySQL配置文件

启动MySQL

检查MySQL运行状态

设置MySQL登录密码并登录

验证二进制日志功能是否开启

授权给slave

查看master最新状态

slave端

安装MySQL

修改MySQL配置文件

启动MySQL

设置MySQL登录密码并登录

指定master

启动slave

查看同步状态

发现错误：Slave_IO_Running: Connecting

再次查看同步状态

测试一主一从同步复制

如果想要彻底结束主从复制关系，可以

MySQL主主（互为主从）

MySQL1

修改MySQL配置文件

重启MySQL数据库

授权给MySQL2

查看master最新状态

MySQL2

修改MySQL配置文件

重启MySQL数据库

停止slave

指定master

启动slave

查看同步状态

授权给MySQL1

查看master状态

切换回MySQL1

指定master

启动slave

查看同步状态

测试主主复制

MySQL一主多从

master端

授权slave

查看master最新状态

slave端

指定master

启动slave

查看同步状态

测试一主多从同步复制

备份 shuaiguoer 数据库

远程拷贝到slave2上面

创建 shuaiguoer 数据库

导入数据

插入数据

查询数据

mysql主从同步数据复制模式

复制模式介绍

配置半同步复制

查看当前的数据库服务器是否支持动态加载模块

使用命令安装模块

启用半同步复制

把配置写进配置文件使其永久生效