MariaDB的备份与主从、高可用实践
qiuhom 人气:21、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。
[root@test-centos7-node1 scripts]# cat chose_backup_mysql.sh
#!/bin/bash
#
#************************************************************************
#Author: qiuhom
#QQ: 467697313
#mail: qiuhom467697313@qq.com
#Date: 2020-01-12
#FileName： chose_backup_mysql.sh
#URL: https://www.cnblogs.com/qiuhom-1874/
#Description：
#Copyright (C): 2020 All rights reserved
#************************************************************************
[ -f /etc/init.d/functions ] && . /etc/init.d/functions
fun_mysqldump(){
if `which mysqldump &> https://img.qb5200.com/download-x/dev/null` ;then
mysqldump_cmd=`which mysqldump`
else
yum_cmd_path=`which yum`
$yum_cmd_path install mariadb -y &> https://img.qb5200.com/download-x/dev/null
[ $? -eq 0 ] && echo "mysqldump is installed ,please reselect " && exit 1
fi
if [ "$passwd" != "null" ];then
${mysqldump_cmd} -u$user -p$passwd -h$host -A --compact > $backup_file
else
${mysqldump_cmd} -u$user -h$host -A --compact > $backup_file
fi
[ $? -eq 0 ] && action " backup successful ,please cat $backup_file" /bin/true || action "mariadb backup is failed " /bin/false
exit 0
}
try_connection_mariadb(){
if [ "$2" != "null" ];then
if `mysql -u"$1" -p"$2" -h"$3" -e "show databases" &> https://img.qb5200.com/download-x/dev/null` ;then
user=$1
passwd=$2
host=$3
else
echo "connection is lose , please check user or passwd or host " && exit 3
fi
else
if `mysql -u"$1" -h"$3" -e "show databases" &> https://img.qb5200.com/download-x/dev/null` ;then
user=$1
host=$3
else
echo "connection is lose , please check user or host " && exit 3
fi
fi
}
check_backup_path(){
[ $# -eq 0 ] && backup_file=""
if [[ $1 =~ ^(\/.*\/)$ ]];then
[ ! -e $1 ] && mkdir -p $1
backup_file="${1}all_backup.sql"
elif [[ $1 =~ ^([^\/].*\/)$ ]];then
backup_path="${PWD}/$1"
[ ! -e ${backup_path} ] && mkdir -p ${backup_path}
backup_file="${backup_path}all_backup.sql"
elif [[ $1 =~ ^(\/.*[^\/]$) ]];then
dir=`dirname $1`
[ ! -e "$dir" ] && mkdir -p $dir
backup_file="$1"
elif [[ $1 =~ ^([^\/].*[^\/]$) ]];then
backup_path="${PWD}/$1"
[ ! -e `dirname ${backup_path}` ] && mkdir -p `dirname ${backup_path}`
backup_file="${backup_path}"
else
echo "you input backup file is error" && exit 4
fi
}
set_default_user_pass_host(){
[ "$1" != "" ] && user=$user || user=$USER
[ "$2" != "" ] && passwd=$passwd || passwd="null"
[ "$3" != "" ] && host=$host || host="localhost"
}
input_user_passwd_host(){
read -p "please input user(default $USER):" user
read -p "please input passwd(default 'null'):" passwd
read -p "please input host(default 127.0.0.1):" host
}
fun_xtrabackup(){
[ ! -e `which xtrabackup &> https://img.qb5200.com/download-x/dev/null` ] && yum install percona-xtrabackup -y &> https://img.qb5200.com/download-x/dev/null
[ ! -e $backup_dir ] && mkdir -p $backup_dir
if [ "$passwd" != "null" ];then
xtrabackup --user=$1 --password=$2 --host=$3 --backup --target-dir=$4 &> https://img.qb5200.com/download-x/dev/null
else
xtrabackup --user=$1 --host=$3 --backup --target-dir=$4 &> https://img.qb5200.com/download-x/dev/null
fi
[ $? -eq 0 ] && action "xtrabackup completed OK!" /bin/true || action "xtrabackup completed failed" /bin/false
exit
}
check_target_dir(){
[ $# -eq 0 ] && backup_dir=""
if [[ $1 =~ ^(\/.*\/)$ ]];then
[ ! -e $1 ] && mkdir -p $1
backup_dir=$1
elif [[ $1 =~ ^([^\/].*\/)$ ]];then
backup_path="${PWD}/$1"
[ ! -e ${backup_path} ] && mkdir -p ${backup_path}
backup_dir="${backup_path}"
else
echo "target-dir must is directory " && exit 5
fi
}
while true
do
cat << EOF
Please input a number choose you backup tool
1.mysqldump
2.xtrabackup
3.quit
EOF
read -p "you choose:" choose
case $choose in
1)
input_user_passwd_host
set_default_user_pass_host $user $passwd $host
if try_connection_mariadb $user $passwd $host ;then
read -p "please input mariadb backup file path(default $HOME/backup.all.sql):" backupfile_path
[ "$backupfile_path" != "" ] && check_backup_path $backupfile_path || backup_file="$HOME/backup.all.sql"
fun_mysqldump $user $passwd $host $backup_file
fi
;;
2)
input_user_passwd_host
set_default_user_pass_host $user $passwd $host
if try_connection_mariadb $user $passwd $host ;then
read -p "please input target-dir (default $HOME/backup/):" target_dir
[ "$target_dir" != "" ] && check_target_dir $target_dir || backup_dir="$HOME/backup/"
fun_xtrabackup $user $passwd $host $backup_dir
fi
;;
3)
echo "bye !!" && exit 6
;;
*)
echo "choose error" && exit 7
;;
esac
done
[root@test-centos7-node1 scripts]#
验证：
[root@test-centos7-node1 scripts]# ls /
bin boot dev etc home lib lib64 media mnt opt proc root run sbin snap srv sys tmp usr var
[root@test-centos7-node1 scripts]# sh chose_backup_mysql.sh
Please input a number choose you backup tool
1.mysqldump
2.xtrabackup
3.quit
you choose:1
please input user(default root):test
please input passwd(default 'null'):admin
please input host(default 127.0.0.1):192.168.0.10
please input mariadb backup file path(default /root/backup.all.sql):/backup/mariadb/all.sql
backup successful ,please cat /backup/mariadb/all.sql [ OK ]
[root@test-centos7-node1 scripts]# ll /backup/mariadb/all.sql
-rw-r--r--. 1 root root 512222 Jan 12 10:01 /backup/mariadb/all.sql
[root@test-centos7-node1 scripts]# ll /
total 20
drwxr-xr-x. 3 root root 21 Jan 12 10:01 backup
lrwxrwxrwx. 1 root root 7 Jan 1 07:19 bin -> usr/bin
dr-xr-xr-x. 5 root root 4096 Jan 1 07:24 boot
drwxr-xr-x. 20 root root 3280 Jan 11 22:55 dev
drwxr-xr-x. 76 root root 8192 Jan 12 08:41 etc
drwxr-xr-x. 2 root root 6 Jan 12 07:27 home
lrwxrwxrwx. 1 root root 7 Jan 1 07:19 lib -> usr/lib
lrwxrwxrwx. 1 root root 9 Jan 1 07:19 lib64 -> usr/lib64
drwxr-xr-x. 2 root root 6 Nov 5 2016 media
drwxr-xr-x. 2 root root 6 Nov 5 2016 mnt
drwxr-xr-x. 2 root root 6 Nov 5 2016 opt
dr-xr-xr-x. 129 root root 0 Jan 11 22:54 proc
dr-xr-x---. 5 root root 4096 Jan 12 09:56 root
drwxr-xr-x. 25 root root 720 Jan 12 08:53 run
lrwxrwxrwx. 1 root root 8 Jan 1 07:19 sbin -> usr/sbin
drwxr-xr-x. 2 root root 6 Jan 11 03:24 snap
drwxr-xr-x. 2 root root 6 Nov 5 2016 srv
dr-xr-xr-x. 13 root root 0 Jan 11 22:55 sys
drwxrwxrwt. 9 root root 280 Jan 12 10:00 tmp
drwxr-xr-x. 13 root root 155 Jan 1 07:19 usr
drwxr-xr-x. 19 root root 267 Jan 1 07:24 var
[root@test-centos7-node1 scripts]# ll /backup/
total 0
drwxr-xr-x. 2 root root 21 Jan 12 10:01 mariadb
[root@test-centos7-node1 scripts]# sh chose_backup_mysql.sh
Please input a number choose you backup tool
1.mysqldump
2.xtrabackup
3.quit
you choose:2
please input user(default root):
please input passwd(default 'null'):
please input host(default 127.0.0.1):
please input target-dir (default /root/backup/):/backup/xtrabackups/
xtrabackup completed OK! [ OK ]
[root@test-centos7-node1 scripts]# ll /backup/
total 0
drwxr-xr-x. 2 root root 21 Jan 12 10:01 mariadb
drwxr-xr-x. 6 root root 187 Jan 12 10:03 xtrabackups
[root@test-centos7-node1 scripts]# ll /backup/xtrabackups/
total 18456
-rw-r-----. 1 root root 431 Jan 12 10:03 backup-my.cnf
drwxr-x---. 2 root root 272 Jan 12 10:03 hellodb
-rw-r-----. 1 root root 18874368 Jan 12 10:03 ibdata1
drwxr-x---. 2 root root 4096 Jan 12 10:03 mysql
drwxr-x---. 2 root root 4096 Jan 12 10:03 performance_schema
drwxr-x---. 2 root root 20 Jan 12 10:03 test
-rw-r-----. 1 root root 113 Jan 12 10:03 xtrabackup_checkpoints
-rw-r-----. 1 root root 461 Jan 12 10:03 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 12 10:03 xtrabackup_logfile
[root@test-centos7-node1 scripts]#
说明：以上脚本实现了用户选择一款工具的名称做备份，然后指定连接数据库的用户名，如果未指定用户名，默认是当前Linux登录用户的用户名作为连接mariadb数据库的用户；指定连接数据库的密码，若未指定默认是空；指定数据库地址，若未指定，默认是localhost 或者127.0.0.1 ，最后还要指定备份到那个的地方，如果使用mysqldump 那么需要指定其存放文件的全路径（包括文件名称，若只是给定了一个目录，那么mysqldump默认会在指定的目录下创建一个all_backup.sql文件），若未指定存放文件的全路径，则默认放在当前用户家目录下，并取名backup.all.sql;如果选择的是xtrabackup备份工具备份数据，也需要指定其数据库用户名，密码，数据库地址，以及存放备份文件的目录，用户名和密码和数据库地址 ，若都没有指定，那么用户名就是用的当前Linux登录用户，密码为空，数据库地址为localhost或127.0.0.1 同mysqldump 工具的默认值相同。最后就是存放备份数据库文件目录，若未指定默认存放在当前用户的家目录的backup下存放。
2、配置Mysql主从同步
1）准备两台mariadb数据库 centos7上的mariadb为主库，centos6上的mariadb为从库 mariadb 编译安装请参考https://www.cnblogs.com/qiuhom-1874/p/12111497.html
2）在主库上开启log-bin日志，并配置主库和从库的server-id（组从库server-id不能相同），如果从库需要级联其他从库需开启log-bin
主库配置文件
[root@test-centos7-node1 ~]# grep -Eiv ^"#|^$" /etc/my.cnf [client] port = 3306 socket = https://img.qb5200.com/download-x/data/mysql/mysql.sock [mysqld] port = 3306 socket = https://img.qb5200.com/download-x/data/mysql/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin=mysql-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [root@test-centos7-node1 ~]#
说明：主库配置文件中只需要开启log-bin和server-id 即可
从库配置文件
[root@test-centos6-node1 ~]# grep -Eiv ^"#|^$" /etc/my.cnf [client] port = 3306 socket = https://img.qb5200.com/download-x/data/mysql/mysql.sock [mysqld] port = 3306 socket = https://img.qb5200.com/download-x/data/mysql/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 server-id = 2 read_only =ON relay_log =relay-log relay_log_index =relay-log.index [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [root@test-centos6-node1 ~]#
说明：从库需要开启中继日志，并把从库设置为只读（普通用户只能读，不能修改），server-id 不同于主库即可。更改了主从库的配置文件后需要重启服务才能生效。
3）重启主从库数据库，使其配置文件生效
主库
[root@test-centos7-node1 ~]# /etc/init.d/mysqld restart Restarting mysqld (via systemctl): [ OK ] [root@test-centos7-node1 ~]#
从库
[root@test-centos6-node1 ~]# /etc/init.d/mysqld restart Shutting down MariaDB.. SUCCESS! Starting MariaDB.200113 09:00:23 mysqld_safe Logging to 'https://img.qb5200.com/download-x/data/mysql/test-centos6-node1.err'. 200113 09:00:23 mysqld_safe Starting mysqld daemon with databases from https://img.qb5200.com/download-x/data/mysql SUCCESS! [root@test-centos6-node1 ~]#
说明：本次实验我是源码编译安装的mariadb,所以启动都是直接用脚本启动。
4）在主库上创建用于同步的账号
MariaDB [(none)]> select user,password,host from mysql.user; +------+----------+--------------------+ | user | password | host | +------+----------+--------------------+ | root | | localhost | | root | | test-centos7-node1 | | root | | 127.0.0.1 | | root | | ::1 | +------+----------+--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> grant replication slave on *.* to 'rep_user'@'192.168.0.%' identified by 'admin'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> select user,password,host from mysql.user; +----------+-------------------------------------------+--------------------+ | user | password | host | +----------+-------------------------------------------+--------------------+ | root | | localhost | | root | | test-centos7-node1 | | root | | 127.0.0.1 | | root | | ::1 | | rep_user | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | 192.168.0.% | +----------+-------------------------------------------+--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]>
说明：授权只需要给replication slave 权限即可，有关mysql创建用户授权可参考https://www.cnblogs.com/qiuhom-1874/p/9741166.html
5）在从库上测试创建的账号是否能够登录到主库
[root@test-centos6-node1 ~]# mysql -urep_user -padmin -h192.168.0.10 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 22 Server version: 10.2.19-MariaDB-log Source distribution 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)]>
说明：是能够连接上主库的，说明账号没有问题。如果创建的账号无法连接主库，需要检查主库是否开启了防火墙，检查账号是否正确，最后还要检查主库的监听端口等。
5）在主库上查看二进制文件名和位置点，并记录
MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 328 | +------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]>
6）在从库上配置连接主库用于复制到账号信息
MariaDB [(none)]> show slave status\G
Empty set (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.10',
-> MASTER_USER='rep_user',
-> MASTER_PASSWORD='admin',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=328;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.10
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
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: 328
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: 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:
1 row in set (0.00 sec)
MariaDB [(none)]>
说明：change master to 这个命令太长了，可用help change master to 查看其帮助。我们需要配置好主库地址，用于复制到账号，密码，以及主库的端口，二进制文件名，二进制日志位置点信息即可，配置好后就可以用show slave status\G 查看得到刚才我们配置的信息
7）从库开启同步
MariaDB [(none)]> start slave ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.10
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000001
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: 328
Relay_Log_Space: 858
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
1 row in set (0.00 sec)
MariaDB [(none)]>
说明：可看到IO线程和sql线程都已经是yes的了。到此mariadb的主从复制就做好了，接下来测试
测试：在主库上导入数据，看看从库是否能够及时的同步过来
1）主库导入数据并查看导入到数据
[root@test-centos7-node1 ~]# rz rz waiting to receive. zmodem trl+C ȡ 100% 7 KB 7 KB/s 00:00:01 0 Errors [root@test-centos7-node1 ~]# mysql < hellodb_innodb.sql [root@test-centos7-node1 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 Server version: 10.2.19-MariaDB-log Source distribution 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 databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> use hellodb Database changed MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.00 sec) MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [hellodb]>
说明：可看到主库已经有数据生成
2）从库查看数据是否同主库一致
[root@test-centos6-node1 ~]# mysql -e "show databases;" +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | test | +--------------------+ [root@test-centos6-node1 ~]# mysql -e "use hellodb;show tables;" +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ [root@test-centos6-node1 ~]# mysql -e "use hellodb;select * from students;" +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ [root@test-centos6-node1 ~]#
说明：可看到从库把主库里新加的库和表都复制过来了
有关mysql主从复制详细说明请参考https://www.cnblogs.com/qiuhom-1874/p/9762855.html
3、使用MHA实现Mysql高可用。
1）环境说明 3台centos7为mariadb数据库主从复制环境，centos6为mha管理节点，其中node1为主从复制主节点，2、3为从节点
2）前期准备工作，关闭所有服务器上的selinu和防火墙
[root@test-centos7-node1 ~]# systemctl stop firewalld [root@test-centos7-node1 ~]# systemctl is-enabled firewalld enabled [root@test-centos7-node1 ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/systemhttps://img.qb5200.com/download-x/dbus-org.fedoraproject.FirewallD1.service. [root@test-centos7-node1 ~]# systemctl is-enabled firewalld disabled [root@test-centos7-node1 ~]# sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config [root@test-centos7-node1 ~]# cat /etc/selinux/config # This file controls the state of SELinux on the system. # SELINUX=disabled # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of three two values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted [root@test-centos7-node1 ~]# setenforce 0 [root@test-centos7-node1 ~]# getenforce Permissive [root@test-centos7-node1 ~]#
说明：在主从复制所有节点以及管理节点都关闭防火墙和selinux
[root@test-centos6-node1 ~]# /etc/init.d/iptables stop iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] [root@test-centos6-node1 ~]# chkconfig iptables off [root@test-centos6-node1 ~]# chkconfig --list|grep iptables iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off [root@test-centos6-node1 ~]# sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config [root@test-centos6-node1 ~]# cat /etc/selinux/config # This file controls the state of SELinux on the system. # SELINUX=disabled # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of these two values: # targeted - Targeted processes are protected, # mls - Multi Level Security protection. SELINUXTYPE=targeted [root@test-centos6-node1 ~]# setenforce 0 [root@test-centos6-node1 ~]# getenforce Permissive [root@test-centos6-node1 ~]#
说明：centos6是管理节点，也需要关闭防火墙和selinux，这样做就是排除后续做实验，防火墙和selinux带来的不必要的错误。
3）搭建mariadb的主从复制
主节点配置文件
[root@test-centos7-node1 my.cnf.d]# cat /etc/my.cnf.d/master.cnf [mysqld] log-bin server_id=1 skip_name_resolve=1 [root@test-centos7-node1 my.cnf.d]#
说明：可以在server.cnf里添加以上配置，也可以在my.cnf里面加，当然也可以单独建立独立的配置文件，这样方便管理
从节点配置文件
root@test-centos7-node2 ~]# cat /etc/my.cnf.d/slave.cnf [mysqld] server_id=2 log-bin read_only relay_log_purge=0 skip_name_resolve=1 [root@test-centos7-node2 ~]#
说明：从节点需要加上relay_log_purge=0表示不清除中继日志。注意这里需要说明一点的是，在备用的主节点(将来可能成为主的服务器)上需要开启log-bin，server_id 的值不同于其他主机即可
[root@test-centos7-node3 ~]# cat /etc/my.cnf.d/slave.cnf [mysqld] server_id=3 read_only relay_log_purge=0 skip_name_resolve=1 [root@test-centos7-node3 ~]#
4）重新启动所有节点的数据库服务，在主库上查看二进制日志文件名和日志位置点
[root@test-centos7-node1 ~]# systemctl restart mariadb [root@test-centos7-node1 ~]# [root@test-centos7-node1 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]>
说明：之所以查看主库二进制日志名称和位置点数方便待会从库里配置
5）主库创建用于从库连接主库复制的账号
MariaDB [(none)]> grant replication slave on *.* to repuser@'192.168.0.%' identified by 'admin'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> select user,host,password from mysql.user; +---------+--------------------+-------------------------------------------+ | user | host | password | +---------+--------------------+-------------------------------------------+ | root | localhost | | | root | test-centos7-node1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | test-centos7-node1 | | | repuser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | +---------+--------------------+-------------------------------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]>
6）在从库上测试主库刚才建立的账号是否可登录主库
[root@test-centos7-node2 ~]# mysql -urepuser -padmin -h192.168.0.10 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show grants for repuser@'192.168.0.%'; +------------------------------------------------------------------------------------------------------------------------------+ | Grants for repuser@192.168.0.% | +------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'192.168.0.%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' | +------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) MariaDB [(none)]>
7）在从库上配置连接主库进行复制的账号和二进制日志名称及位置点信息
[root@test-centos7-node2 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, 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
Empty set (0.01 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.10',
-> MASTER_USER='repuser',
-> MASTER_PASSWORD='admin',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.08 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.10
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000001
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: 245
Relay_Log_Space: 245
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
1 row in set (0.01 sec)
MariaDB [(none)]>
说明：两从库都执行上面相同的change master to 命令即可
8）从库开启复制
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.10
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 397
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 683
Relay_Master_Log_File: mariadb-bin.000001
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: 397
Relay_Log_Space: 979
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
1 row in set (0.00 sec)
MariaDB [(none)]>
说明：到此主从复制就大家完成，接下来在主库上创建用于管理端管理数据库的账号
9）在主库上创建用于管理端管理数据库的管理帐号
MariaDB [(none)]> grant all on *.* to repmanage@'192.168.0.%' identified by 'admin'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> select user,host,password from mysql.user; +-----------+--------------------+-------------------------------------------+ | user | host | password | +-----------+--------------------+-------------------------------------------+ | root | localhost | | | root | test-centos7-node1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | test-centos7-node1 | | | repuser | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | | repmanage | 192.168.0.% | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | +-----------+--------------------+-------------------------------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]>
说明：此刻就可以去从节点看刚才在主库创建的账号是否同步到从库里去了，如果同步了，说明mariadb的主从复制是没有问题的。
10）在所有节点上做ssh key验证包括管理节点上，实现双向key验证
[root@test-centos6-node1 ~]# ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/root/.ssh/id_rsa): Created directory '/root/.ssh'. Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /root/.ssh/id_rsa. Your public key has been saved in /root/.ssh/id_rsa.pub. The key fingerprint is: 99:5f:36:b0:d1:cb:d2:85:f3:fa:65:ac:68:86:0e:e3 root@test-centos6-node1 The key's randomart image is: +--[ RSA 2048]----+ | | | . . | | o + . | | o * = | | S o B . | | . + o . | | o ... +| | . o. oo + | | E..o. o | +-----------------+ [root@test-centos6-node1 ~]# ssh-copy-id 192.168.0.11 The authenticity of host '192.168.0.11 (192.168.0.11)' can't be established. RSA key fingerprint is f7:d4:c0:12:41:4a:46:4e:8b:d6:eb:80:06:ca:5e:fe. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.11' (RSA) to the list of known hosts. root@192.168.0.11's password: Now try logging into the machine, with "ssh '192.168.0.11'", and check in: .ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting. [root@test-centos6-node1 ~]# scp -rp /root/.ssh 192.168.0.10:/root/ The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established. RSA key fingerprint is 7e:4a:a2:53:1b:fa:7b:52:c3:b6:9d:f7:7a:8d:4d:23. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.10' (RSA) to the list of known hosts. root@192.168.0.10's password: authorized_keys 100% 405 0.4KB/s 00:00 id_rsa 100% 1675 1.6KB/s 00:00 id_rsa.pub 100% 405 0.4KB/s 00:00 known_hosts 100% 788 0.8KB/s 00:00 [root@test-centos6-node1 ~]# scp -rp /root/.ssh 192.168.0.20:/root/ The authenticity of host '192.168.0.20 (192.168.0.20)' can't be established. RSA key fingerprint is 7e:4a:a2:53:1b:fa:7b:52:c3:b6:9d:f7:7a:8d:4d:23. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.20' (RSA) to the list of known hosts. root@192.168.0.20's password: authorized_keys 100% 405 0.4KB/s 00:00 id_rsa 100% 1675 1.6KB/s 00:00 id_rsa.pub 100% 405 0.4KB/s 00:00 known_hosts 100% 1182 1.2KB/s 00:00 [root@test-centos6-node1 ~]# scp -rp /root/.ssh 192.168.0.30:/root/ The authenticity of host '192.168.0.30 (192.168.0.30)' can't be established. RSA key fingerprint is 7e:4a:a2:53:1b:fa:7b:52:c3:b6:9d:f7:7a:8d:4d:23. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.30' (RSA) to the list of known hosts. root@192.168.0.30's password: authorized_keys 100% 405 0.4KB/s 00:00 id_rsa 100% 1675 1.6KB/s 00:00 id_rsa.pub 100% 405 0.4KB/s 00:00 known_hosts 100% 1576 1.5KB/s 00:00 [root@test-centos6-node1 ~]#
说明：这样在管理端做好了ssh key验证后，管理端可以任意登录被管理端，同时被管理端也可以连接管理端。到此所有环境的准备都已经准备好了，接下来装包
11）在管理端安装两个包mha4mysql-manager和mha4mysql-node
[root@test-centos6-node1 ~]# rz rz waiting to receive. zmodem trl+C ȡ 100% 85 KB 85 KB/s 00:00:01 0 Errors-0.el6.noarch.rpm... [root@test-centos6-node1 ~]# rz rz waiting to receive. zmodem trl+C ȡ 100% 35 KB 35 KB/s 00:00:01 0 Errorsel6.noarch.rpm... [root@test-centos6-node1 ~]# ls mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm [root@test-centos6-node1 ~]# yum install mha4mysql-* Loaded plugins: fastestmirror Setting up Install Process Examining mha4mysql-manager-0.56-0.el6.noarch.rpm: mha4mysql-manager-0.56-0.el6.noarch Marking mha4mysql-manager-0.56-0.el6.noarch.rpm to be installed Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun.com Examining mha4mysql-node-0.56-0.el6.noarch.rpm: mha4mysql-node-0.56-0.el6.noarch Marking mha4mysql-node-0.56-0.el6.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mha4mysql-manager.noarch 0:0.56-0.el6 will be installed --> Processing Dependency: perl(Config::Tiny) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Config::Tiny) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(DBI) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch::File) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch::Screen) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Parallel::ForkManager) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Parallel::ForkManager) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Time::HiRes) for package: mha4mysql-manager-0.56-0.el6.noarch ---> Package mha4mysql-node.noarch 0:0.56-0.el6 will be installed --> Processing Dependency: perl(DBD::mysql) for package: mha4mysql-node-0.56-0.el6.noarch --> Running transaction check ---> Package perl-Config-Tiny.noarch 0:2.12-7.1.el6 will be installed ---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed ---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed ---> Package perl-Log-Dispatch.noarch 0:2.27-1.el6 will be installed --> Processing Dependency: perl(MIME::Lite) for package: perl-Log-Dispatch-2.27-1.el6.noarch --> Processing Dependency: perl(Mail::Send) for package: perl-Log-Dispatch-2.27-1.el6.noarch --> Processing Dependency: perl(Mail::Sender) for package: perl-Log-Dispatch-2.27-1.el6.noarch --> Processing Dependency: perl(Mail::Sendmail) for package: perl-Log-Dispatch-2.27-1.el6.noarch --> Processing Dependency: perl(Params::Validate) for package: perl-Log-Dispatch-2.27-1.el6.noarch ---> Package perl-Parallel-ForkManager.noarch 0:1.20-1.el6 will be installed ---> Package perl-Time-HiRes.x86_64 4:1.9721-144.el6 will be installed --> Running transaction check ---> Package perl-MIME-Lite.noarch 0:3.027-2.el6 will be installed --> Processing Dependency: perl(MIME::Types) >= 1.28 for package: perl-MIME-Lite-3.027-2.el6.noarch --> Processing Dependency: perl(Email::Date::Format) for package: perl-MIME-Lite-3.027-2.el6.noarch ---> Package perl-Mail-Sender.noarch 0:0.8.16-3.el6 will be installed ---> Package perl-Mail-Sendmail.noarch 0:0.79-12.el6 will be installed ---> Package perl-MailTools.noarch 0:2.04-4.el6 will be installed --> Processing Dependency: perl(Date::Parse) for package: perl-MailTools-2.04-4.el6.noarch --> Processing Dependency: perl(Date::Format) for package: perl-MailTools-2.04-4.el6.noarch ---> Package perl-Params-Validate.x86_64 0:0.92-3.el6 will be installed --> Running transaction check ---> Package perl-Email-Date-Format.noarch 0:1.002-5.el6 will be installed ---> Package perl-MIME-Types.noarch 0:1.28-2.el6 will be installed ---> Package perl-TimeDate.noarch 1:1.16-13.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================= Package Arch Version Repository Size ================================================================================================= Installing: mha4mysql-manager noarch 0.56-0.el6 /mha4mysql-manager-0.56-0.el6.noarch 325 k mha4mysql-node noarch 0.56-0.el6 /mha4mysql-node-0.56-0.el6.noarch 102 k Installing for dependencies: perl-Config-Tiny noarch 2.12-7.1.el6 base 23 k perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k perl-DBI x86_64 1.609-4.el6 base 705 k perl-Email-Date-Format noarch 1.002-5.el6 base 16 k perl-Log-Dispatch noarch 2.27-1.el6 epel 71 k perl-MIME-Lite noarch 3.027-2.el6 base 82 k perl-MIME-Types noarch 1.28-2.el6 base 32 k perl-Mail-Sender noarch 0.8.16-3.el6 epel 54 k perl-Mail-Sendmail noarch 0.79-12.el6 epel 28 k perl-MailTools noarch 2.04-4.el6 base 101 k perl-Parallel-ForkManager noarch 1.20-1.el6 epel 27 k perl-Params-Validate x86_64 0.92-3.el6 base 75 k perl-Time-HiRes x86_64 4:1.9721-144.el6 base 49 k perl-TimeDate noarch 1:1.16-13.el6 base 37 k Transaction Summary ================================================================================================= Install 16 Package(s) Total size: 1.8 M Total download size: 1.4 M Installed size: 3.5 M Is this ok [y/N]: y Downloading Packages: (1/14): perl-Config-Tiny-2.12-7.1.el6.noarch.rpm | 23 kB 00:00 (2/14): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:00 (3/14): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00 (4/14): perl-Email-Date-Format-1.002-5.el6.noarch.rpm | 16 kB 00:00 (5/14): perl-Log-Dispatch-2.27-1.el6.noarch.rpm | 71 kB 00:00 (6/14): perl-MIME-Lite-3.027-2.el6.noarch.rpm | 82 kB 00:00 (7/14): perl-MIME-Types-1.28-2.el6.noarch.rpm | 32 kB 00:00 (8/14): perl-Mail-Sender-0.8.16-3.el6.noarch.rpm | 54 kB 00:00 (9/14): perl-Mail-Sendmail-0.79-12.el6.noarch.rpm | 28 kB 00:00 (10/14): perl-MailTools-2.04-4.el6.noarch.rpm | 101 kB 00:00 (11/14): perl-Parallel-ForkManager-1.20-1.el6.noarch.rpm | 27 kB 00:00 (12/14): perl-Params-Validate-0.92-3.el6.x86_64.rpm | 75 kB 00:00 (13/14): perl-Time-HiRes-1.9721-144.el6.x86_64.rpm | 49 kB 00:00 (14/14): perl-TimeDate-1.16-13.el6.noarch.rpm | 37 kB 00:00 ------------------------------------------------------------------------------------------------- Total 669 kB/s | 1.4 MB 00:02 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : perl-DBI-1.609-4.el6.x86_64 1/16 Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 2/16 Installing : mha4mysql-node-0.56-0.el6.noarch 3/16 Installing : perl-MIME-Types-1.28-2.el6.noarch 4/16 Installing : perl-Config-Tiny-2.12-7.1.el6.noarch 5/16 Installing : perl-Parallel-ForkManager-1.20-1.el6.noarch 6/16 Installing : perl-Params-Validate-0.92-3.el6.x86_64 7/16 Installing : 4:perl-Time-HiRes-1.9721-144.el6.x86_64 8/16 Installing : perl-Mail-Sender-0.8.16-3.el6.noarch 9/16 Installing : 1:perl-TimeDate-1.16-13.el6.noarch 10/16 Installing : perl-MailTools-2.04-4.el6.noarch 11/16 Installing : perl-Mail-Sendmail-0.79-12.el6.noarch 12/16 Installing : perl-Email-Date-Format-1.002-5.el6.noarch 13/16 Installing : perl-MIME-Lite-3.027-2.el6.noarch 14/16 Installing : perl-Log-Dispatch-2.27-1.el6.noarch 15/16 Installing : mha4mysql-manager-0.56-0.el6.noarch 16/16 Verifying : mha4mysql-manager-0.56-0.el6.noarch 1/16 Verifying : perl-Email-Date-Format-1.002-5.el6.noarch 2/16 Verifying : perl-Mail-Sendmail-0.79-12.el6.noarch 3/16 Verifying : mha4mysql-node-0.56-0.el6.noarch 4/16 Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 5/16 Verifying : 1:perl-TimeDate-1.16-13.el6.noarch 6/16 Verifying : perl-MIME-Lite-3.027-2.el6.noarch 7/16 Verifying : perl-Mail-Sender-0.8.16-3.el6.noarch 8/16 Verifying : perl-DBI-1.609-4.el6.x86_64 9/16 Verifying : 4:perl-Time-HiRes-1.9721-144.el6.x86_64 10/16 Verifying : perl-Params-Validate-0.92-3.el6.x86_64 11/16 Verifying : perl-MailTools-2.04-4.el6.noarch 12/16 Verifying : perl-Parallel-ForkManager-1.20-1.el6.noarch 13/16 Verifying : perl-Config-Tiny-2.12-7.1.el6.noarch 14/16 Verifying : perl-Log-Dispatch-2.27-1.el6.noarch 15/16 Verifying : perl-MIME-Types-1.28-2.el6.noarch 16/16 Installed: mha4mysql-manager.noarch 0:0.56-0.el6 mha4mysql-node.noarch 0:0.56-0.el6 Dependency Installed: perl-Config-Tiny.noarch 0:2.12-7.1.el6 perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-DBI.x86_64 0:1.609-4.el6 perl-Email-Date-Format.noarch 0:1.002-5.el6 perl-Log-Dispatch.noarch 0:2.27-1.el6 perl-MIME-Lite.noarch 0:3.027-2.el6 perl-MIME-Types.noarch 0:1.28-2.el6 perl-Mail-Sender.noarch 0:0.8.16-3.el6 perl-Mail-Sendmail.noarch 0:0.79-12.el6 perl-MailTools.noarch 0:2.04-4.el6 perl-Parallel-ForkManager.noarch 0:1.20-1.el6 perl-Params-Validate.x86_64 0:0.92-3.el6 perl-Time-HiRes.x86_64 4:1.9721-144.el6 perl-TimeDate.noarch 1:1.16-13.el6 Complete! [root@test-centos6-node1 ~]
说明：安装这两个包需要开启epel源，因为它们的依赖包有些来自epel源里
12）在各个被管理端安装mha4mysql-node包
[root@test-centos7-node1 ~]# ls mha4mysql-node-0.56-0.el6.noarch.rpm [root@test-centos7-node1 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm Loaded plugins: fastestmirror Examining mha4mysql-node-0.56-0.el6.noarch.rpm: mha4mysql-node-0.56-0.el6.noarch Marking mha4mysql-node-0.56-0.el6.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mha4mysql-node.noarch 0:0.56-0.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================= Installing: mha4mysql-node noarch 0.56-0.el6 /mha4mysql-node-0.56-0.el6.noarch 102 k Transaction Summary ================================================================================================================================= Install 1 Package Total size: 102 k Installed size: 102 k Is this ok [yhttps://img.qb5200.com/download-x/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mha4mysql-node-0.56-0.el6.noarch 1/1 Verifying : mha4mysql-node-0.56-0.el6.noarch 1/1 Installed: mha4mysql-node.noarch 0:0.56-0.el6 Complete! [root@test-centos7-node1 ~]#
说明：到此mha的软件都已部署完毕，接下来是在管理端建立配置文件
13）在管理节点新建配置文件
[root@test-centos6-node1 ~]# mkdir /etc/mastermh/ [root@test-centos6-node1 ~]# cd /etc/mastermh/ [root@test-centos6-node1 mastermh]# cat >> mariadb.cnf << EOF > [server default] > user=repmanage > password=admin > manager_workdir=https://img.qb5200.com/download-x/data/mastermha/mariadb/ > manager_log=https://img.qb5200.com/download-x/data/mastermha/mariadb/manager.log > remote_workdir=https://img.qb5200.com/download-x/data/mastermha/mariadb/ > ssh_user=root > repl_user=repuser > repl_password=admin > ping_interval=1 > > > [server1] > hostname=192.168.0.10 > candidate_master=1 > [server2] > hostname=192.168.0.20 > candidate_master=1 > [server3] > hostname=192.168.0.30 > EOF [root@test-centos6-node1 mastermh]# cat mariadb.cnf [server default] user=repmanage password=admin manager_workdir=https://img.qb5200.com/download-x/data/mastermha/mariadb/ manager_log=https://img.qb5200.com/download-x/data/mastermha/mariadb/manager.log remote_workdir=https://img.qb5200.com/download-x/data/mastermha/mariadb/ ssh_user=root repl_user=repuser repl_password=admin ping_interval=1 [server1] hostname=192.168.0.10 candidate_master=1 [server2] hostname=192.168.0.20 candidate_master=1 [server3] hostname=192.168.0.30 [root@test-centos6-node1 mastermh]#
说明：此文件的文件可以说任意名称，只要自己知道就行，也没有特定规定放在哪个位置，看自己的喜好即可。因为待会启动mha 我们是要指定配置文件的路径，配置文件中主要配置了用于管理mariadb节点的账号密码以及ssh管理的用户以及主从复制到账号和密码信息还有就是各个节点的地址，candidate_master=1表示将来可能选举成为主节点。ping_interval=1表示检测主库的时间间隔，心跳值；配置文件中manager的工作目录和日志目录我们不需要提前建立好，它这个目录只要我们指定了会自动生成的。
14）mha验证ssh基于KEY验证是否正常
[root@test-centos6-node1 ~]# masterha_check_ssh --conf=/etc/mastermh/mariadb.cnf Tue Jan 14 08:05:04 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Jan 14 08:05:04 2020 - [info] Reading application default configuration from /etc/mastermh/mariadb.cnf.. Tue Jan 14 08:05:04 2020 - [info] Reading server configuration from /etc/mastermh/mariadb.cnf.. Tue Jan 14 08:05:04 2020 - [info] Starting SSH connection tests.. Tue Jan 14 08:05:05 2020 - [debug] Tue Jan 14 08:05:04 2020 - [debug] Connecting via SSH from root@192.168.0.10(192.168.0.10:22) to root@192.168.0.20(192.168.0.20:22).. Warning: Permanently added '192.168.0.20' (ECDSA) to the list of known hosts. Tue Jan 14 08:05:05 2020 - [debug] ok. Tue Jan 14 08:05:05 2020 - [debug] Connecting via SSH from root@192.168.0.10(192.168.0.10:22) to root@192.168.0.30(192.168.0.30:22).. Warning: Permanently added '192.168.0.30' (ECDSA) to the list of known hosts. Tue Jan 14 08:05:05 2020 - [debug] ok. Tue Jan 14 08:05:06 2020 - [debug] Tue Jan 14 08:05:05 2020 - [debug] Connecting via SSH from root@192.168.0.30(192.168.0.30:22) to root@192.168.0.10(192.168.0.10:22).. Tue Jan 14 08:05:06 2020 - [debug] ok. Tue Jan 14 08:05:06 2020 - [debug] Connecting via SSH from root@192.168.0.30(192.168.0.30:22) to root@192.168.0.20(192.168.0.20:22).. Tue Jan 14 08:05:06 2020 - [debug] ok. Tue Jan 14 08:05:06 2020 - [debug] Tue Jan 14 08:05:04 2020 - [debug] Connecting via SSH from root@192.168.0.20(192.168.0.20:22) to root@192.168.0.10(192.168.0.10:22).. Tue Jan 14 08:05:05 2020 - [debug] ok. Tue Jan 14 08:05:05 2020 - [debug] Connecting via SSH from root@192.168.0.20(192.168.0.20:22) to root@192.168.0.30(192.168.0.30:22).. Warning: Permanently added '192.168.0.30' (ECDSA) to the list of known hosts. Tue Jan 14 08:05:06 2020 - [debug] ok. Tue Jan 14 08:05:06 2020 - [info] All SSH connection tests passed successfully. [root@test-centos6-node1 ~]#
说明：如果没有报错表示SSH key验证是没有问题的，在配置文件中配置的ssh信息是正确的
15）mha验证配置文件中配置的主从复制信息是否正确
[root@test-centos6-node1 ~]# masterha_check_repl --conf=/etc/mastermh/mariadb.cnf
Tue Jan 14 09:55:54 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jan 14 09:55:54 2020 - [info] Reading application default configuration from /etc/mastermh/mariadb.cnf..
Tue Jan 14 09:55:54 2020 - [info] Reading server configuration from /etc/mastermh/mariadb.cnf..
Tue Jan 14 09:55:54 2020 - [info] MHA::MasterMonitor version 0.56.
Tue Jan 14 09:55:55 2020 - [info] GTID failover mode = 0
Tue Jan 14 09:55:55 2020 - [info] Dead Servers:
Tue Jan 14 09:55:55 2020 - [info] Alive Servers:
Tue Jan 14 09:55:55 2020 - [info] 192.168.0.10(192.168.0.10:3306)
Tue Jan 14 09:55:55 2020 - [info] 192.168.0.20(192.168.0.20:3306)
Tue Jan 14 09:55:55 2020 - [info] 192.168.0.30(192.168.0.30:3306)
Tue Jan 14 09:55:55 2020 - [info] Alive Slaves:
Tue Jan 14 09:55:55 2020 - [info] 192.168.0.20(192.168.0.20:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:enabled
Tue Jan 14 09:55:55 2020 - [info] Replicating from 192.168.0.10(192.168.0.10:3306)
Tue Jan 14 09:55:55 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Jan 14 09:55:55 2020 - [info] 192.168.0.30(192.168.0.30:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:disabled
Tue Jan 14 09:55:55 2020 - [info] Replicating from 192.168.0.10(192.168.0.10:3306)
Tue Jan 14 09:55:55 2020 - [info] Current Alive Master: 192.168.0.10(192.168.0.10:3306)
Tue Jan 14 09:55:55 2020 - [info] Checking slave configurations..
Tue Jan 14 09:55:55 2020 - [warning] log-bin is not set on slave 192.168.0.30(192.168.0.30:3306). This host cannot be a master.
Tue Jan 14 09:55:55 2020 - [info] Checking replication filtering settings..
Tue Jan 14 09:55:55 2020 - [info] binlog_do_db= , binlog_ignore_db=
Tue Jan 14 09:55:55 2020 - [info] Replication filtering check ok.
Tue Jan 14 09:55:55 2020 - [info] GTID (with auto-pos) is not supported
Tue Jan 14 09:55:55 2020 - [info] Starting SSH connection tests..
Tue Jan 14 09:55:58 2020 - [info] All SSH connection tests passed successfully.
Tue Jan 14 09:55:58 2020 - [info] Checking MHA Node version..
Tue Jan 14 09:55:58 2020 - [info] Version check ok.
Tue Jan 14 09:55:58 2020 - [info] Checking SSH publickey authentication settings on the current master..
Tue Jan 14 09:55:59 2020 - [info] HealthCheck: SSH to 192.168.0.10 is reachable.
Tue Jan 14 09:55:59 2020 - [info] Master MHA Node version is 0.56.
Tue Jan 14 09:55:59 2020 - [info] Checking recovery script configurations on 192.168.0.10(192.168.0.10:3306)..
Tue Jan 14 09:55:59 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=https://img.qb5200.com/download-x/data/mastermha/mariadb//save_binary_logs_test --manager_version=0.56 --start_file=mariadb-bin.000002
Tue Jan 14 09:55:59 2020 - [info] Connecting to root@192.168.0.10(192.168.0.10:22)..
Creating https://img.qb5200.com/download-x/data/mastermha/mariadb if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mariadb-bin.000002
Tue Jan 14 09:55:59 2020 - [info] Binlog setting check done.
Tue Jan 14 09:55:59 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Jan 14 09:55:59 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='repmanage' --slave_host=192.168.0.20 --slave_ip=192.168.0.20 --slave_port=3306 --workdir=https://img.qb5200.com/download-x/data/mastermha/mariadb/ --target_version=5.5.56-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Jan 14 09:55:59 2020 - [info] Connecting to root@192.168.0.20(192.168.0.20:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000007
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000007
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Jan 14 09:55:59 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='repmanage' --slave_host=192.168.0.30 --slave_ip=192.168.0.30 --slave_port=3306 --workdir=https://img.qb5200.com/download-x/data/mastermha/mariadb/ --target_version=5.5.56-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Jan 14 09:55:59 2020 - [info] Connecting to root@192.168.0.30(192.168.0.30:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000009
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000009
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Jan 14 09:56:00 2020 - [info] Slaves settings check done.
Tue Jan 14 09:56:00 2020 - [info]
192.168.0.10(192.168.0.10:3306) (current master)
+--192.168.0.20(192.168.0.20:3306)
+--192.168.0.30(192.168.0.30:3306)
Tue Jan 14 09:56:00 2020 - [info] Checking replication health on 192.168.0.20..
Tue Jan 14 09:56:00 2020 - [info] ok.
Tue Jan 14 09:56:00 2020 - [info] Checking replication health on 192.168.0.30..
Tue Jan 14 09:56:00 2020 - [info] ok.
Tue Jan 14 09:56:00 2020 - [warning] master_ip_failover_script is not defined.
Tue Jan 14 09:56:00 2020 - [warning] shutdown_script is not defined.
Tue Jan 14 09:56:00 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[root@test-centos6-node1 ~]#
说明：看到最后提示MySQL Replication Health is OK 代表我们mariadb主从复制环境是健康的。接下来就可以开启监控
16）启动mha
说明：mha开启默认是前台执行，所以我们开启后光标一直在屏幕上闪烁，看到这种情况说明mha已经在监控我们的主从复制环境了，生产环境中 一般建议后台运行，前台运行关闭了CRT，mha也跟随着停止了工作，所以后台方式运行最佳。还需要说明一点的是mha它只是一次使用，也就是说它不能重复使用，如果我们主从环境中主节点宕机了，它的工作就是把主切换到我们预先配置的从节点，使其变为主节点，后续它就退出。接下来测试
测试：把主从环境中的主节点宕机，看看mha是否能够将我们预先设置好的从切换成主
说明：我们在主库上停止了mariadb服务后，管理节点上的mha立马就有反应了，过后就退出了，从上面的mha打印的信息看不出来具体哪个服务器成为主库了。我们可以查看manager的日志可以看到
[root@test-centos6-node1 ~]# tail -20 https://img.qb5200.com/download-x/data/mastermha/mariadb/manager.log Tue Jan 14 10:07:03 2020 - [info] Master failover to 192.168.0.20(192.168.0.20:3306) completed successfully. Tue Jan 14 10:07:03 2020 - [info] ----- Failover Report ----- mariadb: MySQL Master failover 192.168.0.10(192.168.0.10:3306) to 192.168.0.20(192.168.0.20:3306) succeeded Master 192.168.0.10(192.168.0.10:3306) is down! Check MHA Manager logs at test-centos6-node1:https://img.qb5200.com/download-x/data/mastermha/mariadb/manager.log for details. Started automated(non-interactive) failover. The latest slave 192.168.0.20(192.168.0.20:3306) has all relay logs for recovery. Selected 192.168.0.20(192.168.0.20:3306) as a new master. 192.168.0.20(192.168.0.20:3306): OK: Applying all logs succeeded. 192.168.0.30(192.168.0.30:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.0.30(192.168.0.30:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.0.20(192.168.0.20:3306) 192.168.0.20(192.168.0.20:3306): Resetting slave info succeeded. Master failover to 192.168.0.20(192.168.0.20:3306) completed successfully. [root@test-centos6-node1 ~]#
说明：从manage.log里记录的日志可以看到主库从192.168.0.10成功转移到192.168.0.20上，并且192.168.0.30已经从192.168.0.20哪里把所有的日志应用成功，并启动了slave，也就是告诉我们30已经切换新主去做主从同步数据了
[root@test-centos7-node3 ~]# mysql -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.20
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-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: 245
Relay_Log_Space: 827
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: 2
[root@test-centos7-node3 ~]#
说明：可以看到原来的从库已经拥护现在新的主库了
[root@test-centos7-node2 ~]# mysql -e " show slave status" [root@test-centos7-node2 ~]#
说明：原来的从节点上已经没有原主库同步的信息了
[root@test-centos7-node2 ~]# mysql -e " show variables like 'read_only'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ [root@test-centos7-node2 ~]#
说明：原来的从节点只读属性已经关闭了，这是因为原来的主节点宕机后，管理端把它提升为主的同时，关闭了它的只读属性。这里还需要说明一点，mha切换了主后，如果原来的主库后续又恢复正常，此时它也不能顶替现在的主库，相当于它和现在的集群环境没有关系了，只是一台单独的主机。
到此mha高可用实验就做完了，以上就是mha高可用实验的整个过程。
加载全部内容