1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。
#!/bin/bash
echo "请选择备份方式:
M:mysqldump
X:xtrabackup"
read bk
case $bk in
M | m)
mysqldump -A --single-transaction --master-data=2 > /all.sql && echo "mysqldump is ok";;
X | x)
xtrabackup --backup --target-dir=/backup/ &> /dev/null && echo "xtrabackup is ok";;
*)
echo "exit";;
esac
#!/bin/bash
. /etc/init.d/functions
username=root
passname=
mysqldumpdir=/data/mysqldump
xtrabackupdir=/data/xtrabackup
[ -d $mysqldumpdir ] || mkdir $mysqldumpdir
read -p "1) mysqldump
2) xtrabackup
Please input a number[1|2]: " bk
case $bk in
1)
mysqldump -u${username} -p${passname} -A -F -E -R -q \
--single-transaction --master-data=1 --flush-privileges \
--triggers --default-character-set=utf8 \
> $mysqldumpdir/`date +"%F-%T"`.sql && \
action "`date +"%F-%T"` data backup to $mysqldumpdir" true || action "backup data" false
;;
2)
xtrabackup --user=$username --password=$passname \
--backup --target-dir=$xtrabackupdir \
&> /dev/null && action "`date +"%F-%T"` data backup to $xtrabackupdir" true \
|| action "backup data" false
;;
*)
echo "exit"
;;
esac
2、配置Mysql主从同步
配置mysql主服务器
~]# vim /etc/my.cnf
[mysqld]
server_id=7
log_bin
查看主服务器二进制日志开始大小
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 418 |
| mariadb-bin.000002 | 759 |
+--------------------+-----------+
2 rows in set (0.00 sec)
创建mysql从服务复制帐号
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.1.1.%' identified by 'replpass';
启动mysql服务
systemctl start mariadb
配置mysql从服务器
vim /etc/my.cnf
[mysqld]
server_id=8
read_only
启动mysql服务
systemctl start mariadb
使用有复制权限的用户账号连接至主服务器，并启动复制线程
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.1.1.7', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=759;
MariaDB [(none)]> start slave;
查看slave状态
MariaDB [(none)]> show slave status\G
测试
主服务器添加数据后从服务器能够立刻查询到
3、使用MHA实现Mysql高可用。
###监控主机192.168.101.21安装
###mha4mysql-manager-0.55-0.el6.noarch.rpm
###mha4mysql-node-0.54-0.el6.noarch.rpm
[root@mha-manager ~]# yum install mha4*.rpm -y
###在被监控主机192.168.101.20(master),192.168.101.22(slave1),192.168.101.23(slave2)上安装
###mha4mysql-node-0.54-0.el6.noarch.rpm
[root@master ~]# yum install mha4mysql-node-0.54-0.el6.noarch.rpm -y
[root@slave1 ~]# yum install mha4mysql-node-0.54-0.el6.noarch.rpm -y
[root@slave2 ~]# yum install mha4mysql-node-0.54-0.el6.noarch.rpm -y
###在管理节点192.168.101.21建立配置文件
[root@mha-manager ~]# mkdir /etc/mha/
[root@mha-manager ~]# vim /etc/mha/app1.cnf
[server default]
user=mhauser
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.101.20
candidate_master=1
[server2]
hostname=192.168.101.22
candidate_master=1
[server3]
hostname=192.168.101.23
###配置master
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=20
log-bin
skip_name_resolve
###启动mysql
[root@master ~]# systemctl start mariadb
###创建主从同步帐号
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.101.%' identified by 'centos';
###创建mha管理帐号
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.101.%' identified by 'centos';
###配置slave1
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server_id=22
log-bin
read_only
skip_name_resolve
relay_log_purge=0
###实现slave
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.101.20', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;
###配置slave2
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
server_id=23
log-bin
read_only
skip_name_resolve
relay_log_purge=0
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.101.20', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;
###测试
[root@mha-manager ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Sun Aug 9 08:42:25 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 9 08:42:25 2020 - [info] Reading application default configurations from /etc/mha/app1.cnf..
Sun Aug 9 08:42:25 2020 - [info] Reading server configurations from /etc/mha/app1.cnf..
Sun Aug 9 08:42:25 2020 - [info] Starting SSH connection tests..
Sun Aug 9 08:42:26 2020 - [debug]
Sun Aug 9 08:42:25 2020 - [debug] Connecting via SSH from root@192.168.101.20(192.168.101.20:22) to root@192.168.101.22(192.168.101.22:22)..
Sun Aug 9 08:42:25 2020 - [debug] ok.
Sun Aug 9 08:42:25 2020 - [debug] Connecting via SSH from root@192.168.101.20(192.168.101.20:22) to root@192.168.101.23(192.168.101.23:22)..
Sun Aug 9 08:42:26 2020 - [debug] ok.
Sun Aug 9 08:42:27 2020 - [debug]
Sun Aug 9 08:42:25 2020 - [debug] Connecting via SSH from root@192.168.101.22(192.168.101.22:22) to root@192.168.101.20(192.168.101.20:22)..
Sun Aug 9 08:42:26 2020 - [debug] ok.
Sun Aug 9 08:42:26 2020 - [debug] Connecting via SSH from root@192.168.101.22(192.168.101.22:22) to root@192.168.101.23(192.168.101.23:22)..
Sun Aug 9 08:42:27 2020 - [debug] ok.
Sun Aug 9 08:42:27 2020 - [debug]
Sun Aug 9 08:42:26 2020 - [debug] Connecting via SSH from root@192.168.101.23(192.168.101.23:22) to root@192.168.101.20(192.168.101.20:22)..
Sun Aug 9 08:42:26 2020 - [debug] ok.
Sun Aug 9 08:42:26 2020 - [debug] Connecting via SSH from root@192.168.101.23(192.168.101.23:22) to root@192.168.101.22(192.168.101.22:22)..
Sun Aug 9 08:42:27 2020 - [debug] ok.
Sun Aug 9 08:42:27 2020 - [info] All SSH connection tests passed successfully.
[root@mha-manager ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
Sun Aug 9 08:42:40 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 9 08:42:40 2020 - [info] Reading application default configurations from /etc/mha/app1.cnf..
Sun Aug 9 08:42:40 2020 - [info] Reading server configurations from /etc/mha/app1.cnf..
Sun Aug 9 08:42:40 2020 - [info] MHA::MasterMonitor version 0.55.
Creating directory /data/mastermha/app1/.. done.
Sun Aug 9 08:42:41 2020 - [info] Dead Servers:
Sun Aug 9 08:42:41 2020 - [info] Alive Servers:
Sun Aug 9 08:42:41 2020 - [info] 192.168.101.20(192.168.101.20:3306)
Sun Aug 9 08:42:41 2020 - [info] 192.168.101.22(192.168.101.22:3306)
Sun Aug 9 08:42:41 2020 - [info] 192.168.101.23(192.168.101.23:3306)
Sun Aug 9 08:42:41 2020 - [info] Alive Slaves:
Sun Aug 9 08:42:41 2020 - [info] 192.168.101.22(192.168.101.22:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Sun Aug 9 08:42:41 2020 - [info] Replicating from 192.168.101.20(192.168.101.20:3306)
Sun Aug 9 08:42:41 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Aug 9 08:42:41 2020 - [info] 192.168.101.23(192.168.101.23:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Sun Aug 9 08:42:41 2020 - [info] Replicating from 192.168.101.20(192.168.101.20:3306)
Sun Aug 9 08:42:41 2020 - [info] Current Alive Master: 192.168.101.20(192.168.101.20:3306)
Sun Aug 9 08:42:41 2020 - [info] Checking slave configurations..
Sun Aug 9 08:42:41 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.101.22(192.168.101.22:3306).
Sun Aug 9 08:42:41 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.101.23(192.168.101.23:3306).
Sun Aug 9 08:42:41 2020 - [info] Checking replication filtering settings..
Sun Aug 9 08:42:41 2020 - [info] binlog_do_db= , binlog_ignore_db=
Sun Aug 9 08:42:41 2020 - [info] Replication filtering check ok.
Sun Aug 9 08:42:41 2020 - [info] Starting SSH connection tests..
Sun Aug 9 08:42:43 2020 - [info] All SSH connection tests passed successfully.
Sun Aug 9 08:42:43 2020 - [info] Checking MHA Node version..
Sun Aug 9 08:42:44 2020 - [info] Version check ok.
Sun Aug 9 08:42:44 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sun Aug 9 08:42:44 2020 - [info] HealthCheck: SSH to 192.168.101.20 is reachable.
Sun Aug 9 08:42:45 2020 - [info] Master MHA Node version is 0.54.
Sun Aug 9 08:42:45 2020 - [info] Checking recovery script configurations on the current master..
Sun Aug 9 08:42:45 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.55 --start_file=mariadb-bin.000001
Sun Aug 9 08:42:45 2020 - [info] Connecting to root@192.168.101.20(192.168.101.20)..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mariadb-bin.000001
Sun Aug 9 08:42:45 2020 - [info] Master setting check done.
Sun Aug 9 08:42:45 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sun Aug 9 08:42:45 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.101.22 --slave_ip=192.168.101.22 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.55 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Sun Aug 9 08:42:45 2020 - [info] Connecting to root@192.168.101.22(192.168.101.22: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.000002
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sun Aug 9 08:42:45 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.101.23 --slave_ip=192.168.101.23 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.55 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Sun Aug 9 08:42:45 2020 - [info] Connecting to root@192.168.101.23(192.168.101.23: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.000002
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sun Aug 9 08:42:46 2020 - [info] Slaves settings check done.
Sun Aug 9 08:42:46 2020 - [info]
192.168.101.20 (current master)
+--192.168.101.22
+--192.168.101.23
Sun Aug 9 08:42:46 2020 - [info] Checking replication health on 192.168.101.22..
Sun Aug 9 08:42:46 2020 - [info] ok.
Sun Aug 9 08:42:46 2020 - [info] Checking replication health on 192.168.101.23..
Sun Aug 9 08:42:46 2020 - [info] ok.
Sun Aug 9 08:42:46 2020 - [warning] master_ip_failover_script is not defined.
Sun Aug 9 08:42:46 2020 - [warning] shutdown_script is not defined.
Sun Aug 9 08:42:46 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
###开始监听mysql服务器, 当master服务器关机后,slave1升级为主
[root@mha-manager ~]# masterha_manager --conf=/etc/mha/app1.cnf
Sun Aug 9 08:42:55 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 9 08:42:55 2020 - [info] Reading application default configurations from /etc/mha/app1.cnf..
Sun Aug 9 08:42:55 2020 - [info] Reading server configurations from /etc/mha/app1.cnf..
Sun Aug 9 08:43:21 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 9 08:43:21 2020 - [info] Reading application default configurations from /etc/mha/app1.cnf..
Sun Aug 9 08:43:21 2020 - [info] Reading server configurations from /etc/mha/app1.cnf..
[root@mha-manager ~]#
###slave1服务器升级为主,同步状态消失, slave2同步指向slave1服务器
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
