使用mysqldump工具对数据库进行全备份

需求描述:

　　通过mysqldump工具的--all-databases选项对所有数据库进行备份.

操作过程:

1.通过--all-databases选项对所有的数据库进行备份

[mysql@redhat6 MysqlDb_Backup]$ mysqldump -uroot -p --single-transaction --master-data --flush-logs --all-databases > FullBackup_2018-04-09.sql
Enter password: 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[mysql@redhat6 MysqlDb_Backup]$ ls -ltr
total 2123616
-rw-rw-r-- 1 mysql mysql 2137635937 Apr  8 09:45 Full-Backup_On_2018-04-08.sql
-rw-rw-r-- 1 mysql mysql   11542439 Apr  9 11:34 Backup_2018-04-09.sql
-rw-rw-r-- 1 mysql mysql       5943 Apr  9 15:00 Backup_store_basic_info.sql
-rw-rw-r-- 1 mysql mysql   25389763 Apr  9 15:04 FullBackup_2018-04-09.sql

备份:通过--all-databases选项对Mysql进行全库的备份.备份出来的SQL dump文件中,包含创建数据库的SQL语句.

2.查看生成的sql文件

-- MySQL dump 10.13  Distrib 5.5.57, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version    5.5.57-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 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=107;

--
-- Current Database: `mysql`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;  #在生成的SQL格式的备份文件中,包含CREATE DATABASE语句.

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

3.查看都备份了哪些数据库

[mysql@redhat6 MysqlDb_Backup]$ grep "CREATE DATABASE \/\*\!32312 IF NOT EXISTS\*\/" FullBackup_2018-04-09.sql 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test01` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `top_server` /*!40100 DEFAULT CHARACTER SET utf8 */;

4.查看当前所有的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |   #该数据库在全备时是不进行备份的
| mysql              |
| performance_schema |   #该数据库在全备时是不进行备份的
| test01             |
| top_server         |
+--------------------+

备注:在进行数据库的全备份时,information_schema和performance_schema两个DB是没有进行备份的.

 

文档创建时间:2018年4月9日15:33:35