MySQL主从复制以及读写分离

1、主从复制及主主复制的实现

1.1 主从复制

1.1.1、主节点设置

#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2020-12-01T06:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#使用初始密码登录
[root@localhost ~]# mysql -hlocalhost -uroot -p'QmtSPzqXW0*m'
...
Server version: 5.7.32
...
#修改密码
mysql> alter user root@"localhost" identified by "Jhd2020@";
#使用新密码登录
mysql> grant replication slave on *.* to repluser@'192.168.2.%'
identified by 'Jhd2020@';
#导入数据库文件
[root@localhost ~]# mysql -hlocalhost -uroot -p'Jhd2020@' < hellodb_innodb.sql
#修改配置
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=8
log-bin
#创建有复制权限的用户账号
mysql> grant replication slave on *.* to repluser@'192.168.2.%' identified by 'Jhd2020@';

#查看二进制文件和位置
mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       759 |
+----------------------+-----------+
1 row in set (0.00 sec)

1.1.2 从节点

#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2020-12-01T06:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#使用初始密码登录
[root@localhost ~]# mysql -hlocalhost -uroot -p'QmtSPzqXW0*m'
...
Server version: 5.7.32
...
#修改密码
mysql> alter user root@"localhost" identified by "Jhd2020@";
#使用新密码登录
mysql> grant replication slave on *.* to repluser@'192.168.2.%'
identified by 'Jhd2020@';
#导入数据库文件
[root@localhost ~]# mysql -hlocalhost -uroot -p'Jhd2020@' < hellodb_innodb.sql
#修改配置
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=18
[root@localhost ~]# systemctl restart mysqld
#使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.157',MASTER_USER='repluser',MASTER_PASSWORD='Jhd2020@', MASTER_PORT=3306,MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=759;

mysql> start slave;

mysql> show slave status\G

1.2 主主复制

1.2.1、第一个master点安装配置

#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2020-12-01T06:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#使用初始密码登录
[root@localhost ~]# mysql -hlocalhost -uroot -p'QmtSPzqXW0*m'
...
Server version: 5.7.32
...
#修改密码
mysql> alter user root@"localhost" identified by "Jhd2020@";
#使用新密码登录
mysql> grant replication slave on *.* to repluser@'192.168.2.%'
identified by 'Jhd2020@';
#导入数据库文件
[root@localhost ~]# mysql -hlocalhost -uroot -p'Jhd2020@' < hellodb_innodb.sql
#修改配置
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=8
log-bin
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度

#重启
[root@localhost ~]# systemctl restart mysqld
#查看节点
[root@localhost ~]# mysql -hlocalhost -uroot -p
mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       456 |
+----------------------+-----------+
1 row in set (0.00 sec)
#创建可复制账户
mysql> grant replication slave on *.* to repluser@'192.168.2.%' identified by 'Jhd2020@';

1.2.2、在第二个master节点上配置

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=18
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -hlocalhost -uroot -p
#创建可复制账户
mysql> grant replication slave on *.* to repluser@'192.168.2.%' identified by 'Jhd2020@';
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.2.157',
MASTER_USER='repluser',
MASTER_PASSWORD='Jhd2020@',
MASTER_PORT=3306,
MASTER_LOG_FILE='localhost-bin.000001',
MASTER_LOG_POS=456;
#启动节点
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#查看二进制文件的位置
mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       456 |
+----------------------+-----------+
1 row in set (0.00 sec)

1.2.3、在第一个msater 节点上配置

[root@localhost ~]# mysql -hlocalhost -uroot -p
mysql> CHANGE MASTER TO
CHANGE MASTER TO
MASTER_HOST='192.168.2.158',
MASTER_USER='repluser',
MASTER_PASSWORD='Jhd2020@',
MASTER_PORT=3306,
MASTER_LOG_FILE='localhost-bin.000001',
MASTER_LOG_POS=456;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#在第一个master节点操作
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> use db1
Database changed
mysql> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 (name) values('user1');
Query OK, 1 row affected (0.00 sec)

mysql> insert t1 (name) values('user2');
Query OK, 1 row affected (0.00 sec)

