多种MySQL复制方法详解

本文主要给大家介绍多种MySQL复制方法详解，其所涉及的东西，从理论知识来获悉，有很多书籍、文献可供大家参考，从现实意义角度出发，累计多年的实践经验可分享给大家。

MySQL复制主要是把写操作发送给主节点、读操作发送给从节点，每个节点都有相关的数据集；从服务专门启动一个线程，把自己扮演成为客户端，通过MySQL协议向MySQL主节点请求读取二进制日志文件中的事件，随后主节点将会检查自己二进制日志中的事件并发送给从节点，从节点收到后先保存在自己的中继日志中，每读取到一个事件都会在中继中保存下来

复制中用到的线程名称：

从节点：

I/O Thread：从主节点请求二进制事件，并保存于中继日志中

SQL Thread：从中继日志中读取二进制日志事件，在本地完成重放

主节点：

dump Thread：为每个从节点的I/O Thread启动一个dump Thread，用于向其发送二进制日志事件

复制的功用：

1、实现数据分布的目的

2、主要能够完成写操作的负载均衡效果

3、能够实现备份的效果（当主云服务器挂了，备节点可以当主节点）

4、可以实现高可用和故障切换

5、可以在从节点上为Mysql做升级测试

MySQL复制的方式：主从复制、主主复制、半同步复制、过滤复制、SSL复制

一、实现主从复制：

准备环境

1、部署Master节点

（1）、安装mariadb服务

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

（2）编辑其配置文件

[root@node0 ~]# vim /etc/my.cnf   ##mariadb配置文件路径[mysqld]log_bin=mysql-bin   ##开启二进制日志功能server-id=1   ##定义唯一Server-idinnodb_file_per_table = ONskip_name_resolve = ON   ##禁止反解主机名[root@node0 ~]# systemctl start mariadb.service  ##启动mariadb服务MariaDB [(none)]> show global variables like "server_id";    ##查看其server-idserver_id  1     MariaDB [(none)]> show global variables like "log_bin";  ##查看其二进制日志是否开启log_bin  ON  MariaDB [(none)]> show master status;   ##查看正在使用的二进制日志文件mysql-bin.000002   245           | MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass';   ##创建具有复制权限的用户MariaDB [(none)]> flush privileges;

2、部署Slave节点

（1）安装mariadb服务

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

（2）编辑其配置文件

[root@node1 ~]# vim /etc/my.cnf[mysqld]relay-log=relay-log   ##启用中继日志server-id=2    ##定义唯一Server-id；此Server-id一定不能和Master的上的Server-id相同innodb_file_per_table = ONskip_name_resolve = ON  [root@node1 ~]# systemctl start mariadb.serviceMariaDB [(none)]> show global variables like "relay_log";  ##查看中继日志是否启用relay_log   relay-log  MariaDB [(none)]> show global variables like "server_id";   ##查看其server-iserver_id   2

（3）使用具有复制权限的用户账号连接至主服务器上，并启动复制线程

MariaDB [(none)]> change master to master_host='192.168.1.108',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000002',master_log_pos=245;## master_host：指明主节点的IP地址## master_user、master_password：指明具有复制权限的用户账号## master_log_file：指明主节点正在使用的二进制日志；## master_log_pos：指明二进制日志的positionMariaDB [(none)]> start slave;   ##启动复制线程MariaDB [(none)]> show slave status\G;  ##查看复制线程的状态信息Slave_IO_Running: YesSlave_SQL_Running: Yes

3、测试主从服务器是否实现复制功能

（1）在Master节点上查看一次数据库

MariaDB [(none)]> show databases;+--------------------+| Database           +--------------------+| information_schema | lweim            | mysql              | performance_schema | test               +--------------------+

（2）在Slave上查看一次数据库

MariaDB [(none)]> show databases;+--------------------+| Database           +--------------------+| information_schema | mysql              | performance_schema | testdb               +--------------------+

（3）在Master节点上创建一个名为"MaGeRepo"的数据库，并在Slave节点上查看

MariaDB [(none)]> create database MaGeRepo;  ##在主节点上创建数据库"MaGeRepo"Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases;   ##在从节点上查看数据库+--------------------+| Database           +--------------------+| information_schema | MaGeRepo                         | mysql              | performance_schema | testdb               +--------------------

在主从复制架构中应该注意的问题：

1、在Master节点上的参数

sync_binlog=ONsync_master_info=ON如果用的是InnoDB存储引擎，以下两项也要开启innodb_flush_log_at_tx_commit = ON   ##刷写日志innodb_support_xa = ON   ##是否让innodb支持分布式事务

2、在Slave节点上的参数

skip_slave_start = OFF  ##是否自动启动事务线程sync_relay_log = ON   sync_relay_log_info = ON

