第12周作业
MySQL主从复制及主主复制的实现
MySQL主从复制的实现
环境准备
| 服务器 | IP | 数据库 | 主从 |
| master | 10.0.0.7 | MariaDB-10.4.22 | 主 |
| slave | 10.0.0.17 | MariaDB-10.4.22 | 从 |
主节点
修改master主节点的配置
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二进制文件存放目录，修改所有者和所属组
[root@master ~]#mkdir /data/logbin/ -p
[root@master ~]#chown -R mysql.mysql /data/logbin/
重启数据库服务
[root@master ~]#systemctl restart mysqld
数据库完全备份
[root@master ~]#mysqldump -A -F -uroot -pMySQL@2022. --master-data=1 --single-transaction > /data/all.sql
创建复制用户并授权
MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
将备份复制到从节点
[root@master ~]#scp /data/all.sql 10.0.0.17:/data
从节点
修改slave从节点的配置
[root@slave ~]#vim /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
read_only=on
log-bin=/data/logbin/mysql-bin
建立二进制文件存放目录，修改所有者和所属组
[root@slave ~]#mkdir /data/logbin/ -p
[root@slave ~]#chown -R mysql.mysql /data/logbin/
重启数据库服务
[root@slave ~]#systemctl restart mysqld
从节点修改备份文件
[root@slave ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=328;
关闭二进制日志
MariaDB [mysql]> set sql_log_bin=off;
导入备份数据
MariaDB [mysql]> source /data/all.sql
查看从节点备份状态
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 328
Relay_Log_File: centos7-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
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
开启从节点slave进程，从节点开始复制
MariaDB [mysql]> start slave;
再次查看从节点备份状态
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 714
Relay_Log_File: centos7-relay-bin.000002
Relay_Log_Pos: 941
Relay_Master_Log_File: mysql-bin.000002
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: 714
Relay_Log_Space: 1252
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: 7
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: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
查看从节点的账号信息及数据库
MariaDB [mysql]> select user,host from mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| repluser | 10.0.0.% |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-----------+
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
从节点开启二进制日志
MariaDB [mysql]> set sql_log_bin=on;
MySQL主主复制的实现
环境准备
| 服务器 | IP | 数据库 |
| master1 | 10.0.0.7 | MariaDB-10.4.22 |
| master2 | 10.0.0.17 | MariaDB-10.4.22 |
master1节点
修改master1的配置
[root@master1 ~]#cat /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二进制文件存放目录，修改所有者和所属组
[root@master1 ~]#mkdir /data/logbin/ -p
[root@master1 ~]#chown -R mysql.mysql /data/logbin/
重启数据库服务
[root@master1 ~]#systemctl restart mysqld
查看二进制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
创建复制用户并授权
MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
MariaDB [mysql]> FLUSH PRIVILEGES;
完成备份后再次查看二进制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 656 |
+------------------+-----------+
确定同步master2的数据
MariaDB [mysql]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
开启master1进程，开始复制
MariaDB [mysql]> start slave;
查看master1备份状态
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: 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: 866
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: 17
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
创建数据库db1
MariaDB [mysql]> create database db1;
查看master2 创建的db2数据
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
master2节点
修改master2的配置
[root@master2 ~]#cat /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二进制文件存放目录，修改所有者和所属组
[root@master2 ~]#mkdir /data/logbin/ -p
[root@master2 ~]#chown -R mysql.mysql /data/logbin/
重启数据库服务
[root@master2 ~]#systemctl restart mysqld
查看二进制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
确定同步master1的数据
MariaDB [mysql]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
查看master2备份状态
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: master2-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
开启master2进程，开始复制
MariaDB [mysql]> start slave;
再次查看master2备份状态
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 656
Relay_Log_File: master2-relay-bin.000002
Relay_Log_Pos: 883
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: 656
Relay_Log_Space: 1194
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: 7
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: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
再次查看二进制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
查看master2的账号信息
MariaDB [mysql]> select user,host from mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| repluser | 10.0.0.% |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-----------+
查看master1 创建的db1数据
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
创建db2数据库
MariaDB [mysql]> create database db2;
mariabackup实现完全备份+增量备份+binlog恢复库
注：在MariaDB 10.3及更高版本中，建议使用Mariabackup替代 Percona XtraBackup的备份方法。
在MariaDB 10.3及更高版本中，不支持 Percona XtraBackup。
mariabackup工具备份和还原，需要三步实现
- 备份：对数据库做完全或增量备份
- 预准备： 还原前，先对备份的数据，整理至一个临时目录
- 还原：将整理好的数据，复制回数据库目录中
环境准备
centos7:MariaDB-10.4.22:10.0.0.7:备份
centos7:MariaDB-10.4.22:10.0.0.17:还原
查看备份前的数据库
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| hellodb2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
完全备份
在原主机做完全备份到/backup
[root@backup ~]#mkdir /backup
#创建用户并授权
MariaDB [mysql]> CREATE USER mariabackup@'localhost' IDENTIFIED BY 'mypassword';
MariaDB [mysql]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
MariaDB [mysql]> FLUSH PRIVILEGES;
[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/base
[root@backup ~]#ll /backup/base/
total 12352
-rw-r----- 1 root root 24576 Aug 8 09:29 aria_log.00000001
-rw-r----- 1 root root 52 Aug 8 09:29 aria_log_control
-rw-r----- 1 root root 324 Aug 8 09:29 backup-my.cnf
drwx------ 2 root root 4096 Aug 8 09:29 hellodb
drwx------ 2 root root 4096 Aug 8 09:29 hellodb2
-rw-r----- 1 root root 976 Aug 8 09:29 ib_buffer_pool
-rw-r----- 1 root root 12582912 Aug 8 09:28 ibdata1
-rw-r----- 1 root root 2560 Aug 8 09:29 ib_logfile0
drwx------ 2 root root 4096 Aug 8 09:29 mysql
drwx------ 2 root root 4096 Aug 8 09:29 performance_schema
-rw-r----- 1 root root 75 Aug 8 09:29 xtrabackup_checkpoints
-rw-r----- 1 root root 444 Aug 8 09:29 xtrabackup_info
第一次修改数据
MariaDB [hellodb]> 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 |
+-----+---------------+-----+--------+
MariaDB [hellodb]> insert teachers values(null,'wang',18,'M');
MariaDB [hellodb]> insert teachers values(null,'weiow',18,'M');
MariaDB [hellodb]> 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 | wang | 18 | M |
| 6 | weiow | 18 | M |
+-----+---------------+-----+--------+
增量备份
第一次增量备份
[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
[root@backup ~]#ll /backup/
total 8
drwx------ 6 root root 4096 Aug 8 09:29 base
drwx------ 6 root root 4096 Aug 8 09:31 inc1
第二次修改数据
MariaDB [hellodb]> insert teachers values(null,'zhang',19,'M');
MariaDB [hellodb]> insert teachers values(null,'jie',20,'M');
MariaDB [hellodb]> 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 | wang | 18 | M |
| 6 | weiow | 18 | M |
| 7 | zhang | 19 | M |
| 8 | jie | 20 | M |
+-----+---------------+-----+--------+
第二次增量备份
[root@backup ~]#mariabackup -umariabackup -pmypassword --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
[root@backup ~]#ll /backup/
total 12
drwx------ 6 root root 4096 Aug 8 10:12 base
drwx------ 6 root root 4096 Aug 8 10:20 inc1
drwx------ 6 root root 4096 Aug 8 10:25 inc2
[root@backup ~]#du -sh /backup/*
16M /backup/base
3.0M /backup/inc1
3.0M /backup/inc2
数据库还原
mysql节点停止数据库服务
[root@mysql ~]#systemctl stop mysqld
确保datadir数据目录为空
#注：数据库目录必须为空，否则MySQL服务不能启动
[root@mysql ~]#cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
datadir=/data/mysql
[root@mysql ~]#rm -rf /data/mysql/*
backup节点开始复制
#目标主机无需创建/backup目录,直接复制目录本身
[root@backup ~]#scp -r /backup/ 10.0.0.17:/
查看backup节点复制的文件
[root@mysql ~]#ll /backup/
total 12
drwx------ 6 root root 4096 Aug 8 11:10 base
drwx------ 6 root root 4096 Aug 8 11:10 inc1
drwx------ 6 root root 4096 Aug 8 11:10 inc2
预准备完全备份
#准备现有备份以还原到 MariaDB 服务器
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base
#合并第1次增量备份到完全备份
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc1
#合并第2次增量备份到完全备份
[root@mysql ~]#mariabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
复制到数据库目录
#注意数据库目录必须为空，MySQL服务不能启动
[root@mysql ~]#mariabackup --copy-back --target-dir=/backup/base
#还原属性
[root@mysql ~]#chown -R mysql:mysql /data/mysql
#重启数据库服务
[root@mysql ~]#systemctl restart mysqld
[root@mysql ~]#du -sh /data/mysql
112M /data/mysql
mysql还原节点确认数据的完整性
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| hellodb2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
#注：如果出现这种问题，需要reboot重启系统
MariaDB [hellodb]> select * from teachers;
ERROR 1932 (42S02): Table 'hellodb.teachers' doesn't exist in engine
[root@mysql ~]#reboot
MariaDB [hellodb]> 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 | wang | 18 | M |
| 6 | weiow | 18 | M |
| 7 | zhang | 19 | M |
| 8 | jie | 20 | M |
+-----+---------------+-----+--------+
MyCAT实现MySQL读写分离
环境准备：
mycat-server:CentOS7.9-10.0.0.27
mysql-master:CentOS7.9-10.0.0.7:MariaDB-10.4.22 写节点
mysql-slave:CentOS7.9-10.0.0.17:MariaDB-10.4.22 读节点
关闭SELinux和防火墙
systemctl stop firewalld
setenforce 0
时间同步
主节点
修改master主节点的配置
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin
建立二进制文件存放目录，修改所有者和所属组
[root@master ~]#mkdir /data/logbin/ -p
[root@master ~]#chown -R mysql.mysql /data/logbin/
重启数据库服务
[root@master ~]#systemctl restart mysqld
查看二进制位置
MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
创建复制用户并授权
MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
MariaDB [mysql]> FLUSH PRIVILEGES;
导入hellodb数据库
MariaDB [mysql]> source /root/hellodb_innodb.sql
启用通用日志，查看读写分离
MariaDB [mysql]> set global general_log=on;
MariaDB [mysql]> show variables like 'general%';
+------------------+------------+
| Variable_name | Value |
+------------------+------------+
| general_log | ON |
| general_log_file | master.log |
+------------------+------------+
#永久保存需要写到配置文件里
[root@master ~]#vim /etc/my.cnf
[mysqld]
general_log=ON
[root@master ~]#tail -f /data/mysql/master.log
#确认主节点写
220227 0:44:58 325 Query update teachers set age=@@server_id where tid=4
#停止从节点后，确认主节点开始读
220227 1:19:02 331 Query select * from students
停止主节点
[root@master ~]#systemctl stop mysqld
从节点
修改slave从节点的配置
[root@slave ~]#vim /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
read_only=on
log-bin=/data/logbin/mysql-bin
建立二进制文件存放目录，修改所有者和所属组
[root@slave ~]#mkdir /data/logbin/ -p
[root@slave ~]#chown -R mysql.mysql /data/logbin/
重启数据库服务
[root@slave ~]#systemctl restart mysqld
确定同步master主节点的数据
MariaDB [mysql]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;
开启slave从节点进程，开始复制
MariaDB [mysql]> start slave;
查看slave从节点备份状态
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 656
Relay_Log_File: centos7-relay-bin.000002
Relay_Log_Pos: 883
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: 656
Relay_Log_Space: 1194
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: 7
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: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
查看master主节点导入的hellodb数据库
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
启用通用日志，查看读写分离
MariaDB [mysql]> set global general_log=on;
MariaDB [mysql]> show variables like 'general%';
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | ON |
| general_log_file | slave.log |
+------------------+-----------+
#永久保存需要写到配置文件里
[root@master ~]#vim /etc/my.cnf
[mysqld]
general_log=ON
[root@slave ~]#tail -f /data/mysql/slave.log
#确认从节点读
220809 0:34:48 447 Query select * from teachers
停止从节点
[root@slave ~]#systemctl stop mysqld
启动从节点
[root@slave ~]#systemctl start mysqld
mycat节点
环境准备
[root@mycat ~]#yum -y install java
[root@mycat ~]#wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@mycat ~]#mkdir /apps
[root@mycat ~]#[root@mycat ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-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
[root@mycat ~]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
[root@mycat ~]#mycat start
Starting Mycat-server...
#可以看到打开多个端口,其中8066端口用于连接MyCAT
[root@mycat ~]#ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 1 127.0.0.1:32000 *:*users:(("java",pid=2478,fd=4))
LISTEN 0 128 *:22 *:*users:(("sshd",pid=1293,fd=3))
LISTEN 0 100 127.0.0.1:25 *:*users:(("master",pid=1387,fd=13))
LISTEN 0 50 [::]:1984 [::]:*users:(("java",pid=2478,fd=70))
LISTEN 0 128 [::]:8066 [::]:*users:(("java",pid=2478,fd=94))
LISTEN 0 50 [::]:40998 [::]:*users:(("java",pid=2478,fd=69))
LISTEN 0 128 [::]:9066 [::]:*users:(("java",pid=2478,fd=90))
LISTEN 0 50 [::]:37942 [::]:*users:(("java",pid=2478,fd=71))
LISTEN 0 128 [::]:22 [::]:*users:(("sshd",pid=1293,fd=4))
LISTEN 0 100 [::1]:25 [::]:*users:(("master",pid=1387,fd=14))
#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/08/09 17:17:47 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/08/09 17:17:47 | Launching a JVM...
INFO | jvm 1 | 2022/08/09 17:17:49 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/08/09 17:17:49 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/08/09 17:17:49 |
INFO | jvm 1 | 2022/08/09 17:17:59 | MyCAT Server startup successfully. see logs in logs/mycat.log
在mycat 服务器上修改server.xml文件，配置Mycat的连接信息
[root@mycat ~]#vim /apps/mycat/conf/server.xml
#删除注释“<!-- -->”,并修改下面行的8066改为3306
<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> #5 * 60 * 1000L; //连
接空闲检查 删除#号后面的部分
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
<user name="root" defaultAccount="true"> #连接Mycat的用户名
<property name="password">MyCAT2022.</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相对应
修改schema.xml实现读写分离策略
[root@mycat ~]#cp /apps/mycat/conf/schema.xml /apps/mycat/conf/schema.xml.bak
[root@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="10.0.0.7:3306" user="root"
password="123456">
<readHost host="host2" url="10.0.0.17:3306" user="root"
password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
重新启动mycat
[root@mycat ~]#mycat restart
#查看日志，确定成功
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2022/08/09 17:25:15 | at io.mycat.config.util.ConfigUtil.getDocument(ConfigUtil.java:115)
INFO | jvm 1 | 2022/08/09 17:25:15 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:111)
INFO | jvm 1 | 2022/08/09 17:25:15 | ... 13 more
STATUS | wrapper | 2022/08/09 17:25:18 | <-- Wrapper Stopped
STATUS | wrapper | 2022/08/09 17:25:18 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/08/09 17:26:18 | Launching a JVM...
INFO | jvm 1 | 2022/08/09 17:27:23 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/02/26 21:24:25 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/02/26 21:24:25 |
INFO | jvm 1 | 2022/02/26 21:24:28 | MyCAT Server startup successfully. see logs in logs/mycat.log
#连接MyCAT的端口已改为3306
[root@mycat ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 100 [::]:3306 [::]:*
LISTEN 0 100 [::]:9066 [::]:*
LISTEN 0 50 [::]:43274 [::]:*
LISTEN 0 50 [::]:37134 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
在Mycat服务器上连接并测试
[root@mycat ~]#mysql -uroot -pMyCAT2022. -h127.0.0.1 -DTESTDB
MySQL [TESTDB]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
MySQL [TESTDB]> use TESTDB;
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave |
+------------+
在主和从服务器分别启用通用日志后，查看读写分离
MySQL [TESTDB]> 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 |
+-----+---------------+-----+--------+
MySQL [TESTDB]> update teachers set age=@@server_id where tid=4;
#确认主节点写
MySQL [TESTDB]> 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 | 7 | F |
+-----+---------------+-----+--------+
停止从节点后，MyCAT自动调度读请求至主节点
MySQL [TESTDB]> select * from students;
#停止从节点后，确认主节点开始读
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
#启动从节点后，确认从节点读
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
停止主节点，MyCAT不会自动调度写请求至从节点
MySQL [TESTDB]> update teachers set age=@@server_id where tid=3;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
Ansible常用模块介绍
Command 模块
功能：在远程主机执行命令，此为默认模块，可忽略 -m 选项
Shell 模块
和command相似，用shell执行命令，支持各种符号，比如：*,$, >
注意：此模块不具有幂等性
Script 模块
功能：在远程主机上运行ansible服务器上的脚本(无需执行权限)
注意：此模块不具有幂等性
Copy 模块
功能：从ansible服务器主控端复制文件到远程主机
注意: src=file 如果是没指明路径，则为当前目录或当前目录下的files目录下的file文件
Get_url 模块
功能: 用于将文件从http、https或ftp下载到被管理机节点上
Fetch 模块
功能：从远程主机提取文件至ansible的主控端，该模块的工作原理与[copy]类似，但与之相反，它用于从远程机器获取文件，并将它们存储在本地文件树中，按主机名组织，目前不支持目录
File 模块
功能：设置文件属性，创建软链接等
stat 模块
功能：检查文件或文件系统的状态
unarchive 模块
功能：解包解压缩
Archive 模块
功能：打包压缩保存在被管理节点
Hostname 模块
功能：管理主机名，注意，此模块不修改“/etc/hosts”。
Cron 模块
功能：计划任务，使用此模块管理crontab和环境变量条目。
支持时间：minute，hour，day，month，weekday
Yum 和 Apt 模块
功能：
yum 管理软件包，只支持RHEL，CentOS，fedora，不支持Ubuntu其它版本；
apt 模块管理 Debian 相关版本的软件包
Service 模块
功能：管理服务，控制远程主机上的服务
User 模块
功能：管理用户，管理用户帐户和用户属性
Group 模块
功能：管理组
Replace 模块
功能：该模块有点类似于sed命令，主要也是基于正则表达式进行匹配和替换，建议使用此模块将替换文件中模式的所有实例
SELinux 模块
功能：管理 SELInux 策略，配置SELinux模式和策略，使用后可能需要重新启动
mount 挂载和卸载
功能: 挂载和卸载文件系统，此模块控制“/etc/fstab”中的活动和配置装载点。
Setup 模块
功能： setup 模块来收集主机的系统信息，这些 facts 信息可以直接以变量的形式使用，但是如果主机较多，会影响执行速度，playbooks会自动调用此模块，以收集有关的有用变量可以在剧本中使用的远程主机
可以使用 gather_facts：no 来禁止 Ansible 收集 facts 信息
debug 模块
功能：此模块可以用于输出信息，并且通过 msg 定制输出的信息内容，该模块在执行期间打印语句，对调试非常有用
注意：msg后面的变量有时需要加 " " 引起来
