1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）
准备：两台主机，数据库版本一样
- 主服务器10.0.0.181 centos8
- 从服务器10.0.0.182 centos8
主服务器
#备份前设置主服务器数据库server-id和log-bin
[root@centos181 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=181
log-bin
[root@centos181 ~]#systemctl restart mariadb
#在主服务器完全备份数据库
[root@centos181 ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup-`date +%F_%T`.sql
[root@centos181 ~]#ll /backup/
total 484
-rw-r--r-- 1 root root 487683 Oct 16 19:35 fullbackup-2020-10-16_19:35:01.sql
#拷贝数据库备份到从服务器
[root@centos181 ~]#scp /backup/fullbackup-2020-10-16_19\:35\:01.sql 10.0.0.182:/data/
[root@centos182 ~]#ll /data/
total 9396
-rw-r--r-- 1 root root 487683 Oct 16 19:37 fullbackup-2020-10-16_19:35:01.sql
#优化主从节点服务器的性能
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> set global sync_binlog=0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [hellodb]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 379 |
| mariadb-bin.000002 | 375 |
+--------------------+-----------+
2 rows in set (0.000 sec)
#创建复制用户
MariaDB [(none)]> grant replication slave on *.* to xiaobai@'10.0.0.%' identified by 123456
Query OK, 0 rows affected (0.001 sec)
从服务器
#设置从服务器数据库server-id
[root@centos182 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=182
read-only
[root@centos182 ~]#systemctl restart mariadb
#配置从节点，从完全备份的位置之后开始复制
[root@centos182 ~]#grep '^CHANGE MASTER' /data/fullbackup-2020-10-16_19\:35\:01.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375;
[root@centos182 ~]#vim /data/fullbackup-2020-10-16_19\:35\:01.sql
MASTER_USER='xiaobai',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=375;
[root@centos182 ~]#mysql < /data/fullbackup-2020-10-16_19\:35\:01.sql
[root@centos182 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.181
Master_User: xiaobai
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 375
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-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: 375
Relay_Log_Space: 256
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.181
Master_User: xiaobai
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 574
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 756
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: 574
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: 181
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: 1
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）
准备：三台主机，数据库版本一样
- 主服务器10.0.0.181 centos8
- 从服务器10.0.0.182 centos8，10.0.0.183 centos8
#找到从节服务器的数据库是最新的，让它成为新的master
[root@centos182 ~]#cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000004
690
mariadb-bin.000003
389
0
[root@centos182 ~]#cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000002
557
mariadb-bin.000003
389
0
#新master配置文件修改，关闭read-only，开启二进制日志
[root@centos182 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=182
read-only=off
log-bin=/data/mysql/logbin/mysql-bin
#清除旧的master复制信息
MariaDB [hellodb]> set global read_only=off;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> stop slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [hellodb]> reset slave all;
Query OK, 0 rows affected (0.000 sec)
#创建复制用户
MariaDB [hellodb]> grant replication slave on *.* to xiaohong@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)
#在新的master上完全备份
[root@centos182 ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/backup.sql
#复制SQL语句到从节点
[root@centos182 ~]#scp /backup/backup.sql 10.0.0.183:
#分析旧的master的二进制日志，将未同步到新master的二进制日志导出来，恢复到新master，尽可能恢复数据
#其它从节点重新还原数据库，指向新的master
[root@centos183 ~]#vim backup.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.182',
MASTER_USER='xiaohong',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000005', MASTER_LOG_POS=884;
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]>set sql_log_bin=off;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> source backup.sql;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]>set sql_log_bin=on;
Query OK, 0 rows affected (0.001 sec)
MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.001 sec)
3、通过 MHA 0.58 搭建一个数据库集群结构
准备：四台主机
- 10.0.0.171 centos7 MHA 管理端
- 10.0.0.181 centos8 master
- 10.0.0.182 centos8 slave1
- 10.0.0.183 centos8 slave2
#在管理节点安装两个包
[root@mha-manager ~]#yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#在所有MySQL服务器上安装mha4mysql-node包
[root@master ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave1 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave2 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#在所有节点实现相互之间的ssh key验证
[root@mha-manager ~]#ssh-keygen
[root@mha-manager ~]#ssh-copy-id 10.0.0.171
[root@mha-manager ~]#rsync -av .ssh 10.0.0.181:/root/
[root@mha-manager ~]#rsync -av .ssh 10.0.0.181:/root/
[root@mha-manager ~]#rsync -av .ssh 10.0.0.181:/root/
#在管理节点建立配置文件
[root@mha-manager ~]#mkdir /etc/mhamanager
[root@mha-manager ~]#vim /etc/mhamanager/app1.cnf
[server default]
user=mhauser
password=123456
manager_workdir=/data/mhamanager/app1/
manager_log=/data/mhamanager/app1/manager.log
remote_workdir=/data/mhamanager/app1/
ssh_user=root
repl_user=repluser
repl_password=123456
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
master_binlog_dir=/data/mysql/
[server1]
hostname=10.0.0.181
[server2]
hostname=10.0.0.182
candidate_master=1
[server3]
hostname=10.0.0.183
#相关脚本
[root@mha-manager ~]#vim /usr/local/bin/sendmail.sh
echo "MySQL is down"|mail -s "MHA warning" 609037350@qq.com
[root@mha-manager ~]#vim /usr/local/bin/master_ip_failover
[root@mha-manager ~]#chmod +x /usr/local/bin/master_ip_failover
#实现master
[root@master ~]#mkdir /data/mysql/
[root@master ~]#chown mysql.mysql /data/mysql/
[root@master ~]#vim /etc/my.cnf
[mysqld]
server-id=181
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1
general_log
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to mhauser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#配置VIP
[root@master ~]#ifconfig eth0:1 10.0.0.100/24
#实现slave
[root@slave1 ~]#mkdir /data/mysql
[root@slave1 ~]#chown mysql.mysql /data/mysql/
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=182
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1
mysql> CHANGE MASTER TO
->
-> MASTER_HOST='10.0.0.181',
->
-> MASTER_USER='repluser',
->
-> MASTER_PASSWORD='123456',
->
-> MASTER_LOG_FILE='mysql-bin.000001',
->
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.181
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: slave1-relay-bin.000004
Relay_Log_Pos: 367
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: 154
Relay_Log_Space: 21330
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: 181
Master_UUID: 24bddf2d-1044-11eb-b4cf-000c29f8f583
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
1 row in set (0.00 sec)
#其它从节点执行和slave1相同步骤
#检查mha环境
[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Sun Oct 18 17:15:20 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Oct 18 17:15:20 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Oct 18 17:15:20 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Sun Oct 18 17:15:20 2020 - [info] Starting SSH connection tests..
Sun Oct 18 17:15:21 2020 - [debug]
Sun Oct 18 17:15:20 2020 - [debug] Connecting via SSH from root@10.0.0.181(10.0.0.181:22) to root@10.0.0.182(10.0.0.182:22)..
Sun Oct 18 17:15:21 2020 - [debug] ok.
Sun Oct 18 17:15:21 2020 - [debug] Connecting via SSH from root@10.0.0.181(10.0.0.181:22) to root@10.0.0.183(10.0.0.183:22)..
Sun Oct 18 17:15:21 2020 - [debug] ok.
Sun Oct 18 17:15:22 2020 - [debug]
Sun Oct 18 17:15:21 2020 - [debug] Connecting via SSH from root@10.0.0.182(10.0.0.182:22) to root@10.0.0.181(10.0.0.181:22)..
Sun Oct 18 17:15:21 2020 - [debug] ok.
Sun Oct 18 17:15:21 2020 - [debug] Connecting via SSH from root@10.0.0.182(10.0.0.182:22) to root@10.0.0.183(10.0.0.183:22)..
Sun Oct 18 17:15:21 2020 - [debug] ok.
Sun Oct 18 17:15:23 2020 - [debug]
Sun Oct 18 17:15:21 2020 - [debug] Connecting via SSH from root@10.0.0.183(10.0.0.183:22) to root@10.0.0.181(10.0.0.181:22)..
Sun Oct 18 17:15:22 2020 - [debug] ok.
Sun Oct 18 17:15:22 2020 - [debug] Connecting via SSH from root@10.0.0.183(10.0.0.183:22) to root@10.0.0.182(10.0.0.182:22)..
Sun Oct 18 17:15:22 2020 - [debug] ok.
Sun Oct 18 17:15:23 2020 - [info] All SSH connection tests passed successfully.
[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
Sun Oct 18 17:50:04 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Oct 18 17:50:04 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Oct 18 17:50:04 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Sun Oct 18 17:50:04 2020 - [info] MHA::MasterMonitor version 0.58.
Sun Oct 18 17:50:05 2020 - [info] GTID failover mode = 0
Sun Oct 18 17:50:05 2020 - [info] Dead Servers:
Sun Oct 18 17:50:05 2020 - [info] Alive Servers:
Sun Oct 18 17:50:05 2020 - [info] 10.0.0.181(10.0.0.181:3306)
Sun Oct 18 17:50:05 2020 - [info] 10.0.0.182(10.0.0.182:3306)
Sun Oct 18 17:50:05 2020 - [info] 10.0.0.183(10.0.0.183:3306)
Sun Oct 18 17:50:05 2020 - [info] Alive Slaves:
Sun Oct 18 17:50:05 2020 - [info] 10.0.0.182(10.0.0.182:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled
Sun Oct 18 17:50:05 2020 - [info] Replicating from 10.0.0.181(10.0.0.181:3306)
Sun Oct 18 17:50:05 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Oct 18 17:50:05 2020 - [info] 10.0.0.183(10.0.0.183:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled
Sun Oct 18 17:50:05 2020 - [info] Replicating from 10.0.0.181(10.0.0.181:3306)
Sun Oct 18 17:50:05 2020 - [info] Current Alive Master: 10.0.0.181(10.0.0.181:3306)
Sun Oct 18 17:50:05 2020 - [info] Checking slave configurations..
Sun Oct 18 17:50:05 2020 - [info] Checking replication filtering settings..
Sun Oct 18 17:50:05 2020 - [info] binlog_do_db= , binlog_ignore_db=
Sun Oct 18 17:50:05 2020 - [info] Replication filtering check ok.
Sun Oct 18 17:50:05 2020 - [info] GTID (with auto-pos) is not supported
Sun Oct 18 17:50:05 2020 - [info] Starting SSH connection tests..
Sun Oct 18 17:50:08 2020 - [info] All SSH connection tests passed successfully.
Sun Oct 18 17:50:08 2020 - [info] Checking MHA Node version..
Sun Oct 18 17:50:08 2020 - [info] Version check ok.
Sun Oct 18 17:50:08 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sun Oct 18 17:50:08 2020 - [info] HealthCheck: SSH to 10.0.0.181 is reachable.
Sun Oct 18 17:50:09 2020 - [info] Master MHA Node version is 0.58.
Sun Oct 18 17:50:09 2020 - [info] Checking recovery script configurations on 10.0.0.181(10.0.0.181:3306)..
Sun Oct 18 17:50:09 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/ --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000002
Sun Oct 18 17:50:09 2020 - [info] Connecting to root@10.0.0.181(10.0.0.181:22)..
Creating /data/mastermha/app1 if not exists.. Creating directory /data/mastermha/app1.. done.
ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql/, up to mysql-bin.000002
Sun Oct 18 17:50:09 2020 - [info] Binlog setting check done.
Sun Oct 18 17:50:09 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sun Oct 18 17:50:09 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.182 --slave_ip=10.0.0.182 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Sun Oct 18 17:50:09 2020 - [info] Connecting to root@10.0.0.182(10.0.0.182:22)..
Creating directory /data/mastermha/app1/.. done.
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to slave1-relay-bin.000004
Temporary relay log file is /data/mysql/slave1-relay-bin.000004
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sun Oct 18 17:50:09 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.183 --slave_ip=10.0.0.183 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Sun Oct 18 17:50:09 2020 - [info] Connecting to root@10.0.0.183(10.0.0.183:22)..
Creating directory /data/mastermha/app1/.. done.
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to slave2-relay-bin.000005
Temporary relay log file is /data/mysql/slave2-relay-bin.000005
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sun Oct 18 17:50:10 2020 - [info] Slaves settings check done.
Sun Oct 18 17:50:10 2020 - [info]
10.0.0.181(10.0.0.181:3306) (current master)
+--10.0.0.182(10.0.0.182:3306)
+--10.0.0.183(10.0.0.183:3306)
Sun Oct 18 17:50:10 2020 - [info] Checking replication health on 10.0.0.182..
Sun Oct 18 17:50:10 2020 - [info] ok.
Sun Oct 18 17:50:10 2020 - [info] Checking replication health on 10.0.0.183..
Sun Oct 18 17:50:10 2020 - [info] ok.
Sun Oct 18 17:50:10 2020 - [info] Checking master_ip_failover_script status:
Sun Oct 18 17:50:10 2020 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.0.0.181 --orig_master_ip=10.0.0.181 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.0.100/24;/sbin/arping -I
eth0 -c 3 -s 10.0.0.100/24 10.0.0.2 >/dev/null 2>&1===
Checking the Status of the script.. OK
/sbin/arping: option requires an argument -- 'I'
Usage: arping [-fqbDUAV] [-c count] [-w timeout] [-I device] [-s source] destination
-f : quit on first reply
-q : be quiet
-b : keep broadcasting, don't go unicast
-D : duplicate address detection mode
-U : Unsolicited ARP mode, update your neighbours
-A : ARP answer mode, update your neighbours
-V : print version and exit
-c count : how many packets to send
-w timeout : how long to wait for a reply
-I device : which ethernet device to use
-s source : source ip address
destination : ask for what ip address
Sun Oct 18 17:50:10 2020 - [info] OK.
Sun Oct 18 17:50:10 2020 - [warning] shutdown_script is not defined.
Sun Oct 18 17:50:10 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
#查看状态
[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
#开启mha，默认是前台运行
[root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf
Sun Oct 18 17:53:44 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Oct 18 17:53:44 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Oct 18 17:53:44 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
#查看健康性检查
[root@master ~]#tail -f /data/mysql/master.log
2020-10-18T09:57:51.118189Z 36 Query SELECT 1 As Value
2020-10-18T09:57:52.117141Z 36 Query SELECT 1 As Value
2020-10-18T09:57:53.118033Z 36 Query SELECT 1 As Value
2020-10-18T09:57:54.118403Z 36 Query SELECT 1 As Value
2020-10-18T09:57:55.118674Z 36 Query SELECT 1 As Value
2020-10-18T09:57:56.119437Z 36 Query SELECT 1 As Value
2020-10-18T09:57:57.120658Z 36 Query SELECT 1 As Value
.....
[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:6265) is running(0:PING_OK), master:10.0.0.181
4、实战案例：Percona XtraDB Cluster(PXC 5.7）
准备：三台主机
- 10.0.0.171 centos7 pxc1
- 10.0.0.172 centos7 pxc2
- 10.0.0.173 centos7 pxc3
os版本目前不支持centos8
[root@pxc1 ~]#cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
关闭防火墙和selinux，保证时间同步
注意：如果已经安装MySQL，必须卸载
安装percona xtradb cluster5.7
#设置yum源
[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/\$releasever/RPMS/\$basearch
enabled=1
gpgcheck=0
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.172:/etc/yum.repos.d/
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.173:/etc/yum.repos.d/
#在三个节点都安装好pxc 5.7
[root@pxc1 ~]##yum install Percona-XtraDB-Cluster-57 -y
[root@pxc2 ~]##yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]##yum install Percona-XtraDB-Cluster-57 -y
在各个节点上分别配置mysql和集群配置文件
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=171 #每个节点各不相同
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.171,10.0.0.172,10.0.00.173 #三个节点ip
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
wsrep_node_address=10.0.0.171 #各个节点，指定自己的ip
# Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-1 #各个节点，指定自己节点名称
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释
#对其它节点执行上面操作
启动pxc集群中第一个节点
[root@pxc1 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:*
tcp LISTEN 0 100 127.0.0.1:25 *:*
tcp LISTEN 0 128 [::]:22 [::]:*
tcp LISTEN 0 100 [::1]:25 [::]:*
#启动第一个节点
[root@pxc1 ~]#systemctl start mysql@bootstrap.service
[root@pxc1 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:*
tcp LISTEN 0 128 *:4567 *:*
tcp LISTEN 0 100 127.0.0.1:25 *:*
tcp LISTEN 0 128 [::]:22 [::]:*
tcp LISTEN 0 100 [::1]:25 [::]:*
tcp LISTEN 0 80 [::]:3306 [::]:*
#查看root密码
[root@pxc1 ~]#grep "temporary password" /var/log/mysqld.log
2020-10-18T13:37:40.075675Z 1 [Note] A temporary password is generated for root@localhost: j9f4Lps;woO6
#登陆mysql
[root@pxc1 ~]#mysql -uroot -p'j9f4Lps;woO6'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.31-34-57-log
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
#修改root密码
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
#创建相关用户并授权
mysql> create user 'sstuser'@'localhost' identified by 's3cretPass';
Query OK, 0 rows affected (0.00 sec)
mysql> grant reload,lock tables,process,replication client on *.* to 'sstuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
启动pxc集群中其它所有节点
[root@pxc2 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:*
tcp LISTEN 0 100 127.0.0.1:25 *:*
tcp LISTEN 0 128 [::]:22 [::]:*
tcp LISTEN 0 100 [::1]:25 [::]:*
[root@pxc2 ~]#systemctl start mysql
[root@pxc2 ~]#ss -ntulp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=784,fd=3))
tcp LISTEN 0 128 *:4567 *:* users:(("mysqld",pid=3122,fd=11))
tcp LISTEN 0 100 127.0.0.1:25 *:* users:(("master",pid=1015,fd=13))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=784,fd=4))
tcp LISTEN 0 100 [::1]:25 [::]:* users:(("master",pid=1015,fd=14))
tcp LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=3122,fd=43))
[root@pxc3 ~]#systemctl start mysql
查看集群状态，验证集群是否成功
#在任意节点查看集群状态
[root@pxc1 ~]#mysql -uroot -p123456
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.01 sec)
#在任意节点创建数据库
[root@pxc3 ~]#mysql -uroot -p123456
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#在其它节点验证数据是否同步
[root@pxc2 ~]#mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#利用xshell软件，同时在三个节点创建数据库
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
#其它节点提示失败
mysql> create database db2;
ERROR 1007 (HY000): Can't create database 'db2'; database exists
在pxc集群中加入新节点
#在pxc集群中添加一台新的主机pxc4:10.0.0.174
[root@pxc4 ~]#yum -y install Percona-XtraDB-Cluster-57
[root@pxc4 ~]#vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
#修改配置文件
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=174
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
[root@pxc4 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.171,10.0.0.172,10.0.0.173,10.0.0.174
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
wsrep_node_address=10.0.0.174
# Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-4
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="sstuser:s3cretPass"
[root@pxc4 ~]#systemctl start mysql
[root@pxc4 ~]#mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision 7359e4f, WSREP version 31.45, wsrep_31.45
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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 |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
#修改其它节点的配置文件
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://10.0.0.171,10.0.0.172,10.0.0.173,10.0.0.174
#其它节点执行上面命令修改配置
5、通过 ansible 部署二进制 mysql 8
#安装ansible并修改配置文件
[root@ansible ~]#yum -y install ansible
[root@ansible ~]#vim /etc/ansible/hosts
# Ex 3: A collection of database servers in the 'dbservers' group
## [dbservers]
##
## db01.intranet.mydomain.net
## db02.intranet.mydomain.net
## 10.25.1.56
## 10.25.1.57
[dbservers]
10.0.0.182
#实现基于key验证的脚本
[root@ansible ~]#vim ssh_key.sh
#!/bin/bash
#**********************************************************************
#Author: DOU
#Date: 2020-10-19
#FileName: ssh_key.sh
#Description: The test script
#**********************************************************************
#
IPLIST="
10.0.0.182
"
rpm -q sshpass &> /dev/null || yum -y install sshpass
[ -f /root/.ssh/id_rsa ] || ssh-keygen -f /root/.ssh/id_rsa -P ''
export SSHPASS=123456
for IP in $IPLIST;do
sshpass -e ssh-copy-id -o StrictHostKeyChecking=no $IP
done
[root@ansible ~]#bash ssh_key.sh
#创建安装数据库所需要的文件
[root@ansible ansible]#cat install_mysql.yml
---
- hosts: dbsrvs
remote_user: root
gather_facts: no
tasks:
- name: create user mysql
shell: id mysql &> /dev/null || useradd mysql -r -d /data/mysql -s /sbin/nologin
- name: import packages
yum: name=libaio,perl-Data-Dumper,ncurses-c++-libs,ncurses-compat-libs
- name: data dir
file: path=/data/mysql state=directory owner=mysql group=mysql
- name: copy file
unarchive: src=files/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz dest=/usr/local owner=root group=root
- name: create link
file: src=/usr/local/mysql-8.0.19-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link
- name: path
copy: content='PATH=/usr/local/mysql/bin/:$PATH' dest=/etc/profile.d/mysql.sh
- name: source path
shell: source /etc/profile.d/mysql.sh
- name: config file
copy: content='[mysqld]\ndatadir=/data/mysql\nsocket=/data/mysql/mysql.sock\nlog-error=/data/mysql/mysql.log\npid-file=/data/mysql/mysql.pid\n\n[client]\nport=3306\nsocket=/data/mysql/mysql.sock\n' dest=/etc/my.cnf
- name: initialize file
shell: /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql
- name: cp service
shell: /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
- name: service config
shell: chkconfig --add mysqld;chkconfig mysqld on;service mysqld start
- name: change mysql password
shell: mysqladmin -uroot -p`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log` password 123456
[root@ansible ansible]#ansible-playbook install_mysql.yml
PLAY [dbsrvs] ******************************************************************
TASK [create user mysql] *******************************************************
changed: [10.0.0.182]
TASK [import packages] *********************************************************
changed: [10.0.0.182]
TASK [data dir] ****************************************************************
changed: [10.0.0.182]
TASK [copy file] ***************************************************************
changed: [10.0.0.182]
TASK [create link] *************************************************************
changed: [10.0.0.182]
TASK [path] ********************************************************************
changed: [10.0.0.182]
TASK [source path] *************************************************************
changed: [10.0.0.182]
TASK [config file] *************************************************************
changed: [10.0.0.182]
TASK [initialize file] *********************************************************
changed: [10.0.0.182]
TASK [cp service] **************************************************************
changed: [10.0.0.182]
TASK [service config] **********************************************************
changed: [10.0.0.182]
TASK [change mysql password] ***************************************************
changed: [10.0.0.182]
PLAY RECAP *********************************************************************
10.0.0.182 : ok=12 changed=12 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
[root@centos182 ~]#mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> exit
