ProxySQL备份策略

最新推荐文章于 2022-01-14 15:10:11 发布
最新推荐文章于 2022-01-14 15:10:11 发布
阅读量722 收藏
点赞数
分类专栏： ProxySQL
原文链接：https://mydbops.wordpress.com/2019/05/18/proxysql-series-proxysql-backup-startegies/
版权

ProxySQL Series : ProxySQL Backup Startegies

介绍

我们深知ProxySQL是MySQL的领先SQL感知代理之一。 在本博客中，我将解释ProxySQL的备份和还原策略。 我认为，关于该主题的博客仍然结构不完善。

如果您正在寻找我们的ProxySQL系列的其他文章：

ProxySQL真的需要备份吗?

  • 您已经在单个ProxySQL下配置了产品服务器。 有人在AWS控制台中意外终止了代理服务器，现在真的需要ProxySQL备份吗？
  • 您有来自DEV团队的请求，要使用现有的相同配置再构建一个ProxySQL，您准备好手动执行新ProxySQL设置配置的所有命令（复杂规则）了吗？
  • 意外地，您的DC崩溃了，您需要在其他DC中构建ProxySQL，您将如何在短时间内做到这一点？

ProxySQL备份策略–

作为一名DBA，我认为以下四种策略在备份ProxySQL方面起着至关重要的作用。

  • sqlite3备份
  • 物理快照
  • 配置文件备份
  • mysqldump

在进入本主题之前，我将简要介绍一下我的测试环境。 我在ProxySQL中配置了5台服务器（1个主服务器3个从属服务器1个备份节点）。

Master：192.168.33.25

Slaves：192.168.33.26，192.168.33.27，192.168.33.28

Backup：192.168.33.29

mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers;
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname      | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100          | 192.168.33.25 | 3306 | ONLINE | master  |
| 100          | 192.168.33.26 | 3306 | ONLINE | slave1  |
| 100          | 192.168.33.27 | 3306 | ONLINE | slave2  |
| 100          | 192.168.33.28 | 3306 | ONLINE | slave3  |
| 100          | 192.168.33.29 | 3306 | ONLINE | backup  |
+--------------+---------------+------+--------+---------+
5 rows in set (0.00 sec)

让我们进入实际实验

Sqlite3 backup

Sqlite是ProxySQL的后端数据库。 Sqlite3是用于在Sqlite数据库上执行备份的客户端工具。 这也有助于查看数据文件中的可用内容，而无需还原备份。

Example

[root@mydbopslabs25 proxysql]# ls /var/lib/proxysql/
mysql_server.sql  proxysql.db proxysql.log  proxysql.pid proxysql_stats.db
[root@mydbopslabs25 proxysql]# sqlite3 proxysql.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from mysql_servers;
100|192.168.33.25|3306|ONLINE|1|0|1000|0|0|0|master
100|192.168.33.26|3306|ONLINE|1|0|1000|0|0|0|slave1
100|192.168.33.27|3306|ONLINE|1|0|1000|0|0|0|slave2
100|192.168.33.28|3306|ONLINE|1|0|1000|0|0|0|slave3
100|192.168.33.29|3306|ONLINE|1|0|1000|0|0|0|backup
sqlite> .quit

以下是使用Sqlite3执行逻辑备份和还原的过程。

cmd -

[root@mydbopslabs25 proxysql]# sqlite3 proxysql.db .dump > data.sql 

Backup file output -

[root@mydbopslabs25 proxysql]# cat data.sql | head -n5
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT NOT NULL DEFAULT 3306 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1 , compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) );

INSERT INTO "mysql_servers" VALUES(100,'192.168.33.25',3306,'ONLINE',1,0,1000,0,0,0,'master');

INSERT INTO "mysql_servers" VALUES(100,'192.168.33.26',3306,'ONLINE',1,0,1000,0,0,0,'slave1');

备份还原

