mysql —复制

MySQL的扩展

读写分离

　　复制：每个节点都有相同的数据集

　　　　向外扩展

　　　　二进制日志

　　　　单向

复制的功用：

　　　　数据分布

　　　　负载均衡读

　　　　备份

　　　　高可用和故障切换

　　　　MySQL升级测试

MySQL复制相关概念

　　主从复制：主节点将数据同步到多个从节点

　　级联复制：主节点将数据同步到一个从节点，其他的从节点在向从节点复制数据

　　同步复制：将数据从主节点全部同步到从节点时才返回给用户的复制策略叫同步复制

　　异步复制：只要数据写入到主节点就立即返回给用户同步完成

　　读写分离：在前端加一个调度器，负责将改变数据的语句和查询数据的语句分开调度，把写操作调度到主节点，读操作调度到从节点

主节点：

　　dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

从节点：

　　I/O Thread：向Master请求二进制日志事件，并保存于中继日志中

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

跟复制功能相关的文件：

　　master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等

　　relay-log.info（中继日志）：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

复制架构：

　　一主一从

　　一主多从

　　主主复制

　　环状复制

　　级联复制

　　多主一从

简单的一主一从架构实现

主服务器配置

~]# vim /etc/my.cnf
    [mysqld]
    log_bin
    binlog_format=ROW
    server_id=1
~]# systemctl restart mariadb
~]# mysql
MariaDB [(none)]> grant replication slave on *.* to lv(从服务接收的用户)@'192.168.130.%' identified by 'centos'; #授权同步账号
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size           |
+--------------------+-----------+
| mariadb-bin.000001 | 19578633 |
| mariadb-bin.000002 | 395 | #记录此位置，从服务器从这里开始同步

+--------------------+-----------+
2 rows in set (0.00 sec)

2）从服务器配置

~]# vim /etc/my.cnf
    [mysqld]
    server_id=2  #服务器ID唯一
    relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
    relay_log_index=relay-log.index #默认值hostname-relay-bin.index
    read_only=ON
~]# systemctl restart mariadb #重启服务
~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.130.8',  #指定主节点IP
    -> MASTER_USER='lv',  #同步用户的用户名
    -> MASTER_PASSWORD='centos',  #同步用户的密码
    -> MASTER_PORT=3306, #数据库的端口号
    -> MASTER_LOG_FILE='mariadb-bin.000002', #以上记录的文件
    -> MASTER_LOG_POS=395, #位置 
    -> MASTER_CONNECT_RETRY=10; #重试时间10秒
 MariaDB [(none)]> START SLAVE; #开始主从复制

3）测试

在主节点上生成一些数据：
MariaDB [(none)]> CREATE DATABASE testdb;
MariaDB [(none)]> use testdb
MariaDB [testdb]> create table testlog (id int auto_increment primary key,name char(20),age int default 20);
MariaDB [testdb]> delimiter $$
MariaDB [testdb]> create procedure pro_testlog()
    -> begin
    -> declare i int;
    -> set i = 1;
    -> while i < 100000
    -> do insert into testlog(name,age) values (concat('testuser',i),i);
    -> set i = i +1; 
    -> end while;
    -> end$$
MariaDB [testdb]> delimiter ;
MariaDB [testdb]> START TRANSACTION;
MariaDB [testdb]> CALL pro_testlog;
MariaDB [testdb]> COMMIT;

在从节点上查看同步情况：
MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
|    99999 |  #同步成功
+----------+
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ****************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.130.8
                  Master_User: lv
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000002
          Read_Master_Log_Pos: 10389814
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 10389944
        Relay_Master_Log_File: master1-bin.000002
             Slave_IO_Running: Yes  #IO线程已启动
            Slave_SQL_Running: Yes  #SQL线程已启动
        Seconds_Behind_Master: 0    #主从复制的时间差
             Master_Server_Id: 1

旧数据库新加从服务器

1）主服务器配置

~]# vim /etc/my.cnf
    [mysqld]
    log_bin
    binlog_format=ROW
    log-basename=master1
    server_id=1 #为当前节点设置一个全局惟的ID号
~]# systemctl restart mariadb
~]# mysqldump -uroot -pcentos -A -F --single-transaction --master-data=1 > full.sql
~]# scp full.sql root@192.168.130.7:/root/
~]#mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser(从服务接收的用户)@'192.168.130.7' IDENTIFIED BY 'centos';

