1、MySQL Ver 8.0.21主從復(fù)制及主主復(fù)制的實(shí)現(xiàn)
1.1 主節(jié)點(diǎn)
[root@centos8-8 ~]# yum -y install mysql-server
[root@centos8-8 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log_bin
server_id=8
[root@centos8-8 ~]# systemctl restart mysqld.service
[root@centos8-8 ~]# mysql
#查看二進(jìn)制日志文件和位置
mysql> show master logs;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| centos8-8-bin.000001 | 179 | No |
| centos8-8-bin.000002 | 179 | No |
| centos8-8-bin.000003 | 179 | No |
| centos8-8-bin.000004 | 179 | No |
| centos8-8-bin.000005 | 156 | No |
+----------------------+-----------+-----------+
5 rows in set (0.00 sec)
#創(chuàng)建復(fù)制用戶并添加slave復(fù)制權(quán)限
mysql> create user 'repluser'@'192.168.108.%' identified by 'ddq.com';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repluser'@'192.168.108.%';
Query OK, 0 rows affected (0.01 sec)
1.2 從節(jié)點(diǎn)
[root@centos8-18 ~]# yum -y install mysql-server
[root@centos8-18 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=18
[root@centos8-18 ~]# systemctl restart mysqld.service
[root@centos8-18 ~]# mysql
mysql> change master to
-> master_host='192.168.108.8',
-> master_user='repluser',
-> master_password='ddq.com',
-> master_port=3306,
-> master_log_file='centos8-8-bin.000005',
-> master_log_pos=156,
-> master_connect_retry=10;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.108.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: centos8-8-bin.000005
Read_Master_Log_Pos: 691
Relay_Log_File: centos8-18-relay-bin.000002
Relay_Log_Pos: 863
Relay_Master_Log_File: centos8-8-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 691
Relay_Log_Space: 1077
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 8
Master_UUID: 5b058811-f8dd-11eb-994d-52540099d568
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
- 到這一步已完成主從復(fù)制
1.3 實(shí)現(xiàn)主主復(fù)制
#在第一個(gè)主節(jié)點(diǎn)上實(shí)現(xiàn)
[root@centos8-8 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log_bin
server_id=8
auto_increment_offset=1
auto_increment_increment=2
[root@centos8-8 ~]# systemctl restart mysqld.service
[root@centos8-8 ~]# mysql
mysql> show master logs;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| centos8-8-bin.000001 | 179 | No |
| centos8-8-bin.000002 | 179 | No |
| centos8-8-bin.000003 | 179 | No |
| centos8-8-bin.000004 | 179 | No |
| centos8-8-bin.000005 | 714 | No |
| centos8-8-bin.000006 | 156 | No |
+----------------------+-----------+-----------+
6 rows in set (0.00 sec)
#在第二個(gè)主節(jié)點(diǎn)上實(shí)現(xiàn)
[root@centos8-18 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=18
log_bin
auto_increment_offset=2
auto_increment_increment=2
[root@centos8-18 ~]# systemctl restart mysqld.service
[root@centos8-18 ~]# mysql
mysql> show master logs;
+-----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------------+-----------+-----------+
| centos8-18-bin.000001 | 156 | No |
+-----------------------+-----------+-----------+
1 row in set (0.00 sec)
#在第一個(gè)主節(jié)點(diǎn)上實(shí)現(xiàn)
mysql> change master to
-> master_host='192.168.108.18',
-> master_user='repluser',
-> master_password='ddq.com',
-> master_port=3306,
-> master_log_file='centos8-18-bin.000001',
-> master_log_pos=156,
-> master_connect_retry=10;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.108.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: centos8-18-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: centos8-8-relay-bin.000002
Relay_Log_Pos: 329
Relay_Master_Log_File: centos8-18-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 542
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 18
Master_UUID: 5507bc09-f8d7-11eb-8f0f-5254005a4312
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
#在主節(jié)點(diǎn)一上創(chuàng)建數(shù)據(jù)庫(kù)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master logs;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| centos8-8-bin.000001 | 179 | No |
| centos8-8-bin.000002 | 179 | No |
| centos8-8-bin.000003 | 179 | No |
| centos8-8-bin.000004 | 179 | No |
| centos8-8-bin.000005 | 714 | No |
| centos8-8-bin.000006 | 343 | No |
+----------------------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.108.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: centos8-18-bin.000001
Read_Master_Log_Pos: 350
Relay_Log_File: centos8-8-relay-bin.000002
Relay_Log_Pos: 329
Relay_Master_Log_File: centos8-18-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 350
Relay_Log_Space: 542
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 18
Master_UUID: 5507bc09-f8d7-11eb-8f0f-5254005a4312
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
#在主節(jié)點(diǎn)二上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.108.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: centos8-8-bin.000006
Read_Master_Log_Pos: 343
Relay_Log_File: centos8-18-relay-bin.000006
Relay_Log_Pos: 515
Relay_Master_Log_File: centos8-8-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 343
Relay_Log_Space: 729
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 8
Master_UUID: 5b058811-f8dd-11eb-994d-52540099d568
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
mysql> show master logs;
+-----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------------+-----------+-----------+
| centos8-18-bin.000001 | 350 | No |
+-----------------------+-----------+-----------+
1 row in set (0.00 sec)
2、xtrabackup實(shí)現(xiàn)全量+增量+binlog恢復(fù)數(shù)據(jù)庫(kù)
2.1 安裝Percona XtraBackup
percona提供的mysql數(shù)據(jù)庫(kù)備份工具拙绊,惟一開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫(kù)進(jìn)行熱備的工具
手冊(cè):https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
下載: https://www.percona.com/downloads/
[root@centos8-8 ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@centos8-8 ~]# percona-release enable-only tools release
[root@centos8-8 ~]# yum install percona-xtrabackup-80
[root@centos8-8 ~]# yum install qpress
2.2 xtrabackup 完全+增量備份還原
1点寥、備份過程
#在原主機(jī)做完全備份到/backup
[root@centos8-8 ~]# mkdir /backup
[root@centos8-8 ~]# xtrabackup -uroot --backup --target-dir=/backup/base
#第一次修改數(shù)據(jù)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database db2;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
#做第一次增量備份
[root@centos8-8 ~]# xtrabackup -uroot --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
[root@centos8-8 ~]# cat /backup/inc1/xtrabackup_info
uuid = d69c3dd7-fa4b-11eb-ab25-52540099d568
name =
tool_name = xtrabackup
tool_command = -uroot --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
tool_version = 8.0.25-17
ibbackup_version = 8.0.25-17
server_version = 8.0.21
start_time = 2021-08-11 10:28:37
end_time = 2021-08-11 10:28:39
lock_time = 0
binlog_pos = filename 'centos8-8-bin.000009', position '156'
innodb_from_lsn = 17923714
innodb_to_lsn = 17925785
partial = N
incremental = Y
format = file
compressed = N
encrypted = N
[root@centos8-8 ~]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 17923714
to_lsn = 17925785
last_lsn = 17925785
flushed_lsn = 17925785
#第二次修改數(shù)據(jù)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| db3 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
#做第二次增量備份
[root@centos8-8 ~]# xtrabackup -uroot --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
[root@centos8-8 ~]# cat /backup/inc2/xtrabackup_info
uuid = 4225652a-fa4c-11eb-ab25-52540099d568
name =
tool_name = xtrabackup
tool_command = -uroot --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
tool_version = 8.0.25-17
ibbackup_version = 8.0.25-17
server_version = 8.0.21
start_time = 2021-08-11 10:31:38
end_time = 2021-08-11 10:31:39
lock_time = 0
binlog_pos = filename 'centos8-8-bin.000010', position '156'
innodb_from_lsn = 17925785
innodb_to_lsn = 17927306
partial = N
incremental = Y
format = file
compressed = N
encrypted = N
[root@centos8-8 ~]# cat /backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 17925785
to_lsn = 17927306
last_lsn = 17927306
flushed_lsn = 17927306
#復(fù)制備份文件到目標(biāo)主機(jī)
[root@centos8-8 ~]# scp -r /backup/ 192.168.108.28:/
2、還原過程
#1)預(yù)準(zhǔn)備完成備份缸榛,此選項(xiàng)--apply-log-only 阻止回滾未完成的事務(wù)
[root@centos8-28 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#2)合并第1次增量備份到完全備份
[root@centos8-28 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
#3)合并第2次增量備份到完全備份:最后一次還原不需要加選項(xiàng)--apply-log-only
[root@centos8-28 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
#4)復(fù)制到數(shù)據(jù)庫(kù)目錄盟萨,注意數(shù)據(jù)庫(kù)目錄必須為空凉翻,MySQL服務(wù)不能啟動(dòng)
[root@centos8-28 ~]# xtrabackup --copy-back --target-dir=/backup/base
#5)還原屬性
[root@centos8-28 ~]# chown -R mysql:mysql /var/lib/mysql
#6)啟動(dòng)服務(wù)
[root@centos8-28 ~]# systemctl start mysqld.service
3、MyCAT實(shí)現(xiàn)MySQL讀寫分離
- mysql-master 192.168.100.7
- mysql-slave1 192.168.100.17
- mysql-mycat 192.168.100.37
mysql-master
[root@mysql-master ~]# yum -y install mariadb-server
[root@mysql-master ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
log_bin
[root@mysql-master ~]# systemctl start mariadb
[root@mysql-master ~]# mysql < hellodb_innodb.sql
[root@mysql-master ~]# mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 404 |
+--------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.100.%' identified by 'ddq.com';
MariaDB [mysql]> grant all on *.* to 'root'@'192.168.100.%' identified by 'ddq.com';
MariaDB [mysql]> flush privileges;
mysql-slave1
[root@mysql-slave1 ~]# echo 192.168.100.7 masterhost >> /etc/hosts
[root@mysql-slave1 ~]# yum -y install mariadb-server
[root@mysql-slave1 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=2
log-bin
read_only=ON
relay_log=relay-log
relay_log_index=relay-log.index
[root@mysql-master ~]# systemctl start mariadb
[root@mysql-master ~]# mysql
MariaDB [(none)]> change master to master_host='masterhost',
-> master_user='repluser',
-> master_password='ddq.com',
-> master_log_file='mariadb-bin.000001',
-> master_log_pose=563;
Query OK, 0 rows affected (0.01 sec)
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: masterhost
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 563
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 563
Relay_Log_Space: 819
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
mysql-mycat
下載安裝JDK
[root@mysql-mycat ~]# yum -y install java
[root@mysql-mycat ~]# java -version
openjdk version "1.8.0_302"
OpenJDK Runtime Environment (build 1.8.0_302-b08)
OpenJDK 64-Bit Server VM (build 25.302-b08, mixed mode)
下載安裝mycat
Mycat 官網(wǎng):http://www.mycat.org.cn/
[root@mysql-mycat ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210730131311/Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz
[root@mysql-mycat ~]# tar zxf Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz
[root@mysql-mycat ~]# mkdir /apps
[root@mysql-mycat ~]# mv mycat/ /apps/
[root@mysql-mycat ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mysql-mycat ~]# source /etc/profile.d/mycat.sh
[root@mysql-mycat conf]# vi server.xml
......
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
......
<user name="root" defaultAccount="true">
<property name="password">ddq.com</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
......
[root@mysql-mycat conf]# vi schema.xml
#修改為以下內(nèi)容捻激,balance改為1制轰,表示讀寫分離
<?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="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://192.168.100.7:3306" user="root" password="ddq.com">
<readHost host="hostM2" url="jdbc:mysql://192.168.100.17:3306" user="root" password="ddq.com" />
</writeHost>
</dataHost>
</mycat:schema>
[root@mysql-mycat conf]# mycat start
在client上測(cè)試
[root@client ~]# mysql -uroot -pddq.com -h192.168.100.37 -P3306
MySQL [(none)]> use TESTDB;
Database changed
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MySQL [TESTDB]> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
OK!
MySQL [TESTDB]> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| t1 |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
停止從節(jié)點(diǎn),MyCAT自動(dòng)調(diào)度讀請(qǐng)求至主節(jié)點(diǎn)
[root@mysql-slave1 ~]# systemctl stop mariadb
[root@client ~]# mysql -uroot -pddq.com -h192.168.100.37 -P3306
MySQL [(none)]> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| mysql-master |
+--------------+
1 row in set (0.09 sec)
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| t1 |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
#停止主節(jié)點(diǎn)胞谭,MyCAT不會(huì)自動(dòng)調(diào)度寫請(qǐng)求至從節(jié)點(diǎn)