1、主从复制及主主复制的实现
主从复制

# 主节点

[root@master ~]# yum -y install mysql-server
[root@master ~]# vim /etc/my.cnf.d/mysql-server.cnf
server-id=27
log-bin=/data/sql_logs/mysql-bin

# 创建文件夹并授权
[root@master ~]# mkdir -p /data/sql_logs/
[root@master ~]# chown -R mysql.mysql /data/sql_logs/
[root@master ~]# systemctl enable --now mysqld.service

[root@master ~]# mysql
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> create user test@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to test@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

# 从节点

[root@slave ~]# yum -y install mysql-server
[root@slave ~]# vim /etc/my.cnf.d/mysql-server.cnf
server-id=87
read_only=ON

[root@slave ~]# systemctl enable --now mysqld.service

[root@slave ~]# mysql
mysql> change master to master_host='10.0.0.27',
    -> master_user='test',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=156;
Query OK, 0 rows affected, 8 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.27
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 673
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 841
        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: 673
              Relay_Log_Space: 1050
              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: 27
                  Master_UUID: 71b7cc8c-bfb3-11ec-94c4-000c29e1f358
             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> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

# 从节点
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

Mysql主主复制

# 主节点

[root@master ~]# yum -y install mysql-server
[root@master ~]# vim /etc/my.cnf.d/mysql-server.cnf
server-id=27
auto_increment_offset=1
auto_increment_increment=2

[root@master ~]# systemctl enable --now mysqld.service

[root@master ~]# mysql
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       179 | No        |
| binlog.000002 |       156 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

mysql> create user test@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to test@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

# 先去从节点开启同步，再回主节点开启同步
mysql> change master to master_host='10.0.0.87',
    -> master_user='test',
    -> master_password='123456',
    -> master_log_file='binlog.000001',
    -> master_log_pos=683;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.87
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 683
               Relay_Log_File: master-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.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: 683
              Relay_Log_Space: 531
              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: 87
                  Master_UUID: af916a32-bfbb-11ec-99f2-000c29208f49
             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)

# 从节点

[root@slave ~]# yum -y install mysql-server
[root@slave ~]# vim /etc/my.cnf.d/mysql-server.cnf
server-id=87
auto_increment_offset=2
auto_increment_increment=2

[root@slave ~]# systemctl enable --now mysqld.service

[root@slave ~]# mysql
mysql> create user test@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to test@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='10.0.0.27',
    -> master_user='test',
    -> master_password='123456',
    -> master_log_file='binlog.000002',
    -> master_log_pos=156;
    
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: 10.0.0.27
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 156
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.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: 156
              Relay_Log_Space: 530
              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: 27
                  Master_UUID: 71b7cc8c-bfb3-11ec-94c4-000c29e1f358
             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> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

# 测试是否同步
# 主节点
mysql> create database test_master;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_master        |
| test_slave         |
+--------------------+
7 rows in set (0.01 sec)

# 从节点
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_master        |
+--------------------+
5 rows in set (0.00 sec)

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

2、xtrabackup实现全量+增量+binlog恢复库
安装xtrabackup

