运维开发网

作业10

运维开发网 https://www.qedev.com 2020-10-20 12:05 出处:51CTO 作者:打豆豆c
1、如果主节点已经运行了一段时间,且有大量数据时,如何配置并启动slave节点(写出操作步骤)准备:两台主机,数据库版本一样主服务器10.0.0.181centos8从服务器10.0.0.182centos8主服务器#备份前设置主服务器数据库server-id和log-bin[[email protected]~]#vim/etc/my.cnf.d/mariadb-server.cnf[mysqld]s

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

准备:两台主机,数据库版本一样

  • 主服务器10.0.0.181 centos8
  • 从服务器10.0.0.182 centos8

主服务器

#备份前设置主服务器数据库server-id和log-bin
[[email protected] ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=181
log-bin
[[email protected] ~]#systemctl restart mariadb

#在主服务器完全备份数据库
[[email protected] ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup-`date +%F_%T`.sql
[[email protected] ~]#ll /backup/
total 484
-rw-r--r-- 1 root root 487683 Oct 16 19:35 fullbackup-2020-10-16_19:35:01.sql

#拷贝数据库备份到从服务器
[[email protected] ~]#scp /backup/fullbackup-2020-10-16_19\:35\:01.sql 10.0.0.182:/data/
[[email protected] ~]#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 [email protected]'10.0.0.%' identified by 123456
Query OK, 0 rows affected (0.001 sec)

从服务器

#设置从服务器数据库server-id
[[email protected] ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=182
read-only
[[email protected] ~]#systemctl restart mariadb

#配置从节点,从完全备份的位置之后开始复制
[[email protected] ~]#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;
[[email protected] ~]#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;

[[email protected] ~]#mysql < /data/fullbackup-2020-10-16_19\:35\:01.sql 
[[email protected] ~]#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
[[email protected] ~]#cat /var/lib/mysql/relay-log.info 
5
./mariadb-relay-bin.000004
690
mariadb-bin.000003
389
0

[[email protected] ~]#cat /var/lib/mysql/relay-log.info 
5
./mariadb-relay-bin.000002
557
mariadb-bin.000003
389
0

#新master配置文件修改,关闭read-only,开启二进制日志
[[email protected] ~]#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 [email protected]'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)

#在新的master上完全备份
[[email protected] ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/backup.sql
#复制SQL语句到从节点
[[email protected] ~]#scp /backup/backup.sql 10.0.0.183:

#分析旧的master的二进制日志,将未同步到新master的二进制日志导出来,恢复到新master,尽可能恢复数据

#其它从节点重新还原数据库,指向新的master
[[email protected] ~]#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
#在管理节点安装两个包
[[email protected]nager ~]#yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  mha4mysql-node-0.58-0.el7.centos.noarch.rpm 

#在所有MySQL服务器上安装mha4mysql-node包
[[email protected] ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
[[email protected] ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
[[email protected] ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm 

#在所有节点实现相互之间的ssh key验证
[[email protected] ~]#ssh-keygen
[[email protected] ~]#ssh-copy-id 10.0.0.171
[[email protected] ~]#rsync -av .ssh 10.0.0.181:/root/
[[email protected] ~]#rsync -av .ssh 10.0.0.181:/root/
[[email protected] ~]#rsync -av .ssh 10.0.0.181:/root/

#在管理节点建立配置文件
[[email protected] ~]#mkdir /etc/mhamanager
[[email protected] ~]#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

#相关脚本
[[email protected] ~]#vim /usr/local/bin/sendmail.sh

echo "MySQL is down"|mail -s "MHA warning" [email protected]

[[email protected] ~]#vim /usr/local/bin/master_ip_failover
[[email protected] ~]#chmod +x /usr/local/bin/master_ip_failover

#实现master
[[email protected] ~]#mkdir /data/mysql/
[[email protected] ~]#chown mysql.mysql /data/mysql/

[[email protected] ~]#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 [email protected]'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on *.* to [email protected]'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#配置VIP
[[email protected] ~]#ifconfig eth0:1 10.0.0.100/24

#实现slave
[[email protected] ~]#mkdir /data/mysql
[[email protected] ~]#chown mysql.mysql /data/mysql/
[[email protected] ~]#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环境
[[email protected] ~]#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 [email protected](10.0.0.181:22) to [email protected](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 [email protected](10.0.0.181:22) to [email protected](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 [email protected](10.0.0.182:22) to [email protected](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 [email protected](10.0.0.182:22) to [email protected](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 [email protected](10.0.0.183:22) to [email protected](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 [email protected](10.0.0.183:22) to [email protected](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.

[[email protected] ~]#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 [email protected](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 [email protected](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 [email protected](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.

#查看状态
[[email protected] ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

#开启mha,默认是前台运行
[[email protected] ~]#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..

#查看健康性检查
[[email protected] ~]#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
.....

[[email protected] ~]#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

[[email protected] ~]#cat /etc/redhat-release 
CentOS Linux release 7.8.2003 (Core)

关闭防火墙和seLinux,保证时间同步

注意:如果已经安装MySQL,必须卸载

安装percona xtradb cluster5.7

#设置yum源
[[email protected] ~]#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

[[email protected] ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.172:/etc/yum.repos.d/
[[email protected] ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.173:/etc/yum.repos.d/

#在三个节点都安装好pxc 5.7
[[email protected] ~]##yum install Percona-XtraDB-Cluster-57 -y
[[email protected] ~]##yum install Percona-XtraDB-Cluster-57 -y
[[email protected] ~]##yum install Percona-XtraDB-Cluster-57 -y

在各个节点上分别配置mysql和集群配置文件

[[email protected] ~]#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

[[email protected] ~]#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集群中第一个节点

[[email protected] ~]#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                           [::]:*  

#启动第一个节点
[[email protected] ~]#systemctl start [email protected]
[[email protected] ~]#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密码
[[email protected] ~]#grep "temporary password" /var/log/mysqld.log 
2020-10-18T13:37:40.075675Z 1 [Note] A temporary password is generated for [email protected]: j9f4Lps;woO6

#登陆mysql
[[email protected] ~]#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集群中其它所有节点

[[email protected] ~]#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                                [::]:* 

[[email protected] ~]#systemctl start mysql
[[email protected] ~]#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))

[[email protected] ~]#systemctl start mysql

查看集群状态,验证集群是否成功

#在任意节点查看集群状态
[[email protected] ~]#mysql -uroot -p123456
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.01 sec)

#在任意节点创建数据库
[[email protected] ~]#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)

#在其它节点验证数据是否同步
[[email protected] ~]#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
[[email protected] ~]#yum -y install Percona-XtraDB-Cluster-57 
[[email protected] ~]#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

[[email protected] ~]#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"
[[email protected] ~]#systemctl start mysql

[[email protected] ~]#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)

#修改其它节点的配置文件
[[email protected] ~]#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
#其它节点执行上面命令修改配置

扫码领视频副本.gif

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号