[root@mydbopslabs25 proxysql]# mv data.sql /
[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# sqlite3 /var/lib/proxysql/proxysql.db < /data.sql 
[root@mydbopslabs25 proxysql]# 
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-02 20:51:46 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032  -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;"
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname      | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100          | 192.168.33.25 | 3306 | ONLINE | master  |
| 100          | 192.168.33.26 | 3306 | ONLINE | slave1  |
| 100          | 192.168.33.27 | 3306 | ONLINE | slave2  |
| 100          | 192.168.33.28 | 3306 | ONLINE | slave3  |
| 100          | 192.168.33.29 | 3306 | ONLINE | backup  |
+--------------+---------------+------+--------+---------+

是的，它运行正常。 这是一个逻辑备份，我们可以在RUNTIME上使用此方法而无需关闭ProxySQL。

Physical snapshot

这是我们用来进行冷备份的常用方法。 以下是此备份过程中涉及的步骤。

  • 关闭ProxySQL
  • 拷贝data directory到备份目录
  • 启动ProxySQL
[root@mydbopslabs25 proxysql]# pwd
/var/lib/proxysql
[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# cp -r /var/lib/proxysql/* /backup_snapshot/
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-02 20:59:33 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@mydbopslabs25 proxysql]# ls -lrth /backup_snapshot/
total 168K
-rw------- 1 root root 108K May  2 20:59 proxysql_stats.db
-rw------- 1 root root  15K May 2 20:59 proxysql.log
-rw-r--r-- 1 root root  42K May 2 20:59 proxysql.db

下面的步骤是恢复过程

[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# pwd
/var/lib/proxysql
[root@mydbopslabs25 proxysql]# rm -rf /var/lib/proxysql/*
[root@mydbopslabs25 proxysql]# cp -r /backup_snapshot/* /var/lib/proxysql/
[root@mydbopslabs25 proxysql]# chown -R proxysql:proxysql /var/lib/proxysql/

[root@mydbopslabs25 proxysql]# ls -lrth
total 168K
-rw------- 1 proxysql proxysql  15K May 2 21:02 proxysql.log
-rw-r--r-- 1 proxysql proxysql  42K May 2 21:02 proxysql.db
-rw------- 1 proxysql proxysql 108K May  2 21:02 proxysql_stats.db

[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-02 21:03:02 [INFO] Using config file /etc/proxysql.cnf
DONE!

[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032  -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;"
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname      | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100          | 192.168.33.25 | 3306 | ONLINE | master  |
| 100          | 192.168.33.26 | 3306 | ONLINE | slave   |
| 100          | 192.168.33.27 | 3306 | ONLINE | slave1  |
| 100          | 192.168.33.28 | 3306 | ONLINE | slave2  |
| 100          | 192.168.33.29 | 3306 | ONLINE | backup  |
+--------------+---------------+------+--------+---------+

在不停止ProxySQL的情况下拍摄快照可能会导致备份损坏。

注意：确保以相同的ProxySQL版本还原备份，以避免架构结构上的冲突。

Config file backup

我个人非常喜欢这种备份方法。 ProxySQL提供了我们可以在运行时完成的最大配置，而无需重新启动ProxySQL。 无需配置文件条目。 但是，每当在ProxySQL中进行更改时，我建议也将这些条目添加到ProxySQL配置文件中，并进行备份。

这是我的备份ProxySQL配置文件，我在“ / etc /”下添加了

cat /etc/proxysql.cnf
.
.
mysql_servers =
(
        { address="192.168.33.25" , port=3306 , hostgroup=100, status="ONLINE", comment="master" },
        { address="192.168.33.26" , port=3306 , hostgroup=100, status="ONLINE", comment="slave1" },
        { address="192.168.33.27" , port=3306 , hostgroup=100, status="ONLINE", comment="slave2" },
        { address="192.168.33.28" , port=3306 , hostgroup=100, status="ONLINE", comment="slave3" },
        { address="192.168.33.29" , port=3306 , hostgroup=100, status="ONLINE", comment="backup" }
)
:

现在，我将使用更新的配置文件启动ProxySQL，

#Linux shell 

[root@mydbopslabs25 proxysql]# service proxysql start 
Starting ProxySQL: 2019-05-03 00:02:01 [INFO] Using config file /etc/proxysql.cnf DONE! 
[root@mydbopslabs25 proxysql]# ls 
[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032  -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;" 

+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname      | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100          | 192.168.33.25 | 3306 | ONLINE | master  |
| 100          | 192.168.33.26 | 3306 | ONLINE | slave   |
| 100          | 192.168.33.27 | 3306 | ONLINE | slave1  |
| 100          | 192.168.33.28 | 3306 | ONLINE | slave2  |
| 100          | 192.168.33.29 | 3306 | ONLINE | backup  |
+--------------+---------------+------+--------+---------+

这是在ProxySQL中实现备份的最简单方法之一。我们可以将这种方法用于新的部署。

Mysqldump

我们可以使用mysqldump执行ProxySQL备份。 但是，MySQL和Sqlite3数据库之间有一组不同的管理命令。 不过，我们可以通过忽略MySQL转储文件中不需要的对象来实现此目的。

Example

cmd-
[root@mydbopslabs25 proxysql]# mysqldump -h127.0.0.1 -uadmin -padmin -P6032 --no-tablespaces --no-create-info --no-create-db --skip-triggers main mysql_servers | grep -i 'lock tables\|insert into' > mysqldump_data.sql

[root@mydbopslabs25 proxysql]# ls -lrth mysqldump_data.sql 
-rw-r--r-- 1 root root 470 May  3 00:20 mysqldump_data.sql

output-
[root@mydbopslabs25 proxysql]# cat mysqldump_data.sql 
LOCK TABLES `mysql_servers` WRITE;
INSERT INTO `mysql_servers` VALUES ('100','192.168.33.25','3306','ONLINE','1','0','1000','0','0','0','master'),('100','192.168.33.26','3306','ONLINE','1','0','1000','0','0','0','slave'),('100','192.168.33.27','3306','ONLINE','1','0','1000','0','0','0','slave1'),('100','192.168.33.28','3306','ONLINE','1','0','1000','0','0','0','slave2'),('100','192.168.33.29','3306','ONLINE','1','0','1000','0','0','0','backup');
UNLOCK TABLES;

恢复mysqldump文件，

[root@mydbopslabs25 proxysql]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mydbopslabs25 proxysql]# pwd
/var/lib/proxysql
[root@mydbopslabs25 proxysql]# rm -rf 
[root@mydbopslabs25 proxysql]# service proxysql start
Starting ProxySQL: 2019-05-03 00:26:23 [INFO] Using config file /etc/proxysql.cnf
DONE!

[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032 -vvv < /mysqldump_data.sql 
--------------
LOCK TABLES `mysql_servers` WRITE
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
INSERT INTO `mysql_servers` VALUES ('100','192.168.33.25','3306','ONLINE','1','0','1000','0','0','0','master'),('100','192.168.33.26','3306','ONLINE','1','0','1000','0','0','0','slave'),('100','192.168.33.27','3306','ONLINE','1','0','1000','0','0','0','slave1'),('100','192.168.33.28','3306','ONLINE','1','0','1000','0','0','0','slave2'),('100','192.168.33.29','3306','ONLINE','1','0','1000','0','0','0','backup')
--------------
Query OK, 5 rows affected (0.00 sec)
--------------
UNLOCK TABLES
--------------
Query OK, 0 rows affected (0.00 sec)
Bye

[root@mydbopslabs25 proxysql]# mysql -h127.0.0.1 -uadmin -padmin -P6032  -e "select hostgroup_id,hostname,port,status,comment from mysql_servers;"
+--------------+---------------+------+--------+---------+
| hostgroup_id | hostname      | port | status | comment |
+--------------+---------------+------+--------+---------+
| 100          | 192.168.33.25 | 3306 | ONLINE | master  |
| 100          | 192.168.33.26 | 3306 | ONLINE | slave   |
| 100          | 192.168.33.27 | 3306 | ONLINE | slave1  |
| 100          | 192.168.33.28 | 3306 | ONLINE | slave2  |
| 100          | 192.168.33.29 | 3306 | ONLINE | backup  |
+--------------+---------------+------+--------+---------+

如上所述，由于在Sqlite3中大多数情况下不支持MySQL转储文件中的SQL语句（代码），因此恢复起来有点困难。

我相信该博客肯定会为正在为其ProxySQL服务器寻找备份解决方案的人提供帮助。

Fan_-_
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql数据库备份和同步_mysql数据库备份，主从复制及半同步复制
weixin_31081677的博客
01-19 279
1、使用mysqldump备份数据库并通过备份及二进制日志还原数据(备份完后再写入数据,然后再删库)mysqldump -A --single-transaction -F --master-data=2 > /data/backup/mysql.sql-A备份所有数据库，--single-stransaction开启事务备份 -F 刷新日志 --master-data=2记录二进制日志位置...
MySQL——使用mysqldump命令备份
m0_72882572的博客
11-06 1万+
使用mysqldump命令备份 mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。本节将介绍mysqldump命令的工作原理和使用方法。
proxysql配置信息清理、备份、还原
07-18 619
1、紧接上文，配置过程请看上文2、进入proxysql数据目录 [root@12c proxysql]# cd /var/lib/proxysql/ [root@12c proxysql]# ll total 344 -rw-rw-r-- 1 proxysql proxysql 1050 Jul 18 13:45 proxysql-ca.pem -rw-rw-r-- 1 proxysql pr...
Linux随笔19-MySQL主从复制、Percona XtraBackup实现全量和增量备份、ProxySQL实现读写分离
ikkyphoenix的专栏
08-13 618
Contents1. MySQL5.7实现主从复制2. Percona XtraBackup实现全量和增量备份3. MyCAT实现读写分离4. References 1. MySQL5.7实现主从复制 通过主从复制，主要解决数据备份以及高可用和故障切换以及恢复的问题。主从复制有几种形式： 1主1从 ：一台master节点以及一台slave节点，master节点执行数据写入，slave节点设置为只读。 1主多从 ：上述形式的扩展，通过设置多个从节点，可以更好的提升数据安全性。 级联复制（1主1中间1从） ：
利用SQLSERVER代理实现自动备份
weixin_48315099的博客
02-01 1681
利用SQLSERVER代理达到自动备份 自行记录，如有问题欢迎讨论，作者也是个新手。 打开SQLSERVER 代理服务 新建维护计划向导 如图： 点击【下一步】 输入 名称、说明后，即可点击【下一步】 勾选 【备份数据库（完整）】，点击【下一步】 再次点击【下一步】 选完数据库后，点击【下一步】 点击【下一步】 点击【完成】 新建（修改）每天执行时间： 根据自己的需求选择触发时间。 SQL Server 代理 ------作业 当完成【维护计划向导】时，作业下方会出
cpp-ProxySQL高性能的MySQL代理
08-16
- **负载均衡**：ProxySQL可以根据预定义的策略，如轮询、最小连接数等，将客户端请求智能地分发到后端MySQL实例，从而实现数据读写分离和减轻单个数据库的压力。 - **故障检测与自动切换**：通过实时监控后端...
MySQL&Redis安装使用规范
最新发布
12-08
9. **恢复管理**：在需要恢复时，根据备份策略选择相应的恢复方法，如使用Xtrabackup进行物理恢复，或利用Binlog进行增量恢复。 总结来说，MySQL与Redis的安装使用规范旨在通过合理架构设计、高可用策略、中间件...
MYSQL 考试1
08-03
冷备份和热备份是两种不同的备份策略： - 冷备份：在数据库完全关闭的情况下进行，确保数据一致性，但期间无法提供服务。 - 热备份：在数据库运行时进行，可能会影响性能，但允许在备份过程中继续提供服务。 查看...
04-JAVA面试题_第四阶段.pdf
08-20
11. **数据库数据备份策略** - **冷备份**：定期备份数据库文件，适用于非实时数据恢复需求。 - **热备份**：通过主从复制，实时同步主库的变更到从库，提高数据安全性。 - **双机热备**：两台数据库互为主从，...
mysql主从复制读写分离的配置方法详解
09-09
MySQL主从复制和读写分离是数据库高可用性和性能优化的重要技术。主从复制确保数据在多个服务器间同步，而读写分离则可以提高...在实际环境中，还需考虑安全性、监控、备份等多方面因素，以确保整个系统的健壮性。
SQL Server数据库自动备份
热门推荐
qq_56948714的博客
01-14 2万+
SQL Server数据库自动备份
ProxySQL新版本对MGR的原生支持
老叶茶馆
03-31 2549
导读译者：张锐志（微信号：516160507），知数堂第10期MySQL学员原文出处：http://lefred.be/content/mysql-group-replication-native-support-in-proxysql/原文作者：lefredProxySQL在MySQL的代理和负载均衡中一直处于领先地位。其中包含了诸如缓存查询，多路复用，流量镜像，读写分离，路由等等的强力功能。在
基于mgr的 高可用 + 可扩展 + 读写分离 + 热备份的方案思考
awesomeQ的专栏
11-23 1085
首先mysql group replication(MGR)的复制，本身是支持高可用的。 读写分离 + 可扩展 使用 proxysql+innodb cluster+mysql shell 来实现。新增加一台备机时可以使用XtraBackup备份。然后在proxysql中进行配置，无需重启proxysql即可实现无感知添加slave。 proxysql的高可用使用proxysql cluste...
ProxySQL实现双主双从读写分离部署
运维小lemon的博客
03-14 1666
文章目录1.环境说明2.安装proxysql和安装mysql和配置主从2.1给四台mysql安装mariadb2.2安装proxysql2.2.1配置mysql-master和mysql-master22.2.2登录proxysql管理端2.2.3使用 insert 语句添加 mysql 主从到 mysql_servers 表中，其中：hostgroup_id 1 表示写组，2表示读组2.2.4在...
使用MySQL Proxy解决MySQL主从同步延迟
06-03 1121
MySQL的主从同步机制非常方便的解决了高并发读的应用需求，给Web方面开发带来了极大的便利。但这种方式有个比较大的缺陷在于MySQL的同步机制是依赖Slave主动向Master发请求来获取数据的，而且由于服务器负载、网络拥堵等方面的原因，Master与Slave 之间的数据同步延迟是完全没有保证的。短在1秒内，长则几秒、几十秒甚至更长都有可能。 由于数据延迟问题的存在，当应用程序在Mast...
proxysql配置读写分离策略和权重调整一例
csd753111111的博客
10-09 488
操作系统：CentOS release 6.9 主库: 192.168.140.51 从库1: 192.168.140.52 read_only=on 从库2: 192.168.16.15...
mysql dump 影响业务_mysqldump对mysql数据库的影响
weixin_33410492的博客
02-04 1134
对于想入门或者初级，中级mysql数据库运维人员，了解mysqldump对mysql数据库的影响，是非常必要的，当执行mysqldump命令之后，mysql后台执行了什么，下面就带大家看看，在这里使用general_log进行分析1.首先的开启数据库的general_log，如下所示[root@localhost] 17:30:41 [(none)]>show variables like ...
数据库自动备份（Slq Server 代理作业）
鱼珊珊
04-29 5461
主要能完成的工作有： 1．        数据库的定期（周期性）备份 2．        定时完成某些动作（工作） 举例：需要完成的工作是每天凌晨1：00把数据库test，备份一份放在D:\fullbak，备份的数据库命名为test+yyymmdd(备份当时的时间).bak。 --数据库自动备份（SQL Server 代理->  作业-> 右键新建作业->常规里面写名称和所有者->
SQL自动备份 备份到本地或者远程服务器
lilinn01的专栏
03-17 6141
0.1 在SQLServer2008 --> 备份数据库 --> 安全 --> 新建用户 --> 用户名                                        选择该windows用户 （确保 --> 机器名/人名 --> 是正确的，因为有换机器名的可能性）                                      数据库角色成员身份 --> db_ow
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值