#在第二个master节点进行查询
mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
+----+-------+
1 row in set (0.00 sec)

2、xtrabackup实现全量+增量+binlog恢复库

2.1 下载安装xtrabackup

#下载xtrabackup的RPM包
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
#安装
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

2.2 安装mysql

#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2020-12-01T06:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#使用初始密码登录
[root@localhost ~]# mysql -hlocalhost -uroot -p'QmtSPzqXW0*m'
...
Server version: 5.7.32
...
#修改密码
mysql> alter user root@"localhost" identified by "Jhd2020@";
#使用新密码登录

2.3 导入测试数据，实现增量备份

#导入数据库
[root@localhost ~]# mysql -hlocalhost -uroot -p'Jhd2020@' < hellodb_innodb.sql
#创建数据库
[root@localhost ~]# mysql -hlocalhost -uroot -p
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> use db1
Database changed
mysql> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 (name) values('user1');
Query OK, 1 row affected (0.00 sec)

mysql> insert t1 (name) values('user2');
Query OK, 1 row affected (0.00 sec)

# 1、完全备份
[root@localhost ~]# mkdir /backup/
[root@localhost ~]# xtrabackup -uroot -p'Jhd2020@' --backup --target-dir=/backup/base

# 2、第一次修改数据
mysql> insert t1 (name) values('user3');
Query OK, 1 row affected (0.00 sec)

mysql> insert t1 (name) values('user4');
Query OK, 1 row affected (0.01 sec)
# 3、第一次增量备份
[root@localhost ~]# xtrabackup -uroot -p'Jhd2020@' --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

# 4、第二次修改数据
mysql> insert t1 (name) values('user5');
Query OK, 1 row affected (0.00 sec)

mysql> insert t1 (name) values('user6');
Query OK, 1 row affected (0.00 sec)

# 5、第二次增量备份
[root@localhost ~]# xtrabackup -uroot -p'Jhd2020@' --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

# 6、src传输到目标主机
[root@localhost ~]# scp -r /backup/* 192.168.2.158:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}