二、实现主主复制

准备环境

1、部署虚拟机1

（1）安装mariadb服务

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

（2）编辑其配置文件

[root@node0 ~]# vim /etc/my.cnflog_bin=mysql-binrelay-log=relay-log   ##启用中继日志server-id=1innodb_file_per_table = ON[root@node0 ~]# systemctl start mariadb.serviceMariaDB [(none)]> show global variables like "%log%";relay_log  relay-log log_bin   ON     MariaDB [(none)]> show master status;mysql-bin.000003    245              MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass';

2、部署虚拟机2

（1）安装mariadb服务

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

（2）编辑配置文件

[root@node1 ~]# vim /etc/my.cnf[mysqld]relay-log=relay-loglog_bin=mysql-bin  ##启用二进制日志  server-id=2innodb_file_per_table = ONskip_name_resolve = ON[root@node1 ~]# systemctl start mariadb.serviceMariaDB [(none)]> show master status;mysql-bin.000003   245                                 MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass';   ##创建具有复制权限的用户账号

3、两个节点分别使用双方创建的用户连到对方的服务器上，并启动复制线程

（1）让虚拟机1连接至虚拟机2

MariaDB [(none)]> change master to master_host='192.168.1.109',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=245;MariaDB [(none)]> flush privileges;MariaDB [(none)]> start slave;

（2）让虚拟机2连接至虚拟机1

MariaDB [(none)]> change master to master_host='192.168.1.108',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=245;MariaDB [(none)]> flush privileges;MariaDB [(none)]> start slave;

4、测试主主复制模型

（1）在分别查看虚拟机1和虚拟机2的数据库

MariaDB [(none)]> show databases;  ##查看虚拟机1的数据库+--------------------| Database           +--------------------+| information_schema | mysql              | performance_schema | test              +--------------------+MariaDB [(none)]> show databases;  ##查看虚拟机2的数据库+--------------------+ | Database           +--------------------+| information_schema | mysql              | performance_schema | testdb              +--------------------+

（2）在虚拟机1上添加"LweimRepo"数据库，在虚拟机2上添加"WzxRepo"数据库

MariaDB [(none)]> create database LweimRepo;   ##虚拟机1Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> flush privileges;MariaDB [(none)]> create database WzxRepo;    ##虚拟机2Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> flush privileges;

（3）再次查看两个节点的数据库

MariaDB [(none)]> show databases;   ##查看虚拟机1+--------------------+| Database           +--------------------+| information_schema | LweimRepo          | WzxRepo            | mysql              | performance_schema | test               +--------------------+MariaDB [(none)]> show databases;   ##查看虚拟机2+--------------------+| Database           +--------------------+| information_schema | LweimRepo          | WzxRepo            | mysql              | performance_schema | testdb              +--------------------+

三、实现半同步复制：当master节点有多个slave节点时，只需要有一个slave节点复制完成之后并响应给master节点，随后master节点响应客户端

准备环境

1、给master节点安装插件"semisync_master.so"

MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';MariaDB [(none)]> show global variables like "%semi%";+------------------------------------+-------+| Variable_name            Value +------------------------------------+-------+| rpl_semi_sync_master_enabled     OFF     ##是否启用半同步复制的主节点，需把它设置为ON| rpl_semi_sync_master_timeout     10000    ##等待slave的响应时长，单位为毫秒，默认为10s| rpl_semi_sync_master_trace_level   32       ##跟踪节点32，默认值就好，不建议更改| rpl_semi_sync_master_wait_no_slave   ON       ##当没有slave节点时是否等待+------------------------------------+-------+MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1;  ##"rpl_semi_sync_master_enabled"更改为"ON"

2、在Slave节点上安装"semisync_slave.so"

MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;  ##启用半同步复制的slave节点MariaDB [(none)]> show global variables like "%semi%";+---------------------------------+-------+| Variable_name           Value +---------------------------------+-------+| rpl_semi_sync_slave_enabled    ON     ##是否启用半同步复制的slav节点| rpl_semi_sync_slave_trace_level   32    +---------------------------------+-------+

3、实现主从复制

4、在Master节点上查看是否增加slave节点

MariaDB [(none)]> show global status like "%semi%";+--------------------------------------------+-------+| Variable_name                Value +--------------------------------------------+-------+| Rpl_semi_sync_master_clients         1      ##已经连接Slave节点的个数| Rpl_semi_sync_master_net_avg_wait_time    0     | Rpl_semi_sync_master_net_wait_time       0     | Rpl_semi_sync_master_net_waits         0     | Rpl_semi_sync_master_no_times          1    | Rpl_semi_sync_master_no_tx          2     | Rpl_semi_sync_master_status           ON   | Rpl_semi_sync_master_timefunc_failures    0     | Rpl_semi_sync_master_tx_avg_wait_time      0     | Rpl_semi_sync_master_tx_wait_time        0     | Rpl_semi_sync_master_tx_waits         0     | Rpl_semi_sync_master_wait_pos_backtraverse   0     | Rpl_semi_sync_master_wait_sessions      0      | Rpl_semi_sync_master_yes_tx           0     +--------------------------------------------+-------

