Linux九阴真经之九阴白骨爪残卷15

一、MySQL复制相关概念

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

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

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

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

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

主节点：

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日志的对应关系

复制架构：

一主一从
一主多从
主主复制
环状复制
级联复制
多主一从

常见的架构有主从架构或者级联架构

二、简单的一主一从架构实现

一、数据库搭建主从架构

1、主服务器配置

~]# vim /etc/my.cnf
    [mysqld]
    log_bin
    binlog_format=ROW
    log-basename=master1
    server_id=1
~]# systemctl restart mariadb
~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.95.5' IDENTIFIED BY 'testpass';  #授权同步账户及主机IP
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| master1-bin.000001 |     26756 |
| master1-bin.000002 |    921736 |
| master1-bin.000003 |       401 |  #记录此位置，从服务器从这里开始同步
+--------------------+-----------+

2、从服务配置

~]# vim /etc/my.cnf
    [mysqld]
    server_id=2  #服务器ID唯一
    relay_log=relay-log
    relay_log_index=relay-log.index
    read_only=ON
~]# systemctl restart mariadb
~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.95.2',  #指定主节点IP
    -> MASTER_USER='testuser',  #同步用户的用户名
    -> MASTER_PASSWORD='testpass',  #密码
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master1-bin.000003',  #主服务器记录的文件
    -> MASTER_LOG_POS=401,  #位置
    -> 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(30),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 STATUSG
*************************** 1. row ****************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.95.2
                  Master_User: testuser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master1-bin.000003
          Read_Master_Log_Pos: 10389814
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 10389944
        Relay_Master_Log_File: master1-bin.000003
             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
~]# systemctl restart mariadb
~]# mysqldump -A -F --single-transaction --master-data=1 > full.sql  #完全备份到文件内
~]# scp full.sql root@192.168.95.5:/root/   #将安全备份复制到远程主机
~]# mysql -e 'GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.95.5' IDENTIFIED BY 'testpass';'

2、从服务器配置

~]# vim /etc/my.cnf
    [mysqld]
    server_id=2
    relay_log=relay-log   
    relay_log_index=relay-log.index
    read_only=ON
~]# systemctl restart mariadb
~]# vim full.sql  #在备份的SQL文件中加入以下信息
    CHANGE MASTER TO
    MASTER_HOST='192.168.95.2',
    MASTER_USER='testuser',
    MASTER_PASSWORD='testpass',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='master1-bin.000005',
    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;  #启动复制

3、查看同步状态

show slave statusG

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.95.2
Master_User: laobai
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000003
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000005
Relay_Log_Pos: 531
Relay_Master_Log_File: master1-bin.000003
Slave_IO_Running: Yes #同步状态正常
Slave_SQL_Running: Yes

三、级联架构实现

1、主服务器配置

[root@centos7 ~]#vim /etc/my.cnf    
[mysqld]
log_bin       #开启二进制
server_id=1   #主服务器的ID号
MariaDB [(none)]> grant replication slave on *.* to bai@'192.168.95.%' identified by '123456';

MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 395 |
+-------------------+-----------+

2、中继服务器配置

[mysqld]
log_bin #注意，级联架构中中继从节点一定得开二进制日志功能
server_id=2
log_slave_updates #此项的作用是将从服务器更改的数据记录到二进制文件中

[root@localhost ~]# systemctl restart mariadb

添加主服务器并同步数据

MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.95.2',
-> MASTER_USER='bai',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000001',
-> MASTER_LOG_POS=395,
-> MASTER_CONNECT_RETRY=10;

MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.95.2
Master_User: bai
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 395
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #同步成功

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+

MariaDB [(none)]> grant replication slave on *.* to bai@'192.168.95.%' identified by '123456';

3、从服务器配置

[mysqld]
read_only=ON
server_id=3   
[root@laobai ~]#service mysqld restart

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.95.5',
    ->     MASTER_USER='bai',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_PORT=3306,
    ->     MASTER_LOG_FILE='mariadb-bin.000001',
    ->     MASTER_LOG_POS=245,
    ->     MASTER_CONNECT_RETRY=10;

mysql> start slave;

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.95.5
                  Master_User: bai
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 395
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 542
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4、从服务器2配置

[mysqld]
read_only=ON
server_id=4
[root@localhost ~]# service mysqld restart

和中继服务器同步
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.95.5',
    ->     MASTER_USER='bai',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_PORT=3306,
    ->     MASTER_LOG_FILE='mariadb-bin.000001',
    ->     MASTER_LOG_POS=245,
    ->     MASTER_CONNECT_RETRY=10;

