mysql存储过程备份表到文件 mysql 备份数据文件

转载

数据探索者 2024-01-10 12:31:41

文章标签 mysql存储过程备份表到文件 mysql 增量备份数据库 文章分类 MySQL 数据库

1、测试环境

软件版本

mysql-5.7.28-el7-x86_64.tar

系统环境

10.1.1.12：CentOS Linux release 7.7.1908 (Core)
10.1.1.13：CentOS Linux release 7.7.1908 (Core)

1、搭建测试环境

在10.1.1.12和10.1.1.13上分别安装mysql。

将10.1.1.12上的mysql当作旧数据库。
将10.1.1.13上的mysql当作新数据库。

2、向10.1.1.12的mysql中写入测试数据

(1)创建测试数据库和表

//登录mysql
]# /usr/local/mysql/bin/mysql -uroot -p'Apps@123'

//创建一个测试数据库
mysql> CREATE DATABASE bkdatabase;

//创建一个测试数据表
mysql> CREATE TABLE bkdatabase.bktable (name char(10), age int);

(2)向mysql中写入测试数据

//写入测试数据
mysql> insert into bkdatabase.bktable(name, age) values("hh1", 1), ("hh2", 2), ("hh3", 3);

//查看测试数据
mysql> select * from bkdatabase.bktable;
+------+------+
| name | age  |
+------+------+
| hh1  |    1 |
| hh2  |    2 |
| hh3  |    3 |
+------+------+

2、使用XtraBackup备份mysql数据库（全量+增量）

下载XtraBackup：https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
说明文档：https://docs.percona.com/percona-xtrabackup/2.4/index.html
Percona XtraBackup是一个基于MySQL的服务器的开源的热备份工具，在备份期间不会锁定数据库。
xtrabackup主要包含两个工具：xtrabackup和innobackupex

xtrabackup2.2及以前两个工具都有，主要使用innobackupex
xtrabackup2.3开始innobackupex被弃用，但仍然可以使用。xtrabackup8.0开始innobackupex完全被删除，不能再使用。
xtrabackup2.3开始主要使用xtrabackup。

Percona xtradabackup 2.4不支持备份MySQL 8.0、Percona Server for MySQL 8.0或Percona XtraDB Cluster 8.0及其以上的版本。对于数据库8.0及其以上的版本，需要使用Percona XtraBackup 8.0进行备份。

mysql 5.7及其以下的版本，需要使用Percona XtraBackup 8.0以下的版本。

Percona XtraBackup提供的一组工具有：

xtrabackup：用于备份MyISAM、InnoDB和XtraDB表的数据库。
xbcrypt：用于加密和解密备份文件。
xbstream：允许以流的形式从xbstream格式提取文件。
xbcloud：

1、XtraBackup 2.4简介

Percona XtraBackup 2.4可以备份MySQL（5.1、5.5、5.6和5.7）服务器上的InnoDB、XtraDB和MyISAM表中的数据，以及带有XtraDB的Percona服务器。
xtradabackup 2.4的优点：

快速可靠的完成数据库备份（例如，热备份，增量备份，bacula备份等）
备份期间不间断的事务处理
通过更好的压缩节省磁盘空间和网络带宽
自动备份验证
更快的恢复时间可延长正常运行时间

2、XtraBackup 8.0简介

Percona XtraBackup 8.0可以备份MySQL 8.0服务器上的InnoDB、XtraDB、MyISAM和MyRocks表中的数据，也可以备份Percona Server for MySQL with XtraDB、Percona Server for MySQL 8.0和Percona XtraDB Cluster 8.0。
XtraBackup 8.0优点：

快速可靠的完成数据库备份（例如，热备份，增量备份，bacula备份等）
备份期间不间断的事务处理
通过更好的压缩节省磁盘空间和网络带宽
自动备份验证
更快的恢复时间可延长正常运行时间
时间点恢复

3、全量备份介绍

(1)全量备份

如果目标目录不存在，xtrabackup将创建该目录。如果目录存在且为空，xtrabackup将成功。如果文件存在，它将失败（Xtrabackup不会覆盖现有的文件）。
在任何时候取消都是安全的，因为它不会修改数据库。

xtrabackup --backup --target-dir=/data/backups/

(2)准备备份

在使用xtrabackup—backup选项进行备份后，首先需要准备它，以便恢复。在准备好数据文件之前，数据文件在时间点上是不一致的，因为它们是在程序运行时的不同时间复制的，并且在此过程中可能已被更改。如果你试图用这些数据文件启动InnoDB，它会检测到损坏并自行崩溃，以防止你在损坏的数据上运行。xtrabackup -—prepare步骤使文件在某一时刻完全一致，这样你就可以在它们上运行InnoDB。
可以在任何机器上运行prepare操作。它不需要在原始服务器上，也不需要在要恢复到的服务器上。

