MySQL/MariaDB数据库的mysqldump工具备份还原实战
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.mysqldump概述
1>.逻辑备份工具
mysqldump:
客户端命令，通过mysql协议连接至mysql服务器进行备份。因此备份过程中需要MySQL数据库实例必须处于可访问状态。
博主推荐阅读:https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html mydumper:
博主推荐阅读:https://launchpad.net/mydumper/ phpMyAdmin:
一个基于PHP语言开发的Web版本的数据库管理工具，常见的备份操作均可以再Web点点鼠标就可以轻松完成。该服务需要手动安装。
博主推荐阅读:https://www.phpmyadmin.net/
2>.Schema
Schema存放着数据的各种定义，DDL语言("CREATE DATABASE","CREATE TABLE","CREATE VIEW"等)包括通过INSERT语句导出各种数据。
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
3>.mysqldump工具
客户端命令，通过mysql协议连接至mysql服务器进行备份。 博主推荐阅读:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
二.mysqldump常用选项案例
1>.备份指定的数据库(仅备份该数据库下所有的表，但不包括create database语句，生产环境不推荐使用)
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjie |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock
[root@node105.yinzhengjie.org.cn ~]# mysqldump yinzhengjie -uroot -pyinzhengjie -S /mysql//socket/mysql.sock > /root/yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /root/yinzhengjiedb.sql
-- MySQL dump 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: yinzhengjie
-- ------------------------------------------------------
-- Server version 10.2.-MariaDB-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 `classes`
-- DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
`ClassID` tinyint() unsigned NOT NULL AUTO_INCREMENT,
`Class` varchar() DEFAULT NULL,
`NumOfStu` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `classes`
-- LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (,'Shaolin Pai',),(,'Emei Pai',),(,'QingCheng Pai',),(,'Wudang Pai',),(,'Riyue Shenjiao',),(,'Liansha
n Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15);/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `coc`
-- DROP TABLE IF EXISTS `coc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coc` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`ClassID` tinyint() unsigned NOT NULL,
`CourseID` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `coc`
-- LOCK TABLES `coc` WRITE;
/*!40000 ALTER TABLE `coc` DISABLE KEYS */;
INSERT INTO `coc` VALUES (,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,);
/*!40000 ALTER TABLE `coc` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `courses`
-- DROP TABLE IF EXISTS `courses`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `courses` (
`CourseID` smallint() unsigned NOT NULL AUTO_INCREMENT,
`Course` varchar() NOT NULL,
PRIMARY KEY (`CourseID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `courses`
-- LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
INSERT INTO `courses` VALUES (,'Hamo Gong'),(,'Kuihua Baodian'),(,'Jinshe Jianfa'),(,'Taiji Quan'),(,'Daiyu Zanghua'),(,'Weituo Zhang'),(,
'Dagou Bangfa');/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `scores`
-- DROP TABLE IF EXISTS `scores`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scores` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`StuID` int() unsigned NOT NULL,
`CourseID` smallint() unsigned NOT NULL,
`Score` tinyint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `scores`
-- LOCK TABLES `scores` WRITE;
/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
INSERT INTO `scores` VALUES (,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,
,),(,,,),(,,,),(,,,),(,,,);/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `students`
-- DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
`StuID` int() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint() unsigned DEFAULT NULL,
`TeacherID` int() unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `students`
-- LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (,'Shi Zhongyu',,'M',,),(,'Shi Potian',,'M',,),(,'Xie Yanke',,'M',,),(,'Ding Dian',,'M',,),(,
'Yu Yutong',,'M',,),(,'Shi Qing',,'M',,NULL),(,'Xi Ren',,'F',,NULL),(,'Lin Daiyu',,'F',,NULL),(,'Ren Yingying',,'F',,NULL),(,'Yue Lingshan',,'F',,NULL),(,'Yuan Chengzhi',,'M',,NULL),(,'Wen Qingqing',,'F',,NULL),(,'Tian Boguang',,'M',,NULL),(,'Lu Wushuang',,'F',,NULL),(,'Duan Yu',,'M',,NULL),(,'Xu Zhu',,'M',,NULL),(,'Lin Chong',,'M',,NULL),(,'Hua Rong',,'M',,NULL),(,'Xue Baochai',,'F',,NULL),(,'Diao Chan',,'F',,NULL),(,'Huang Yueying',,'F',,NULL),(,'Xiao Qiao',,'F',,NULL),(,'Ma Chao',,'M',,NULL),(,'Xu Xian',,'M',NULL,NULL),(,'Sun Dasheng',,'M',NULL,NULL),(,'a',,'F',NULL,NULL),(,'b',,'F',NULL,NULL),(,'c',,'F',NULL,NULL);/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `teachers`
-- DROP TABLE IF EXISTS `teachers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teachers` (
`TID` smallint() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') DEFAULT NULL,
PRIMARY KEY (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `teachers`
-- LOCK TABLES `teachers` WRITE;
/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
INSERT INTO `teachers` VALUES (,'Jason Yin',,'F'),(,'YinZhengjie',,'F'),(,'Jenny',,'M');
/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `toc`
-- DROP TABLE IF EXISTS `toc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `toc` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`CourseID` smallint() unsigned DEFAULT NULL,
`TID` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `toc`
-- LOCK TABLES `toc` WRITE;
/*!40000 ALTER TABLE `toc` DISABLE KEYS */;
/*!40000 ALTER TABLE `toc` 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 -- ::
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump yinzhengjie -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock > /root/yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW CREATE DATABASE yinzhengjie" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-------------+----------------------------------------------------------------------+
| Database | Create Database |
+-------------+----------------------------------------------------------------------+
| yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+----------------------------------------------------------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "USE yinzhengjie;SHOW TABLES;" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-----------------------+
| Tables_in_yinzhengjie |
+-----------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-----------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "DROP DATABASE yinzhengjie" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "CREATE DATABASE yinzhengjiedb DEFAULT CHARACTER SET utf8" -uroot -pyinzhengjie -S /mysql//sock
et/mysql.sock [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjiedb |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "USE yinzhengjiedb;SHOW TABLES;" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock yinzhengjiedb < /root/yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "USE yinzhengjiedb;SHOW TABLES;" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SELECT * FROM yinzhengjiedb.students" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| | Shi Zhongyu | | M | | |
| | Shi Potian | | M | | |
| | Xie Yanke | | M | | |
| | Ding Dian | | M | | |
| | Yu Yutong | | M | | |
| | Shi Qing | | M | | NULL |
| | Xi Ren | | F | | NULL |
| | Lin Daiyu | | F | | NULL |
| | Ren Yingying | | F | | NULL |
| | Yue Lingshan | | F | | NULL |
| | Yuan Chengzhi | | M | | NULL |
| | Wen Qingqing | | F | | NULL |
| | Tian Boguang | | M | | NULL |
| | Lu Wushuang | | F | | NULL |
| | Duan Yu | | M | | NULL |
| | Xu Zhu | | M | | NULL |
| | Lin Chong | | M | | NULL |
| | Hua Rong | | M | | NULL |
| | Xue Baochai | | F | | NULL |
| | Diao Chan | | F | | NULL |
| | Huang Yueying | | F | | NULL |
| | Xiao Qiao | | F | | NULL |
| | Ma Chao | | M | | NULL |
| | Xu Xian | | M | NULL | NULL |
| | Sun Dasheng | | M | NULL | NULL |
| | a | | F | NULL | NULL |
| | b | | F | NULL | NULL |
| | c | | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
[root@node105.yinzhengjie.org.cn ~]#
使用备份文件恢复时得先手动创建数据库，若未知数据库的默认编码可能会导致数据恢复出现一系列字符编码问题。这种备份方式不推荐使用
2>.备份指定的表(仅备份某个数据库中指定的表，但不包括create database语句，生产环境不推荐使用)
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjiedb |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "USE yinzhengjiedb;SHOW TABLES;" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump yinzhengjiedb students -uroot -pyinzhengjie -S /mysql//socket/mysql.sock > /root/students.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /root/students.sql
-- MySQL dump 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: yinzhengjiedb
-- ------------------------------------------------------
-- Server version 10.2.-MariaDB-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 `students`
-- DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
`StuID` int() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint() unsigned DEFAULT NULL,
`TeacherID` int() unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `students`
-- LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (,'Shi Zhongyu',,'M',,),(,'Shi Potian',,'M',,),(,'Xie Yanke',,'M',,),(,'Ding Dian',,'M',,),(,
'Yu Yutong',,'M',,),(,'Shi Qing',,'M',,NULL),(,'Xi Ren',,'F',,NULL),(,'Lin Daiyu',,'F',,NULL),(,'Ren Yingying',,'F',,NULL),(,'Yue Lingshan',,'F',,NULL),(,'Yuan Chengzhi',,'M',,NULL),(,'Wen Qingqing',,'F',,NULL),(,'Tian Boguang',,'M',,NULL),(,'Lu Wushuang',,'F',,NULL),(,'Duan Yu',,'M',,NULL),(,'Xu Zhu',,'M',,NULL),(,'Lin Chong',,'M',,NULL),(,'Hua Rong',,'M',,NULL),(,'Xue Baochai',,'F',,NULL),(,'Diao Chan',,'F',,NULL),(,'Huang Yueying',,'F',,NULL),(,'Xiao Qiao',,'F',,NULL),(,'Ma Chao',,'M',,NULL),(,'Xu Xian',,'M',NULL,NULL),(,'Sun Dasheng',,'M',NULL,NULL),(,'a',,'F',NULL,NULL),(,'b',,'F',NULL,NULL),(,'c',,'F',NULL,NULL);/*!40000 ALTER TABLE `students` 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 -- ::
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump yinzhengjiedb students -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock > /root/students.sql
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjiedb |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "CREATE DATABASE testdb DEFAULT CHARACTER SET utf8" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| yinzhengjiedb |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "USE testdb;SHOW TABLES;" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock testdb < /root/students.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "USE testdb;SHOW TABLES;" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+------------------+
| Tables_in_testdb |
+------------------+
| students |
+------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SELECT * FROM testdb.students" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| | Shi Zhongyu | | M | | |
| | Shi Potian | | M | | |
| | Xie Yanke | | M | | |
| | Ding Dian | | M | | |
| | Yu Yutong | | M | | |
| | Shi Qing | | M | | NULL |
| | Xi Ren | | F | | NULL |
| | Lin Daiyu | | F | | NULL |
| | Ren Yingying | | F | | NULL |
| | Yue Lingshan | | F | | NULL |
| | Yuan Chengzhi | | M | | NULL |
| | Wen Qingqing | | F | | NULL |
| | Tian Boguang | | M | | NULL |
| | Lu Wushuang | | F | | NULL |
| | Duan Yu | | M | | NULL |
| | Xu Zhu | | M | | NULL |
| | Lin Chong | | M | | NULL |
| | Hua Rong | | M | | NULL |
| | Xue Baochai | | F | | NULL |
| | Diao Chan | | F | | NULL |
| | Huang Yueying | | F | | NULL |
| | Xiao Qiao | | F | | NULL |
| | Ma Chao | | M | | NULL |
| | Xu Xian | | M | NULL | NULL |
| | Sun Dasheng | | M | NULL | NULL |
| | a | | F | NULL | NULL |
| | b | | F | NULL | NULL |
| | c | | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
对表中的数据进行还原
3>.使用"-B"选项备份指定的数据库，包括create database语句
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| yinzhengjiedb |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -B yinzhengjiedb -uroot -pyinzhengjie -S /mysql//socket/mysql.sock > /root/yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /root/yinzhengjiedb.sql
-- MySQL dump 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: yinzhengjiedb
-- ------------------------------------------------------
-- Server version 10.2.-MariaDB-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: `yinzhengjiedb`
-- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `yinzhengjiedb` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `yinzhengjiedb`; --
-- Table structure for table `classes`
-- DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
`ClassID` tinyint() unsigned NOT NULL AUTO_INCREMENT,
`Class` varchar() DEFAULT NULL,
`NumOfStu` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `classes`
-- LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (,'Shaolin Pai',),(,'Emei Pai',),(,'QingCheng Pai',),(,'Wudang Pai',),(,'Riyue Shenjiao',),(,'Liansha
n Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15);/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `coc`
-- DROP TABLE IF EXISTS `coc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coc` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`ClassID` tinyint() unsigned NOT NULL,
`CourseID` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `coc`
-- LOCK TABLES `coc` WRITE;
/*!40000 ALTER TABLE `coc` DISABLE KEYS */;
INSERT INTO `coc` VALUES (,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,),(,,);
/*!40000 ALTER TABLE `coc` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `courses`
-- DROP TABLE IF EXISTS `courses`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `courses` (
`CourseID` smallint() unsigned NOT NULL AUTO_INCREMENT,
`Course` varchar() NOT NULL,
PRIMARY KEY (`CourseID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `courses`
-- LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
INSERT INTO `courses` VALUES (,'Hamo Gong'),(,'Kuihua Baodian'),(,'Jinshe Jianfa'),(,'Taiji Quan'),(,'Daiyu Zanghua'),(,'Weituo Zhang'),(,
'Dagou Bangfa');/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `scores`
-- DROP TABLE IF EXISTS `scores`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scores` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`StuID` int() unsigned NOT NULL,
`CourseID` smallint() unsigned NOT NULL,
`Score` tinyint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `scores`
-- LOCK TABLES `scores` WRITE;
/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
INSERT INTO `scores` VALUES (,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,
,),(,,,),(,,,),(,,,),(,,,);/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `students`
-- DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
`StuID` int() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint() unsigned DEFAULT NULL,
`TeacherID` int() unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `students`
-- LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (,'Shi Zhongyu',,'M',,),(,'Shi Potian',,'M',,),(,'Xie Yanke',,'M',,),(,'Ding Dian',,'M',,),(,
'Yu Yutong',,'M',,),(,'Shi Qing',,'M',,NULL),(,'Xi Ren',,'F',,NULL),(,'Lin Daiyu',,'F',,NULL),(,'Ren Yingying',,'F',,NULL),(,'Yue Lingshan',,'F',,NULL),(,'Yuan Chengzhi',,'M',,NULL),(,'Wen Qingqing',,'F',,NULL),(,'Tian Boguang',,'M',,NULL),(,'Lu Wushuang',,'F',,NULL),(,'Duan Yu',,'M',,NULL),(,'Xu Zhu',,'M',,NULL),(,'Lin Chong',,'M',,NULL),(,'Hua Rong',,'M',,NULL),(,'Xue Baochai',,'F',,NULL),(,'Diao Chan',,'F',,NULL),(,'Huang Yueying',,'F',,NULL),(,'Xiao Qiao',,'F',,NULL),(,'Ma Chao',,'M',,NULL),(,'Xu Xian',,'M',NULL,NULL),(,'Sun Dasheng',,'M',NULL,NULL),(,'a',,'F',NULL,NULL),(,'b',,'F',NULL,NULL),(,'c',,'F',NULL,NULL);/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `teachers`
-- DROP TABLE IF EXISTS `teachers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teachers` (
`TID` smallint() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') DEFAULT NULL,
PRIMARY KEY (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `teachers`
-- LOCK TABLES `teachers` WRITE;
/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
INSERT INTO `teachers` VALUES (,'Jason Yin',,'F'),(,'YinZhengjie',,'F'),(,'Jenny',,'M');
/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
UNLOCK TABLES; --
-- Table structure for table `toc`
-- DROP TABLE IF EXISTS `toc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `toc` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`CourseID` smallint() unsigned DEFAULT NULL,
`TID` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Dumping data for table `toc`
-- LOCK TABLES `toc` WRITE;
/*!40000 ALTER TABLE `toc` DISABLE KEYS */;
/*!40000 ALTER TABLE `toc` 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 -- ::
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -B yinzhengjiedb -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock > /root/yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]# mysql -e "DROP DATABASE yinzhengjiedb" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock < /root/yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| yinzhengjiedb |
+--------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "USE yinzhengjiedb;SHOW TABLES" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SELECT * FROM yinzhengjiedb.teachers" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+-----+-------------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------------+-----+--------+
| | Jason Yin | | F |
| | YinZhengjie | | F |
| | Jenny | | M |
+-----+-------------+-----+--------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock < /root/yinzhengjiedb.sql #恢复时无需手动创建数据库啦！
[root@node105.yinzhengjie.org.cn ~]# mysqldump -B yinzhengjiedb -uroot -pyinzhengjie -S /mysql//socket/mysql.sock > /root/yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -B yinzhengjiedb -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | gzip > /root/yinzhengjiedb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -B yinzhengjiedb -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | xz > /root/yinzhengjiedb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : yinzhengjiedb.sql
-rw-r--r-- root root Nov : yinzhengjiedb.sql.gz
-rw-r--r-- root root Nov : yinzhengjiedb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : yinzhengjiedb.sql
-rw-r--r-- root root Nov : yinzhengjiedb.sql.gz
-rw-r--r-- root root Nov : yinzhengjiedb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mkdir data && mv yinzhengjiedb.sql.xz data
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
drwxr-xr-x root root Nov : data
-rw-r--r-- root root Nov : yinzhengjiedb.sql
-rw-r--r-- root root Nov : yinzhengjiedb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cd data/
[root@node105.yinzhengjie.org.cn ~/data]#
[root@node105.yinzhengjie.org.cn ~/data]# ll
total
-rw-r--r-- root root Nov : yinzhengjiedb.sql.xz
[root@node105.yinzhengjie.org.cn ~/data]#
[root@node105.yinzhengjie.org.cn ~/data]# xz -d yinzhengjiedb.sql.xz
[root@node105.yinzhengjie.org.cn ~/data]#
[root@node105.yinzhengjie.org.cn ~/data]# ll
total
-rw-r--r-- root root Nov : yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~/data]#
[root@node105.yinzhengjie.org.cn ~/data]#
[root@node105.yinzhengjie.org.cn ~/data]# cd
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
drwxr-xr-x root root Nov : data
-rw-r--r-- root root Nov : yinzhengjiedb.sql
-rw-r--r-- root root Nov : yinzhengjiedb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mkdir data2 && mv yinzhengjiedb.sql.gz data2
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
drwxr-xr-x root root Nov : data
drwxr-xr-x root root Nov : data2
-rw-r--r-- root root Nov : yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cd data2/
[root@node105.yinzhengjie.org.cn ~/data2]#
[root@node105.yinzhengjie.org.cn ~/data2]# ll
total
-rw-r--r-- root root Nov : yinzhengjiedb.sql.gz
[root@node105.yinzhengjie.org.cn ~/data2]#
[root@node105.yinzhengjie.org.cn ~/data2]# gzip -d yinzhengjiedb.sql.gz
[root@node105.yinzhengjie.org.cn ~/data2]#
[root@node105.yinzhengjie.org.cn ~/data2]# ll
total
-rw-r--r-- root root Nov : yinzhengjiedb.sql
[root@node105.yinzhengjie.org.cn ~/data2]#
生产环境中如果备份的数据库较大可用考虑使用gzip压缩或者压缩比例更高的xz工具(详情请戳我)
4>.使用"-A"选项备份所有数据库，包含create database，存储过程和自定义函数等
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | gzip > /root/alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# gzip -d alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock | gzip > /root/alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock yinzhengjiedb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 10.2.-MariaDB-log MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| yinzhengjiedb |
+--------------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> DROP DATABASE testdb;
Query OK, row affected (0.01 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> DROP DATABASE yinzhengjiedb;
Query OK, rows affected (0.02 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SOURCE /root/alldb.sql
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Database changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Database changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Database changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| yinzhengjiedb |
+--------------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW TABLES;
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]> SELECT * FROM teachers;
+-----+-------------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------------+-----+--------+
| | Jason Yin | | F |
| | YinZhengjie | | F |
| | Jenny | | M |
+-----+-------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SOURCE /root/alldb.sql #手动删除数据库，然后通过备份的文件进行还原(详情请戳我)
5>.使用"--master-data"选项记录备份时所处于二进制日志所在位置(此选项须启用二进制日志)
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER STATUS" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data= -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | gzip > /root/alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# gzip -d alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# head - alldb.sql
-- MySQL dump 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 10.2.-MariaDB-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.000003', MASTER_LOG_POS=; --
-- GTID to start replication from
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data=1 -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock | gzip > /root/alldb.sql.gz
6>.使用"-F"选项备份前滚动日志
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER LOGS" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER STATUS" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data= -F -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | gzip > /root/alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER STATUS" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER LOGS" -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# gzip -d alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# head - alldb.sql
-- MySQL dump 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 10.2.-MariaDB-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.000005', MASTER_LOG_POS=; --
-- GTID to start replication from
-- -- SET GLOBAL gtid_slave_pos='0-1-331'; --
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data=2 -F -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock | gzip > /root/alldb.sql.gz
7>.使用"--compact"选项去掉注释，适合调试，生产环境不使用
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data= -F --compact -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | gzip > /root/alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# gzip -d alldb.sql.gz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# head alldb.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=;
-- SET GLOBAL gtid_slave_pos='0-1-331'; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `mysql`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `column_stats` (
`db_name` varchar() COLLATE utf8_bin NOT NULL,
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data=2 -F --compact -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock | gzip > /root/alldb.sql.gz
8>.使用"-d, --no-data"选项只备份表结构
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data= -F -d -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | xz > /root/alldb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# xz -d alldb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat alldb.sql
-- MySQL dump 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 10.2.-MariaDB-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.000007', MASTER_LOG_POS=; --
-- GTID to start replication from
-- -- SET GLOBAL gtid_slave_pos='0-1-331'; --
-- Current Database: `mysql`
-- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `mysql`; --
-- Table structure for table `column_stats`
-- DROP TABLE IF EXISTS `column_stats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `column_stats` (
`db_name` varchar() COLLATE utf8_bin NOT NULL,
`table_name` varchar() COLLATE utf8_bin NOT NULL,
`column_name` varchar() COLLATE utf8_bin NOT NULL,
`min_value` varbinary() DEFAULT NULL,
`max_value` varbinary() DEFAULT NULL,
`nulls_ratio` decimal(,) DEFAULT NULL,
`avg_length` decimal(,) DEFAULT NULL,
`avg_frequency` decimal(,) DEFAULT NULL,
`hist_size` tinyint() unsigned DEFAULT NULL,
`hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') COLLATE utf8_bin DEFAULT NULL,
`histogram` varbinary() DEFAULT NULL,
PRIMARY KEY (`db_name`,`table_name`,`column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- 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 */;
CREATE TABLE `columns_priv` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Column_name` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `db`
-- DROP TABLE IF EXISTS `db`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `db` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `event`
-- DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event` (
`db` char() CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char() NOT NULL DEFAULT '',
`body` longblob NOT NULL,
`definer` char() CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`execute_at` datetime DEFAULT NULL,
`interval_value` int() DEFAULT NULL,
`interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SE
COND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL, `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_executed` datetime DEFAULT NULL,
`starts` datetime DEFAULT NULL,
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBT
RACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`originator` int() unsigned NOT NULL,
`time_zone` char() CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
`character_set_client` char() CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char() CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char() CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob DEFAULT NULL,
PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `func`
-- DROP TABLE IF EXISTS `func`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `func` (
`name` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`ret` tinyint() NOT NULL DEFAULT ,
`dl` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`type` enum('function','aggregate') CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `gtid_slave_pos`
-- DROP TABLE IF EXISTS `gtid_slave_pos`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gtid_slave_pos` (
`domain_id` int() unsigned NOT NULL,
`sub_id` bigint() unsigned NOT NULL,
`server_id` int() unsigned NOT NULL,
`seq_no` bigint() unsigned NOT NULL,
PRIMARY KEY (`domain_id`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Replication slave GTID position';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `help_category`
-- DROP TABLE IF EXISTS `help_category`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_category` (
`help_category_id` smallint() unsigned NOT NULL,
`name` char() NOT NULL,
`parent_category_id` smallint() unsigned DEFAULT NULL,
`url` text NOT NULL,
PRIMARY KEY (`help_category_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help categories';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `help_keyword`
-- DROP TABLE IF EXISTS `help_keyword`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_keyword` (
`help_keyword_id` int() unsigned NOT NULL,
`name` char() NOT NULL,
PRIMARY KEY (`help_keyword_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help keywords';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `help_relation`
-- DROP TABLE IF EXISTS `help_relation`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_relation` (
`help_topic_id` int() unsigned NOT NULL,
`help_keyword_id` int() unsigned NOT NULL,
PRIMARY KEY (`help_keyword_id`,`help_topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='keyword-topic relation';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `help_topic`
-- DROP TABLE IF EXISTS `help_topic`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_topic` (
`help_topic_id` int() unsigned NOT NULL,
`name` char() NOT NULL,
`help_category_id` smallint() unsigned NOT NULL,
`description` text NOT NULL,
`example` text NOT NULL,
`url` text NOT NULL,
PRIMARY KEY (`help_topic_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help topics';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `host`
-- DROP TABLE IF EXISTS `host`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `host` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `index_stats`
-- DROP TABLE IF EXISTS `index_stats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `index_stats` (
`db_name` varchar() COLLATE utf8_bin NOT NULL,
`table_name` varchar() COLLATE utf8_bin NOT NULL,
`index_name` varchar() COLLATE utf8_bin NOT NULL,
`prefix_arity` int() unsigned NOT NULL,
`avg_frequency` decimal(,) DEFAULT NULL,
PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `innodb_index_stats`
-- DROP TABLE IF EXISTS `innodb_index_stats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `innodb_index_stats` (
`database_name` varchar() COLLATE utf8_bin NOT NULL,
`table_name` varchar() COLLATE utf8_bin NOT NULL,
`index_name` varchar() COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`stat_name` varchar() COLLATE utf8_bin NOT NULL,
`stat_value` bigint() unsigned NOT NULL,
`sample_size` bigint() unsigned DEFAULT NULL,
`stat_description` varchar() COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `innodb_table_stats`
-- DROP TABLE IF EXISTS `innodb_table_stats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `innodb_table_stats` (
`database_name` varchar() COLLATE utf8_bin NOT NULL,
`table_name` varchar() COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`n_rows` bigint() unsigned NOT NULL,
`clustered_index_size` bigint() unsigned NOT NULL,
`sum_of_other_index_sizes` bigint() unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `plugin`
-- DROP TABLE IF EXISTS `plugin`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `plugin` (
`name` varchar() NOT NULL DEFAULT '',
`dl` varchar() NOT NULL DEFAULT '',
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL plugins';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `proc`
-- DROP TABLE IF EXISTS `proc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proc` (
`db` char() CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char() NOT NULL DEFAULT '',
`type` enum('FUNCTION','PROCEDURE') NOT NULL,
`specific_name` char() NOT NULL DEFAULT '',
`language` enum('SQL') NOT NULL DEFAULT 'SQL',
`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
`param_list` blob NOT NULL,
`returns` longblob NOT NULL,
`body` longblob NOT NULL,
`definer` char() CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBT
RACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char() CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char() CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char() CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob DEFAULT NULL,
PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `procs_priv`
-- DROP TABLE IF EXISTS `procs_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `procs_priv` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Routine_name` char() CHARACTER SET utf8 NOT NULL DEFAULT '',
`Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,
`Grantor` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `proxies_priv`
-- DROP TABLE IF EXISTS `proxies_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proxies_priv` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Proxied_host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Proxied_user` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`With_grant` tinyint() NOT NULL DEFAULT ,
`Grantor` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `roles_mapping`
-- DROP TABLE IF EXISTS `roles_mapping`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `roles_mapping` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Role` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Admin_option` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
UNIQUE KEY `Host` (`Host`,`User`,`Role`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Granted roles';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `servers`
-- DROP TABLE IF EXISTS `servers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `servers` (
`Server_name` char() NOT NULL DEFAULT '',
`Host` char() NOT NULL DEFAULT '',
`Db` char() NOT NULL DEFAULT '',
`Username` char() NOT NULL DEFAULT '',
`Password` char() NOT NULL DEFAULT '',
`Port` int() NOT NULL DEFAULT ,
`Socket` char() NOT NULL DEFAULT '',
`Wrapper` char() NOT NULL DEFAULT '',
`Owner` char() NOT NULL DEFAULT '',
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `table_stats`
-- DROP TABLE IF EXISTS `table_stats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `table_stats` (
`db_name` varchar() COLLATE utf8_bin NOT NULL,
`table_name` varchar() COLLATE utf8_bin NOT NULL,
`cardinality` bigint() unsigned DEFAULT NULL,
PRIMARY KEY (`db_name`,`table_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `tables_priv`
-- DROP TABLE IF EXISTS `tables_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tables_priv` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')
CHARACTER SET utf8 NOT NULL DEFAULT '', `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `time_zone`
-- DROP TABLE IF EXISTS `time_zone`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone` (
`Time_zone_id` int() unsigned NOT NULL AUTO_INCREMENT,
`Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `time_zone_leap_second`
-- DROP TABLE IF EXISTS `time_zone_leap_second`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_leap_second` (
`Transition_time` bigint() NOT NULL,
`Correction` int() NOT NULL,
PRIMARY KEY (`Transition_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Leap seconds information for time zones';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `time_zone_name`
-- DROP TABLE IF EXISTS `time_zone_name`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_name` (
`Name` char() NOT NULL,
`Time_zone_id` int() unsigned NOT NULL,
PRIMARY KEY (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `time_zone_transition`
-- DROP TABLE IF EXISTS `time_zone_transition`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_transition` (
`Time_zone_id` int() unsigned NOT NULL,
`Transition_time` bigint() NOT NULL,
`Transition_type_id` int() unsigned NOT NULL,
PRIMARY KEY (`Time_zone_id`,`Transition_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transitions';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `time_zone_transition_type`
-- DROP TABLE IF EXISTS `time_zone_transition_type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_transition_type` (
`Time_zone_id` int() unsigned NOT NULL,
`Transition_type_id` int() unsigned NOT NULL,
`Offset` int() NOT NULL DEFAULT ,
`Is_DST` tinyint() unsigned NOT NULL DEFAULT ,
`Abbreviation` char() NOT NULL DEFAULT '',
PRIMARY KEY (`Time_zone_id`,`Transition_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transition types';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `user`
-- DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char() CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int() unsigned NOT NULL DEFAULT ,
`max_updates` int() unsigned NOT NULL DEFAULT ,
`max_connections` int() unsigned NOT NULL DEFAULT ,
`max_user_connections` int() NOT NULL DEFAULT ,
`plugin` char() CHARACTER SET latin1 NOT NULL DEFAULT '',
`authentication_string` text COLLATE utf8_bin NOT NULL,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`default_role` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`max_statement_time` decimal(,) NOT NULL DEFAULT 0.000000,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `general_log`
-- /*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `general_log` (
`event_time` timestamp() NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`user_host` mediumtext NOT NULL,
`thread_id` bigint() unsigned NOT NULL,
`server_id` int() unsigned NOT NULL,
`command_type` varchar() NOT NULL,
`argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `slow_log`
-- /*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `slow_log` (
`start_time` timestamp() NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`user_host` mediumtext NOT NULL,
`query_time` time() NOT NULL,
`lock_time` time() NOT NULL,
`rows_sent` int() NOT NULL,
`rows_examined` int() NOT NULL,
`db` varchar() NOT NULL,
`last_insert_id` int() NOT NULL,
`insert_id` int() NOT NULL,
`server_id` int() unsigned NOT NULL,
`sql_text` mediumtext NOT NULL,
`thread_id` bigint() unsigned NOT NULL,
`rows_affected` int() NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Current Database: `testdb`
-- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `testdb`; --
-- Table structure for table `students`
-- DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
`StuID` int() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint() unsigned DEFAULT NULL,
`TeacherID` int() unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Current Database: `yinzhengjiedb`
-- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `yinzhengjiedb` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `yinzhengjiedb`; --
-- Table structure for table `classes`
-- DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
`ClassID` tinyint() unsigned NOT NULL AUTO_INCREMENT,
`Class` varchar() DEFAULT NULL,
`NumOfStu` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `coc`
-- DROP TABLE IF EXISTS `coc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coc` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`ClassID` tinyint() unsigned NOT NULL,
`CourseID` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `courses`
-- DROP TABLE IF EXISTS `courses`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `courses` (
`CourseID` smallint() unsigned NOT NULL AUTO_INCREMENT,
`Course` varchar() NOT NULL,
PRIMARY KEY (`CourseID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `scores`
-- DROP TABLE IF EXISTS `scores`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scores` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`StuID` int() unsigned NOT NULL,
`CourseID` smallint() unsigned NOT NULL,
`Score` tinyint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `students`
-- DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
`StuID` int() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint() unsigned DEFAULT NULL,
`TeacherID` int() unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `teachers`
-- DROP TABLE IF EXISTS `teachers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teachers` (
`TID` smallint() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('F','M') DEFAULT NULL,
PRIMARY KEY (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */; --
-- Table structure for table `toc`
-- DROP TABLE IF EXISTS `toc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `toc` (
`ID` int() unsigned NOT NULL AUTO_INCREMENT,
`CourseID` smallint() unsigned DEFAULT NULL,
`TID` smallint() unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) 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 -- ::
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data=2 -F -d -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock | xz > /root/alldb.sql.xz
9>.使用"-t, --no-create-info"选项只备份数据,不备份create table
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data= -F -t -uroot -pyinzhengjie -S /mysql//socket/mysql.sock | xz > /root/alldb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# xz -d alldb.sql.xz
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : alldb.sql
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# head - alldb.sql
-- MySQL dump 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 10.2.-MariaDB-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_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.000008', MASTER_LOG_POS=; --
-- GTID to start replication from
-- -- SET GLOBAL gtid_slave_pos='0-1-331'; --
-- Current Database: `mysql`
-- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `mysql`; --
-- Dumping data for table `column_stats`
-- LOCK TABLES `column_stats` WRITE;
/*!40000 ALTER TABLE `column_stats` DISABLE KEYS */;
/*!40000 ALTER TABLE `column_stats` ENABLE KEYS */;
UNLOCK TABLES; --
-- Dumping data for table `columns_priv`
-- LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
INSERT INTO `columns_priv` VALUES ('172.30.1.%','yinzhengjie','centos','students','Name','0000-00-00 00:00:00','Select'),('172.30.1.%','yinzhengj
ie','centos','students','Age','-- ::','Select');/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES; --
-- Dumping data for table `db`
-- LOCK TABLES `db` WRITE;
/*!40000 ALTER TABLE `db` DISABLE KEYS */;
INSERT INTO `db` VALUES ('172.30.1.%','yinzhengjie','jason','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
/*!40000 ALTER TABLE `db` ENABLE KEYS */;
UNLOCK TABLES; --
-- Dumping data for table `func`
-- LOCK TABLES `func` WRITE;
/*!40000 ALTER TABLE `func` DISABLE KEYS */;
/*!40000 ALTER TABLE `func` ENABLE KEYS */;
UNLOCK TABLES; --
-- Dumping data for table `gtid_slave_pos`
-- LOCK TABLES `gtid_slave_pos` WRITE;
/*!40000 ALTER TABLE `gtid_slave_pos` DISABLE KEYS */;
/*!40000 ALTER TABLE `gtid_slave_pos` ENABLE KEYS */;
UNLOCK TABLES; --
-- Dumping data for table `help_category`
-- LOCK TABLES `help_category` WRITE;
/*!40000 ALTER TABLE `help_category` DISABLE KEYS */;
INSERT INTO `help_category` VALUES (,'Geographic',,''),(,'Polygon properties',,''),(,'WKT',,''),(,'Numeric Functions',,''),(,'Plugins
',35,''),(6,'MBR',34,''),(7,'Control flow functions',38,''),(8,'Transactions',35,''),(9,'Help Metadata',35,''),(10,'Account Management',35,''),(11,'Point properties',34,''),(12,'Encryption Functions',38,''),(13,'LineString properties',34,''),(14,'Miscellaneous Functions',38,''),(15,'Logical operators',38,''),(16,'Functions and Modifiers for Use with GROUP BY',35,''),(17,'Information Functions',38,''),(18,'Comparison operators',38,''),(19,'Bit Functions',38,''),(20,'Table Maintenance',35,''),(21,'User-Defined Functions',35,''),(22,'Data Types',35,''),(23,'Compound Statements',35,''),(24,'Geometry constructors',34,''),(25,'GeometryCollection properties',1,''),(26,'Administration',35,''),(27,'Data Manipulation',35,''),(28,'Utility',35,''),(29,'Language Structure',35,''),(30,'Geometry relations',34,''),(31,'Date and Time Functions',38,''),(32,'WKB',34,''),(33,'Procedures',35,''),(34,'Geographic Features',35,''),(35,'Contents',0,''),(36,'Geometry properties',34,''),(37,'String Functions',38,''),(38,'Functions',35,''),(39,'Data Definition',35,'');/*!40000 ALTER TABLE `help_category` ENABLE KEYS */;
UNLOCK TABLES; --
-- Dumping data for table `help_keyword`
-- LOCK TABLES `help_keyword` WRITE;
/*!40000 ALTER TABLE `help_keyword` DISABLE KEYS */;
INSERT INTO `help_keyword` VALUES (,'JOIN'),(,'HOST'),(,'REPEAT'),(,'SERIALIZABLE'),(,'REPLACE'),(,'AT'),(,'SCHEDULE'),(,'RETURNS'),(,'S
TARTS'),(9,'MASTER_SSL_CA'),(10,'NCHAR'),(11,'COLUMNS'),(12,'COMPLETION'),(13,'WORK'),(14,'DATETIME'),(15,'MODE'),(16,'OPEN'),(17,'INTEGER'),(18,'ESCAPE'),(19,'VALUE'),(20,'MASTER_SSL_VERIFY_SERVER_CERT'),(21,'SQL_BIG_RESULT'),(22,'DROP'),(23,'GEOMETRYCOLLECTIONFROMWKB'),(24,'EVENTS'),(25,'MONTH'),(26,'PROFILES'),(27,'DUPLICATE'),(28,'REPLICATION'),(29,'UNLOCK'),(30,'INNODB'),(31,'YEAR_MONTH'),(32,'SUBJECT'),(33,'PREPARE'),(34,'LOCK'),(35,'NDB'),(36,'CHECK'),(37,'FULL'),(38,'INT4'),(39,'BY'),(40,'NO'),(41,'MINUTE'),(42,'PARTITION'),(43,'DATA'),(44,'DAY'),(45,'SHARE'),(46,'REAL'),(47,'SEPARATOR'),(48,'MESSAGE_TEXT'),(49,'MASTER_HEARTBEAT_PERIOD'),(50,'DELETE'),(51,'ON'),(52,'COLUMN_NAME'),(53,'CONNECTION'),(54,'CLOSE'),(55,'X509'),(56,'USE'),(57,'SUBCLASS_ORIGIN'),(58,'WHERE'),(59,'PRIVILEGES'),(60,'SPATIAL'),(61,'EVENT'),(62,'SUPER'),(63,'SQL_BUFFER_RESULT'),(64,'IGNORE'),(65,'SHA2'),(66,'QUICK'),(67,'SIGNED'),(68,'OFFLINE'),(69,'SECURITY'),(70,'AUTOEXTEND_SIZE'),(71,'NDBCLUSTER'),(72,'POLYGONFROMWKB'),(73,'FALSE'),(74,'LEVEL'),(75,'FORCE'),(76,'BINARY'),(77,'TO'),(78,'CHANGE'),(79,'CURRENT_USER'),(80,'HOUR_MINUTE'),(81,'UPDATE'),(82,'PRESERVE'),(83,'TABLE_NAME'),(84,'INTO'),(85,'FEDERATED'),(86,'VARYING'),(87,'MAX_SIZE'),(88,'HOUR_SECOND'),(89,'VARIABLE'),(90,'ROLLBACK'),(91,'PROCEDURE'),(92,'TIMESTAMP'),(93,'IMPORT'),(94,'AGAINST'),(95,'CHECKSUM'),(96,'COUNT'),(97,'LONGBINARY'),(98,'THEN'),(99,'INSERT'),(100,'ENGINES'),(101,'HANDLER'),(102,'PORT'),(103,'DAY_SECOND'),(104,'EXISTS'),(105,'MUTEX'),(106,'HELP_DATE'),(107,'RELEASE'),(108,'BOOLEAN'),(109,'MOD'),(110,'DEFAULT'),(111,'TYPE'),(112,'NO_WRITE_TO_BINLOG'),(113,'OPTIMIZE'),(114,'SQLSTATE'),(115,'RESET'),(116,'CLASS_ORIGIN'),(117,'INSTALL'),(118,'ITERATE'),(119,'DO'),(120,'BIGINT'),(121,'SET'),(122,'ISSUER'),(123,'DATE'),(124,'STATUS'),(125,'FULLTEXT'),(126,'COMMENT'),(127,'MASTER_CONNECT_RETRY'),(128,'INNER'),(129,'RELAYLOG'),(130,'STOP'),(131,'MASTER_LOG_FILE'),(132,'MRG_MYISAM'),(133,'PRECISION'),(134,'REQUIRE'),(135,'TRAILING'),(136,'PARTITIONS'),(137,'LONG'),(138,'OPTION'),(139,'REORGANIZE'),(140,'ELSE'),(141,'DEALLOCATE'),(142,'IO_THREAD'),(143,'CASE'),(144,'CIPHER'),(145,'CONTINUE'),(146,'FROM'),(147,'READ'),(148,'LEFT'),(149,'ELSEIF'),(150,'MINUTE_SECOND'),(151,'COMPACT'),(152,'DEC'),(153,'FOR'),(154,'WARNINGS'),(155,'MIN_ROWS'),(156,'STRING'),(157,'CONDITION'),(158,'ENCLOSED'),(159,'FUNCTION'),(160,'AGGREGATE'),(161,'FIELDS'),(162,'INT3'),(163,'ARCHIVE'),(164,'AVG_ROW_LENGTH'),(165,'ADD'),(166,'KILL'),(167,'FLOAT4'),(168,'TABLESPACE'),(169,'VIEW'),(170,'REPEATABLE'),(171,'INFILE'),(172,'HELP_VERSION'),(173,'ORDER'),(174,'USING'),(175,'CONSTRAINT_CATALOG'),(176,'MIDDLEINT'),(177,'GRANT'),(178,'UNSIGNED'),(179,'DECIMAL'),(180,'GEOMETRYFROMTEXT'),(181,'INDEXES'),(182,'FOREIGN'),(183,'CACHE'),(184,'HOSTS'),(185,'MYSQL_ERRNO'),(186,'COMMIT'),(187,'SCHEMAS'),(188,'LEADING'),(189,'SNAPSHOT'),(190,'CONSTRAINT_NAME'),(191,'DECLARE'),(192,'LOAD'),(193,'SQL_CACHE'),(194,'CONVERT'),(195,'DYNAMIC'),(196,'COLLATE'),(197,'POLYGONFROMTEXT'),(198,'BYTE'),(199,'GLOBAL'),(200,'LINESTRINGFROMWKB'),(201,'WHEN'),(202,'HAVING'),(203,'AS'),(204,'STARTING'),(205,'RELOAD'),(206,'AUTOCOMMIT'),(207,'REVOKE'),(208,'GRANTS'),(209,'OUTER'),(210,'CURSOR_NAME'),(211,'FLOOR'),(212,'EXPLAIN'),(213,'WITH'),(214,'AFTER'),(215,'STD'),(216,'CSV'),(217,'DISABLE'),(218,'UNINSTALL'),(219,'OUTFILE'),(220,'LOW_PRIORITY'),(221,'FILE'),(222,'NODEGROUP'),(223,'SCHEMA'),(224,'SONAME'),(225,'POW'),(226,'DUAL'),(227,'MULTIPOINTFROMWKB'),(228,'INDEX'),(229,'MULTIPOINTFROMTEXT'),(230,'DEFINER'),(231,'MASTER_BIND'),(232,'REMOVE'),(233,'EXTENDED'),(234,'MULTILINESTRINGFROMWKB'),(235,'CROSS'),(236,'CONTRIBUTORS'),(237,'NATIONAL'),(238,'GROUP'),(239,'SHA'),(240,'ONLINE'),(241,'UNDO'),(242,'IGNORE_SERVER_IDS'),(243,'ZEROFILL'),(244,'CLIENT'),(245,'MASTER_PASSWORD'),(246,'OWNER'),(247,'RELAY_LOG_FILE'),(248,'TRUE'),(249,'CHARACTER'),(250,'MASTER_USER'),(251,'SCHEMA_NAME'),(252,'TABLE'),(253,'ENGINE'),(254,'INSERT_METHOD'),(255,'CASCADE'),(256,'RELAY_LOG_POS'),(257,'SQL_CALC_FOUND_ROWS'),(258,'UNION'),(259,'MYISAM'),(260,'LEAVE'),(261,'MODIFY'),(262,'MATCH'),(263,'MASTER_LOG_POS'),(264,'DISTINCTROW'),(265,'DESC'),(266,'TIME'),(267,'NUMERIC'),(268,'EXPANSION'),(269,'CODE'),(270,'CURSOR'),(271,'GEOMETRYCOLLECTIONFROMTEXT'),(272,'CHAIN'),(273,'LOGFILE'),(274,'FLUSH'),(275,'CREATE'),(276,'DESCRIBE'),(277,'EXTENT_SIZE'),(278,'MAX_UPDATES_PER_HOUR'),(279,'INT2'),(280,'PROCESSLIST'),(281,'ENDS'),(282,'LOGS'),(283,'DISCARD'),(284,'HEAP'),(285,'SOUNDS'),(286,'BETWEEN'),(287,'MULTILINESTRINGFROMTEXT'),(288,'REPAIR'),(289,'PACK_KEYS'),(290,'FAST'),(291,'VALUES'),(292,'CALL'),(293,'LOOP'),(294,'VARCHARACTER'),(295,'BEFORE'),(296,'TRUNCATE'),(297,'SHOW'),(298,'ALL'),(299,'REDUNDANT'),(300,'USER_RESOURCES'),(301,'PARTIAL'),(302,'BINLOG'),(303,'END'),(304,'SECOND'),(305,'AND'),(306,'FLOAT8'),(307,'PREV'),(308,'HOUR'),(309,'SELECT'),(310,'DATABASES'),(311,'OR'),(312,'IDENTIFIED'),(313,'WRAPPER'),(314,'MASTER_SSL_CIPHER'),(315,'SQL_SLAVE_SKIP_COUNTER'),(316,'BOTH'),(317,'BOOL'),(318,'YEAR'),(319,'MASTER_PORT'),(320,'CONCURRENT'),(321,'HELP'),(322,'UNIQUE'),(323,'TRIGGERS'),(324,'PROCESS'),(325,'OPTIONS'),(326,'RESIGNAL'),(327,'CONSISTENT'),(328,'MASTER_SSL'),(329,'DATE_ADD'),(330,'MAX_CONNECTIONS_PER_HOUR'),(331,'LIKE'),(332,'PLUGIN'),(333,'FETCH'),(334,'IN'),(335,'COLUMN'),(336,'DUMPFILE'),(337,'USAGE'),(338,'EXECUTE'),(339,'MEMORY'),(340,'CEIL'),(341,'QUERY'),(342,'MASTER_HOST'),(343,'LINES'),(344,'SQL_THREAD'),(345,'SERVER'),(346,'MAX_QUERIES_PER_HOUR'),(347,'MASTER_SSL_CERT'),(348,'MULTIPOLYGONFROMWKB'),(349,'TRANSACTION'),(350,'DAY_MINUTE'),(351,'STDDEV'),(352,'DATE_SUB'),(353,'REBUILD'),(354,'GEOMETRYFROMWKB'),(355,'INT1'),(356,'RENAME'),(357,'PARSER'),(358,'RIGHT'),(359,'ALTER'),(360,'MAX_ROWS'),(361,'SOCKET'),(362,'STRAIGHT_JOIN'),(363,'NATURAL'),(364,'VARIABLES'),(365,'ESCAPED'),(366,'SHA1'),(367,'KEY_BLOCK_SIZE'),(368,'PASSWORD'),(369,'OFFSET'),(370,'CHAR'),(371,'NEXT'),(372,'ERRORS'),(373,'SQL_LOG_BIN'),(374,'TEMPORARY'),(375,'COMMITTED'),(376,'SQL_SMALL_RESULT'),(377,'UPGRADE'),(378,'BEGIN'),(379,'DELAY_KEY_WRITE'),(380,'PROFILE'),(381,'MEDIUM'),(382,'INTERVAL'),(383,'SSL'),(384,'DAY_HOUR'),(385,'NAME'),(386,'REFERENCES'),(387,'AES_ENCRYPT'),(388,'STORAGE'),(389,'ISOLATION'),(390,'CEILING'),(391,'EVERY'),(392,'INT8'),(393,'AUTHORS'),(394,'RESTRICT'),(395,'UNCOMMITTED'),(396,'LINESTRINGFROMTEXT'),(397,'IS'),(398,'NOT'),(399,'ANALYSE'),(400,'DATAFILE'),(401,'DES_KEY_FILE'),(402,'SIGNAL'),(403,'COMPRESSED'),(404,'START'),(405,'PLUGINS'),(406,'SAVEPOINT'),(407,'IF'),(408,'ROWS'),(409,'PRIMARY'),(410,'PURGE'),(411,'LAST'),(412,'USER'),(413,'EXIT'),(414,'KEYS'),(415,'LIMIT'),(416,'KEY'),(417,'MERGE'),(418,'UNTIL'),(419,'SQL_NO_CACHE'),(420,'DELAYED'),(421,'CONSTRAINT_SCHEMA'),(422,'ANALYZE'),(423,'CONSTRAINT'),(424,'SERIAL'),(425,'ACTION'),(426,'WRITE'),(427,'INITIAL_SIZE'),(428,'SESSION'),(429,'DATABASE'),(430,'NULL'),(431,'POWER'),(432,'USE_FRM'),(433,'TERMINATED'),(434,'SLAVE'),(435,'NVARCHAR'),(436,'ASC'),(437,'RETURN'),(438,'OPTIONALLY'),(439,'ENABLE'),(440,'DIRECTORY'),(441,'MAX_USER_CONNECTIONS'),(442,'WHILE'),(443,'LOCAL'),(444,'DISTINCT'),(445,'AES_DECRYPT'),(446,'MASTER_SSL_KEY'),(447,'NONE'),(448,'TABLES'),(449,'<>'),(450,'RLIKE'),(451,'TRIGGER'),(452,'COLLATION'),(453,'SHUTDOWN'),(454,'HIGH_PRIORITY'),(455,'BTREE'),(456,'FIRST'),(457,'COALESCE'),(458,'WAIT'),(459,'CATALOG_NAME'),(460,'MASTER'),(461,'FIXED'),(462,'MULTIPOLYGONFROMTEXT'),(463,'ROW_FORMAT');[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data=2 -F -t -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock | xz > /root/alldb.sql.xz
10>.mysqldump常见选项
-A， --all-databases :
备份所有数据库，含create database
-B , --databases db_name…:
指定备份的数据库，包括create database语句
-E, --events:
备份相关的所有event scheduler
-R, --routines:
备份所有存储过程和自定义函数
--triggers:
备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8
指定字符集
--master-data[=#]：
此选项须启用二进制日志
：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1(一般用于主从复制使用该备份方式,"--master-data=1")
：记录为注释的CHANGE MASTER TO语句(若不用于主从复制只是单纯备份使用"--master-data=2"即可。)
此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs:
备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次日志
--compact:
去掉注释，适合调试，生产不使用
-d, --no-data:
只备份表结构
-t, --no-create-info:
只备份数据,不备份create table
-n,--no-create-db:
不备份create database，可被-A或-B覆盖
--flush-privileges:
备份mysql或权限刷新相关时需要使用
-f, --force:
忽略SQL错误，继续执行
--hex-blob:
使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick:
不缓存查询，直接输出，加快备份速度
[root@node105.yinzhengjie.org.cn ~]# mysqldump --help
mysqldump Ver 10.16 Distrib 10.2.-MariaDB, for Linux (x86_64)
Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS] Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqldump client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
The following specify which files/extra groups are read (specified before remaining options):
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=# Additionally read default groups with # appended as a suffix. -A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
--add-drop-trigger Add a DROP TRIGGER before each create.
--add-locks Add locks around INSERT statements.
(Defaults to on; use --skip-add-locks to disable.)
--allow-keywords Allow creation of column names that are keywords.
--apply-slave-statements
Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START
SLAVE' to bottom of dump.
--character-sets-dir=name
Directory for character set files.
-i, --comments Write additional information.
(Defaults to on; use --skip-comments to disable.)
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1. or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
-a, --create-options
Include all MySQL specific create options.
(Defaults to on; use --skip-create-options to disable.)
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED.
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
(Defaults to on; use --skip-disable-keys to disable.)
--dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to , will printit as a CHANGE MASTER command
in the dumped data output; if equal to , that command
will be prefixed with a comment symbol. This option will
turn --lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump - don't
forget to read about --single-transaction below). In all
cases any action on logs will happen at the exact moment
of the dump.Option automatically turns --lock-tables off.
-E, --events Dump events.
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
(Defaults to on; use --skip-extended-insert to disable.)
--fields-terminated-by=name
Fields in the output file are terminated by the given
string.
--fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
--fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
--fields-escaped-by=name
Fields in the output file are escaped by the given
character.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an SQL error.
--gtid Used together with --master-data= or --dump-slave=.When
enabled, the output from those options will set the GTID
position instead of the binlog file and offset; the
file/offset will appear only as a comment. When disabled,
the GTID position will still appear in the output, but
only commented.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table.
--include-master-host-port
Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGE
MASTER TO..' in dump produced with --dump-slave.
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
(Defaults to on; use --skip-lock-tables to disable.)
--log-error=name Append warnings and errors to given file.
--log-queries When restoring the dump, the server will, if logging
turned on, log the queries to the general and slow query
log.
(Defaults to on; use --skip-log-queries to disable.)
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to , will print it as a
CHANGE MASTER command; if equal to , that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (on servers before MariaDB 5.3 this will
still take a global read lock for a short time at the
beginning of the dump; don't forget to read about
--single-transaction below). In all cases, any action on
logs will happen at the exact moment of the dump. Option
automatically turns --lock-tables off.
--max-allowed-packet=#
The maximum packet length to send to or receive from
server.
--net-buffer-length=#
The buffer size for TCP/IP and socket communication.
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
normally is output for each dumped database if
--all-databases or --databases is given.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
--no-data-med No row information for engines that Manage External Data
(MRG_MyISAM, MRG_ISAM, CONNECT, OQGRAPH, SPIDER, VP,
FEDERATED).
(Defaults to on; use --skip-no-data-med to disable.)
-N, --no-set-names Same as --skip-set-charset.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't buffer query, dump directly to stdout.
(Defaults to on; use --skip-quick to disable.)
-Q, --quote-names Quote table and column names with backticks (`).
(Defaults to on; use --skip-quote-names to disable.)
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used.
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
(Defaults to on; use --skip-set-charset to disable.)
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
--dump-date Put a dump date to the end of the output.
(Defaults to on; use --skip-dump-date to disable.)
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name The socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags).
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-crl=name Certificate revocation list (implies --ssl).
--ssl-crlpath=name Certificate revocation list path (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
(Defaults to on; use --skip-tz-utc to disable.)
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records. Quotes are mandatory.
-X, --xml Dump a database as well formed XML.
--plugin-dir=name Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use. Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-drop-trigger FALSE
add-locks TRUE
allow-keywords FALSE
apply-slave-statements FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
dump-slave
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
flush-logs FALSE
flush-privileges FALSE
force FALSE
gtid FALSE
hex-blob FALSE
host (No default value)
include-master-host-port FALSE
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
log-queries TRUE
master-data
max-allowed-packet
net-buffer-length
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
no-data-med TRUE
order-by-primary FALSE
port
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket /tmp/mysql.sock
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
ssl-verify-server-cert FALSE
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)
plugin-dir (No default value)
default-auth (No default value)
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqldump --help
11>. 分库备份脚本
核心代码如下:
方案一:
for db in `mysql -e 'show databases' |grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db|gzip > $db`date +%F`.sql.gz;done
方案二:
mysql -e 'show databases' |grep -Ev '^(Database|information_schema|performance_schema)$'|sed -r 's/(.*)/mysqldump -B \1 |gzip > \/data\/\1.sql.gz/' |bash
三.生产环境实战备份策略
1>.MyISAM备份选项
MyISAM支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：
-x,--lock-all-tables：(强烈推荐使用)
加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables：
对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
2>.InnoDB备份选项
InnoDB支持热备，可用温备但不建议用
--single-transaction:(强烈推荐使用)
此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将--single-transaction选项和--quick结合一起使用
3>.InnoDB建议备份策略
mysqldump –uroot –A –F –E –R --single-transaction --master-data= --flush-privileges --triggers --default-character-set=utf8mb4 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
4>.MyISAM建议备份策略
mysqldump –uroot –A –F –E –R –x --master-data= --flush-privileges --triggers --default-character-set=utf8mb4 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
四.模拟生产环境备份实战
1>.模拟生产环境使用
[root@node102.yinzhengjie.org.cn ~]# mkdir -pv /data/logbin
mkdir: created directory ‘/data’
mkdir: created directory ‘/data/logbin’
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# chown -R mysql:mysql /data/logbin
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll -d /data/logbin/
drwxr-xr-x mysql mysql Nov : /data/logbin/
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
log_bin = /data/logbin/mysql-bin
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
character-set-server = utf8mb4
default_storage_engine = InnoDB [mysqld_safe]
log-error = /var/log/mariadb/mariadb.log
pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/
total
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node102.yinzhengjie.org.cn ~]#
将二进制日志和数据文件分开存放并启动mariaDB数据库
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE yinzhengjiedb;
Query OK, row affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW CREATE DATABASE yinzhengjiedb;
+---------------+---------------------------------------------------------------------------+
| Database | Create Database |
+---------------+---------------------------------------------------------------------------+
| yinzhengjiedb | CREATE DATABASE `yinzhengjiedb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+---------------+---------------------------------------------------------------------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjiedb |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE yinzhengjiedb;
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjiedb |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjiedb
Database changed
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> CREATE TABLE students(
-> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR() NOT NULL,
-> sex ENUM('boy','girl') DEFAULT 'boy',
-> age TINYINT UNSIGNED,
-> mobile CHAR(),
-> address VARCHAR()
-> );
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW TABLES;
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| students |
+-------------------------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int() unsigned | NO | PRI | NULL | auto_increment |
| name | varchar() | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint() unsigned | YES | | NULL | |
| mobile | char() | YES | | NULL | |
| address | varchar() | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',,,'beijing'),('Jay','',,'*');
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node102.yinzhengjie.org.cn ~]#
往"yinzhengjiedb"数据库中写入数据
2>.备份数据库
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW TABLE STATUS FROM yinzhengjiedb;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-
--------------------+-------------+------------+--------------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-
--------------------+-------------+------------+--------------------+----------+----------------+---------+| students | InnoDB | | Compact | | | | | | | |
-- :: | NULL | NULL | utf8mb4_general_ci | NULL | | |+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-
--------------------+-------------+------------+--------------------+----------+----------------+---------+ row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW TABLE STATUS FROM yinzhengjiedb\G
*************************** . row ***************************
Name: students
Engine: InnoDB
Version:
Row_format: Compact
Rows:
Avg_row_length:
Data_length:
Max_data_length:
Index_length:
Data_free:
Auto_increment:
Create_time: -- ::
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
row in set (0.00 sec) MariaDB [(none)]>
查看"yinzhengjiedb"数据库的存储引擎
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysqldump -uroot -pyinzhengjie -A --single-transaction -F --master-data= | xz > /root/all_bak_`date +%F`.sql.xz
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -e "SHOW MASTER STATUS"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : all_bak_2019--.sql.xz
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysqldump -uroot -pyinzhengjie -A --single-transaction -F --master-data=2 | xz > /root/all_bak_`date +%F`.sql.xz
3>.备份后，继续模拟数据库使用一段时间(故意让现有的数据和备份时的数据不一致)
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjiedb
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
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> INSERT INTO students SET name='yinzhengjie',age=,address='shanxi';
Query OK, row affected (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> INSERT students (age,sex,name,mobile,address) VALUES (,'girl','Gloria Tang Tsz-Kei',null,'*');
Query OK, row affected (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> FLUSH LOGS;
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> CREATE TABLE employee SELECT * FROM students;
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> CREATE TABLE custom LIKE students;
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW TABLES;
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| custom |
| employee |
| students |
+-------------------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# cat /data/logbin/mysql-bin.index
/data/logbin/mysql-bin.
/data/logbin/mysql-bin.
/data/logbin/mysql-bin.
[root@node102.yinzhengjie.org.cn ~]#
详细操作步骤请使劲戳我！！
4>.模拟删除数据库所有数据
[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
log_bin = /data/logbin/mysql-bin
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
character-set-server = utf8mb4
default_storage_engine = InnoDB
[mysqld_safe]
log-error = /var/log/mariadb/mariadb.log
pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
-rw-rw---- mysql mysql Oct : aria_log.
-rw-rw---- mysql mysql Oct : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Oct : ib_logfile1
drwx------ mysql mysql Oct : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Oct : performance_schema
drwx------ mysql mysql Nov : yinzhengjiedb
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# rm -rf /var/lib/mysql/* #删除数据库的所有内容
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 0
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# systemctl restart mariadb #重启数据库时如果数据库目录没有任何数据他会自动初始化，只不过之前的数据全没了。
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql 16384 Nov 6 11:45 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 6 11:45 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov 6 11:45 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Nov 6 11:45 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Nov 6 11:45 ib_logfile1
drwx------ 2 mysql mysql 4096 Nov 6 11:45 mysql
srwxrwxrwx 1 mysql mysql 0 Nov 6 11:45 mysql.sock
drwx------ 2 mysql mysql 4096 Nov 6 11:45 performance_schema
drwx------ 2 mysql mysql 6 Nov 6 11:45 test
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie #由于删除了所有数据库，因此我们之前配置的密码完全失效
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql #很显然，MariaDB的5.5.64版本默认是没有为root设置密码的
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec) MariaDB [(none)]>
删除Mariadb数据库存放路径的所有文件并重启服务，发现启动后的MariaDB数据库中并没有之前的数据，数据已经全部丢失啦！
5>.模拟通过备份文件和二进制日志进行恢复
[root@node102.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : all_bak_2019--.sql.xz
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# xz -d all_bak_2019--.sql.xz
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll
total
-rw-r--r-- root root Nov : all_bak_2019--.sql
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SET sql_log_bin = OFF;
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SOURCE /root/all_bak_2019--.sql
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) 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
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Database changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.01 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) MariaDB [yinzhengjiedb]>
解压备份文件并使用source命令恢复数据
MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjiedb |
+--------------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> USE yinzhengjiedb
Database changed
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW TABLES;
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| students |
+-------------------------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]>
验证恢复后的数据与实际数据不服（我们不难发现再备份之后的数据全丢啦！）
[root@node102.yinzhengjie.org.cn ~]# head - all_bak_2019--.sql
-- MySQL dump 10.14 Distrib 5.5.-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.-MariaDB /*!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.000002', MASTER_LOG_POS=; #不难发现这里记录着有当时备份的位置 --
-- Current Database: `mysql`
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin. --start-position= > increase.sql #我们基于上面的备份文件可用看出当时备份的结束位置，通过该位置继续往后中找
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.{,,,} >> increase.sql #从上面看出来备份文件是从"mysql-bin.000002"文件的245之后的数据都没有备份，因此我们要将每个文件修改的内容追加到同一个文件中。
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjiedb
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
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW TABLES;
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| students |
+-------------------------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SET sql_log_bin = OFF;
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SOURCE increase.sql;
......
Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) MariaDB [mysql]>
MariaDB [mysql]> use yinzhengjiedb
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
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW TABLES;
+-------------------------+
| Tables_in_yinzhengjiedb |
+-------------------------+
| custom |
| employee |
| students |
+-------------------------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SELECT * FROM custom;
Empty set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SELECT * FROM employee;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | yinzhengjie | boy | | NULL | shanxi |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SET sql_log_bin = ON;
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]>
通过二进制日志还原丢失的部分数据，恢复过程中建议将二进制日志功能暂时关闭，等确认恢复后再打开。