[root@master ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.27-19/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm
[root@master ~]# yum -y install percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm

全量备份

# 有密码需加-pxxxxxx
[root@master ~]# xtrabackup -uroot --backup --target-dir=/data/backup/
[root@master ~]# ll /data/backup/
total 72752
-rw-r----- 1 root root      475 Apr 19 19:32 backup-my.cnf
-rw-r----- 1 root root      156 Apr 19 19:32 binlog.000004
-rw-r----- 1 root root       16 Apr 19 19:32 binlog.index
-rw-r----- 1 root root     3475 Apr 19 19:32 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 19 19:32 ibdata1
drwxr-x--- 2 root root      143 Apr 19 19:32 mysql
-rw-r----- 1 root root 28311552 Apr 19 19:32 mysql.ibd
drwxr-x--- 2 root root     8192 Apr 19 19:32 performance_schema
drwxr-x--- 2 root root       28 Apr 19 19:32 sys
drwxr-x--- 2 root root       20 Apr 19 19:32 test
drwxr-x--- 2 root root       20 Apr 19 19:32 test_master
drwxr-x--- 2 root root       20 Apr 19 19:32 test_slave
-rw-r----- 1 root root 16777216 Apr 19 19:32 undo_001
-rw-r----- 1 root root 16777216 Apr 19 19:32 undo_002
-rw-r----- 1 root root       18 Apr 19 19:32 xtrabackup_binlog_info
-rw-r----- 1 root root      102 Apr 19 19:32 xtrabackup_checkpoints
-rw-r----- 1 root root      459 Apr 19 19:32 xtrabackup_info
-rw-r----- 1 root root     2560 Apr 19 19:32 xtrabackup_logfile
-rw-r----- 1 root root       39 Apr 19 19:32 xtrabackup_tablespaces

增量备份

# 增加数据
[root@master ~]# mysql
mysql> create database testdb1;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
+--------------------+
5 rows in set (0.00 sec)

# 第一次增量备份
[root@master ~]# xtrabackup -uroot --backup --target-dir=/data/in1 --incremental-basedir=/data/backup/

[root@master ~]# ll /data/
total 8
drwxr-xr-x 2 root  root     6 Apr 19 19:32 ackup
drwxr-x--- 8 root  root  4096 Apr 19 19:32 backup
drwxr-x--- 6 root  root  4096 Apr 19 19:55 in1
drwxr-xr-x 2 mysql mysql   53 Apr 19 15:46 sql_logs

# 增加数据
mysql> create database testdb2;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
+--------------------+
6 rows in set (0.00 sec)

# 第二次增量备份
[root@master ~]# xtrabackup -uroot --backup --target-dir=/data/in2 --incremental-basedir=/data/in1

[root@master ~]# ll /data/
total 12
drwxr-xr-x 2 root  root     6 Apr 19 19:32 ackup
drwxr-x--- 8 root  root  4096 Apr 19 19:32 backup
drwxr-x--- 6 root  root  4096 Apr 19 19:55 in1
drwxr-x--- 7 root  root  4096 Apr 19 20:04 in2
drwxr-xr-x 2 mysql mysql   53 Apr 19 15:46 sql_logs

binlog恢复数据库

# 拷贝到还原主机，还原主机不要有数据，还原主机需要安装xtrabackup
[root@master ~]# scp -r /data/* 10.0.0.87:/data/

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

# 合并第1次增量备份到完全备份
[root@slave ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup --incremental-dir=/data/in1

# 合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@slave ~]# xtrabackup --prepare --target-dir=/data/backup --incremental-dir=/data/in2

# 复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@slave ~]# mv /var/lib/mysql /var/lib/mysql.bak
[root@slave ~]# mkdir /var/lib/mysql
[root@slave ~]# xtrabackup --copy-back --target-dir=/data/backup
[root@slave ~]# chown -R mysql.mysql /var/lib/mysql
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
+--------------------+
6 rows in set (0.01 sec)

3、MyCAT实现MySQL读写分离

mysql-master: master:10.0.0.37 MySQL 8.0
mysql-slave:  slave:10.0.0.47 MySQL 8.0
mycat-server: mycat:10.0.0.57 

# master

[root@master ~]# yum -y install mysql-server
[root@master ~]# vim /etc/my.cnf.d/mysql-server.cnf 
server-id=37
log-bin=/data/sql_logs/mysql-bin
[root@master ~]# mkdir -p /data/sql_logs/
[root@master ~]# chown -R mysql.mysql /data/sql_logs/
[root@master ~]# systemctl enable --now mysqld.service
[root@master ~]# mysql

mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       388 | No        |
+------------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> create user test@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to test@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

# slave

[root@slave ~]# yum -y install mysql-server
[root@slave ~]# vim /etc/my.cnf.d/mysql-server.cnf 
server-id=47
read-only=1

[root@slave ~]# mysql
mysql> change master to master_host='10.0.0.37',
    -> master_user='test',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=388;
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.37
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 388
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 324
        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: 388
              Relay_Log_Space: 533
              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: 37
                  Master_UUID: 0d834dc9-bfde-11ec-a100-000c296e9ed0
             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> create database testdb;
Query OK, 1 row affected (0.00 sec)

# 从库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

# mycat

[root@mycat ~]# yum -y install jave
[root@mycat ~]# java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)
[root@mycat ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]# mkdir /apps/
[root@mycat ~]# tar xf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

# 配置环境变量
[root@mycat ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]# source /etc/profile.d/mycat.sh
# 可以在主从库测试连接
[root@mycat ~]# mysql -uroot -p123456 -h 10.0.0.57 -P8066

# 在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@mycat ~]# vim /apps/mycat/conf/server.xml
                        <property name="serverPort">3306</property>
                        <property name="managerPort">9066</property>
                        <property name="idleTimeout">300000</property>
                        <property name="authTimeout">15000</property>
                        <property name="bindIp">0.0.0.0</property>
                        <property name="dataNodeIdleCheckPeriod">300000</property>
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
                        
                <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
                <property name="defaultSchema">TESTDB</property>
        </user>

                        
[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.37:3306" user="root"
                   password="123456">
         <readHost host="host2" url="10.0.0.47:3306" user="root" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>
                
# 启动mycat
[root@mycat ~]# file /apps/mycat/bin/mycat 
/apps/mycat/bin/mycat: a /usr/bin/env sh script, ASCII text executable
# 内存不够可能起不来服务
[root@mycat ~]# mycat start
Starting Mycat-server...


# 在主服务器配置账户
[root@master ~]# mysql
mysql> create database hellodb;
Query OK, 1 row affected (0.01 sec)

mysql> create user 'root'@'10.0.0.%' IDENTIFIED BY '123456' ;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON *.* TO 'root'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

# 在Mycat服务器上连接并测试
[root@centos8 ~]# mysql -uroot -p123456 -h127.0.0.1 TESTDB
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          47 |
+-------------+
1 row in set (0.00 sec)

# 停止从节点
mysql> select @@hostname;
+-------------------+
| @@hostname        |
+-------------------+
| master.sakura.com |
+-------------------+
1 row in set (0.00 sec)

4、ansible常用模块介绍

Command 模块
功能：在远程主机执行命令，此为默认模块，可忽略 -m 选项
注意：此命令不支持 $VARNAME < > | ; & 等，可能用shell模块实现
注意：此模块不具有幂等性

Shell 模块
功能：和command相似，用shell执行命令,支持各种符号,比如:*,$, > 
注意：此模块不具有幂等性
注意：调用bash执行命令 类似 cat /tmp/test.md | awk -F'|' '{print $1,$2}' &> /tmp/example.txt 这些
复杂命令，即使使用shell也可能会失败，解决办法：写到脚本时，copy到远程，执行，再把需要的结果
拉回执行命令的机器
范例：将shell模块代替command，设为模块
[root@ansible ~]#vim /etc/ansible/ansible.cfg
#修改下面一行
module_name = shell

Script 模块
功能：在远程主机上运行ansible服务器上的脚本(无需执行权限)
注意：此模块不具有幂等性
范例：
ansible websrvs -m script -a /data/test.sh

Copy 模块
功能：从ansible服务器主控端复制文件到远程主机
注意: src=file 如果是没指明路径,则为当前目录或当前目录下的files目录下的file文件
#如目标存在，默认覆盖，此处指定先备份
ansible websrvs -m copy -a "src=/root/test1.sh dest=/tmp/test2.sh   owner=wang mode=600 backup=yes"
#指定内容，直接生成目标文件    
ansible websrvs -m copy -a "content='test line1\ntest line2\n' dest=/tmp/test.txt"
#复制/etc目录自身,注意/etc/后面没有/
ansible websrvs -m copy -a "src=/etc dest=/backup"
#复制/etc/下的文件，不包括/etc/目录自身,注意/etc/后面有/
ansible websrvs -m copy -a "src=/etc/ dest=/backup"

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@ansible ~]#ansible websrvs -m get_url -a 
'url=http://nginx.org/download/nginx-1.18.0.tar.gz  dest=/usr/local/src/nginx.tar.gz 
 checksum="md5:b2d33d24d89b8b1f87ff5d251aa27eb8"'

Fetch 模块
功能：从远程主机提取文件至ansible的主控端，copy相反，目前不支持目录
范例：
ansible websrvs -m fetch -a 'src=/root/test.sh dest=/data/scripts

File 模块
功能：设置文件属性,创建软链接等
#创建空文件
ansible all -m file  -a 'path=/data/test.txt state=touch'
ansible all -m file  -a 'path=/data/test.txt state=absent'
ansible all -m file -a "path=/root/test.sh owner=wang mode=755"
#创建目录
ansible all -m file -a "path=/data/mysql state=directory owner=mysql 
group=mysql"
#创建软链接
ansible all -m file -a 'src=/data/testfile path|dest|name=/data/testfile-link 
state=link'
#创建目录
ansible all -m file  -a 'path=/data/testdir state=directory'
#递归修改目录属性,但不递归至子目录
ansible all -m file -a "path=/data/mysql state=directory owner=mysql 
group=mysql"
#递归修改目录及子目录的属性
ansible all -m file -a "path=/data/mysql state=directory owner=mysql group=mysql recurse=yes"

stat 模块
功能：检查文件或文件系统的状态
注意：对于Windows目标，请改用win_stat模块
选项：
path：文件/对象的完整路径（必须）
常用的返回值判断：
exists： 判断是否存在
isuid： 调用用户的ID与所有者ID是否匹配

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：设置解压缩后的文件权限

Archive 模块
 功能：打包压缩保存在被管理节点
 范例：
 ansible websrvs -m archive  -a 'path=/var/log/ dest=/data/log.tar.bz2 format=bz2 owner=wang mode=0600'

Hostname 模块
功能：管理主机名
范例：
ansible node1 -m hostname -a "name=websrv"

Cron 模块
功能：计划任务
支持时间：minute，hour，day，month，weekday
#备份数据库脚本
[root@centos8 ~]#cat /root/mysql_backup.sh 
#!/bin/bash
mysqldump -A -F --single-transaction --master-data=2 -q -uroot |gzip > 
/data/mysql_`date +%F_%T`.sql.g
#创建任务
ansible 10.0.0.8 -m cron -a 'hour=2 minute=30 weekday=1-5 name="backup mysql" job=/root/mysql_backup.sh'
ansible websrvs   -m cron -a "minute=*/5 job='/usr/sbin/ntpdate ntp.aliyun.com &>/dev/null' name=Synctime"
#禁用计划任务
ansible websrvs   -m cron -a "minute=*/5 job='/usr/sbin/ntpdate 172.20.0.1 &>/dev/null' name=Synctime disabled=yes"
#启用计划任务
ansible websrvs   -m cron -a "minute=*/5 job='/usr/sbin/ntpdate 172.20.0.1 &>/dev/null' name=Synctime disabled=no"
#删除任务
ansible websrvs -m cron -a "name='backup mysql' state=absent"
ansible websrvs -m cron -a 'state=absent name=Synctime'

Yum 和 Apt 模块
功能：
yum 管理软件包，只支持RHEL，CentOS，fedora，不支持Ubuntu其它版本
apt 模块管理 Debian 相关版本的软件包

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
- name: Add multiple repositories into the same file (2/2)
 yum_repository:
   name: rpmforge
   description: RPMforge YUM repo
   file: external_repos
   baseurl: http://apt.sw.be/redhat/el7/en/$basearch/rpmforge
   mirrorlist: http://mirrorlist.repoforge.org/el7/mirrors-rpmforge
   enabled: no
    
- name: Remove repository from a specific repo file
   yum_repository:
     name: epel
     file: external_repos
     state: absent

Service 模块
功能：管理服务
范例：
ansible all -m service -a 'name=httpd state=started enabled=yes'
ansible all -m service -a 'name=httpd state=stopped'
ansible all -m service -a 'name=httpd state=reloaded'
ansible all -m shell -a "sed -i 's/^Listen 80/Listen 8080/' 
/etc/httpd/conf/httpd.conf"
ansible all -m service -a 'name=httpd state=restarted'

User 模块
功能：管理用户
范例：
#创建用户
ansible all -m user -a 'name=user1 comment="test user" uid=2048 home=/app/user1 group=root'
ansible all -m user -a 'name=nginx comment=nginx uid=88 group=nginx groups="root,daemon" shell=/sbin/nologin system=yes create_home=no home=/data/nginx non_unique=yes'
#remove=yes表示删除用户及家目录等数据,默认remove=no
ansible all -m user -a 'name=nginx state=absent remove=yes'
#生成123456加密的密码
ansible localhost -m debug -a "msg={{ '123456'| password_hash('sha512','salt')}}"
localhost | SUCCESS => { "msg": "$6$salt$MktMKPZJ6t59GfxcJU20DwcwQzfMvOlHFVZiOVD71w."}#用上面创建的密码创建用户
ansible websrvs -m user -a 'name=test password="$6$salt$MktMKPZJ6t59GfxcJU20DwcwQzfMvOlHFVZiOVD71w."'
#创建用户test,并生成4096bit的私钥
ansible websrvs -m user -a 'name=test generate_ssh_key=yes ssh_key_bits=4096 ssh_key_file=.ssh/id_rsa'

Group 模块
功能：管理组
范例：
#创建组
ansible websrvs -m group  -a 'name=nginx gid=88 system=yes'
#删除组
ansible websrvs -m group  -a 'name=nginx state=absent'

Lineinfile 模块
ansible在使用sed进行替换时，经常会遇到需要转义的问题，而且ansible在遇到特殊符号进行替换时，
存在问题，无法正常进行替换 。其实在ansible自身提供了两个模块：lineinfile模块和replace模块，可
以方便的进行替换
一般在ansible当中去修改某个文件的单行进行替换的时候需要使用lineinfile模块
regexp参数 ：使用正则表达式匹配对应的行，当替换文本时，如果有多行文本都能被匹配，则只有最
后面被匹配到的那行文本才会被替换，当删除文本时，如果有多行文本都能被匹配，这么这些行都会被
删除。
如果想进行多行匹配进行替换需要使用replace模块
功能：相当于sed，可以修改文件内容
范例：
ansible websrvs -m lineinfile -a "path=/etc/httpd/conf/httpd.conf  regexp='^Listen' line='Listen 80'"
ansible all -m   lineinfile -a "path=/etc/selinux/config regexp='^SELINUX=' line='SELINUX=disabled'"
ansible all -m lineinfile  -a 'dest=/etc/fstab state=absent regexp="^#"'

Replace 模块
该模块有点类似于sed命令，主要也是基于正则进行匹配和替换，建议使用
范例：
ansible all -m replace -a "path=/etc/fstab regexp='^(UUID.*)' replace='#\1'"  
ansible all -m replace -a "path=/etc/fstab regexp='^#(UUID.*)' replace='\1'"

SELinux 模块
该模块管理 SELInux 策略
范例:
[root@ansible ~]#ansible 10.0.0.8 -m selinux -a 'state=disabled'
[WARNING]: SELinux state temporarily changed from 'enforcing' to 'permissive'. 
State change will take effect next reboot.
10.0.0.8 | CHANGED => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/libexec/platform-python"
   },
    "changed": true,
    "configfile": "/etc/selinux/config",
    "msg": "Config SELinux state changed from 'enforcing' to 'disabled'",
    "policy": "targeted",
    "reboot_required": true,
    "state": "disabled"
}
[root@centos8 ~]#grep -v '#' /etc/selinux/config
SELINUX=disabled
SELINUXTYPE=targeted
[root@centos8 ~]#getenforce 
Permissive

reboot 模块
[root@ansible ~]#ansible websrvs -m reboot

mount 挂载和卸载
功能: 挂载和卸载文件系统
范例:
#临时挂载
mount websrvs -m mount -a 'src="UUID=b3e48f45-f933-4c8e-a700-22a159ec9077" path=/home fstype=xfs opts=noatime state=present'
#临时取消挂载
mount websrvs -m mount -a 'path=/home fstype=xfs opts=noatime state=unmounted'
#永久挂载
ansible websrvs -m mount -a 'src=10.0.0.8:/data/wordpress path=/var/www/html/wp�content/uploads opts="_netdev" state=mounted'
#永久卸载
ansible websrvs -m mount -a 'src=10.0.0.8:/data/wordpress path=/var/www/html/wp�content/uploads state=absent'

Setup 模块
功能： setup 模块来收集主机的系统信息，这些 facts 信息可以直接以变量的形式使用，但是如果主机
较多，会影响执行速度
可以使用 gather_facts: no 来禁止 Ansible 收集 facts 信息

debug 模块
此模块可以用于输出信息,并且通过 msg 定制输出的信息内容
注意: msg后面的变量有时需要加 " " 引起来