# 2还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份，
[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup/base
5）还原属性：
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql
6）启动服务：
[root@localhost ~]# systemctl start mysqld

2.4、完全备份

#下载xtrabackup的RPM包
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
#安装
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2020-12-01T06:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#使用初始密码登录
[root@localhost ~]# mysql -hlocalhost -uroot -p'QmtSPzqXW0*m'
...
Server version: 5.7.32
...
#修改密码
mysql> alter user root@"localhost" identified by "Jhd2020@";

# 在原主机上进行备份到/backup
[root@localhost ~]# xtrabackup -uroot -p'Jhd2020@' --backup --target-dir=/backup/
#复制到目标主机
[root@localhost ~]# scp -r /backup/* 192.168.2.158:/backup/

# 在目标主机上还原
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup/

[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup/
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql

3、MyCAT实现MySQL读写分离

准备三台服务器

mysql-master 10.0.0.7
mysql-slave 10.0.0.17
mycat-server 10.0.0.27 内存建议2G以上

关闭防火墙、SELinux，并进行时间同步
systemctl stop firewalld
setenforce 0
nntpdate ntp.aliyun.com

3.1、创建mysql 主从数据库

#下载mysql5.7
[root@centos7 ~]# systemctl stop firewall
[root@centos7 ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@centos7 ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@centos7 ~]# yum -y install mysql-server
[root@centos7 ~]# systemctl start mysqld
[root@centos7 ~]# systemctl enable mysqld
#查看mysql初始密码
[root@centos7 ~]# grep 'root@localhost:' /var/log/mysqld.log
2020-12-01T06:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#使用初始密码登录
[root@centos7 ~]# mysql -hlocalhost -uroot -p'QmtSPzqXW0*m'
...
Server version: 5.7.32
...
#修改密码
mysql> alter user root@"localhost" identified by "Jhd2020@";
#使用新密码登录
[root@centos7 ~]# mysql -uroot -p

3.2.1、修改master和slave上的配置文件

#master上的my.cnf
[root@centos7 ~]# vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin

#slave上的my.cnf
[root@centos17 ~]# vim /etc/my.cnf
[mysqld]
server-id = 2

3.2.2、master上创建复制用户

[root@centos7 ~]# mysql -uroot -p
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'Jhd2020@';
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| centos7-bin.000002 |      306 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_USER='repluser',
MASTER_PASSWORD='Jhd2020@',
MASTER_LOG_FILE='centos7-bin.000002',
MASTER_LOG_POS=306;

3.2.3、slave上执行

[root@centos17 ~]# mysql -uroot -p
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_USER='repluser',
MASTER_PASSWORD='Jhd2020@',
MASTER_LOG_FILE='centos7-bin.000002',
MASTER_LOG_POS=306;

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.0.0.7
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: centos7-bin.000002
          Read_Master_Log_Pos: 306
               Relay_Log_File: centos17-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: centos7-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.3、在10.0.0.27的服务器上安装mycat 并启动

[root@centos27 ~]# yum -y install mysql
[root@centos27 ~]# yum -y install java
#确认安装成功
[root@centos27 ~]# java -version
openjdk version "1.8.0_282"
OpenJDK Runtime Environment (build 1.8.0_282-b08)
OpenJDK 64-Bit Server VM (build 25.282-b08, mixed mode)
#下载并安装
[root@centos27 ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@centos27 ~]# mkdir /app
[root@centos27 ~]# tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /app/

[root@centos27 ~]# echo 'PATH=/app/mycat/bin:$PATH' > /etc/profile.d/mycat.sh

[root@centos27 ~]# source /etc/profile.d/mycat.sh 

[root@centos27 ~]# ss -ntl
State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port              
LISTEN     0      100            127.0.0.1:25                                 *:*                  
LISTEN     0      128                    *:111                                *:*                  
LISTEN     0      128                    *:22                                 *:*                  
LISTEN     0      100                [::1]:25                              [::]:*                  
LISTEN     0      128                 [::]:111                             [::]:*                  
LISTEN     0      128                 [::]:22                              [::]:* 


[root@centos27 ~]# mycat start
Starting Mycat-server...
[root@centos27 ~]# ss -ntlp
State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port              
LISTEN     0      100            127.0.0.1:25                                 *:*                   users:(("master",pid=2626,fd=13))
LISTEN     0      1              127.0.0.1:32000                              *:*                   users:(("java",pid=2720,fd=4))
LISTEN     0      128                    *:111                                *:*                   users:(("rpcbind",pid=708,fd=4),("systemd",pid=1,fd=54))
LISTEN     0      128                    *:22                                 *:*                   users:(("sshd",pid=1127,fd=3))
LISTEN     0      100                [::1]:25                              [::]:*                   users:(("master",pid=2626,fd=14))
LISTEN     0      50                  [::]:1984                            [::]:*                   users:(("java",pid=2720,fd=65))
LISTEN     0      100                 [::]:8066                            [::]:*                   users:(("java",pid=2720,fd=86))
LISTEN     0      100                 [::]:9066                            [::]:*                   users:(("java",pid=2720,fd=82))
LISTEN     0      50                  [::]:44523                           [::]:*                   users:(("java",pid=2720,fd=66))
LISTEN     0      128                 [::]:111                             [::]:*                   users:(("rpcbind",pid=708,fd=6),("systemd",pid=1,fd=58))
LISTEN     0      50                  [::]:39636                           [::]:*                   users:(("java",pid=2720,fd=64))
LISTEN     0      128                 [::]:22                              [::]:*                   users:(("sshd",pid=1127,fd=4))
#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@centos27 ~]# tail -f /app/mycat/logs/wrapper.log 
STATUS | wrapper  | 2021/04/18 16:38:10 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/04/18 16:38:10 | Launching a JVM...
INFO   | jvm 1    | 2021/04/18 16:38:10 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/04/18 16:38:10 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/04/18 16:38:10 | 
INFO   | jvm 1    | 2021/04/18 16:38:12 | MyCAT Server startup successfully. see logs in logs/mycat.log

#用默认密码123456来连接mycat
[root@centos27 ~]# mysql -uroot -p'123456' -h10.0.0.27 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

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 TESTDB |
+------------------+
| address          |
| travelrecord     |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from travelrecord;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0

3.4、在mycat服务器上修改server.xml文件配置mycat的连接信息

[root@centos27 ~]# vim /app/mycat/conf/server.xml 
...省略...
        <user name="root" defaultAccount="true">
                <property name="password">Jhd2020@</property>
                <property name="schemas">TESTDB</property>
                <property name="defaultSchema">TESTDB</property>
        </user>

3.5、修改schema.xml实现读写分离策略

[root@centos27 ~]# vim /app/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.7:3306" user="root"
password="Jhd2020@">
<readHost host="host2" url="10.0.0.17:3306" user="root"
password="Jhd2020@" />
</writeHost>
</dataHost>
</mycat:schema>

#重启mycat
[root@centos27 ~]# mycat restart

3.6、在后端主服务器创建用户并对mycat授权

[root@centos7 ~]# mysql -uroot -p < mycat.sql 
[root@centos7 ~]# mysql -uroot -p 
mysql>GRANT ALL ON *.* TO 'root'@'10.0.0.%' IDENTIFIED BY 'Jhd2020@' WITH GRANT OPTION;
mysql> flush privileges;

3.7、在mycat服务器上连接并测试

[root@centos27 ~]# mysql -uroot -p'Jhd2020@' -h127.0.0.1 -P8066 -DTESTDB
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |        #只能看到一个虚拟数据库
+----------+
1 row in set (0.00 sec)

mysql> use TESTDB
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.01 sec)
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> use TESTDB
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.01 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| centos17    |
+------------+
1 row in set (0.01 sec)

3.8、通过通用日志确认实现读写分离

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'general_log_file';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log_file | /var/lib/mysql/centos7.log |
+------------------+----------------------------+
1 row in set (0.00 sec)

在主和从服务器分别启用通用日志，查看读写分离

[root@centos7 ~]# vim /etc/my.cnf
[mysqld]
general_log=ON
[root@centos7 ~]# systemctl restart mysqld
[root@centos7 ~]# tail -f /var/lib/mysql/centos7.log 


[root@centos17 ~]# vim /etc/my.cnf
[mysqld]
general_log=ON
[root@centos17 ~]# systemctl restart mysqld
[root@centos17 ~]# tail -f /var/lib/mysql/centos17.log

3.9、停止从节点，MyCAT自动调度读请求至主节点

#主节点停止前
[root@centos27 ~]# mysql -uroot -p'Jhd2020@' -h127.0.0.1 -P8066 
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| centos17   |
+------------+
1 row in set (0.00 sec)

#停止从节点后
[root@centos17 ~]# systemctl stop mysqld
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| centos7    |
+------------+
1 row in set (0.00 sec)

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,684评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,143评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,214评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,788评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,796评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,665评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,027评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,679评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,346评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,664评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,766评论 1赞 331
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,412评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,015评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,974评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,203评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,073评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,501评论 2赞 343

MySQL主从复制以及读写分离

1、主从复制及主主复制的实现

1.1 主从复制

1.1.1、主节点设置

1.1.2 从节点

1.2 主主复制

1.2.1、第一个master点安装配置

1.2.2、在第二个master节点上配置

1.2.3、在第一个msater 节点上配置

2、xtrabackup实现全量+增量+binlog恢复库

2.1 下载安装xtrabackup

2.2 安装mysql

2.3 导入测试数据，实现增量备份

2.4、完全备份

3、MyCAT实现MySQL读写分离

3.1、创建mysql 主从数据库

3.2.1、修改master和slave上的配置文件

3.2.2、master上创建复制用户

3.2.3、slave上执行

3.3、在10.0.0.27的服务器上安装mycat 并启动

3.4、在mycat服务器上修改server.xml文件配置mycat的连接信息

3.5、修改schema.xml实现读写分离策略

3.6、在后端主服务器创建用户并对mycat授权

3.7、在mycat服务器上连接并测试

3.8、通过通用日志确认实现读写分离

3.9、停止从节点，MyCAT自动调度读请求至主节点

推荐阅读更多精彩内容