Linux相关知识的第十九回合
主从复制及主主复制的实现
MySQL主从复制
MySQL-Master端部署
# yum安装mariadb-server
yum install -y mariadb-server
# 修改my.cnf的配置文件
cp -rp /etc/my.cnf.d/mariadb-server.cnf{,.`datebak`}
vim /etc/my.cnf.d/mariadb-server.cnf
######################################################################
[mysqld]
innodb_buffer_pool_size = 2G
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock
character-set-server=utf8mb4
symbolic-links=0
# 指定相关日志文件位置
general_log_file=/data/mysql/logs/mysqld.log
log-error=/data/mysql/logs/mysqld.error
slow_query_log_file=/data/mysql/logs/mysqlslow.log
# 设置相关bin-log参数
## 设置server-id
server-id=1
## 设置二进制日志格式
binlog_format=row
## 开启二进制日志并设置目录
log-bin=/data/mysql/logs/mysql-bin
log-bin-index=/data/mysql/logs/mysql-bin.index
binlog_cache_size=1M
max_binlog_size=500M
pid-file=/var/run/mysqld/mysqld.pid
# 添加客户端socket的信息
[client]
port=3306
socket=/data/mysql/data/mysql.sock
######################################################################
# 创建目录数据目录及日志目录
mkdir -p /data/mysql/{data,logs}
chown -R mysql:mysql /data/mysql
# 启动
systemctl start mysqld
systemctl status mysqld.service
ss -tnl|grep 3306
# 设置开机自启动
systemctl enable mysqld
# 为了实验的方便,mysql没有设置密码,在正式环境中必须要设置复杂的密码
# 登录mysql
mysql
MariaDB root@(none):(none)> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 325 |
+------------------+-----------+
MariaDB root@(none):(none)> grant replication slave on *.* to repluser@'192.168.168.%' identified by 'Q1w2e3';
Query OK, 0 rows affected
Time: 0.003s
MySQL-Slave端部署
# yum安装mariadb-server
yum install -y mariadb-server
# 修改my.cnf的配置文件
cp -rp /etc/my.cnf.d/mariadb-server.cnf{,.`datebak`}
vim /etc/my.cnf.d/mariadb-server.cnf
######################################################################
[mysqld]
innodb_buffer_pool_size = 2G
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock
character-set-server=utf8mb4
symbolic-links=0
# 指定相关日志文件位置
general_log_file=/data/mysql/logs/mysqld.log
log-error=/data/mysql/logs/mysqld.error
slow_query_log_file=/data/mysql/logs/mysqlslow.log
# 设置相关bin-log参数
## 设置server-id
server-id=2
## 设置只读
read_only=ON
relay_log=/data/mysql/logs/relay-log
relay_log_index=/data/mysql/logs/relay-log.index
pid-file=/var/run/mysqld/mysqld.pid
# 添加客户端socket的信息
[client]
port=3306
socket=/data/mysql/data/mysql.sock
######################################################################
# 创建目录数据目录及日志目录
mkdir -p /data/mysql/{data,logs}
chown -R mysql:mysql /data/mysql
# 启动
systemctl start mysqld
systemctl status mysqld.service
ss -tnl|grep 3306
# 设置开机自启动
systemctl enable mysqld
# 登录mysql
mysql
MariaDB root@(none):(none)> CHANGE MASTER TO MASTER_HOST='192.168.168.66',
-> MASTER_USER='repluser', MASTER_PASSWORD='Q1w2e3', MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=325;
Query OK, 0 rows affected
Time: 0.011s
MariaDB root@(none):(none)> start slave;
Query OK, 0 rows affected
Time: 0.003s
测试数据是否同步
# 在Master端插入数据
MariaDB root@(none):(none)> source /root/hellodb_innodb.sql
MariaDB root@(none):hellodb> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 10546 |
+------------------+-----------+
MariaDB root@(none):hellodb> show processlist;
+----+-------------+----------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+----------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------+----------+
| 1 | system user | | <null> | Daemon | <null> | InnoDB purge coordinator | <null> | 0.0 |
| 2 | system user | | <null> | Daemon | <null> | InnoDB purge worker | <null> | 0.0 |
| 3 | system user | | <null> | Daemon | <null> | InnoDB purge worker | <null> | 0.0 |
| 4 | system user | | <null> | Daemon | <null> | InnoDB shutdown handler | <null> | 0.0 |
| 5 | system user | | <null> | Daemon | <null> | InnoDB purge worker | <null> | 0.0 |
| 9 | root | localhost | hellodb | Query | 0 | Init | show processlist | 0.0 |
| 11 | repluser | 192.168.168.38:54590 | <null> | Binlog Dump | 452 | Master has sent all binlog to slave; waiting for binlog to be updated | <null> | 0.0 |
+----+-------------+----------------------+---------+-------------+--------+-----------------------------------------------------------------------+------------------+----------+
# 在Slave端测试
MariaDB root@(none):hellodb> show slave status\G;
***************************[ 1. row ]***************************
Slave_IO_State | Waiting for master to send event
Master_Host | 192.168.168.66
Master_User | repluser
Master_Port | 3306
Connect_Retry | 60
Master_Log_File | mysql-bin.000001
Read_Master_Log_Pos | 10546 # 数据已经同步过来
Relay_Log_File | relay-log.000002
Relay_Log_Pos | 9005
Relay_Master_Log_File | mysql-bin.000001
Slave_IO_Running | Yes
Slave_SQL_Running | Yes
Seconds_Behind_Master | 0 # slave与master已经一致
Slave_SQL_Running_State | Slave has read all relay log; waiting for the slave I/O thread to update it
MySQL主主复制
MySQL-Master端修改
# 修改my.cnf的配置文件
cp -rp /etc/my.cnf.d/mariadb-server.cnf{,.`datebak`}
vim /etc/my.cnf.d/mariadb-server.cnf
######################################################################
[mysqld]
innodb_buffer_pool_size = 2G
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock
character-set-server=utf8mb4
symbolic-links=0
# 指定相关日志文件位置
general_log_file=/data/mysql/logs/mysqld.log
log-error=/data/mysql/logs/mysqld.error
slow_query_log_file=/data/mysql/logs/mysqlslow.log
# 设置相关bin-log参数
## 设置server-id
server-id=1
## 设置二进制日志格式
binlog_format=row
## 开启二进制日志并设置目录
log-bin=/data/mysql/logs/mysql-bin
log-bin-index=/data/mysql/logs/mysql-bin.index
binlog_cache_size=1M
max_binlog_size=500M
# 设置自动增长序列从1开始
auto_increment_offset=1
# 设置每次递增为2,就是步长为2,即实现奇数增长
auto_increment_increment=2
relay_log=/data/mysql/logs/relay-log
relay_log_index=/data/mysql/logs/relay-log.index
pid-file=/var/run/mysqld/mysqld.pid
# 添加客户端socket的信息
[client]
port=3306
socket=/data/mysql/data/mysql.sock
######################################################################
# 重启MySQL服务
systemctl restart mariadb
MySQL-Slave端修改
# 修改my.cnf的配置文件
cp -rp /etc/my.cnf.d/mariadb-server.cnf{,.`datebak`}
vim /etc/my.cnf.d/mariadb-server.cnf
######################################################################
[mysqld]
innodb_buffer_pool_size = 2G
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock
character-set-server=utf8mb4
symbolic-links=0
# 指定相关日志文件位置
general_log_file=/data/mysql/logs/mysqld.log
log-error=/data/mysql/logs/mysqld.error
slow_query_log_file=/data/mysql/logs/mysqlslow.log
# 设置相关bin-log参数
## 设置server-id
server-id=2
## 设置二进制日志格式
binlog_format=row
## 开启二进制日志并设置目录
log-bin=/data/mysql/logs/mysql-bin
log-bin-index=/data/mysql/logs/mysql-bin.index
binlog_cache_size=1M
max_binlog_size=500M
# 设置自动增长序列从2开始
auto_increment_offset=2
# 设置每次递增为2,就是步长为2,即实现偶数增长
auto_increment_increment=2
relay_log=/data/mysql/logs/relay-log
relay_log_index=/data/mysql/logs/relay-log.index
pid-file=/var/run/mysqld/mysqld.pid
# 添加客户端socket的信息
[client]
port=3306
socket=/data/mysql/data/mysql.sock
######################################################################
# 重启MySQL服务
systemctl restart mariadb
# 查看master状态
mysql -e 'show master logs;'
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 325 |
+------------------+-----------+
登录MySQL的Master端,配置与slave端的同步信息
# Master端
MariaDB root@(none):hellodb> CHANGE MASTER TO MASTER_HOST='192.168.168.38',MASTER_USER='repluser',MASTER_PASSWORD='Q1w2e3', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=325;
Query OK, 0 rows affected
Time: 0.017s
MariaDB root@(none):(none)> start slave;
MariaDB root@(none):(none)> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 339 | | | |
+------------------+----------+--------------+------------------+-------------------+
MariaDB root@(none):(none)> show slave status\G
Slave_IO_State |
Master_Host | 192.168.168.38
Master_User | repluser
Master_Port | 3306
Connect_Retry | 60
Master_Log_File | mysql-bin.000001
Read_Master_Log_Pos | 325
Relay_Log_File | mariadb-relay-bin.000002
Relay_Log_Pos | 552
Relay_Master_Log_File | mysql-bin.000001
Slave_IO_Running | Yes
Slave_SQL_Running | Yes
Seconds_Behind_Master | 0
xtrabackup实现全量+增量+binlog恢复库
# 安装最新版
cd /data/soft/
# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
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
yum install -y percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
rpm -ql percona-xtrabackup-24
#####################################################################
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.22
/usr/share/doc/percona-xtrabackup-24-2.4.22/LICENSE
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
#####################################################################
# 备份
# 完全备份
mkdir /data/backups/mysql
xtrabackup -u'root' -p'2Fkq7eTPNK#K' --backup --target-dir=/data/backups/mysql/fullbackup_$(datebak)
# 第一次修改数据库数据
# 第一次增量备份
xtrabackup -u'root' -p'2Fkq7eTPNK#K' --backup --target-dir=/data/backups/mysql/incbackup_$(datebak) --incremental-basedir=/data/backups/mysql/fullbackup_<full_backup>
# 第二次修改数据库数据
# 第二次增量备份
xtrabackup -u'root' -p'2Fkq7eTPNK#K' --backup --target-dir=/data/backups/mysql/incbackup_$(datebak) --incremental-basedir=/data/backups/mysql/incbackup_<first_inc_backup>
# 还原
# 关闭数据库
systemctl stop mysqld.service
# 清空数据目录下的所有内容
rm -rf /data/mysql/data/*
ll /data/mysql/data/
total 0
# 预准备
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql
xtrabackup --prepare --target-dir=/data/backups/mysql --incremental-dir=/data/backups/mysql/incbackup_$(datebak)
# 复制到数据库目录
# 注意:数据库目录必须为空,MySQL服务不能启动
xtrabackup --copy-back --target-dir=/data/backups/mysql/
# 还原属性
ll /data/mysql/data/
#####################################################################
total 185M
-rw-r----- 1 root root 48M Apr 14 17:16 ib_logfile0
-rw-r----- 1 root root 48M Apr 14 17:16 ib_logfile1
-rw-r----- 1 root root 76M Apr 14 17:17 ibdata1
drwxr-x--- 2 root root 4.0K Apr 14 17:17 mysql
drwxr-x--- 2 root root 8.0K Apr 14 17:17 sys
drwxr-x--- 2 root root 334 Apr 14 17:17 db1
drwxr-x--- 2 root root 4.0K Apr 14 17:17 hellodb
drwxr-x--- 2 root root 8.0K Apr 14 17:17 performance_schema
-rw-r----- 1 root root 828 Apr 14 17:17 ib_buffer_pool
-rw-r----- 1 root root 495 Apr 14 17:17 xtrabackup_info
-rw-r----- 1 root root 26 Apr 14 17:17 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 1 Apr 14 17:17 xtrabackup_master_key_id
-rw-r----- 1 root root 12M Apr 14 17:17 ibtmp1
#####################################################################
chown -R mysql:mysql /data/mysql/data
# 启动
systemctl start mysqld
# 检查
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> select * from hellodb.coc;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 2 | 6 |
| 5 | 3 | 1 |
| 6 | 3 | 7 |
| 7 | 4 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 1 |
| 10 | 5 | 9 |
| 11 | 6 | 3 |
| 12 | 6 | 4 |
| 13 | 7 | 4 |
| 14 | 7 | 3 |
+----+---------+----------+
14 rows in set (0.01 sec)
MyCAT实现MySQL读写分离
MySQL主从复制架构搭建
MySQL
主从复制架构搭建,上面已经做过演示,在此不赘述
主从复制成功后,设置root密码:
grant all on *.* to 'root'@'192.168.168.%' identified by 'Hooper!@34' with grant option;
Mycat部署
安装包下载地址: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
# 安装java、MySQL客户端及Mycat
yum instal -y java
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)
yum instal -y mariadb
mysql --version
mysql Ver 15.1 Distrib 10.3.27-MariaDB, for Linux (x86_64) using readline 5.1
cd /data/soft
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
mkdir /data
tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /data
echo 'PATH=/data/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
source /etc/profile.d/mycat.sh
# 修改Mycat与应用程序或者客户端的连接信息
cp -rp /data/mycat/conf/server.xml{,.`datebak`}
vim /data/mycat/conf/server.xml
######################################################################
108 <user name="root" defaultAccount="true">
109 <property name="password">Mycat!@34</property> # 修改客户端连接密码
110 <property name="schemas">TESTDB</property> # 虚拟数据库,要与schema.xml设置的相同
111 <!-- <property name="defaultSchema">TESTDB</property> --> # 注销
125 <user name="user">
126 <property name="password">Mycat!@34</property>
127 <property name="schemas">TESTDB</property>
128 <property name="readOnly">true</property>
129 <!-- <property name="defaultSchema">TESTDB</property> --> # 注销
130 </user>
######################################################################
# 修改Mycat与后端MySQL主从数据库的连接信息
cp -rp /data/mycat/conf/schema.xml{,.`datebak`}
vim /data/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" randomDataNode="dn1"></schema>
<!-- database="hooper" 连接后端MySQL数据库的库名 -->
<dataNode name="dn1" dataHost="localhost1" database="hooper"/>
<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="hostM1" url="192.168.168.66:3306" user="root" password="Hooper!@34">
<readHost host="hostS2" url="192.168.168.38:3306" user="root" password="Hooper!@34" />
</writeHost>
</dataHost>
</mycat:schema>
######################################################################
# 启动Mycat并检查是否启动成功
mycat start
tail -f /data/mycat/logs/wrapper.log
INFO | jvm 1 | 2021/07/29 16:25:13 | MyCAT Server startup successfully. see logs in logs/mycat.log
测试数据
# 登录Mycat的数据库
mysql -u'root' -p'Mycat!@34' -P'8066' -h'192.168.168.211' -D'TESTDB'
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave |
+------------+
1 row in set (0.002 sec)
扩展知识:
dataHost
节点中的balance
(负载均衡类型)属性值共四种情况:
balance="0"
不开启读写分离机制,所有读操作都发送到当前可用的writeHost
上;
balance="1"
全部的readHost
与stand by writeHost
参与select
语句的负载均衡;简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2都参与select
语句的负载均衡;
balance="2"
所有读操作都随机的在writeHost、readhost
上分发;
balance="3"
所有读请求随机的分发到readhost
执行,writerHost
不负担读压力