1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）
重启数据库
如果从节点开启了二进制日志，需要关闭后再还原
启用从节点
- 3、通过 MHA 0.58 搭建一个数据库集群结构
!/usr/bin/env perl
$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.
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.
A simple system call that enable the VIP on the new master
A simple system call that disable the VIP on the old_master
最后显示ok即可
查看状态
- 4、实战案例：Percona XtraDB Cluster(PXC 5.7）
推荐使用国内yum源，官方源速度慢
启动第一个节点
启动完成之后登录mysql
修改root密码
创建相关用户并授权
查看当前节点数
查看当前节点ip
在任意节点创建数据库

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

配置从节点 ```bash vim /etc/my.cnf [mysqld] server-id=# （配置一个唯一的id） read-only

重启数据库

systemctl restart mariadb


2. 完全备份主节点数据库并查看当前二进制日志的位置，创建复制账号
```bash
#进入数据库查看master二进制日志位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28224 |
| mariadb-bin.000002 |      1101 |
| mariadb-bin.000003 |       389 |
| mariadb-bin.000004 |       344 |
+--------------------+-----------+
4 rows in set (0.000 sec)
#创建复制账号
MariaDB [(none)]>grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456'
#完全备份
mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql

将完全备份传到从节点服务器上,进行还原 ```bash scp /root/all.sql 从节点ip:/data/

如果从节点开启了二进制日志，需要关闭后再还原

MariaDB [(none)]> set sql_log_bin=off; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> source /data/all.sql Query OK, 0 rows affected (0.000 sec) MariaDB [hellodb]> set sql_log_bin=off; Query OK, 0 rows affected (0.000 sec)

启用从节点

MariaDB [hellodb]> CHANGE MASTER TO -> MASTER_HOST=’10.0.0.8’, -> MASTER_USER=’repluser’, -> MASTER_PASSWORD=’123456’, -> MASTER_PORT=3306, -> MASTER_LOG_FILE=’mariadb-bin.000004’, -> MASTER_LOG_POS=344; MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status\G; * 1. row * Slave_IO_State: Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 344 Relay_Log_File: mariadb-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mariadb-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes

<a name="VLhrU"></a>
## 2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）
1. 找出从节点数据库最新的那个成为新的master。（为了尽可能减少数据损失，越新说明数据缺失的越少）
1. 修改新的master配置并备份数据库
```bash
vim /etc/my.cnf
[mysqld]
server-id=28
read-only=off {关闭只读}
log-bin
#清除新master上旧信息
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
#备份新master的数据（完全备份）
mysqldump -A -F --single-transaction --master-data=1  > /data/allbackup.sql
#用scp发送到其他slave
scp /data/allbackup.sql 从节点ip:/data/

其他slave同步数据，并指向新master

#在其他从节点上同步数据
MariaDB [hellodb]>CHANGE MASTER TO
MASTER_HOST='10.0.0.28',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000005', 
MASTER_LOG_POS=371;
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
MariaDB [hellodb]>set sql_log_bin=off;
MariaDB [hellodb]>source backup.sql;
MariaDB [hellodb]>set sql_log_bin=on;
MariaDB [hellodb]>start slave;

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

四台主机

10.0.1.17 MHA管理端
10.0.1.27 master
10.0.1.37 slave1
10.0.1.47 slave2
在管理端上安装两个包mha4mysql-manager和mha4mysql-node
其他节点安装mha4mysql-node
所有服务器上安装mysql5.7版本

所有节点之间实现相互之间的key验证 ```bash [root@17 ~]#ssh-keygen [root@17 ~]#ssh-copy-id 127.0.0.1 [root@17 ~]#rsync -av .ssh 10.0.1.27: [root@17 ~]#rsync -av .ssh 10.0.1.37: [root@17 ~]#rsync -av .ssh 10.0.1.47:


3. **在17管理节点上修改配置文件**
```bash
[root@17 ~]# 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=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/mysqllogbin/
[server1]
hostname=10.0.1.27
candidate_master=1
[server2]
hostname=10.0.1.37
[server3]
hostname=10.0.1.47
candidate_master=1
[root@17 ~]#

设置报警邮件

[root@17 ~]# cat /usr/local/bin/sendmail.sh
echo "MySQL is down" | mail -s "MHA Warning" 2234734448@qq.com
[root@17 ~]# chmod +x /usr/local/bin/sendmail.sh
[root@17 ~]# cat 1.txt 
set from=2234734448@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=2234734448@qq.com
set smtp-auth-password=ovvksvlrohrxdidb
[root@17 ~]# cat 1.txt >> /etc/mail.rc

编辑切换VIP的perl脚本 ```bash

!/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.1.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@17 ~]#chmod +x /usr/local/bin/master_ip_failover


6. 配置主节点
```bash
#创建二进制文件目录
[root@27 ~]# mkdir /data/mysqllogbin
[root@27 ~]# chown mysql.mysql /data/mysqllogbin/
[root@27 ~]# vim /etc/my.cnf
[mysqld]
server-id=27
log-bin=/data/mysqllogbin/mysql-bin
general_log
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock        
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@27 ~]# mysql -uroot -p123456
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.1.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to mhauser@'10.0.1.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#配置vip
[root@27 ~]#ifconfig eth0:1 10.0.1.100/8

