1、主从复制及主主复制的实现
主从复制实现
MySQL的主从复制,读写分离,每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
主从复制相关线程
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
主节点配置:
#修改主节点配置文件,重新启动服务
[root@master ~]# echo -e "server-id=128 \nlog-bin" >> /etc/my.cnf.d/mysql-server.cnf
[root@master ~]# systemctl restart mysqld.service
#查看二进制文件和位置
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.000001 | 179 | No |
| master-bin.000002 | 204 | No |
| master-bin.000003 | 156 | No |
+-------------------+-----------+-----------+
3 row in set (0.00 sec)
#备份数据库
[root@master ~]# mysqldump -A -F --master-data=1 --single-transaction > /data/all.sql
#创建复制账户
mysql> create user repluser@'172.16.128.%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication slave on *.* to repluser@'172.16.128.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#备份数据拷贝到slave 节点
[root@master ~]# scp -r /data 172.16.128.129:/
root@172.16.128.129's password:
all.sql 100% 1208KB 41.8MB/s 00:00
从节点配置:
#修改配置文件,重新启动服务
[root@slave ~]# echo -e "server-id=129 \nlog-bin \nread-only" >> /etc/my.cnf.d/mysql-server.cnf
[root@slave ~]# systemctl restart mysqld.service
#slave 节点修改备份文件,
#增加
22 -- Position to start replication or point-in-time recovery from
23 --
24
25 CHANGE MASTER TO MASTER_HOST='172.16.128.128',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306, MASTER_LOG_FILE='ma ster-bin.000003', MASTER_LOG_POS=156;
26
27 --
28 -- Current Database: `mysql`
#登陆数据临时关闭二进制log,还原数据,再启动二进制log
[root@slave ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26 Source distribution
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>
mysql> set sql_log_bin=0;
mysql> source /data/all.sql;
mysql> set sql_log_bin=1;
#启动复制
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.16.128.128
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 689
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 858
Relay_Master_Log_File: master-bin.000003
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: 689
Relay_Log_Space: 1067
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: 44e3b8c3-9954-11ec-83e8-000c29688702
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
检查主从进程
##主节点:
mysql> show processlist;
+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1519 | Waiting on empty queue | NULL |
| 10 | repluser | 172.16.128.129:50348 | NULL | Binlog Dump | 665 | Source has sent all binlog to replica; waiting for more updates | NULL |
| 11 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
3 rows in set (0.01 sec)
mysql> create database db2;
Query OK, 1 row affected (0.01 sec)
##从节点:
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 2152 | Waiting on empty queue | NULL |
| 13 | root | localhost | NULL | Query | 0 | init | show processlist |
| 14 | system user | connecting host | NULL | Connect | 4 | Waiting for source to send event | NULL |
| 15 | system user | | NULL | Query | 4 | Replica has read all relay log; waiting for more updates | NULL |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db01 |
| db2 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.01 sec)
mysql>
主主复制实现
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
另一个节点使用偶数id
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程
第一个节点
#第一个节点mysqld语句块增加配置
[root@master01 ~]# cat /etc/my.cnf.d/mysql-server.cnf
server-id=128
log-bin
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
#重启服务
[root@master01 ~]# systemctl restart mysqld.service
#查看二进制文件位置
mysql> show master logs;
+---------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------------+-----------+-----------+
| master01-bin.000001 | 179 | No |
| master01-bin.000002 | 156 | No |
+---------------------+-----------+-----------+
2 rows in set (0.00 sec)
#新建复制账户
mysql> create user repluser@'172.16.128.%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication slave on *.* to 'repluser'@'172.16.128.%';
Query OK, 0 rows affected (0.01 sec)
第二个节点
#第二个节点mysqld语句块增加配置
[root@master02 ~]# cat /etc/my.cnf.d/mysql-server.cnf
server-id=128
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
#重启服务
[root@master02 ~]# systemctl restart mysqld.service
#登录系统
mysql> CHANGE MASTER TO MASTER_HOST='172.16.128.128',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='master01-bin.000002',MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.16.128.128
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master01-bin.000002
Read_Master_Log_Pos: 699
Relay_Log_File: master02-relay-bin.000002
Relay_Log_Pos: 870
Relay_Master_Log_File: master01-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: 699
Relay_Log_Space: 1082
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: 951d5824-9963-11ec-9f65-000c29688702
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql>
mysql> show master logs;
+---------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------------+-----------+-----------+
| master02-bin.000001 | 179 | No |
| master02-bin.000002 | 713 | No |
+---------------------+-----------+-----------+
2 rows in set (0.00 sec)
回到第一个节点，启动同步进程
mysql> CHANGE MASTER TO MASTER_HOST='172.16.128.129',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='master02-bin.000002',MASTER_LOG_POS=713;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.16.128.129
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master02-bin.000002
Read_Master_Log_Pos: 1124
Relay_Log_File: master01-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: master02-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: 1124
Relay_Log_Space: 741
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: 4681cd81-9962-11ec-a364-000c29303fa3
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
mysql>
检查同步
#第一个节点新建数据库master01
mysql> create database master01;
Query OK, 1 row affected (0.00 sec)
#第二个节点查询
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master01 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
#第二个节点新建数据库master02
mysql> create database master02;
Query OK, 1 row affected (0.00 sec)
#第一个节点查询
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master01 |
| master02 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
2、xtrabackup实现全量+增量+binlog恢复库
xtrabackup 安装部署官方文档
https://www.percona.com/doc/percona-xtrabackup/8.0/installation/yum_repo.html
两台服务器都进行下载安装xtrabackup,准备备份目录
[root@mysql01 ~]#yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@mysql01 ~]#percona-release enable-only tools
[root@mysql01 ~]#yum install percona-xtrabackup-80
[root@mysql01 ~]# mkdir /backup
全量备份
[root@mysql01 ~]# xtrabackup -uroot --backup --target-dir=/backup/base
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --user=root --backup=1 --target-dir=/backup/base
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
220301 10:34:01 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: NO).
220301 10:34:01 version_check Connected to MySQL server
220301 10:34:01 version_check Executing a version check against the server...
# A software update is available:
220301 10:34:04 version_check Done.
220301 10:34:04 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
Using server version 8.0.26
220301 10:34:04 Executing LOCK INSTANCE FOR BACKUP...
……
MySQL binlog position: filename 'binlog.000002', position '156'
220301 10:34:07 [00] Writing /backup/base/backup-my.cnf
220301 10:34:07 [00] ...done
220301 10:34:07 [00] Writing /backup/base/xtrabackup_info
220301 10:34:07 [00] ...done
xtrabackup: Transaction log of lsn (18273965) to (18273985) was copied.
220301 10:34:08 completed OK!
[root@mysql01 ~]# ls -l /backup/base/
total 70708
-rw-r-----. 1 root root 475 Mar 1 10:34 backup-my.cnf
-rw-r-----. 1 root root 156 Mar 1 10:34 binlog.000002
-rw-r-----. 1 root root 16 Mar 1 10:34 binlog.index
drwxr-x---. 2 root root 132 Mar 1 10:34 hellodb
-rw-r-----. 1 root root 5996 Mar 1 10:34 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 1 10:34 ibdata1
drwxr-x---. 2 root root 143 Mar 1 10:34 mysql
-rw-r-----. 1 root root 26214400 Mar 1 10:34 mysql.ibd
drwxr-x---. 2 root root 8192 Mar 1 10:34 performance_schema
drwxr-x---. 2 root root 28 Mar 1 10:34 sys
-rw-r-----. 1 root root 16777216 Mar 1 10:34 undo_001
-rw-r-----. 1 root root 16777216 Mar 1 10:34 undo_002
-rw-r-----. 1 root root 18 Mar 1 10:34 xtrabackup_binlog_info
-rw-r-----. 1 root root 102 Mar 1 10:34 xtrabackup_checkpoints
-rw-r-----. 1 root root 458 Mar 1 10:34 xtrabackup_info
-rw-r-----. 1 root root 2560 Mar 1 10:34 xtrabackup_logfile
-rw-r-----. 1 root root 39 Mar 1 10:34 xtrabackup_tablespaces
第一次增量备份
## 修改数据
[root@mysql01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 8.0.26 Source distribution
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> create database db01 ;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db01 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql>
#第一次增量备份
[root@mysql01 ~]# xtrabackup -uroot --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --user=root --backup=1 --target-dir=/backup/inc1 --incremental-basedir=/backup/base
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
…
xtrabackup: Transaction log of lsn (18274537) to (18274547) was copied.
220301 11:05:59 completed OK!
[root@mysql01 ~]# ls -l /backup/inc1/
total 232
-rw-r-----. 1 root root 475 Mar 1 11:05 backup-my.cnf
-rw-r-----. 1 root root 156 Mar 1 11:05 binlog.000005
-rw-r-----. 1 root root 16 Mar 1 11:05 binlog.index
drwxr-x---. 2 root root 20 Mar 1 11:05 db01
drwxr-x---. 2 root root 4096 Mar 1 11:05 hellodb
-rw-r-----. 1 root root 5996 Mar 1 11:05 ib_buffer_pool
-rw-r-----. 1 root root 32768 Mar 1 11:05 ibdata1.delta
-rw-r-----. 1 root root 64 Mar 1 11:05 ibdata1.meta
drwxr-x---. 2 root root 143 Mar 1 11:05 mysql
-rw-r-----. 1 root root 81920 Mar 1 11:05 mysql.ibd.delta
-rw-r-----. 1 root root 73 Mar 1 11:05 mysql.ibd.meta
drwxr-x---. 2 root root 8192 Mar 1 11:05 performance_schema
drwxr-x---. 2 root root 61 Mar 1 11:05 sys
-rw-r-----. 1 root root 16384 Mar 1 11:05 undo_001.delta
-rw-r-----. 1 root root 69 Mar 1 11:05 undo_001.meta
-rw-r-----. 1 root root 32768 Mar 1 11:05 undo_002.delta
-rw-r-----. 1 root root 69 Mar 1 11:05 undo_002.meta
-rw-r-----. 1 root root 18 Mar 1 11:05 xtrabackup_binlog_info
-rw-r-----. 1 root root 107 Mar 1 11:05 xtrabackup_checkpoints
-rw-r-----. 1 root root 500 Mar 1 11:05 xtrabackup_info
-rw-r-----. 1 root root 2560 Mar 1 11:05 xtrabackup_logfile
-rw-r-----. 1 root root 39 Mar 1 11:05 xtrabackup_tablespaces
第二次增量备份
#再次修改数据
mysql> create table emp ( id int unsigned auto_increment primary key,name varchar(20) not null,age tinyint unsigned ) engine=innodb default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> desc emp;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> insert emp (name,age) values('user01',18),('user02',19);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from emp;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | user01 | 18 |
| 2 | user02 | 19 |
+----+--------+------+
2 rows in set (0.00 sec)
# 第二次增量备份
[root@mysql01 ~]# xtrabackup -uroot --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --user=root --backup=1 --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
……
220301 15:45:16 [00] ...done
xtrabackup: Transaction log of lsn (18293755) to (18293781) was copied.
220301 15:45:17 completed OK!
[root@mysql01 ~]# ls -l /backup/inc2
total 1064
-rw-r-----. 1 root root 475 Mar 1 15:45 backup-my.cnf
-rw-r-----. 1 root root 156 Mar 1 15:45 binlog.000006
-rw-r-----. 1 root root 16 Mar 1 15:45 binlog.index
drwxr-x---. 2 root root 47 Mar 1 15:45 db01
drwxr-x---. 2 root root 4096 Mar 1 15:45 hellodb
-rw-r-----. 1 root root 5996 Mar 1 15:45 ib_buffer_pool
-rw-r-----. 1 root root 49152 Mar 1 15:45 ibdata1.delta
-rw-r-----. 1 root root 64 Mar 1 15:45 ibdata1.meta
drwxr-x---. 2 root root 143 Mar 1 15:45 mysql
-rw-r-----. 1 root root 655360 Mar 1 15:45 mysql.ibd.delta
-rw-r-----. 1 root root 73 Mar 1 15:45 mysql.ibd.meta
drwxr-x---. 2 root root 8192 Mar 1 15:45 performance_schema
drwxr-x---. 2 root root 61 Mar 1 15:45 sys
-rw-r-----. 1 root root 163840 Mar 1 15:45 undo_001.delta
-rw-r-----. 1 root root 69 Mar 1 15:45 undo_001.meta
-rw-r-----. 1 root root 147456 Mar 1 15:45 undo_002.delta
-rw-r-----. 1 root root 69 Mar 1 15:45 undo_002.meta
-rw-r-----. 1 root root 18 Mar 1 15:45 xtrabackup_binlog_info
-rw-r-----. 1 root root 107 Mar 1 15:45 xtrabackup_checkpoints
-rw-r-----. 1 root root 500 Mar 1 15:45 xtrabackup_info
-rw-r-----. 1 root root 3072 Mar 1 15:45 xtrabackup_logfile
-rw-r-----. 1 root root 39 Mar 1 15:45 xtrabackup_tablespaces
把备份数据拷贝到目标还原主机
[root@mysql01 ~]# scp -r /backup 172.16.128.129:目标主机数据还原
目标主机安装mysql,版本相同或者比源主机版本高,同时安装xtrabackup软件
#预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@mysql02 backup]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/backup/base
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
xtrabackup: cd to /backup/base/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(18274005)
……
Log background threads are being closed...
Shutdown completed; log sequence number 18274015
Number of pools: 1
220301 18:27:53 completed OK!
#合并第1次增量备份到完全备份
[root@mysql02 backup]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/backup/base --incremental-dir=/backup/inc1
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
incremental backup from 18274005 is enabled.
xtrabackup: cd to /backup/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(18274537)
…..
220301 18:28:03 [00] Copying /backup/inc1//xtrabackup_tablespaces to ./xtrabackup_tablespaces
220301 18:28:03 [00] ...done
220301 18:28:03 [00] Copying /backup/inc1/binlog.000005 to ./binlog.000005
220301 18:28:03 [00] ...done
220301 18:28:03 [00] Copying /backup/inc1/binlog.index to ./binlog.index
220301 18:28:03 [00] ...done
220301 18:28:03 completed OK!
#合并第2次增量备份到完全备份,最后一次还原不需要加选项--apply-log-only
[root@mysql02 backup]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/backup/base --incremental-dir=/backup/inc2
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
incremental backup from 18274537 is enabled.
xtrabackup: cd to /backup/base/
…..
FTS optimize thread exiting.
Trying to access missing tablespace 4294967294
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 18294294
220301 18:28:22 completed OK!
#复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@mysql02 backup]# xtrabackup --copy-back --target-dir=/backup/base
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/backup/base
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
220301 18:35:02 [01] Copying undo_001 to /var/lib/mysql/undo_001
220301 18:35:02 [01] ...done
220301 18:35:02 [01] Copying undo_002 to /var/lib/mysql/undo_002
220301 18:35:02 [01] ...done
220301 18:35:02 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
220301 18:35:03 [01] ...done
220301 18:35:03 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
…..
220301 18:35:03 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
220301 18:35:03 [01] ...done
220301 18:35:03 [01] Creating directory ./#innodb_temp
220301 18:35:03 [01] ...done.
220301 18:35:03 completed OK!
#还原目录属性
[root@mysql02 backup]# chown -R mysql:mysql /var/lib/mysql
#启动服务,验证数据是否正常还原
[root@mysql02 backup]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
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 |
+--------------------+
| db01 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> select * from db01.emp;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | user01 | 18 |
| 2 | user02 | 19 |
+----+--------+------+
2 rows in set (0.03 sec)
mysql>
3、MyCAT实现MySQL读写分离
安装配置主从数据库
# master 修改配置文件
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
log-bin
server-id=128
# 创建同步账户
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'172.16.128.%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
# 查看二进制位置
[root@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 |
+--------------------+-----------+
| mariadb-bin.000001 | 28247 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.001 sec)
# 创建mycat账户
MariaDB [(none)]> grant all on *.* to 'mycat'@'172.16.128.%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
# slave修改配置文件
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
log-bin
server-id=130
read-only=1
# 启动数据库,配置复制账户连接主服务器,并启动复制进程
MariaDB [(none)]> change master to master_host='172.16.128.128',master_user='repluser',master_password='123456',master_log_file='mariadb-bin.000002',master_log_pos=344;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.128.128
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 976
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 1189
Relay_Master_Log_File: mariadb-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: 976
Relay_Log_Space: 1500
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_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: 4
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]>
# master 主库新增数据测试主库是否正常同步
MariaDB [(none)]> create database master01;
Query OK, 1 row affected (0.003 sec)
# 从库查询
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master01 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
安装配置mycat
# 安装java运行环境
[root@mycat ~]# yum install java
[root@mycat ~]# java -version
openjdk version "1.8.0_322"
OpenJDK Runtime Environment (build 1.8.0_322-b06)
OpenJDK 64-Bit Server VM (build 25.322-b06, mixed mode)
[root@mycat ~]#
## 配置环境变量
[root@mycat bin]# echo 'PATH=/usr/local/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat bin]#
[root@mycat bin]# source /etc/profile.d/mycat.sh
# 修改配置文件
vim conf/server.xml
…..
<!-- 修改登录mycat端口号，默认8066，修改mysql默认端口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>
…..
<!-- 修改登录mycat的账户和口令 根据需要进行修改 -->
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
vim conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 修改dataNode 默认randomDataNode 注意大小写 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!-- auto sharding by id (long) -->
<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
<!--<table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true">
<childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable>
</table> -->
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<!--注释不用的dataNode -->
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<!-- <dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" /> -->
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 增加后台真正数据库的地址-->
<!-- can have multi write hosts -->
<writeHost host="host1" url="jdbc:mysql://172.16.128.128:3306" user="mycat"
password="123456">
<readHost host="host2" url="jdbc:mysql://172.16.128.130:3306" user="mycat"
password="123456"/>
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<!--
<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
<heartbeat> </heartbeat>
<writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost>
</dataHost>
# 启动mycat
[root@mycat logs]# mycat start
Starting Mycat-server...
[root@mycat logs]#
[root@mycat logs]#
[root@mycat logs]# cat wrapper.log
STATUS | wrapper | 2022/03/05 01:41:06 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/03/05 01:41:06 | Launching a JVM...
INFO | jvm 1 | 2022/03/05 01:41:08 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/03/05 01:41:08 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/03/05 01:41:08 |
INFO | jvm 1 | 2022/03/05 01:41:09 | MyCAT Server startup successfully. see logs in logs/mycat.log
登录mycat进行查询和更新操作验证是否读写分离
MariaDB [hellodb]> show variables like 'general_log%';
+------------------+--------------+
| Variable_name | Value |
+------------------+--------------+
| general_log | OFF |
| general_log_file | centos84.log |
+------------------+--------------+
2 rows in set (0.001 sec)
MariaDB [hellodb]> set global general_log=1;
Query OK, 0 rows affected (0.002 sec)
#登录mycat进行查询和更新操作
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 |
| 5 | mage | 30 | M |
+------+---------------+------+--------+
5 rows in set (0.01 sec)
MySQL [TESTDB]> update teachers set name='mage' where tid=5;
Query OK, 1 row affected (0.01 sec)
OK!
# 主库log
220304 18:11:50 160 Query SELECT @@session.tx_isolation
160 Query USE `hellodb`
160 Query SET autocommit=1
160 Query update teachers set name='mage' where tid=5
220304 18:11:59 169 Connect mycat@172.16.128.132 as anonymous on
169 Query /* mysql-connector-java-5.1.35 ( Revision: 5fb9c5849535c13917c2cf9baaece6ef9693ef27 ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
169 Query /* mysql-connector-java-5.1.35 ( Revision: 5fb9c5849535c13917c2cf9baaece6ef9693ef27 ) */SELECT @@session.auto_increment_increment
# 从库log
220304 18:11:47 52 Query SELECT @@session.tx_isolation
52 Query SET autocommit=1
52 Query select * from teachers
220304 18:11:49 92 Connect mycat@172.16.128.132 as anonymous on
92 Query /* mysql-connector-java-5.1.35 ( Revision: 5fb9c5849535c13917c2cf9baaece6ef9693ef27 ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
92 Query /* mysql-connector-java-5.1.35 ( Revision: 5fb9c5849535c13917c2cf9baaece6ef9693ef27 ) */SELECT @@session.auto_increment_increment
92 Query SET NAMES latin1
92 Query SET character_set_results = NULL
92 Query SET autocommit=1
92 Query select user()
92 Quit
220304 18:11:50 16 Query BEGIN
ansible常用模块介绍
Command 模块:
功能：在远程主机执行命令，此为默认模块，可忽略 -m 选项
注意：此命令不支持 $VARNAME < > | ; & 等，可能用shell模块实现,此模块不具有幂等性
[root@CentOS84 ~]# ansible test01 -a 'ls -l /root'
10.10.10.10 | CHANGED | rc=0 >>
total 1076
-rw-------. 1 root root 1328 Mar 6 05:34 anaconda-ks.cfg
drwxr-xr-x. 2 root root 4096 Mar 5 21:58 backup
-rw-r--r--. 1 root root 24488 Mar 5 21:44 epel-release-8-11.el8.noarch.rpm
-rw-r--r--. 1 root root 1062124 Nov 16 22:51 nginx-1.20.2.tar.gz
-rw-r--r--. 1 root root 25 Mar 7 15:52 test.sh
10.10.10.11 | CHANGED | rc=0 >>
total 32
-rw-------. 1 root root 1328 Mar 6 05:34 anaconda-ks.cfg
drwxr-xr-x. 2 root root 4096 Mar 5 21:58 backup
-rw-r--r--. 1 root root 24488 Mar 5 21:44 epel-release-8-11.el8.noarch.rpm
lrwxrwxrwx. 1 root root 33 Mar 7 15:45 Shanghai -> /usr/share/zoneinfo/Asia/Shanghai
Shell 模块:
功能：和command相似，用shell执行命令,支持各种符号,比如:*,$, >
注意：此模块不具有幂等性
[root@CentOS84 ~]# ansible test01 -m shell -a 'echo 'hello' > hello.txt'
10.10.10.10 | CHANGED | rc=0 >>
10.10.10.11 | CHANGED | rc=0 >>
[root@CentOS84 ~]# ansible test01 -m shell -a 'cat hello.txt'
10.10.10.10 | CHANGED | rc=0 >>
hello
10.10.10.11 | CHANGED | rc=0 >>
hello
Script 模块:
功能：在远程主机上运行ansible服务器上的脚本(无需执行权限)
注意：此模块不具有幂等性
[root@CentOS84 ~]# ansible test01 -m script -a '/root/hostname.sh'
10.10.10.10 | CHANGED => {
"changed": true,
"rc": 0,
"stderr": "",
"stderr_lines": [],
"stdout": "CentOS84\n",
"stdout_lines": [
"CentOS84"
]
}
10.10.10.11 | CHANGED => {
"changed": true,
"rc": 0,
"stderr": "Shared connection to 10.10.10.11 closed.\r\n",
"stderr_lines": [
"Shared connection to 10.10.10.11 closed."
],
"stdout": "CentOS8411\r\n",
"stdout_lines": [
"CentOS8411"
]
}
User 模块:
功能：管理用户
[root@CentOS84 ~]# ansible test01 -m user -a 'user=mysql comment="mysql user" uid=3306 shell=/sbin/nologin system=yes'
10.10.10.10 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": true,
"comment": "mysql user",
"create_home": true,
"group": 991,
"home": "/home/mysql",
"name": "mysql",
"shell": "/sbin/nologin",
"state": "present",
"system": true,
"uid": 3306
}
10.10.10.11 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": true,
"comment": "mysql user",
"create_home": true,
"group": 991,
"home": "/home/mysql",
"name": "mysql",
"shell": "/sbin/nologin",
"state": "present",
"system": true,
"uid": 3306
}
Copy 模块:
功能：从ansible服务器主控端复制文件到远程主机
注意: src=file 如果是没指明路径,则为当前目录或当前目录下的files目录下的file文件
[root@CentOS84 ~]# ansible test01 -m copy -a 'src=/root/hostname.sh dest=/root/test.sh owner=mysql mode=600'
10.10.10.10 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": true,
"checksum": "4080c03edf24ad1ca3a9e83c54a363236df4432b",
"dest": "/root/test.sh",
"gid": 0,
"group": "root",
"md5sum": "6c65510d520b68b9ef4d5f76552e8a98",
"mode": "0600",
"owner": "mysql",
"secontext": "unconfined_u:object_r:admin_home_t:s0",
"size": 21,
"src": "/root/.ansible/tmp/ansible-tmp-1646651347.1888273-4352-253518339844149/source",
"state": "file",
"uid": 3306
}
10.10.10.11 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": true,
"checksum": "4080c03edf24ad1ca3a9e83c54a363236df4432b",
"dest": "/root/test.sh",
"gid": 0,
"group": "root",
"md5sum": "6c65510d520b68b9ef4d5f76552e8a98",
"mode": "0600",
"owner": "mysql",
"secontext": "system_u:object_r:admin_home_t:s0",
"size": 21,
"src": "/root/.ansible/tmp/ansible-tmp-1646651347.6414096-4353-141375303849608/source",
"state": "file",
"uid": 3306
}
##copy整个目录
[root@CentOS84 ~]# ansible test01 -m copy -a 'src=/data dest=/tmp'
^C [ERROR]: User interrupted execution
[root@CentOS84 ~]# ls -l /tmp/
total 12
drwxr-xr-x. 4 root root 103 Mar 7 19:12 data
-rwx------. 1 root root 701 Mar 6 05:34 ks-script-jswl11w0
-rwx------. 1 root root 291 Mar 6 05:34 ks-script-ne2sf35v
drwxr-xr-x. 6 root root 4096 Mar 7 19:11 root
drwx------. 3 root root 17 Mar 7 15:36 systemd-private-15c79bda9a4c46e487b93580ce73ac50-chronyd.service-3kP7li
drwx------. 2 root root 6 Mar 7 15:36 vmware-root_858-2722632195
drwx------. 2 root root 6 Mar 5 22:14 vmware-root_866-2722763301
drwx------. 2 root root 6 Mar 5 22:10 vmware-root_869-3988752892
drwx------. 2 root root 6 Mar 6 05:36 vmware-root_895-3979642976
##copy目录下的文件
[root@CentOS84 ~]# ansible test01 -m copy -a 'src=/data/ dest=/tmp'
^C [ERROR]: User interrupted execution
[root@CentOS84 ~]# ls -l /tmp/
total 1036
-rw-r--r--. 1 root root 0 Mar 7 19:13 a.txt
-rw-r--r--. 1 root root 25 Mar 7 19:13 copy.txt
drwxr-xr-x. 4 root root 103 Mar 7 19:12 data
-rw-r--r--. 1 root root 6 Mar 7 19:13 hello.txt
-rwx------. 1 root root 701 Mar 6 05:34 ks-script-jswl11w0
-rwx------. 1 root root 291 Mar 6 05:34 ks-script-ne2sf35v
drwxr-xr-x. 4 root root 44 Mar 7 19:13 log
drwxr-xr-x. 3 root root 97 Mar 7 19:13 nginx-1.20.2
-rw-r--r--. 1 root root 1038145 Mar 7 19:13 nginx.tar.gz
drwxr-xr-x. 6 root root 4096 Mar 7 19:11 root
drwx------. 3 root root 17 Mar 7 15:36 systemd-private-15c79bda9a4c46e487b93580ce73ac50-chronyd.service-3kP7li
drwx------. 2 root root 6 Mar 7 15:36 vmware-root_858-2722632195
drwx------. 2 root root 6 Mar 5 22:14 vmware-root_866-2722763301
drwx------. 2 root root 6 Mar 5 22:10 vmware-root_869-3988752892
drwx------. 2 root root 6 Mar 6 05:36 vmware-root_895-3979642976
Get_url 模块
功能: 用于将文件从http、https或ftp下载到被管理机节点上
常用参数如下:
url: 下载文件的URL,支持HTTP，HTTPS或FTP协议
dest: 下载到目标路径（绝对路径），如果目标是一个目录，就用服务器上面文件的名称，如果目标设置了名 称就用目标设置的名称
owner：指定属主
group：指定属组
mode：指定权限
force: 如果yes，dest不是目录，将每次下载文件，如果内容改变，替换文件。如果否，则只有在目标不存 在时才会下载该文件
checksum: 对目标文件在下载后计算摘要，以确保其完整性
示例: checksum="sha256:D98291AC[...]B6DC7B97", checksum="sha256:http://example.com/path/sha256sum.txt"
url_username: 用于HTTP基本认证的用户名。 对于允许空口令的站点，此参数可以不使用 `url_password'
url_password: 用于HTTP基本认证的口令。 如果未指定`url_username'参数，则不会使用 `url_password'参数
validate_certs：如果“no”，SSL证书将不会被验证。 适用于自签名证书在私有网站上使用
timeout: URL请求的超时时间,秒为单位
[root@CentOS84 ~]# ansible test01 -m get_url -a 'url=http://nginx.org/download/nginx-1.20.2.tar.gz dest=/data/ checksum="md5:3bcc5ccdc052c35d0d3c5557cf56c7d2"'
File 模块:
功能：设置文件属性,创建软链接等
[root@CentOS84 ~]# ansible test01 -m file -a 'path=/data/test.txt state=touch'
[root@CentOS84 ~]# ansible test01 -m file -a "path=/data state=directory"
[root@CentOS84 ~]# ansible test01 -m file -a "path=/data state=absent"
[root@CentOS84 ~]# ansible test01 -m file -a 'src=/data/nginx-1.20.2 dest=/data/nginx state=link'
[root@CentOS84 ~]# ansible test01 -m file -a 'path=/data/mysql state=directory owner=mysql group=mysql recurse=yes'
stat 模块:
功能：检查文件或文件系统的状态
注意：对于Windows目标，请改用win_stat模块
选项：path：文件/对象的完整路径（必须）
常用的返回值判断：
exists： 判断是否存在
isuid： 调用用户的ID与所有者ID是否匹配
[root@CentOS84 ~]# ansible 127.0.0.1 -m stat -a 'path=/etc/passwd'unarchive 模块 :
功能：解包解压缩
实现有两种用法：
1、将ansible主机上的压缩包传到远程主机后解压缩至特定目录，设置copy=yes,此为默认值,可省略
2、将远程主机上的某个压缩包解压缩到指定路径下，设置copy=no
常见参数：
copy：默认为yes，当copy=yes，拷贝的文件是从ansible主机复制到远程主机上，如果设置为copy=no，
会在远程主机上寻找src源文件
remote_src：和copy功能一样且互斥，yes表示在远程主机，不在ansible主机，no表示文件在ansible 主机上
src：源路径，可以是ansible主机上的路径，也可以是远程主机(被管理端或者第三方主机)上的路径，如果
是远程主机上的路径，则需要设置copy=no
dest：远程主机上的目标路径
mode：设置解压缩后的文件权限
[root@CentOS84 ~]# ansible test01 -m unarchive -a 'src=/data/nginx-1.20.2.tar.gz dest=/data'Archive 模块
功能：打包压缩保存在被管理节点
[root@CentOS84 ~]# ansible test01 -m archive -a 'path=/data/mysql/ dest=/data/lmysql.tar.gz format=gz 'Cron 模块
功能：计划任务
支持时间：minute，hour，day，month，weekday
[root@CentOS84 ~]# ansible test01 -m cron -a 'hour=2 minute=30 weekday=1-5 name="backup mysql" job=/root/mysql_backup.sh'
[root@CentOS84 ~]# ansible test01 -m cron -a 'name="backup mysql" state=absent'
Lineinfile 模块
ansible在使用sed进行替换时，经常会遇到需要转义的问题，而且ansible在遇到特殊符号进行替换时，
存在问题，无法正常进行替换 。其实在ansible自身提供了两个模块：lineinfile模块和replace模块，可
以方便的进行替换 一般在ansible当中去修改某个文件的单行进行替换的时候需要使用lineinfile模块 regexp参数
：使用正则表达式匹配对应的行，当替换文本时，如果有多行文本都能被匹配，则只有最
后面被匹配到的那行文本才会被替换，当删除文本时，如果有多行文本都能被匹配，这么这些行都会被 删除。 如果想进行多行匹配进行替换需要使用replace模块 功能：相当于sed，可以修改文件内容
[root@CentOS84 ~]# ansible test01 -a 'echo -e "123 \n123 \nabc" > /data/test.txt'
10.10.10.10 | CHANGED | rc=0 >>
10.10.10.11 | CHANGED | rc=0 >>
[root@CentOS84 ~]# ansible test01 -m lineinfile -a "path=/data/test.txt regexp='^123' line='abc'"
[root@CentOS84 ~]# cat /data/test.txt
123
abc
abc
Replace 模块
该模块有点类似于sed命令，主要也是基于正则进行匹配和替换，建议使用
[root@CentOS84 ~]# ansible test01 -m replace -a "path=/data/test.txt regexp='^abc' replace='123'"
10.10.10.10 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": true,
"msg": "2 replacements made"
}
10.10.10.11 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": true,
"msg": "2 replacements made"
}
[root@CentOS84 ~]# cat /data/test.txt
123
123
123
Yum 和 Apt 模块
功能：
yum 管理软件包，只支持RHEL，CentOS，fedora，不支持Ubuntu其它版本
apt 模块管理 Debian 相关版本的软件包
[root@CentOS84 ~]# ansible test01 -m yum -a 'name=sl'
[root@CentOS84 ~]# ansible test01 -m yum -a 'name=sl state=absent'
Setup 模块
功能：
setup 模块来收集主机的系统信息，这些 facts 信息可以直接以变量的形式使用，但是如果主机
较多，会影响执行速度
[root@CentOS84 ~]# ansible test01 -m setup -a "filter=ansible_hostname"
