2023年7月30日发(作者：)

MySQL数据备份与还原（mysqldump）

⼀ mysqldump指令实现数据备份、mysql指令实现数据还原　　经常有朋友问我，DBA到底是做什么的，百科上说：数据库管理员（Database Administrator，简称DBA），是从事管理和维护数据库管理系统(DBMS)的相关⼯作⼈员的统称，属于运维⼯程师的⼀个分⽀，主要负责业务数据库从设计、测试到部署交付的全⽣命周期管理。DBA的核⼼⽬标是保证数据库管理系统的稳定性、安全性、完整性和⾼性能。

　　百科出来的内容总是那么的专业，让⼈看完之后的感觉是很解释的很好，但是我没有看懂或者似懂⾮懂的模糊感。。哈哈，其实我认为，DBA主要做三件事情：1.保证公司的数据不丢失不损坏 2.提⾼数据库管理系统的⼯作性能　　对于现在的公司来讲，数据变得尤为重要，可以说最重要，你的⽹站可以⽆法访问，服务器可以宕机，但是数据绝对不能丢，所以我们本节内容就冲着如果保护好数据⽽来的。本篇博客的内容并不是很深⼊，毕竟不是专业的DBA，只是作为超哥的讲课内容，让⼤家学⼀些数据备份的基本操作，⼊门级别咱们只讲⼀下mysqldump指令，⾄于如果做主从复制，双机热备，数据库⾼可⽤，数据库集群，⼤家可以去看我其他的博客，博客写完了，⽬前还在整理，整理好之后我就发出来供⼤家批评指正，共同学习~~~，因为毕竟咱们学的是开发，本篇内容就当作是拓展⾃⼰的知识领域吧，对你来讲都是很有好处的~~~⼤家加油吧　　那么我们就来学⼀下mysqldump指令。　　1.⾸先我们先创建⼀个名为crm2的库　　　　mysql> create database crm2;　　　　mysql> show create database crm2;　　2.切换到crm2库下　　　　mysql> use crm2;　　3.创建两张表，student表和class表　　　　mysql> create table tb1(id int primary key,name char(8) not null,age int,class_id int not null);　　　　Query OK, 0 rows affected (0.63 sec)　　　　mysql> create table class(id int primary key,cname char(20) not null);　　　　Query OK, 0 rows affected (0.34 sec)　　4.给两张表插⼊⼀些数据　　　　mysql> insert into class values(1,'⼀班'),(2,'⼆班');　　　　mysql> insert into student values(1,'Jaden',18,1),(2,'太⽩',45,1),(3,'彦涛',30,2);　　5.查看⼀下两个表的数据　　　　mysql> select * from student;　　　　+----+--------+------+----------+　　　　| id | name | age | class_id |　　　　+----+--------+------+----------+　　　　| 1 | Jaden | 18 | 1 |　　　　| 2 | 太⽩ | 45 | 1 |　　　　| 3 | 彦涛 | 30 | 2 |　　　　+----+--------+------+----------+　　　　3 rows in set (0.00 sec)　　　　mysql> select * from class;　　　　+----+--------+　　　　| id | cname |　　　　+----+--------+　　　　| 1 | ⼀班 |　　　　| 2 | ⼆班 |　　　　+----+--------+　　　　2 rows in set (0.00 sec)　　　　好，前期⼯作准备完毕，下⾯我们来通过mysqldump指令进⾏备份，在cmd窗⼝下执⾏下⾯的指令，注意不是进⼊mysql⾥⾯输⼊的，是在外⾯⾯。　　　　C:WINDOWSsystem32>mysqldump -h 127.0.0.1 -u root -p666 crm2 > f:数据库备份练习　　　　Warning: Using a password on the command line interface can be insecure. (这个提⽰是因为我把密码显⽰出来了，⾃⼰在⾃⼰电脑上测试的时候，这个警告可以忽略)　　　　然后我们就会发现在这个'f:数据库备份练习'路径下⾯就有了⽂件　　　　然后我们通过nodepad++(随便⼀个⽂本编辑器都可以)，打开看看⾥⾯的内容：　　　　-- MySQL dump 10.13 Distrib 5.6.42, for Win64 (x86_64)　　　　--　　　　-- Host: 127.0.0.1 Database: crm2　　　　-- ------------------------------------------------------　　　　-- Server version 5.6.42　　　　/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;　　　　/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;　　　　/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;　　　　/*!40101 SET NAMES utf8 */;　　　　/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;　　　　/*!40103 SET TIME_ZONE='+00:00' */;　　　　/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;　　　　/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;　　　　/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;　　　　/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;　　　　--　　　　-- Table structure for table `class`　　　　--　　　　DROP TABLE IF EXISTS `class`; --如果之前存在class表，就将之前的class表删除　　　　/*!40101 SET @saved_cs_client = @@character_set_client */;　　　　/*!40101 SET character_set_client = utf8 */;　　　　CREATE TABLE `class` ( --创建表　　　　`id` int(11) NOT NULL,　　　　`cname` char(20) NOT NULL,　　　　PRIMARY KEY (`id`)　　　　) ENGINE=InnoDB DEFAULT CHARSET=utf8;　　　　/*!40101 SET character_set_client = @saved_cs_client */;　　　　--　　　　-- Dumping data for table `class`　　　　--　　　　LOCK TABLES `class` WRITE; --锁表　　　　/*!40000 ALTER TABLE `class` DISABLE KEYS */;　　　　INSERT INTO `class` VALUES (1,'⼀班'),(2,'⼆班'); --插⼊数据　　　　/*!40000 ALTER TABLE `class` ENABLE KEYS */;　　　　UNLOCK TABLES; --解锁　　　　......　　　　等等⼤致内容(如果你插⼊的数据有中⽂，这⾥显⽰的确实乱码的同学，往回看看我的关于修改mysql字符集编码的博客，将编码改为统⼀的，然后重新操作⼀遍就可以了)　　　　上⾯的这个指令的意思就是将crm2这个库，备份到这个'f:数据库备份练习'路径下，并且命名为⽂件。　　执⾏备份语句的时候，其中可以加上很多的参数，⽤来添加⼀些备份的时候的特殊要求的，其中有⼀个-B参数，执⾏备份语句时，如果加上了-B参数，那么将来再执⾏数据还原的时候，就不需要⾃⼰到数据库⾥⾯去先创建⼀个crm2这个库了，并且执⾏数据还原语句的时候就不需要指定crm2这个库了，如果没有加-B参数，就需要⾃⾏到数据库中先创建⼀个crm2这个库，并且执⾏语句是要指定将数据恢复到这个crm2库⾥⾯，看对⽐：　　⾸先上⾯我们执⾏的语句中没有加上-B参数，那么恢复数据的时候，怎么恢复呢，看下⾯的语句　　　　1.连接到数据库中，并创建crm2这个库　　　　　　mysql -u root -p666　　　　　　mysql> create database crm2;　　　　2.退出mysql或者重新启动⼀个cmd窗⼝，然后执⾏　　　　　　mysql -uroot -p 库名 < mysqldump出来的那个sql⽂件的路径　　　　例如：mysql -uroot -p crm2< f:数据库备份练习　　　　3.这样就恢复好了，我们连接上数据库并查看⾥⾯的内容：　　　　　　mysql -u root -p666　　　　　　use crm2;　　　　　　mysql> show tables;　　　　　　+----------------+　　　　　　| Tables_in_crm2 |　　　　　　+----------------+　　　　　　| class |　　　　　　| student |　　　　　　+----------------+　　　　　　2 rows in set (0.00 sec)　　　　　　mysql> select * from student;　　　　　　+----+--------+------+----------+　　　　　　| id | name | age | class_id |　　　　　　+----+--------+------+----------+　　　　　　| 1 | Jaden | 18 | 1 |　　　　　　| 2 | 太⽩ | 45 | 1 |　　　　　　| 3 | 彦涛 | 30 | 2 |　　　　　　+----+--------+------+----------+　　　　　　3 rows in set (0.00 sec)　　　　　　mysql> desc student;　　　　　　+----------+---------+------+-----+---------+-------+　　　　　　| Field | Type | Null | Key | Default | Extra |　　　　　　+----------+---------+------+-----+---------+-------+　　　　　　| id | int(11) | NO | PRI | NULL | |　　　　　　| name | char(8) | NO | | NULL | |　　　　　　| age | int(11) | YES | | NULL | |　　　　　　| class_id | int(11) | NO | | NULL | |　　　　　　+----------+---------+------+-----+---------+-------+　　　　　　4 rows in set (0.02 sec)　　　　就这么简单我们就将数据库恢复了，表中的数据和表结构都恢复了。　　　　执⾏mysqldump的时候加上了-B参数，那么恢复数据的时候，就不需要指定是恢复那个库⾥⾯的数据了，也不需要提前到数据库中创建⼀个crm2库了，因为-B参数导出的⽂件中⾃带创建数据库和连接数据库的功能：(使⽤-B参数备份出来的内容⾃带create database 库名和use 库名的功能)　　　　ump -uroot -p -B crm2> f:数据库备份练习　　　　2.在cmd窗⼝下执⾏：mysql -uroot -p < f:数据库备份练习　　　　3.查看⼀下是否恢复了：　　　　　　mysql> show databases;　　　　　　+--------------------+　　　　　　| Database |　　　　　　+--------------------+　　　　　　| information_schema |　　　　　　| crm2 |　　　　　　| d1 |　　　　　　| mysql |　　　　　　| performance_schema |　　　　　　| test |　　　　　　+--------------------+　　　　　　mysql> use crm2;　　　　　　Database changed　　　　　　mysql> show tables;　　　　　　+----------------+　　　　　　| Tables_in_crm2 |　　　　　　+----------------+　　　　　　| class |　　　　　　| student |　　　　　　+----------------+　　　　　　2 rows in set (0.00 sec)　　　　　　mysql> select * from class;　　　　　　+----+--------+　　　　　　| id | cname |　　　　　　+----+--------+　　　　　　| 1 | ⼀班 |　　　　　　| 2 | ⼆班 |　　　　　　+----+--------+　　　　　　2 rows in set (0.00 sec)　　　　　　mysql> desc student;　　　　　　+----------+---------+------+-----+---------+-------+　　　　　　| Field | Type | Null | Key | Default | Extra |　　　　　　+----------+---------+------+-----+---------+-------+　　　　　　| id | int(11) | NO | PRI | NULL | |　　　　　　| name | char(8) | NO | | NULL | |　　　　　　| age | int(11) | YES | | NULL | |　　　　　　| class_id | int(11) | NO | | NULL | |　　　　　　+----------+---------+------+-----+---------+-------+　　　　　　4 rows in set (0.02 sec)　　　　上⾯我们就完成了⼀个简单数据库备份和恢复的过程(在linux下⾯还可以在导出的时候压缩⽂件内容，减⼩空间占⽤mysqldump -uroot -p -B crm2|gzip> f:数据库备份练习，windows好像是没有⾃带的zip压缩指令，⼤家有兴趣的可以去查⼀下，作为了解吧)　　　　原理：其实很简单，就是把数据从mysql库⾥⾯以逻辑的sql语句的形式直接输出或者⽣成备份⽂件的过程。　　　　上⾯我们说完了单库备份，下⾯来看看多个库怎么备份呀　　　　　　C:WINDOWSsystem32>mysqldump -uroot -p -B crm2 mysql> f:数据库备份练习　　　　　　Enter password: ***　　　　就是多个库名⽤空格分开，这样备份出来的sql⽂件还是⼀个，也就是这两个库都备份到⼀个⽂件⾥⾯了。⼀般作备份的时候的⽂件名字都是库名.sql，多个库⼀般就是库名_库名_库名.sql。　　　　那如果我们将很多的库都是⼀起备份的，但是我们只想恢复其中⼀个库怎么办，这样搞是不是就不太合适了(因为⼀个⽂件算是⼀个备份，在进⾏恢复的时候，⼀下就将⽂件⾥⾯的所有的库都还原了，效率低不说，还⿇烦)，那就需要分库备份了，也就是将每个库分开来进⾏备份，⾃⼰备份⾃⼰的，⼀个⼀个来　　　　其实就是执⾏多个单库备份的语句　　　　　　mysqldump -uroot -p -B crm2> f:数据库备份练习　　　　　　mysqldump -uroot -p -B mysql> f:数据库备份练习　　　　　　...　　　　但是如果库⽐较多(企业的数据库⾥⼀般都会有多个库)，这么写就⽐较⿇烦了，所以需要获取所有数据库的库名，然后根据库名来循环执⾏上⾯的单库备份的语句，并将库名作为变量放到语句⾥⾯进⾏循环。需要写脚本(就是⼀堆系统指令组成的程序)来做这件事情了，具体怎么做，咱们就不说啦，⽬前知道⼀下就可以了(写⼀个.sh⽂件，然后sh+⽂件来执⾏这个⽂件，⽂件⾥⾯写个for循环就⾏了，等你学会写shell脚本就会了)　　　　备份表：　　　　　　上⾯我们说的是如何备份库，现在我们来看看如果备份其中的某个表：　　　　　　语法：mysqldump -u ⽤户名 -p 库名 表名> (路径)备份的⽂件名　　　　　　　　mysqldump -uroot -p crm2 student> f:数据库备份练习crm2_table_　　　　单纯进⾏表备份的时候，就不⽤写-B参数了，因为库crm2后⾯就是student表了，也就是说你的crm2库还在呢　　　　备份多个表：　　　　　　语法：mysqldump -u ⽤户名 -p 库名 表名1 表名2> (路径)备份的⽂件名　　　　和多个库⼀起备份有⼀个同样的问题，就是如果我只需要恢复某⼀张表怎么办，上⾯的多表备份是不是也不太合适啊，所以⼜要进⾏分表备份　　　　⼜是同样的套路，获取所有的表名，写⼀个循环脚本，执⾏单表备份的指令。　　　　分库分表备份有些缺点：⽂件多，很碎，数据量⾮常⼤的时候，效率低　　　　　　1.做⼀个完整的全备，再做⼀个分库分表的备份　　　　　　2.脚本批量恢复多个sql⽂件。　　　　　　备份数据库表结构：　　　　利⽤mysqldump -d参数只备份表的结果，例如：备份crm2库的所有表的结构：　　　　C:WINDOWSsystem32>mysqldump -uroot -p -B -d crm2> f:数据库备份练习　　　　Enter password: ***　　备份出来的⽂件打开⼀看，就没有了插⼊数据的部分　　mysqldump的关键参数说明：　　　　　　1.-B指定多个库，增加建库语句和use 语句　　　　　　2.--compact 去掉注释，适合调试输出，⽣产上不⽤　　　　　　3.-A或者--all-databases　　　　　　　　例如：C:WINDOWSsystem32>mysqldump -uroot -p -B -A> f:数据库备份练习　　　　　　　　Enter password: ***　　　　　　4.-F刷新binlog⽇志(binlog具体是什么，后⾯咱们再解释)　　　　　　5.--master-data 增加binlog⽇志⽂件名及对应的为⽀点。　　　　　　6.-x，--lock-all-tables 将所有的表锁住，⼀般mysql引擎都是锁表，全部都不能使⽤了，所有不太友好　　　　　　7.--add-locks这个选项会在INSERT语句中捆上⼀个LOCK TABLE和UNLOCK TABLE语句。这就防⽌在这些记录被再次导⼊数据库时其他⽤户对表进⾏的操作(mysql默认是加上的)　　　　　　8.-l，--lock-tables Lock all tables for read　　　　　　9.-d 只备份表结构　　　　　　10.-t 只备份数据　　　　　　11. --single-transaction 开启事务，适合innodb事务数据库备份　　　　　　　　InnoDB表在备份时，通常启⽤选项--single-transaction来保证备份的⼀致性，实际上他的⼯作原理时设定本次会话的隔离界别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了数据。　　　　　　　　MyISAM全库备份指令推荐：(gzip是压缩⽂件为zip类型的)　　　　　　　　mysqldump -uroot -p666 -A -B --master-data=2 -x|gzip>f:数据库备份练习　　　　　　　　InnoDB全库备份指令推荐：　　　　　　　　mysqldump -uroot -p666 -A -B --master-data=2 --single-transaction|gzip>f:数据库备份练习　　　　数据恢复：　　⼀、通过source命令恢复数据库　　　　进⼊mysql数据库控制台，mysql -uroot -p666登陆后　　　　mysql>use 数据库;　　　　然后使⽤source命令，后⾯参数为脚本⽂件(如这⾥⽤到的是.sql⽂件，如果你备份的是.txt⽂件，那这⾥写.txt⽂件)　　　　mysql>source #这个⽂件是系统路径下的，默认是登陆mysql前的系统路径，在mysql中查看系统路径的⽅法是通过system+系统命令来搞的　　　　mysql>system ls　　⼆、利⽤mysql命名恢复(标准)　　　　mysql -root -p666 -e "use crm2;drop table student;show tables;" 必须是双引号　　　　mysql -uroot -p666 crm2

