第十周 mysql 和ansible 作业

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

#1. 在主服务器完全备份
[root@c8-108-master ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/backup/fullbackup_`date +%F_%T`.sql
[root@c8-108-master ~]#ll /data/backup
total 480
-rw-r--r-- 1 root root 487947 Oct 17 11:20 fullbackup_2020-10-17_11:20:47.sql

[root@c8-108-master ~]#scp /data/backup/fullbackup_2020-10-17_11\:20\:47.sql 10.0.0.128:/data/
root@10.0.0.128's password: 
fullbackup_2020-10-17_11:20:47.sql        100%  477KB  17.2MB/s   00:00    

#2. 将完全备份还原到新的slave 从节点中
[root@c8-128-slave2 ~]#yum install mariadb-server -y
[root@c8-128-slave2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=128
read_only
log_bin
binlog_format=row
innodb_file_per_table

[root@c8-128-slave2 ~]#systemctl restart mariadb.service

#3. 配置从节点，从完全备份的位置之后开始复制
[root@c8-128-slave2 ~]#grep '^CHANGE MASTER' /data/fullbackup_2020-10-17_11\:20\:47.sql 
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000004', MASTER_LOG_POS=387;

[root@c8-128-slave2 ~]#vim /data/fullbackup_2020-10-17_11\:20\:47.sql
CHANGE MASTER TO MASTER_HOST='10.0.0.108', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='master-bin.000004', 
MASTER_LOG_POS=387;

[root@c8-128-slave2 ~]#vim /data/fullbackup_2020-10-17_11\:20\:47.sql
[root@c8-128-slave2 ~]#mysql < /data/fullbackup_2020-10-17_11\:20\:47.sql 
[root@c8-128-slave2 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

#1. 查看从节点的数据库，将最新的那个从节点升级为master
[root@c8-118-slave1 ~]#cat /var/lib/mysql/relay-log.info 
5
./relay-log.000003
1114
master-bin.000006
978
0

#2. 将slave1 升级为master，修改配置文件，关闭read-only 配置
[mysqld]
server_id=118
read_only=OFF
# 路径/data/mysql 一定要设置权限mysql:mysql
log_bin=/data/mysql/logbin/mysql-bin   


#3. 清除旧的master 复制信息
MariaDB [hellodb]> set global read_only=off;
MariaDB [hellodb]> stop slave;
MariaDB [hellodb]> reset slave all;

#4. 在新的master 上做完全备份
[root@c8-118-slave1 ~]#mysqldump -A -F --single-transaction --master-data=1 > backup.sql
[root@c8-118-slave1 ~]#scp backup.sql 10.0.0.128:

#5. 在实际环境中，需要分析旧的master的二进制日志，将未同步到至新master的二进制日志导出，恢复到新master，尽可能恢复数据

#6. 其他所有slave 重新还原数据库，指向新的master
[root@c8-128-slave2 ~]#vim backup.sql
CHANGE MASTER TO MASTER_HOST='10.0.0.118', 
MASTER_USER='repluser', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000002', 
MASTER_LOG_POS=525;

MariaDB [(none)]> stop slave;
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source backup.sql;
MariaDB [(none)]> set sql_log_bin=on;
MariaDB [(none)]> start slave;

3、通过 MHA 0.58 搭建一个数据库集群结构

集群环境:
MHA-manager:10.0.0.107	Centos7
master:10.0.0.108	Centos8
slave1:10.0.0.118	Centos8
slave2:10.0.0.128	Centos8

注意：
mha4mysql-manager 只支持centos7 以下的版本
mha4mysql-node	支持centos6/7/8

#相关配置文件准备：
#1. master_ip_failover 文件
[root@mha-manager ~]#cat /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.0.0.100';#设置Virtual IP
my $gateway = '10.0.0.254';#网关Gateway IP
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

#2. sendmail 文件准备
[root@mha-manager ~]#cat /usr/local/bin/sendmail.sh
echo "MySQL is down" | mail -s "MHA Waring" dan_xiao1040@163.com

#注意：需要修改/etc/postfix/main.cf 中的配置参数，否则会报错
[root@mha-manager ~]#vim /etc/postfix/main.cf
#inet_interfaces = $myhostname, localhost
inet_interfaces = all
# Enable IPv4, and IPv6 if supported
inet_protocols = all

#搭建步骤：
#1. 在所有节点中实现相互之间ssh key 验证
[root@manager ~]#ssh-keygen
[root@manager ~]#ssh-copy-id 10.0.0.107

#注意：确保10.0.0.108/118/128 中已安装了rsync，否则会报错
[root@manager ~]#rsync -av .ssh 10.0.0.108:/root/
[root@manager ~]#rsync -av .ssh 10.0.0.118:/root/
[root@manager ~]#rsync -av .ssh 10.0.0.128:/root/

#2. manager中安装mha4mysql-manager 和mha4mysql-node
#建议安装顺序：相关依赖包，node，manager
[root@manager ~]#yum install perl-DBD-MySQL
[root@manager ~]#yum install perl-Config-Tiny
[root@manager ~]#yum install perl-Log-Dispatch
[root@manager ~]#yum install perl-Parallel-ForkManager
[root@manager ~]#yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@manager ~]#yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

#3. master/slave1/slave2 中安装mha4mysql-node
[root@master ~]#yum install perl-DBD-MySQL
[root@master ~]#yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

#4. manager中创建配置文件
[root@manager ~]#vim /etc/mastermha/app1.cnf
[root@manager ~]#cat /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=123456
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=123456
ping_interval=2
[server1]
hostname=10.0.0.108
port=3306
[server2]
hostname=10.0.0.118
port=3306
candidate_master=1
[server3]
hostname=10.0.0.128
port=3306

#5. 实现master
[root@master ~]#yum install mysql-5.7.30 -y
[root@master ~]#vim /etc/my.cnf
[mysqld]
[mysqld]
server-id=108
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1

[root@master ~]#systemctl start mysqld
[root@master ~]#mysql -uroot -p123456
mysql> show master logs;
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to mhauser@'10.0.0.%' identified by '123456';

#6. 实现slave(1和2的操作一致)
[root@slave1 ~]#yum install mysql-5.7.30 -y
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=118
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1

[root@slave1 ~]#systemctl start mysqld
[root@slave1 ~]#mysql -uroot -p123456
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.108',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000003',
    ->   MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 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.108
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 738
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 904
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 738
              Relay_Log_Space: 1112
              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: 108
                  Master_UUID: 06335329-1130-11eb-846c-000c29c54577
             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)

#7. 检查主从复制是否成功，成功进行下一步(主库创建修改数据，都可，略)
#8. 检查MHA 环境
[root@manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Sun Oct 18 17:16:20 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Oct 18 17:16:20 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Oct 18 17:16:20 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Sun Oct 18 17:16:20 2020 - [info] Starting SSH connection tests..
Sun Oct 18 17:16:21 2020 - [debug] 
Sun Oct 18 17:16:20 2020 - [debug]  Connecting via SSH from root@10.0.0.108(10.0.0.108:22) to root@10.0.0.118(10.0.0.118:22)..
Warning: Permanently added '10.0.0.118' (ECDSA) to the list of known hosts.
Sun Oct 18 17:16:21 2020 - [debug]   ok.
Sun Oct 18 17:16:21 2020 - [debug]  Connecting via SSH from root@10.0.0.108(10.0.0.108:22) to root@10.0.0.128(10.0.0.128:22)..
Warning: Permanently added '10.0.0.128' (ECDSA) to the list of known hosts.
Sun Oct 18 17:16:21 2020 - [debug]   ok.
Sun Oct 18 17:16:23 2020 - [debug] 
Sun Oct 18 17:16:21 2020 - [debug]  Connecting via SSH from root@10.0.0.118(10.0.0.118:22) to root@10.0.0.108(10.0.0.108:22)..
Sun Oct 18 17:16:21 2020 - [debug]   ok.
Sun Oct 18 17:16:21 2020 - [debug]  Connecting via SSH from root@10.0.0.118(10.0.0.118:22) to root@10.0.0.128(10.0.0.128:22)..
Warning: Permanently added '10.0.0.128' (ECDSA) to the list of known hosts.
Sun Oct 18 17:16:22 2020 - [debug]   ok.
Sun Oct 18 17:16:23 2020 - [debug] 
Sun Oct 18 17:16:21 2020 - [debug]  Connecting via SSH from root@10.0.0.128(10.0.0.128:22) to root@10.0.0.108(10.0.0.108:22)..
Sun Oct 18 17:16:22 2020 - [debug]   ok.
Sun Oct 18 17:16:22 2020 - [debug]  Connecting via SSH from root@10.0.0.128(10.0.0.128:22) to root@10.0.0.118(10.0.0.118:22)..
Sun Oct 18 17:16:22 2020 - [debug]   ok.
Sun Oct 18 17:16:23 2020 - [info] All SSH connection tests passed successfully.

[root@manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
[root@manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf

#9. MHA启动
#默认是前台启动
[root@manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf
#后台启动方式
[root@manager ~]#nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null

#查看状态
[root@manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf 

4、实战案例：Percona XtraDB Cluster(PXC 5.7）

环境准备：
pxc1:10.0.0.107
pxc2:10.0.0.117
pxc3:10.0.0.127

yum 源配置
[root@c7-107 ~]# cat /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

#在三个节点都安装好PXC 5.7
[root@c7-117 ~]# yum install Percona-XtraDB-Cluster-57 -y

#搭建步骤
#1. 配置mysql及集群配置文件
#配置107
[root@c7-107 ~]# cat /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=107
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@c7-107 ~]# grep -v '^[#]' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.107,10.0.0.117,10.0.0.127
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.107
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"


[root@c7-117 ~]# grep -v '^[#]' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.107,10.0.0.117,10.0.0.127
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.117
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-2
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"

[root@c7-117 ~]# cat /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=117
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@c7-127 ~]# cat /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=127
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@c7-127 ~]# grep -v '^[#]' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.107,10.0.0.117,10.0.0.127
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.127
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-3
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"

#2 启动pxc集群中的第一个节点
[root@c7-107 ~]# systemctl start mysql@bootstrap.service

# 查看密码
[root@c7-107 ~]# awk '/A temporary password/{print $NF}' /var/log/mysqld.log
g-?_(uG6U*eW
[root@c7-107 ~]# mysql -uroot -p'g-?_(uG6U*eW'

#修改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)

#查看相关变量
mysql> SHOW VARIABLES LIKE 'wsrep%'\G;
#查看相关状态变量
mysql> SHOW STATUS LIKE 'wsrep%'\G;
#重点关注下面内容
mysql> show status like 'wsrep%'\G;

#3 启动pxc集群中其他所有节点
[root@c7-117 ~]# systemctl start mysql
[root@c7-127 ~]# systemctl start mysql

#4 查看集群状态，验证集群是否成功
[root@c7-107 ~]#mysql -uroot -p123456
mysql> SHOW VARIABLES LIKE 'wsrep_node_name';
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';

#在任意节点查看数据库
mysql> show databases;

#在任意节点创建数据库
mysql> create database db1;

#在任意其它节点验证数据是否同步
[root@c7-127 ~]# mysql -uroot -p123456

5、通过 ansible 部署二进制 mysql 8

环境
10.0.0.18  （安装ansible）
10.0.0.8

#部署步骤
[root@c8-18 ~]# yum -y install ansible
[root@c8-18 ~]# vim /etc/ansible/hosts
10.0.0.8

#基于key验证
[root@c8-18 ~]# ssh-keygen
[root@c8-18 ~]# ssh-copy-id 10.0.0.18
[root@c8-18 ~]# rsync -av .ssh 10.0.0.8:/root/

[root@c8-18 ~]#cat /data/ansible/files/my.cnf
[mysqld]
socket=/tmp/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
log-bin
pid-file=/data/mysql/mysqld.pid
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/data/mysql/mysqld_error.log

[root@c8-18 ~]#cat /data/ansible/files/mysql_secret.sh 
#!/bin/bash cp -f /usr/local/mysql/support-files/mysql.server 
/etc/rc.d/init.d/mysqld && chkconfig --add mysqld /usr/local/mysql/bin/mysqladmin -u root password "123456"

[root@c8-18 ~]#tree /data/ansible/files/
/data/ansible/files/
├── my.cnf
├── mysql_secret.sh
└── mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

[root@c8-18 ~]# cat /data/ansible/install_mysql.yml
#部署文件
---
- hosts: 10.0.0.8
  remote_user: root
  gather_facts: no

  tasks: 
    - name: "创建Mysql用户"
      shell: id mysql &> /dev/null || useradd -r -d /data/mysql -s /sbin/nologin mysql
    - name: "安装依赖包"
      yum: name=libaio,perl-Data-Dumper,ncurses-c++-libs,ncurses-compat-libs
    - name: "创建mysql的家目录文件夹"
      file: path=/data/mysql state=directory owner=mysql group=mysql
    - name: "解压二进制程序"
      unarchive: src=/data/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz dest=/usr/local owner=root group=root
    - name: "将压缩后的文件设置为软链接"
      file: src=/usr/local/mysql-8.0.20-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link
    - name: "环境变量"
      copy: content='PATH=/usr/local/mysql/bin/:$PATH' dest=/etc/profile.d/mysql.sh
    - name: "同步环境变量"
      shell: source /etc/profile.d/mysql.sh
    - name: "准备MySQL配置文件"
      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: "生成数据库文件"
      shell: /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql
    - name: "复制服务启动文件"
      shell: /bin/cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld 
    - name: "启动mysql服务并设置开机启动"
      shell: chkconfig --add mysqld;chkconfig mysqld on;service mysqld start
    - name: "修改mysql默认密码"
      shell: mysqladmin  -uroot -p`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log` password 123456
      
[root@c8-18 ~]# ansible-playbook  /data/ansible/install_mysql.yml