1. 主从复制的环境
2. 服务器能够发邮件
#准备主从复制环境#主库
[root@db01 ~]# netstat -lntp | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13646/mysqld
[root@db01 ~]# mysqldump -uroot -pqls.123 --single-transaction -R -A >all.sql #导出所有的数据库文件
[root@db01 ~]# ll all.sql
-rw-r--r-- 1 root root 1219298 2020-04-28 09:13 all.sql
[root@db01 ~]# scp -rp all.sql root@10.0.0.170:#主库开启binlog日志及server_id
[root@db01 ~]# vim /etc/my.cnf
log_bin=mysql-bin
server_id=1
[root@db01 ~]# systemctl restart mariadb.service
[root@db01 ~]# mysql -uroot -pqls.123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h'forhelp. Type '\c' to clear the current input statement.
#创建用户 主从复制用户
MariaDB [(none)]> grant replication slave on *.* to rep@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
#查看binlog日志及所在节点
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 388 | | |
+------------------+----------+--------------+------------------+
1 row inset (0.00 sec)
MariaDB [(none)]>
#从库
[root@db02 ~]# yum install -y mariadb-server
[root@db02 ~]# systemctl start mariadb.service
[root@db02 ~]# mysqladmin password 'qls.123'#从库导入主库的数据库文件
[root@db02 ~]# mysql -uroot -pqls.123 < all.sql
[root@db02 ~]# vim /etc/my.cnf
server_id=2
[root@db02 ~]# systemctl restart mariadb.service
[root@db02 ~]# mysql -uroot -pqls.123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h'forhelp. Type '\c' to clear the current input statement.
MariaDB [(none)]> change master to
-> master_host='10.0.0.81',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=388;
Query OK, 0 rows affected (0.01 sec)
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: 10.0.0.81
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 388
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
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: 388
Relay_Log_Space: 825
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 inset (0.00 sec)
#从库写入报错 1007
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
#尝试跳过这一次的错误
MariaDB [(none)]> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
#启动从库的主从复制
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
如果出现 1158 1159 1008 1007 1062错误，请跳过此错误。
[root@db01 /service/scripts/day08]# cat master_slave.sh#!/bin/bash#定义变量
DB_User=root
DB_Pass=qls.123
IO_Status=$(mysql -u$DB_User -p$DB_Pass -e "show slave status\G" | awk '/Slave_IO_Running/{print $2}')
SQL_Status=$(mysql -u$DB_User -p$DB_Pass -e "show slave status\G" | awk '/Slave_SQL_Running/{print $2}')
#判断主从复制异常情况if [ $IO_Status == "Yes" -a $SQL_Status == "Yes" ];thenecho"数据库主从复制正常！"else#判断IO线程异常情况if [ $IO_Status == "Yes" ];thenecho"数据库主从复制IO线程正常！"elseecho"数据库主从复制IO线程报错！"
IO_Error_log=/tmp/myslq_io_error.log
echo $(date +%F"日"%H:%M"分") >$IO_Error_log
mysql -u$DB_User -p$DB_Pass -e "show slave status\G" | grep "Last_IO" >>$IO_Error_log
mail -s "数据库主从复制IO线程报错！" xxxxx@qq.com < $IO_Error_logif [ $? -eq 0 ];thenecho"数据库主从复制IO线程报错邮件发送成功！"elseecho"数据库主从复制IO线程报错邮件发送失败！"fifi#判断SQL线程异常情况if [ $SQL_Status == "Yes" ];thenecho"数据库主从复制SQL线程正常！"else
SQL_State=$(mysql -u$DB_User -p$DB_Pass -e "show slave status\G" | awk '/Last_SQL_Errno/{print $2}')
case$SQL_Statein
1007|1062|1058|1059|1008)
echo"数据库主从复制SQL线程出现${SQL_State}报错！脚本尝试跳过这次错误！"
mysql -u$DB_User -p$DB_Pass -e "stop slave;set global sql_slave_skip_counter=1;start slave;"sleep 3
SQL_Status=$(mysql -u$DB_User -p$DB_Pass -e "show slave status\G" | awk '/Slave_SQL_Running/{print $2}')
if [ $SQL_Status == "Yes" ];thenecho"脚本尝试跳过一次错误之后！数据库主从复制SQL线程正常！"echo"脚本尝试跳过一次错误之后！数据库主从复制SQL线程正常！$(date +%F"日"%H:%M"分")" | mail -s "数据库主从复制SQL线程现${SQL_State}报错！已解决！" xxxxx@qq.com
elseecho"脚本尝试跳过一次错误之后！数据库主从复制SQL线程还是报错！"
SQL_Error_log=/tmp/myslq_sql_error.log
echo $(date +%F"日"%H:%M"分") >$SQL_Error_log
mysql -u$DB_User -p$DB_Pass -e "show slave status\G" | grep "Last_SQL" >>$SQL_Error_log
mail -s "数据库主从复制SQL线程报错！" xxxxx@qq.com < $SQL_Error_logif [ $? -eq 0 ];thenecho"数据库主从复制SQL线程报错邮件发送成功！"elseecho"数据库主从复制SQL线程报错邮件发送失败！"fifi
;;
*)
echo"数据库主从复制SQL线程出现${SQL_State}报错！"
SQL_Error_log=/tmp/myslq_sql_error.log
echo $(date +%F"日"%H:%M"分") >$SQL_Error_log
mysql -u$DB_User -p$DB_Pass -e "show slave status\G" | grep "Last_SQL" >>$SQL_Error_log
mail -s "数据库主从复制SQL线程报错！" xxxxx@qq.com < $SQL_Error_logif [ $? -eq 0 ];thenecho"数据库主从复制SQL线程报错邮件发送成功！"elseecho"数据库主从复制SQL线程报错邮件发送失败！"fiesacfifi
3. 循环语句之while循环概述
while循环只有条件成立，则就会反复执行循环体里面的命令，直到条件为假时，才会结束
无限的循环下去，死循环。
while 条件测试
do
循环体
done#三种写法 whiletruedo
循环体
donewhileread line
do
循环体
done < file.txt
while [ 条件判断 ]
do
循环体
done
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现