关于MySQLslave升级master， MHA ，PXC5.7和ansible自动化安装mysql8.0
1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）
answer：通过恢复备份恢复数据到从服务器复制起始位置为备份POS和二进制
@1修改配置文件
@2备份出数据库,会自动刷新日志,查看二进制日志
#在主服务器上做完全备份,复制到从服务器上 [root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/fullbackup.sql [root@master ~]#scp /data/fullbackup.sql 10.0.0.135:/data The authenticity of host '10.0.0.135 (10.0.0.135)' can't be established. ECDSA key fingerprint is SHA256:E8oPeOCwQOdj6sRDLmQ9KsGivUoLOysv2Xj2/oUP0G4. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added '10.0.0.135' (ECDSA) to the list of known hosts. root@10.0.0.135's password: fullbackup.sql 100% 469KB 33.5MB/s 00:00 #将完全备份还原到新节点 [root@slave ~]#yum -y install mariadb-server [root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=135 read-only [root@slave ~]#systemctl start mariadb.service [root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup.sql CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=389; [root@slave ~]#mysql < /data/fullbackup.sql [root@slave ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 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)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.134', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mariadb-bin.000002', -> MASTER_LOG_POS=389; Query OK, 0 rows affected (0.003 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.134 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 586 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 754 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: 586 Relay_Log_Space: 1065 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: 1 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.001 sec)
2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）
#新找到最新的数据库信息，让它成为新主 [root@centos8 ~]#cat /var/lib/mysql/relay-log.info [root@centos8 ~]#vim /etc/my.cnf.d/server.cnf [mysqld] server-id=135 read-only=off log_bin=/data/mysql/mysql-bin [root@centos8 ~]#systemctl restart mariadb #清除老主复制过来的信息 [root@centos8 ~]#mysql MariaDB [(none)]> set global read_only=off; MariaDB [(none)]> stop slave; MariaDB [(none)]> reset slave all; 再新master上完全备份，并拷贝到其他从服务器 [root@centos8 ~]#mysqldump -A -F --single-transaction --master-data=1 > full_`date +%F`.sql [root@centos8 ~]#scp full_2020-10-18.sql 10.0.0.136: #分析旧的master的二进制日志，找到最新的二进制文件并导出来，恢复到新master 但还是会丢一小部分数据 全凭手速 #为其他所有slave还原数据，配置文件指向新的master [root@centos8 ~]#vim full_2020-10-18.sql CHANGE MASTER TO MASTER_HOST='10.0.0.135', MASTER_USER='users', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=389; [root@centos8 ~]#mysql MariaDB [(none)]> stop slave; MariaDB [(none)]> reset slave all; MariaDB [(none)]> set sql_log_bin=off; MariaDB [(none)]> source full_2020-10-13.sql ; MariaDB [(none)]> set sql_log_bin=on; MariaDB [(none)]> start slave; MariaDB [test]> show slave status\G; #查看新数据库是否完成恢复 [root@centos8 ~]#mysql MariaDB [hellodb]> create table wtf select * from teachers; MariaDB [hellodb]> select * from wtf;
#slave执行
[root@centos8 ~]#mysql
MariaDB [hellodb]> select * from wtf;
3、通过 MHA 0.58 搭建一个数据库集群结构
MHA实现高可用
环境要求： MHA： 10.0.0.7 master：10.0.0.17 slave1:10.0.0.27 slave2:10.0.0.37 关闭防火墙、selinux、时间同步 主从服务器安装mysql5.7.29 在管理节点上安装两个包： Mha4mysql-manger Mha4sql-node 在被管理节点上安装：mha4mysql-node [root@centos7 ~]#yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm [root@centos7 ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm 在所有节点上实现相互之间ssh key验证 [root@centos7 ~]# ssh-keygen [root@centos7 ~]# ssh-copy-id 127.0.0.1 [root@centos7 ~]# rsync -av .ssh 10.0.0.17:/root/ [root@centos7 ~]# rsync -av .ssh 10.0.0.27:/root/ [root@centos7 ~]# rsync -av .ssh 10.0.0.37:/root/ 在管理节点上建立配置文件 [root@centos7 ~]#mkdir -p /etc/mastermha [root@centos7 ~]#vim /etc/mastermha/app1.cnf [server default] # 登陆mysql数据库账户及密码，缺省为root，因为需要STOP SLAVE, CHANGE MASTER, RESET SLAVE等。 user=mhauser #用于远程连接MySQL所有节点的用户,需要有管理员的权限 password=123456 # working directory on the manager #位于管理节点工作目录 manager_workdir=/data/masterha/app1 #目录会自动生成,无需手动创建 # manager log file #位于管理节点工作日志文件 manager_log=/data/mastermha/app1/manager.log # working directory on MySQL servers # node 上用于产生日志的工作目录，如果不存在，MHA node会自动创建，前提需要有相应的权限，否则node会终止。 # 缺省目录为 "/var/tmp". remote_workdir=/data/masterha/app1 ssh_user=root #用于实现远程ssh基于KEY的连接,访问二进制日志 repl_user=repluser #主从复制的用户信息 repl_password=123456 ping_interval=1 #健康性检查的时间间隔 master_ip_failover_script=/usr/local/bin/master_ip_failover.pl #切换VIP的perl脚本 report_script=/usr/local/bin/sendmail.sh #切换VIP的perl脚本 check_repl_delay=0 #默认如果slave中从库落后主库relaylog超过100M，主库不会选择这个从库为新的master，因为这个从库进行恢复需要很长的时间.通过这个参数，mha触发主从切换的时候会忽略复制的延时，通过check_repl_delay=0这个参数，mha触发主从切换时会忽略复制的延时，对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master master_binlog_dir=/data/mysql/ #指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定 #[serverN] 部分，为各节点配置信息，作用域为各单独节点，各节点书写顺序影响成为新master的顺序 #也可以通过配置candidate_master参数来影响哪个节点具有优先级成为新master [server1] hostname=10.0.0.17 candidate_master=1 #设置为优先候选master，即使不是集群中事件最新的slave，也会优先当选master [server2] hostname=10.0.0.27 [server3] hostname=10.0.0.37 candidate_master=1 #设置为优先候选master，即使不是集群中事件最新的slave，也会优先当选master 说明：主库宕机谁接新的master 1.所有从节点都是一致的，默认会配置文件的顺序去选择一个脚本 2.从节点日志不一致，自动选择最接近于主库信息的从库充当主库 3.如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。但是此节点日志量落后主库超过100M日志的话，也不会被选择。可以配合check_repl_delay=0，关闭日志量的检查，强制选择候 选节点 相关脚本 [root@centos7 ~]#cat /usr/local/bin/sendmail.sh echo 'MySQL is down ' |mail -s 'MHA waring' 1655841639@qq.com [root@centos7 ~]#chmod +x /usr/local/bin/sendmail.sh [root@centos7 ~]#vim .mailrc set from=1655841639@qq.com set smtp=smtp.qq.com set smtp-auth-user=1655841639@qq.com set smtp-auth-password=voubowyealfqdeh set smtp-auth=login set ssl-verify=ignore [root@centos7 ~]#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.2';#网关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"; } [root@centos7 ~]#chmod +x /usr/local/bin/master_ip_failover 实现Master [root@centos17 ~]#mkdir /data/mysql/ [root@centos17 ~]#chown mysql.mysql /data/mysql/ [root@centos17 ~]#vim /etc/my.cnf [mysqld] server-id=17 log-bin=/data/mysql/mysql-bin skip_name_resolve=1 general_log datadir=/data/mysql [root@centos17 ~]#mysql -uroot -p mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 8839 | +------------------+-----------+ 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'; #配置VIP [root@centos17 ~]#ifconfig eth0:1 10.0.0.100/24 实现slave [root@centos27 ~]#mkdir /data/mysql [root@centos27 ~]#chown mysql.mysql /data/mysql/ [root@centos27 ~]#vim /etc/my.cnf [mysqld] server-id=27 log-bin=/data/mysql/mysql-bin skip_name_resolve=1 general_log datadir=/data/mysql [root@centos27 ~]#mysql -uroot -p mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.17', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=8839; mysql> start slave ; 检查MHA环境 #检查环境 [root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf All SSH connection tests passed successfully. [root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf MySQL Replication Health is OK. #查看状态 [root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf app1 is stopped(2:NOT_RUNNING). 启动MHA #开启MHA,默认是前台运行 nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null #查看状态 masterha_check_status --conf=/etc/mastermha/app1.cnf app1 (pid:25994) is running(0:PING_OK), master:10.0.0.17 #查看到健康性检查 [root@centos17 ~]#tail -f /var/lib/mysql/centos8.log 排错日志 tail /data/mastermha/app1/manager.log 模拟故障，master：centos17宕机,自动提升1从为主 #主节点：10.0.0.17，查找进程，关闭 [root@centos17 ~]#ps -aux|grep mysql [root@centos17 ~]#kill -9 12658 [root@centos17 ~]# killall -9 mysqld #开启MHA，默认前台执行 [root@centos7 ~]#nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null #查看状态 [root@centos7 ~]masterha_check_status --conf=/etc/mastermha/app1.cnf #在管理节点查看分析错误日志 [root@centos7 ~]#tail -f /data/mastermha/app1/manager.log Started automated(non-interactive) failover. The latest slave 10.0.0.27(10.0.0.27:3306) has all relay logs for recovery. Selected 10.0.0.27(10.0.0.27:3306) as a new master. 10.0.0.27(10.0.0.27:3306): OK: Applying all logs succeeded. 10.0.0.37(10.0.0.37:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 10.0.0.37(10.0.0.37:3306): OK: Applying all logs succeeded. Slave started, replicating from 10.0.0.27(10.0.0.27:3306) 10.0.0.27(10.0.0.27:3306): Resetting slave info succeeded. Master failover to 10.0.0.27(10.0.0.27:3306) completed successfully. [root@centos 17 ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf app1 is stopped(2:NOT_RUNNING). #验证VIP漂移至新的Master上 [root@slave1 ~]#ip a 收到报警邮件
如果再次运行MHA，需要删除下面文件
[root@centos7 ~]# ll /data/mastermha/app1/app1.failover.complete -rw-r--r-- 1 root root 0 Oct 17 10:19 /data/mastermha/app1/app1.failover.complete [root@centos7 ~]#rm -rf /data/mastermha/app1/app1.failover.complete
转自https://www.cnblogs.com/weiweirui/p/13836282.html
4、实战案例：Percona XtraDB Cluster(PXC 5.7） Galera Cluster配置文件简介👇
#配置pxc清华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.135:/etc/yum.repos.d/ [root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.136:/etc/yum.repos.d/ [root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.137:/etc/yum.repos.d/ [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 [root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y #配置各个节点mysql和集群配置文件 [root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf [mysqld] server-id=134 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@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf [mysqld] server-id=135 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@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf [mysqld] server-id=136 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/mysqld.cnf [mysqld] server-id=137 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@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc1 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.135,10.0.0.136,10.0.0.137 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.135 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@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc2 ~]# [root@pxc2 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.135,10.0.0.136,10.0.0.137 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.136 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@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc3 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.135,10.0.0.136,10.0.0.137 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.137 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" #启动PXC集群中第一个节点 [root@pxc1 ~]#systemctl start mysql@bootstrap.service [root@pxc1 ~]#ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 *:4567 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 100 [::1]:25 [::]:* LISTEN 0 80 [::]:3306 [::]:* [root@pxc1 ~]#grep "temporary password" /var/log/mysqld.log 2020-10-18T16:44:53.765387Z 1 [Note] A temporary password is generated for root@localhost: TBE8Nne+9RGE [root@pxc1 ~]#mysql -uroot -pTBE8Nne+9RGE 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> alter user root@'localhost' identified by 'magedu'; Query OK, 0 rows affected (0.00 sec) mysql> grant reload,lock tables,process,replication client on *.* to sstuser@'localhost' identified by 's3cretPass'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show variables like 'wsrep%'\G *************************** 1. row *************************** Variable_name: wsrep_OSU_method Value: TOI *************************** 2. row *************************** Variable_name: wsrep_RSU_commit_timeout Value: 5000 *************************** 3. row *************************** Variable_name: wsrep_auto_increment_control Value: ON *************************** 4. row *************************** Variable_name: wsrep_causal_reads Value: OFF *************************** 5. row *************************** Variable_name: wsrep_certification_rules Value: strict *************************** 6. row *************************** Variable_name: wsrep_certify_nonPK Value: ON *************************** 7. row *************************** Variable_name: wsrep_cluster_address Value: gcomm://10.0.0.7,10.0.0.17,10.0.0.27 *************************** 8. row *************************** Variable_name: wsrep_cluster_name Value: pxc-cluster *************************** 9. row *************************** Variable_name: wsrep_convert_LOCK_to_trx Value: OFF *************************** 10. row *************************** Variable_name: wsrep_data_home_dir Value: /var/lib/mysql/ *************************** 11. row *************************** Variable_name: wsrep_dbug_option Value: *************************** 12. row *************************** Variable_name: wsrep_debug Value: OFF *************************** 13. row *************************** Variable_name: wsrep_desync Value: OFF *************************** 14. row *************************** Variable_name: wsrep_dirty_reads Value: OFF *************************** 15. row *************************** Variable_name: wsrep_drupal_282555_workaround Value: OFF *************************** 16. row *************************** Variable_name: wsrep_forced_binlog_format Value: NONE *************************** 17. row *************************** Variable_name: wsrep_load_data_splitting Value: ON *************************** 18. row *************************** Variable_name: wsrep_log_conflicts Value: ON *************************** 19. row *************************** Variable_name: wsrep_max_ws_rows Value: 0 *************************** 20. row *************************** Variable_name: wsrep_max_ws_size Value: 2147483647 *************************** 21. row *************************** Variable_name: wsrep_node_address Value: 10.0.0.7 *************************** 22. row *************************** Variable_name: wsrep_node_incoming_address Value: AUTO *************************** 23. row *************************** Variable_name: wsrep_node_name Value: pxc-cluster-node-1 *************************** 24. row *************************** Variable_name: wsrep_notify_cmd Value: *************************** 25. row *************************** Variable_name: wsrep_on Value: ON *************************** 26. row *************************** Variable_name: wsrep_preordered Value: OFF *************************** 27. row *************************** Variable_name: wsrep_provider Value: /usr/lib64/galera3/libgalera_smm.so *************************** 28. row *************************** Variable_name: wsrep_provider_options Value: base_dir = /var/lib/mysql/; base_host = 10.0.0.134; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs *************************** 29. row *************************** Variable_name: wsrep_recover Value: OFF *************************** 30. row *************************** Variable_name: wsrep_reject_queries Value: NONE *************************** 31. row *************************** Variable_name: wsrep_replicate_myisam Value: OFF *************************** 32. row *************************** Variable_name: wsrep_restart_slave Value: OFF *************************** 33. row *************************** Variable_name: wsrep_retry_autocommit Value: 1 *************************** 34. row *************************** Variable_name: wsrep_slave_FK_checks Value: ON *************************** 35. row *************************** Variable_name: wsrep_slave_UK_checks Value: OFF *************************** 36. row *************************** Variable_name: wsrep_slave_threads Value: 8 *************************** 37. row *************************** Variable_name: wsrep_sst_auth Value: ******** *************************** 38. row *************************** Variable_name: wsrep_sst_donor Value: *************************** 39. row *************************** Variable_name: wsrep_sst_donor_rejects_queries Value: OFF *************************** 40. row *************************** Variable_name: wsrep_sst_method Value: xtrabackup-v2 *************************** 41. row *************************** Variable_name: wsrep_sst_receive_address Value: AUTO *************************** 42. row *************************** Variable_name: wsrep_start_position Value: 00000000-0000-0000-0000-000000000000:-1 *************************** 43. row *************************** Variable_name: wsrep_sync_wait Value: 0 43 rows in set (0.00 sec) mysql> show status like 'wsrep%'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | wsrep_local_state_uuid | 6085b686-1046-11eb-ac84-631f2cc73305 | | wsrep_protocol_version | 9 | | wsrep_last_applied | 3 | | wsrep_last_committed | 3 | | wsrep_replicated | 3 | | wsrep_replicated_bytes | 816 | | wsrep_repl_keys | 3 | | wsrep_repl_keys_bytes | 96 | | wsrep_repl_data_bytes | 521 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 2 | | wsrep_received_bytes | 150 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.500000 | | wsrep_local_cached_downto | 1 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_interval | [ 100, 100 ] | | wsrep_flow_control_interval_low | 100 | | wsrep_flow_control_interval_high | 100 | | wsrep_flow_control_status | OFF | | wsrep_cert_deps_distance | 1.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 1.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 1.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 1 | | wsrep_cert_bucket_count | 22 | | wsrep_gcache_pool_size | 2256 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_open_transactions | 0 | | wsrep_open_connections | 0 | | wsrep_ist_receive_status | | | wsrep_ist_receive_seqno_start | 0 | | wsrep_ist_receive_seqno_current | 0 | | wsrep_ist_receive_seqno_end | 0 | | wsrep_incoming_addresses | 10.0.0.134:3306 | | wsrep_cluster_weight | 1 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | 60853201-1046-11eb-b428-d757c0543d63 | | wsrep_cluster_conf_id | 1 | | wsrep_cluster_size | 1 | | wsrep_cluster_state_uuid | 6085b686-1046-11eb-ac84-631f2cc73305 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 0 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 3.45(ra60e019) | | wsrep_ready | ON | +----------------------------------+--------------------------------------+ 71 rows in set (0.00 sec) #启动PXC集群中其它所有节点 [root@pxc2 ~]#systemctl start mysql [root@pxc2 ~]#ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 *:4567 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 80 [::]:3306 [::]:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 100 [::1]:25 [::]:* [root@pxc3 ~]#systemctl start mysql [root@pxc3 ~]#ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 *:4567 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 80 [::]:3306 [::]:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 100 [::1]:25 [::]:* #查看集群状态，验证集群是否成功 [root@pxc1 ~]#mysql -uroot -pmagedu mysql> show variables like 'wsrep_node_name'; +-----------------+--------------------+ | Variable_name | Value | +-----------------+--------------------+ | wsrep_node_name | pxc-cluster-node-1 | +-----------------+--------------------+ 1 row in set (0.00 sec) mysql> show variables like 'wsrep_node_address'; +--------------------+------------+ | Variable_name | Value | +--------------------+------------+ | wsrep_node_address | 10.0.0.134 | +--------------------+------------+ 1 row in set (0.00 sec) mysql> show variables like 'wsrep_on'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wsrep_on | ON | +---------------+-------+ 1 row in set (0.01 sec) mysql> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec) [root@pxc2 ~]#mysql -uroot -pmagedu 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 14 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 | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) [root@pxc3 ~]#mysql -uroot -pmagedu 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 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> create database db1; Query OK, 1 row affected (0.00 sec) [root@pxc1 ~]#mysql -uroot -pmagedu 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 16 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 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) ##在PXC集群中再加一台新主机PXC4：10.0.0.137 [root@pxc4 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc4 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.134,10.0.0.135,10.0.0.136,10.0.0.137 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.137 wsrep_cluster_name=pxc-cluster wsrep_node_name=pxc-cluster-node-4 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" [root@pxc4 ~]#systemctl start mysql [root@pxc4 ~]#mysql -uroot -pmagedu 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 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 status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 4 | +--------------------+-------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) #修改其他节点的配置文件 [root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc1 ~]#grep -E "^wsrep_cluster_address=gcomm:" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.134,10.0.0.135,10.0.0.136,10.0.0.137 [root@pxc2 ~]#grep -E "^wsrep_cluster_address=gcomm:" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.134,10.0.0.135,10.0.0.136,10.0.0.137 [root@pxc3 ~]#grep -E "^wsrep_cluster_address=gcomm:" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.134,10.0.0.135,10.0.0.136,10.0.0.137
5、通过 ansible 部署二进制 mysql 8
@1准备MySQL8.0的二进制安装包和配置文件
[root@ansible files]# ls /data/ansible/files/ mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
[root@ansible files]# 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=/var/log/mysqld.log
@2写yml文件
[root@ansible ansible]# vim /data/ansible/install_mysql.yml --- #install mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz - hosts: websrvs gather_facts: false remote_user: root tasks: - name: install packages yum: name=libaio,perl-Data-Dumper,perl-Getopt-Long,libtinfo.so.5 state=present - name: create mysql group group: name=mysql gid=306 - name: create mysql user user: name=mysql uid=306 group=mysql shell=/sbin/nologin system=yes create_home=no home=/data/mysql - name: copy tar to remote host and file mode unarchive: src=/data/ansible/files/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz dest=/usr/local owner=root group=root - name: create linkfile /usr/local/mysql file: src=/usr/local/mysql-8.0.19-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link - name: data dir shell: chdir=/usr/local/mysql /usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --initialize-insecure tags: data - name: config my.cnf copy: src=/data/ansible/files/my.cnf dest=/etc/my.cnf - name: service scriot shell: /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql - name: enable service shell: /etc/init.d/mysql start;chkconfig --add mysql;chkconfig mysql on tags: service - name: PATH variable copy: content='PATH=/usr/local/mysql/bin:$PATH' dest=/etc/profile.d/mysql.sh - name: load cache shell: source /etc/profile
@3这是准备好的两个文件 #本来还有安全加固脚本 后来装不上就删了 可能我脑抽👍
[root@ansible ansible]# tree . ├── create_mysql.yml ├── files │ ├── my.cnf │ ├── mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz │ └── secure_mysql.sh └── install_mysql.yml 1 directory, 5 files
@4执行
[root@ansible ansible]# ansible-playbook install_mysql.yml PLAY [websrvs] ************************************************************************************************************ TASK [install packages] *************************************************************************************************** changed: [10.0.0.135] TASK [create mysql group] ************************************************************************************************* changed: [10.0.0.135] TASK [create mysql user] ************************************************************************************************** changed: [10.0.0.135] TASK [copy tar to remote host and file mode] ****************************************************************************** changed: [10.0.0.135] TASK [create linkfile /usr/local/mysql] *********************************************************************************** changed: [10.0.0.135] TASK [data dir] *********************************************************************************************************** changed: [10.0.0.135] TASK [config my.cnf] ****************************************************************************************************** changed: [10.0.0.135] TASK [service scriot] ***************************************************************************************************** changed: [10.0.0.135] TASK [enable service] ***************************************************************************************************** changed: [10.0.0.135] TASK [PATH variable] ****************************************************************************************************** changed: [10.0.0.135] TASK [load cache] ********************************************************************************************************* changed: [10.0.0.135] PLAY RECAP **************************************************************************************************************** 10.0.0.135 : ok=11 changed=11 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
@5切换到10.0.0.135 不好使就重启一下
[root@centos8 ~]# systemctl status mysql ● mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql; generated) Active: inactive (dead) Docs: man:systemd-sysv-generator(8) [root@centos8 ~]# systemctl start mysql [root@centos8 ~]# systemctl status mysql ● mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql; generated) Active: active (exited) since Sat 2020-10-17 19:40:52 CST; 2s ago Docs: man:systemd-sysv-generator(8) Process: 6031 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS) Oct 17 19:40:52 centos8.2 systemd[1]: Starting LSB: start and stop MySQL... Oct 17 19:40:52 centos8.2 mysql[6031]: Starting MySQL SUCCESS! Oct 17 19:40:52 centos8.2 systemd[1]: Started LSB: start and stop MySQL. Oct 17 19:40:52 centos8.2 mysql[6031]: 2020-10-17T11:40:52.762187Z mysqld_safe A mysqld process already exists
开启的时候报错了 找不到libtinfo.so.5 那就用libtinfo.so.6.1代替一下
[root@centos8 ~]# mysql -uroot mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
对策👇
[root@centos8 ~]# ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
[root@centos8 ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 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>
这不就起来了 简简单单
如果还有其他问题 请咨询百度 🤞