mysql> start slave;
mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.95.5
                  Master_User: bai
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 395
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 542
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5、测试：我们在主服务器上导入hellodb 数据库看看从服务器和中继服务器是否有同步

[root@centos7 ~]#mysql < hellodb_innodb.sql

中继服务器

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

从服务器

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| test               |
+--------------------+

同步完成

四、主主复制

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

1、主服务器1

[mysqld]
log_bin
binlog_format=ROW
log-basename=master1
server_id=1
relay_log=relay-log
relay_log_index=relay-log.index
auto_increment_offset=1  #自增长字段从1开始
auto_increment_increment=2  #每次增长2，也就是说master1节点写入的数据的id字段全部是奇数
[root@master ~]# systemctl start mariadb
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| master1-bin.000001 |     27033 |
| master1-bin.000002 |    942126 |
| master1-bin.000003 |       245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master2-bin.000003',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

主服务器2

[mysqld]
log_bin
binlog_format=ROW
log-basename=master2
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
auto_increment_offset=2  #自增长字段从1开始
auto_increment_increment=2  #每次增长2，也就是说master1节点写入的数据的id字段全部是偶数
[root@master2 ~]# systemctl start mariadb
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| master2-bin.000001 |     27036 |
| master2-bin.000002 |    942126 |
| master2-bin.000003 |       245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master1-bin.000003',
    -> MASTER_LOG_POS=245,
    -> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;

测试

在master1上创建表，增加数据
MariaDB [(none)]> CREATE DATABASE db1;
MariaDB [(none)]> use db1
MariaDB [db1]> CREATE TABLE t1(id INT(2) AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
MariaDB [db1]> INSERT t1(name) VALUES ('tom');
MariaDB [db1]> INSERT t1(name) VALUES ('maria'); 
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  3 | maria |
+----+-------+
在master2上增加数据
MariaDB [db1]> INSERT t1(name) VALUES ('jerry');
MariaDB [db1]> INSERT t1(name) VALUES ('tony'); 
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  3 | maria |
|  4 | jerry |
|  6 | tony  |

五、半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失；半同步复制的机制是在有多台从服务器时，只要有一台从服务器同步到主服务器数据即可，服务器会通知客户端已经完成同步，无需等到完全同步完成后才通知客户端。

实现步骤

1、先搭建一个主从架构，

[mysqld]
log_bin                                                                                      
server_id=1
[root@centos7 ~]#systemctl restart mariadb
MariaDB [(none)]> grant replication slave on *.* to laobai@'192.168.95.5' identified by '123456';
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     26762 |
| master-bin.000002 |    921736 |
| master-bin.000003 |       398 |
+-------------------+-----------+
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@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin
server_id=2
log_slave_updates
[root@slave1 ~]# systemctl restart mariadb

MariaDB [(none)]> CHANGE MASTER TO     MASTER_HOST='192.168.95.2',     MASTER_USER='laobai',     MASTER_PASSWORD='123456',     MASTER_PORT=3306,     MASTER_LOG_FILE='master-bin.000003',     MASTER_LOG_POS=398,     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 |
+--------------------+-----------+
| mariadb-bin.000001 |       264 |
| mariadb-bin.000002 |       343 |
| mariadb-bin.000003 |       397 |
+--------------------+-----------+
MariaDB [(none)]> grant replication slave on *.* to laobai@'192.168.95.3' identified by '123456';

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.95.2
Master_User: laobai
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 477
Relay_Log_File: mariadb-relay-bin.000004
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3、节点2

[mysqld]
read_only=ON
server_id=3
[root@slave2 ~]# systemctl restart mariadb

mysql> CHANGE MASTER TO     MASTER_HOST='192.168.95.5',     MASTER_USER='laobai',     MASTER_PASSWORD='123456',     MASTER_PORT=3306,     MASTER_LOG_FILE='mariadb-bin.000003',     MASTER_LOG_POS=245,     MASTER_CONNECT_RETRY=10;Ctrl-C -- exit!
mysql> start slave;
mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.95.5
                  Master_User: laobai
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000004
          Read_Master_Log_Pos: 245
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 392
        Relay_Master_Log_File: mariadb-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

相关阅读:
MySQL数据库基准压力测试工具之MySQLSlap使用实例
 WPF水珠效果按钮组
 获取用户Ip地址通用方法常见安全隐患(HTTP_X_FORWARDED_FOR)
leaflet的入门开发（一）
linux 安装pip 和python3
python模拟Get请求保存网易歌曲的url
Phalcon 上下文编码（Contextual Escaping）
（七十七）地理编码与反地理编码
 设计模式简单介绍
 windows下安装Jenkins
原文地址：https://www.cnblogs.com/huxiaojun/p/9215517.html