第六周作业

1. 通过编译、二进制安装MySQL5.7
   二进制安装
   1、安装相关包
   yum -y install libaio numactl-libs

2、创建用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

3、准备程序文件
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.31-linuxglibc2.12-x86_64.tar.gz
tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local/
ln -s mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
chown -R root.root /usr/local/mysql/

4、准备环境变量
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.s

5、准备配置文件
cp /etc/my.cnf{,.bak}
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

6、初始化数据库文件并提取root密码，生成 root 空密码
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

7、准备服务脚本和启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
8、修改口令

修改前面生成的空密码为指定密码（密码要定期修改）

mysqladmin -uroot password MySQL@2022.

9、测试登录
mysql -uroot -pMySQL@2022.

编译安装
1、源码包下载，官网下载地址：https://downloads.mysql.com/archives/community/
、安装相关依赖包

[root@centos7 ~]#yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper
3、创建用户和数据目录

[root@centos7 ~]#useradd -r -s /sbin/nologin -d /data/mysql mysql
[root@centos7 ~]#id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)
4、创建数据库目录，修改权限

[root@centos7 ~]#mkdir /data/mysql -pv
mkdir: created directory ‘/data/mysql’
[root@centos7 ~]#chown mysql.mysql /data/mysql/
[root@centos7 ~]#ll -d /data/mysql/
drwxr-xr-x 2 mysql mysql 6 Jan 27 20:42 /data/mysql/
5、解压缩源码包

[root@centos7 ~]#tar xvf mysql-boost-5.7.30.tar.gz -C /usr/local/src
[root@centos7 ~]#tar xvf boost_1_59_0.tar.gz -C /usr/local/src

6、源码编译安装 MySQL

[root@centos7 ~]#cd /usr/local/src/mysql-5.7.30/
[root@centos7 mysql-5.7.30]#pwd
/usr/local/src/mysql-5.7.30
[root@centos7 mysql-5.7.30]#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_PARTITION_STORAGE_ENGINE=1
-DWITHOUT_MROONGA_STORAGE_ENGINE=1
-DWITH_DEBUG=0
-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0
-DENABLED_LOCAL_INFILE=1
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DDOWNLOAD_BOOST=1
-DWITH_BOOST=/usr/local/src/boost_1_59_0

[root@centos7 mysql-5.7.30]#make && make install
提示：如果出错，执行rm -f CMakeCache.txt

7、准备环境变量

[root@centos7 mysql-5.7.30]#echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7 mysql-5.7.30]#. /etc/profile.d/mysql.sh
8、修改配置文件

[root@centos7 mysql-5.7.30]#cd /apps/mysql/
[root@centos7 mysql]#pwd
/apps/mysql

[root@centos7 mysql]#vim /etc/my.cnf
[mysqld]
explicit_defaults_for_timestamp=true
datadir=/data/mysql/
basedir=/apps/mysql/bin/mysql/
port=3306
pid-file=/data/mysql/mysql.pid
socket=/data/mysql/mysql.socket
symbolic-links=0
character_set_server=utf8
user=mysql

[mysqld_safe]
log-error=/data/mysql/mysql.log

[client]
port=3306
socket=/data/mysql/mysql.socket
default-character-set=utf8
9、初始化数据库

[root@centos7 mysql]#bin/mysqld --initialize-insecure --datadir=/data/mysql/ --user=mysql
10、准备启动脚本,并启动服务

[root@centos7 mysql]#cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7 mysql]#chkconfig --add mysqld

2. 二进制安装mariadb10.4
   安装前的准备：
   找到官网，http://mariadb.org，找到mariadb-10.4.22-linux-glibc_214-x86_64.tar.gz版本
   安装步骤：

1、安装相关包

[root@centos7 ~]#yum -y install libaio numactl-libs
2、创建用户和组

[root@centos7 ~]#groupadd mysql
[root@centos7 ~]#useradd -r -g mysql -s /bin/false mysql
3、准备程序文件

[root@centos7 ~]#tar xfv mariadb-10.4.22-linux-glibc_214-x86_64.tar.gz -C /usr/local
[root@centos7 ~]#cd /usr/local/
[root@centos7 local]#ln -s mariadb-10.4.22-linux-glibc_214-x86_64 mysql
[root@centos7 local]#chown -R root.root /usr/local/mysql/
4、准备环境变量

[root@centos7 local]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7 local]#. /etc/profile.d/mysql.sh
5、创建数据库目录，修改权限

[root@centos7 local]#mkdir /data/mysql -pv
mkdir: created directory ‘/data/mysql’
[root@centos7 local]#chown -R mysql:mysql /data/mysql/
6、准备配置文件

