Mysql数据库之数据备份与恢复方式，
目录
- 一、数据备份的重要性
- 二、数据库备份的分类
- 1.从物理与逻辑的角度分类
- 2.从数据库的备份策略角度，备份可分为
- 2.1完全备份
- 2.2差异备份
- 2.3增量备份
- 2.4总结
- 三、常见的备份方法
- 四、Mysql数据库完全备份
- 1.完全备份定义
- 2.优缺点
- 3.数据库完全备份分类
- 4.Mysql数据库完全备份实际操作
- 4.1环境准备
- 4.2物理冷备份与恢复——使用tar命令备份
- 4.3物理温备份与恢复——使用mysqldump命令
- 5.Mysql数据库完全恢复实际操作
- 5.1使用Source命令恢复数据库
- 5.2使用Mysql命令恢复数据库
- 5.3有无--database的区别
- 6.Crontab -e——执行定时备份
- 五、日志
- 1.配置文件
- 1.1STATEMENT（基于SQL语句）记录格式
- 1.2ROW（基于行）记录格式
- 1.3MIXED（混合输入）记录格式——推荐使用
- 2.查看数据库日志
- 2.1查看二进制日志文件内容
- 六、增量备份
- 1.环境准备
- 2.使用二进制日志备份与恢复
- 2.1数据备份
- 2.2数据恢复
- 3.节点恢复
- 3.1插入数据
- 3.2备份二进制日志文件
- 3.3删除数据库
- 3.4恢复最原始数据
- 3.5恢复第三条数据，跳过第四条数据，恢复第五条数据
- 七、总结
- 1.物理冷备份
- 2.逻辑备份
- 3.完全恢复
- 4.增量备份
一、数据备份的重要性
在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
- 程序错误（损失不会太大）
- 人为操作数据
- 运算错误
- 磁盘故障
- 灾难（如火灾、地震）和盗窃
拓展：容灾是一个旨在确保业务连续性的系统工程，它涉及IT系统的设计和实施，以防止用户业务系统受到各种灾难的影响和破坏。容灾的核心目的是在自然或人为原因导致生产系统发生灾难时，尽可能保证业务的连续性。这通常包括建立两套或多套功能相同的IT系统，这些系统分布在相隔较远的不同地点，并能够进行健康状态监视和功能切换。当一处系统因意外（如火灾、地震等）停止工作时，整个应用系统可以切换到另一处，使得系统功能可以继续正常工作
二、数据库备份的分类
1.从物理与逻辑的角度分类
物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份方法
- 冷备份（脱机备份）：是在关机数据库的时候进行的（可备份整个数据库）（不建议使用）
- 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
- 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份
2.从数据库的备份策略角度，备份可分为
- 完全备份：每次对数据库进行完整的备份
- 差异备份：备份自从上次完全备份之后被修改过的文件
- 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份
2.1完全备份
完全备份过程中每次备份都会进行完全备份，会导致备份文件占用大量的磁盘空间，并且有大量的重复数据，只适合第一次备份，不常用
2.2差异备份
差异备份要先进行一次完全备份，每次差异备份都会备份上一次完全备份后的数据，可能会出现备份的重复数据，导致占用大量的磁盘空间；备份恢复时，先恢复完全备份呢，再导入差异备份的数据
2.3增量备份
- 增量备份要先执行一次完全备份，每一次增量备份的数据都是备份在上一次完全备份或者上一次增量备份后的数据，不会出现重复数据，也不会占用额外的磁盘空间
- 增量备份数据恢复时，需要先恢复完全备份数据，再恢复增量备份数据（需要按照次序）
2.4总结
| 完全备份 | 差异备份 | 增量备份 | |
|---|---|---|---|
| 执行顺序 | 每次完全备份会备份之前完全备份的数据，会出现重复数据 | 每次执行差异备份会备份之前每次的差异备份 | 每一次增量备份的数据都是备份上一次增量备份后新增的数据 |
| 占用磁盘空间 | 占用大量的磁盘空间 | 占用少量额外的磁盘空间 | 占用极少量的磁盘空间 |
| 数据恢复 | 把完全备份的文件导入即可 恢复速度很快 | 先恢复完全备份数据，再导入差异备份数据 | 先恢复完全备份数据，再恢复增量备份数据（需要按照次序进恢复） |
备份频率建议：一周一次的全备，全备的时间要选择在不提供业务或者业务处理较少的时间段执行（建议为01点到05点之间）；每天的增量备份；特定场景的差异备份
三、常见的备份方法
物理冷备
- 备份时数据库处于关闭状态，直接打包数据库文件
- 备份速度快，恢复时也是最简单的
专用备份工具mydump或mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份
- 进行增量备份，需要刷新二进制日志
Mysql支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行的数据库更改所需的信息进行恢复。如果进行增量备份（包含上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志
第三方备份工具
- 免费的Mysql热备份软件Percona XtraBackup
四、Mysql数据库完全备份
1.完全备份定义
- 是对整个数据库、数据库结构和文件结构的备份
- 保存的是备份完成时刻的数据库
- 是差异备份与增量备份的基础
2.优缺点
- 优点：备份与恢复操作简单方便
- 缺点：数据存在大量的重复；占用大量的备份空间；备份与恢复时间长
3.数据库完全备份分类
物理冷备份与恢复
- 关闭Mysql数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现有Mysql目录即可
Mysqldump备份与恢复
- Mysql自带的备份工具，可方便实现对Mysql的备份
- 可以将指定的库、表导出为SQL脚本
- 使用命令Mysql导入备份的数据
导出使用的是Mysqldump；导入使用的是Mysql命令
4.Mysql数据库完全备份实际操作
InnoDB 存储引擎的数据库在磁盘上存储成三个文件:
- db.opt(表属性文件)
- 表名.frm(表结构文件)
- 表名.ibd(表数据文件)
4.1环境准备
目前Mysql数据库中存在class数据库，及class数据表以及数据表中的数据
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.05 sec) mysql> create database class; Query OK, 1 row affected (0.00 sec) mysql> use class; Database changed mysql> show tables; Empty set (0.00 sec) mysql> create table class(id int(6),name varchar(8),remark varchar(40)); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-----------------+ | Tables_in_class | +-----------------+ | class | +-----------------+ 1 row in set (0.00 sec) mysql> insert into class values(1,'cxk','ctrl'); Query OK, 1 row affected (0.01 sec) mysql> insert into class values(2,'wyb','skateboarding'); Query OK, 1 row affected (0.00 sec) mysql> select * from class; +------+------+---------------+ | id | name | remark | +------+------+---------------+ | 1 | cxk | ctrl | | 2 | wyb | skateboarding | +------+------+---------------+ 2 rows in set (0.00 sec)
4.2物理冷备份与恢复——使用tar命令备份
4.2.1备份方法一
[root@localhost mysql]#systemctl stop mysqld #物理冷备份需要先关闭mysqld服务 [root@localhost ~]#cd /usr/local/mysql/ [root@localhost mysql]#ls bin docs man mysql.sock.lock share COPYING include mysqld.pid mysql-test support-files data lib mysql.sock README usr [root@localhost mysql]#tar zcvf data.tar.gz data/ -C /opt/ #使用tar命令进行压缩备份 [root@localhost mysql]#ls /opt boost_1_59_0.tar.gz mysql-5.7.17 mysql-5.7.17.tar.gz rh data.tar.gz
4.2.2备份方法二
[root@localhost mysql]#systemctl stop mysqld #物理冷备份需要先关闭mysqld服务 [root@localhost mysql]#tar Jcvf /opt/mysql_allbackup$(date +%F).tar.xz /usr/local/mysql/data #压缩打包/usr/local/mysql/data下的数据 保存在/opt目录下取名为mysql_allbackup$(date +%F).tar.xz date +%F代表当天的日期 [root@localhost mysql]#ls /opt boost_1_59_0.tar.gz mysql-5.7.17.tar.gz rh mysql-5.7.17 mysql_allbackup2024-03-25.tar.xz
4.2.3模式移走data数据文件目录
[root@localhost mysql]#mv data/ /home/ [root@localhost mysql]#ls bin docs man mysql.sock.lock share COPYING include mysqld.pid mysql-test support-files data.tar.gz lib mysql.sock README usr [root@localhost mysql]#systemctl restart mysqld.service #此时重启mysqld数据库 可能会报错 或者重新建立一个新的data数据文件目录（如果是新建的data数据文件目录）那么新建的data数据文件目录中无任何数据文件 [root@localhost mysql]#systemctl restart mysqld.service [root@localhost mysql]#ls bin data.tar.gz lib mysql.sock README usr COPYING docs man mysql.sock.lock share data include mysqld.pid mysql-test support-files #这里看到有一个新的data数据文件目录已经建立 [root@localhost mysql]#cd data/ [root@localhost data]#ls auto.cnf client-key.pem ib_logfile1 private_key.pem sys ca-key.pem ib_buffer_pool ibtmp1 public_key.pem ca.pem ibdata1 mysql server-cert.pem client-cert.pem ib_logfile0 performance_schema server-key.pem #这里看不到之前数据库中的文件
4.2.4执行数据恢复操作一
[root@localhost mysql]#tar zxvf data.tar.gz -C /usr/local/mysql/ #使用tar命令解压刚刚完全备份的数据文件 指定解压目录为/usr/local/mysql目录下 [root@localhost mysql]#cd data/ [root@localhost data]#ls auto.cnf client-cert.pem ib_logfile0 performance_schema server-key.pem ca-key.pem client-key.pem ib_logfile1 private_key.pem sys ca.pem ib_buffer_pool ibtmp1 public_key.pem class ibdata1 mysql server-cert.pem #此时我们看到刚刚完全备份的数据文件又恢复了
4.2.5执行数据恢复操作二
[root@localhost mysql]#tar Jxvf /opt/mysql_allbackup2024-03-25.tar.xz -C /usr/local/mysql/data #使用tar命令解压刚刚的完全备份文件 指定解压目录到/usr/local/mysql目录下 [root@localhost mysql]#ls bin data.tar.gz lib mysql.sock README usr COPYING docs man mysql.sock.lock share data include mysqld.pid mysql-test support-files [root@localhost mysql]#cd data/ [root@localhost data]#ls auto.cnf client-cert.pem ib_logfile0 performance_schema server-key.pem ca-key.pem client-key.pem ib_logfile1 private_key.pem sys ca.pem ib_buffer_pool ibtmp1 public_key.pem class ibdata1 mysql server-cert.pem #此时可以看到刚刚完全备份的数据文件内容
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | class | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use class; 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> select * from class; +------+------+---------------+ | id | name | remark | +------+------+---------------+ | 1 | cxk | ctrl | | 2 | wyb | skateboarding | +------+------+---------------+ 2 rows in set (0.00 sec)
数据库做迁移的时候 做整迁的时候使用该备份方式
4.3物理温备份与恢复——使用mysqldump命令
4.3.1备份单个数据库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | class | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> create database class2; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | class | | class2 | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
[root@localhost mysql]#mysqldump -uroot -p123456 --databases class > class.sql #使用mysqldump命令 指定用户root 指定密码为123456 指定备份的数据库class 备份存储为class.sql数据文件 mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls bin data include mysqld.pid mysql-test support-files class.sql data.tar.gz lib mysql.sock README usr COPYING docs man mysql.sock.lock share [root@localhost mysql]#cat class.sql #class.sql数据文件存放了一些sql语句 也就是在sql环境中的操作 注意一定要以sql结尾 方便后期的恢复 -- MySQL dump 10.13 Distrib 5.7.17, for Linux (x86_64) -- -- Host: localhost Database: class -- ------------------------------------------------------ -- Server version 5.7.17 /*!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: `class` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `class` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `class`; -- -- Table structure for table `class` -- DROP TABLE IF EXISTS `class`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `class` ( `id` int(6) DEFAULT NULL, `name` varchar(8) DEFAULT NULL, `remark` varchar(40) DEFAULT NULL ) 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,'cxk','ctrl'),(2,'wyb','skateboarding'); /*!40000 ALTER TABLE `class` 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 2024-03-25 16:04:00
4.3.2备份多个数据库
[root@localhost mysql]#mysqldump -uroot -p123456 --databases class class2 > class-class2.sql #使用mysqldump命令 指定用户root 指定密码为123456 指定备份的数据库class和class2 备份存储为class-class.sql数据文件 mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls bin data lib mysql.sock.lock support-files class-class2.sql data.tar.gz man mysql-test usr class.sql docs mysqld.pid README COPYING include mysql.sock share [root@localhost mysql]#cat class-class2.sql #class-class2.sql数据文件存放了一些sql语句 也就是在sql环境中的操作 注意一定要以sql结尾 方便后期的恢复 -- MySQL dump 10.13 Distrib 5.7.17, for Linux (x86_64) -- -- Host: localhost Database: class -- ------------------------------------------------------ -- Server version 5.7.17 /*!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: `class` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `class` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `class`; -- -- Table structure for table `class` -- DROP TABLE IF EXISTS `class`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `class` ( `id` int(6) DEFAULT NULL, `name` varchar(8) DEFAULT NULL, `remark` varchar(40) DEFAULT NULL ) 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,'cxk','ctrl'),(2,'wyb','skateboarding'); /*!40000 ALTER TABLE `class` ENABLE KEYS */; UNLOCK TABLES; -- -- Current Database: `class2` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `class2` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `class2`; /*!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 2024-03-25 16:07:25
[root@localhost mysql]#ll 总用量 1428 drwxr-xr-x 2 mysql mysql 4096 3月 19 13:12 bin -rw-r--r-- 1 root root 2174 3月 25 16:07 class-class2.sql -rw-r--r-- 1 root root 2031 3月 25 16:04 class.sql -rw-r--r-- 1 mysql mysql 17987 11月 28 2016 COPYING drwxr-x--- 7 mysql mysql 188 3月 25 16:02 data -rw-r--r-- 1 root root 1401469 3月 25 15:27 data.tar.gz drwxr-xr-x 2 mysql mysql 55 3月 19 13:10 docs drwxr-xr-x 3 mysql mysql 4096 3月 19 13:10 include drwxr-xr-x 4 mysql mysql 191 3月 19 13:12 lib drwxr-xr-x 4 mysql mysql 30 3月 19 13:11 man -rw-r----- 1 mysql mysql 5 3月 25 15:58 mysqld.pid srwxrwxrwx 1 mysql mysql 0 3月 25 15:58 mysql.sock -rw------- 1 mysql mysql 5 3月 25 15:58 mysql.sock.lock drwxr-xr-x 10 mysql mysql 4096 3月 19 13:13 mysql-test -rw-r--r-- 1 mysql mysql 2478 11月 28 2016 README drwxr-xr-x 28 mysql mysql 4096 3月 19 13:13 share drwxr-xr-x 2 mysql mysql 112 3月 19 13:13 support-files drwxr-xr-x 4 mysql mysql 30 3月 25 15:49 usr [root@localhost mysql]#chown mysql:mysql class.sql class-class2.sql [root@localhost mysql]#ll 总用量 1428 drwxr-xr-x 2 mysql mysql 4096 3月 19 13:12 bin -rw-r--r-- 1 mysql mysql 2174 3月 25 16:07 class-class2.sql -rw-r--r-- 1 mysql mysql 2031 3月 25 16:04 class.sql -rw-r--r-- 1 mysql mysql 17987 11月 28 2016 COPYING drwxr-x--- 7 mysql mysql 188 3月 25 16:02 data -rw-r--r-- 1 root root 1401469 3月 25 15:27 data.tar.gz drwxr-xr-x 2 mysql mysql 55 3月 19 13:10 docs drwxr-xr-x 3 mysql mysql 4096 3月 19 13:10 include drwxr-xr-x 4 mysql mysql 191 3月 19 13:12 lib drwxr-xr-x 4 mysql mysql 30 3月 19 13:11 man -rw-r----- 1 mysql mysql 5 3月 25 15:58 mysqld.pid srwxrwxrwx 1 mysql mysql 0 3月 25 15:58 mysql.sock -rw------- 1 mysql mysql 5 3月 25 15:58 mysql.sock.lock drwxr-xr-x 10 mysql mysql 4096 3月 19 13:13 mysql-test -rw-r--r-- 1 mysql mysql 2478 11月 28 2016 README drwxr-xr-x 28 mysql mysql 4096 3月 19 13:13 share drwxr-xr-x 2 mysql mysql 112 3月 19 13:13 support-files drwxr-xr-x 4 mysql mysql 30 3月 25 15:49 usr
注意：这里一定要将sql结尾的备份数据文件修改为mysql属主和属组，否则mysqld服务无法调用
4.3.3备份所有的库
[root@localhost mysql]#mysqldump -uroot -p123456 --all-databases > alldatabases.sql #使用mysqldump命令 指定用户root 指定密码为123456 指定备份的所有数据库 备份存储为alldatabases.sql数据文件 mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls alldatabases.sql COPYING include mysql.sock share bin data lib mysql.sock.lock support-files class-class2.sql data.tar.gz man mysql-test usr class.sql docs mysqld.pid README
4.3.4备份单个数据表
[root@localhost mysql]#mysqldump -uroot -p123456 class class > class_class.sql #使用mysqldump命令 指定用户为root 指定密码为123456 指定class数据库下的class数据表 备份到当前目录下 取名为class_class.sql数据文件 mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls alldatabases.sql class.sql docs mysqld.pid README bin COPYING include mysql.sock share class-class2.sql data lib mysql.sock.lock support-files class_class.sql data.tar.gz man mysql-test usr
4.3.5备份多个数据表
mysql> create table test(id int(6),name char(8),hobby varchar(40)); Query OK, 0 rows affected (0.00 sec) mysql> create table test2(id int(6),name char(8),hobby varchar(40)); Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-----------------+ | Tables_in_class | +-----------------+ | class | | test | | test2 | +-----------------+ 3 rows in set (0.00 sec)
[root@localhost mysql]#mysqldump -uroot -p123456 class class test test2 > class_class_test_test2.sql #使用mysqldump命令 指定用户为root 指定密码为123456 指定class数据库下的class、test、test2三个数据表 备份到当前目录下 取名为class_class_test_test2.sql数据文件 mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls alldatabases.sql class.sql include mysql.sock.lock usr bin COPYING lib mysql-test class-class2.sql data man README class_class.sql data.tar.gz mysqld.pid share class_class_test_test2.sql docs mysql.sock support-files
4.3.6只备份数据表结构
mysql> desc class; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(6) | YES | | NULL | | | name | varchar(8) | YES | | NULL | | | remark | varchar(40) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
[root@localhost mysql]#mysqldump -uroot -p123456 -d class class > classdesc.sql #使用mysqldump命令 指定用户为root 指定密码123456 -d只保存表结构（desc）class数据库下class数据表的结构 保存在当前目录下取名为classdesc.sql数据文件 mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls alldatabases.sql class.sql lib README bin COPYING man share class-class2.sql data mysqld.pid support-files class_class.sql data.tar.gz mysql.sock usr class_class_test_test2.sql docs mysql.sock.lock classdesc.sql include mysql-test [root@localhost mysql]#cat classdesc.sql -- MySQL dump 10.13 Distrib 5.7.17, for Linux (x86_64) -- -- Host: localhost Database: class -- ------------------------------------------------------ -- Server version 5.7.17 /*!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`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `class` ( `id` int(6) DEFAULT NULL, `name` varchar(8) DEFAULT NULL, `remark` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 2024-03-25 16:20:21
[root@localhost mysql]#grep -v "^--" classdesc.sql |grep -v "^/"|grep -v "^$" #过滤出SQL语句 #整条命令解释：过滤出classdesc.sql数据文件中不是以"--"开头的、不是以"/"开头的并且不是空行的内容 DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(6) DEFAULT NULL, `name` varchar(8) DEFAULT NULL, `remark` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.Mysql数据库完全恢复实际操作
使用mysqldump命令导出的文件可以使用导入的方法
- Source命令
- Mysql命令
拓展：mysql -e是指在bash环境执行SQL语句，-e指调用命令（此命令行方便在Shell脚本中运行）
mysql> show tables; +-----------------+ | Tables_in_class | +-----------------+ | class | | test | | test2 | +-----------------+ 3 rows in set (0.00 sec)
[root@localhost mysql]#mysql -uroot -p123456 -e 'drop table class.test2;' #在bash环境使用mysql命令登入数据库指定用户root 指定密码 指定sql命令删除class数据库下的test2数据表 mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show tables; +-----------------+ | Tables_in_class | +-----------------+ | class | | test | +-----------------+ 2 rows in set (0.00 sec)
5.1使用Source命令恢复数据库
mysql> source /usr/local/mysql/class_class_test_test2.sql mysql> show tables; +-----------------+ | Tables_in_class | +-----------------+ | class | | test | | test2 | +-----------------+ 3 rows in set (0.00 sec)
5.2使用Mysql命令恢复数据库
[root@localhost mysql]#mysql -uroot -p123456 -e 'drop table class.class;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 -e 'show tables from class;' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+ | Tables_in_class | +-----------------+ | test | | test2 | +-----------------+ [root@localhost mysql]#mysql -uroot -p123456 class < class_class_test_test2.sql #使用mysql命令 指定用户root 指定密码为123456 将该目录下的class_class_test_test2.sql数据文件导入到class数据库中 mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 -e 'show tables from class;' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+ | Tables_in_class | +-----------------+ | class | | test | | test2 | +-----------------+ [root@localhost mysql]#mysql -uroot -p123456 -e 'select * from class.class;' mysql: [Warning] Using a password on the command line interface can be insecure. +------+------+---------------+ | id | name | remark | +------+------+---------------+ | 1 | cxk | ctrl | | 2 | wyb | skateboarding | +------+------+---------------+
5.3有无--database的区别
Mysqldump严格来说是属于温备份，需要对表进行写入的锁定。
在全量备份与恢复中，class数据库，class数据库中有class数据表
- 当备份增加--database时，表示针对class整个数据库；
- 当备份不增加--databases时，表示只针对class数据库下所有的数据表
5.3.1有database
[root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | class | | class2 | | mysql | | performance_schema | | sys | +--------------------+ [root@localhost mysql]#mysql -uroot -p123456 -e 'show tables from class;' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+ | Tables_in_class | +-----------------+ | class | | test | | test2 | +-----------------+ [root@localhost mysql]#mysqldump -uroot -p123456 --databases class > class_all.sql #使用mysqldump命令 指定root用户 指定密码 备份class数据库下所有内容到当前目录 取名为class_all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls alldatabases.sql classdesc.sql include mysql-test bin class.sql lib README class_all.sql COPYING man share class-class2.sql data mysqld.pid support-files class_class.sql data.tar.gz mysql.sock usr class_class_test_test2.sql docs mysql.sock.lock [root@localhost mysql]#cat class_all.sql -- MySQL dump 10.13 Distrib 5.7.17, for Linux (x86_64) -- -- Host: localhost Database: class -- ------------------------------------------------------ -- Server version 5.7.17 /*!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: `class` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `class` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `class`; -- -- Table structure for table `class` -- DROP TABLE IF EXISTS `class`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `class` ( `id` int(6) DEFAULT NULL, `name` varchar(8) DEFAULT NULL, `remark` varchar(40) DEFAULT NULL ) 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,'cxk','ctrl'),(2,'wyb','skateboarding'); /*!40000 ALTER TABLE `class` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `test` -- DROP TABLE IF EXISTS `test`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test` ( `id` int(6) DEFAULT NULL, `name` char(8) DEFAULT NULL, `hobby` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `test` -- LOCK TABLES `test` WRITE; /*!40000 ALTER TABLE `test` DISABLE KEYS */; /*!40000 ALTER TABLE `test` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `test2` -- DROP TABLE IF EXISTS `test2`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test2` ( `id` int(6) DEFAULT NULL, `name` char(8) DEFAULT NULL, `hobby` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `test2` -- LOCK TABLES `test2` WRITE; /*!40000 ALTER TABLE `test2` DISABLE KEYS */; /*!40000 ALTER TABLE `test2` 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 2024-03-25 16:51:08
[root@localhost mysql]#mysql -uroot -p123456 -e 'drop database class;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | class2 | | mysql | | performance_schema | | sys | +--------------------+
[root@localhost mysql]#mysql -uroot -p123456 class < class_all.sql #恢复数据库 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1049 (42000): Unknown database 'class' [root@localhost mysql]#mysql -uroot -p123456 -e 'create database class;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | class | | class2 | | mysql | | performance_schema | | sys | +--------------------+ [root@localhost mysql]#mysql -uroot -p123456 class < class_all.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | class | | class2 | | mysql | | performance_schema | | sys | +--------------------+ [root@localhost mysql]#mysql -uroot -p123456 -e 'select * from class.class;' mysql: [Warning] Using a password on the command line interface can be insecure. +------+------+---------------+ | id | name | remark | +------+------+---------------+ | 1 | cxk | ctrl | | 2 | wyb | skateboarding | +------+------+---------------+
5.3.2无database
[root@localhost mysql]#mysqldump -uroot -p123456 class > /opt/class_all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#ls /opt/|grep "class" class_all.sql
[root@localhost mysql]#mysql -uroot -p123456 -e 'drop database class;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | class2 | | mysql | | performance_schema | | sys | +--------------------+ [root@localhost mysql]#mysql -uroot -p123456 -e 'create database class;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 class < /opt/class_all.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql]#mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | class | | class2 | | mysql | | performance_schema | | sys | +--------------------+ [root@localhost mysql]#mysql -uroot -p123456 -e 'select * from class.class;' mysql: [Warning] Using a password on the command line interface can be insecure. +------+------+---------------+ | id | name | remark | +------+------+---------------+ | 1 | cxk | ctrl | | 2 | wyb | skateboarding | +------+------+---------------+
6.Crontab -e——执行定时备份
0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -p123456 class > class_all_$(date +%F).sql; /usr/local/mysql/bin/mysqladmin -uroot -p flush-logs 或者 0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -p123456 class > class_all_$(date +%Y%m%d).sql; /usr/local/mysql/bin/mysqladmin -uroot -p flush-logs
五、日志
数据库日志对于数据库的备份和恢复中起着至关重要的作用
日志默认存放位置/usr/local/mysql/data文件夹下
1.配置文件
[root@localhost mysql]#vim /etc/my.cnf [root@localhost mysql]#sed -n '26,33p' /etc/my.cnf log-error=/usr/local/mysql/data/mysql_error.log #错误日志 存放位置子啊/usr/local/mysql/data/目录下 错误日志文件名为mysql_error.log general_log=ON #通用查询日志开启 general_log_file=/usr/local/mysql/data/mysql_general.log #通用查询日志 保存位置在/usr/local/mysql/data目录下 通用查询日志文件名为mysql_general.log log-bin=mysql-bin #二进制日志（binlog）：用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认开启 slow_query_log=ON #慢查询开启 慢查询：用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认关闭 slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log #慢查询日志默认存放位置 /usr/local/mysql/data/ 慢查询日志文件名为mysql_slow_query.log long_query_time=5 #设置超过5秒执行的语句被记录 缺省时为10秒 binlog_format = MIXED #指定二进制日志(binlog)的记录格式为MIXED（混合输入）
二进制日志开启后，重启mysql会在data目录中看到二进制日志（mysql-bin.000001,mysql-bin.000002...文件）开启二进制日志会产生一个索引文件及索引列表（mysql-bin.index）
[root@localhost mysql]#ls alldatabases.sql classdesc.sql include mysql-test bin class.sql lib README class_all.sql COPYING man share class-class2.sql data mysqld.pid support-files class_class.sql data.tar.gz mysql.sock usr class_class_test_test2.sql docs mysql.sock.lock [root@localhost mysql]#cd data/ [root@localhost data]#ls auto.cnf ibdata1 mysql mysql_error.log sys class ib_logfile0 mysql-bin.000001 mysql_general.log class2 ib_logfile1 mysql-bin.000002 mysql_slow_query.log ib_buffer_pool ibtmp1 mysql-bin.index performance_schema
其中，索引文件记录更新的sql语句；
索引文件刷新方式
- 重启Mysql服务的时候会更新索引文件，用于记录新的更新的sql语句
- 刷新二进制日志
二进制日志（binlog）有三种不同的记录格式
- STATEMENT（基于SQL语句）
- ROW（基于行）
- MIXED（混合输入）
默认格式是STATEMENT记录格式
1.1STATEMENT（基于SQL语句）记录格式
每一条设计到被修改的SQL都会记录在binlog
- 缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题
- 总结：增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想想的恢复可能你先删除或者在修改，可能会倒过来。准确率低
如果使用STATEMENT记录格式，假如删除数据库数据表中的第四行的数据，再次恢复数据表的数据，不一定是第四行，有可能将第四行恢复到最后
1.2ROW（基于行）记录格式
只记录变动的记录，不记录SQL的上下文环境
- 缺点：如果遇到update......set....where true 那么binlog的数据量会越来越大
- 总结：update、delete以多行数据起作用，来用行记录下来，只记录变动的记录，不记录sql的上下文环境，比如sql语句记录一行，但是ROW就可能记录10行，但是准确性高，高并发的时候由于操作量，性能变低 比较大所以记录都记下来
1.3MIXED（混合输入）记录格式——推荐使用
一般的语句使用statement，函数使用ROW方式存储。
根据并发量进行分配，并发量低选择STATEMENT，并发量高选择ROW
2.查看数据库日志
mysql> show variables like 'log_bin%'; #查看二进制日志是否开启 +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+---------------------------------------+ 5 rows in set (0.00 sec) mysql> show variables like 'general%'; #查看通用查询日志状态 +------------------+-----------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------+ | general_log | ON | | general_log_file | /usr/local/mysql/data/mysql_general.log | +------------------+-----------------------------------------+ 2 rows in set (0.00 sec) mysql> show variables like '%slow%'; #查看慢查询日志是否开启 +---------------------------+--------------------------------------------+ | Variable_name | Value | +---------------------------+--------------------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /usr/local/mysql/data/mysql_slow_query.log | +---------------------------+--------------------------------------------+ 5 rows in set (0.00 sec) mysql> show variables like 'long_query_time'; #查看慢查询时间设置 +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 5.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
variables 表示变量 like 表示模糊查询
- xxx%：以xxx为开头的字段
- %xxx：以xxx为结尾的字段
- %xxx%：只要出现xxx字段的都会显示出来
- xxx：精准查询
2.1查看二进制日志文件内容
[root@localhost data]#cp /usr/local/mysql/data/mysql-bin.000001 /opt
[root@localhost data]#vim /opt/mysql-bin.000001
[root@localhost data]#cat /opt/mysql-bin.000001 _þbinª?fw{5.7.17-logª?f8
**4
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。