MySQL数据备份如何做？逻辑备份工具MySQLdump案例讲解

挖数网精选

446
文章

0
评论

2020-04-2702:05:00 评论 892 9264字

摘要

我们知道对数据进行备份很重要，出现非正常操作可以进行对数据进行恢复，下面我们就来使用一下mysql数据库自带的一个逻辑备份工具mysqldump。本文分类详细，希望这篇文章能够带给你满满收获。

我们知道对数据进行备份很重要，出现非正常操作可以进行对数据进行恢复，下面我们就来使用一下mysql数据库自带的一个逻辑备份工具mysqldump

一. 简单概述

mysqldump：mysql数据自带的一个备份命令

1. 语法格式

mysqldump -u用户名 -p 数据库名 > 备份的文件名

2. mysqldump逻辑备份的工作原理

mysqldump命令备份数据的过程，实际是把数据从mysql库里以逻辑的sql语句形式直接输出或生成备份文件的过程
恢复的时候就是把sql语句在数据库里面执行一遍的过程

3. mysqldump帮助文档

[root@ctos3 ~]# mysqldump --help > 1.txt

[root@ctos3 ~]# cat 1.txt

注：该部分剩余代码内容过多，以单独整理成文档，小伙伴添加客服领取。

4. mysqldump备份使用场景

迁移或者升级数据库时
增加从库时候
因为硬件或特殊异常情况，主库或从库宕机
人为的DDL,DML语句，主从库没办法了，所有库都会执行
跨机房灾备，需要备份

二. mysqldump的一些关键参数

-B --databases：指定多个库。增加建库语句和use语句
--compact：去掉注释，适合调试输出
-A --all-databases：备份所有库
-F：刷新binlog 日志
--master-data：增加binlog日志文件名及对应的位置点
-x --lock-all-tables：全局的读锁，会阻止对所有表的写入操作
-l：该锁不会阻止读，也不会阻止新的数据插入
-d：只备份表结构
-t：只备份数据
--signle-transaction：适合innodb事务数据库备份

在介绍相关参数的时候，我们先来了解一下-e参数

作用：加上-e参数可以直接在外面交互式查看数据库里面的东西，而不用登录到数据库里面

例一：查看数据库

[root@cots3 ~]# mysql -uroot -p -e "show databases;"Enter password:

+----------------------+

| Database |

+-----------------------+

| information_schema |

| data |

| db |

| mysql |

| performance_schema |

| sys |

+------------------------+

例二：可以执行多个操作，如进入某个数据库然后查看表

[root@cots3 ~]# mysql -uroot -p -e "use data;show tables;"Enter password:

+----------------+

| Tables_in_data |

+----------------+

| Student |

+----------------+

例三：查看数据库连接情况，也可以show full processlist查看完整的语句

[root@cots3 ~]# mysql -uroot -p -e "show processlist;"Enter password:

+----+------+-----------+------+---------+------+----------+------------------+

+----+------+-----------+------+---------+------+----------+------------------+

+----+------+-----------+------+---------+------+----------+------------------+

参数介绍：

id：进程
user：用户
host：连接的主机
db：使用的哪个库
command：执行了什么命令
time：多长时间
state：状态
info：信息

1. -B参数

作用：如果删除了数据库，不加-B参数,备份回来是会报错的，除非将数据库创建回来，备份时候加了-B参数会有自动建库语句

例子：我们来演示以下加-B和不加-B的效果

注：演练部分代码过多，已整理成文档，请小伙伴添加客服领取。

2. -d参数：只备份表结构

例子：只备份data库的所有表的结构

[root@cots3 ~]# mysqldump -uroot -p --compact -d data

Enter password:

/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `Student` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

`age` varchar(16) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `test` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;

查看test表结构

[root@cots3 ~]# mysqldump -uroot -p --compact -d data test