2）从服务器配置~]# vim /etc/my.cnf

    [mysqld]
    server_id=2  #为当前节点设置一个全局惟的ID号
    relay_log=relay-log  
    read_only=ON
~]# systemctl restart mariadb
~]# vim full.sql  #在备份的SQL文件中加入以下信息
CHANGE MASTER
TO MASTER_HOST='192.168.130.8',
MASTER_USER='testuser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003',# 已默认的
MASTER_LOG_POS=245, #已默认的
MASTER_CONNECT_RETRY=10;
~]# mysql < full.sql  #导入SQL的同时配置已经完成
MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
|    99999 |
+----------+
MariaDB [(none)]> START SLAVE;  #启动同步复制
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.8
Master_User: testuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 401
Relay_Log_File: relay-log.024649
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: Yes #IO线程已启动
Slave_SQL_Running: Yes #SQL线程已启动
Seconds_Behind_Master: 0 #主从复制的时间差
Master_Server_Id: 1

级联复制

1）主节点

[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
server_id=1
[root@master ~]# systemctl restart mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.130.%' IDENTIFIED BY 'centos';
MariaDB [(none)]> SHOW MASTER LOGS;

+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 19578756 |
| mariadb-bin.000002 | 401 |
+--------------------+-----------+
2 rows in set (0.00 sec)

2）从节点

[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
log_bin  #注意，级联架构中中继从节点一定得开二进制日志功能
binlog_format=ROW
read_only=ON
server_id=2
log_slave_updates  #这项为关键，作用是将从服务的数据改变记录到二进制日志文件中
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave1 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.130.10',
    -> MASTER_USER='testuser',
    -> MASTER_PASSWORD='ccentos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000002',
    -> MASTER_LOG_POS=401,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 19578633 |
| mariadb-bin.000002 | 10389327 |
| mariadb-bin.000003 | 1792 |
| mariadb-bin.000004 | 264 |
| mariadb-bin.000005 | 245 |
+--------------------+-----------+
5 rows in set (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.%' IDENTIFIED BY 'centos';

3）从节点的从节点

[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave2 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.130.8',
    -> MASTER_USER='testuser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000005',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

4）测试

在主节点上生成一些数据：
MariaDB [(none)]> CREATE DATABASE testdb;
MariaDB [(none)]> use testdb
MariaDB [testdb]> create table testlog (id int auto_increment primary key,name char(20),age int default 20);
MariaDB [testdb]> delimiter $$
MariaDB [testdb]> create procedure pro_testlog()
    -> begin
    -> declare i int;
    -> set i = 1;
    -> while i < 100000
    -> do insert into testlog(name,age) values (concat('testuser',i),i);
    -> set i = i +1; 
    -> end while;
    -> end$$
MariaDB [testdb]> delimiter ;
MariaDB [testdb]> START TRANSACTION;
MariaDB [testdb]> CALL pro_testlog;
MariaDB [testdb]> COMMIT;

在从节点上查看同步情况：
MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
|    99999 |  #同步成功
+----------+
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.10
Master_User: testuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 401
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes #IO线程已启动
Slave_SQL_Running: Yes #SQL线程已启动
Seconds_Behind_Master: 0 #主从复制的时间差
Master_Server_Id: 1

在从节点的从节点上查看同步情况：
MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
|    99999 |  #同步成功
+----------+
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.8
Master_User: testuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000005
Read_Master_Log_Pos: 401
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 687
Relay_Master_Log_File: mariadb-bin.000005
Slave_IO_Running: Yes #IO线程已启动
Slave_SQL_Running: Yes #SQL线程已启动
Seconds_Behind_Master: 0 #主从复制的时间差
Master_Server_Id: 1

主主复制架构

容易产生的问题：数据不一致，因此慎用；考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2

半同步复制的实现

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失；半同步复制的机制是只有当主节点和从节点同步完成，仅有一台同步完成即可，返回写入完成，这样的机制保证了数据的安全性。

1）主节点

[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
log-basename=master
server_id=1
relay_log=relay-log
relay_log_index=relay-log.index
[root@master ~]# systemctl restart mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.%' IDENTIFIED BY 'centos';
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 19578756 |
| mariadb-bin.000002 | 586 |
| mariadb-bin.000003 | 401 |
+--------------------+-----------+
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  #安装模块
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;  #开启半同步功能
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |  #已开启
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| 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     |
+--------------------------------------------+-------+

2）从节点1

[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
log_bin
binlog_format=ROW
log-basename=slave
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave1 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.130.10',
    -> MASTER_USER='testuser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000003',
    -> MASTER_LOG_POS=401,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #安装模块
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1; #开启半同步功能
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| slave-bin.000001 | 245 |
+------------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';

3）从节点2

[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave2 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.130.10',
    -> MASTER_USER='testuser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='slave-bin.000001',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
当然，从服务器还可以有很多，此处到此为止。再按上面的测试一下就OK！

复制过滤器

复制过滤器表示可以指定只复制指定的表或者只复制指定的库，其他的表和库都不进行复制，两种方法可以实现：

　　主服务器实现：主服务器指定只有指定的表或者库的修改信息才传递到从服务器的中继日志，但是主服务器出现问题之后会丢失未复制表和库的数据；不建议使用（限定的二进制文件不会同步过来；这样做会使二进制数据造成丢失）

　　从服务器实现：所有二进制日志都传到从服务器的中继日志，但是sql_thread只执行指定表和库的修改语句，当主服务器出现故障，则不会丢失数据，所有建议采用从服务器的复制过滤；（将所有的二进制日志文件都复制过来，但在同步到自己的数据库时，会选择不同步限制条件的内容）

从服务器配置：
SET GLOBAL replicate_do_db=库名 #指定只复制指定的库（相当于库的白名单）

SET GLOBAL Replicate_Ignore_DB=库名 #指定不复制指定的库（相当于库的黑名单）

SET GLOBAL Replicate_Do_Table=表名 #指定只复制指定表（相当于表的白名单）

SET GLOBAL Replicate_Ignore_Table=表名 #指定不复制指定表（相当于表的黑名单）

当指定了复制过滤器后，通过SHOW SLAVE STATUS;命令可以查看到指定的库和表

建议此配置写入配置文件永久生效。

加密复制

在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性

主服务器开启SSL：[mysqld] 加一行ssl
主服务器配置证书和私钥；并且创建一个要求必须使用SSL连接的复制账号
从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项

参看：https://mariadb.com/kb/en/library/replication-with-secureconnections/

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+

特别提示：在配置之前先检查mysql服务是否支持ssl功能，如果have_ssl的值为'DISABLED'则支持；如果为'NO'则不支持，需要再重新编译安装或者安装具有ssl功能的版本

1）CA

[root@CA ~]# mkdir /etc/my.cnf.d/ssl/
[root@CA ~]# cd /etc/my.cnf.d/ssl/
[root@CA ssl]# openssl genrsa 2048 > cakey.pem
[root@CA ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 #自签证书
    Country Name (2 letter code) [XX]:CN
    State or Province Name (full name) []:beijing
    Locality Name (eg, city) [Default City]:beijing
    Organization Name (eg, company) [Default Company Ltd]:testmysqlca 
    Organizational Unit Name (eg, section) []:opt
    Common Name (eg, your name or your server's hostname) []:ca.testmysqlca.com

[root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr
    Country Name (2 letter code) [XX]:CN
    State or Province Name (full name) []:beijing
    Locality Name (eg, city) [Default City]:beijing
    Organization Name (eg, company) [Default Company Ltd]:testmysqlca
    Organizational Unit Name (eg, section) []:opt
    Common Name (eg, your name or your server's hostname) []:master.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt #签署master证书

[root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr
    Country Name (2 letter code) [XX]:CN
    State or Province Name (full name) []:beijing
    Locality Name (eg, city) [Default City]:beijing
    Organization Name (eg, company) [Default Company Ltd]:testmysqlca 
    Organizational Unit Name (eg, section) []:opt
    Common Name (eg, your name or your server's hostname) []:slave.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt #签署slave证书

[root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave2.key > slave2.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:testmysqlca
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:slave2.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 > slave2.crt #签署slave2证书

[root@CA ssl]# openssl verify -CAfile cacert.pem master.crt slave.crt slave2.crt #检查证书是否可用
master.crt: OK
slave.crt: OK
slave2.crt: OK
先在各个节点上创建/etc/my.cnf.d/ssl/文件夹，将各自的证书，CA的证书和各自的秘钥文件复制过去
[root@CA ssl]# scp cacert.pem master.crt master.key root@192.168.0.7:/etc/my.cnf.d/ssl/
[root@CA ssl]# scp cacert.pem slave.crt slave.key root@192.168.0.8:/etc/my.cnf.d/ssl/ 
[root@CA ssl]# scp cacert.pem slave2.crt slave2.key root@192.168.0.9:/etc/my.cnf.d/ssl/

2）master

[root@master ~]# mkdir /etc/my.cnf.d/ssl/
[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
log-basename=master
server_id=1
ssl #开启ssl功能
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem #指定CA证书的路径
ssl-cert=/etc/my.cnf.d/ssl/master.crt #指定自己的证书的路径
ssl-key=/etc/my.cnf.d/ssl/master.key #指定自己的秘钥文件路径
[root@master ~]# systemctl restart mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass' REQUIRE SSL; #授权用户并且强制迫使用户开启ssl登录
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     26753 |
| master-bin.000002 |    921736 |
| master-bin.000003 |       413 |
+-------------------+-----------+

3）slave1

[root@slave1 ~]# mkdir /etc/my.cnf.d/ssl/
[root@slave1 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key
[root@slave1 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000003',
    -> MASTER_LOG_POS=413,
    -> MASTER_CONNECT_RETRY=10,
    -> MASTER_SSL=1;  #注意，需要指明开启ssl链接
MariaDB [(none)]> START SLAVE;

4）slave2

[root@slave2 ~]# mkdir /etc/my.cnf.d/ssl/
[root@slave2 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave2.crt --ssl-key=/etc/my.cnf.d/ssl/slave2.key
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave2.crt
ssl-key=/etc/my.cnf.d/ssl/slave2.key
[root@slave2 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000003',
    -> MASTER_LOG_POS=413,
    -> MASTER_CONNECT_RETRY=10,
    -> MASTER_SSL=1;
MariaDB [(none)]> START SLAVE;

MySQL复制的相关指令和变量总结

选项：

log_bin 启用二进制日志，在主节点或级联复制中间的从节点必须要开启
binlog_format=ROW 二进制日志记录方式为基于行的方式记录，强烈建议开启
log-basename=master | slave ... 二进制日志的前缀名，不是必须向，但建议标识
server_id = # 服务器ID，各个节点的ID必须唯一
relay_log = relay-log 开启中继日志，并以relay-log为文件名开头，从节点开启
relay_log_index = relay-log.index 中继日志索引文件
log_slave_updates 作用是SQL线程重读中继日志时将改变数据的操作记录为二进制日志，在级联复制中使用
ssl 开启ssl功能
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
sync_binlog=1 每次写后立即同步二进制日志到磁盘
innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
sync_master_info=# #次事件后master.info同步到磁盘
skip_slave_start=ON 不自动启动slave
sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘
auto_increment_offset=1 自动增长开始点，在主主复制中使用
auto_increment_increment=2 增长幅度

变量：

replicate_do_db= 指定复制库的白名单
replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table= 指定复制的表，黑名单

rpl_semi_sync_slave_enabled=1 开启半同步复制，需要安装模块

指令：

START SLAVE; 启动主从复制
STOP SLAVE; 停止复制
SHOW SLAVE STATUS; 查看复制状态
Seconds_Behind_Master: 0 从服务器是否落后于主服务
RESET SLAVE ALL; 重置从服务器的配置
MASTER_SSL=1, 配合 CHANGE MASTER TO 使用，开启ssl加密复制
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } 删除二进制日志，谨慎操作
SHOW MASTER STATUS 查看二进制日志状态
SHOW BINLOG EVENTS 查看二进制日志
SHOW BINARY LOGS 查看二进制日志
SHOW PROCESSLIST 查看进程

`MHA`

对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库。

`工作原理`

从宕机崩溃的master保存二进制日志事件（binlog events）
识别含有最新更新的slave 
应用差异的中继日志（relay log）到其他的slave 
应用从master保存的二进制日志事件（binlog events） 
提升一个slave为新的master 
使其他的slave连接新的master进行复制

Manager工具包主要包括以下几个工具：

masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 故障转移（自动或手动）
masterha_conf_host 添加或删除配置的server信息

MHA软件由两部分组成，Manager工具包和Node工具包

Node工具包：这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：

save_binary_logs 保存和复制master的二进制日志

apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave

purge_relay_logs 清除中继日志（不会阻塞SQL线程）

注意：为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA 的同时建议配置成MySQL 5.5的半同步复制

实现主从高可用MHA （在主服务器宕机时，自动将从服务器切换成主服务器来使用）

此功能只是一次性的，切换之后，功能就停用了，如果想启用需要再次开启此功能。

结构拓扑图：四台主机；manager,master,slave1,slave2 （管理者，主服务器，两个从服务器）

具体实验步骤：

1). 准备

selinux ,iptables ,time  （此实验各台服务器的时间要保持一致）

在vim /etc/ntp.conf  添加server 172.20.0.1   iburst

systemctl  start nptd

2) . 实现主从服务器的搭建

~]#vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
skip_name_resolve  #跳过名字解析
~]#systemctl start mariadb #重启服务
mysql > grant replication slave on *.* to lv@’192.168.60.%’ identified by ‘centos’;  #授权用户账号
mysql > show master logs;  #查看此时的二进制日志状态为后面从服务器同步时使用

3 ）.在slave1服务器上

~]#vim /etc/my.cnf
[mysqld]
server_id=2
log_bin
read_only=1
relay_log_purge=0
skip_name_resolve=1
~]#systemctl start mariadb  #重启服务
mysql > CHANGE MASTER TO MASTER_HOST=’192.168.60.20′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=394; #连接同步
mysql > start slave #开启同步
mysql > show slave status\G; #查看同步状态

4 ）.在slave2服务器上：

~]#vim /etc/my.cnf

[mysqld]
server_id=3
log_bin
read_only=1
relay_log_purge=0
skip_name_resolve=1
~]#systemctl start mariadb  #重启服务
mysql > CHANGE MASTER TO MASTER_HOST=’192.168.60.20′, MASTER_USER=’lv’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=394;   #连接同步
mysql > start slave #开启同步
mysql > show slave status\G; #查看同步状态

5 ). 在manager主机上：

实现基于key的验证：
~]#ssh-keygen  #生成私钥对
~]#ssh-copy-id 192.168.60.4  #把自己的公钥复制到自己的机器上
~]#cd   .ssh  #进到此目录下
~]#scp -pr .ssh 192.168.60.20:/root/
~]#scp -pr .ssh 192.168.60.21:/root/
~]#scp -pr .ssh 192.168.60.22:/root/  #将公钥都复制到其他主机的家目录下

6) . 在master服务器上新建授权用户：

grant all on *.* to testuser@’192.168.130.%’ identified by ‘centos’; # 此账号由于是现在建立的他会自动同步到其他两个从服务器上

7 ). 在管理节点上安装两个包： mha4mysql-manager mha4mysql-node #manager服务器上安装

在被管理节点安装： mha4mysql-node  #主从服务器上安装
mha4mysql-manager-0.56-0.el6.noarch.rpm Public
Documents mha4mysql-node-0.56-0.el6.noarch.rpm  #网上下载两个包
~]#yum install mha*   #安装两个包，要依赖于epel源

