需求描述:
通过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.sqlEnter password: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.[mysql@redhat6 MysqlDb_Backup]$ ls -ltrtotal 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