1、编写脚本，支持让用户自主选择，使用MysqLdump还是xtraback全量备份。
[root@test-centos7-node1 scripts]# cat chose_backup_MysqL.sh #!/bin/bash##************************************************************************#Author: qiuhom#QQ: 467697313#mail: [email protected]#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/functionsfun_MysqLdump(){ if `which MysqLdump &> /dev/null` ;then MysqLdump_cmd=`which MysqLdump` else yum_cmd_path=`which yum` $yum_cmd_path install mariadb -y &> /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 [ "" != "null" ];then if `MysqL -u"" -p"" -h"" -e "show databases" &> /dev/null` ;then user= passwd= host= else echo "connection is lose,please check user or passwd or host " && exit 3 fi else if `MysqL -u"" -h"" -e "show databases" &> /dev/null` ;then user= host= else echo "connection is lose,please check user or host " && exit 3 fi fi}check_backup_path(){ [ $# -eq 0 ] && backup_file="" if [[ =~ ^(\/.*\/)$ ]];then [ ! -e ] && mkdir -p backup_file="all_backup.sql" elif [[ =~ ^([^\/].*\/)$ ]];then backup_path="${PWD}/" [ ! -e ${backup_path} ] && mkdir -p ${backup_path} backup_file="${backup_path}all_backup.sql" elif [[ =~ ^(\/.*[^\/]$) ]];then dir=`dirname ` [ ! -e "$dir" ] && mkdir -p $dir backup_file="" elif [[ =~ ^([^\/].*[^\/]$) ]];then backup_path="${PWD}/" [ ! -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(){ [ "" != "" ] && user=$user || user=$USER [ "" != "" ] && passwd=$passwd || passwd="null" [ "" != "" ] && 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 &> /dev/null` ] && yum install percona-xtrabackup -y &> /dev/null [ ! -e $backup_dir ] && mkdir -p $backup_dir if [ "$passwd" != "null" ];then xtrabackup --user= --password= --host= --backup --target-dir= &> /dev/null else xtrabackup --user= --host= --backup --target-dir= &> /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 [[ =~ ^(\/.*\/)$ ]];then [ ! -e ] && mkdir -p backup_dir= elif [[ =~ ^([^\/].*\/)$ ]];then backup_path="${PWD}/" [ ! -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.quitEOF 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.quityou choose:1please input user(default root):testplease input passwd(default 'null'):adminplease input host(default 127.0.0.1):192.168.0.10please 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 20drwxr-xr-x. 3 root root 21 Jan 12 10:01 backuplrwxrwxrwx. 1 root root 7 Jan 1 07:19 bin -> usr/bindr-xr-xr-x. 5 root root 4096 Jan 1 07:24 bootdrwxr-xr-x. 20 root root 3280 Jan 11 22:55 devdrwxr-xr-x. 76 root root 8192 Jan 12 08:41 etcdrwxr-xr-x. 2 root root 6 Jan 12 07:27 homelrwxrwxrwx. 1 root root 7 Jan 1 07:19 lib -> usr/liblrwxrwxrwx. 1 root root 9 Jan 1 07:19 lib64 -> usr/lib64drwxr-xr-x. 2 root root 6 Nov 5 2016 mediadrwxr-xr-x. 2 root root 6 Nov 5 2016 mntdrwxr-xr-x. 2 root root 6 Nov 5 2016 optdr-xr-xr-x. 129 root root 0 Jan 11 22:54 procdr-xr-x---. 5 root root 4096 Jan 12 09:56 rootdrwxr-xr-x. 25 root root 720 Jan 12 08:53 runlrwxrwxrwx. 1 root root 8 Jan 1 07:19 sbin -> usr/sbindrwxr-xr-x. 2 root root 6 Jan 11 03:24 snapdrwxr-xr-x. 2 root root 6 Nov 5 2016 srvdr-xr-xr-x. 13 root root 0 Jan 11 22:55 sysdrwxrwxrwt. 9 root root 280 Jan 12 10:00 tmpdrwxr-xr-x. 13 root root 155 Jan 1 07:19 usrdrwxr-xr-x. 19 root root 267 Jan 1 07:24 var[root@test-centos7-node1 scripts]# ll /backup/total 0drwxr-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.quityou choose:2please 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 0drwxr-xr-x. 2 root root 21 Jan 12 10:01 mariadbdrwxr-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.cnfdrwxr-x---. 2 root root 272 Jan 12 10:03 hellodb-rw-r-----. 1 root root 18874368 Jan 12 10:03 ibdata1drwxr-x---. 2 root root 4096 Jan 12 10:03 MysqLdrwxr-x---. 2 root root 4096 Jan 12 10:03 performance_schemadrwxr-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 = 3306socket = /data/MysqL/MysqL.sock[MysqLd]port = 3306socket = /data/MysqL/MysqL.sockskip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 1Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32Mthread_concurrency = 8log-bin=MysqL-binserver-ID = 1[MysqLdump]quickmax_allowed_packet = 16M[MysqL]no-auto-rehash[myisamchk]key_buffer_size = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[MysqLhotcopy]interactive-timeout[root@test-centos7-node1 ~]#
说明：主库配置文件中只需要开启log-bin和server-ID 即可
从库配置文件
[root@test-centos6-node1 ~]# grep -Eiv ^"#|^$" /etc/my.cnf[clIEnt]port = 3306socket = /data/MysqL/MysqL.sock[MysqLd]port = 3306socket = /data/MysqL/MysqL.sockskip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 1Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32Mthread_concurrency = 8server-ID = 2read_only =ONrelay_log =relay-logrelay_log_index =relay-log.index[MysqLdump]quickmax_allowed_packet = 16M[MysqL]no-auto-rehash[myisamchk]key_buffer_size = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[MysqLhotcopy]interactive-timeout[root@test-centos6-node1 ~]#
说明：从库需要开启中继日志，并把从库设置为只读（普通用户只能读，不能修改），server-ID 不同于主库即可。更改了主从库的配置文件后需要重启服务才能生效。
3）重启主从库数据库，使其配置文件生效
主库
[root@test-centos7-node1 ~]# /etc/init.d/MysqLd restartRestarting MysqLd (via systemctl): [ OK ][root@test-centos7-node1 ~]#
从库
[root@test-centos6-node1 ~]# /etc/init.d/MysqLd restartShutting down MariaDB.. SUCCESS! Starting MariaDB.200113 09:00:23 MysqLd_safe Logging to '/data/MysqL/test-centos6-node1.err'.200113 09:00:23 MysqLd_safe Starting MysqLd daemon with databases from /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,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.10Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection ID is 22Server version: 10.2.19-MariaDB-log Source distributioncopyright (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\GEmpty 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: NulLMaster_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: 0Master_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 it1 row in set (0.00 sec)MariaDB [(none)]>
说明：可看到IO线程和sql线程都已经是yes的了。到此mariadb的主从复制就做好了，接下来测试
测试：在主库上导入数据，看看从库是否能够及时的同步过来
1）主库导入数据并查看导入到数据
[root@test-centos7-node1 ~]# rzrz 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 ~]# MysqLWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection ID is 21Server version: 10.2.19-MariaDB-log Source distributioncopyright (c) 2000,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 hellodbDatabase changedMariaDB [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 firewalldenabled[root@test-centos7-node1 ~]# systemctl disable firewalldRemoved symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.[root@test-centos7-node1 ~]# systemctl is-enabled firewalldDisabled[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 ~]# getenforcePermissive[root@test-centos7-node1 ~]#
说明：在主从复制所有节点以及管理节点都关闭防火墙和selinux
[root@test-centos6-node1 ~]# /etc/init.d/iptables stopiptables: 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 iptablesiptables 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-binserver_ID=1skip_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=2log-binread_onlyrelay_log_purge=0skip_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=3read_onlyrelay_log_purge=0skip_name_resolve=1[root@test-centos7-node3 ~]#
4）重新启动所有节点的数据库服务，在主库上查看二进制日志文件名和日志位置点
[root@test-centos7-node1 ~]# systemctl restart mariadb[root@test-centos7-node1 ~]#[root@test-centos7-node1 ~]# MysqLWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection ID is 2Server version: 5.5.56-MariaDB MariaDB Servercopyright (c) 2000,2017,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,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.10Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection ID is 3Server version: 5.5.56-MariaDB MariaDB Servercopyright (c) 2000,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 [email protected].% |+------------------------------------------------------------------------------------------------------------------------------+| 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 ~]# MysqLWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection ID is 3Server version: 5.5.56-MariaDB MariaDB Servercopyright (c) 2000,MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show slave status\GEmpty set (0.01 sec)MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.0.10',-> MASTER_USER='repuser',-> 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: NulLMaster_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: 01 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: 0Master_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: 11 row in set (0.00 sec)MariaDB [(none)]>
说明：到此主从复制就大家完成，接下来在主库上创建用于管理端管理数据库的账号
9）在主库上创建用于管理端管理数据库的管理帐号
MariaDB [(none)]> grant all on *.* to repmanage@'192.168.0.%' IDentifIEd by 'admin'; query OK,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-node1The 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.11The 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)? yesWarning: Permanently added '192.168.0.11' (RSA) to the List of kNown [email protected]'s password: Now try logging into the machine,with "ssh '192.168.0.11'",and check in: .ssh/authorized_keysto 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)? yesWarning: Permanently added '192.168.0.10' (RSA) to the List of kNown [email protected]'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)? yesWarning: Permanently added '192.168.0.20' (RSA) to the List of kNown [email protected]'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)? yesWarning: Permanently added '192.168.0.30' (RSA) to the List of kNown [email protected]'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 ~]# rzrz 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 ~]# rzrz waiting to receive. zmodem trl+C ȡ 100% 35 KB 35 KB/s 00:00:01 0 Errorsel6.noarch.rpm...[root@test-centos6-node1 ~]# lsmha4MysqL-manager-0.56-0.el6.noarch.rpm mha4MysqL-node-0.56-0.el6.noarch.rpm[root@test-centos6-node1 ~]# yum install mha4MysqL-*Loaded plugins: fastestmirrorSetting up Install ProcessExamining mha4MysqL-manager-0.56-0.el6.noarch.rpm: mha4MysqL-manager-0.56-0.el6.noarchMarking mha4MysqL-manager-0.56-0.el6.noarch.rpm to be installedLoading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun.comExamining mha4MysqL-node-0.56-0.el6.noarch.rpm: mha4MysqL-node-0.56-0.el6.noarchMarking mha4MysqL-node-0.56-0.el6.noarch.rpm to be installedResolving 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 ResolutionDependencIEs 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 kInstalling 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 kTransaction Summary=================================================================================================Install 16 Package(s)Total size: 1.8 MTotal download size: 1.4 MInstalled size: 3.5 MIs this ok [y/N]: yDownloading 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_deBUGRunning Transaction TestTransaction Test SucceededRunning 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 ~]# lsmha4MysqL-node-0.56-0.el6.noarch.rpm[root@test-centos7-node1 ~]# yum install mha4MysqL-node-0.56-0.el6.noarch.rpm Loaded plugins: fastestmirrorExamining mha4MysqL-node-0.56-0.el6.noarch.rpm: mha4MysqL-node-0.56-0.el6.noarchMarking mha4MysqL-node-0.56-0.el6.noarch.rpm to be installedResolving DependencIEs--> Running transaction check---> Package mha4MysqL-node.noarch 0:0.56-0.el6 will be installed--> Finished Dependency ResolutionDependencIEs Resolved================================================================================================================================= Package Arch Version Repository Size=================================================================================================================================Installing: mha4MysqL-node noarch 0.56-0.el6 /mha4MysqL-node-0.56-0.el6.noarch 102 kTransaction Summary=================================================================================================================================Install 1 PackageTotal size: 102 kInstalled size: 102 kIs this ok [y/d/N]: yDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning 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=/data/mastermha/mariadb/> manager_log=/data/mastermha/mariadb/manager.log> remote_workdir=/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=repmanagepassword=adminmanager_workdir=/data/mastermha/mariadb/manager_log=/data/mastermha/mariadb/manager.logremote_workdir=/data/mastermha/mariadb/ssh_user=rootrepl_user=repuserrepl_password=adminPing_interval=1[server1]hostname=192.168.0.10candIDate_master=1[server2]hostname=192.168.0.20candIDate_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 [email protected](192.168.0.10:22) to [email protected](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 [email protected](192.168.0.10:22) to [email protected](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 [email protected](192.168.0.30:22) to [email protected](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 [email protected](192.168.0.30:22) to [email protected](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 [email protected](192.168.0.20:22) to [email protected](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 [email protected](192.168.0.20:22) to [email protected](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 = 0Tue 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:enabledTue 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:DisabledTue 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 supportedTue 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=/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 [email protected](192.168.0.10:22).. Creating /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.000002Tue 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=/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=xxxTue Jan 14 09:55:59 2020 - [info] Connecting to [email protected](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=/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=xxxTue Jan 14 09:55:59 2020 - [info] Connecting to [email protected](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 /data/mastermha/mariadb/manager.logTue 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) succeededMaster 192.168.0.10(192.168.0.10:3306) is down!Check MHA Manager logs at test-centos6-node1:/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: 0Master_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高可用实验的整个过程。
总结以上是内存溢出为你收集整理的MariaDB的备份与主从以及高可用实践全部内容，希望文章能够帮你解决MariaDB的备份与主从以及高可用实践所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）