8 ). 在主从的三个服务器上安装mha4mysql-manager-0.56-0.el6.noarch.rpm Public包

9). 在manager服务器上创建一个文件：mkdir /etc/mha

~]#vim /etc/mha/app1.cnf
[server default]
user=testuser    #此为在第6步创建授权的用户
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=lv
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.130.10  #主服务器的地址
candidate_master=1
[server2]
hostname=192.168.130.7  #从服务器的地址
candidate_master=1     #添加此行代表，主服务器宕机时此为备用的
[server3]
hostname=192.168.130.8

10). MHA验证和启动 (检查和验证)

~]#masterha_check_ssh –conf=/etc/mha/app1.conf  #探测ssh协议能否连接成功
~]#masterha_check_repl –conf=/etc/mha/app1.conf   #检查复制功能
检查复制功能时出现错误，提示lv用户没有复制的权限，添加上权限，在主服务器上：grant all on *.* to lv@’192.168.130.%’; 添加和后再查看一下权限：show grants for lv@’192.168.130.%’;
~]#masterha_manager –conf=/etc/mha/app1.conf  #开启监控功能此命令为前台运行命令，开启后是不会退出的，以此执行词条命令应该在终端上行，如果在远程上执行，连接断开会造成监控失败

排错日志： /data/mha/app1/manager.log

posted @ 2018-06-17 22:22 琼兔阅读(209) 评论(0) 编辑收藏举报

刷新页面返回顶部

范儿

工欲善其事，必先利其器。

mysql —复制

MySQL的扩展

MySQL复制相关概念

简单的一主一从架构实现

旧数据库新加从服务器

级联复制

主主复制架构

半同步复制的实现

复制过滤器

加密复制

MySQL复制的相关指令和变量总结

`MHA`

`工作原理`

Manager工具包主要包括以下几个工具：

MHA软件由两部分组成，Manager工具包和Node工具包

公告