[root@centos7 local]#cd /usr/local/mysql/
[root@centos7 mysql]#cp /etc/my.cnf{,.bak}
[root@centos7 mysql]#vim /etc/my.cnf
[root@centos7 mysql]#cat /etc/my.cnf
[mysqld]

datadir=/var/lib/mysql

datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Settings user and group are ignored when systemd is used.

If you need to run mysqld under a different user or group,

customize your systemd unit file for mariadb according to the

instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

log-error=/data/mysql/mysql.log
pid-file=/var/run/mariadb/mariadb.pid

include all files from the config directory

!includedir /etc/my.cnf.d

7、创建配置文件中指定的路径并修改权限

[root@centos7 mysql]#mkdir /var/lib/mysql
[root@centos7 mysql]#chown -R mysql:mysql /var/lib/mysql/
[root@centos7 mysql]#touch /var/lib/mysql/mysql.sock
[root@centos7 mysql]#ll /var/lib/mysql
total 0
-rw-r--r-- 1 root root 0 Feb 1 13:00 mysql.sock
[root@centos7 mysql]#chmod guo+wr /var/lib/mysql/mysql.sock
[root@centos7 mysql]#ll /var/lib/mysql
total 0
-rw-rw-rw- 1 root root 0 Feb 1 13:00 mysql.sock
[root@centos7 mysql]#touch /data/mysql/mysql.log
[root@centos7 mysql]#ll /data/mysql/
total 0
-rw-r--r-- 1 root root 0 Feb 1 13:18 mysql.log
[root@centos7 mysql]#chmod guo+rw /data/mysql/mysql.log
[root@centos7 mysql]#ll /data/mysql/mysql.log
-rw-rw-rw- 1 root root 0 Feb 1 13:18 /data/mysql/mysql.log
[root@centos7 mysql]#mkdir /var/run/mariadb
[root@centos7 mysql]#chown -R mysql:mysql /var/run/mariadb/
[root@centos7 mysql]#touch /var/run/mariadb/mariadb.pid
[root@centos7 mysql]#ll /var/run/mariadb/
total 0
-rw-r--r-- 1 root root 0 Feb 1 13:32 mariadb.pid
[root@centos7 mysql]#chmod guo+rw /var/run/mariadb/mariadb.pid
[root@centos7 mysql]#ll /var/run/mariadb/
total 0
-rw-rw-rw- 1 root root 0 Feb 1 13:32 mariadb.pid
8、初始化数据库文件并生成 root 空密码

[root@centos7 mysql]#./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
9、启动MariaDB守护程序

[root@centos7 mysql]#./bin/mysqld_safe --user=mysql --datadir=/data/mysql &
10、测试MariaDB守护程序

[root@centos7 mysql]#cd ./mysql-test ; perl mysql-test-run.pl
11、准备服务脚本和启动

[root@centos7 mysql]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7 mysql]#chkconfig --add mysqld
[root@centos7 mysql]#chkconfig --list mysqld

Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.

  If you want to list systemd services use 'systemctl list-unit-files'.
  To see services enabled on particular target use
  'systemctl list-dependencies [target]'.

mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

[root@centos7 mysql]#systemctl start mysqld.service

12、数据库的登录、查询，修改密码，Ctrl+D，退出数据库

[root@centos7 mysql]#ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
[root@centos7 mysql]#./bin/mysql -uroot -p
MariaDB [(none)]> show databases;
MariaDB [(none)]> use mysql;
MariaDB [mysql]> select user,host,password from user;
MariaDB [mysql]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.4.22-MariaDB |
+-----------------+
MariaDB [mysql]> GRANT ALL PRIVILEGES ON . TO root@'localhost' IDENTIFIED BY "MySQL@2022.";
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> Bye
[root@centos7 mysql]#
13、登录测试

[root@centos7 mysql]#mysql -uroot -pMySQL@2022.
14、加固MySQL服务器，在安装完成后，运行mysql_secure_installation命令，提高安全性

运行脚本：mysql_secure_installation

设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库

[root@centos7 mysql]#mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none): #输入root用户的当前密码
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n] n #root帐户已受保护，回答“n”
... skipping.
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] n #已经设置好密码了，不用改，回答“n”
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y #删除匿名用户，回答“y”
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y #不允许root用户远程登录，回答“y”
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove database and access to it? [Y/n] y #删除test数据库,回答“y”

• Dropping test database...
  ... Success!
• Removing privileges on test database...
  ... Success!
  Reloading the privilege tables will ensure that all changes made so far
  will take effect immediately.
  Reload privilege tables now? [Y/n] y #重新加载特权表,回答“y”
  ... Success!
  Cleaning up...
  All done! If you've completed all of the above steps, your MariaDB
  installation should now be secure.

Thanks for using MariaDB!

