MySQL集群配置

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)
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末艇挨,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子韭赘,更是在濱河造成了極大的恐慌缩滨,老刑警劉巖,帶你破解...
    沈念sama閱讀 212,657評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件泉瞻,死亡現(xiàn)場(chǎng)離奇詭異脉漏,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)袖牙,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,662評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門侧巨,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人鞭达,你說我怎么就攤上這事司忱。” “怎么了畴蹭?”我有些...
    開封第一講書人閱讀 158,143評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵坦仍,是天一觀的道長(zhǎng)。 經(jīng)常有香客問我叨襟,道長(zhǎng)繁扎,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,732評(píng)論 1 284
  • 正文 為了忘掉前任糊闽,我火速辦了婚禮梳玫,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘右犹。我一直安慰自己提澎,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,837評(píng)論 6 386
  • 文/花漫 我一把揭開白布念链。 她就那樣靜靜地躺著盼忌,像睡著了一般莉炉。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上碴犬,一...
    開封第一講書人閱讀 50,036評(píng)論 1 291
  • 那天,我揣著相機(jī)與錄音梆暮,去河邊找鬼服协。 笑死,一個(gè)胖子當(dāng)著我的面吹牛啦粹,可吹牛的內(nèi)容都是我干的偿荷。 我是一名探鬼主播,決...
    沈念sama閱讀 39,126評(píng)論 3 410
  • 文/蒼蘭香墨 我猛地睜開眼唠椭,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼跳纳!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起贪嫂,我...
    開封第一講書人閱讀 37,868評(píng)論 0 268
  • 序言:老撾萬榮一對(duì)情侶失蹤寺庄,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后力崇,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體斗塘,經(jīng)...
    沈念sama閱讀 44,315評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,641評(píng)論 2 327
  • 正文 我和宋清朗相戀三年亮靴,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了馍盟。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,773評(píng)論 1 341
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡茧吊,死狀恐怖贞岭,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情搓侄,我是刑警寧澤瞄桨,帶...
    沈念sama閱讀 34,470評(píng)論 4 333
  • 正文 年R本政府宣布,位于F島的核電站讶踪,受9級(jí)特大地震影響讲婚,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜俊柔,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 40,126評(píng)論 3 317
  • 文/蒙蒙 一筹麸、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧雏婶,春花似錦物赶、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,859評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)告嘲。三九已至,卻和暖如春奖地,著一層夾襖步出監(jiān)牢的瞬間橄唬,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,095評(píng)論 1 267
  • 我被黑心中介騙來泰國(guó)打工参歹, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留仰楚,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 46,584評(píng)論 2 362
  • 正文 我出身青樓犬庇,卻偏偏與公主長(zhǎng)得像僧界,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子臭挽,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,676評(píng)論 2 351

推薦閱讀更多精彩內(nèi)容