#下⾯的这些内容是我之前整理的，⼤家不要看了，我会改版的，等新版出来之后在发出来给⼤家，下⾯的太晦涩难懂了~~~⼆ MySQL数据备份#1. 物理备份： 直接复制数据库⽂件，适⽤于⼤型数据库环境。但不能恢复到异构系统中如Windows。#2. 逻辑备份： 备份的是建表、建库、插⼊等操作所执⾏SQL语句，适⽤于中⼩型数据库，效率相对较低。#3. 导出表： 将表导⼊到⽂本⽂件中。

　　⼀、使⽤mysqldump实现逻辑备份#语法：# mysqldump -h 服务器 -u⽤户名 -p密码 数据库名 > 备份⽂件.sql#⽰例：#单库备份mysqldump -uroot -p123 db1 > qldump -uroot -p123 db1 table1 table2 > #多库备份mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_#备份所有库mysqldump -uroot -p123 --all-databases >

　　　　⼆、恢复逻辑备份#⽅法⼀：[root@localhost backup]# mysql -uroot -p123 < /backup/#⽅法⼆：mysql> use db1;mysql> SET SQL_LOG_BIN=0;mysql> source /root/#注：如果备份/恢复单个库时，可以修改sql⽂件DROP database if exists school;create database school;use school;