5、在Master节点上创建数据库"GunDuZi，并在Slave节点上查看

MariaDB [(none)]> create database GunDuZi;MariaDB [(none)]> show global status like "%semi%";+--------------------------------------------+-------+| Variable_name                Value +--------------------------------------------+-------+| Rpl_semi_sync_master_clients         1     | Rpl_semi_sync_master_net_avg_wait_time    8147  | Rpl_semi_sync_master_net_wait_time      8147  | Rpl_semi_sync_master_net_waits        1     | Rpl_semi_sync_master_no_times         1     | Rpl_semi_sync_master_no_tx          2     | Rpl_semi_sync_master_status          ON    | Rpl_semi_sync_master_timefunc_failures    0     | Rpl_semi_sync_master_tx_avg_wait_time    9388  | Rpl_semi_sync_master_tx_wait_time      9388  | Rpl_semi_sync_master_tx_waits          1     | Rpl_semi_sync_master_wait_pos_backtraverse   0     | Rpl_semi_sync_master_wait_sessions       0     | Rpl_semi_sync_master_yes_tx           1     +--------------------------------------------+-------MariaDB [(none)]> show databases;+--------------------+| Database           +--------------------+| information_schema | GunDuZi            | mysql              | performance_schema | test               +--------------------+##注意：一定要先安装插件，在实现主从复制

四、实现过滤复制：让从节点指定复制数据库或复制数据库中指定的表

准备环境

1、实现主从复制

2、指定Slave节点需要复制的数据库为"wtcdb"

MariaDB [wxpp]> set global replicate_do_db=wtcdb;MariaDB [wxpp]> show global variables like "replicate%";+----------------------------------+-----------+| Variable_name           Value     +----------------------------------+-----------+| replicate_annotate_row_events    OFF       | replicate_do_db           wtcdb   ##仅复制那些数据库   | replicate_do_table   ##仅复制那些数据库中的表| replicate_events_marked_for_skip  replicate | replicate_ignore_db     ##忽略的数据库（不复制的数据库）| replicate_ignore_table   ##忽略的表（不复制的表）| replicate_wild_do_table   ##可以使用通配符来指定需要复制的数据库| replicate_wild_ignore_table              +----------------------------------+-----------+

3、查看Slave节点上的数据库

MariaDB [(none)]> show databases;+--------------------+| Database           +--------------------+| information_schema | mysql              | performance_schema | test               | wxpp               +--------------------+

4、在Master节点上创建"lweimdb"、"wzxdb"、"wtcdb"三个数据库

MariaDB [(none)]> create database lweimdb;Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> create database wzxdb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create database wtcdb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show databases;+--------------------+| Database           +--------------------+| information_schema | lweimdb            | mysql              | performance_schema | test               | wtcdb              | wxpp               | wzxdb              +--------------------+

5、再一次查看Slave节点上的数据库，是否只复制了"wtcdb"数据库

MariaDB [(none)]> show databases;+--------------------+| Database           +--------------------+| information_schema | mysql             | performance_schema | test               | wtcdb              | wxpp               +--------------------+

五、实现SSL复制

准备环境

1、部署虚拟机3

（1）创建CA证书秘钥

[root@localhost CA]# (umask 077;openssl genrsa -out ./private/cakey.pem 1024)

（2）让CA自签证书

[root@localhost CA]# openssl req -new -x509 -key ./private/cakey.pem -out cacert.pem -days 365[root@localhost CA]# touch serial index.txt  ##创建所需要的文件[root@localhost CA]# echo 01 > serial   ##生成证书序列号

2、Master节点创建证书，并让CA机构签署

[root@node0 ssl]# (umask 077; openssl genrsa -out master.key 1024)  ##Master节点生成秘钥[root@node0 ssl]# openssl req -new -key master.key -out master.csr -days 365  ##生成需要签署的证书[root@localhost CA]# openssl ca -in /tmp/master.csr -out master.crt -days 365 ##让CA签署证书[root@node0 ssl]# chmod 600 *   ##更改权限为600[root@node0 ssl]# chown mysql.mysql -R ssl/*   ##更改文件属主属组为mysql[root@node0 ssl]# lltotal 12-rw------- 1 mysql mysql 1046 Jun  9 20:49 cacert.pem-rw------- 1 mysql mysql 3202 Jun  9 20:43 master.crt-rw------- 1 mysql mysql  887 Jun  9 20:35 master.key