xtrabackup --prepare --target-dir=/data/backups/

(3)恢复备份

在恢复前，需要先进行准备备份。
在恢复备份前，必须停止MySQL服务，并且清空datadir目录。不能恢复数据到正在运行的mysqld实例的datadir中(导入部分备份时除外)。

xtrabackup --copy-back --target-dir=/data/backups/

4、增量备份介绍

(1)增量备份

如果要进行增量备份，必须从全量备份开始。

#全量备份
xtrabackup --backup --target-dir=/data/backups/base
#增量备份，以全量备份为基础进行第一次增量备份
xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base
#增量备份，以第一次增量备份为基础进行第二次增量备份
xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1

(2)准备备份

在合并备份时，除最后一个增量备份外其他所有准备备份都要使用--apply-log-only参数。
--apply-log-only：不进行回滚操作

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1
xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2

(3)恢复备份

xtrabackup --copy-back --target-dir=/data/backups/base

2.1、安装XtraBackup

因为是mysql 5.7，所以使用XtraBackup 2.4进行备份。
在10.1.1.12和10.1.1.13上进行如下操作：

//(1)下载xtrabackup
]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz

//(2)解压xtrabackup
]# tar zvfx percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz

//(3)查看看xtrabackup提供的工具
]# ls -l ./percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12/bin/
lrwxrwxrwx 1 root root        10 12月  2 02:22 innobackupex -> xtrabackup
-rwxr-xr-x 1 root root  10185463 12月  2 02:22 xbcloud
-rwxr-xr-x 1 root root      3020 12月  2 02:14 xbcloud_osenv
-rwxr-xr-x 1 root root   5299580 12月  2 02:22 xbcrypt
-rwxr-xr-x 1 root root   5370640 12月  2 02:22 xbstream
-rwxr-xr-x 1 root root 202001891 12月  2 02:22 xtrabackup

2.2、备份mysql的特殊权限

在10.1.1.12进行如下操作：

//(1)创建一个具有完全备份所需的最低权限的mysql用户。
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'Backup@123';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';

//(2)授予备份用户拥有percona_schema数据库的所有权限
mysql> GRANT ALL PRIVILEGES ON percona_schema.* TO 'bkpuser'@'localhost' WITH GRANT OPTION;

//(3)重新加载权限
mysql> FLUSH PRIVILEGES;

在进行备份的时候使用--history和--incremental-history-name参数，需要使用percona_schema数据库。

第一次使用--history参数时，XtraBackup会自动在要备份的MySQL中创建percona_schema数据库。

也可以不使用percona_schema数据库，只需要在备份的时候不使用--history和--incremental-history-name参数即可。

2.3、备份数据

若不想使用percona_schema数据库，可以将相关命令做如下修改：

--incremental-history-name参数：要与--incremental参数联合使用，并与--incremental-basedir互斥。

mysql存储过程备份表到文件 mysql 备份数据文件_mysql

mysql存储过程备份表到文件 mysql 备份数据文件_数据库_02

//全量备份
]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \
    --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \
    --backup --target-dir=/data/backups/mysql/20230204-155540-full/
//第一次增量备份
]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \
    --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \
    --backup --target-dir=/data/backups/mysql/20230204-160509-incr/ --incremental-basedir=/data/backups/mysql/20230204-155540-full/
//第二次增量备份
]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \
    --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \
    --backup --target-dir=/data/backups/mysql/20230204-162009-incr/ --incremental-basedir=/data/backups/mysql/20230204-160509-incr/

View Code

1、全量备份

使用XtraBackup对MySQL进行全量+增量的备份方式，第一份备份必须是全量备份，其余备份是增量备份。
全量备份是一次备份周期的起始备份，因此全量备份中不能使用--incremental-history-name参数。
在10.1.1.12进行如下操作：

//(1)创建用于存储全量备份的目录
]# mkdir -p /data/backups/mysql/$(date "+%Y%m%d-%H%M%S")-full

//(2)执行全量备份
]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \
    --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \
    --backup --target-dir=/data/backups/mysql/20230204-155540-full/ --history=mysql-backup-20230204-155540

2、第一次增量备份

在10.1.1.12进行如下操作：

//(1)插入模拟数据
mysql> insert into bkdatabase.bktable(name, age) values("hh4", 4), ("hh5", 5), ("hh6", 6);

//(2)查看最近一次备份的--history值，用在--incremental-history-name参数中
mysql> select name from percona_schema.xtrabackup_history order by end_time desc limit 1;
+------------------------------+
| name                         |
+------------------------------+
| mysql-backup-20230204-155540 |
+------------------------------+