　　三、备份/恢复案例#数据库备份/恢复实验⼀：数据库损坏备份：1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_2. # mysql -uroot -p123 -e 'flush logs' //截断并产⽣新的binlog3. 插⼊数据 //模拟服务器正常运⾏4. mysql> set sql_log_bin=0; //模拟服务器损坏mysql> drop database db;恢复：1. # mysqlbinlog 最后⼀个binlog > /backup/last_2. mysql> set sql_log_bin=0;

mysql> source /backup/2014-02-13_ //恢复最近⼀次完全备份

mysql> source /backup/last_ //恢复最后个binlog⽂件#数据库备份/恢复实验⼆：如果有误删除备份：1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_2. mysql -uroot -p123 -e 'flush logs' //截断并产⽣新的binlog3. 插⼊数据 //模拟服务器正常运⾏4. drop table db1.t1 //模拟误删除5. 插⼊数据 //模拟服务器正常运⾏恢复：1. # mysqlbinlog 最后⼀个binlog --stop-position=260 > /tmp/

# mysqlbinlog 最后⼀个binlog --start-position=900 > /tmp/

2. mysql> set sql_log_bin=0;

mysql> source /backup/2014-02-13_ //恢复最近⼀次完全备份mysql> source /tmp/ //恢复最后个binlog⽂件mysql> source /tmp/ //恢复最后个binlog⽂件注意事项：1. 完全恢复到⼀个⼲净的环境（例如新的数据库或删除原有的数据库）2. 恢复期间所有SQL语句不应该记录到binlog中

