Mysql备份恢复
Mysql备份恢复
1、备份
1.1、备份类型
【完全备份】
完全备份是备份整个数据库。
【部分备份】
部分备份是备份部分数据。部分备份又分为两种:增量备份和差异备份。
增量备份:备份上一次备份(增量备份或完全备份)后变化的数据。
差异备份:备份上一次完全备份后变化的数据。
1.2 、备份方式
备份方式有两种:物理备份和逻辑备份。
热备份:在备份时,数据库的读写操作不受影响。
温备份:在备份时,数据库的读不受影响,但是不能进行写操作。
冷备份:在备份时,数据库的读写不能进行。
【存储引擎限制】
热备份 | 温备份 | 冷备份 | |
---|---|---|---|
MyISAM | × | √ | √ |
InnoDB | √ | √ | √ |
1.3、备份工具
常用的备份工具:
工具名 | 介绍 |
---|---|
mysqldump | 逻辑备份,适用于所有Mysql存储引擎,支持温备份,完全备份、部分备份,对于InnoDB存储引擎,支持热备份 |
cp,tar | 物理备份,linux的复制和压缩解压命令,热备份 |
lvm2 snapshot | 热备份,借助文件系统管理工具 |
xtrabackup | InnoDB/XtraDB的热备份工具,支持完全备份、增量备份 |
1.4、备份策略
1、cp tar复制压缩数据库文件
2、mysqldump
3、xtrabackup
2、备份示例
2.1、cp tar备份
首先先查看备份前的数据
拷贝data目录的所有文件到一个新位置
cp -a /software/mysql/mysql3307/data /software/backup
查看备份好的文件
关闭mysql服务
[root@iz2zeaf5jdjve80rjlsjgnz bin]# ps -ef|grep mysql
root 9877 28844 0 09:15 pts/0 00:00:00 grep --color=auto mysql
systemd+ 29968 29951 0 Dec16 ? 00:58:33 mysqld
root 31855 1 0 09:04 pts/0 00:00:00 /bin/sh /software/mysql/mysql3307/bin/mysqld_safe --datadir=/software/mysql/mysql3307/data --pid-file=/software/mysql/mysql3307/mysqld.pid
mysql 32595 31855 0 09:04 pts/0 00:00:01 /software/mysql/mysql3307/bin/mysqld --basedir=/software/mysql/mysql3307 --datadir=/software/mysql/mysql3307/data --plugin-dir=/software/mysql/mysql3307/lib/plugin --user=mysql --log-error=iz2zeaf5jdjve80rjlsjgnz.err --open-files-limit=4161 --pid-file=/software/mysql/mysql3307/mysqld.pid --port=3307
[root@iz2zeaf5jdjve80rjlsjgnz bin]# kill -9 31855 32595
[root@iz2zeaf5jdjve80rjlsjgnz bin]# ps -ef|grep mysql
root 10354 28844 0 09:16 pts/0 00:00:00 grep --color=auto mysql
systemd+ 29968 29951 0 Dec16 ? 00:58:33 mysqld
重新初始化mysql
[root@iz2zeaf5jdjve80rjlsjgnz bin]# ./mysql_install_db --user=mysql --datadir=/software/mysql/mysql3307/data --basedir=/software/mysql/mysql3307
2019-12-22 09:17:20 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2019-12-22 09:17:23 [WARNING] The bootstrap log isn't empty:
2019-12-22 09:17:23 [WARNING] 2019-12-22T01:17:20.509218Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
这时候初始化是没有给出密码的,需要在my.cnf文件中配置skip-grant-tables
重新开启mysql服务
[root@iz2zeaf5jdjve80rjlsjgnz support-files]# ./mysql.server start
Starting MySQL [ OK ]
Logging to '/software/mysql/mysql3307/data/iz2zeaf5jdjve80rjlsjgnz.err'.
随便输入一个密码进入mysql控制台
[root@iz2zeaf5jdjve80rjlsjgnz bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
修改默认密码
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update mysql.user set authentication_string=password('root') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
再次修改密码
[root@iz2zeaf5jdjve80rjlsjgnz bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#修改默认密码
mysql> set password = password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#设置远程访问
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#刷新数据库
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
[root@iz2zeaf5jdjve80rjlsjgnz bin]#
使用navicat客户端和修改后的密码再次连接
删除mysql的data目录下的所有文件
复制backup备份文件夹的文件到mysql的data目录
刷新数据库
使用cp tar备份并恢复成功。
2.2、mysqldump
2.2.1、mysqldump命令详解
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
其中,OPTIONS可选的操作有
【连接】
-u,--user=name #用户名
-S,--socket=name #套接字
-p,--password[=name] #密码
-P,--port=port #端口
-h,--host=name #主机地址
-r,--result-file=name #输出的文件位置
【筛选】
-A,--all-databases #选择所有数据库
-B,--databases #选择需要导出的数据库,后面跟数据库名,在输出文件的内容中都会加上建库,use库语句
--ignore-table=db_name.tb_name #导出数据库时忽略指定的数据库下的表,要忽略多个需要多次填写该选项
-d,--no-data #不导出表的数据,只导出表结构
-E,--events #导出时间调度器
-R,--rowtines #导出存储过程和函数,但不会导出属性值,到处属性值可以导出mysql.proc表然后再恢复
--triggers #导出触发器,默认开启
--tables #会覆盖--databases选项,导出指定的表,但只能导出一个数据库中的表,格式是--tables db_name tables
--where='where_condition' #导出指定条件的数据,如--where="username='rlxy93'"
【DDL】
--add-drop-database #在输出文件的内容的create database语句前加上drop database语句
--add-drop-table #在输出文件的内容的create table语句前加上drop table语句,默认开启
--add-drop-trigger #在输出文件的内容的create trigger语句前加上drop trigger语句
-n,--no-create-db #指定了--databases或--all-databases选项时,会默认加上数据库创建语句,该选项可以设置不输出数据库创建语句
-t,--no-create-info #不创建建表语句
--replace #使用replace语句代替insert语句
【字符】
--default-character-set=charset_name #导出数据时,指定导出的字符集,默认utf8
--set-charset #在导出结果中加上set names charset_name语句,默认开启
【复制】
--apply-salve-statements #在change master前加上stop slave,在最后加上start slave
--delete-master-logs #导出后删除日志
--include-master-host-port #在文件中增加master host=<host>....
【格式化】
--compact #简化导出的内容,几乎所有的注释都不会输出
-c,--complete-insert #在insert语句中加上插入的列的信息
--create-options #在导出的建表语句中,加上所有的建表选项
-T,--tab=dir_name #根据每张表的结构和数据分别导出到表名.sql和表名.txt文件中。不能和--databases或--all-databases一起使用。
--fields-terminated-by=name #指定输出文件中的字段分隔符
--fields-enclosed-by=name #指定输出文件中的字段值的包围符,如使用引号将字符串包围起来使用
--fields-optionally-enclosed-by=name #指定输出文件中的可选字段引用符
--fields-escaped-by=name #指定输出文件中的转义字符
--lines-terminated-by=name #指定输出文件中的换行符
-Q,--quote-names #引用表名和列明时使用的标识符,默认使用反引号 `
【性能】
-K,--disable-keys #在insert语句前后加上禁用和启用索引的语句,默认开启
--insert-ignore #使用insert ignore语句替代insert语句
-q,--quick #快速导出数据,默认导出数据时会一次性检索表中所有数据加载进内存中,该选项是每检索一行就导出一行
【锁】
--add-locks #在insert语句前后加上lock tables和unlock tables语句,默认开启
-F,--flush-logs #在开始导出前先使用flush logs,如果使用了--all-databases,则一次在每个数据库导出前flush logs,如果使用了--lock-all-tables,--master-data或--single-transaction,则仅flush logs一次,等价于使用flush tables with read lock锁定所有表,这样可以使导出和flush操作在同一时刻执行
--flush-privileges #在导出完所有数据库后,在数据文件的结尾加上flush privileges语句,在导出的数据库涉及到mysql库或依赖于mysql库时都需要使用该选项
-x,-lock-all-tables #给所有的表加上一个持续到导出结束的全局读锁。该选项自动禁用--lock-tables和--single-transaction选项
-l,--lock-tables #在导出每个数据库钱一次对该数据库的所有表加上一个read local锁
--single-transaction #在导出前将事务隔离级别设置为repeatable read并发送开启事务start transaction语句给服务端
--no-autocommit #在insert语句前后加上set autocommit=0,并在需要提交的地方加上commit语句
--order-by-primary #如果表中存在主键或唯一索引,则排序后按序导出
2.2.2、mysqldump备份操作
【备份rlxy93库】
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak rlxy93
在根目录可以找到backup.bak文件
[root@iz2zeaf5jdjve80rjlsjgnz /]# ls
backup.bak bin boot cert dev docker etc home lib lib64 lost+found media mnt opt proc root run sbin software srv sys tmp usr var
打开backup.bak文件
-- MySQL dump 10.13 Distrib 5.7.24, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: rlxy93
-- ------------------------------------------------------
-- Server version 5.7.24-log
/*!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 `admin`
--
DROP TABLE IF EXISTS `admin`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin` (
`user` char(100) DEFAULT NULL,
`password` char(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `admin`
--
LOCK TABLES `admin` WRITE;
/*!40000 ALTER TABLE `admin` DISABLE KEYS */;
INSERT INTO `admin` VALUES ('a','a'),('b','b'),('c','c'),('d','d'),('e','e'),('r','r');
/*!40000 ALTER TABLE `admin` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `course`
--
DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `course`
--
LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'数学'),(2,'英语'),(3,'体育');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `student`
--
DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`stu_id` bigint(20) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`stu_age` int(10) DEFAULT NULL,
`course_id` int(10) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `stu_name` (`stu_name`,`stu_age`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `student`
--
LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'zhangsan',20,1),(2,'lisi',21,2),(3,'wangwu',22,3),(4,NULL,23,3);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2019-12-22 14:52:08
在上面的文件中可以发现,上述命令执行后,不会有建库和use语句。如果需要,可以加上--databases选项
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93
执行后,backup.bak文件中会有
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rlxy93` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `rlxy93`;
【备份多个数据库】
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 lll
【备份所有数据库】
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --all-databases
【不要创建数据库语句和建表语句】
在 【备份rlxy93库】 的基础上加了--no-create-db --no-create-info选项
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 --no-create-db --no-create-info
-- MySQL dump 10.13 Distrib 5.7.24, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: rlxy93
-- ------------------------------------------------------
-- Server version 5.7.24-log
/*!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 */;
--
-- Current Database: `rlxy93`
--
USE `rlxy93`;
--
-- Dumping data for table `admin`
--
LOCK TABLES `admin` WRITE;
/*!40000 ALTER TABLE `admin` DISABLE KEYS */;
INSERT INTO `admin` VALUES ('a','a'),('b','b'),('c','c'),('d','d'),('e','e'),('r','r');
/*!40000 ALTER TABLE `admin` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping data for table `course`
--
LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'数学'),(2,'英语'),(3,'体育');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping data for table `student`
--
LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'zhangsan',20,1),(2,'lisi',21,2),(3,'wangwu',22,3),(4,NULL,23,3);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2019-12-22 16:43:31
不难发现,建表和建库的语句都没有了。
【replace语句】将insert语句替换成replace语句
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 --no-create-db --no-create-info --replace
使用后,会将备份文件里面的insert语句全部修改成replace
REPLACE INTO `admin` VALUES ('a','a'),('b','b'),('c','c'),('d','d'),('e','e'),('r','r');
【compact选项】优化注释
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 --compact
backup.bak文件
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rlxy93` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `rlxy93`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin` (
`user` char(100) DEFAULT NULL,
`password` char(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `admin` VALUES ('a','a'),('b','b'),('c','c'),('d','d'),('e','e'),('r','r');
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `course` VALUES (1,'数学'),(2,'英语'),(3,'体育');
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`stu_id` bigint(20) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`stu_age` int(10) DEFAULT NULL,
`course_id` int(10) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `stu_name` (`stu_name`,`stu_age`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `student` VALUES (1,'zhangsan',20,1),(2,'lisi',21,2),(3,'wangwu',22,3),(4,NULL,23,3);
通过对比可以发现,backup.bak文件的很多注释都没有了,包括锁表解锁表的操作也没有了。
【-c,complete-insert选项】insert显示列名
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 --compact -c
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 --compact --complete-insert
backup.bak文件
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rlxy93` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `rlxy93`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin` (
`user` char(100) DEFAULT NULL,
`password` char(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `admin` (`user`, `password`) VALUES ('a','a'),('b','b'),('c','c'),('d','d'),('e','e'),('r','r');
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `course` (`course_id`, `course_name`) VALUES (1,'数学'),(2,'英语'),(3,'体育');
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`stu_id` bigint(20) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`stu_age` int(10) DEFAULT NULL,
`course_id` int(10) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `stu_name` (`stu_name`,`stu_age`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `student` (`stu_id`, `stu_name`, `stu_age`, `course_id`) VALUES (1,'zhangsan',20,1),(2,'lisi',21,2),(3,'wangwu',22,3),(4,NULL,23,3);
可以看出,在所有的insert语句中,加上了插入列列名。
【ignore-table选项】备份时忽略某张表
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 --compact --ignore-table rlxy93.course
backup.bak文件
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rlxy93` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `rlxy93`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin` (
`user` char(100) DEFAULT NULL,
`password` char(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `admin` VALUES ('a','a'),('b','b'),('c','c'),('d','d'),('e','e'),('r','r');
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`stu_id` bigint(20) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`stu_age` int(10) DEFAULT NULL,
`course_id` int(10) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `stu_name` (`stu_name`,`stu_age`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `student` VALUES (1,'zhangsan',20,1),(2,'lisi',21,2),(3,'wangwu',22,3),(4,NULL,23,3);
【no-data】只备份表结构
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --databases rlxy93 --compact --no-data
backup.bak
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rlxy93` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `rlxy93`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin` (
`user` char(100) DEFAULT NULL,
`password` char(100) DEFAULT NULL
) ENGINE=MyISAM 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 `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`stu_id` bigint(20) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`stu_age` int(10) DEFAULT NULL,
`course_id` int(10) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `stu_name` (`stu_name`,`stu_age`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
【tables】备份指定数据库下的指定表
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --compact --tables rlxy93 student course
backup.bak
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`stu_id` bigint(20) NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`stu_age` int(10) DEFAULT NULL,
`course_id` int(10) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
KEY `stu_name` (`stu_name`,`stu_age`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `student` VALUES (1,'zhangsan',20,1),(2,'lisi',21,2),(3,'wangwu',22,3),(4,NULL,23,3);
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `course` VALUES (1,'数学'),(2,'英语'),(3,'体育');
【where】根据条件来备份数据
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --compact --where="course_id>=2" --tables rlxy93 course
backup.bak
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `course` VALUES (2,'英语'),(3,'体育');
使用时,需要指定某张表。
【add-locks】在insert前后加锁释放锁
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --compact --tables rlxy93 course --add-locks
backup.bak
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
LOCK TABLES `course` WRITE;
INSERT INTO `course` VALUES (1,'数学'),(2,'英语'),(3,'体育');
UNLOCK TABLES;
【no-autocommit】在insert前加上start transaction,在插入完后再加上commit
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --compact --tables rlxy93 course --no-autocommit
backup.bak
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(10) NOT NULL,
`course_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
set autocommit=0;
INSERT INTO `course` VALUES (1,'数学'),(2,'英语'),(3,'体育');
commit;
3、恢复示例
3.1、恢复工具
常用的恢复工具有:
1、mysql自带的命令行工具mysqlimport
2、mysql命令行中的source
3、mysql的命令行工具在连接mysql数据库时导入
3.2、恢复示例
【mysqlimport导入某张数据表示例】
首先使用mysqldump导出rlxy93中的student表
./mysqldump -uroot -proot -S /tmp/mysql.sock -r /student.sql --tables rlxy93 student --no-create-info --compact
backup.bak文件
INSERT INTO `student` VALUES (1,'zhangsan',20,1),(2,'lisi',21,2),(3,'wangwu',22,3),(4,NULL,23,3);
删除数据表中的数据
delete from course;
使用mysqlimport导入
./mysqlimport -uroot -proot -S /tmp/mysql.sock rlxy93 /student.sql
导入成功。
【source命令】
mysql> source /student.sql
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> source /student.sql
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student;
+--------+----------+---------+-----------+
| stu_id | stu_name | stu_age | course_id |
+--------+----------+---------+-----------+
| 3 | wangwu | 22 | 3 |
| 2 | lisi | 21 | 2 |
| 1 | zhangsan | 20 | 1 |
| 4 | NULL | 23 | 3 |
+--------+----------+---------+-----------+
4 rows in set (0.00 sec)
【连接数据库时导入】
前面两种方式局限于导入到某个数据库,而第三种方式可以创建数据库,然后导入表。
备份rlxy93数据库
[root@iz2zeaf5jdjve80rjlsjgnz bin]# ./mysqldump -uroot -proot -S /tmp/mysql.sock -r /backup.bak --compact --databases rlxy93
删除rlxy93数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DB1 |
| DB2 |
| DB3 |
| amoeba |
| lll |
| mysql |
| performance_schema |
| sys |
+--------------------+
9 rows in set (0.00 sec)
连接时导入backup.bak文件
./mysql -u root -proot < /backup.bak
再次查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DB1 |
| DB2 |
| DB3 |
| amoeba |
| lll |
| mysql |
| performance_schema |
| rlxy93 |
| sys |
+--------------------+
10 rows in set (0.00 sec)