mysql&ansible

1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）

#在主服务器完全备份并将备份文件传至slave节点

[root@master ~]# mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql
[root@master ~]# ll /backup/
total 472
-rw-r--r--. 1 root root 479562 Oct 16 20:40 fullbackup_2020-10-16_20:40:16.sql
[root@master ~]# scp /backup/fullbackup_2020-10-16_20\:40\:16.sql 10.0.0.28:/data/
root@10.0.0.28's password: 
fullbackup_2020-10-16_20:4 100%  468KB  19.9MB/s   00:00    

#优化主服务器的性能

MariaDB [(none)]> set global innodb_flush_log_at_trx_commit=2    -> ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> set global sync_binlog=0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.004 sec)

#将完全备份还原到新的slave节点

[root@slave ~]# vim /data/fullbackup_2020-10-16_20\:40\:16.sql 


CHANGE MASTER TO
MASTER_LOG_FILE='mariadb-bin.000008',
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_POS=389;

[root@slave ~]# mysql < /data/fullbackup_2020-10-16_20\:40\:16.sql 



MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: 
                   Master_Host: 10.0.0.18
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000008
           Read_Master_Log_Pos: 389
                Relay_Log_File: mariadb-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.000008
              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: 389
               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: 

MariaDB [(none)]> start slave;

2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）

#找到哪个从节点的数据是最新（从日志可以看出slave1的日志更新），让其成为新的master

[root@slave1 ~]# cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000004
1567
mariadb-bin.000002
1678
0








[root@slave2 ~]# cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000012
809
mariadb-bin.000002
1551
0

#在新master上修改配置文件，关闭read-only，并清除旧的master信息

[root@slave1 ~]# vim /etc/my.cnf.d/mariad-server.cnf
[mysqld]
server-id=18
log-bin
read-only=OFF
[root@slave1 ~]# systemctl restart mariadb
[root@slave1 ~]# mysql
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.001 sec)

#在新master上完全备份并将备份文件传至从节点

[root@slave1 ~]# mysqldump -A --single-transaction --master-data=1 -F >/data/backup.sql 
[root@slave1 ~]# vim /data/backup.sql 
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=375;
[root@slave1 ~]# scp /data/backup.sql 10.0.0.28:/data/
The authenticity of host '10.0.0.28 (10.0.0.28)' can't be established.
ECDSA key fingerprint is SHA256:SvVacGS2sluikgyqq1nkKgFU/D3kKs5KpRpn9eNPqIQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.28' (ECDSA) to the list of known hosts.
root@10.0.0.28's password: 
backup.sql                                                                                         100%  468KB   4.9MB/s   00:00    

#在从节点上重新还原数据库，并指向新的master

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.000 sec)
[root@slave2 ~]# mysql < /data/backup.sql 
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G;
********************