　　四、实现⾃动化备份备份计划：1. 什么时间 2:002. 对哪些数据库备份3. 备份⽂件放的位置备份脚本：[root@localhost~]# vim /mysql_#!/bin/bashback_dir=/backupback_file=`date +%F`_r=rootpass=123if [ ! -d /backup ];thenmkdir -p /backupfi# 备份并截断⽇志mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}mysql -u${user} -p${pass} -e 'flush logs'# 只保留最近⼀周的备份cd $back_dirfind . -mtime +7 -exec rm -rf {} ;⼿动测试：[root@localhost ~]# chmod a+x /mysql_

[root@localhost ~]# chattr +i /mysql_[root@localhost ~]# /mysql_配置cron：[root@localhost ~]# crontab -l2 * * * /mysql_

　　五、表的导出和导⼊ INTO OUTFILE 导出⽂本⽂件⽰例：mysql> SELECT * FROM t1INTO OUTFILE ''FIELDS TERMINATED BY ',' //定义字段分隔符OPTIONALLY ENCLOSED BY '”' //定义字符串使⽤什么符号括起来LINES TERMINATED BY 'n' ; //定义换⾏符mysql 命令导出⽂本⽂件⽰例:# mysql -u root -p123 -e 'select * from ' > /tmp/# mysql -u root -p123 --xml -e 'select * from ' > /tmp/# mysql -u root -p123 --html -e 'select * from ' > /tmp/AD DATA INFILE 导⼊⽂本⽂件mysql> DELETE FROM student1;mysql> LOAD DATA INFILE '/tmp/'INTO TABLE t1FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '”'LINES TERMINATED BY 'n';

#可能会报错mysql> select * from into outfile 'C:' fields terminated by ',' lines terminated by 'rn';ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable#数据库最关键的是数据，⼀旦数据库权限泄露，那么通过上述语句就可以轻松将数据导出到⽂件中然后下载拿⾛，因⽽mysql对此作了限制，只能将⽂件导出到指定⽬录在配置⽂件中[mysqld]secure_file_priv='C:' #只能将数据导出到C:下重启mysql重新执⾏上述语句

　　六、数据库迁移务必保证在相同版本之间迁移# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h ⽬标IP -uroot -p456