mysql组复制实现读写分离
前言
数据库读写分离是一种常见的数据库优化技术，在高并发场景下可以显著提升系统的性能和稳定性。其原理是将数据库的读、写操作分别由不同的数据库服务器处理，使得读和写可以并行进行，从而减轻单台数据库服务器的负担。
具体来说，在数据库读写分离的架构中，通常会有一个主数据库服务器和若干个从数据库服务器。主数据库服务器主要负责写操作，从数据库服务器则负责读操作。当应用程序需要进行读操作时，可以直接从从数据库服务器中读取数据，从而极大地减小了主数据库服务器的负担。同时，在主数据库服务器进行写操作时，也不会影响从数据库服务器的读操作。
当然，为了保证数据的一致性，从数据库服务器需要及时地同步主数据库服务器的数据更新。常见的同步方式包括主从同步和双向同步。
总之，数据库读写分离是一种常用的优化数据库性能和提高系统稳定性的技术，特别是在高并发场景下，更是有着不可替代的作用。
一、mysql组复制是什么？
组复制（Group Replication）是MySQL 5.7版本中新增的高可用性解决方案，它基于MySQL的复制机制，通过使用多主复制来提供高可用性和灵活性。
组复制实现了Master-Master的复制模式，多个MySQL Server之间可以进行相互复制，因此当一个或者多个节点失效时，系统仍然可以继续工作。此外，组复制还提供了更丰富、更复杂的拓扑结构和更灵活的失败处理方式，比如可以自定义节点优先级顺序以及重新选举算法。
组复制的实现基于MySQL的GTID（全局事务标识符）技术，每个事务在整个集群中都拥有唯一的标识，因此在进行读写操作的时候，可以保证数据的一致性和正确性。组复制还提供了自适应的一致性模式，在网络异常的情况下，可以自动选择合适的一致性模式来保证数据的一致性。
总之，组复制是一种非常强大的高可用性解决方案，它提供了更加灵活和可靠的数据复制和故障恢复机制，可以满足各种不同的应用场景需求。
二、组复制的实现
前提条件：
三个节点都需要解析
192.168.56.170 server1
192.168.56.171 server2
192.168.56.172 server3
停止数据库，并删除/data/mysql目录下的数据
server1：
[root@server1 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server1:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_ip_whitelist="192.168.56.0/24,127.0.0.1/8"
group_replication_allow_local_disjoint_gtids_join=1
#初始化
[root@server1 mysql]# mysqld --initialize --user=mysql
[root@server1 mysql]# /etc/init.d/mysqld start
[root@server1 mysql]# mysql -p
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.05 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1d9921fc-8689-11ed-96fd-000c291fbda9 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
server2:
[root@server2 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server2:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_ip_whitelist="192.168.56.0/24,127.0.0.1/8"
group_replication_allow_local_disjoint_gtids_join=1
[root@server2 mysql]# mysqld --initialize --user=mysql
[root@server2 mysql]# /etc/init.d/mysqld start
[root@server2 mysql]# mysql -p
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.73 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1d9921fc-8689-11ed-96fd-000c291fbda9 | server1 | 3306 | ONLINE |
| group_replication_applier | 5d53f49f-8689-11ed-bcbf-000c29eccb66 | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
server3:
[root@server3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server3:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_ip_whitelist="192.168.56.0/24,127.0.0.1/8"
group_replication_allow_local_disjoint_gtids_join=1
[root@server3 ~]# mysqld --initialize --user=mysql
[root@server3 ~]# /etc/init.d/mysqld start
[root@server3 ~]# mysql -p
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.10 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 146df1e6-868a-11ed-aaf9-000c29dcf199 | server3 | 3306 | ONLINE |
| group_replication_applier | 1d9921fc-8689-11ed-96fd-000c291fbda9 | server1 | 3306 | ONLINE |
| group_replication_applier | 5d53f49f-8689-11ed-bcbf-000c29eccb66 | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
测试：
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)
三个节点都可以读写数据并同步。
三、读写分离
首先确认mysql组复制集群是可用的
server4：
安装代理软件：
[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
[root@server4 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
...
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.168.56.170:3306,192.168.56.171:3306,192.168.56.172:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 192.168.56.172:3306,192.168.56.171:3306,192.168.56.170:3306
routing_strategy = first-available
[root@server4 mysqlrouter]# systemctl start mysqlrouter.service
[root@server4 mysqlrouter]# netstat -antlp |grep :700
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 7759/mysqlrouter
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 7759/mysqlrouter
测试：
在mysql集群中创建测试用户
mysql> grant all on test.* to wxh@'%' identified by 'westos';
安装mysql客户端
[root@server4 mysqlrouter]# yum install -y mariadb
[root@server4 mysqlrouter]# mysql -h 192.168.56.173 -P 7001 -u wxh -pwestos
[root@server1 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 10540 mysql 32u IPv6 49572 0t0 TCP *:mysql (LISTEN)
mysqld 10540 mysql 74u IPv6 50002 0t0 TCP server1:mysql->192.168.56.173:38266 (ESTABLISHED)
[root@server4 mysqlrouter]# mysql -h 192.168.56.173 -P 7002 -u wxh -pwestos
[root@server3 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 8047 mysql 22u IPv6 40339 0t0 TCP *:mysql (LISTEN)
mysqld 8047 mysql 55u IPv6 40738 0t0 TCP server3:mysql->server4:58822 (ESTABLISHED)
2165
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
