MariaDB数据库主从复制、双主复制、半同步复制、基于SSL的安全复制实现及其功能特性介绍

3t4342

一.复制概述
    MariaDB/MySQL内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将MySQL的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
    请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

1.mysql支持的复制类型：
(1)：基于语句的复制:在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。  
  一旦发现没法精确复制时，会自动选着基于行的复制。   
(2)：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
(3)：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

2.复制解决的问题
MySQL复制技术有以下一些特点：
(1).数据分布 (Data distribution )
(2).负载平衡(load balancing)
(3).备份(Backups)
(4).高可用性和容错行 High availability and failover

3.复制如何工作
整体上来说，复制有3个步骤：   
(1).master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2).slave将master的binary log events拷贝到它的中继日志(relay log)；
(3).slave重做中继日志中的事件，将改变反映它自己的数据。
下图描述了复制的过程,经典的图哦：摘自《高性能MySQL_第3版》这本经典书中的复制那章；
                                 

    该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
    下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
    SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
    此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

二.复制配置过程简介

有两台MySQL数据库服务器node3和node4，node3为主服务器，node4为从服务器，初始状态时，node3和node4中的数据信息相同，当node3中的数据发生变化时，node4也跟着发生相应的变化，使得node3和node4的数据信息同步，达到备份的目的。

要点：
负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

配置主从复制的过程：
1）.主节点操作步骤
（1）、启用二进制日志
（2）、设置一个在当前集群中唯一的server-id；
（3）、创建一个有复制权限（replication slave，replication client）帐号；
2）.slave节点的操作步骤
（1）、启用中继日志；
（2）、设置一个在当前集群中唯一的server-id；
（3）、使用有复制权限的用户帐号连接至主服务器，并启动复制线程；


注意：
（1）、服务器版本：主从服务器版本一致；
如果版本不一致，必须保证从服务器的版本高于主服务器的版本；
（2）、如果mysql数据库的隔离级别为可读，其二进制日志格式尽量使用基于行的；


实验环境：
服务器版本为：
CentOS 6.6 64bit
数据库软件版本为：
mariadb-10.0.12.tar.gz
node3节点的IP地址：172.16.31.20/16
node4节点的IP地址：172.16.31.21/16

数据库主从复制实现

三.编译安装mariadb数据库过程略。
提供的my.cnf如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

[iyunv@node3 ~]# grep -v '^#' /etc/my.cnf [client] port            = 3306 socket          = /tmp/mysql.sock [mysqld] port            = 3306 socket          = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 4 datadir = /mydata/data innodb_file_per_table = on skip_name_resolve = on [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout

我这里提供一个脚本，基本上可以实现mariadb编译安装自动化：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

[iyunv@node3 ~]# cat installmysql.sh #!/bin/bash #install mysql mysql_dir="/usr/local/mysql" mysql_datadir="/mydata/data" mysql_logdir="/mydata/data/" mysql_passwd="oracle" function install_mysql()  { yum groupinstall -y Development Tools yum install -y cmake ncurses-devel openssl-devel openssl cd /root/ useradd -M -s /sbin/nologin mysql mkdir -p $mysql_datadir chown mysql.mysql -R $mysql_datadir tar zxf mariadb-10.0.12.tar.gz cd mariadb-10.0.12 cmake . -DCMAKE_INSTALL_PREFIX=$mysql_dir/ -DMYSQL_DATADIR=$mysql_datadir -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_TCP_PORT=3306 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DCMAKE_THREAD_PREFER_PTHREAD=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DWITH_DEBUG=0 make && make install rm -rf /etc/my.cnf rm -rf /etc/init.d/mysqld cp /root/my.cnf /etc/my.cnf cp support-files/mysql.server /etc/init.d/mysqld chmod 755 /etc/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on chown mysql.mysql -R $mysql_logdir chown mysql.mysql -R $mysql_datadir $mysql_dir/scripts/mysql_install_db --user=mysql --basedir=$mysql_dir --datadir=$mysql_datadir /sbin/service mysqld start echo 'export PATH=$PATH:'$mysql_dir'/bin' >> /etc/profile source /etc/profile $mysql_dir/bin/mysql -e "grant all privileges on *.* to root@'%' identified by '$mysql_passwd' with grant option;" $mysql_dir/bin/mysql -e "flush privileges;" $mysql_dir/bin/mysql -e "delete from mysql.user where password='';" /sbin/service mysqld restart echo "mysql install success!" } install_mysql

将数据库源码包和配置好的my.cnf文件放置在/root目录下后即可进行编译安装；

四.数据库主从复制
1.主节点node3配置

1 2 3 4 5 6 7 8 9

对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值： 配置/etc/my.cnf在[mysqld]这个配置段加入如下内容： [mysqld] #二进制变更日志 log-bin=mysql-bin #二进制日志格式为混合模式 binlog_format=mixed #为主服务器node3的ID值 server-id       = 1

2.从节点node3配置：

1 2 3 4 5 6 7

配置/etc/my.cnf在[mysqld]这个配置段加入如下内容： log-bin=mysql-bin binlog_format=mixed server-id       = 10 relay-log       = relay-bin log_slave_updates       = 1 read_only       = on

配置简要介绍：
server_id是必须的，而且唯一。
slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。
relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。
有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并不是很实用，特别是那些需要在slave上创建表的应用。

3.在Master节点node3的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中
命令如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