3. 导入hellodb.sql生成数据库
   (1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
   

(2) 以ClassID为分组依据，显示每组的平均年龄

(3) 显示第2题中平均年龄大于30的分组及平均年龄

(4) 显示以L开头的名字的同学的信息

4.数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

5.主从复制及主主复制的实现
主从复制

主主复制
主节点配置文件

[mysqld]
server-id=1 #为当前节点设置一个全局惟的ID号（不能和主节点一
log-bin #启用二进制
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
从节点配置文件:

[mysqld]
server-id=2
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
配置连接master的相关信息

change master to MASTER_HOST='192.168.0.117',MASTER_USER='repluser',MASTER_PASSWORD='testdb',MASTER_PORT=3306,MASTER_LOG_FILE=' mariadb-bin.000001',MASTER_LOG_POS=330;
start slave;
查看主从状态

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.114
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 1198
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试验证：

在114上执行

create database t1;
create table t1(id int auto_increment primary key,name char(10));
insert t1 (name) values('user1');
在117上执行

insert t1 (name) values('user2');
结果验证

MariaDB [db1]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 3 | user2 |
+----+-------+
6.xtrabackup实现全量+增量+binlog恢复库
1.下载并安装xtrabackup包

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
2.在主机做完全备份到/backup目录（备份目录为一级目录时可自动创建目录）

xtrabackup -uroot -p123456 --backup --target-dir=/backup/
3.在目标主机上还原mysql

把备份文件直接scp至目标主机

[root@localhost ~]# scp -r /backup root@192.168.0.116:/
在目标主机上还原

预准备：确保数据一致，提交完成的事务，回滚未完成的事务

[root@localhost ~]# xtrabackup --prepare --target-dir=/backup

复制到数据库目录

注意：数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup

还原属性

[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql

启动服务

[root@localhost ~]# systemctl start mysqld
查看是否已经还原成功

[root@localhost ~]# mysql -uroot -p123456
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 2
Server version: 5.7.35 MySQL Community Server (GPL)

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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
增量备份以及还原

新增数据

mysql> create database testdb1;
Query OK, 1 row affected (0.01 sec)

mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)
第一次增量

xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/
新增数据

mysql> create database testdb3;
Query OK, 1 row affected (0.01 sec)

mysql> create database testdb4;
Query OK, 1 row affected (0.00 sec)
拷贝到目标主机

[root@localhost ~]# scp -r /backup root@192.168.0.116:/backup1

备份过程生成三个备份目录

[root@localhost ~]# ls -al /backup1
total 12352
drwxr-x---. 8 root root 261 Aug 29 07:51 .
dr-xr-xr-x. 19 root root 4096 Aug 29 07:51 ..
-rw-r-----. 1 root root 487 Aug 29 07:51 backup-my.cnf
-rw-r-----. 1 root root 316 Aug 29 07:51 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Aug 29 07:51 ibdata1
drwxr-x---. 8 root root 4096 Aug 29 07:51 inc1
drwxr-x---. 10 root root 4096 Aug 29 07:51 inc2
drwxr-x---. 2 root root 4096 Aug 29 07:51 mysql
drwxr-x---. 2 root root 8192 Aug 29 07:51 performance_schema
drwxr-x---. 2 root root 8192 Aug 29 07:51 sys
drwxr-x---. 2 root root 112 Aug 29 07:51 testdb
-rw-r-----. 1 root root 22 Aug 29 07:51 xtrabackup_binlog_info
-rw-r-----. 1 root root 135 Aug 29 07:51 xtrabackup_checkpoints
-rw-r-----. 1 root root 476 Aug 29 07:51 xtrabackup_info
-rw-r-----. 1 root root 2560 Aug 29 07:51 xtrabackup_logfile
备份主机开始还原

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

6）启动服务：
[root@centos7 ~]#systemctl start mysqld
查看是否还原

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb1 |
| testdb2 |
| testdb3 |
| testdb4 |

7.MyCAT实现MySQL读写分离
所有主机的系统环境：

[root@mycat ~]# cat /etc/centos-release
CentOS Linux release 8.3.2011
服务器共三台

client 10.0.0.7
mycat-server 10.0.0.8 #内存建议2G以上
mysql-master 10.0.0.18 MySQL 8.0
mysql-slave 10.0.0.28 MySQL 8.0
关闭SELinu和防火墙

systemctl stop firewalld
setenforce 0
时间同步
1.创建 MySQL 主从数据库

主服务器

[root@master ~]# yum -y install mysql-server
[root@master ~]# systemctl enable --now mysqld

从服务器

[root@slave ~]# yum -y install mysql-server
[root@slave ~]# systemctl enable --now mysqld
1)修改master和slave上的配置文件

master上的my.cnf

