作业（五）
1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）
#通过备份恢复至从服务器，复制起始位置为备份时，二进制日志文件及其POS
#在主服务器完全备份
[root@master ~]# mkdir backup
[root@master ~]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 > backup/fullbackup_`date +%F_%T`.sql
[root@master ~]# scp backup/fullbackup_2020-10-16_19\:37\:35.sql 10.0.0.61:/data/
#配置从节点，从完全备份的位置之后开始复制
[root@slave1 ~]# vim /data/fullbackup_2020-10-16_19\:37\:35.sql
CHANGE MASTER TO
master_host='10.0.0.60',
master_user='repluser',
master_password='123456',
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
[root@slave1 ~]# mysql -uroot -p123456
mysql> start slave;
2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）
#找到哪个从节点的数据库是最新，让它成为新master
[root@master ~]# cat /var/lib/mysql/relay-log.info
./relay-log.000004
1017
mariadb-bin.000002
731
#新master修改配置文件，关闭read-only配置
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server-id=60
log_bin
read_only=OFF
#清除旧的master复制信息
mysql> set global read_only=off;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.02 sec)
#在新master上完全备份
[root@slave1 ~]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 > backup.sql
[root@slave1 ~]# scp backup.sql 10.0.0.61:
#分析旧的master的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master，尽可能恢复数据
#其他所有slave重新还原数据库，指向新的master
[root@slave2 ~]# vim backup.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.60',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=245;
mysql> stop slave;
mysql> reset slave all;
mysql> set sql_log_bin=off;
mysql> source backup.sql;
mysql> set sql_log_bin=on;
mysql> start slave;
3、通过 MHA 0.58 搭建一个数据库集群结构
#四台主机
10.0.0.58 centos7 MHA管理端
10.0.0.60 centos7 master
10.0.0.61 centos7 slave1
10.0.0.62 centos7 slave2
#先装node包再装manager包
[root@mha-manager ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mha-manager ~]# yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
#在所有mysql服务器上安装mha4mysql-node包
[root@master ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave1 ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave2 ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#在所有节点实现相互之间ssh key验证
[root@mha-manager ~]# ssh-keygen
[root@mha-manager ~]# ssh-copy-id 127.0.0.1
[root@mha-manager ~]# rsync -av .ssh 10.0.0.60:/root/
[root@mha-manager ~]# rsync -av .ssh 10.0.0.61:/root/
[root@mha-manager ~]# rsync -av .ssh 10.0.0.62:/root/
#在管理节点建立配置文件
[root@mha-manager ~]# mkdir /etc/mastermha
[root@mha-manager ~]# vim /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=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.60
[server2]
hostname=10.0.0.61
candidate_master=1
[server3]
hostname=10.0.0.62
#相关脚本
[root@mha-manager etc]# cat /usr/local/bin/sendmail.sh
echo "MySQL is down" | mail -s "MHA Warning" 429095598@qq.com
[root@mha-manager etc]# chmod +x /usr/local/bin/sendmail.sh
[root@mha-manager etc]# vim /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";
}
[root@mha-manager ~]# chmod +x /usr/local/bin/master_ip_failover
#实现master
[root@master ~]# mkdir -p /data/mysql
[root@master ~]# chown mysql.mysql /data/mysql
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=60
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1
general_log
[root@master ~]# mysql -uroot -p123456
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 461 |
| mysql-bin.000003 | 448 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to mhauser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#配置VIP
[root@master ~]# ifconfig eth0:1 10.0.0.100/24
#实现slave
[root@slave1 ~]# mkdir -p /data/mysql
[root@slave1 ~]# chown mysql.mysql /data/mysql/
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server_id=61
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1
[root@slave1 ~]# mysql -uroot -p123456
mysql> change master to
-> master_host='10.0.0.60',
-> master_user='repluser',
-> master_password='123456',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=448;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#检查MHA的环境
[root@mha-manager ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@mha-manager ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
[root@mha-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
#开启MHA,默认是前台运行
[root@mha-manager ~]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null
#查看状态
[root@mha-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
4、实战案例：Percona XtraDB Cluster(PXC 5.7）
#环境准备
#四台主机：
pxc1:10.0.0.58
pxc2:10.0.0.59
pxc3:10.0.0.60
#关闭防火墙和SELinux，保证时间同步
#如果已经安装mysql，必须卸载
#配置pxc的yum源
[root@pxc1 ~]# vim /etc/yum.repos.d/pxc.repo
[percona]name=percona_repobaseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearchenabled = 1
gpgcheck = 0
[root@pxc1 ~]# scp /etc/yum.repos.d/pxc.repo 10.0.0.59:/etc/yum.repos.d/
[root@pxc1 ~]# scp /etc/yum.repos.d/pxc.repo 10.0.0.60:/etc/yum.repos.d/
#在三个节点都安装好PXC 5.7
[root@pxc1 ~]# yum -y install Percona-XtraDB-Cluster-57
[root@pxc2 ~]# yum -y install Percona-XtraDB-Cluster-57
[root@pxc3 ~]# yum -y install Percona-XtraDB-Cluster-57
#配置文件/etc/percona-xtradb-cluster.conf.d/mysqld.cnf 节点号修改
[root@pxc1 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=58 #各个节点不同
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
#PXC的配置文件修改
[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.58,10.0.0.59,10.0.0.60 #三个节点的IP
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.58 #各个节点指定自己的IP
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 ~]# 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.58,10.0.0.59,10.0.0.60 #三个节点的IP
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.59 #各个节点指定自己的IP
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 ~]# 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.58,10.0.0.59,10.0.0.60 #三个节点的IP
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.60 #各个节点指定自己的IP
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" #取消本行注释
#启动第一个节点
[root@pxc1 ~]# ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
udp UNCONN 0 0 127.0.0.1:323 *:*
udp UNCONN 0 0 [::1]:323 [::]:*
tcp LISTEN 0 128 *:22 *:*
tcp LISTEN 0 100 127.0.0.1:25 *:*
tcp LISTEN 0 128 [::]:22 [::]:*
tcp LISTEN 0 100 [::1]:25 [::]:*
[root@pxc1 ~]# systemctl start mysql@bootstrap.service
[root@pxc1 ~]# ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
udp UNCONN 0 0 127.0.0.1:323 *:*
udp UNCONN 0 0 [::1]:323 [::]:*
tcp LISTEN 0 128 *:22 *:*
tcp LISTEN 0 128 *:4567 *:*
tcp LISTEN 0 100 127.0.0.1:25 *:*
tcp LISTEN 0 80 [::]:3306 [::]:*
tcp LISTEN 0 128 [::]:22 [::]:*
tcp LISTEN 0 100 [::1]:25 [::]:*
#查看root密码
[root@pxc1 ~]# grep "temporary password" /var/log/mysqld.log
2020-10-17T02:33:26.079985Z 1 [Note] A temporary password is generated for root@localhost: 0,&B*je=B*Mp
[root@pxc1 ~]# mysql -uroot -p'0,&B*je=B*Mp'
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
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 '123456'; #修改root密码
Query OK, 0 rows affected (0.01 sec)
#创建相关用户并授权
mysql> create user 'sstuser'@'localhost' identified by 's3cretPass';
Query OK, 0 rows affected (0.01 sec)
mysql> grant reload,lock tables,process,replication client on *.* to 'sstuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
#启动PXC其他所有节点
[root@pxc2 ~]# systemctl start mysql
[root@pxc3 ~]# systemctl start mysql
#在任意节点查看集群状态
[root@pxc1 ~]# 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 15
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 variables like 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name | Value |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-1 |
+-----------------+--------------------+
1 row in set (0.01 sec)
mysql> show variables like 'wsrep_node_address';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| wsrep_node_address | 10.0.0.58 |
+--------------------+-----------+
1 row in set (0.01 sec)
mysql> show variables like 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)
5、通过 ansible 部署二进制 mysql 8
#准备两台机器
ansible：10.0.0.58
mysql：10.0.0.59
#配置epel源
#安装ansible
[root@ansible ~]# yum -y install ansible
[root@ansible ~]# vim /etc/ansible/hosts
[dbsrvs]
10.0.0.59
#准备二进制文件，配置文件
[root@ansible ~]# ll /data/ansible/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
-rw-r--r-- 1 root root 485074552 Oct 17 12:06 /data/ansible/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
[root@ansible ~]# cat /data/ansible/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
log_bin=/data/mysqlbinlog/mysql-bin
[client]
socket=/data/mysql/mysql.sock
#基于key验证
[root@ansible ~]# ssh-keygen
[root@ansible ~]# scp -r .ssh 10.0.0.59:/root/
#安装mysql相关模块文件
[root@ansible ~]# ansible dbsrvs -m yum -a 'name=libaio,numactl-libs state=present'
#创建组和用户
[root@ansible ~]# ansible dbsrvs -m group -a 'name=mysql gid=66 system=yes'
10.0.0.59 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": true,
"gid": 66,
"name": "mysql",
"state": "present",
"system": true
}
[root@ansible ~]# ansible dbsrvs -m user -a 'name=mysql uid=66 group=mysql shell=/sbin/nologin system=yes create_home=no home=/data/mysql'
10.0.0.59 | CHANGED => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": true,
"comment": "",
"create_home": false,
"group": 66,
"home": "/data/mysql",
"name": "mysql",
"shell": "/sbin/nologin",
"state": "present",
"system": true,
"uid": 66
}
#二进制包解压到mysql主机上
[root@ansible ~]# ansible dbsrvs -m unarchive -a 'src=/data/ansible/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz dest=/usr/local owner=root group=root'
#创建软连接
[root@ansible ~]# ansible dbsrvs -m shell -a 'echo "PATH=/usr/local/mysql/bin:\$PATH" > /etc/profile.d/mysql.sh'
[root@ansible ~]# ansible dbsrvs -m shell -a '. /etc/profile.d/mysql.sh'
#拷贝配置文件到mysql机器上
[root@ansible ~]# ansible dbsrvs -m copy -a 'src=/data/ansible/my.cnf dest=/etc/my.cnf'
#生成数据库文件
[root@ansible ~]# ansible dbsrvs -m shell -a 'mysqld --initialize --user=mysql --datadir=/data/mysql'
#启动脚本复制到系统文件里
[root@ansible ~]# ansible dbsrvs -m shell -a 'cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld'
#使用shell脚本更改密码
[root@ansible ~]# ansible dbsrvs -m script -a dbsrvs.sh
[root@ansible ~]# cat dbsrvs.sh
passwd=`awk '/temporary password/{print $NF}' /data/mysql/mysql.log`
mysqladmin -uroot -p"${passwd}" password "123456"
972
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
