共计 9740 个字符,预计需要花费 25 分钟才能阅读完成。
本篇内容介绍了“MYSQL 完全备份、主从复制、级联复制、半同步的知识点总结”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
mysql 完全备份
1,启用二进制日志,并于数据库分离,单独存放
vim /etc/my.cnf
添加
log_bin=/data/bin/mysql-bin
创建 /data/bin 文件夹并授权
chown mysql.mysql /data/bin
2,完成备份数据库
mysqldump -A --single-transaction --master-data=2 | xz /data/all.sql.xz
3,对数据库进行增删改
INSERT hellodb.students(stuid,name,gender,age) VALUE(27, Lujunyi , M ,30);
4, 停止 MySQL
systemctl stop mariadb.service
5,解压备份文件
unxz /data/all.sql.xz
6, 查找完全备份时二进制日志的位置
vim /data/all.sql
HANGE MASTER TO MASTER_LOG_FILE= mysql-bin.000001 , MASTER_LOG_POS=468
7,导出完成备份后的二进制日志
mysqlbinlog --start-position=468 /data/bin/mysql-bin.000001 /data/inc.sql
8,还原数据
mysql -e source /data/all.sql
mysql -e source /data/inc.sql
9,验证完成。
误删除的恢复
1,启用二进制日志并与数据库分开存放
vim /etc/my.cnf
添加
log_bin=/data/bin/mysql-bin
创建 /data/bin 文件夹并授权
chown mysql.mysql /data/bin
2,对数据库进行完全备份
mysqldump -A --single-transaction --master-data=2 | xz /data/all.sql.xz
3,对数据库进行增删改
mysql -e drop table hellodb.students
mysql -e insert hellodb.teachers value (5, wangqi ,50, M)
4, 停止服务
systemctl stop mariadb.service
5,删除数据库
rm -rf /var/lib/mysql/*
6,解压备份文件
unxz /data/all.sql.xz
7,查看备份文件,查找二进制节点
vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE= mysql-bin.000004 , MASTER_LOG_POS=521902;
8,导出二进制日志节点数据
mysqlbinlog --start-position=521902 /data/bin/mysql-bin.000004 /data/inc.sql
9,删除二进制日志节点数据中误操作的命令
vim /data/inc.sql
DROP TABLE `hellodb`.`students` /* generated by server */
10,启动服务
systemctl start mariadb.service
11. 关闭二进制日志记录
mysql -e SET sql_log_bin=off
12, 导入备份数据
mysql /data/all.sql
mysql /data/inc.sql
13, 验证完成。
主从复制
# 主服务器
1,主服务器启用二进制日志,并更改二进制目录
vim /etc/my.cnf
binlog-format=row server-id=1(主从服务器必需保证不同)
log_bin=/data/bin/mysql-bin
更改目录见上面
2,重启服务
service mysql restart
3, 创建一个账户用来复制数据的账户
mysql -e GRANT REPLICATION SLAVE ON *.* TO repluser @ 172.22.7.% IDENTIFIED BY centos
4,查看主服务器正在使用的二进制日志
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 14383 |
+------------------+-----------+
1 row in set (0.00 sec)
# 从服务器
5,更配置
vim /etc/my.cnf
server-id=2
read-only
#log-bin=/data/bin/mysql-bin
6, 启动服务
service mysql restart
7, 关联主服务
MariaDB [(none)]
CHANGE MASTER TO MASTER_HOST= 172.22.7.70 , MASTER_USER= repluser , MASTER_PASSWORD= centos , MASTER_PORT=3306, MASTER_LOG_FILE= mysql-bin.000001 , MASTER_LOG_POS=14383;
8,查看从服务器状态
show slave status\G;
9, 启动线程
start slave;
# 测试
10, 增删改主服务器数据,查看从服务器数据是否同步。
主从复制出错的解决 -sql_slave_skip_counter
#master 服务 ip=172.22.7.70
1,主服务器启用二进制日志,并更改二进制目录
vim /etc/my.cnf
binlog-format=row server-id=1(主从服务器必需保证不同)
log_bin=/data/bin/mysql-bin
更改目录见上面
2,重启服务
service mysql restart
3, 创建一个账户用来复制数据的账户
mysql -e GRANT REPLICATION SLAVE ON *.* TO repluser @ 172.22.7.% IDENTIFIED BY centos
4,查看主服务器正在使用的二进制日志
mysql -e show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 245 |
+------------------+-----------+
#slave 服务 ip=172.22.7.71
5, 修改配置文件, 启动服务
[mysqld] server-id = 2 read-only
vim /etc/my.cnf
systemctl start mariadb
# 配置错误 master 服务信息
6,配置,change master to
CHANGE MASTER TO
MASTER_HOST= 172.22.7.77 ,
MASTER_USER= wang ,
MASTER_PASSWORD= lodman ,
MASTER_PORT=3306,
MASTER_LOG_FILE=log-bin.001 ,
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
7, 查看 slave 状态
mysql -e show slave status\G
Slave_IO_State:
Master_Host: 172.22.7.77
Master_User: wang
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: log-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: ct7m1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: log-bin.001
Slave_IO_Running: No
Slave_SQL_Running: No
·········略
8,启动复制线程
mysql -e start slave
9, 再次查看 slave 状态
mysql -e show slave status\G
Slave_IO_State:
Master_Host: 172.22.7.77
Master_User: wang
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: log-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: ct7m1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: log-bin.001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
·········略
10,master 服务增删改数据
11, 查看 slave 服务是否同步,失败!
# 解决错误
12,停止并重置 slave 复制线程服务
mysql -e stop slave
mysql -e reset slave
13,配置正确的 change master to 信息
CHANGE MASTER TO MASTER_HOST= 172.22.7.70 ,
MASTER_USER= repluser ,
MASTER_PASSWORD= centos ,
MASTER_PORT=3306,
MASTER_LOG_FILE= mysql-bin.000002 ,
MASTER_LOG_POS=245;
14,查看 slave 状态
show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 172.22.7.70
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 7382
Relay_Log_File: ct7m1-relay-bin.000002
Relay_Log_Pos: 540
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
15,发现 slave 状态中 Slave_SQL_Running: No,执行下面命令更为为 YES
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.03 sec) MariaDB [(none)]show slave status\G;
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.22.7.70 Master_User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 7382 Relay_Log_File: ct7m1-relay-bin.000003 Relay_Log_Pos: 540 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ·········略
16,查看 slave 服务数据是否同步
17,同步完成。
Mysql 级联复制
在生产换进中有一种主从复制的方法主节点先将数据同步到一个中间的从节点,然后由从节点给后续的其他从节点来复制数据,这种复制方式称为级联复制。
级联复制的好处是可以极大的减轻主节点的压力
级联复制在配置时需要在中间节点上启用 log_slave_updates 的选项。
# 环境
服务器 master slave slave
系统 centos7 centos7 centos7
ip 172.22.7.70 172.22.7.70 172.22.7.71
#mater
1,主服务器启用二进制日志,并更改二进制目录
vim /etc/my.cnf
binlog-format=row server-id=1(主从服务器必需保证不同)
log_bin=/data/bin/mysql-bin
更改目录见上面
2,重启服务
service mysql restart
3, 创建一个账户用来复制数据的账户
mysql -e GRANT REPLICATION SLAVE ON *.* TO repluser @ 172.22.7.% IDENTIFIED BY centos
4,查看主服务器正在使用的二进制日志
mysql -e show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 7488 |
| mysql-bin.000003 | 402 |
+------------------+-----------+
#slave
5,修改配置文件, 并创建二进制日志目录
vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mysql-bin
binlog-format=row
read-only
log_slave_updates
server-id=2
更改目录见上
6,启动服务
systemctl restart mariadb
7,配置 change master to 信息
CHANGE MASTER TO MASTER_HOST= 172.22.7.70 ,MASTER_USER= repluser ,MASTER_PASSWORD= centos ,MASTER_PORT=3306,MASTER_LOG_FILE= mysql-bin.000003 , MASTER_LOG_POS=402;
8,启动 slave 线程
mysql -e start slave
9,查看 slave 状态
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.22.7.70
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7539
Relay_Log_File: ct7m1-relay-bin.000002
Relay_Log_Pos: 7677
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
10,在 master 上增删改数据测试查看
#slave1
11,在 slave 上将 slave 上的数据拷贝过来
mysqldump -A --single-transaction -F --master-data=1 /data/all.sql
scp /data/all.sql 172.22.7.72:/data
12,slave 创建一个账户用来复制数据的账户
mysql -e GRANT REPLICATION SLAVE ON *.* TO repluser @ 172.22.7.% IDENTIFIED BY centos
13,修改 slave1 配置
vim /etc/my.cnf
[mysqld]
read-only
server-id=3
14,启动服务
systemctl start mariadb
15,查看 slave 服务器的二进制日志
mysql -e show master logs
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 351 |
| mysql-bin.000002 | 351 |
| mysql-bin.000003 | 351 |
| mysql-bin.000004 | 25552 |
| mysql-bin.000005 | 586 |
+------------------+-----------+
16,打开 all.sql 文件对 change master on 信息加以修改
CHANGE MASTER TO MASTER_HOST= 172.22.7.71 ,MASTER_USER= repluser ,MASTER _PASSWORD= centos ,MASTER_PORT=3306,MASTER_LOG_FILE= mysql-bin.000005 , MASTER_LOG_POS=586;
17,导入 slave 数据
mysql /data/all.sql
18,启动线程
mysql -e START SLAVE;
19,查看 slave 状态
mysql -e show slave status\G;
20, 对 master 对象增删改操作,查看是否同步
21,同步完成。
MySQL 半同步
异步复制是当用户写入一条记录时,先将数据写入到主节点,然后回复用户一个写入成功的消息,然后慢慢的将数据复制到其背后的其他从节点,这样的好处是效率比较高,但是缺点也是非常明显,主服务器和从服务器的延迟过大并且主服务器突然发生异常,此时就会造成数据的丢失。
同步复制是当用户写入一条记录时,主节点将数据写入数据库,然后将数据复制给其后面的其他从节点,当所有的从节点返回数据复制成功后,主节点再回复用户数据接入成功的消息,这样做的好处是,确保了数据的安全性,但损失了效率。
半同步复制是间于同步复制和异步复制之间的一种复制方法,他的工作原理是:当用户执行写操作时,主节点会将数据发送给其后面的其他从节点,只要有一个从节点返回复制成功的消息,主节点就直接返回写入成功,如果主节点背后的从节点迟迟不返回复制成功消息,此时就会有一个超时时长,一旦达到超时时长,主节点就先返回消息告诉用户复制成功,而后将数据继续给从节点复制。
# 配置主从复制 步骤见上。
# 配置半同步
##master
1,修改配置文件启用插件
vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mariadb-bin
binlog-format=row
server-id=1
rpl_semi_sync_master_enabled
2, 重启服务,
systemctl restart mariadb
3,查看插件是否启动
SHOW GLOBAL VARIABLES LIKE %semi%
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
4,设置超时时长
SET GLOBAL rpl_semi_sync_master_timeout=3000;
Query OK, 0 rows affected (0.00 sec)
##slave
5,修改配置文件启用插件
vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mariadb-bin
binlog-format=row
server-id=2
rpl_semi_sync_master_enabled
6, 重启服务,
systemctl restart mariadb
7,查看插件是否启动
SHOW GLOBAL VARIABLES LIKE %semi%
+------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows in set (0.00 sec)
8, 启动复制线程
mysql -e START SLAVE
9,测试检查
10,同步完成
“MYSQL 完全备份、主从复制、级联复制、半同步的知识点总结”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站,丸趣 TV 小编将为大家输出更多高质量的实用文章!