MySQL 数据库 | 穿着草鞋前往

二进制安装 MySQL 5.7 和 MySQL8.0

1、安装相关包

1	yum -y install libaio numactl-libs libncurses*

2、创建用户和组及目录

groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql

mkdir -p /data/mysql/
chown -R mysql:mysql /data/mysql/

mkdir -p /mysql/binlog
chown -R mysql:mysql /mysql/binlog

3、准备程序文件

# 下载好的程序文件
[root@Rocky8-mini3 ~]# ls
mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz
[root@Rocky8-mini3 ~]# tar xf mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz -C /usr/local/src
[root@Rocky8-mini3 ~]# cd /usr/local/src
[root@Rocky8-mini3 src]# ln -s mysql-5.7.40-linux-glibc2.12-x86_64/ mysql
[root@Rocky8-mini3 src]# chown -R root:root /usr/local/src/mysql/

4、准备环境变量

1 2	[root@Rocky8-mini3 bin]# echo 'PATH=/usr/local/src/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky8-mini3 bin]# . /etc/profile.d/mysql.sh

5、准备配置文件和相关目录

[root@Rocky8-mini3 bin]# cat /etc/my.cnf
[client]
socket=/data/mysql/mysql.sock

[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"

[mysqld]
port=3306
log-bin=/mysql/binlog/mysql-bin
server-id=1001
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
bind-address = 0.0.0.0
symbolic-links=0

log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

6、初始化数据库文件

（1）生成随机密码

ll -d /data/mysql/
drwxr-xr-x 2 mysql mysql 6 Feb  7 23:47 /data/mysql/

[root@Rocky8-mini4 local]# mysqld --initialize --user=mysql --datadir=/data/mysql
2023-02-07T15:47:43.136469Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
···
···
2023-02-07T15:47:43.395989Z 1 [Note] A temporary password is generated for root@localhost: jUFcLah?a6K:

（2）生成空密码

1	mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

7、准备服务脚本启动

[root@Rocky8-mini3 bin]# cp /usr/local/src/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@Rocky8-mini3 bin]# chkconfig --add mysqld
[root@Rocky8-mini3 bin]# chkconfig mysqld on
# 默认为/usr/local/mysql,此处我的目录为/usr/local/src/mysql,故需要在下处修改
[root@Rocky8-mini3 bin]# vi /etc/init.d/mysqld
     64 if test -z "$basedir"
     65 then
     66   basedir=/usr/local/src/mysql
     67   bindir=/usr/local/src/mysql/bin
[root@Rocky8-mini3 bin]# service mysqld start
Starting MySQL.Logging to '/data/mysql/mysqld.log'.
 SUCCESS!

8、修改口令

# 修改之前生成的随机密码为指定密码
mysqladmin -uroot -p'jUFcLah?a6K:' password wuhaolam
# 修改前面生成的空密码为指定密码
mysqladmin -uroot password wuhaolam

9、测试登录

1	[root@Rocky8-mini3 ~]# mysql -uroot -pwuhaolam

源码编译安装MySQL5.7

源码下载地址：https://downloads.mysql.com/archives/community/

实验环境

Rocky Linux release 8.5 (Green Obsidian)
boost_1_59_0.tar.gz  
mysql-5.7.40.tar.gz  
rpcsvc-proto-1.4.tar.gz

防火墙和selinux关闭

1、安装相关依赖包

yum -y install gcc gcc-c++  cmake  bison  zlib-devel libcurl-devel libarchive  ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper libtirpc-devel

# 编译安装boost 1.59版本
[root@Centos8-mini ~]# yum install  bzip2-devel.x86_64
[root@Centos8-mini ~]# ls
boost_1_59_0.tar.gz
[root@Centos8-mini ~]# tar xf boost_1_59_0.tar.gz -C /usr/local/src/
[root@Centos8-mini ~]# cd /usr/local/src/boost_1_59_0/ 
[root@Centos8-mini boost_1_59_0]# ./bootstrap.sh --with-libraries=all --with-toolset=gcc
[root@Centos8-mini boost_1_59_0]# ./b2 toolset=gcc
[root@Centos8-mini boost_1_59_0]# ./b2 --prefix=/usr

# 编译安装rpcsvc-proto-1.4
[root@Centos8-mini ~]# ls
rpcsvc-proto-1.4.tar.gz
[root@Centos8-mini ~]# tar xf rpcsvc-proto-1.4.tar.gz -C /usr/local/src/
[root@Centos8-mini ~]# cd /usr/local/src/rpcsvc-proto-1.4/
[root@Centos8-mini rpcsvc-proto-1.4]# ./configure && make && make install

## 编译boost时遇到下面错误
./boost/mpl/assert.hpp:193:21: warning: unnecessary parentheses in declaration of ‘assert_not_arg’ [-Wparentheses] failed ************ (boost::mpl::not_<Pred>::************

在当前文件boost/mpl/assert.hpp 最上面一行添加：
// 禁止boost 断言宏
#define BOOST_MPL_CFG_ASSERT_BROKEN_POINTER_TO_POINTER_TO_MEMBER

2、创建用户和数据库目录

groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql

mkdir -p /data/mysql/
chown -R mysql:mysql /data/mysql/

3、源码编译安装

# 准备需要编译的源码包
[root@Centos8-mini ~]# ls
mysql-5.7.40.tar.gz

[root@Centos8-mini ~]# tar xf mysql-5.7.40.tar.gz -C /usr/local/src/
[root@Centos8-mini ~]# cd /usr/local/src/mysql-5.7.40/

# 注意-DWITH_BOOST=/usr/local/src/boost_1_59_0/，编译安装boost的路径 
[root@Centos8-mini mysql-5.7.40]# cmake . -DCMAKE_INSTALL_PREFIX=/apps/mysql -DMYSQL_DATADIR=/data/mysql/ -DSYSCONFDIR=/etc/ -DMYSQL_USER=mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1  -DWITH_BOOST=/usr/local/src/boost_1_59_0/ -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITHOUT_MROONGA_STORAGE_ENGINE=1 -DWITH_DEBUG=0 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DENABLED_LOCAL_INFILE=1 -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
# 编译遇到错误，删除CMakeCache.txt文件

[root@Rocky8-mini2 mysql-5.7.40]# make
[root@Rocky8-mini2 mysql-5.7.40]# make install

4、生成环境变量

1 2	[root@Rocky8-mini2 mysql-5.7.40]# echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky8-mini2 mysql-5.7.40]# . /etc/profile.d/mysql.sh

5、准备配置文件

[root@Rocky8-mini mysql-5.7.40]# cat /etc/my.cnf 
[client]
port=3306
socket=/data/mysql/mysql.sock

[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0

log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysql.pid

6、初始化数据库

1	[root@Rocky8-mini2 mysql-5.7.40]# mysqld --initialize --user=mysql --datadir=/data/mysql

7、准备自启动脚本

[root@Rocky8-mini2 mysql-5.7.40]# cp -a support-files/mysql.server /etc/init.d/mysqld

[root@Rocky8-mini mysql-5.7.40]# chmod +x /etc/init.d/mysqld
[root@Rocky8-mini mysql-5.7.40]# chkconfig --add mysqld
[root@Rocky8-mini mysql-5.7.40]# chkconfig mysqld on
[root@Rocky8-mini2 mysql-5.7.40]# service mysqld start
Starting MySQL.Logging to '/data/mysql/Rocky8-mini2.err'.
 SUCCESS!

8、修改默认密码并登录

[root@Rocky8-mini mysql]# grep password /data/mysql/mysqld.log
2023-03-01T13:42:36.266976Z 1 [Note] A temporary password is generated for root@localhost: frPDygTwQ7/>
[root@Rocky8-mini mysql]# mysqladmin -uroot -p'frPDygTwQ7/>' password wuhaolam
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

[root@Rocky8-mini mysql]# mysql -uroot -pwuhaolam
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 3
Server version: 5.7.40 Source distribution

Copyright (c) 2000, 2022, 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.

08:49:19(root@localhost) [(none)]>

数据库相关练习

【前提准备】

1、下载好数据文件
http://file.wuhaolam.top/hellodb_innodb.sql
2、导入数据库中
10:14:57(root@localhost) [(none)]> source hellodb_innodb.sql;

【相关练习】

1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
select name,gender,age from students where age>25 and gender='M';

2. 以ClassID为分组依据，显示每组的平均年龄
SELECT ClassID,AVG(age) FROM students GROUP BY ClassID;

3. 显示第2题中平均年龄大于30的分组及平均年龄
SELECT ClassID,AVG(age) FROM students GROUP BY ClassID HAVING AVG(age)>30;

4. 显示以L开头的名字的同学的信息
SELECT * FROM students WHERE NAME LIKE 'L%';

5. 显示TeacherID非空的同学的相关信息
SELECT * FROM students WHERE TeacherID IS NOT NULL;

6. 以年龄排序后，显示年龄最大的前10位同学的信息
SELECT * FROM students ORDER BY age DESC LIMIT 10;

7. 查询年龄大于等于20岁，小于等于25岁的同学的信息
SELECT * FROM students WHERE age BETWEEN 20 AND 25;

8. 以ClassID分组，显示每班的同学的人数
SELECT ClassID,COUNT(stuid) 班级人数 FROM students GROUP BY ClassID;

9. 以Gender分组，显示其年龄之和
SELECT gender,SUM(age) FROM students GROUP BY Gender;

10. 以ClassID分组，显示其平均年龄大于25的班级
SELECT ClassID,AVG(age) FROM students GROUP BY ClassID HAVING AVG(age)>25;

11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
SELECT gender,SUM(age) FROM students WHERE age > 25 GROUP BY Gender;

12. 显示前5位同学的姓名、课程及成绩
SELECT stu.`Name`,cou.`Course`,sco.`Score` FROM students AS stu,courses AS cou,scores AS sco WHERE stu.`StuID`=sco.`StuID` AND cou.`CourseID`=sco.`CourseID` LIMIT 10;

13. 显示其成绩高于80的同学的名称及课程
SELECT stu.`Name`,cou.`Course`,sco.`Score` FROM students AS stu,courses AS cou,scores AS sco WHERE stu.`StuID`=sco.`StuID` AND cou.`CourseID`=sco.`CourseID` AND sco.`Score`>80;

14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
SELECT stu.`Name`,AVG(score) FROM students AS stu,scores AS sco WHERE stu.`StuID`=sco.`StuID` GROUP BY stu.`Name` ORDER BY AVG(score) DESC LIMIT 3;

15. 显示每门课程课程名称及学习了这门课的同学的个数
SELECT courses.`Course` 课程名称,COUNT(scores.`CourseID`) FROM courses,scores WHERE courses.`CourseID`=scores.`CourseID` GROUP BY courses.`Course`;

16. 显示其年龄大于平均年龄的同学的名字
SELECT NAME,age FROM students WHERE age > (SELECT AVG(age) FROM students);

17. 显示其学习的课程为第1、2，4或第7门课的同学的名字
SELECT NAME,CourseID FROM students,scores WHERE students.`StuID`=scores.`StuID` AND scores.`CourseID` IN (1,2,4,7);

18. 显示其成员数大于为3个的班级的同学中年龄大于同班同学平均年龄的同学
SELECT NAME,classid,age FROM students AS A WHERE classid IN (SELECT classid FROM students GROUP BY classid HAVING COUNT(stuid) > 3 ) AND  age > (SELECT AVG(age) FROM students WHERE students.`ClassID`=A.`ClassID`);

19. 统计各班级中年龄大于全校同学平均年龄的同学
SELECT NAME,classid,age FROM students WHERE age > (SELECT AVG(age) FROM students);

mysqldump 和二进制日志结合实现数据的恢复

案例描述：每天2点做数据库的完全备份，在早上8点误删除了表students，8点10分发现故障，需要将数据库还原到8点10分的状态，并且恢复被删除的表。

# 完全备份
[root@Rocky8-mini ~]# mysqldump -uroot -pwuhaolam -A -F --single-transaction --master-data=2 > /backup/allbackupsql_`date +%F_%T`.sql
[root@Rocky8-mini ~]# ll -h /backup/allbackupsql_2023-03-04_02\:07\:01.sql 
-rw-r--r-- 1 root root 868K Mar  4 02:07 /backup/allbackupsql_2023-03-04_02:07:01.sql

# 数据发生更新
02:09:12(root@localhost) [hellodb]> insert students values(26,'Tom',22,'M',2,2);
Query OK, 1 row affected (0.00 sec)

02:10:25(root@localhost) [hellodb]> insert students values(27,'Jerry',22,'F',2,1);
Query OK, 1 row affected (0.01 sec)

# 早上八点表被删除
02:14:55(root@localhost) [hellodb]> show tables;

# 数据发生更新
02:16:08(root@localhost) [hellodb]> insert teachers values(5,'ZERO',36,'M');
Query OK, 1 row affected (0.00 sec)

# 发现数据丢失，准备还原（停止数据库访问）
## 从完全备份的文件中找到二进制的位置
[root@Rocky8-mini ~]# grep '\-\- CHANGE MASTER TO MASTER_LOG_FILE' /backup/allbackupsql_2023-03-04_02\:07\:01.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

## 备份从完全备份后的二进制日志
[root@Rocky8-mini ~]# mysqlbinlog --start-position=154 /mysql/binlog/mysql-bin.000003 > /backup/binlog.sql

## 找到误删除的语句，从备份中删除
[root@Rocky8-mini ~]# grep -i 'drop' /backup/binlog.sql
DROP TABLE `students` /* generated by server */
[root@Rocky8-mini ~]# sed -i.bak '/^DROP TABLE/d' /backup/binlog.sql

## 登录数据库，利用完全备份和修改过的二进制日志进行还原
02:29:56(root@localhost) [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

02:30:11(root@localhost) [(none)]> source /backup/allbackupsql_2023-03-04_02:07:01.sql;
02:30:35(root@localhost) [hellodb]> source /backup/binlog.sql;

02:30:52(root@localhost) [hellodb]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

利用xtrabackup完全，增量备份及还原

【工具下载】

https://www.percona.com/downloads（MySQL8.0的数据库需要使用 Percona XtraBackup 8.0的rpm包，MySQL5.X的版本使用 2.4的rpm包）

利用xtrabackup备份生成的相关文件

xtrabackup_info：文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
xtrabackup_checkpoints：文本文件，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。
LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
xtrabackup_binlog_info：文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
backup-my.cnf：文本文件，备份命令用到的配置选项信息
xtrabackup_logfile：备份生成的二进制日志文件

实例：利用 Percona XtraBackup 2.4 完全，增量备份及还原 MySQL 5.7

# 实验环境
两台Rocky8 Linux机器，安装了xtrabackup和mysql 5.7
192.168.119.128      备份主机
192.168.119.138      还原主机

# 安装好xtrabackup软件包
[root@Rocky8-mini ~]# ls 
percona-xtrabackup-24-2.4.27-1.el8.x86_64.rpm
[root@Rocky8-mini ~]# yum -y install libev-4.24-6.el8.x86_64 perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64 perl-Digest-MD5-2.55-396.el8.x86_64 rsync
[root@Rocky8-mini ~]# rpm -i percona-xtrabackup-24-2.4.27-1.el8.x86_64.rpm

# 完全备份
[root@Rocky8-mini ~]# xtrabackup -uroot -pwuhaolam --backup --target-dir=/backup/base

# 第一次修改数据
07:39:18(root@localhost) [hellodb]> insert teachers values(6,'LuFei',32,'M');

# 第一次增量备份
[root@Rocky8-mini ~]# xtrabackup -uroot -pwuhaolam --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

# 第二次修改数据
07:45:00(root@localhost) [hellodb]> insert teachers values(7,'LaMei',22,'F');

# 第二次增量备份
[root@Rocky8-mini ~]# xtrabackup -uroot -pwuhaolam --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

# 还原
## 将备份文件传输到还原主机
[root@Rocky8-mini ~]# rsync -a /backup root@192.168.119.138:/

## 预准备完成备份，--apply-log-only 阻止回滚未完成的事务
[root@Rocky8-mini2 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base

## 合并第一次增量备份到完全备份
[root@Rocky8-mini2 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

## 合并第二次增量备份到完全备份，最后一次不需要加 --apply-log-only
[root@Rocky8-mini2 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

## 复制数据库目录，数据库目录必须为空，MySQL服务不能启动
[root@Rocky8-mini2 ~]# xtrabackup --copy-back --target-dir=/backup/base
## 还原属性
[root@Rocky8-mini2 ~]# chown -R mysql:mysql /data/mysql/
## 启动服务
[root@Rocky8-mini2 ~]# service mysqld start

实现主从复制

【实验环境】

【实验步骤】

Master 设备
# 修改配置文件，二进制日志的目录需要自己创建并修改属主属组为mysql
[root@Rocky8-mini ~]# vim /etc/my.cnf
[mysqld]
log-bin=/mysql/binlog/mysql-bin
server-id=128
[root@Rocky8-mini ~]# systemctl restart mysqld.service

# 完全备份
[root@Rocky8-mini ~]# mysqldump -uroot -pwuhaolam -A -F --master-data=1 --single-transaction > /backup/all.sql

# 创建复制用户并授权
04:46:18(root@localhost) [(none)]> create user replyuser@"192.168.119.%" identified by "wuhaolam";
04:46:24(root@localhost) [(none)]> grant replication slave on *.* to replyuser@"192.168.119.%";

# 将备份数据复制到从节点
[root@Rocky8-mini ~]# scp /backup/all.sql root@192.168.119.138:/

Slave 设备
# 配置从节点
[root@Rocky8-mini2 ~]# vim /etc/my.cnf
server-id=138
read-only

# 从节点修改备份文件
[root@Rocky8-mini2 ~]# vim /all.sql
CHANGE MASTER TO MASTER_HOST='192.168.119.128', MASTER_USER='replyuser', MASTER_PASSWORD='wuhaolam', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154;

# 从节点还原
05:56:44(root@localhost) [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

05:56:54(root@localhost) [(none)]> source /all.sql;

05:57:12(root@localhost) [hellodb]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

# 开启从节点复制
06:10:51(root@localhost) [hellodb]> start slave;
Query OK, 0 rows affected (0.00 sec)

06:11:03(root@localhost) [hellodb]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.119.128
                  Master_User: replyuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 629
               Relay_Log_File: Rocky8-mini2-relay-bin.000002
                Relay_Log_Pos: 795
        Relay_Master_Log_File: mysql-bin.000007
             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: 629
              Relay_Log_Space: 1009
              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: 128
                  Master_UUID: fa5a0767-ba39-11ed-b05a-000c294da889
             Master_Info_File: /data/mysql/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: 
1 row in set (0.00 sec)

Note：在MySQL8.0中可能会报如下错误

error connecting to master ‘slave@192.168.0.104:3306’ - retry-time: 60 retries: 6 message:
Authentication plugin ‘caching_sha2_password’ reported error:
Authentication requires secure connection.

解决办法

如果是 MySQL8.0 的环境中进行主从复制，需要在从节点配置主节点信息时添加 “GET_MASTER_PUBLIC_KEY=1”

例如：CHANGE MASTER TO MASTER_HOST=’192.168.119.128’, MASTER_USER=’replyuser’, MASTER_PASSWORD=’wuhaolam’, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000007’, MASTER_LOG_POS=154;

实现级联复制

【实验环境】

【实验步骤】

Master 设备
# 修改配置文件，二进制日志的目录需要自己创建并修改属主属组为mysql
[root@Rocky8-mini ~]# vim /etc/my.cnf
[mysqld]
log-bin=/mysql/binlog/mysql-bin
server-id=128
[root@Rocky8-mini ~]# systemctl restart mysqld.service

# 完全备份
[root@Rocky8-mini ~]# mysqldump -uroot -pwuhaolam -A -F --master-data=1 --single-transaction > /backup/all.sql

# 创建复制用户并授权
04:46:18(root@localhost) [(none)]> create user replyuser@"192.168.119.%" identified by "wuhaolam";
04:46:24(root@localhost) [(none)]> grant replication slave on *.* to replyuser@"192.168.119.%";

# 将备份数据复制到从节点
[root@Rocky8-mini ~]# scp /backup/all.sql root@192.168.119.138:/
[root@Rocky8-mini ~]# scp /backup/all.sql root@192.168.119.129:/

中间级联 Slave1 配置
# log_slave_updates,级联复制中间节点必选项
[root@Rocky8-mini2 ~]# vim /etc/my.cnf
log-bin=/mysql/binlog/mysql-bin
server-id=138
read-only
log_slave_updates
[root@Rocky8-mini2 ~]# systemctl restart mysqld

# 还原数据库
[root@Rocky8-mini2 ~]# vim /all.sql
CHANGE MASTER TO MASTER_HOST='192.168.119.128', MASTER_USER='replyuser', MASTER_PASSWORD='wuhaolam', MASTER_PORT=3306, MASTER_LOG_     FILE='mysql-bin.000008', MASTER_LOG_POS=154;
07:25:18(root@localhost) [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

07:25:29(root@localhost) [(none)]> source /all.sql;
# 记录二进制位置，给第三个节点使用
07:25:57(root@localhost) [hellodb]> show master logs;

07:26:21(root@localhost) [hellodb]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

07:27:44(root@localhost) [hellodb]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Slave2 节点配置
# 修改配置文件 
[root@Rocky8-mini3 ~]# vim /etc/my.cnf
server-id=129
read-only
[root@Rocky8-mini3 ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

# 还原数据库
CHANGE MASTER TO MASTER_HOST='192.168.119.138', MASTER_USER='replyuser', MASTER_PASSWORD='wuhaolam', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=154;

[root@Rocky8-mini3 ~]# mysql -uroot -pwuhaolam < /all.sql
[root@Rocky8-mini3 ~]# mysql -uroot -pwuhaolam -e 'start slave;'

主主复制

一般用于主主复制的架构

【实验环境】

【实验步骤】

# 基于前面主从架构的基础上实现
## 在 Master2 上的配置
07:43:05(root@localhost) [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       343 |
| mysql-bin.000004 |       177 |
| mysql-bin.000005 |    871301 |
| mysql-bin.000006 |       177 |
| mysql-bin.000007 |       310 |
+------------------+-----------+
7 rows in set (0.00 sec)

## 在 Master1 上配置
06:42:52(root@localhost) [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.119.138', MASTER_USER='replyuser', MASTER_PASSWORD='wuhaolam', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=310;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

06:57:40(root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

半同步复制

默认情况下，MySQL的赋值功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库就结束，不验证从库是否接收完毕。当主服务器或从服务器发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。

官方文档

1
2
3

https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
https://mariadb.com/kb/en/library/semisynchronous-replication/

【实验步骤】

# 基于前面实现的主从复制架构，停止主从复制
[root@Rocky8-mini2 ~]# mysql -uroot -pwuhaolam -e 'stop slave;'

# 查看插件文件
[root@Rocky8-mini ~]# find / -name 'semisync*'
/usr/local/src/mysql-5.7.40-linux-glibc2.12-x86_64/lib/plugin/debug/semisync_master.so
/usr/local/src/mysql-5.7.40-linux-glibc2.12-x86_64/lib/plugin/debug/semisync_slave.so
/usr/local/src/mysql-5.7.40-linux-glibc2.12-x86_64/lib/plugin/semisync_master.so
/usr/local/src/mysql-5.7.40-linux-glibc2.12-x86_64/lib/plugin/semisync_slave.so

# master 配置，增加如下两行
[root@Rocky8-mini ~]# vim /etc/my.cnf
# 需要先安装semisync_master.so插件后，重启服务才可以成功
rpl_semi_sync_master_enabled=ON
# 设置3s内无法同步，也将返回成功信息给客户端
rpl_semi_sync_master_timeout=3000

# slave 配置
[root@Rocky8-mini2 ~]# vim /etc/my.cnf
# 需要先安装semisync_slave.so插件后，重启服务才可以成功
rpl_semi_sync_slave_enabled=ON

# master 配置
## 安装插件
08:00:37(root@localhost) [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
## 查看插件
08:01:34(root@localhost) [(none)]> show plugins;
## 临时修改变量
08:01:55(root@localhost) [(none)]> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.01 sec)

08:02:20(root@localhost) [(none)]> set global rpl_semi_sync_master_timeout=3000;
Query OK, 0 rows affected (0.00 sec)

08:02:34(root@localhost) [(none)]> show global variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)

08:02:57(root@localhost) [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

# 从服务器配置
09:04:40(root@localhost) [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

09:05:19(root@localhost) [(none)]> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

09:05:41(root@localhost) [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

# 如果已经实现主从复制，必须先 stop slave
09:06:13(root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

09:06:20(root@localhost) [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

【测试】

# 在master上创建数据库
08:03:54(root@localhost) [(none)]> create database db2;
Query OK, 1 row affected (0.01 sec)

# 停止从服务器复制线程
09:06:22(root@localhost) [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

# 重新在master上测试，需要等待3s（如果有多个从，只有所有从服务器都停止复制线程，主服务器才会等待）
08:09:13(root@localhost) [(none)]> create database db4;
Query OK, 1 row affected (3.01 sec)

复制过滤器

让从节点只复制指定的数据库或者是指定的数据库中的表

复制过滤器的两种实现方式

（1）服务器选项：主服务器仅向二进制日志中记录与指定数据库相关的事件

优点：只需要在主节点配置一次即可

缺点：基于二进制日志的还原将无法实现，因为本机的二进制日志也会受限于此配置，将不会记录

vim /etc/my.cnf
# 数据库白名单列表，不支持同时指定多个值，只能分开多行书写
binlog-do-db=db1
binlog-do-db=db2
# 数据库黑名单列表
binlog-ignore-db=

（2）从服务器 SQL_THREAD 在读取 Relay log 中的事件时，仅读取与特定数据库（特定表）相关的事件并更新自己的数据库信息

优点：不影响二进制备份还原

缺点：会造成网络及磁盘I/O浪费，在所有从节点都要配置

vim /etc/my.cnf
# 白名单
replicate-do-db=db1_name
replicate-do-db=db2_name
replicate-do-table=db_name.tbl_name
# 黑名单
replicate-ignore-db=
replicate-ignore-table=
# 支持通配符
## 数据库foo开头并且表名以bar开头
replicate-wild-do-table=foo%.bar%
replicate-wild-ignore-table=

# 注意: 在MySQL中，这些选项都为服务器选项，不支持在系统中查看
# 变量查看文档 
## https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html

MyCat

MyCat 介绍

MyCat是一个开源的分布式数据库系统，是一个实现了MySQL协议的Server，前端用户可以把它看做是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生（Native）协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分库分表，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。

MyCat后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。

MyCat 简单概括

开源，面向企业应用开发的大数据库集群
支持事务、ACID、可替代MySQL的加强版数据库
可以视为MySQL集群的企业级数据库，用来替代Oracle集群
一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL server
结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
一个新颖的数据库中间件产品

MyCat 工作原理

MyCat最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终返回给用户

MyCat高可用性

在生产环境中，MyCat节点最后使用双节点，可以使用的高可用的集群方式有

Keepalived + MyCat + MySQL
Keepalived + LVS + MyCat + MySQL
Keepalived + HAproxy + MyCat + MySQL

MyCat 安装

1、安装JDK

yum -y install java
[root@Rocky8-mini ~]# java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)

2、下载安装MyCat

wget http://dl.mycat.org.cn/1.6.7.6/20220524101549/Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz
mkdir /apps
tar xf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz -C /apps
ls /apps/mycat/
bin  catlet  conf  lib  logs  version.txt
echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
. /etc/profile.d/mycat.sh

# 启动mycat
[root@Rocky8-mini4 ~]# mycat start
Starting Mycat-server...
[root@Rocky8-mini4 ~]# cat /apps/mycat/logs/wrapper.log 
STATUS | wrapper  | 2023/03/12 04:38:27 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2023/03/12 04:38:28 | Launching a JVM...
INFO   | jvm 1    | 2023/03/12 04:38:28 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2023/03/12 04:38:28 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2023/03/12 04:38:28 | 
INFO   | jvm 1    | 2023/03/12 04:38:29 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO   | jvm 1    | 2023/03/12 04:38:30 | MyCAT Server startup successfully. see logs in logs/mycat.log

MyCat 主要配置文件说明

server.xml

server.xml几乎保存了所有mycat需要的系统配置信息。如连接MyCat的用户，密码，数据库名称等。

<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
        <property name="defaultSchema">TESTDB</property>
        <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->

        <!-- 表级 DML 权限设置 -->
        <!--            
        <privileges check="false">
                <schema name="TESTDB" dml="0110" >
                        <table name="tb01" dml="0000"></table>
                        <table name="tb02" dml="1111"></table>
                </schema>
        </privileges>           
         -->
</user>

<user name="user">
	<property name="password">user</property>
	<property name="schemas">TESTDB</property>
	<property name="readOnly">true</property>
	<property name="defaultSchema">TESTDB</property>
</user>

参数说明
user 用户配置节点
name 客户端登录MyCat的用户名，也就是客户端用来连接MyCat的用户名
password 客户端登录MyCat的密码
schemas 数据库名，此处和schema.xml文件中的配置关联，多个数据库名用逗号分开
privileges 配置用户针对表的增删改查的权限
readOnly MyCat逻辑库所具有的权限。true为只读，false为读写都有。

注意：

server.xml 文件里面登录MyCat的用户名和密码可以任意定义，这个账户和密码是为客户机登录MyCat时使用的账号
逻辑库名（如上中的TESTDB，是登录MyCat后显示的库名，切换这个库后，显示的就是代理的真实的MySQL数据库中的表）也要在schema.xml文件中被定义，否则会导致MyCat服务启动失败
此处只定义了一个标签，如果定义多个标签，设置了多个连接MyCat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库

schema.xml

该文件是最主要的配置项，关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置。MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
 
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="user" dataNode="dn1,dn2" rule="auto-sharding-long" />
		<table name="stat_tcp_stream" dataNode="dn2,dn3" rule="auto-sharding-long" />
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="mpos_tshark_miner_2014" />
	<dataNode name="dn2" dataHost="localhost2" database="mpos_tshark_miner2014" />
	<dataNode name="dn3" dataHost="localhost3" database="mpos_tshark_hrtel" />
	
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" dbType="mysql" dbDriver="jdbc" writeType="0" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="jdbc:mysql://192.168.1.150:5029" user="root" password="root"></writeHost>
	</dataHost>
	<dataHost name="localhost2" maxCon="1000" minCon="1" balance="0" dbType="mysql" dbDriver="jdbc"> 
		<heartbeat> 		</heartbeat>
		 <writeHost host="hostM2" url="jdbc:mysql://192.168.1.150:5029" user="root" 	password="root"></writeHost> 
	 </dataHost>		
	<dataHost name="localhost3" maxCon="1000" minCon="1" balance="0" dbType="mysql" dbDriver="jdbc"> 
		<heartbeat> 		</heartbeat>
		 <writeHost host="hostM3" url="jdbc:mysql://192.168.1.150:5029" user="root" 	password="root"></writeHost> 
	 </dataHost>		 
</mycat:schema>

【配置参数解释说明】

参数说明
schema 数据库设置，此数据库为逻辑数据库，name与server.xml文件中的schema对应
dataNode 分片信息，也就是分库相关配置
dataHost 物理数据库，真正存储数据的数据库

【配置说明】

name 属性唯一标识dataHost标签，供上层的标签使用
maxCon 属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数
minCon 属性指定每个读写实例连接池的最小连接，初始化连接池的大小

实战案例：利用MyCat实现MySQL读写分离

【实验架构】

【实验环境】

1
2
3

mycat-server   192.168.119.150
mysql-master   192.168.119.128
mysql-slave    192.168.119.138

【实验步骤】

1、创建主从复制架构，基于之前的实验

## 从节点查看主从信息
[root@mysql-slave ~]# mysql -uroot -pwuhaolam -e 'show slave status\G'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.119.128
                  Master_User: replyuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 877
               Relay_Log_File: Rocky8-mini2-relay-bin.000033
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000013
             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: 877
              Relay_Log_Space: 848
              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: 128
                  Master_UUID: fa5a0767-ba39-11ed-b05a-000c294da889
             Master_Info_File: /data/mysql/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:

2、在代理服务器 192.168.119.150 上安装 mycat 服务

1
2
3

# 安装过程见上文中MyCat安装，程序启动后，8066端口用于连接MyCat
[root@mycat-server ~]# ss -ntl | grep 8066
LISTEN 0      128                *:8066             *:*

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

[root@mycat-server ~]# vim /apps/mycat/conf/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>
                        <property name="dataNodeIdleCheckPeriod">300000</property>
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>

<user name="root" defaultAccount="true">                        
                <property name="password">wuhaolam</property>
                <property name="schemas">hellodb,db1</property>
</user>

注意: 这里使用的用户名为root,密码为wuhaolam,逻辑数据库名为hellodb和db1,这些信息可以随意定义,读写权限都有,没有对表做任何特殊的权限。其它默认。

4、修改schema.xml实现读写分离

[root@mycat-server ~]# 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="hellodb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <schema name="db1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"> </schema>

        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        <dataNode name="dn2" dataHost="localhost1" database="db1" />

        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.119.128:3306" user="mycat" password="wuhaolam">
                        <readHost host="hostS1" url="192.168.119.138:3306" user="mycat" password="wuhaolam">
                        </readHost>
                </writeHost>
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>
</mycat:schema>

注意: 要保证能使用mycat用户登录进入后面的真实的数据库。

5、在后端服务器创建用户并对MyCat授权

09:25:24(root@localhost) [(none)]> grant all on *.* to 'mycat'@'192.168.119.%' identified by 'wuhaolam';
Query OK, 0 rows affected, 1 warning (0.00 sec)

10:39:17(root@localhost) [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

6、连接MyCat服务器上连接并测试

:41: (root@192.168.119.150) [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| db1      |
| hellodb  |
+----------+
2 rows in set (0.01 sec)

7、开启通用日志确认实现读写分离

# 在主从服务器上临时开启通用日志
10:39:24(root@localhost) [(none)]> set global general_log=1;

# 插入的数据在 mysql-master 主机上显示日志信息
[root@mysql-master ~]# tail -f /data/mysql/Rocky8-mini.log 
2023-03-12T14:51:41.162054Z	  114 Query	select user()
2023-03-12T14:51:51.161289Z	  114 Query	select user()
2023-03-12T14:52:01.161901Z	  114 Query	select user()
2023-03-12T14:52:11.161869Z	  114 Query	select user()
2023-03-12T14:52:14.012241Z	  114 Init DB	hellodb
2023-03-12T14:52:14.012377Z	  114 Query	SET names utf8;
2023-03-12T14:52:14.012991Z	  114 Query	insert teachers values(7,'ShanZhi','35','M')
2023-03-12T14:52:21.162882Z	  114 Init DB	db1
2023-03-12T14:52:21.163033Z	  114 Query	select user()
2023-03-12T14:52:31.164418Z	  114 Query	select user()
2023-03-12T14:52:41.161604Z	  114 Query	select user()

# 查询的信息在 mysql-slave 上显示信息
[root@mysql-slave ~]# tail -f /data/mysql/Rocky8-mini2.log 
2023-03-12T14:49:43.965851Z	   50 Connect	mycat@192.168.119.150 on db1 using TCP/IP
2023-03-12T14:49:43.966087Z	   51 Connect	mycat@192.168.119.150 on hellodb using TCP/IP
2023-03-12T14:49:53.962295Z	   44 Query	select user()
2023-03-12T14:50:03.963241Z	   48 Query	select user()
2023-03-12T14:50:12.369474Z	   51 Query	SET names utf8;
2023-03-12T14:50:12.369682Z	   51 Query	show tables
2023-03-12T14:50:13.964787Z	   43 Query	select user()
2023-03-12T14:50:23.965344Z	   46 Query	select user()
2023-03-12T14:50:24.792032Z	   51 Query	select * from teachers
2023-03-12T14:50:33.962562Z	   50 Query	select user()

# select user() 为MyCat对后端服务器进行健康性检查
# 停止从节点，MyCat会将请求调度至主节点; 但停止主节点，不会将请求调度至从节点

MySQL 高可用性

MHA Master High Availability

MHA 工作原理和架构

MHA 集群架构

MHA 工作原理

1、MHA 利用 select 1 as value 指令判断 Master 服务器的健康性，一旦 Master 宕机，MHA 从宕机崩溃的 Master 保存 binlog events

2、识别含有最新的 slave，即从 Master 同步数据最全的 slave

3、应用差异的 Relay log 到其它的 slave

4、应用从 Master 保存的二进制日志事件 (binlog events)

5、提升一个 slave 为新的Master

6、使其它的 slave 连接新的 Master 进行复制

注意：为了尽可能减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL的半同步复制

MHA 软件

MHA 软件由两部分组成，Manager 工具包和 Node 工具包

Manager 工具包主要包含的工具如下:

masterha_check_ssh	           检查MHA的SSH配置状况
masterha_check_repl            检查MySQL复制状况
masterha_manager               启动MHA
masterha_check_status          检测当前MHA运行状态
masterha_master_monitor        检测master是否宕机
masterha_master_switch         故障转移(手动或自动)
masterha_conf_host             添加或删除配置的server信息
masterha_stop --conf=app1.cnf  停止MHA
masterha_secondary_check       两个或多个网络线路检查MySQL主服务器的可用

Node 工具包：这些工具通常由MHA Manager的脚本触发，无需人为操作

save_binary_logs                  保存和赋值master的二进制日志
apply_diff_relay_logs             识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog                去除不必要的ROLLBACK事件(MHA已经不在使用此工具)
purge_relay_logs                  清楚中继日志(不会阻塞SQL线程)

MHA 自定义扩展

secondary_check_script              通过多条网络路由检测master的可用性
master_ip_ailover_script            更新Application使用的masterip
shutdown_script                     强制关闭master节点
report_script                       发送报告
init_conf_load_script               加载初始配置参数
master_ip_online_change_script      更新master节点的IP地址

MHA 配置文件

1 2	global 配置，为各application提供默认配置，默认文件路径 /etc/masterha_default.cnf application 配置: 为每一个主从复制集群提供配置

MHA 实战案例

【实验架构图和环境】

【实验环境】
192.168.119.142  centos7  MHA管理端
192.168.119.138  Rocky8   mysql-master
192.168.119.129  Rocky8   mysql-slave1
192.168.119.150  Rocky8   mysql-slave2

1、在 MHA 管理节点安装 Manager 和 Node 包

下载地址：

https://github.com/yoshinorim/mha4mysql-manager/releases

https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58

[root@MHA ~]# ls
anaconda-ks.cfg  mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@MHA ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@MHA ~]# yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

2、在所有MySQL服务器上安装 mha4mysql-node 包

1
2
3

[root@mysql-master ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mysql-slave1 ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mysql-slave2 ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

3、在所有节点之间实现key验证

使用脚本实现key验证，注意根据自己的环境修改脚本中需要互相实现key验证的主机

4、在管理节点建立配置文件

[root@MHA ~]# mkdir /etc/mastermha
[root@MHA ~]# cat /etc/mastermha/app1.cnf 
[server default]
# 用于连接mysql所有节点的用户，需要拥有管理员权限
user=mhauser
password=wuhaolam
# 目录会自动生成
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
# 用于实现ssh基于key验证，访问二进制日志
ssh_user=root
# 主从复制的用户信息
repl_user=repluser
repl_password=wuhaolam
# 健康性检查的时间间隔
ping_interval=1
# 切换 VIP 的perl脚本
master_ip_failover_script=/usr/local/bin/master_ip_failover
# 主从发生切换时的报警脚本
report_script=/usr/local/bin/sendmail.sh
# 默认值为1,如果slave落后主库relay log超过100M,则主库不会选择此库作为Master
# 通过设置该参数,忽略复制的延时
check_repl_delay=0
# 指定二进制日志的存放位置
master_binlog_dir=/data/mysql/

[server1]
hostname=192.168.119.138
candidate_master=1
[server2]
hostname=192.168.119.129
# 设置为优先候选master
candidate_master=1
[server3]
hostname=192.168.119.150

说明：当主库宕机谁来接管新的master

1
2
3

1、所有节点日志都是一致的，默认会以配置文件的顺序去选择一个新主
2、从节点日志不一致，自动选择最接近于主库的从库充当新主
3、如果对于某个节点设置了权重（candidate_master=1）,权重节点会优先选择。但是此节点日志量落后于主库超过100M也不会被选择。可以配合check_repl_delay=0,关闭日志量的检查，强制选择候选节点

5、相关脚本

# 报警脚本，前提需要先配置好邮件发送
[root@MHA ~]# vim /usr/local/bin/sendmail.sh
#!/bin/bash
echo "MySQL is down" | mail -s 'MHA warning' wuhaolam@163.com
[root@MHA ~]# chmod +x /usr/local/bin/sendmail.sh

# 切换VIP的perl脚本
[root@MHA ~]# cat /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
# 设置VIP,gateway,broadcast,vip所在的网卡
my $vip = '192.168.119.100/24';
my $brdc = '192.168.119.255';
my $gateway = '192.168.119.2';
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
[root@MHA ~]# chmod +x /usr/local/bin/master_ip_failover

6、实现数据库Master节点

# 脚本自动安装MySQL5.7
[root@mysql-master ~]# cat mysql_install.sh 
#!/bin/bash

. /etc/init.d/functions
PackageDir="/root"
PackageName="mysql-5.7.40-linux-glibc2.12-x86_64"
MysqlRootPassword="wuhaolam"
ID=`ip a show eth0 | awk -F'[./]' '/\<inet\>/ {print $4}'`

yum -y install libaio numactl-libs libncurses* &> /dev/null || { echo -e "\033[1;31minstall package fail\033[0m";exit 1; }

if ! id mysql &> /dev/null;then
	groupadd mysql
	useradd -r -g mysql -s /sbin/nologin mysql
fi

if [ -d /data/mysql ];then
	action "数据库存在，退出安装" /bin/false
	exit 1
else
	mkdir -p /data/mysql/ && chown -R mysql:mysql /data/mysql/
fi

[ -e ${PackageDir}/${PackageName}.tar.gz ] || { echo -e "\033[1;31mPackage file is not exits\033[0m";exit 1; }

tar xf ${PackageDir}/${PackageName}.tar.gz -C /usr/local/
cd /usr/local/
ln -s ${PackageName}/ mysql
chown -R root:root /usr/local/mysql/

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

cat > /etc/my.cnf << EOF
[client]
port=3306
socket=/data/mysql/mysql.sock

[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"

[mysqld]
log-bin=/data/mysql/mysql-bin
server-id=${ID}
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0
skip_name_resolve=1

log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
EOF

mysqld --initialize --user=mysql --datadir=/data/mysql &> /dev/null 

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start &> /dev/null && echo -e "\033[1;32mMySQL startup success\033[0m" || { echo -e "\033[1;31mMySQL startup failed\033[0m";exit 1; }

Password=`awk '/A temporary password/ {print $11}' /data/mysql/mysql.log`
mysqladmin -uroot -p${Password} password ${MysqlRootPassword} &> /dev/null && action "数据库安装完成" /bin/true || action "密码修改失败" /bin/false

--------------------------------------------------------------------
# 安装完成后,MySQL主配置及相关信息查看
:08: (root@localhost) [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       414 |
+------------------+-----------+
2 rows in set (0.00 sec)

:08: (root@localhost) [(none)]> create user repluser@'192.168.119.%' identified by 'wuhaolam';
Query OK, 0 rows affected (0.00 sec)

:11: (root@localhost) [(none)]> grant replication slave on *.* to repluser@'192.168.119.%';
Query OK, 0 rows affected (0.00 sec)

:12: (root@localhost) [(none)]> create user mhauser@'192.168.119.%' identified by 'wuhaolam';
Query OK, 0 rows affected (0.00 sec)

:12: (root@localhost) [(none)]> grant all on *.* to mhauser@'192.168.119.%';
Query OK, 0 rows affected (0.00 sec)

:16: (root@localhost) [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 配置VIP
[root@mysql-master ~]# ifconfig eth0:1 192.168.119.100/24

7、实现slave

# 脚本安装MySQL5.7,从节点的配置文件中关闭relay_log_purge
# slave2 的配置同slave1
[root@mysql-slave1 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/mysql/mysql.sock

[mysql]
prompt="\r:\m:\s(\u@\h) [\d]>\_"

[mysqld]
log-bin=/data/mysql/mysql-bin
server-id=129
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0
skip_name_resolve=1
relay_log_purge=0

log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

:59: (root@localhost) [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.119.138', MASTER_USER='repluser', MASTER_PASSWORD='wuhaolam', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=414;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

:01: (root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

8、检查MHA环境

# 前面两个的结果显示ok即为成功
[root@MHA ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@MHA ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
[root@MHA ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

9、启动MHA

[root@MHA ~]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null &

# 查看MHA的状态，可以看到当前的master节点的信息
[root@MHA ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:4523) is running(0:PING_OK), master:192.168.119.138
# 查看MHA的日志，也可以看见
[root@mha ~]# cat /data/mastermha/app1/manager.log | grep "current master"
Thu Mar 23 22:45:26 2023 - [info] Checking SSH publickey authentication settings on the current master..
192.168.119.138(192.168.119.138:3306) (current master)


# 若启动出错，可查看 /data/mastermha/app1/manager.log 日志

10、模拟故障

# 在mysql的master节点上关闭mysql服务
[root@mysql-master ~]# service mysqld stop

# 当MySQL主节点宕机后，MHA管理程序会自动退出
[root@MHA ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

使用 tail -f /data/mastermha/app1/manager.log 查看具体的主从节点切换

在从节点上查看主从相关信息

# 在slave1节点上查看VIP是否正常切换
[root@mysql-slave1 ~]# ip a show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:8b:d7:3c brd ff:ff:ff:ff:ff:ff
    inet 192.168.119.129/24 brd 192.168.119.255 scope global noprefixroute eth0
       valid_lft forever preferred_lft forever
    inet 192.168.119.100/24 brd 192.168.119.255 scope global secondary eth0:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe8b:d73c/64 scope link 
       valid_lft forever preferred_lft forever
       
# 在slave2节点上查看新主信息是否切换
:39: (root@localhost) [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.119.129
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-slave2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 154
              Relay_Log_Space: 534
              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: 129
                  Master_UUID: a3e552fe-ca22-11ed-9336-000c298bd73c
             Master_Info_File: /data/mysql/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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

查看邮件告警信息是否发送

11、故障修复

# 恢复 MySQL，将之前的MySQL主节点重新启动
[root@mysql-master ~]# service mysqld start

# 记录目前主服务器的二进制位置，并配置之前的MySQL主节点为新主的从节点
## 新主节点查看
[root@mysql-slave1 ~]# mysql -uroot -pwuhaolam -e 'show master logs;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       461 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
## 修改原主节点为新主节点的从节点
[root@mysql-master ~]# mysql -uroot -pwuhaolam -e "CHANGE MASTER TO MASTER_HOST='192.168.119.129', MASTER_USER='repluser', MASTER_PASSWORD='wuhaolam', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;"
[root@mysql-master ~]# mysql -uroot -pwuhaolam -e 'start slave;'

# 再次运行MHA文件
[root@MHA ~]# rm -f /data/mastermha/app1/app1.failover.complete
## 重新检查环境
[root@MHA ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@MHA ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
[root@MHA ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
## 启动
[root@mha ~]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null &
[1] 2990
[root@mha ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:2990) is running(0:PING_OK), master:192.168.119.129