题1
mysql的数据库备份脚本
[root@use07 ~]# cat sqlback.sh
#!/bin/bash
while true;do
#打印备份模式，全备或单备
cat<<EOF
backup mode:
full databases -- f
single databases -- s
EOF
read -p "enter f|s: " cho
#全备判断
if [ $cho == 'f' -o $cho == 'F' ];then
while true;do
#输入路径
read -p "enter dir path: " dir
#路径判断
if [ -d $dir ] && [[ "$dir" =~ ^/.* ]] && [[ "$dir" =~ .*/$ ]];then
mysqldump -A -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8mb4 --hex-blob > ${dir}full_$(date +%F).sql
[ $? -eq 0 ]&& echo "OK `ls ${dir}full_$(date +%F).sql`"
break 2
#路径不正确提示
else
echo "like this format: /tmp/"
continue
fi
done
#单备判断
elif [ $cho == 's' -o $cho == 'S' ];then
while true;do
#输入数据库名
read -p "enter database name: " dat
#数据库名判断
for i in $(mysql -e 'show databases;' | grep -Ev Da);do
if [ "$dat" == "$i" ];then
while true;do
#数据库名正确后输入路径
read -p "enter dir path: " dir
#判断路径
if [ -d $dir ] && [[ "$dir" =~ ^/.* ]] && [[ "$dir" =~ .*/$ ]];then
mysqldump -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8mb4 --hex-blob $dat > ${dir}${dat}_$(date +%F).sql
[ $? -eq 0 ]&& echo "OK `ls ${dir}${dat}_$(date +%F).sql`"
break 4
else
echo "like this format: /tmp/"
continue
fi
done
fi
done
#数据库名不存在提示
echo "${dat} is not exist"
done
#没有选择正确的备份模式
else
echo "choose please"
continue
fi
done
题2
实现mariadb数据库1主1从
安装数据库
yum install mariadb-server -y
配置主，主的ID是1，启用二进制日志
[root@use07 ~]# head -n 3 /etc/my.cnf
[mysqld]
server_id=1
log_bin
启动主库
[root@use07 ~]# systemctl restart mariadb.service
[root@use07 ~]# systemctl enable mariadb.service
配置从，主机ID是2，开启只读属性
[root@use08 ~]# head -n 3 /etc/my.cnf
[mysqld]
server_id=2
read_only
启动从库
[root@use08 ~]# systemctl restart mariadb.service
[root@use08 ~]# systemctl enable mariadb.service
确定主库当前二进制日志位置，这里由于是新安装的数据库，因此从库指定从第一个二进制日志的开始位置245开始复制。
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)
主库创建复制用户
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.247.%' identified by '123';
从库指向主库，并启动复制
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.247.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
从库查看主从状态，主要关注是Slave_IO_Running: Yes与Slave_SQL_Running: Yes
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.247.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 400
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 686
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: 400
Relay_Log_Space: 982
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)
主库查看复制进程状态，主要关注Binlog Dump进程有没启动，从库有没请求进行复制
MariaDB [(none)]> show processlist\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
Id: 3
User: repluser
Host: 192.168.247.18:43154
db: NULL
Command: Binlog Dump
Time: 206
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000
2 rows in set (0.00 sec)
题3
先完成一个1主2从配置，注意事项如下
skip_name_resolve所有节点跳过域名解析
log_bin所有节点开启
relay_log_purge=0从库不要删除中继日志
安装mha包
管理节点
yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm
主从节点
yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm
实现各节点密钥登录
[root@use05 ~]# 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:
SHA256:jqaPoImkQ0d9jilklbpGSw0wSBiJ9YDzllds4xg47Eg root@use05
The key's randomart image is:
+---[RSA 2048]----+
|*X+. .. |
|BE=+.o= |
|.= oB* . |
|. =Boo.. |
| .*.o = S |
| . * o + |
|..+ . o . |
|=o . + |
|=. o.. |
+----[SHA256]-----+
[root@use05 ~]# ssh-copy-id 192.168.247.15
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.247.15 (192.168.247.15)' can't be established.
ECDSA key fingerprint is SHA256:N9KmussTK+TG13tJhyqmJcI+HteBSVUFHXX5rsSRN04.
ECDSA key fingerprint is MD5:16:00:47:7f:1d:49:65:05:91:d4:db:a4:e6:b3:bd:f7.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.247.15's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh '192.168.247.15'"
and check to make sure that only the key(s) you wanted were added.
[root@use05 ~]# scp -r .ssh/ 192.168.247.16:/root/
The authenticity of host '192.168.247.16 (192.168.247.16)' can't be established.
ECDSA key fingerprint is SHA256:N9KmussTK+TG13tJhyqmJcI+HteBSVUFHXX5rsSRN04.
ECDSA key fingerprint is MD5:16:00:47:7f:1d:49:65:05:91:d4:db:a4:e6:b3:bd:f7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.247.16' (ECDSA) to the list of known hosts.
root@192.168.247.16's password:
id_rsa 100% 1679 535.5KB/s 00:00
id_rsa.pub 100% 392 123.4KB/s 00:00
known_hosts 100% 352 98.4KB/s 00:00
authorized_keys 100% 392 175.8KB/s 00:00
[root@use05 ~]# scp -r .ssh/ 192.168.247.17:/root/
The authenticity of host '192.168.247.17 (192.168.247.17)' can't be established.
ECDSA key fingerprint is SHA256:N9KmussTK+TG13tJhyqmJcI+HteBSVUFHXX5rsSRN04.
ECDSA key fingerprint is MD5:16:00:47:7f:1d:49:65:05:91:d4:db:a4:e6:b3:bd:f7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.247.17' (ECDSA) to the list of known hosts.
root@192.168.247.17's password:
id_rsa 100% 1679 329.0KB/s 00:00
id_rsa.pub 100% 392 241.0KB/s 00:00
known_hosts 100% 528 344.6KB/s 00:00
authorized_keys 100% 392 225.3KB/s 00:00
[root@use05 ~]# scp -r .ssh/ 192.168.247.18:/root/
The authenticity of host '192.168.247.18 (192.168.247.18)' can't be established.
ECDSA key fingerprint is SHA256:N9KmussTK+TG13tJhyqmJcI+HteBSVUFHXX5rsSRN04.
ECDSA key fingerprint is MD5:16:00:47:7f:1d:49:65:05:91:d4:db:a4:e6:b3:bd:f7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.247.18' (ECDSA) to the list of known hosts.
root@192.168.247.18's password:
id_rsa 100% 1679 876.2KB/s 00:00
id_rsa.pub 100% 392 160.6KB/s 00:00
known_hosts 100% 704 391.2KB/s 00:00
authorized_keys
主库添加mha数据库账号
ariaDB [(none)]> grant all on *.* to mhauser@'192.168.247.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
配置mha
管理节点
[root@use05 ~]# cat /etc/mha/app1.conf
[server default]
user=mhauser
password=123
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=123
ping_interval=1
[server1]
hostname=192.168.247.16
candidate_master=1
[server2]
hostname=192.168.247.17
candidate_master=1
[server3]
hostname=192.168.247.18
启动前检查mha配置
管理节点
[root@use05 ~]# masterha_check_ssh --conf=/etc/mha/app1.conf
Sat Sep 5 18:06:03 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep 5 18:06:03 2020 - [info] Reading application default configuration from /etc/mha/app1.conf..
Sat Sep 5 18:06:03 2020 - [info] Reading server configuration from /etc/mha/app1.conf..
Sat Sep 5 18:06:03 2020 - [info] Starting SSH connection tests..
Sat Sep 5 18:06:04 2020 - [debug]
Sat Sep 5 18:06:03 2020 - [debug] Connecting via SSH from root@192.168.247.16(192.168.247.16:22) to root@192.168.247.17(192.168.247.17:22)..
Sat Sep 5 18:06:03 2020 - [debug] ok.
Sat Sep 5 18:06:03 2020 - [debug] Connecting via SSH from root@192.168.247.16(192.168.247.16:22) to root@192.168.247.18(192.168.247.18:22)..
Sat Sep 5 18:06:04 2020 - [debug] ok.
Sat Sep 5 18:06:05 2020 - [debug]
Sat Sep 5 18:06:03 2020 - [debug] Connecting via SSH from root@192.168.247.17(192.168.247.17:22) to root@192.168.247.16(192.168.247.16:22)..
Sat Sep 5 18:06:04 2020 - [debug] ok.
Sat Sep 5 18:06:04 2020 - [debug] Connecting via SSH from root@192.168.247.17(192.168.247.17:22) to root@192.168.247.18(192.168.247.18:22)..
Sat Sep 5 18:06:04 2020 - [debug] ok.
Sat Sep 5 18:06:06 2020 - [debug]
Sat Sep 5 18:06:04 2020 - [debug] Connecting via SSH from root@192.168.247.18(192.168.247.18:22) to root@192.168.247.16(192.168.247.16:22)..
Sat Sep 5 18:06:04 2020 - [debug] ok.
Sat Sep 5 18:06:04 2020 - [debug] Connecting via SSH from root@192.168.247.18(192.168.247.18:22) to root@192.168.247.17(192.168.247.17:22)..
Sat Sep 5 18:06:05 2020 - [debug] ok.
Sat Sep 5 18:06:06 2020 - [info] All SSH connection tests passed successfully.
[root@use05 ~]# masterha_check_repl --conf=/etc/mha/app1.conf
Sat Sep 5 18:06:35 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep 5 18:06:35 2020 - [info] Reading application default configuration from /etc/mha/app1.conf..
Sat Sep 5 18:06:35 2020 - [info] Reading server configuration from /etc/mha/app1.conf..
Sat Sep 5 18:06:35 2020 - [info] MHA::MasterMonitor version 0.56.
Creating directory /data/mastermha/app1/.. done.
Sat Sep 5 18:06:36 2020 - [info] GTID failover mode = 0
Sat Sep 5 18:06:36 2020 - [info] Dead Servers:
Sat Sep 5 18:06:36 2020 - [info] Alive Servers:
Sat Sep 5 18:06:36 2020 - [info] 192.168.247.16(192.168.247.16:3306)
Sat Sep 5 18:06:36 2020 - [info] 192.168.247.17(192.168.247.17:3306)
Sat Sep 5 18:06:36 2020 - [info] 192.168.247.18(192.168.247.18:3306)
Sat Sep 5 18:06:36 2020 - [info] Alive Slaves:
Sat Sep 5 18:06:36 2020 - [info] 192.168.247.17(192.168.247.17:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Sep 5 18:06:36 2020 - [info] Replicating from 192.168.247.16(192.168.247.16:3306)
Sat Sep 5 18:06:36 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sat Sep 5 18:06:36 2020 - [info] 192.168.247.18(192.168.247.18:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Sep 5 18:06:36 2020 - [info] Replicating from 192.168.247.16(192.168.247.16:3306)
Sat Sep 5 18:06:36 2020 - [info] Current Alive Master: 192.168.247.16(192.168.247.16:3306)
Sat Sep 5 18:06:36 2020 - [info] Checking slave configurations..
Sat Sep 5 18:06:36 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.247.17(192.168.247.17:3306).
Sat Sep 5 18:06:36 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.247.18(192.168.247.18:3306).
Sat Sep 5 18:06:36 2020 - [info] Checking replication filtering settings..
Sat Sep 5 18:06:36 2020 - [info] binlog_do_db= , binlog_ignore_db=
Sat Sep 5 18:06:36 2020 - [info] Replication filtering check ok.
Sat Sep 5 18:06:36 2020 - [info] GTID (with auto-pos) is not supported
Sat Sep 5 18:06:36 2020 - [info] Starting SSH connection tests..
Sat Sep 5 18:06:39 2020 - [info] All SSH connection tests passed successfully.
Sat Sep 5 18:06:39 2020 - [info] Checking MHA Node version..
Sat Sep 5 18:06:39 2020 - [info] Version check ok.
Sat Sep 5 18:06:39 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sat Sep 5 18:06:40 2020 - [info] HealthCheck: SSH to 192.168.247.16 is reachable.
Sat Sep 5 18:06:40 2020 - [info] Master MHA Node version is 0.56.
Sat Sep 5 18:06:40 2020 - [info] Checking recovery script configurations on 192.168.247.16(192.168.247.16:3306)..
Sat Sep 5 18:06:40 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.56 --start_file=mariadb-bin.000001
Sat Sep 5 18:06:40 2020 - [info] Connecting to root@192.168.247.16(192.168.247.16:22)..
Creating /data/mastermha/app1 if not exists.. Creating directory /data/mastermha/app1.. done.
ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mariadb-bin.000001
Sat Sep 5 18:06:40 2020 - [info] Binlog setting check done.
Sat Sep 5 18:06:40 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sat Sep 5 18:06:40 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.247.17 --slave_ip=192.168.247.17 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Sat Sep 5 18:06:40 2020 - [info] Connecting to root@192.168.247.17(192.168.247.17:22)..
Creating directory /data/mastermha/app1/.. done.
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.
Sat Sep 5 18:06:40 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.247.18 --slave_ip=192.168.247.18 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Sat Sep 5 18:06:40 2020 - [info] Connecting to root@192.168.247.18(192.168.247.18:22)..
Creating directory /data/mastermha/app1/.. done.
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.
Sat Sep 5 18:06:40 2020 - [info] Slaves settings check done.
Sat Sep 5 18:06:40 2020 - [info]
192.168.247.16(192.168.247.16:3306) (current master)
+--192.168.247.17(192.168.247.17:3306)
+--192.168.247.18(192.168.247.18:3306)
Sat Sep 5 18:06:40 2020 - [info] Checking replication health on 192.168.247.17..
Sat Sep 5 18:06:40 2020 - [info] ok.
Sat Sep 5 18:06:40 2020 - [info] Checking replication health on 192.168.247.18..
Sat Sep 5 18:06:40 2020 - [info] ok.
Sat Sep 5 18:06:40 2020 - [warning] master_ip_failover_script is not defined.
Sat Sep 5 18:06:40 2020 - [warning] shutdown_script is not defined.
Sat Sep 5 18:06:40 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
前台启动mha
这里是在当前会话启动mha监测主从状态，也可以将进程设为后台与终端无关的启动方式，如nohup,screen之类。下面有个skipping提示是因为这里没有按mha默认文件名创建配置文件的提示。
[root@use05 ~]# masterha_manager --conf=/etc/mha/app1.conf
Sat Sep 5 18:07:52 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Sep 5 18:07:52 2020 - [info] Reading application default configuration from /etc/mha/app1.conf..
Sat Sep 5 18:07:52 2020 - [info] Reading server configuration from /etc/mha/app1.conf..
440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