配置其他从节点 ```bash [root@37 ~]# mkdir /data/mysqllogbin [root@37 ~]# chown mysql.mysql /data/mysqllogbin [root@37 ~]# vim /etc/my.cnf [mysqld] server-id=37 read-only log-bin=/data/mysqllogbin/mysql-bin relay_log_purge=0 datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid

[client] socket=/data/mysql/mysql.sock

[root@37 ~]# service mysqld restart [root@37 ~]# mysql -uroot -p123456 mysql> CHANGE MASTER TO -> MASTER_HOST=’10.0.1.27’, -> MASTER_USER=’repluser’, -> MASTER_PASSWORD=’123456’, -> MASTER_PORT=3306, -> 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)

```bash
[root@47 ~]# mkdir /data/mysqllogbin
[root@47 ~]# chown mysql.mysql /data/mysqllogbin
[root@47 ~]# vim /etc/my.cnf
[mysqld]
server-id=47
read-only
log-bin=/data/mysqllogbin/mysql-bin
relay_log_purge=0
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock        
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@47 ~]# service mysqld restart
[root@47 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.1.27',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   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)

检查mha配置文件是否正确 ```bash [root@17 ~]# masterha_check_ssh —conf=/etc/mastermha/app1.cnf [root@17 ~]# masterha_check_repl —conf=/etc/mastermha/app1.cnf

最后显示ok即可

查看状态

masterha_check_status —conf=/etc/mastermha/app1.cnf


9. 启动MHA
```bash
masterha_manager --conf=/etc/mastermha/app1.cnf

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

要求：
- 目前不支持centos8，所以在centos7上做实验
- 关闭防火墙和selinux
- 时间同步
准备至少3台主机

10.0.1.17
10.0.1.27
10.0.1.37

在三台服务器上面安装Percona XtraDB Cluster(PXC 5.7） ```bash
推荐使用国内yum源，官方源速度慢
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

yum install -y Percona-XtraDB-Cluster-57

注意：3台服务器都要进行此操作
4. **在10.0.1.17节点上修改配置文件**
```bash
#主配置文件不需要修改
#mysqld.cnf文件只需要添加一项
server-id=17
#mysqld_safe.cnf不需要修改
vim etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.1.17,10.0.1.27,10.0.1.37
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.1.17
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="yanlei:123456" #注意创建pxc账户时修改了密码或账户，这里要同步修改，否则无法启动

在10.0.1.27节点上修改配置文件 ```bash vim etc/percona-xtradb-cluster.conf.d/mysqld.cnf server-id=27

vim etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.1.17,10.0.1.27,10.0.1.37 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=10.0.1.27 wsrep_cluster_name=pxc-cluster wsrep_node_name=pxc-cluster-node-2 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=”yanlei:123456”


6. **在10.0.1.37节点上修改配置文件**
```bash
vim etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server-id=37
vim etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.1.17,10.0.1.27,10.0.1.37
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.1.37
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-3
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="yanlei:123456"

在17节点上操作 ```bash
启动第一个节点
systemctl start mysql@bootstrap.service

启动完成之后登录mysql

[root@17 ~]# grep “temporary password” /var/log/mysqld.log 2020-10-16T12:15:32.390698Z 1 [Note] A temporary password is generated for root@localhost: BIlp?OGk?7ds [root@17 ~]# mysql -uroot -pBIlp?OGk?7ds

修改root密码

mysql> alter user ‘root’@’localhost’ identified by ‘123456’; Query OK, 0 rows affected (0.01 sec)

创建相关用户并授权

mysql> CREATE USER ‘yanlei’@’localhost’ IDENTIFIED BY ‘123456’; Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO ‘yanlei’@’localhost’; Query OK, 0 rows affected (0.01 sec)

查看当前节点数

查看当前节点ip

mysql> SHOW VARIABLES LIKE ‘wsrep_node_address’; +——————————+—————+ | Variable_name | Value | +——————————+—————+ | wsrep_node_address | 10.0.1.17 | +——————————+—————+


8. 启动其他节点再次查看
```bash
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.01 sec)
#启动成功

验证 ```bash
在任意节点创建数据库
mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> show databases; +——————————+ | Database | +——————————+ | information_schema | | mysql | | performance_schema | | sys | | testdb1 | +——————————+ 5 rows in set (0.00 sec)

<a name="YGlQz"></a>
## 5、通过 ansible 部署二进制 mysql 8
```bash
---
- hosts: 10.0.1.17
  remote_user: root
  gather_facts: no
  tasks:
    - name: create group
      group: name=mysql gid=88 system=yes
    - name: create user
      user: name=mysql uid=88 system=yes group=mysql shell=/sbin/nologin home=/data/mysql create_home=no
    - name: mkdir datadir
      file: name=/data/mysql owner=mysql group=mysql state=directory
    - name: unarchive package
      unarchive: src=/data/ansible/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz dest=/usr/local/ owner=root group=root
    - name: link
      file: src=/usr/local/mysql-8.0.19-linux-glibc2.12-x86_64 path=/usr/local/mysql state=link
    - name: path
      shell: chdir=/etc/profile.d echo 'PATH=/usr/local/mysql/bin:$PATH' > mysql.sh
    - name: copy my.cnf
      copy: src=/data/ansible/my.cnf dest=/etc/my.cnf
    - name: install mysql
      shell: chdir=/usr/local/mysql mysqld --initialize --user=mysql --datadir=/data/mysql
    - name: mv mysqld
      shell: /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
    - name: enable service
      shell: /etc/init.d/mysqld start;chkconfig --add mysqld;chkconfig mysqld on

Linux

第十二周作业

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

重启数据库

如果从节点开启了二进制日志，需要关闭后再还原

启用从节点

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

!/usr/bin/env perl

$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.

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.

A simple system call that enable the VIP on the new master

A simple system call that disable the VIP on the old_master