一.MySQL集群Cluster
1.MySQL主从复制
(1)架构图
(2)实例:在已经存在的MySQL服务器上增加从节点(增加第二、第三个从节点等等,步骤与之相同)
#配置主节点
[root@matter ~]# yum -y install mysql-server
[root@matter ~]# mkdir -p /data/mysql/logbin
[root@matter ~]# chown -R mysql.mysql /data/mysql/logbin
[root@matter ~]# vim /etc/my.cnf
[mysqld]
server-id=48
log-bin = /data/mysql/logbin/mysql-bin
[root@matter ~]# systemctl start mysqld
#完全备份
[root@master ~]# mysqldump -A -F --master-data=1 --single-transaction > /data/all.sql
[root@rocky8 ~]# ll /data/all.sql
-rw-r--r-- 1 root root 1283652 May 28 16:55 /data/all.sql
[root@rocky8 ~]# ll /data/mysql/logbin
total 24
-rw-r----- 1 mysql mysql 180 May 28 16:54 mysql-bin.000001
-rw-r----- 1 mysql mysql 204 May 28 16:55 mysql-bin.000002
-rw-r----- 1 mysql mysql 11601 May 28 17:10 mysql-bin.000003
-rw-r----- 1 mysql mysql 108 May 28 16:55 mysql-bin.index
#创建从节点连接的用户,并授予权限
[root@slave ~]#mysql
mysql> create user repluser@"10.0.0.%" identified by "123456";
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@"10.0.0.%";
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#将完全备份拷贝到从节点
[root@master ~]# scp /data/all.sql 10.0.0.58:/data
root@10.0.0.58's password:
all.sql 100% 1207KB 103.4MB/s 00:00
#配置从节点
[root@slave ~]# yum -y install mysql-server
[root@slave ~]# ll /data
total 1208
-rw-r--r-- 1 root root 1235904 May 28 12:37 all.sql
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server-id=18
read-only
[root@slave ~]# systemctl start mysqld
[root@rocky8 ~]# vim /data/all.sql
-- Position to start replication or point-in-time recovery from
CHANGE MASTER TO
MASTER_HOST='10.0.0.28',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;
--
-- Current Database: `mysql`
[root@slave ~]#mysql
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/all.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1005
Relay_Log_File: rocky8-relay-bin.000002
Relay_Log_Pos: 1174
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes #IO和SQL线程全是yes
Slave_SQL_Running: Yes
...省略...
1 row in set, 1 warning (0.00 sec)
[root@master ~]#mysql
mysql> show processlist;
+----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1641 | Waiting on empty queue | NULL |
| 17 | repluser | 10.0.0.38:36898 | NULL | Binlog Dump | 967 | Source has sent all binlog to replica; waiting for more updates | NULL |
| 20 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+
3 rows in set, 1 warning (0.00 sec)
(3)实例:当master服务器宕机,提升一个slave成为新的master
#找到一个从节点的数据库是最新的,让它成为新的master(看实际情况,自己到目录中查看)
[root@rocky8 ~]# mysqlbinlog /var/lib/mysql/rocky8-relay-bin.000002
#新的master修改配置文件,关闭read-only配置
[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
server-id=38
read-only=off
log-bin=/data/mysql/logbin/mysql-bin
#清除旧的的master复制信息
[root@rocky8 ~]#mysql
mysql> set global read_only=off;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#在新的master上完全备份
[root@rocky8 ~]# mysqldump -A -F --single-transaction --source-data=1 > /data/all.sql
[root@rocky8 ~]# scp /data/all.sql 10.0.0.48:/data
#分析旧的master 的二进制日志,将未同步到至新master的二进制日志导出来,恢复到新master,尽可能恢复数据
#其它所有slave重新还原数据库,指向新的master
[root@centos8 ~]# vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.38',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157;
[root@centos8 ~]# mysql
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.01 sec)
mysql> source /data/all.sql;
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.38
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 157
Relay_Log_File: rocky8-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略...
2.MySQL级联复制
(1)架构图
(2)实例:实现中间slave节点能将master的二进制日志在本机进行数据库更新,并且也同时更新本机的二进制
#配置主节点
[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
server-id=28
log-bin=/data/mysql/logbin/mysql-bin
[root@rocky8 ~]# mkdir -p /data/mysql/logbin
[root@rocky8 ~]# chown -R mysql.mysql /data/mysql/logbin
[root@rocky8 ~]# systemctl restart mysqld
[root@rocky8 ~]# mysqldump -A -F --source-data=1 --single-transaction > /data/all.sql
[root@rocky8 ~]# ll /data/mysql/logbin
total 12
-rw-r----- 1 mysql mysql 761 May 28 18:43 mysql-bin.000001
-rw-r----- 1 mysql mysql 157 May 28 18:43 mysql-bin.000002
-rw-r----- 1 mysql mysql 72 May 28 18:43 mysql-bin.index
[root@rocky8 ~]# scp /data/all.sql 10.0.0.38:/data
The authenticity of host '10.0.0.38 (10.0.0.38)' can't be established.
ECDSA key fingerprint is SHA256:fYPMd1SvHHmAWvRku0PSSWIJdi2FFU8wxZkCemRBVGM.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.38' (ECDSA) to the list of known hosts.
root@10.0.0.38's password:
all.sql 100% 1254KB 40.5MB/s 00:00
[root@rocky8 ~]# scp /data/all.sql 10.0.0.48:/data
The authenticity of host '10.0.0.48 (10.0.0.48)' can't be established.
ECDSA key fingerprint is SHA256:5Nqyq4SLQ98dBHyAbwbbuEPMfWB1L6Ul/upm+yno8KE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.48' (ECDSA) to the list of known hosts.
root@10.0.0.48's password:
all.sql 100% 1254KB 39.9MB/s 00:00
[root@rocky8 ~]# mysql
mysql> create user repluser@"10.0.0.%" identified by "123456";
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to repluser@"10.0.0.%";
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'repluser'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 450 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#配置级联从节点
[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
server-id=38
read-only
log_slave_updates
[root@rocky8 ~]# vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.28',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=450;
[root@rocky8 ~]# mysql
mysql> set sql_log_bin=0;
mysql> source /data/all.sql;
mysql> set sql_log_bin=1;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 450
Relay_Log_File: rocky8-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略...
mysql> ALTER USER 'repluser'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 180 | No |
| binlog.000002 | 455 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
#配置第二个从节点
[root@centos8 ~]# vim /etc/my.cnf
[mysqld]
server-id=48
read-only
[root@centos8 ~]# vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.38',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=455;
[root@centos8 ~]# mysql
mysql> set sql_log_bin=0;
mysql> source /data/all.sql;
mysql> set sql_log_bin=1;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.38
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 455
Relay_Log_File: centos8-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略...
3.MySQL主主复制
(1)架构图
(2)实例:
#配置第一个master节点
[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
server-id=28
log-bin
auto_increment_offset=1
auto_increment_increment=2
[root@rocky8 ~]# systemctl restart mysqld
[root@rocky8 ~]# mysql
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------+-----------+-----------+
| rocky8-bin.000001 | 180 | No |
| rocky8-bin.000002 | 157 | No |
+-------------------+-----------+-----------+
2 rows in set (0.00 sec)
#配置第二个master节点
[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
server-id=38
log-bin
auto-increment-offset=2
auto-increment-increment=2
log-bin=/data/mysql/logbin/mysql-bin
[root@rocky8 ~]# systemctl restart mysqld
[root@rocky8 ~]# mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.28',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='rocky8-bin.000002', MASTER_LOG_POS=157;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#再配置第一个master节点
[root@rocky8 ~]# mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.38',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.38
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 157
Relay_Log_File: rocky8-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略...
#查看第二个master节点状态
[root@rocky8 ~]# mysql
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rocky8-bin.000002
Read_Master_Log_Pos: 157
Relay_Log_File: rocky8-relay-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: rocky8-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略...
#在第一个节点上执行
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.00 sec)
#在第二个节点上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db1
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
#两个节点同时插入数据
mysql> insert t1(id,name) values(3,'yue');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | bai |
| 2 | fu |
| 3 | yue |
+----+------+
3 rows in set (0.00 sec)
#两个节点同时创建数据库,发生复制冲突,不再同步
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.38
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1642
Relay_Log_File: rocky8-relay-bin.000004
Relay_Log_Pos: 660
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
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: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000004, end_log_pos 1424. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 1167
Relay_Log_Space: 1393
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: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000004, end_log_pos 1424. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
#修复冲突(不建议使用,主主同步 也要使用主从模式)
#两个节点同时停止服务
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1; #几个错误就等于几
mysql> start slave;
4.MySQL半同步复制
(1)架构图
(2)实例:(在主从基础上实现)
#配置主节点
#安装插件
[root@rocky8 ~]# mysql
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show plugins;
+---------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+--------------------+---------+
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+---------------------------------+----------+--------------------+--------------------+---------+
#写在配置文件中启动(一定先安装再启动)
[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
server-id=28
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=3000
[root@rocky8 ~]# mysql
mysql> select @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| 1 |
+--------------------------------+
1 row in set (0.00 sec)
#配置从节点
[root@rocky8 ~]# mysql
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show plugins;
+---------------------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+-------------------+---------+
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+---------------------------------+----------+--------------------+-------------------+---------+
#写在配置文件中启动
[root@rocky8 ~]# vim /etc/my.cnf
[mysqld]
server-id=38
read-only
rpl_semi_sync_slave_enabled
[root@rocky8 ~]# mysql
mysql> select @@rpl_semi_sync_slave_enabled;
+-------------------------------+
| @@rpl_semi_sync_slave_enabled |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
#在主节点查看状态
[root@rocky8 ~]# mysql
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 8 |
| Rpl_semi_sync_master_no_times | 2 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 499 |
| Rpl_semi_sync_master_tx_wait_time | 1498 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
二.MySQL中间件代理服务
1.Mycat
(1)架构图
(2)实例:(在主从复制基础上)
#配置主节点(会同步给从节点)
[root@rocky8 ~]# mysql
mysql> create user 'bai'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on mycat.* to 'bai'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
#配置Mycat服务器(Mycat-server-1.6.7.6)
#安装Mycat-1.6.7.6
[root@rocky8 ~]# yum -y install java
[root@rocky8 ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@rocky8 ~]# mkdir /apps
[root@rocky8 ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps
#配置环境变量
[root@rocky8 ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@rocky8 ~]# . /etc/profile.d/mycat.sh
[root@rocky8 ~]# echo $PATH
/apps/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
#启动Mycat
[root@rocky8 ~]# mycat strat
#修改配置文件(可以修改Mycat的端口号和默认用户名,密码)
[root@rocky8 ~]# vim /apps/mycat/conf/server.xml
...省略...
<property name="serverPort">3306</property>
...省略...
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
[root@rocky8 ~]# vim /apps/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema=**"false"** sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database=**"mycat"** />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver=**"native"** switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url=**"10.0.0.28:3306"** user=**"bai"** password=**"123456"**>
<readHost host="host2" url=**"10.0.0.48:3306"** user=**"bai"** password=**"123456"** />
</writeHost>
</dataHost>
</mycat:schema>
#从新启动Mycat
[root@rocky8 ~]# mycat stop
[root@rocky8 ~]# mycat strat
#使用客户端登录Mycat(使用server.xml中默认的用户名密码)
[root@centos8 ~]# mysql -uroot -p123456 -h10.0.0.38
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | yue |
+----+------+
1 row in set (0.00 sec)
mysql> insert t1(name) values ('bai');
Query OK, 1 row affected (0.02 sec)
#在主节点和从节点监控日志
[root@rocky8 ~]# tail -f /var/lib/mysql/rocky8.log
2024-05-29T09:05:42.408786Z 8 Query select user()
2024-05-29T09:05:52.409353Z 8 Query select user()
2024-05-29T09:06:02.409095Z 8 Query select user()
2024-05-29T09:06:12.409268Z 8 Query select user()
2024-05-29T09:06:22.409255Z 8 Query select user()
2024-05-29T09:06:32.408804Z 8 Query select user()
2024-05-29T09:06:42.408681Z 8 Query select user()
2024-05-29T09:06:52.408661Z 8 Query select user()
2024-05-29T09:07:02.408750Z 8 Query select user()
2024-05-29T09:07:12.408888Z 8 Query select user()
2024-05-29T09:07:20.113893Z 8 Query insert t1(name) values ('bai')
[root@centos8 ~]# tail -f /var/lib/mysql/centos8.log
2024-05-29T09:04:32.318562Z 10 Query select user()
2024-05-29T09:04:42.318339Z 10 Query select user()
2024-05-29T09:04:52.318394Z 10 Query select user()
2024-05-29T09:05:02.317888Z 10 Query select user()
2024-05-29T09:05:12.318091Z 10 Query select user()
2024-05-29T09:05:22.317937Z 10 Query select user()
2024-05-29T09:05:32.318200Z 10 Query select user()
2024-05-29T09:05:42.317606Z 10 Query select user()
2024-05-29T09:05:52.318021Z 10 Query select user()
2024-05-29T09:06:02.317843Z 10 Query select user()
2024-05-29T09:06:12.317873Z 10 Query select user()
2024-05-29T09:06:22.317885Z 10 Query select user()
2024-05-29T09:06:32.317588Z 10 Query select user()
2024-05-29T09:06:37.814130Z 10 Query select * from t1
三.MySQL高可用
1.MySQL高可用解决方案
(1)
- MHA:Master High Availability,对主节点进行监控,可实现自动故障转移至其它从节点;通过提升某一从节点为新的主节点,基于主从复制实现,还需要客户端配合实现,目前MHA主要支持一主多从的架构,要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,另外一台充当从库,出于机器成本的考虑,淘宝进行了改造,目前淘宝TMHA已经支持一主一从。
- GR(Group Replication):MySQL官方提供的组复制技术(MySQL 5.7.17引入的技术),基于原生复制技术Paxos算法,实现了多主更新,复制组由多个server成员构成,组中的每个server可独立地执行事务,但所有读写事务只在冲突检测成功后才会提交
- Galera Cluster:wsrep(MySQL extended with the Write Set Replication)
通过wsrep协议在全局实现复制;任何一节点都可读写,不需要主从复制,实现多主读写
2.MHA
(1)原理:
- MHA利用 SELECT 1 As Value 指令判断master服务器的健康性,一旦master 宕机,MHA 从宕机崩溃的master保存二进制日志事件(binlog events)
- 识别含有最新更新的slave
- 应用差异的中继日志(relay log)到其他的slave
- 应用从master保存的二进制日志事件(binlog events)到所有slave节点
- 提升一个slave为新的master
- 使其他的slave连接新的master进行复制
- 故障服务器自动被剔除集群(masterha_conf_host),将配置信息去掉
- 旧的Master的 VIP 漂移到新的master上,用户应用就可以访问新的Master
3.Galera Cluster
(1)工作原理:
Galera Cluster:集成了Galera插件的MySQL集群,是一种新型的,数据不共享的,高度冗余的高可用方案,目前Galera Cluster有两个版本,分别是Percona Xtradb Cluster及MariaDB Cluster,Galera本身是具有多主特性的,即采用multi-master的集群架构,是一个既稳健,又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案