//(3)创建用于存储第一份增量备份的目录
]# mkdir -p /data/backups/mysql/$(date "+%Y%m%d-%H%M%S")-incr

//(4)执行第一次增量备份
]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \
    --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \
    --backup --target-dir=/data/backups/mysql/20230204-160509-incr/ --history=mysql-backup-20230204-160509 \
    --incremental --incremental-history-name=mysql-backup-20230204-155540

3、第二次增量备份

在10.1.1.12进行如下操作：

//(1)插入模拟数据
mysql> insert into bkdatabase.bktable(name, age) values("hh7", 7), ("hh8", 8), ("hh9", 9);

//(2)查看最近一次备份的--history值，用在--incremental-history-name参数中
mysql> select name from percona_schema.xtrabackup_history order by end_time desc limit 1;
+------------------------------+
| name                         |
+------------------------------+
| mysql-backup-20230204-160509 |
+------------------------------+

//(3)创建用于存储第一份增量备份的目录
]# mkdir -p /data/backups/mysql/$(date "+%Y%m%d-%H%M%S")-incr

//(4)执行第一次增量备份
]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \
    --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \
    --backup --target-dir=/data/backups/mysql/20230204-162009-incr/ --history=mysql-backup-20230204-162009 \
    --incremental --incremental-history-name=mysql-backup-20230204-160509

2.4、准备备份

1、查看备份文件

在10.1.1.12进行如下操作：

]# ls -lrt /data/backups/mysql/
drwxr-xr-x 6 root root  315 2月   4 16:00 20230204-155540-full
drwxr-xr-x 7 root root 4096 2月   4 16:13 20230204-160509-incr
drwxr-xr-x 7 root root 4096 2月   4 16:25 20230204-162009-incr

2、准备恢复xtrabackup备份的备份

在10.1.1.12进行如下操作：

//(1)准备全量备份
]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/20230204-155540-full

//(2)准备第一个增量备份
]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/20230204-155540-full --incremental-dir=/data/backups/mysql/20230204-160509-incr

//(3)准备第二个增量备份
]# xtrabackup --prepare --target-dir=/data/backups/mysql/20230204-155540-full --incremental-dir=/data/backups/mysql/20230204-162009-incr

3、准备恢复二进制日志文件中的数据

在10.1.1.12进行如下操作：

//(1)插入模拟数据
mysql> insert into bkdatabase.bktable(name, age) values("hh10", 10), ("hh11", 11), ("hh12", 12);

//(2)查看备份文件中的xtrabackup_binlog_info，获取xtrabackup备份到了哪个位置（必须先使用xtrabackup准备备份，才能通过查看完全备份查看，否则要查看最后一个增量备份）
]# cat /data/backups/mysql/20230204-155540-full/xtrabackup_binlog_info
mysql-bin.000003	7496	aa5097d9-a45c-11ed-ae4b-000c299ac374:1-19

//(3)导出二进制日志文件中还没有备份的数据
]# /usr/local/mysql/bin/mysqlbinlog --start-position=7496 /usr/local/mysql/logs/mysql-bin.000003 > mysql-bin.sql

4、将准备好的备份文件复制到10.1.1.13

在10.1.1.12进行如下操作：

]# scp -r /data/backups/mysql/20230204-155540-full root@10.1.1.13:/root
]# scp mysql-bin.sql root@10.1.1.13:/root

2.5、恢复备份

在10.1.1.13进行如下操作

1、查看准备好的备份文件

]# ls -l ./
drwxr-xr-x  7 root root     4096 2月   4 17:13 20230204-155540-full
-rw-r--r--  1 root root     6226 2月   4 17:14 mysql-bin.sql

2、停止mysql服务

]# ps -ef | grep mysql
]# kill -9 PID

3、清空datadir目录

]# rm -rf /usr/local/mysql/data/*

4、恢复xtrabackup备份的数据

]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --copy-back --target-dir=./20230204-155540-full

5、启动msyql服务

//修改数据目录的属主属组
]# chown apps.apps -R /usr/local/mysql/data/

//启动mysql服务
]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/conf/my.cnf &

6、恢复二进制中没有备份的数据

]# /usr/local/mysql/bin/mysql -uroot -p'Apps@123' < ./mysql-bin.sql

3、使用mysqldump备份mysql数据库

当数据量比较大时，使用mysqldump完全备份会很慢。建议，数据量较小时使用mysqldump。

3.1、mysqldump命令

//导出指定的数据表。注意：导出文档中没有创建数据的语句，即还原时要手动创建数据库
mysqldump [OPTIONS] database_name [table1] [table2] ... 
//导出一个或多个数据库。注意：还原时不需要手动创建数据库
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//导出所有数据库。注意：还原时不需要手动创建数据库
mysqldump [OPTIONS] --all-databases [OPTIONS]

