今天我们继续来讲Mysql方面的知识。

04 mysql主从复制架构及实现

实战:主主复制
[root@master ~]# systemctl stop mariadb.service
[root@slave ~]# systemctl stop mariadb.service
[root@master ~]# rm -rf /var/lib/mysql/*
[root@slave ~]# rm -rf /var/lib/mysql/*
[root@master ~]# vim /etc/my.cnf
添加
log-bin=master-bin
relay_log=relay-log
server-id=1
innodb_file_per_table=ON
skip_name_resolve=ON

auto_increment_offset=1
auto_increment_increment=2

[root@slave ~]# vim /etc/my.cnf
添加
log-bin=master-bin
relay_log=relay-log
server-id=5
innodb_file_per_table=ON
skip_name_resolve=ON

auto_increment_offset=2
auto_increment_increment=2

[root@master ~]# systemctl start mariadb.service
[root@master ~]# mysql
#确保relay_log值为relay-log
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'relay_log';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| relay_log | relay-log |
+---------------+-----------+
#确保log_bin值为ON
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
[root@slave ~]# systemctl start mariadb.service
[root@slave ~]# mysql
#确保relay_log值为relay-log
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'relay_log';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| relay_log | relay-log |
+---------------+-----------+
#确保log_bin值为ON
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+

#主节点
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 507 | | |
+-------------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=507;
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.132
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 507
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000003
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.132
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 507
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

#从节点
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 507 | | |
+-------------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=507;
MariaDB [(none)]> SHOW SLAVE STATUS \G _USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 507
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 507
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试1:主结点创建一个数据库mydb
#主节点
MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.132
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 507
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

#从结点:
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 595
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 618
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+

结果:从节点已复制主节点的数据库

测试2:从结点在主节点创建的数据库mydb中创建数据表tb1
#从结点:
MariaDB [(none)]> USE mydb;
Database changed
MariaDB [mydb]> CREATE TABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
MariaDB [mydb]> DESC tb1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
MariaDB [mydb]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 659 | | |
+-------------------+----------+--------------+------------------+
#主结点
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.132
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 659
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 682
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [(none)]> use mydb;
MariaDB [mydb]> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| tb1 |
+----------------+
1 row in set (0.00 sec)

MariaDB [mydb]> DESC tb1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES('Yang Kang'),('Yang Guo'),('Yang Yanzhao');MariaDB [mydb]> SELECT * FROM tb1;
+----+--------------+
| id | name |
+----+--------------+
| 1 | Yang Kang |
| 3 | Yang Guo |
| 5 | Yang Yanzhao |
+----+--------------+

#从结点
MariaDB [mydb]> SELECT * FROM tb1;
+----+--------------+
| id | name |
+----+--------------+
| 1 | Yang Kang |
| 3 | Yang Guo |
| 5 | Yang Yanzhao |
+----+--------------+
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES('Zhu Yuanzhang'),('Zhu Di'),('Zhu Yue');
MariaDB [mydb]> SELECT * FROM tb1;
+----+---------------+
| id | name |
+----+---------------+
| 1 | Yang Kang |
| 3 | Yang Guo |
| 5 | Yang Yanzhao |
| 6 | Zhu Yuanzhang |
| 8 | Zhu Di |
| 10 | Zhu Yue |
+----+---------------+

#主节点
MariaDB [mydb]> SELECT * FROM tb1;
+----+---------------+
| id | name |
+----+---------------+
| 1 | Yang Kang |
| 3 | Yang Guo |
| 5 | Yang Yanzhao |
| 6 | Zhu Yuanzhang |
| 8 | Zhu Di |
| 10 | Zhu Yue |
+----+---------------+
MariaDB [mydb]> INSERT INTO tb1 (name) VALUES('Mao Ze');
Query OK, 1 row affected (0.02 sec)

MariaDB [mydb]> SELECT * FROM tb1;
+----+---------------+
| id | name |
+----+---------------+
| 1 | Yang Kang |
| 3 | Yang Guo |
| 5 | Yang Yanzhao |
| 6 | Zhu Yuanzhang |
| 8 | Zhu Di |
| 10 | Zhu Yue |
| 11 | Mao Ze |
+----+---------------+

测试结果:主节点从从结点同步数据成功

实战:半同步复制
[root@master ~]# systemctl stop mariadb.service
[root@slave ~]# systemctl stop mariadb.service
[root@master ~]# rm -rf /var/lib/mysql/*
[root@slave ~]# rm -rf /var/lib/mysql/*
[root@master ~]# vim /etc/my.cnf
添加
log-bin=master-bin
server-id=1
innodb_file_per_table=ON
skip_name_resolve=ON
[root@slave ~]# vim /etc/my.cnf
添加
relay_log=relay-log
server-id=5
innodb_file_per_table=ON
skip_name_resolve=ON
[root@slave ~]# systemctl start mariadb.service
[root@master ~]# systemctl start mariadb.service

#主服务器
[root@master ~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 497 | | |
+-------------------+----------+--------------+------------------+

#从服务器
[root@slave ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=497;
MariaDB [(none)]> START SLAVE; _USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 497
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [(none)]> STOP SLAVE;

#主节点
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| 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 | OFF |
| 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 |
+--------------------------------------------+-------+
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)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+------------------------------------+-------+
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+------------------------------------+-------+
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 497
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

#主服务器
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%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 |
+--------------------------------------------+-------+
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> USE mydb;
MariaDB [mydb]> CREATE TABLE tb1 (id int,name char(30));
MariaDB [mydb]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1759 |
| Rpl_semi_sync_master_net_wait_time | 3518 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 2437 |
| Rpl_semi_sync_master_tx_wait_time | 4875 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+

实战:复制过滤器
#从服务器
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'replicate%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| replicate_annotate_row_events | OFF |
| replicate_do_db | |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
+----------------------------------+-----------+
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> SET GLOBAL replicate_do_db=mydb;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'replicate%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| replicate_annotate_row_events | OFF |
| replicate_do_db | mydb |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
+----------------------------------+-----------+
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 682
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb

#主服务器
MariaDB [mydb]> CREATE DAMariaDB [mydb]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+

#从服务器
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+

#主服务器
MariaDB [mydb]> INSERT INTO tb1 VALUES (1,"a");

#从服务器
MariaDB [(none)]> USE mydb;
MariaDB [mydb]> SELECT * FROM tb1;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+

实战:清理mysql日志
MariaDB [mydb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 30334 |
| master-bin.000002 | 1038814 |
| master-bin.000003 | 957 |
+-------------------+-----------+
MariaDB [mydb]> PURGE BINARY LOGS TO 'master-bin.000002';
MariaDB [mydb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000002 | 1038814 |
| master-bin.000003 | 957 |
+-------------------+-----------+
MariaDB [mydb]> PURGE BINARY LOGS TO 'master-bin.000003';
Query OK, 0 rows affected (0.02 sec)

MariaDB [mydb]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000003 | 957 |
+-------------------+-----------+
[root@master ~]# ls /var/lib/mysql/
aria_log.00000001 ib_logfile1 mysql testdb
aria_log_control master-bin.000003 mysql.sock
ibdata1 master-bin.index performance_schema
ib_logfile0 mydb test
注意:日志清理后,其对应的文件也会被删除

本文出自 “追梦” 博客,请务必保留此出处http://sihua.blog.51cto.com/377227/1875526

因为水平有限,难免有疏忽或者不准确的地方,希望大家能够直接指出来,我会及时改正。一切为了知识的分享。

后续会有更多的精彩的内容分享给大家。