第十二周作业
一、主从复制及主主复制的实现
1.1 主从复制
环境准备：
Rocky8-Master：192.168.119.146
Rocky8-Backup：192.168.119.145
1.1.1 编辑Mysql主设备配置文件
# [mysqld]中添加如下字段
[root@Rocky8-Master ~]# vim /etc/my.cnf
[mysqld]
server-id=146
log-bin=/data/mysql/logbin/mysql-bin
# 创建二进制文件并重启MySQL服务
[root@Rocky8-Master ~]# mkdir -p /data/mysql/logbin/
[root@Rocky8-Master ~]# touch /data/mysql/logbin/mysql-bin
[root@Rocky8-Master ~]# chown -R mysql:mysql /data/mysql/
[root@Rocky8-Master ~]# systemctl restart mariadb.service
1.1.2 查看二进制文件的信息并创建拥有复制权限的用户账号
[root@Rocky8-Master ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master logs; # 记录此时二进制文件的位置，此后发生事件产生的二进制文件都需要复制到从节点
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
# 创建账号并授权
MariaDB [(none)]> create user repluser@'192.168.119.%' identified by '123456';
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.119.%';
Query OK, 0 rows affected (0.001 sec)
1.1.3 配置从节点相关信息
# 修改配置文件，增加如下两行
[root@Rocky8-Backup ~]# vim /etc/mysql/my.cnf
[mysqld]
server_id=145
read-only
[root@Rocky8-Backup ~]# systemctl restart mariadb.service
# 使用有复制权限的账号连接至主节点
[root@Rocky8-Backup ~]# mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.25-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.119.146', # 当前主节点的地址
-> MASTER_USER='repluser', # 主节点创建的授权的账户
-> MASTER_PASSWORD='123456', # 密码
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', # 主节点记录的二进制日志的文件，从该处之后开始同步
-> MASTER_LOG_POS=328; # 修改成与主节点对应的二进制数
Query OK, 0 rows affected (0.005 sec)
# 查看当前从节点状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.119.146
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: Rocky8-Back-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 328
Relay_Log_Space: 256
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: NULL
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: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)![image]
1.1.4 从节点启动复制线程并查看主从节点线程状态
# 开启线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
# 从节点的I/O线程和SQL线程已经打开
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 8 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
| 9 | system user | | NULL | Slave_IO | 8 | Waiting for master to send event | NULL | 0.000 |
| 10 | system user | | NULL | Slave_SQL | 8 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
+----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+
8 rows in set (0.000 sec)
主
MariaDB [(none)]> show processlist; # 主节点的dump线程也已经打开
+----+-------------+-----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 9 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
| 10 | repluser | 192.168.119.145:34724 | NULL | Binlog Dump | 389 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
+----+-------------+-----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
7 rows in set (0.000 sec)
# 线程成功打开后，主从一直处于连接状态
[root@Rocky-Master ~]# ss -nt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
ESTAB 0 96 192.168.119.146:22 192.168.119.1:7930
ESTAB 0 0 [::ffff:192.168.119.146]:3306 [::ffff:192.168.119.145]:34724
1.1.5 验证
# 主节点数据库初始状态
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
# 从节点数据库初始状态
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
# 在主上导入一个数据库
[root@Rocky-Master ~]# ls
anaconda-ks.cfg hellodb_innodb.sql
[root@Rocky-Master ~]# mysql < hellodb_innodb.sql
# 再次查看从节点，自动复制成功
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
1.2 主主复制
实验环境
Rocky8-Master1：192.168.119.145
Rocky8-Master2：192.168.119.146
1.2.1 编辑配置文件
# 编辑Master1的配置文件，并创建相关文件
[root@Rocky8-Master1 ~]# cat /etc/mysql/my.cnf
[mysqld]
server-id=145
log-bin=/data/mysql/mysql-bin
[root@Rocky8-Master1 ~]# touch /data/mysql/mysql-bin
[root@Rocky8-Master1 ~]# chown mysql:mysql /data/mysql/mysql-bin
# 编辑Master2的配置文件并创建相关文件
[root@Rocky8-Master2 ~]# cat /etc/my.cnf
[mysqld]
server-id=146
log-bin=/data/mysql/mysql-bin
[root@Rocky8-Master2 ~]# mkdir -p /data/mysql/
[root@Rocky8-Master2 ~]# touch /data/mysql/mysql-bin
[root@Rocky8-Master2 ~]# chown -R mysql:mysql /data/mysql/
# 重启服务
[root@Rocky8-Master1 ~]# systemctl restart mariadb.service
[root@Rocky8-Master2 ~]# systemctl restart mariadb.service
1.2.2 记录Master1和Master2此时的日志节点，并在Master1上创建拥有复制权限的账号
# Master1 的日志节点信息
[root@Rocky8-Master1 ~]# mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.25-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.001 sec)
# Master2 日志节点信息
[root@Rocky8-Master2 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
# 创建账号，只需在一个节点创建具有复制权限的账号即可，当Master1节点数据复制过去后，Master2上自然就有了该账号，在Master1上创建
MariaDB [(none)]> create user repluser@'192.168.119.%' identified by '123456';
Query OK, 0 rows affected (0.010 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.119.%';
Query OK, 0 rows affected (0.001 sec)
1.2.3 配置Master2为Master1的从节点
# 在Master2上设置被复制的节点信息
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.119.145',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=328;
Query OK, 0 rows affected (0.003 sec)
# 开启线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
# 单项复制已经完成，记录此时主2节点的日志信息
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
1.2.4 配置Master1为Master2的从节点
# 在Master1上设置Master2的信息
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.119.146',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=328;
Query OK, 0 rows affected (0.004 sec)
# 开启线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
# 查看Slave_IO和Slave_SQL线程是否开启
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.119.146
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: Rocky8-Master1-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 328
Relay_Log_Space: 873
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: 146
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified
1.2.5 验证
# 在主1上创建一个db1的数据库，查看主2的同步情况
# 主1
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.000 sec)
# 主2
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.012 sec)
# 在主2上创建一个db2的数据库，在主1上查看结果
# 主2
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.000 sec)
# 主1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.009 sec)
二、xtrabackup实现全量+增量+binlog恢复库
实验环境
Rocky8-mini2 做备份
Rocky8-mini5 做还原
数据库：mysql8.0
2.1 准备好8.0版本的软件包并安装
[root@Rocky8-mini2 ~]# ls
percona-xtrabackup-80-8.0.28-21.1.el8.x86_64.rpm
[root@Rocky8-mini2 ~]# yum -y install percona-xtrabackup-80-8.0.28-21.1.el8.x86_64.rpm
[root@Rocky8-mini5 ~]# yum -y install percona-xtrabackup-80-8.0.28-21.1.el8.x86_64.rpm
2.2 修改配置文件，指定二进制日志记录的位置
# 编辑配置文件
[root@Rocky8-mini2 ~]# vim /etc/my.cnf
[mysqld]
log-bin=/data/mysql/logbin/mysql-bin
# 创建二进制文件存放目录
[root@Rocky8-mini2 ~]# mkdir -p /data/mysql/logbin
[root@Rocky8-mini2 ~]# touch /data/mysql/logbin/mysql-bin
[root@Rocky8-mini2 ~]# chown -R mysql:mysql /data/mysql/
[root@Rocky8-mini2 ~]# systemctl restart mysqld.service
2.3 创建数据库备份目录
[root@Rocky8-mini2 ~]# mkdir /backup
2.4 在Rocky8-mini2上创建备份
# 查看当前数据库信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use hellodb;
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 |
+-------------------+
7 rows in set (0.00 sec)
# 创建完全备份
[root@Rocky8-mini2 ~]# xtrabackup -uroot --backup --target-dir=/backup/base
# 第一次修改数据
mysql> insert into teachers values (5,'Tom',18,'M');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Tom | 18 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
# 第一次增量备份
[root@Rocky8-mini2 ~]# xtrabackup -uroot --backup --target-dir=/backup/increase1 --incremental-basedir=/backup/base
# 第二次修改数据
mysql> insert into teachers
-> values (6,'Jerry',18,'F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers where TID=6;
+-----+-------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------+-----+--------+
| 6 | Jerry | 18 | F |
+-----+-------+-----+--------+
1 row in set (0.00 sec)
# 第二次增量备份
[root@Rocky8-mini2 ~]# xtrabackup -uroot --backup --target-dir=/backup/increase2 --incremental-basedir=/backup/increase1
# 刷新日志记录，记录当前使用的二进制日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 156 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 第三次修改数据
mysql> insert into teachers
-> values(7,'Harry',20,'M');
Query OK, 1 row affected (0.00 sec)
# 备份二进制日志
[root@Rocky8-mini2 ~]# mysqlbinlog /data/mysql/logbin/mysql-bin.000007 > /backup/inc.sql
2.5 将备份文件复制到远程主机
[root@Rocky8-mini2 ~]# scp -r /backup root@192.168.119.146:/
2.6 Rocky8-mini5上做数据库还原
# 远程主机还原
# 准备完全备份
# --apply-log-only 阻止回滚未完成的事务，最后一次增量备份还原不阻止，使事务处于一个完整的状态
[root@Rocky8-mini5 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
# 合并第一次增量备份完全备份
[root@Rocky8-mini5 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/increase1
# 合并第二次增量备份到完全备份，此为最后一次还原，不阻止事务的回滚
[root@Rocky8-mini5 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/increase2
# 复制数据库目录
# 数据库目录必须为空且MySQL服务不能启动
[root@Rocky8-mini5 ~]# xtrabackup --copy-back --target-dir=/backup/base
# 修改数据库属性，并启动数据库服务
[root@Rocky8-mini5 ~]# chown -R mysql:mysql /var/lib/mysql
[root@Rocky8-mini5 ~]# systemctl enable --now mysqld
2.7 Rocky8-mini5使用xtrabackup 还原后验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use hellodb;
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> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Tom | 18 | M |
| 6 | Jerry | 18 | F |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
2.8 使用二进制日志还原数据库最新状态并查看结果
# 利用二进制还原最新状态
mysql> set sql_log_bin=0;
mysql> source /backup/inc.sql;
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
# 查看结果
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Tom | 18 | M |
| 6 | Jerry | 18 | F |
| 7 | Harry | 20 | M |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
三、MyCAT实现MySQL读写分离
环境准备：
Rocky8-Master：192.168.119.146
Rocky8-Backup：192.168.119.136
Centos8-Mycat：192.168.119.148
Centos7-mini2： 192.168.119.147
注：Rocky8-Master和Rocky8-Backup为主从复制架构，此处不进行具体部署操作
3.1 在Centos8-Mycat机器上安装 Mycat
# 安装JDK
[root@Centos8-Mycat ~]# yum -y install java
# 安装mycat
[root@Centos8-Mycat ~]# ls
anaconda-ks.cfg Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@Centos8-Mycat ~]# mkdir /apps
[root@Centos8-Mycat ~]# tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
# 生成环境变量
[root@Centos8-Mycat bin]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@Centos8-Mycat bin]# . /etc/profile.d/mycat.sh
# 启动mycat
[root@Centos8-Mycat bin]# mycat start
Starting Mycat-server...
[root@Centos8-Mycat ~]# tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/06/05 11:38:40 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/06/05 11:38:40 | Launching a JVM...
INFO | jvm 1 | 2022/06/05 11:39:06 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/06/05 11:39:06 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/06/05 11:39:06 |
INFO | jvm 1 | 2022/06/05 11:39:32 | MyCAT Server startup successfully. see logs in logs/mycat.log # 见到此行则成功
# 启动失败解决
[root@Centos8-Mycat ~]# vim /apps/mycat/conf/wrapper.conf
wrapper.startup.timeout=300 # 增加此行内容
3.2 修改mycat相关配置文件
# 修改文件更改默认的8066端口为3306，修改默认的密码123456
[root@Centos8-Mycat ~]# vim /apps/mycat/conf/server.xml
<property name="serverPort">3306</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</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">wuhao</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
# 实现读写分离
[root@Centos8-Mycat ~]# 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="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0"
dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="192.168.119.146:3306" user="root" password="123456">
<readHost host="host2" url="192.168.119.138:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
3.3 验证mycat服务器
# 在Master上创建mycat中定义的权限账号
mysql> create user root@'192.168.119.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to root@'192.168.119.%' ;
# 在从服务器查看，账号已经同步，主从复制也没有问题
mysql> select user,host from mysql.user;
+------------------+---------------+
| user | host |
+------------------+---------------+
| repluser | 192.168.119.% |
| root | 192.168.119.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+---------------+
6 rows in set (0.00 sec)
# 在客户机上远程连接到mycat服务器上
[root@centos7-mini2 ~]# mysql -uroot -p123456 -h 192.168.119.148 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.119.148' (111)
[root@centos7-mini2 ~]# mysql -uroot -pwuhao -h 192.168.119.148 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.119.148' (111)
[root@centos7-mini2 ~]# mysql -uroot -pwuhao -h 192.168.119.148
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> 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 |
+-------------------+
7 rows in set (0.01 sec)
3.4 验证读写分离操作
# 在主从节点上都临时打开通用日志监测功能
mysql> set global general_log=1;
# 实时查看监控日志
[root@Rocky8-Master ~]# tail -f /var/lib/mysql/Rocky8-Master.log
[root@Rocky8-Backup mysql]# tail -f /var/lib/mysql/Rocky8-Backup.log
（1）读操作
read.png
（2）写操作
write.png
3.5 验证读写数据库的切换
# 从服务器宕机，观察读操作能否转换到主服务器
# mycat服务器目前进行读操作的服务器
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 138 |
+-------------+
1 row in set (0.00 sec)
# 从服务器宕机，mycat服务器目前进行读操作的服务器
[root@Rocky8-Backup mysql]# systemctl stop mysqld.service
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
mysql> select @@server_id; # 读操作切换到主服务器
+-------------+
| @@server_id |
+-------------+
| 146 |
+-------------+
1 row in set (0.00 sec)
# 恢复从服务器，主服务器宕机
# 正常写操作
mysql> update teachers
-> set name='jerry'
-> where Tid=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[root@Rocky8-Master ~]# systemctl stop mysqld.service
mysql> update teachers set name='Tom' where Tid=5; # 主服务器宕机，无法实现从代替主进行写操作
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
四、ansible常用模块介绍
环境介绍
control 节点：192.168.119.147
node1 节点：192.168.119.145 dev 主机组
node2 节点：192.168.119.129 test 主机组
node3 节点：192.168.119.138 prod 主机组
node4 节点：192.168.119.146 prod 主机组
# control节点作为各节点的控制端，安装Ansible并对各节点做基于key验证
[root@control ~]# ansible --version
ansible 2.9.27
config file = /etc/ansible/ansible.cfg
configured module search path = [u'/root/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /usr/bin/ansible
python version = 2.7.5 (default, Oct 14 2020, 14:45:30) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]
[root@control ~]# cd .ssh/
[root@control .ssh]# ls
authorized_keys id_rsa id_rsa.pub known_hosts
# 修改配置文件使在个节点上执行的任务以root执行，将个节点信息添加到主机清单文件中
[root@control ~]# vim /etc/ansible/ansible.cfg +107
remote_user = root
[root@control ~]# vim /etc/ansible/hosts
[dev]
192.168.119.145
[test]
192.168.119.129
[prod]
192.168.119.138
192.168.119.146
# 测试
[root@control ~]# ansible all -m ping
192.168.119.146 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": false,
"ping": "pong"
}
192.168.119.145 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": false,
"ping": "pong"
}
192.168.119.138 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": false,
"ping": "pong"
}
192.168.119.129 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": false,
"ping": "pong"
}
4.1 command和shell模块
功能：command 为默认模块，但不支持 $VARNAME、< 、>、|、&等，可用shell模块实现
范例：
# 默认command模块
[root@control ~]# ansible dev -a 'touch file1'
[WARNING]: Consider using the file module with state=touch rather than running 'touch'. If you need to use command because file is insufficient you can
add 'warn: false' to this command task or set 'command_warnings=False' in ansible.cfg to get rid of this message.
192.168.119.145 | CHANGED | rc=0 >>
# 使用shell模块，此处引号应用单引号
[root@control ~]# ansible prod -m shell -a "echo $HOSTNAME"
192.168.119.146 | CHANGED | rc=0 >>
control
192.168.119.138 | CHANGED | rc=0 >>
control
[root@control ~]# ansible prod -m shell -a 'echo $HOSTNAME'
192.168.119.138 | CHANGED | rc=0 >>
node3
192.168.119.146 | CHANGED | rc=0 >>
node4
4.2 script模块
功能：在远程主机上运行ansible服务器上的脚本
[root@control ~]# ansible test -m script -a '/data/test.sh'
192.168.119.129 | CHANGED => {
"changed": true,
"rc": 0,
"stderr": "Shared connection to 192.168.119.129 closed.\r\n",
"stderr_lines": [
"Shared connection to 192.168.119.129 closed."
],
"stdout": "authorized_keys id_rsa id_rsa.pub known_hosts\r\n",
"stdout_lines": [
"authorized_keys id_rsa id_rsa.pub known_hosts"
]
}
4.3 copy模块
功能：从控制端复制文件到远程主机
[root@control ~]# ansible dev -m copy -a "src=/root/boot.html dest=/tmp/"
4.4 get_url模块
功能：将文件从http、https或ftp下载到被管理节点上
[root@control ~]# ansible test -m get_url -a 'url=http://nginx.org/download/nginx-1.18.0.tar.gz dest=/usr/local/src/nginx.tar.gz'
4.5 fetch模块
功能：从远程节点提取文件到控制端，但不支持目录的复制
[root@control ~]# ansible test -m fetch -a 'src=/root/file dest=/root/'
4.6 file模块
功能：创建目录、文件、软链接等，设置属性等
[root@control ~]# ansible test -m file -a 'path=/data/mysql state=directory'
4.7 stat模块
功能：检查文件或文件系统的状态
[root@control ~]# ansible dev -m stat -a 'path=/etc/passwd'
4.8 unarchive模块
功能：解包解压缩
两种方式：
1、将控制节点的压缩包传到远程主机后解压缩到特定目录，默认为此，copy=yes，可省略
2、将远程主机上的压缩包解压到指定的路径下，需设置copy=no
[root@control ~]# ansible test -m unarchive -a 'src=http://nginx.org/download/nginx-1.18.0.tar.gz dest=/usr/local/src/ copy=no'
4.9 archive模块
功能：打包压缩保存在被控制节点
[root@control ~]# ansible test -m archive -a 'path=/var/log/ dest=/data/log.tar.gz format=gz mode=0600'
4.10 hostname模块
功能：管理主机名
[root@control ~]# ansible test -m hostname -a 'name=node2_test'
192.168.119.129 | CHANGED => {
"ansible_facts": {
"ansible_domain": "",
"ansible_fqdn": "node2_test",
"ansible_hostname": "node2_test",
"ansible_nodename": "node2_test",
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": true,
"name": "node2_test"
}
4.11 cron模块
功能：计划任务
# 创建任务
[root@control ~]# ansible test -m cron -a 'name="check content" minute=*/2 job="ls /root/"'
# 删除任务
[root@control ~]# ansible test -m cron -a "name='check content' state=absent"
4.12 yum和apt模块
功能：
yum 管理红帽系列，不支持Ubuntu
apt 管理debian系列软件包
[root@control ~]# ansible test -m yum -a 'name=httpd state=present'
4.13 yum_repository模块
功能：用于创建yum仓库
[root@control ~]# ansible-doc yum_repository
- name: Add multiple repositories into the same file (1/2)
yum_repository:
name: epel
description: EPEL YUM repo
file: external_repos
baseurl: https://download.fedoraproject.org/pub/epel/$releasever/$basearch/
gpgcheck: no
4.14 service模块
功能：管理服务
[root@control ~]# ansible all -m service -a 'name=httpd state=started enabled=yes'
4.15 user模块
功能：管理用户
[root@control ~]# ansible all -m user -a 'name=wh comment="test user" uid=2008 home=/app/wh group=wh'
4.16 group模块
功能：管理组
[root@control ~]# ansible all -m group -a 'name=nginx gid=88 system=yes
4.17 lineinfile模块
功能：类似于sed命令，一般用于单行替换；如果是修改，匹配到多行则按照最后一次匹配到的处理，删除则全部删除
[root@control ~]# ansible-doc lineinfile
- name: Ensure SELinux is set to enforcing mode
lineinfile:
path: /etc/selinux/config
regexp: '^SELINUX='
line: SELINUX=enforcing
4.18 replace模块
功能：类似于sed命令，单行和多行替换都可
# 匹配到UUID开头的全部注释
[root@control ~]# ansible all -m replace -a "path=/etc/fstab regexp='^(UUID.*)' replace='#\1'"
4.19 selinux模块
功能：管理selinux策略
[root@control ~]# ansible all -m selinux -a 'state=disabled'
4.20 reboot模块
功能：重启主机
4.21 mount模块
功能：挂载和卸载文件系统
[root@control ~]# ansible-doc mount
- name: Mount DVD read-only
mount:
path: /mnt/dvd
src: /dev/sr0
fstype: iso9660
opts: ro,noauto
state: present
4.22 setup模块
功能：用于收集主机的系统信息，这些信息可以直接以变量的形式使用，但如果主机多，会影响执行速度
gather_facts: no ,禁止Ansible收集facts信息
# 获取默认IP地址
[root@control ~]# ansible dev -m setup -a 'filter=ansible_default_ipv4'
192.168.119.145 | SUCCESS => {
"ansible_facts": {
"ansible_default_ipv4": {
"address": "192.168.119.145",
"alias": "ens33",
"broadcast": "192.168.119.255",
"gateway": "192.168.119.2",
"interface": "ens33",
"macaddress": "00:0c:29:d3:f6:4f",
"mtu": 1500,
"netmask": "255.255.255.0",
"network": "192.168.119.0",
"type": "ether"
},
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": false
}
4.23 debug模块
功能：用于输出信息，并通过msg定制输出的信息内容
[root@control ~]# ansible-doc debug
- debug:
msg: System {{ inventory_hostname }} has uuid {{ ansible_product_uuid }}