[iyunv@node3 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor.  Commands end with ; or g. Your MariaDB connection id is 5 Server version: 10.0.12-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. #建立一个帐户repluser，并且只能允许172.16.0.0这个网段来登陆，密码是replpass。 MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.16.%.%' identified by 'replpass'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> q Bye

4.拷贝数据
（假如是你完全新安装mysql主从服务器，这个一步就不需要。因为新安装的master和slave有相同的数据）
关停Master服务器，将Master中的数据拷贝到从服务器节点中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！

我这里都是全新安装，一切写操作都未进行过；

5.重启mysqld服务器，主从节点都重启；
主节点：

1 2 3 4 5 6 7 8 9 10

[iyunv@node3 ~]# service mysqld restart Shutting down MySQL.                                       [  OK  ] Starting MySQL.                                            [  OK  ] [iyunv@node3 ~]# ls /mydata/data/ aria_log.00000001  multi-master.info  mysql-bin.000005     performance_schema aria_log_control   mysql              mysql-bin.000006     relaylog binlog             mysql-bin.000001   mysql-bin.000007     test ibdata1            mysql-bin.000002   mysql-bin.index ib_logfile0        mysql-bin.000003   node3.stu31.com.err ib_logfile1        mysql-bin.000004   node3.stu31.com.pid

从节点：

1 2 3 4 5 6 7 8 9 10 11

[iyunv@node4 ~]# service mysqld restart Shutting down MySQL...                                     [  OK  ] Starting MySQL.                                            [  OK  ] #数据复制还未发生，所有中继日志还是未出现的； [iyunv@node4 ~]# ls /mydata/data/ aria_log.00000001  multi-master.info  mysql-bin.000005     performance_schema aria_log_control   mysql              mysql-bin.000006     relaylog binlog             mysql-bin.000001   mysql-bin.000007     test ibdata1            mysql-bin.000002   mysql-bin.index ib_logfile0        mysql-bin.000003   node4.stu31.com.err ib_logfile1        mysql-bin.000004   node4.stu31.com.pid

6.启动从节点的中继日志
接下来就是让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。
命令介绍如下：使用help change master to即可查看命令选项

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

CHANGE MASTER TO option [, option] ... option: MASTER_BIND = 'interface_name' | MASTER_HOST = 'host_name'                  主服务器地址 | MASTER_USER = 'user_name'                  有复制权限的用户名 | MASTER_PASSWORD = 'password'               用户密码 | MASTER_PORT = port_num                     主服务器的端口 | MASTER_CONNECT_RETRY = interval            连接重试时间间隔 | MASTER_HEARTBEAT_PERIOD = interval         心跳检测时间间隔 | MASTER_LOG_FILE = 'master_log_name'        主服务器二进制日志文件 | MASTER_LOG_POS = master_log_pos            二进制日志文件中的位置 | RELAY_LOG_FILE = 'relay_log_name'          中继日志文件名称定义         | RELAY_LOG_POS = relay_log_pos              中继日志时间点定义 | MASTER_SSL = {0|1}                         下面都都是跟SSL安全传输相关的 | MASTER_SSL_CA = 'ca_file_name' | MASTER_SSL_CAPATH = 'ca_directory_name' | MASTER_SSL_CERT = 'cert_file_name' | MASTER_SSL_KEY = 'key_file_name' | MASTER_SSL_CIPHER = 'cipher_list' | MASTER_SSL_VERIFY_SERVER_CERT = {0|1} | IGNORE_SERVER_IDS = (server_id_list)

我们需要记录的二进制日志文件的时间点是最后的那个点，我们先去主服务器节点查看哪个时间点，因为我们是新服务器，主节点上没神马重要数据：

1 2 3 4 5 6 7

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

启动从节点的中继日志：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

[iyunv@node4 ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor.  Commands end with ; or g. Your MariaDB connection id is 5 Server version: 10.0.12-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. #查看只读打开与否； MariaDB [(none)]> show global variables like '%read_only%'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | innodb_read_only | OFF   | | read_only        | ON    | | tx_read_only     | OFF   | +------------------+-------+ 3 rows in set (0.01 sec) #启动中继日志的命令如下； MariaDB [(none)]> change master to master_host='172.16.31.20',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000007',master_log_pos=326,master_connect_retry=5,master_heartbeat_period=2; Query OK, 0 rows affected (0.07 sec) #查看从服务器状态； MariaDB [(none)]> show slave statusG *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 172.16.31.20                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 5               Master_Log_File: mysql-bin.000007           Read_Master_Log_Pos: 326                Relay_Log_File: relay-bin.000001                 Relay_Log_Pos: 4         Relay_Master_Log_File: mysql-bin.000007              Slave_IO_Running: No             Slave_SQL_Running: No               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: 326               Relay_Log_Space: 248               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: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 0                Master_SSL_Crl:            Master_SSL_Crlpath:                    Using_Gtid: No                   Gtid_IO_Pos: 1 row in set (0.00 sec) #通过查看从服务器状态，我们知道了从服务器的IO线程和SQL线程还未开启，下面我们就开启从服务器节点的复制进程，实现主从复制； MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) #再次查看从服务器状态，主要关注IO线程和SQL线程的开启状况： MariaDB [(none)]> show slave statusG *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 172.16.31.20                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 5               Master_Log_File: mysql-bin.000007           Read_Master_Log_Pos: 326                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 535         Relay_Master_Log_File: mysql-bin.000007              Slave_IO_Running: Yes         #IOthread是否运行，如果为No代表slave运行不正常             Slave_SQL_Running: Yes   #SQLthread是否运行，如果为No代表slave运行不正常 信息略......         Seconds_Behind_Master: 0

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。测试在下面进行，我们先来看看主从节点的线程的状态；



7.查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接：
在Master节点node3上输入show processlistG;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

MariaDB [(none)]> show processlist G *************************** 1. row ***************************       Id: 5     User: repluser     Host: 172.16.31.21:52875       db: NULL Command: Binlog Dump     Time: 477    State: Master has sent all binlog to slave; waiting for binlog to be updated     Info: NULL Progress: 0.000 *************************** 2. row ***************************       Id: 6     User: root     Host: localhost       db: NULL Command: Query     Time: 0    State: init     Info: show processlist Progress: 0.000 2 rows in set (0.00 sec) 1.row为处理slave的I/O线程的连接。

在Slave服务器节点node4上运行该语句：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

MariaDB [(none)]> show processlist G *************************** 1. row ***************************       Id: 5     User: root     Host: localhost       db: NULL Command: Query     Time: 0    State: init     Info: show processlist Progress: 0.000 *************************** 2. row ***************************       Id: 6     User: system user     Host:       db: NULL Command: Connect     Time: 587    State: Waiting for master to send event     Info: NULL Progress: 0.000 *************************** 3. row ***************************       Id: 7     User: system user     Host:       db: NULL Command: Connect     Time: 587    State: Slave has read all relay log; waiting for the slave I/O thread to update it     Info: NULL Progress: 0.000 3 rows in set (0.00 sec) 2.row为SQL线程状态。 3.row为I/O线程状态。

8.主从复制测试；
我们在主节点node3上进行导入数据库hellodb的操作：

1 2	[iyunv@node3 ~]# mysql -uroot -p < hellodb.sql Enter password:

我这里提供附件了，可以到附件中下载，免费的；
hellodb.rar (1.89 KB, 下载次数: 0)

2015-1-26 08:49 上传

点击文件名下载附件

数据库存储引擎尽量是innodb的哦。


导入完成后我们到主节点查看：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

[iyunv@node3 ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor.  Commands end with ; or g. Your MariaDB connection id is 10 Server version: 10.0.12-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | binlog             | | hellodb            | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+ 7 rows in set (0.03 sec) MariaDB [(none)]> use hellodb Database changed MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | teachers          | | toc               | +-------------------+ 7 rows in set (0.00 sec) MariaDB [hellodb]> q Bye

到从节点查看复制状态，可以看出复制已经进行了；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

MariaDB [(none)]> show slave statusG *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 172.16.31.20                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 5               Master_Log_File: mysql-bin.000007           Read_Master_Log_Pos: 8640                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 8849         Relay_Master_Log_File: mysql-bin.000007              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: 8640               Relay_Log_Space: 9140               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                Master_SSL_Crl:            Master_SSL_Crlpath:                    Using_Gtid: No                   Gtid_IO_Pos: 1 row in set (0.00 sec)

查看从节点的数据库复制完成与否，可以查看出我们导入的数据库是存在的：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | binlog             | | hellodb            | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+ 7 rows in set (0.02 sec) MariaDB [(none)]> use hellodb; Database changed MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | teachers          | | toc               | +-------------------+ 7 rows in set (0.01 sec)

测试复制是成功的；

至此，数据库的主从复制的实验就完成了，下面进行半同步复制操作实验；


五.半同步复制

半同步复制简述
半同步意思：表示Master服务器只需要接收到其中一台Slave的返回信息，就会commit;否则需要等待直到超时时间然后切换成异步再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以在损失很小的性能的前提下提高数据安全性；

1.半同步的开启，只需要在Master与Slave服务器上都安装上半同步的插件并启用即可；而插件在Mysql的安装目录中："/usr/local/mysql/lib/plugin/"

1 2 3 4 5 6

MariaDB数据库提供的半同步插件如下： [iyunv@node3 ~]# ls /usr/local/mysql/lib/plugin/ |grep '^semisync' #用于Master服务器安装的半同步插件 semisync_master.so #用于Slave服务器安装的半同步插件 semisync_slave.so

2.在Master与Slave服务器分别安装半同步插件
在Master服务器安装半同步插件

1 2 3 4

[iyunv@node3 ~]# mysql MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装Master半同步插件 MariaDB [(none)]> set global rpl_semi_sync_master_enabled = 1;    #开启Master半同步功能 MariaDB [(none)]> set global rpl_semi_sync_master_timeout = 2000; #超时时间毫秒级

在Slave服务器安装半同步插件

1 2 3 4

[iyunv@node4 ~]# mysql MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装Slave半同步插件 MariaDB [(none)]> set global rpl_semi_sync_slave_enabled = 1;      #开启Slave半同步功能 MariaDB [(none)]> stop slave io_thread;start slave io_thread;      #重启IO线程生效

3.在主备节点查看半同步开启状态：
Master主节点：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

MariaDB [(none)]> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name                              | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients               | 1     |#可以看出有一个半同步客户端了； | 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              | 0     | | Rpl_semi_sync_master_no_tx                 | 0     | | 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     | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)

Slave备节点：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

MariaDB [(none)]> show global status like '%semi%'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON    |#半同步状态已经开启了； +----------------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show global variables like '%rpl%'; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | rpl_recovery_rank               | 0     | | rpl_semi_sync_slave_enabled     | ON    |#从服务器节点的半同步已经开启； | rpl_semi_sync_slave_trace_level | 32    | +---------------------------------+-------+ 3 rows in set (0.00 sec)

4.在Master服务器上将前面导入的hellodb数据库删除，然后验证Slave服务器
Master节点删除数据库hellodb：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

[iyunv@node3 ~]# mysql -uroot -p -e 'show databases';       Enter password: +--------------------+ | Database           | +--------------------+ | binlog             | | hellodb            | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+ [iyunv@node3 ~]# mysql -uroot -p -e 'drop database hellodb'; Enter password: [iyunv@node3 ~]# mysql -uroot -p -e 'show databases';       Enter password: +--------------------+ | Database           | +--------------------+ | binlog             | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+

到Slave节点验证数据库是否删除：

1 2 3 4 5 6 7 8 9 10 11 12

[iyunv@node4 ~]# mysql -uroot -p -e 'show databases';   Enter password: +--------------------+ | Database           | +--------------------+ | binlog             | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+

至此，基于半同步复制的主从数据库服务器构建完毕；

注意：以上配置都不能永久生效，如果想要永久生效，将以上配置加入到配置文件重启服务即可；


六.基于SSL安全传输的主从复制的实现

基于SSL安全传输的主从复制的简要介绍：
备份数据库是生产环境中的首要任务，重中之重，有时候不得不通过网络进行数据库的复制，这样就需要保证数据在网络传输过程中的安全性，因此使用基于SSL的复制会大加强数据的安全性。
由于Mysql的主从复制是明文传送的，但如果在生产环境中跨网络我们使用主从还是明文传送的话，就保证不了数据的安全性，为了解决这一问题，我们需要加密进行传送，也就是基于SSL的加密方法进行传输数据。

1.设置Master主节点为CA证书服务器。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

[iyunv@node3 ~]# cd /etc/pki/CA #创建私钥； [iyunv@node3 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048) Generating RSA private key, 2048 bit long modulus ......................................................+++ .................................+++ e is 65537 (0x10001) #生成自签署证书； [iyunv@node3 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:HA Locality Name (eg, city) [Default City]:ZZ Organization Name (eg, company) [Default Company Ltd]:magedu Organizational Unit Name (eg, section) []:ops Common Name (eg, your name or your server's hostname) []:node3.stu31.com Email Address []:admin@stu31.com 创建索引库文件和序列号文件： [iyunv@node3 CA]# touch index.txt; echo 01 > serial; [iyunv@node3 CA]# ls cacert.pem  certs  crl  index.txt  newcerts  private  serial

2.为Master节点node3创建证书申请并由CA服务器签发证书

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

#为数据库服务器创建ssl证书存放路径 [iyunv@node3 CA]# mkdir /usr/local/mysql/ssl [iyunv@node3 CA]# cd /usr/local/mysql/ssl #创建私钥文件； [iyunv@node3 ssl]# (umask 077;openssl genrsa -out master.key 2048) Generating RSA private key, 2048 bit long modulus ..........................+++ ..................................................................................................+++ e is 65537 (0x10001) #生成证书申请； [iyunv@node3 ssl]# openssl req -new -key master.key -out master.csr -days 3650 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:HA Locality Name (eg, city) [Default City]:ZZ Organization Name (eg, company) [Default Company Ltd]:magedu Organizational Unit Name (eg, section) []:ops Common Name (eg, your name or your server's hostname) []:node3.stu31.com Email Address []:admin@stu31.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: #CA服务器签署证书请求； [iyunv@node3 ssl]# openssl ca -in master.csr -out master.crt -days 3650 Using configuration from /etc/pki/tls/openssl.cnf Check that the request matches the signature Signature ok Certificate Details:         Serial Number: 1 (0x1)         Validity             Not Before: Jan 24 08:16:01 2015 GMT             Not After : Jan 21 08:16:01 2025 GMT         Subject:             countryName               = CN             stateOrProvinceName       = HA             organizationName          = magedu             organizationalUnitName    = ops             commonName                = node3.stu31.com             emailAddress              = admin@stu31.com         X509v3 extensions:             X509v3 Basic Constraints:                 CA:FALSE             Netscape Comment:                 OpenSSL Generated Certificate             X509v3 Subject Key Identifier:                 7A:F9:82:44:C2:D2:2B:EE:48:BF:4E:02:31:7D:50:8C:C5:A1:CB:36             X509v3 Authority Key Identifier:                 keyid:A6:00:73:4C:AC:37:A2:C2:A8:A6:CD:D0:8C:D3:8F:37:46:8E:6A:E6 Certificate is to be certified until Jan 21 08:16:01 2025 GMT (3650 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Data Base Updated

3.为Slave节点node4创建证书申请并由CA服务器签发证书

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

#为slave节点node4的数据库服务器创建证书存放路径； [iyunv@node4 ~]# mkdir /usr/local/mysql/ssl [iyunv@node4 ~]# cd /usr/local/mysql/ssl #生成私钥； [iyunv@node4 ssl]# (umask 077;openssl genrsa -out slave.key 2048) Generating RSA private key, 2048 bit long modulus .................................................................+++ .....+++ e is 65537 (0x10001) #生成证书申请文件； [iyunv@node4 ssl]# openssl req -new -key slave.key -out slave.csr -days 3650 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:HA Locality Name (eg, city) [Default City]:ZZ Organization Name (eg, company) [Default Company Ltd]:magedu Organizational Unit Name (eg, section) []:ops Common Name (eg, your name or your server's hostname) []:node4.stu31.com Email Address []:root@stu31.com      Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:

为Slave服务器签署证书

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

#需要将slave节点的证书申请复制到CA服务器， [iyunv@node4 ssl]# scp slave.csr node3:/tmp root@node3's password: slave.csr                                     100% 1037     1.0KB/s   00:00   #让CA服务器签署证书； [iyunv@node3 ssl]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 3650 Using configuration from /etc/pki/tls/openssl.cnf Check that the request matches the signature Signature ok Certificate Details:         Serial Number: 2 (0x2)         Validity             Not Before: Jan 24 08:22:04 2015 GMT             Not After : Jan 21 08:22:04 2025 GMT         Subject:             countryName               = CN             stateOrProvinceName       = HA             organizationName          = magedu             organizationalUnitName    = ops             commonName                = node4.stu31.com             emailAddress              = root@stu31.com         X509v3 extensions:             X509v3 Basic Constraints:                 CA:FALSE             Netscape Comment:                 OpenSSL Generated Certificate             X509v3 Subject Key Identifier:                 11:99:B6:ED:1D:E3:87:BA:28:CA:87:0C:73:28:4C:60:D8:3E:BD:CA             X509v3 Authority Key Identifier:                 keyid:A6:00:73:4C:AC:37:A2:C2:A8:A6:CD:D0:8C:D3:8F:37:46:8E:6A:E6 Certificate is to be certified until Jan 21 08:22:04 2025 GMT (3650 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Data Base Updated

证书在CA服务器节点签署好后，拷贝到slave服务器：

1 2 3 4 5

[iyunv@node4 ssl]# scp node3:/tmp/slave.crt ./ root@node3's password: slave.crt                                     100% 4568     4.5KB/s   00:00    [iyunv@node4 ssl]# ls slave.crt  slave.csr  slave.key

4.将CA证书拷贝到Slave服务器并为Master拷贝一份

1 2	[iyunv@node3 ssl]# scp /etc/pki/CA/cacert.pem node4:/usr/local/mysql/ssl/ [iyunv@node3 ssl]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/

#修改Master与Slave服务器证书属主、属组为mysql用户

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

主节点的权限授予： [iyunv@node3 ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/ [iyunv@node3 ssl]# ls /usr/local/mysql/ssl/ -l total 20 -rw-r--r-- 1 mysql mysql 1391 Jan 24 16:25 cacert.pem -rw-r--r-- 1 mysql mysql 4573 Jan 24 16:16 master.crt -rw-r--r-- 1 mysql mysql 1037 Jan 24 16:14 master.csr -rw------- 1 mysql mysql 1675 Jan 24 16:11 master.key #从节点的权限授予： [iyunv@node4 ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/ [iyunv@node4 ssl]# ls -l /usr/local/mysql/ssl/ total 20 -rw-r--r-- 1 mysql mysql 1391 Jan 24 16:25 cacert.pem -rw-r--r-- 1 mysql mysql 4568 Jan 24 16:23 slave.crt -rw-r--r-- 1 mysql mysql 1037 Jan 24 16:18 slave.csr -rw------- 1 mysql mysql 1679 Jan 24 16:17 slave.key

5.在Master与Slave服务器修改主配置文件开启SSL加密功能

修改Master服务器配置文件：

1 2 3 4 5 6 7 8

[iyunv@node3 ~]# vim /etc/my.cnf                   #添加如下选项 [mysqld]  #在此段中添加如下配置 ssl                                               #开启SSL功能 ssl_ca = /usr/local/mysql/ssl/cacert.pem          #指定CA文件位置 ssl_cert = /usr/local/mysql/ssl/master.crt        #指定证书文件位置 ssl_key = /usr/local/mysql/ssl/master.key         #指定密钥所在位置 重启mysqld服务： [iyunv@node3 ~]# service mysqld restart

修改Slave服务器的配置文件：

1 2 3 4 5 6 7 8

[iyunv@node4 ~]# vim /etc/my.cnf [mysqld] ssl ssl_ca = /usr/local/mysql/ssl/cacert.pem ssl_cert = /usr/local/mysql/ssl/slave.crt ssl_key = /usr/local/mysql/ssl/slave.key 重启mysqld服务： [iyunv@node4 ~]# service mysqld restart

6.在Master服务器查看SSL加密是否开启；然后创建授权一个基于密钥认证的用户

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

[iyunv@node3 ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor.  Commands end with ; or g. Your MariaDB connection id is 7 Server version: 10.0.12-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. #SSL加密是开启的； MariaDB [(none)]> show variables like '%ssl%'; +---------------+---------------------------------+ | Variable_name | Value                           | +---------------+---------------------------------+ | have_openssl  | YES                             | | have_ssl      | YES                             | | ssl_ca        | /usr/local/mysql/ssl/cacert.pem | | ssl_capath    |                                 | | ssl_cert      | /usr/local/mysql/ssl/master.crt | | ssl_cipher    |                                 | | ssl_crl       |                                 | | ssl_crlpath   |                                 | | ssl_key       | /usr/local/mysql/ssl/master.key | +---------------+---------------------------------+ 9 rows in set (0.00 sec) #创建授权一个基于密钥认证的用户 MariaDB [(none)]> grant replication client,replication slave on *.* to 'slaveuser'@'172.16.%.%' identified by 'oracle' require ssl; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) #查看Master服务器二进制日志文件和事件位置用于Slave服务器连接从这个位置开始复制； MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000008 |      656 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> q Bye

7.在Slave服务器测试使用加密用户指定密钥连接Master服务器

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

[iyunv@node4 ~]# mysql -uslaveuser -poracle -h 172.16.31.20 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key Welcome to the MariaDB monitor.  Commands end with ; or g. Your MariaDB connection id is 8 Server version: 10.0.12-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. #可以看出时间节点和上面显示的一致； MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000008 |      656 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> q Bye

8.查看Slave服务器SSL是否开启并连接Master服务器

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80

[iyunv@node4 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor.  Commands end with ; or g. Your MariaDB connection id is 6 Server version: 10.0.12-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. #可以看出slavefuwq节点开启了SSL MariaDB [(none)]> show variables like '%ssl%'; +---------------+---------------------------------+ | Variable_name | Value                           | +---------------+---------------------------------+ | have_openssl  | YES                             | | have_ssl      | YES                             | | ssl_ca        | /usr/local/mysql/ssl/cacert.pem | | ssl_capath    |                                 | | ssl_cert      | /usr/local/mysql/ssl/slave.crt  | | ssl_cipher    |                                 | | ssl_crl       |                                 | | ssl_crlpath   |                                 | | ssl_key       | /usr/local/mysql/ssl/slave.key  | +---------------+---------------------------------+ 9 rows in set (0.01 sec) #由于半同步复制时开启的slave，我们需要先关闭； MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.03 sec) #设置连接master节点； MariaDB [(none)]> change master to master_host='172.16.31.20',master_user='slaveuser',master_password='oracle',master_log_file='mysql-bin.000008',master_log_pos=656,master_ssl=1,master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',master_ssl_cert='/usr/local/mysql/ssl/slave.crt',master_ssl_key='/usr/local/mysql/ssl/slave.key'; Query OK, 0 rows affected (0.09 sec) #启动slave节点的IO线程和SQL线程； MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) #查看Slave服务器状态 MariaDB [(none)]> show slave statusG *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 172.16.31.20                   Master_User: slaveuser                   Master_Port: 3306                 Connect_Retry: 5               Master_Log_File: mysql-bin.000008           Read_Master_Log_Pos: 656                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 535         Relay_Master_Log_File: mysql-bin.000008              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: 656               Relay_Log_Space: 826               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Master_SSL_Allowed: Yes            Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem            Master_SSL_CA_Path:               Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt             Master_SSL_Cipher:                Master_SSL_Key: /usr/local/mysql/ssl/slave.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                Master_SSL_Crl: /usr/local/mysql/ssl/cacert.pem            Master_SSL_Crlpath:                    Using_Gtid: No                   Gtid_IO_Pos: 1 row in set (0.00 sec)

可以发现，我们的slave的IO线程和SQL线程开启了，已经在开始同步了；


9.基于SSL安全传输的测试；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

我们将删除的hellodb数据库重新导入仅Master服务器节点； [iyunv@node3 ~]# mysql -uroot -p < hellodb.sql Enter password: [iyunv@node3 ~]# mysql -uroot -p -e 'show databases'; Enter password: +--------------------+ | Database           | +--------------------+ | binlog             | | hellodb            | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+

我们到Slave节点查看数据库hellodb存在与否：

1 2 3 4 5 6 7 8 9 10 11 12 13

[iyunv@node4 ~]# mysql -u root -p -e 'show databases'; Enter password: +--------------------+ | Database           | +--------------------+ | binlog             | | hellodb            | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+

至此，MariaDB/MySQL的基于SSL加密的复制已全部完成。

七.复制相关的文件介绍

我们到slave节点查看数据文件：

1 2 3 4 5 6 7 8

[iyunv@node4 ~]# ls /mydata/data/ aria_log.00000001  master.info        mysql-bin.000005     performance_schema aria_log_control   multi-master.info  mysql-bin.000006     relay-bin.000001 binlog             mysql              mysql-bin.000007     relay-bin.000002 hellodb            mysql-bin.000001   mysql-bin.000008     relay-bin.index ibdata1            mysql-bin.000002   mysql-bin.index      relaylog ib_logfile0        mysql-bin.000003   node4.stu31.com.err  relay-log.info ib_logfile1        mysql-bin.000004   node4.stu31.com.pid  test

这里除了基本的数据库文件和二进制日志，还有一些与复制相关的文件。如下：
(1)mysql-bin.index
服务器一旦开启二进制日志，会产生一个与二日志文件同名，但是以.index结尾的文件。它用于跟踪磁盘上存在哪些二进制日志文件。MySQL用它来定位二进制日志文件。它的内容如下：

1 2 3 4 5 6 7 8 9

[iyunv@node4 ~]# cat /mydata/data/mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003 ./mysql-bin.000004 ./mysql-bin.000005 ./mysql-bin.000006 ./mysql-bin.000007 ./mysql-bin.000008

(2)mysql-relay-bin.index
该文件的功能与mysql-bin.index类似，但是它是针对中继日志，而不是二进制日志。内容如下：

1 2 3

[iyunv@node4 ~]# cat /mydata/data/relay-bin.index ./relay-bin.000001 ./relay-bin.000002

(3)master.info
保存master的相关信息。不要删除它，否则，slave重启后不能连接master。内容如图：


(4)relay-log.info

包含slave中当前二进制日志和中继日志的信息。

1 2 3 4 5

[iyunv@node4 ~]# cat /mydata/data/relay-log.info ./relay-bin.000002 8849 mysql-bin.000008 8970

八.双主复制模型架构构建
1.双主复制模型架构简介
主-主复制（也叫双主复制或双向复制）包含两台服务器，每一个都配配置为对方的主库和备库，换句话说，它们是一对主库。

架构如图：


2.双主模型架构的配置过程：
（1）.各自使用不同的server-id；
（2）.都启用二进制日志和中继日志；
（3）.定义自动增长的id字段的增长方式
（4）.都授权有复制权限的用户帐号；
（5）.各自把对方指定为主服务器；

3.考虑一个问题：
如果A服务器上自动增长的列编号有一个35，此时还没有同步到B服务器上，在B服务器上插入一条数据，编号也是35。当同步A的35到B服务器上来的话，必然产生数据丢失。

解决方法：
我们只要保证两台服务器上插入的自增长数据不同就可以了
如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，你可以定义算法，只要不同就可以了。


4.双主复制的数据库主配置文件配置
node3节点的配置：

1 2 3 4 5 6 7 8 9 10 11

[iyunv@node3 ~]# vim /etc/my.cnf [mysqld]                     #在此配置段中增加如下内容； log-bin=mysql-bin binlog_format=mixed server-id       = 1 relay-log=relay-bin #第一个变量名 auto_increment_offset 指自增字段的起始值。 auto_increment_offset=1 #第二个变量名 auto_increment_increment 就是指字段一次递增多少； auto_increment_increment=2 log_slave_updates       = 1

node4节点的配置：

1 2 3 4 5 6 7 8 9 10 11

[iyunv@node4 ~]# vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format=mixed server-id       = 10 relay-log       = relay-bin log_slave_updates       = 1 #第一个变量名 auto_increment_offset 指自增字段的起始值。 auto_increment_offset=2 #第二个变量名 auto_increment_increment 就是指字段一次递增多少； auto_increment_increment=2

其它配置不变，由于我先前实验做了基于SSL的安全复制，我们需要将ssl配置段删除；
略。
配置完成后重启mysqld服务即可；

5.设置复制帐号；
两个节点都需要设置帐号，为了方便我们就全部设置一致：

1 2	MariaDB [(none)]> grant replication slave,replication client on *.* to repluser@'172.16.%.%' identified by 'replpass'; MariaDB [(none)]> flush privileges;

上述命令两个节点都执行一遍；


6.查看两个节点的时间点；
node3节点的时间点；

1 2 3 4 5 6 7

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

node4节点的时间点：

1 2 3 4 5 6 7

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

两个节点的时间点都是一致的；那就方便设置了；

7.双主复制模型开启设置
node3节点启动：

1 2 3 4 5 6

#开启复制； MariaDB [(none)]> change master to master_host='172.16.31.21',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000009',master_log_pos=653; Query OK, 0 rows affected (0.08 sec) #开启复制进程； MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)

node4节点启动：

1 2 3 4

MariaDB [(none)]> change master to master_host='172.16.31.20',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000009',master_log_pos=653; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)

8.查看节点的复制状态信息；
node3的节点的主从复制状态：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

MariaDB [(none)]> show slave statusG *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 172.16.31.21                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000009           Read_Master_Log_Pos: 653                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 535         Relay_Master_Log_File: mysql-bin.000009              Slave_IO_Running: Yes             Slave_SQL_Running: Yes 信息略；         Seconds_Behind_Master: 0 1 row in set (0.00 sec)

node4节点的主从复制状态信息；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

MariaDB [(none)]> show slave statusG *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 172.16.31.20                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 5               Master_Log_File: mysql-bin.000009           Read_Master_Log_Pos: 653                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 535         Relay_Master_Log_File: mysql-bin.000009              Slave_IO_Running: Yes             Slave_SQL_Running: Yes 信息略；         Seconds_Behind_Master: 0    1 row in set (0.00 sec)

9.双主复制架构测试：
在node3节点插入2行数据：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

#创建数据库mydb； MariaDB [(none)]> create database mydb; Query OK, 1 row affected (0.00 sec) #切换到数据库mydb； MariaDB [(none)]> use mydb; Database changed #创建表tb1； MariaDB [mydb]> create table tb1 (id int unsigned not null auto_increment primary key, name char(30)); Query OK, 0 rows affected (0.08 sec) #插入数据tom； MariaDB [mydb]> insert into tb1 (name) values ('tom'); Query OK, 1 row affected (0.01 sec) #插入数据jerry； MariaDB [mydb]> insert into tb1 (name) values ('jerry'); Query OK, 1 row affected (0.01 sec) #查看ID，可发现node3增长是按奇数增长的； MariaDB [mydb]> select * from tb1; +----+-------+ | id | name  | +----+-------+ |  1 | tom   | |  3 | jerry | +----+-------+ 2 rows in set (0.00 sec)

node4节点也插入2条数据：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

#查看数据库； MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | binlog             | | hellodb            | | information_schema | | mydb               | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]> use mydb; Database changed MariaDB [mydb]> insert into tb1 (name) values ('andy'); Query OK, 1 row affected (0.04 sec) MariaDB [mydb]> insert into tb1 (name) values ('allen'); Query OK, 1 row affected (0.01 sec) MariaDB [mydb]> select * from tb1; +----+-------+ | id | name  | +----+-------+ |  1 | tom   | |  3 | jerry | |  4 | andy  | |  6 | allen | +----+-------+ 4 rows in set (0.00 sec)

让在node3节点上插入的行的自动增长都为奇数，让node4节点服务器上的自动增长都为偶数。

至此，MariaDB/MySQL的主从复制、半同步复制、基于SSL加密的复制以及MariaDB/MySQL的双主模型架构就全部完成。


九.数据库复制过滤器功能

1.复制过滤器功能的简介
主服务器只能过滤到库级别，从服务器可以过滤到表级别;
要想使设置永久生效，就写入配置文件

让slave仅复制有限的几个数据库，而非所有；
有两种实现思路：
(1) 主服务器仅向二进制日志中记录有特定数据库相关的写操作；
问题：即时点还原将无法全面实现；
binlog_do_db=                  # 数据库白名单
binlog_ignore_db=              # 数据库黑名单

(2)从服务器的SQL_THREAD仅在中断日志中读取特定数据相关的语句并应用在本地；
问题：会造成网络带宽和磁盘IO的浪费；
        replicate_do_db=  (数据库白名单，多个用列表)
        replicate_ignore_db=  (黑名单)
        不建议同时使用，
        如果同时启用则以白名单为准，
        如果同时出现在白黑名单中，则拒绝复制
        replicate_do_table= db_name.table_name  (表的白名单)
        replicate_ignore_table=(表的黑名单)
        replicate_wild_do_table=
        replicate_wild_ignore_table=
        以上两项支持通配符，进行过滤；


2.复制过滤器功能的实现；
（1）.基于库的白名单的实现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

#我们在node4节点实现只允许同步testdb这个数据库； #先停止slave进程； MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.01 sec) #设置过滤器的白名单，允许同步的数据库； MariaDB [(none)]> set global replicate_do_db='testdb'; Query OK, 0 rows affected (0.00 sec) #启动slave进程； MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) #查看关于过滤器的选项参数； MariaDB [(none)]> show global variables like '%replicat%'; +----------------------------------+-----------+ | Variable_name                    | Value     | +----------------------------------+-----------+ | innodb_replication_delay         | 0         | | replicate_annotate_row_events    | OFF       | | replicate_do_db                  | testdb    | | replicate_do_table               |           | | replicate_events_marked_for_skip | replicate | | replicate_ignore_db              |           | | replicate_ignore_table           |           | | replicate_wild_do_table          |           | | replicate_wild_ignore_table      |           | +----------------------------------+-----------+ 9 rows in set (0.00 sec)

我们到node3节点创建数据库testdb；

1 2 3 4 5 6 7 8 9 10 11 12 13 14

MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | binlog             | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> create database testdb; Query OK, 1 row affected (0.00 sec)

我们在node4节点上查看数据库，发现testdb被同步过来：

1 2 3 4 5 6 7 8 9 10 11 12 13

MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | binlog             | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | | testdb             | +--------------------+ 7 rows in set (0.00 sec)

我们在node3节点新建数据库mydb；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

MariaDB [(none)]> create database mydb; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | binlog             | | information_schema | | mydb               | | mysql              | | performance_schema | | relaylog           | | test               | | testdb             | +--------------------+ 8 rows in set (0.00 sec)

到node4节点查看数据库，发现mydb没有被同步过来，白名单只允许testdb同步过来：

1 2 3 4 5 6 7 8 9 10 11 12 13

MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | binlog             | | information_schema | | mysql              | | performance_schema | | relaylog           | | test               | | testdb             | +--------------------+ 7 rows in set (0.00 sec)

（2）.基于表做过滤实现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

#在从服务器节点node4上做hellodb库的students表过滤 MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> set global replicate_ignore_table='hellodb.students'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global variables like '%replicat%'; +----------------------------------+------------------+ | Variable_name                    | Value            | +----------------------------------+------------------+ | innodb_replication_delay         | 0                | | replicate_annotate_row_events    | OFF              | | replicate_do_db                  | testdb           | | replicate_do_table               |                  | | replicate_events_marked_for_skip | replicate        | | replicate_ignore_db              |                  | | replicate_ignore_table           | hellodb.students | | replicate_wild_do_table          |                  | | replicate_wild_ignore_table      |                  | +----------------------------------+------------------+ 9 rows in set (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) #在从服务器查看从服务器中的hellodb库中的students表中的信息 MariaDB [(none)]> use hellodb; Database changed MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name          | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 | |     2 | Shi Potian    |  22 | M      |       1 |         7 | |     3 | Xie Yanke     |  53 | M      |       2 |        16 | |     4 | Ding Dian     |  32 | M      |       4 |         4 | |     5 | Yu Yutong     |  26 | M      |       3 |         1 | |     6 | Shi Qing      |  46 | M      |       5 |      NULL | |     7 | Xi Ren        |  19 | F      |       3 |      NULL | |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | |    15 | Duan Yu       |  19 | M      |       4 |      NULL | |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | |    17 | Lin Chong     |  25 | M      |       4 |      NULL | |    18 | Hua Rong      |  23 | M      |       7 |      NULL | |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | |    20 | Diao Chan     |  19 | F      |       7 |      NULL | |    21 | Huang Yueying |  22 | F      |       6 |      NULL | |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | |    23 | Ma Chao       |  23 | M      |       4 |      NULL | |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.01 sec)

主服务器节点node3也是有hellodb数据库的，我们在这个库中的students表中插入一个数据：

1 2 3 4

MariaDB [(none)]> use hellodb; Database changed MariaDB [hellodb]> insert into students(stuid,name,age,gender,classid) values (26,'Qiao Feng',35,'M',5);   Query OK, 1 row affected (0.02 sec)

我们到从节点上查看hellodb库的students表，发现在主节点node3插入的数据未同步过来，实现了表过滤操作：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name          | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 | |     2 | Shi Potian    |  22 | M      |       1 |         7 | |     3 | Xie Yanke     |  53 | M      |       2 |        16 | |     4 | Ding Dian     |  32 | M      |       4 |         4 | |     5 | Yu Yutong     |  26 | M      |       3 |         1 | |     6 | Shi Qing      |  46 | M      |       5 |      NULL | |     7 | Xi Ren        |  19 | F      |       3 |      NULL | |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | |     9 | Ren Yingying  |  20 | F      |       6 |      NULL | |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | |    13 | Tian Boguang  |  33 | M      |       2 |      NULL | |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | |    15 | Duan Yu       |  19 | M      |       4 |      NULL | |    16 | Xu Zhu        |  21 | M      |       1 |      NULL | |    17 | Lin Chong     |  25 | M      |       4 |      NULL | |    18 | Hua Rong      |  23 | M      |       7 |      NULL | |    19 | Xue Baochai   |  18 | F      |       6 |      NULL | |    20 | Diao Chan     |  19 | F      |       7 |      NULL | |    21 | Huang Yueying |  22 | F      |       6 |      NULL | |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | |    23 | Ma Chao       |  23 | M      |       4 |      NULL | |    24 | Xu Xian       |  27 | M      |    NULL |      NULL | |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec)

我们在主节点上创建一张表tb1：

1 2	MariaDB [hellodb]> create table tb1 like students; Query OK, 0 rows affected (0.11 sec)

我们到从服务器节点查看表是否同步过来：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | tb1               | | teachers          | | toc               | +-------------------+ 8 rows in set (0.00 sec)

至此，数据库的复制过滤器功能就介绍完毕了。

龙泪

666