-B, --databases：导出一个或多个数据库。注意用法上的区别，没有table。所有name参数都被视为数据库名称。'USE db_name;'将包含在输出中。
-A, --all-databases：导出所有数据库。
-d, --no-data：只导出数据库表结构，不导出数据。
-w, --where=name：只导出给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
--master-data=[0|1|2]：默认值是0，建议使用2。

0：不记录二进制日志文件及事件位置；
1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器。
2：以CHANGE MASTER TO的方式记录位置，但默认会被注释。

-x, --lock-all-tables：锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables选项。
-l, --lock-tables：锁定当前导出的数据表，而不是一下子锁定全部数据库下的表。(默认为on;使用--skip-lock-tables禁用。)
-F, --flush-logs：开始导出之前进行日志滚动。请注意：如果同时导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所有表同时被锁定。因此，如果想要导出和刷新日志在同一时刻，应该使用--lock-all-tables或者--master-data和--flush-logs。
--single-transaction：通过在单个事务中导出所有表来创建一致的快照。仅适用于存储在支持多版本的存储引擎中的表(目前只有InnoDB支持);导出不保证与其他存储引擎一致。当——single-transaction导出正在进行时，为了确保导出文件有效(正确的表内容和二进制日志位置)，其他连接不应该使用以下语句:ALTER table、DROP table、RENAME table、TRUNCATE table，因为一致性快照不会与它们隔离。选项自动关闭--lock-all-tables
-e, --extended-insert：使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。(Defaults to on; use --skip-extended-insert to disable.)
--insert-ignore：使用INSERT IGNORE语句插入行。
--replace：使用REPLACE INTO取代INSERT INTO。
--opt：等同于-add-drop-table、--add-locks、--create-options、--quick、--extended-insert、--lock-tables、--set-charset、--disable-keys。默认开启，可以用--skip-opt禁用。
-E, --events：导出事件，导出数据库上的事件调度器。
-R, --routines：导出存储过程和存储函数。
--triggers：导出触发器。

3.2、使用mysqldump命令进行温备

//备份指定的一个数据库
mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 mysql > ./mysql-backup-db.sql
//备份指定的数据库中的多个数据表
mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 mysql user db > ./mysql-backup-tbs.sql

//备份多个数据库
mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 --databases mysql performance_schema > ./mysql-backup-dbs.sql

//备份所有数据库
mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 --all-databases > ./mysql-backup-dball.sql

分步执行温备：

flush tables with read lock;      #刷新表并施加读锁
flush logs;                       #滚动日志
show binary logs;                 #查看备份时使用的日志文件
mysqldump DB_NAME [tb1] [tb2];    #进行备份
unlock tables;                    #释放锁

3.3、备份Innodb数据库

//对InnoDB的数据库进行温备，必须先锁定表。不能立即做快照，必须等Innodb存储引擎的缓冲区中数据都同步到磁盘
mysql> FLUSH TABLES WITH READ LOCK;

//查看Innodb存储引擎的状态
mysql> show engine innodb status;

//InnoDB的热备份：基于MVCC机制。--single-transaction是将隔离级别设置为REPEATABLE READ
mysqldump -uroot -proot --single-transactio --flush-logs --master-data=2 --databases DB_NAME1 DB_NAME2 > ./mysql-backup-innodb.sql

#                                                                                                                                      #

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：论文系统技术架构对比论文架构分析

下一篇：运维业务架构运维业务知识图谱

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql存储过程备份表到文件 mysql 备份数据文件

mysql存储过程备份表到文件 mysql 备份数据文件

1、测试环境

1、搭建测试环境

2、向10.1.1.12的mysql中写入测试数据

2、使用XtraBackup备份mysql数据库（全量+增量）

1、XtraBackup 2.4简介

2、XtraBackup 8.0简介

3、全量备份介绍

4、增量备份介绍

2.1、安装XtraBackup

2.2、备份mysql的特殊权限

2.3、备份数据

1、全量备份

2、第一次增量备份

3、第二次增量备份

2.4、准备备份

1、查看备份文件

2、准备恢复xtrabackup备份的备份

3、准备恢复二进制日志文件中的数据

4、将准备好的备份文件复制到10.1.1.13

2.5、恢复备份

1、查看准备好的备份文件

2、停止mysql服务

3、清空datadir目录

4、恢复xtrabackup备份的数据

5、启动msyql服务

6、恢复二进制中没有备份的数据

3、使用mysqldump备份mysql数据库

3.1、mysqldump命令

3.2、使用mysqldump命令进行温备

3.3、备份Innodb数据库

51CTO博客