遇到的问题：
# 查看slave状态show slave status\G显示IO线程为连接：
Slave_IO_Running: NO
解决思路：
1、检查change master to里用户名、密码、日志位置等信息； 如果不行就用reset slave all 清除所有同步信息；
2、如果以前该主机做过其他主机的冷备，那么有可能是/var/lib/mysql目录(二进制安装找二进制的目录)下的auto.cnf 里的UUID和其他主机一样了，删除该文件即可
# 复制错误解决方法
SET GLOBAL sql_slave_skip_counter = N N是个数，意思是跳过N个错误
或者：
[mysqld]
slave_skip_errors=1007|ALL #此处的1007是show slave status里可以查看到（Last_Errno: 1007），或者写ALL 跳过全部错误
1、mysqldump完全备份和还原
[root@rocky8 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin #开启log-bin二进制日志
[root@rocky8 ~]#mysqldump -uroot -A -F --single-transaction --master-data=2 | gzip > /data/all-`date +%F`.sql.gz
#-A备份所有数据库 -F备份前滚动日志 --single-transaction是开启单事务，--master-data=2
#--master-data 阻止事务回滚 1是开启，用于主从，2是不开启，用于单机
[root@rocky8 ~]#scp /data/all-2022-08-31.sql.gz root@10.0.0.18:/data ## 拷贝备份文件至其他地方
gzip -d all-2022-08-31.sql.gz #解压备份文件
systemctl start mysqld.service
mysql
set sql_log_bin=off; #临时关闭二进制日志功能
source /data/all-2022-08-31.sql #导入数据库文件
set sql_log_bin=on; #开启二进制日志功能
2、利用二进制日志，还原数据库最新状态
[root@rocky8 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log_bin=/data/mysql/mysqllog #开启log-bin二进制日志
[root@rocky8 ~]# mysqldump -uroot -A -F --single-transaction --default-character-set=utf8mb4 --master-data=2 --flush-privileges > /data/mysql_all.sql #完全备份
[root@rocky8 ~]# mysql
mysql> show master logs; #查看现在二进制日志文件和大小
mysqllog.000001 | 156
mysql> use hellodb
mysql> insert students(name,age)values('a',20); #新增条目
mysql> insert teachers (name,age)value('b',20);
mysql> drop table teachers; #删除teachers表
mysql> insert students (name,age)values('c',20);
mysql> exit
[root@rocky8 data]# mysqlbinlog mysql/mysqllog.000001 > inc.sql #把二进制文件导出至inc.sql文件
[root@rocky8 data]#sed -i '/^DROP TABLE/d' inc.sql #删除文件内删除动作
[root@rocky8 ~]# mysql
mysql> set sql_log_bin=0; #临时停止二进制日志
mysql> source /data/mysql_all.sql # 导入完全备份
mysql> source /data/inc.sql #导入二进制日志文件的动作
mysql> select * from teachers; #查看是否还原
mysql> set sql_log_bin=1; #开启二进制日志或者退出重进
3、主从复制
# master：
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log_bin=/data/mysql/mysqllog
[root@master ~]#systemctl restart mysqld.service
[root@master ~]#mysql
mysql> show master logs; #查看二进制文件和位置
+-----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysqllog.000001 | 2494 | No |
| mysqllog.000002 | 156 | No |
+-----------------+-----------+-----------+
mysql> create user repluser@'10.0.0.%' identified by '123456'; #创建复制用户
mysql> grant replication slave on *.* to repluser@'10.0.0.%'; #授权复制用户
# slave：
[root@slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
log_bin
[root@slave8 ~]#systemctl restart mysqld.service
[root@slave8 ~]#mysql
mysql> CHANGE MASTER TO #通过help change maste to 可以查询到，稍微改下就能用
-> MASTER_HOST='10.0.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysqllog.000002',
-> MASTER_LOG_POS=156,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.03 sec)
mysql> show slave status;
mysql> start slave; #开启slave 即可
mysql> show slave status;
...
Slave_IO_Running: Yes #显示yes即连通
Slave_SQL_Running: Yes #显示yes即运行
...
Seconds_Behind_Master: 0
....
mysql> show processlist
4、新增一台从服务器实现一主二从
#master：
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/all1.sql#完全备份
[root@master ~]#scp /data/all1.sql root@10.0.0.28: #复制完全备份到新从服务器节点
#新slave1：
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
log_bin
[root@slave1 ~]#systemctl start mysqld.service
[root@slave1 ~]#vim all1.sql
CHANGE MASTER TO #原来有 ，回车换行，插入下边内容
MASTER_HOST='10.0.0.8', #增加
MASTER_USER='repluser', #增加
MASTER_PASSWORD='123456', #增加
MASTER_PORT=3306, #增加
MASTER_LOG_FILE='mysqllog.000003', MASTER_LOG_POS=156; #原来有
[root@slave1 ~]#mysql
mysql> source /root/all1.sql #导入主的完全备份
mysql> start slave;
5、级联复制
# master:
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log_bin=/data/mysql/mysqllog #二进制日志文件自定义到单独的分区
[root@master ~]#systemctl restart mysqld.service
[root@master ~]#mysql
# 授权和上边主从复制一样
mysql> show grants for repluser@'10.0.0.%'; #查询授权用户信息
+---------------------------------------------------------+
| Grants for repluser@10.0.0.% |
+---------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repluser`@`10.0.0.%` |
+---------------------------------------------------------+
1 row in set (0.00 sec)
[root@master ~]#mysqldump -uroot -A -F --single-transaction --master-data=1 > /data/all2.sql
[root@master ~]#scp /data/all2.sql root@10.0.0.18:/data
[root@master ~]#scp /data/all2.sql root@10.0.0.28:/data
# slave：
[root@slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
log-bin
log_slave_updates #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加
read_only=on
[root@slave ~]#systemctl restart mysqld.service
[root@slave ~]#vim /data/all2.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysqllog.000005', MASTER_LOG_POS=156;
[root@slave ~]#mysql
mysql> set sql_log_bin=0;
mysql> source /data/all2.sql
mysql> show master logs; #记录二进制日志，下一节点
mysql> set sql_log_bin=1;
mysql> start slave;
#slave-slave：
[root@slave-slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
log_bin
read_only
[root@slave-slave ~]#systemctl restart mysqld.service
[root@slave-slave ~]#vim /data/all2.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.18', #中间节点的ip
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysqllog.000005', MASTER_LOG_POS=156;
[root@slave-slave ~]#mysql
mysql> source /data/all2.sql
mysql> start slave;
6、主主复制
- 优点：两个节点，都可以更新数据，并且互为主从
- 缺点： 容易数据不一致
两个节点同时创建数据库 ，会导致复制冲突
# maste1：
[root@master1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin=/data/mysql/mysql-bin #记得建立目录并更改属主属组为mysql
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
[root@rocky8 ~]# systemctl restart mysqld.service
[root@master1 ~]# mysql
mysql> show master logs;
mysql-bin.000043 | 156 | No
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
# master2:
[root@rocky8 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
log-bin=/data/mysql/mylog #记得建立目录并更改属主属组为mysql
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
mysql> show master logs;
mylog.000002 | 156 | No
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000043',
MASTER_LOG_POS=156,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show master logs;
+--------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------+-----------+-----------+
| mylog.000001 | 179 | No |
| mylog.000002 | 705 | No |
+--------------+-----------+-----------+
## master1 ：
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mylog.000002',
MASTER_LOG_POS=705,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
7、分库备份
[root@rocky8 ~]# for i in `mysql -e 'show databases' |egrep -vi '^(database|information_schema|performance_schema|sys)$'`
> do
> mysqldump -B $i | gzip > /data/$i.sql.gz
> done
8、脚本
8-1、冷备+还原
#!/bin/bash
#
#********************************************************************
#Author: wangdayu
#FileName: mysql_autoback.sh
#URL: https://blog.51cto.com/dayu
#Description: The test script
#Copyright (C): 2022 All rights reserved
#********************************************************************
. /etc/init.d/functions
read -p "请输入mysql备份到那台主机的IP地址（并且确保不需要输入ssh密码）：" IP
if [[ $IP =~ ^(([1-9]|[1-9][0-9]{1,2}|2[0-4][0-9]|25[1-5]|0).){3}([1-9]|[1-9][0-9]{1,2}|2[0-4][0-9]|25[1-5]|0)$ ]];then
echo "开始备份"
else
echo "IP地址输入错误"
fi
#IP="10.0.0.28"
mysqldir="/var/lib/mysql"
bindir="/data/mysql"
conffile="/etc/my.cnf.d/mysql-server.cnf"
Allback="mysql_`date +%F_%H-%M`"
Binback="mysql_bin_`date +%F_%H-%M`"
lockfile=/data/mylock
serverIP=`hostname -I | awk -F"." '{print $4}'`
sqlgzip=`ls |egrep -o 'mysql_[0-9].*.tar.gz'`
bingzip=`ls |egrep -o 'mysql_bin.*.tar.gz'`
lock(){
if [ -e ${lockfile} ];then
echo "另一个程序正在备份，退出"
exit 10
else
touch ${lockfile}
fi
}
unlock(){
if [ -e ${lockfile} ];then
rm -rf ${lockfile} &> /dev/null
else
exit 11
fi
}
back(){
lock
systemctl stop mysqld &> /dev/null && action "mysql已停止" || { action "服务停止失败，退出备份" false ; exit 1; }
tar zcf /data/$Allback.tar.gz $mysqldir &> /dev/null && action "mysql已备份至/data下" || { action "备份失败，退出备份" false ;exit 2; }
tar zcf /data/$Binback.tar.gz $bindir &> /dev/null && action "mysql二进制日志已备份至/data下" || { action "二进制日志备份失败，退出备份" false ;exit 3; }
rsync /data/$Allback.tar.gz $IP: &> /dev/null && action "备份文件复制至${IP}家目录" || { action "复制${IP}失败" false ;exit 4; }
rsync /data/$Binback.tar.gz $IP: &> /dev/null && action "二进制日志备份文件复制至${IP}家目录" || { action "二进制日志复制${IP}失败" false ;exit 5; }
rsync ${conffile} $IP: &> /dev/null && action "配置文件复制至${IP}家目录" || { action "配置文件复制${IP}失败" false ;exit 5; }
unlock
echo -e "启动mysql服务\n"; systemctl start mysqld &> /dev/null && action "mysql已启动" || { action "服务启动失败，请检查" false ; exit 6; }
}
restore(){
systemctl stop mysqld &> /dev/null && action "mysql已停止" || { action "服务停止失败，退出还原" false ; exit 1; }
lock
cd
rm -rf /var/lib/mysql/* &> /dev/null
[ -e ${bindir} ] || mkdir -p ${bindir}
chown -R mysql.mysql ${bindir}
tar xf ${sqlgzip} &> /dev/null && action "${sqlgzip}解压完成" || { action "解压失败，退出还原" false ; exit 1; }
mv var/lib/mysql/* ${mysqldir} &> /dev/null && action "mysql备份已复制至${mysqldir}下" || { action "复制失败，退出还原" false ;exit 2; }
rm -rf var
tar xf ${bingzip} &> /dev/null
mv data/mysql/* ${bindir} &> /dev/null && action "mysql二进制日志备份已复制至${bindir}下" || { action "日志复制失败，退出还原" false ;exit 2; }
rm -rf ${mysqldir}/auto.cnf &> /dev/null && action "auto.cnf已删除" || action "删除失败，有可能启动失败，请检查" false
mv -f mysql-server.cnf /etc/my.cnf.d/mysql-server.cnf
sed -i "s/server-id=8/server-id=${serverIP}/" /etc/my.cnf.d/mysql-server.cnf &> /dev/null && action "server-id已更改" || action "更改失败，有可能启动失败，请检查" false
unlock
systemctl start mysqld && action "mysql服务已启动，恭喜还原成功" || action "服务启动失败，请检查配置文件、二进制日志、auto.cnf文件等" false
}
PS3="请输入选择："
select menu in 冷备份 恢复 退出;do
case $menu in
冷备份)
back
;;
恢复)
restore
;;
退出)
exit
;;
*)
echo "输入错误"
;;
esac
done
8-2、mysqldump完全备份
#!/bin/bash
#
#********************************************************************
#Author: wangdayu
#Date: 2022-09-01
#FileName: mysqldump.sh
#URL: https://blog.51cto.com/dayu
#Description: The test script
#Copyright (C): 2022 All rights reserved
#********************************************************************
lockfile="/data/mysqllock"
user="root"
password="123456"
backdir="/data/all_`date +%F_%H-%M`.sql"
lock(){
if [ -e $lockfile ];then
echo "有另一个程序在备份，退出"
exit 1
else
touch $lockfile
fi
}
unlock(){
if [ -e $lockfile ];then
rm -rf ${lockfile}
else
exit 2
fi
}
dump(){
lock
mysqldump -u${user} -p${password} -A -F --single-transaction --master-data=2 | gzip > ${backdir}.gz && echo "已备份至${backdir}"
unlock
}
dump
8-3、分库备份
#!/bin/bash
#
#********************************************************************
#Author: wangdayu
#Date: 2022-09-01
#FileName: mysqldump.sh
#URL: https://blog.51cto.com/dayu
#Description: The test script
#Copyright (C): 2022 All rights reserved
#********************************************************************
lockfile="/data/mysqllock"
user="root"
password="123456"
backdir="/data"
lock(){
if [ -e $lockfile ];then
echo "有另一个程序在备份，退出"
exit 1
else
touch $lockfile
fi
}
unlock(){
if [ -e $lockfile ];then
rm -rf ${lockfile}
else
exit 2
fi
}
dump(){
lock
for i in `mysql -uroot -p"${password}" -e 'show databases' | egrep -iv '^(database|information_schema|performance_schema|sys)$'`;do
mysqldump -uroot -p"${password}" -B $i | gzip > ${backdir}/${i}_`date +%F`.gz
scp ${backdir}/${i}_`date +%F`.gz root@10.0.0.18:
done
unlock
}
dump