Enter password: /*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `test` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;

提示：--compact参数会提供较少的输出（对调试很有用）

3. -t参数：只输出数据

[root@cots3 ~]# mysqldump -uroot -p --compact -t data

Enter password:

INSERT INTO `Student` VALUES (1,"aa","19"),(2,"bb","20"),(3,"张冲","21");

4. 可以使用gzip对备份的数据进行压缩

好处：使用压缩备份的话将会缩小文件大小，减少占用空间

[root@cots3 ~]# mysqldump -uroot -p -B data | gzip > /opt/mysql.bak_sql.gz

Enter password:

[root@cots3 ~]# ls -lh /opt/total 8.0K-rw-r--r-- 1 root root 2.6K Mar 12 06:32 mysql.bak_B.sql-rw-r--r-- 1 root root 851 Mar 12 07:56 mysql.bak_sql.gz

三. 备份库

1. 备份单个库

[root@cots3 ~]# mysqldump -uroot -p -B data > /opt/mysql.bak_data.sql

Enter password:

2. 备份多个库

[root@cots3 ~]# mysqldump -uroot -p -B data db | gzip > /opt/mysql.bak.sql.gz

Enter password:

提示：-B参数是关键，在-B参数后指定库名，空格隔开备份多个库

注意：在备份多个数据库的时候最好使用root去备份，要不然如果使用别的用户取备份，在创建用户的时候如果没有指定那个库，那么就会相对应的备份不了

演示效果：

[root@cots3 ~]# mysql -uroot -p -e "grant all privileges on db.* to test@localhost identified by "guoke123";" #创建一个test用户，指定管理db库

Enter password:

[root@cots3 ~]# mysqldump -utest -p -B data db | gzip > /opt/test_bak1.sql.gz #备份到其他库就会报错，没有权限

Enter password:

mysqldump: Got error: 1044: Access denied for user "test"@"localhost" to database "data" when selecting the database

[root@cots3 ~]# mysqldump -utest -p -B db | gzip > /opt/test_bak1.sql.gz #备份自己管理的就可以

Enter password:

3. 分库备份

简单理解：分库就是将数据库里面的库分开来进行备份

注意点：每个库都可以用对应备份的库作为库名结尾加.sql

分库备份的意义：有时一个企业的数据库里会有多个库，例如（www,guoke,blog），但是有时候出问题的可能只是某一个库，如果在备份时把所有的库都备份成一个数据文件的话，恢复某一个库的数据时就比较麻烦了

备份方法：

使用一条命令完成

[root@cots3 ~]# mkdir /opt/back

Enter password:

[root@cots3 ~]# ls -l /opt/back/total 232-rw-r--r-- 1 root root 851 Mar 12 08:14 data.sql.gz-rw-r--r-- 1 root root 1212 Mar 12 08:14 db.sql.gz-rw-r--r-- 1 root root 229070 Mar 12 08:14 mysql.sql.gz

提示：为什么是输入四次密码呢，因为第一次是登录的密码，其余3次是备份3个库的时候使用

命令解释：首先使用grep过滤出想要备份的库，然后使用sed来进行替换，-r是支持扩展表达式

如果库少的话就一个库对一个应该文件，一个一个来，如果库多久写脚本，用for循环

[root@cots3 ~]# mkdir /opt/bak

[root@cots3 ~]# cat fenku.sh

#!/bin/bashfor dbname in `mysql -uroot -p"guoke123" -e "show databases;" | grep -Evi "database|infor|per|sys"`do

mysqldump -uroot -p"guoke123" --events -B $dbname |gzip > /opt/bak/${dbname}.sql.gz

done

[root@cots3 ~]# ls -l /opt/bak/ #查看效果

total 232-rw-r--r-- 1 root root 868 Mar 12 08:23 data.sql.gz-rw-r--r-- 1 root root 1230 Mar 12 08:23 db.sql.gz-rw-r--r-- 1 root root 229088 Mar 12 08:23 mysql.sql.gz

四. 备份表

1. 备份单个表

语法格式：mysqldump -u 用户名 -p 数据库名表名 > 备份的文件名

例子：

[root@cots3 ~]# mysqldump -uroot -p db test > /opt/mysql_test.sql

Enter password:

[root@cots3 ~]# ls -l /opt/ | grep mysql_test-rw-r--r-- 1 root root 1934 Mar 12 08:26 mysql_test.sql

提示：备份一个表的时候不要加-B,要不然所有表都会出来，就会报错

2. 备份多个表

语法格式：mysqldump -u 用于名 -p 数据库名表名1 表名2 > 备份的文件名

例子：

[root@cots3 ~]# mysqldump -uroot -p db guoke test > /opt/mysql_table.sql

3. 使用脚本进行分库分表备份

[root@ctos3 ~]# cat bak.sh

#!/bin/bash

DIR=/backup

[ ! -d $DIR ] && mkdir $DIR

cd $DIRfor dbname in $DBLISTdo

TABLIST=$($MYLOG "show tables from $dbname;" | sed 1d)for tabname in $TABLISTdo

mkdir -p $DIR/$dbname

$MYDUMP $dbname $tabname --events |gzip > $DIR/${dbname}/${tabname}_$(date +%F_%T).sql.gz

done

备份的效果

[root@ctos3 ~]# ls -l /backup/total 0

drwxr-xr-x. 2 root root 45 Mar 13 09:14 datadrwxr-xr-x. 2 root root 48 Mar 13 09:14 db

[root@ctos3 ~]# ls -l /backup/data/total 4-rw-r--r--. 1 root root 742 Mar 13 09:14 test_2020-03-13_09:14:26.sql.gz

[root@ctos3 ~]# ls -l /backup/db/total 4-rw-r--r--. 1 root root 699 Mar 13 09:14 student_2020-03-13_09:14:27.sql.gz

五. 恢复数据

1. 一般的恢复数据库命令

[root@cots3 ~]# mysql -uroot -p < /opt/mysql_test1.sql #备份时加-B参数恢复方法

[root@cots3 ~]# mysql -uroot -p test < /opt/mysql_test1.sql #备份时不加-B参数方法

2. 恢复分库分表备份的数据

思路：先将所有库找出来，然后使用for循环将其恢复。

提示：如果备份的时候是压缩了，可以先解压，或者可以使用sed过滤数据库名字出来，使用for循环恢复

例子：

（1）首先备份所有数据库

Enter password:

[root@cots3 ~]# ls -l /opt/back/total 8-rw-r--r-- 1 root root 851 Mar 12 08:48 data.sql.gz-rw-r--r-- 1 root root 1212 Mar 12 08:48 db.sql.gz

（2）将数据库删除，进行解压缩再进行恢复

mysql> drop database data;

Query OK, 2 rows affected (0.02 sec)

mysql> drop database db;

Query OK, 3 rows affected (0.02 sec)

（3）将备份的文件解压缩

[root@cots3 ~]# cd /opt/back/[root@cots3 back]# ls

data.sql.gz db.sql.gz

[root@cots3 back]# gunzip *[root@cots3 back]# ls

data.sql db.sql

（4）使用for循环恢复

[root@cots3 ~]# for dbname in `ls /opt/back/*.sql`;do mysql -uroot -p < ${dbname};done

Enter password:

3. 使用source恢复数据库

方法：登录数据库mysql>source 备份文件

例子：

[root@cots3 bak]# mysql -uroot -p -e "drop database data;" #将data库删除

mysql> system ls /opt/bak

data.sql db.sql mysql.sql

mysql> source /opt/bak/data.sql #进行恢复

六. 全量备份与增量备份

1. 全量备份

全量备份就是把数据库中所有的数据进进行备份

例子：

[root@cots3 ~]# mysqldump -uroot -p --events -F -A -B |gzip > /opt/back/mysqlback_$(date +%F).sql.gz #备份所有库

Enter password:

[root@cots3 ~]# ls -l /opt/back/mysqlback_2020-03-12.sql.gz

-rw-r--r-- 1 root root 186470 Mar 12 10:01 /opt/back/mysqlback_2020-03-12.sql.gz

[root@cots3 ~]# mysqldump -uroot -p --events -F -B data | gzip > /opt/back/mysqlbak_$(date +%F).sql.gz #备份单个库

Enter password:

2. 增量备份

增量备份就是上一次全量备份之后，到下一次全备的中间的备份就是增量备份

企业场景全量备份和增量备份部署：

中小公司，全量一般是每天一次，业务流量低估执行全备，备份时会锁表

按天备份的优缺点：

优点：恢复时间短，维护成本低

缺点：占用空间多，占用系统资源多，经常锁表影响用户体验

大公司周备，每周六00点一次全量，下周日-下周六00点前都是增量

按周备份的优缺点：

优点：占用空间小，占用系统资源少，无需锁表，用户体验好点

缺点：维护成本高，恢复麻烦，时间长

3. 增量恢复

利用二进制日志binlog和全备进行的恢复过程，称为增量恢复

增量恢复的应用场景：

人为操作数据库SQL语句破坏主库
只有一个主库

增量恢复的必要条件：开启MySQL的log-bin日志功能

/etc/my.cnf

[mysqld]

log_bin=mysql_bin

server-id=1

七. 使用mysqlbinlog来增量恢复

增量恢复主要是靠binlog日志来进行恢复，mysqlbinlog是用来解析mysql的binlog日志的。

mysqlbinlog的日志作用：用来记录MySQL内部增删改查等对MySQL数据库有更新的内容的记录

提示：查看binlog日志需要使用mysqlbinlog打开

注：该部分剩余代码内容过多，以单独整理成文档，小伙伴添加客服领取。

恢复方法：

1. 基于位置点的增量恢复

[root@ctos3 data]# pwd/application/mysql/data

[root@ctos3 data]# ls -l | grep bin-rw-r-----. 1 mysql mysql 1035 Mar 13 08:44 mysql_bin.000001-rw-r-----. 1 mysql mysql 154 Mar 13 08:44 mysql_bin.000002-rw-r-----. 1 mysql mysql 38 Mar 13 08:44 mysql_bin.index

#指定开始位置到结束位置，从pos466开始到531结束

[root@ctos3 data]# mysqlbinlog --no-defaults mysql_bin.000001 --start-position=466 --stop-position=531 -r pos.sql

[root@ctos3 data]# ls -l | grep pos.sql-rw-r-----. 1 root root 936 Mar 13 08:47 pos.sql

#指定开始位置到文件结束

[root@ctos3 data]# mysqlbinlog --no-defaults mysql_bin.000001 --start-position=531 -r pos531-end.sql

[root@ctos3 data]# ls -l | grep 531-rw-r-----. 1 root root 2450 Mar 13 08:49 pos531-end.sql

#从文件开头到指定位置结束

#提示：关键参数为at，指定开始和结束的位置

2. 指定时间来恢复

[root@ctos3 data]# mysqlbinlog --no-defaults mysql-bin.000001 --start-datetime="2020-1-22 17:30:40" --stop-datetime="2020-3-22 17:30:58" -r time.sql

八. 生产场景不同引擎备份命令

myisam：

[root@ctos3 ~]# mysqldump -uroot -p -A -B --master-data=2 -x --events | gzip > /opt/all.sql.gz

innodb：

[root@ctos3 ~]# mysqldump -uroot -p -A -B --master-data=2 --events --single-transaction | gzip > /opt/all_1.sql.gz

End.

作者：guoke-boy

来源：博客园

https://www.cnblogs.com/guoke-boy/p/12458249.html

本文为转发分享，转载请联系原作者

历史上的今天

4 月

我的微信公众号
微信扫一扫

我的微信公众号
微信扫一扫

2025 年 2 月
一	二	三	四	五	六	日
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28

历史上的今天

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

发表评论 取消回复

登录 注册 找回密码

发表评论取消回复

登录注册找回密码