[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id=18
[root@master ~]# systemctl restart mysqld

slave上的my.cnf

[root@slave ~]# vim /etc/my.cnf
[mysqld]
server-id=28
[root@slave ~]# systemctl restart mysqld
2)Master上创建复制用户（10.0.0.8）

[root@master ~]# mysql
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 179 | No |
| binlog.000002 | 156 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> create user repluser@'10.0.0.%' identified by '123456' ;
mysql> grant replication slave on . to repluser@'10.0.0.%';
3) Slave上执行（10.0.0.18）

[root@slave ~]# mysql
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=156;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 11277
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 11442
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略...
2、在MySQL代理服务器10.0.0.8安装mycat并启动

[root@mycat ~]# yum -y install java
[root@mycat ~]# 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)

下载并安装

[root@mycat ~]# wge thttp://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]# mkdir /apps
[root@mycat ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

配置环境变量

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

启动mycat 注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动 建议内存3G

[root@mycat ~]# mycat start
Starting Mycat-server...

查看日志，确定成功，可能需要等一会儿才能看到成功的提示

[root@mycat ~]# tail -f /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2021/12/25 00:22:40 | MyCAT Server startup successfully. see logs in logs/mycat.log

用默认密码123456来连接mycat

[root@centos7 ~]# mysql -uroot -p123456 -h10.0.0.8 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
4、在mycat 服务器上修改server.xml文件配置Mycat的连接信息(10.0.0.8)

...省略...

删除注释,并修改下面行的8066改为3306

[root@mycat ~]# cat /apps/mycat/conf/server.xml
3306
46 3306
47 300000
48 15000
49 0.0.0.0
50 300000 #删掉 5 * 60 * 1000L; //连接空闲检查 删除#后面此部分

110 #连接mycat的用户名
111 magedu #连接mycat的密码
112 TESTDB #数据库名要和schema.xml相对应
...省略...
125
这里使用的是root，密码为magedu,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都

有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。

5、修改schema.xml实现读写分离策略（10.0.0.8）

[root@mycat ~]# vim /apps/mycat/conf/schema.xml

<mycat:schema xmlns:mycat="http://io.mycat/">

#其中mycat表
示后端服务器实际的数据库名称

select user()




</mycat:schema>

以上***部分表示原配置文件中需要修改的内容

注意大小写

最终的文件内容

<mycat:schema xmlns:mycat="http://io.mycat/">




select user()




</mycat:schema>

重新启动mycat

[root@mycat ~]# mycat restart
上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.18为主库，10.0.0.28为

从库

注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，

也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否

则会导致登录mycat后，对库和表操作失败！

范例：schema.xml

6.在后端主服务器创建用户并对mycat授权（10.0.0.18）

[root@master ~]# mysql
mysql> create root@'10.0.0.%' identified by '123456';
mysql> grant all on . to root@'10.0.0.%';
7、在客户端上连接并测试(10.0.0.7)

[root@centos7 ~]# mysql -uroot -pmagedu -h10.0.0.8
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_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
8.通过通用日志确认实现读写分离

在mysql中查看通用日志

查看慢日志是否开启

mysql> show variables like 'general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/slave.log |
+------------------+--------------------------+
2 rows in set (0.01 sec)

开启慢日志

mysql> set global general_log =1;

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

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

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
general_log=ON
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#tail -f /var/lib/mysql/centos8.log
9、停止从节点，MyCAT自动调度读请求至主节点

[root@slave ~]#systemctl stop mysqld
[root@client ~]#mysql -uroot -pmagedu -h10.0.0.8 -P8066
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+ 1 row in set (0.00 sec)
mysql>

停止主节点，MyCAT不会自动调度写请求至从节点

MySQL [TESTDB]> insert teachers values(5,'wang',30,'M');
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
10、MyCAT对后端服务器的健康性检查方法select user()

开启通用日志

[root@master ~]#mysql
mysql> set global general_log=1;
[root@slave ~]#mysql
mysql> set global general_log=1;

查看通用日志

[root@master ~]# tail -f /var/lib/mysql/master.log
/usr/libexec/mysqld, Version: 8.0.26 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2022-01-02T15:42:42.887840Z 60 Query select user()
2022-01-02T15:42:52.889744Z 58 Query select user()
2022-01-02T15:43:02.887842Z 57 Query select user()

[root@slave ~]# tail -f /var/lib/mysql/slave.log
/usr/libexec/mysqld, Version: 8.0.26 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2021-12-25T17:38:29.668676Z 45 Query select user()
2021-12-25T17:38:32.291701Z 46 Query show variables like 'general%'
2021-12-25T17:38:39.670626Z 42 Query select user()
2021-12-25T17:38:49.669060Z 43 Query select user()
2021-12-25T17:38:59.673280Z 44 Query select user()