3、配置Master节点的配置文件，并创建具有复制权限的用户

[root@node0 ~]# vim /etc/my.cnfssl     ##开启sll功能ssl_ca=/var/lib/mysql/ssl/cacert.pem    ##指明CA机构证书路径ssl_cert=/var/lib/mysql/ssl/master.crt  ##指明Master节点的证书路径ssl_key=/var/lib/mysql/ssl/master.key   ##指明Master节点的秘钥路径[root@node0 ~]# systemctl start mariadb.serviceMariaDB [(none)]> show global variables like "%ssl%";  ##查看是否启用ssl+---------------+-------------------------------+| Variable_name | Value                         +---------------+-------------------------------+| have_openssl  | YES                           | have_ssl      | YES                           | ssl_ca        | /var/lib/mysql/ssl/cacert.pem | ssl_capath                                  | ssl_cert      | /var/lib/mysql/ssl/master.crt | ssl_cipher                                  | ssl_key       | /var/lib/mysql/ssl/master.key +---------------+-------------------------------+MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass' require ssl;

4、将Master节点上的证书发送给Slave，修改其配置文件，连接至Master节点并启动复制线程

[root@node0 ~]# scp ssl/* root@192.168.1.109:/var/lib/mysql/   ##要确保发送的文件属组属主为"mysql"root@192.168.1.109's password: cacert.pem      100% 1046     1.0KB/s   00:00    master.crt      100% 3206     3.1KB/s   00:00     master.key      100%  887      0.9KB/s   00:00    [root@node1 ~]# vim /etc/my.cnf   ssl    ssl_ca=/var/lib/mysql/cacert.pem   ssl_cert=/var/lib/mysql/master.crtssl_key=/var/lib/mysql/master.key[root@node1 ~]# systemctl start mariadb.serviceMariaDB [(none)]> show global variables like '%ssl%';    ##从节点上的SSL的功能已经开启+---------------+---------------------------+| Variable_name  | Value                     +---------------+---------------------------+| have_openssl   | YES                       | have_ssl       | YES                       | ssl_ca         | /var/lib/mysql/cacert.pem | ssl_capath     |                           | ssl_cert       | /var/lib/mysql/master.crt | ssl_cipher     |                          | ssl_key        | /var/lib/mysql/master.key +---------------+---------------------------+MariaDB [(none)]> change master to master_host='192.168.1.108',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000003',master_log_pos=245,master_ssl=1,master_ssl_ca='/var/lib/mysql/cacert.pem',master_ssl_cert='/var/lib/mysql/master.crt',master_ssl_key='/var/lib/mysql/master.key';## master_ssl：启用ssl功能## master_ssl_ca：指明CA证书路径## master_ssl_cert：指明Master节点证书路径## master_ssl_key：指明Master节点秘钥路径MariaDB [(none)]> start slave;  ##启动复制线程MariaDB [(none)]> show slave status\G； ##查看复制线程相关信息Slave_IO_Running: YesSlave_SQL_Running: YesMaster_SSL_Allowed: Yes   ##要确保这三项都为"YES"Master_SSL_CA_File: /var/lib/mysql/cacert.pemMaster_SSL_Cert: /var/lib/mysql/master.crtMaster_SSL_Key: /var/lib/mysql/master.key

5、分别查看Master节点和Slave节点上的数据库

MariaDB [(none)]> show databases;  ##查看Master节点+--------------------+| Database           +--------------------+| information_schema | mysql              | performance_schema | ssl                | test               +--------------------+MariaDB [(none)]> show databases;   ##查看Slave节点+--------------------+| Database           +--------------------+| information_schema | mysql              | performance_schema | test               +--------------------+

6、在Master节点上创建"LweimRepo"、"HjRepo"、"WzxRepo"，随后再到Slave节点上查看

MariaDB [(none)]> create database LweimRepo;Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create database HjRepo;Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create database WzxRepo;Query OK, 1 row affected (0.04 sec)MariaDB [(none)]> show databases;   ##在Slave节点上查看+--------------------+| Database           +--------------------+| information_schema | HjRepo             | LweimRepo          | WzxRepo            | mysql              | performance_schema | test               +--------------------+

问题小结：

1、当Slave节点连接至Master节点时，一定要指明Master节点当前使用的二进制日志以及pos

2、在使用SSL复制时，要确保秘钥、证书文件的权限以及属主属组

3、当生成证书时，三台虚拟机的国家、省份、公司名称必须要一样

看了以上介绍多种MySQL复制方法详解，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，大家可以继续关注行业资讯板块，会定期给大家更新行业新闻和知识，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。