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/3307/socket/mysql.sock +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yinzhengjie | +--------------------+ [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 ~]# [root@node105.yinzhengjie.org.cn ~]# cat /root/yinzhengjiedb.sql -- MySQL dump 10.16 Distrib 10.2.19-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: yinzhengjie -- ------------------------------------------------------ -- Server version 10.2.19-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(3) unsigned NOT NULL AUTO_INCREMENT, `Class` varchar(100) DEFAULT NULL, `NumOfStu` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ClassID`) ) ENGINE=InnoDB AUTO_INCREMENT=9 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 (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'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(10) unsigned NOT NULL AUTO_INCREMENT, `ClassID` tinyint(3) unsigned NOT NULL, `CourseID` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=15 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 (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3); /*!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(5) unsigned NOT NULL AUTO_INCREMENT, `Course` varchar(100) NOT NULL, PRIMARY KEY (`CourseID`) ) ENGINE=InnoDB AUTO_INCREMENT=8 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 (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7, '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(10) unsigned NOT NULL AUTO_INCREMENT, `StuID` int(10) unsigned NOT NULL, `CourseID` smallint(5) unsigned NOT NULL, `Score` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=16 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 (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6, 1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93);/*!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(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=32 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 (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5, 'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL),(29,'a',10,'F',NULL,NULL),(30,'b',20,'F',NULL,NULL),(31,'c',30,'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(5) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(100) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') DEFAULT NULL, PRIMARY KEY (`TID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 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 (1,'Jason Yin',27,'F'),(2,'YinZhengjie',18,'F'),(3,'Jenny',20,'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(10) unsigned NOT NULL AUTO_INCREMENT, `CourseID` smallint(5) unsigned DEFAULT NULL, `TID` smallint(5) 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 2019-11-06 6:39:36 [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW CREATE DATABASE yinzhengjie" -uroot -pyinzhengjie -S /mysql/3307/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/3307/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/3307/socket/mysql.sock [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/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/3307/sock et/mysql.sock [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/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/3307/socket/mysql.sock [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3307/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/3307/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/3307/socket/mysql.sock +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 29 | a | 10 | F | NULL | NULL | | 30 | b | 20 | F | NULL | NULL | | 31 | c | 30 | F | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ [root@node105.yinzhengjie.org.cn ~]#
2>.备份指定的表(仅备份某个数据库中指定的表，但不包括create database语句，生产环境不推荐使用)
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/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/3307/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/3307/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.19-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: yinzhengjiedb -- ------------------------------------------------------ -- Server version 10.2.19-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(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=32 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 (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5, 'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL),(29,'a',10,'F',NULL,NULL),(30,'b',20,'F',NULL,NULL),(31,'c',30,'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 2019-11-06 6:57:30 [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/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/3307/socket/mysql.sock [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/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/3307/socket/mysql.sock [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3307/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/3307/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/3307/socket/mysql.sock +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 29 | a | 10 | F | NULL | NULL | | 30 | b | 20 | F | NULL | NULL | | 31 | c | 30 | 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/3307/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/3307/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.19-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: yinzhengjiedb -- ------------------------------------------------------ -- Server version 10.2.19-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(3) unsigned NOT NULL AUTO_INCREMENT, `Class` varchar(100) DEFAULT NULL, `NumOfStu` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ClassID`) ) ENGINE=InnoDB AUTO_INCREMENT=9 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 (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'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(10) unsigned NOT NULL AUTO_INCREMENT, `ClassID` tinyint(3) unsigned NOT NULL, `CourseID` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=15 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 (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3); /*!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(5) unsigned NOT NULL AUTO_INCREMENT, `Course` varchar(100) NOT NULL, PRIMARY KEY (`CourseID`) ) ENGINE=InnoDB AUTO_INCREMENT=8 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 (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7, '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(10) unsigned NOT NULL AUTO_INCREMENT, `StuID` int(10) unsigned NOT NULL, `CourseID` smallint(5) unsigned NOT NULL, `Score` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=16 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 (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6, 1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93);/*!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(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=32 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 (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5, 'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL),(29,'a',10,'F',NULL,NULL),(30,'b',20,'F',NULL,NULL),(31,'c',30,'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(5) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(100) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') DEFAULT NULL, PRIMARY KEY (`TID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 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 (1,'Jason Yin',27,'F'),(2,'YinZhengjie',18,'F'),(3,'Jenny',20,'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(10) unsigned NOT NULL AUTO_INCREMENT, `CourseID` smallint(5) unsigned DEFAULT NULL, `TID` smallint(5) 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 2019-11-06 7:09:30 [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -e "DROP DATABASE yinzhengjiedb" -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/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/3307/socket/mysql.sock < /root/yinzhengjiedb.sql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW DATABASES" -uroot -pyinzhengjie -S /mysql/3307/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/3307/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/3307/socket/mysql.sock +-----+-------------+-----+--------+ | TID | Name | Age | Gender | +-----+-------------+-----+--------+ | 1 | Jason Yin | 27 | F | | 2 | YinZhengjie | 18 | F | | 3 | Jenny | 20 | M | +-----+-------------+-----+--------+ [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 ~]# [root@node105.yinzhengjie.org.cn ~]# mysqldump -B yinzhengjiedb -uroot -pyinzhengjie -S /mysql/3307/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/3307/socket/mysql.sock | xz > /root/yinzhengjiedb.sql.xz [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 16 -rw-r--r-- 1 root root 7869 Nov 6 07:23 yinzhengjiedb.sql -rw-r--r-- 1 root root 1901 Nov 6 07:23 yinzhengjiedb.sql.gz -rw-r--r-- 1 root root 1840 Nov 6 07:24 yinzhengjiedb.sql.xz [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 16 -rw-r--r-- 1 root root 7869 Nov 6 07:23 yinzhengjiedb.sql -rw-r--r-- 1 root root 1901 Nov 6 07:23 yinzhengjiedb.sql.gz -rw-r--r-- 1 root root 1840 Nov 6 07:24 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 12 drwxr-xr-x 2 root root 34 Nov 6 07:30 data -rw-r--r-- 1 root root 7869 Nov 6 07:23 yinzhengjiedb.sql -rw-r--r-- 1 root root 1901 Nov 6 07:23 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 4 -rw-r--r-- 1 root root 1840 Nov 6 07:24 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 8 -rw-r--r-- 1 root root 7869 Nov 6 07:24 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 12 drwxr-xr-x 2 root root 31 Nov 6 07:30 data -rw-r--r-- 1 root root 7869 Nov 6 07:23 yinzhengjiedb.sql -rw-r--r-- 1 root root 1901 Nov 6 07:23 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 8 drwxr-xr-x 2 root root 31 Nov 6 07:30 data drwxr-xr-x 2 root root 34 Nov 6 07:32 data2 -rw-r--r-- 1 root root 7869 Nov 6 07:23 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 4 -rw-r--r-- 1 root root 1901 Nov 6 07:23 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 8 -rw-r--r-- 1 root root 7869 Nov 6 07:23 yinzhengjiedb.sql [root@node105.yinzhengjie.org.cn ~/data2]#
4>.使用"-A"选项备份所有数据库，包含create database，存储过程和自定义函数等
[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 ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 136 -rw-r--r-- 1 root root 138038 Nov 6 07:38 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 480 -rw-r--r-- 1 root root 489466 Nov 6 07:38 alldb.sql [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock yinzhengjiedb Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 65 Server version: 10.2.19-MariaDB-log 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 [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, 1 row affected (0.01 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> DROP DATABASE yinzhengjiedb; Query OK, 7 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, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 39 rows affected (0.00 sec) Records: 39 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 464 rows affected (0.00 sec) Records: 464 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1028 rows affected (0.00 sec) Records: 1028 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 508 rows affected (0.03 sec) Records: 508 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 28 rows affected (0.00 sec) Records: 28 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 28 rows affected (0.00 sec) Records: 28 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 14 rows affected (0.01 sec) Records: 14 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 15 rows affected (0.00 sec) Records: 15 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 28 rows affected (0.00 sec) Records: 28 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 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 | +-------------------------+ 7 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> SELECT * FROM teachers; +-----+-------------+-----+--------+ | TID | Name | Age | Gender | +-----+-------------+-----+--------+ | 1 | Jason Yin | 27 | F | | 2 | YinZhengjie | 18 | F | | 3 | Jenny | 20 | M | +-----+-------------+-----+--------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]>
5>.使用"--master-data"选项记录备份时所处于二进制日志所在位置(此选项须启用二进制日志)
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER STATUS" -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 526244 | | | +------------------+----------+--------------+------------------+ [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 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 136 -rw-r--r-- 1 root root 138137 Nov 6 08:08 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 480 -rw-r--r-- 1 root root 489696 Nov 6 08:08 alldb.sql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# head -25 alldb.sql -- MySQL dump 10.16 Distrib 10.2.19-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.2.19-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=526244; -- -- GTID to start replication from [root@node105.yinzhengjie.org.cn ~]#
6>.使用"-F"选项备份前滚动日志
[root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER LOGS" -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 29032 | | mysql-bin.000002 | 365 | | mysql-bin.000003 | 526291 | | mysql-bin.000004 | 385 | +------------------+-----------+ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER STATUS" -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 385 | | | +------------------+----------+--------------+------------------+ [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 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER STATUS" -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 385 | | | +------------------+----------+--------------+------------------+ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# mysql -e "SHOW MASTER LOGS" -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 29032 | | mysql-bin.000002 | 365 | | mysql-bin.000003 | 526291 | | mysql-bin.000004 | 432 | | mysql-bin.000005 | 385 | +------------------+-----------+ [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 136 -rw-r--r-- 1 root root 138135 Nov 6 08:22 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 480 -rw-r--r-- 1 root root 489696 Nov 6 08:22 alldb.sql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# head -30 alldb.sql -- MySQL dump 10.16 Distrib 10.2.19-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.2.19-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=385; -- -- GTID to start replication from -- -- SET GLOBAL gtid_slave_pos='0-1-331'; -- [root@node105.yinzhengjie.org.cn ~]#
7>.使用"--compact"选项去掉注释，适合调试，生产环境不使用
[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 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 136 -rw-r--r-- 1 root root 136528 Nov 6 08:35 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 468 -rw-r--r-- 1 root root 478168 Nov 6 08:35 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=385; -- 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(64) COLLATE utf8_bin NOT NULL, [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]#
8>.使用"-d, --no-data"选项只备份表结构
[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 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 8 -rw-r--r-- 1 root root 4344 Nov 6 08:37 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 36 -rw-r--r-- 1 root root 34967 Nov 6 08:37 alldb.sql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# cat alldb.sql -- MySQL dump 10.16 Distrib 10.2.19-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.2.19-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=385; -- -- 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(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `column_name` varchar(64) COLLATE utf8_bin NOT NULL, `min_value` varbinary(255) DEFAULT NULL, `max_value` varbinary(255) DEFAULT NULL, `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, `hist_size` tinyint(3) unsigned DEFAULT NULL, `hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') COLLATE utf8_bin DEFAULT NULL, `histogram` varbinary(255) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `Column_name` char(64) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(80) 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(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `name` char(64) NOT NULL DEFAULT '', `body` longblob NOT NULL, `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `execute_at` datetime DEFAULT NULL, `interval_value` int(11) 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(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `db_collation` char(32) 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(64) COLLATE utf8_bin NOT NULL DEFAULT '', `ret` tinyint(1) NOT NULL DEFAULT 0, `dl` char(128) 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(10) unsigned NOT NULL, `sub_id` bigint(20) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `seq_no` bigint(20) 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(5) unsigned NOT NULL, `name` char(64) NOT NULL, `parent_category_id` smallint(5) 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(10) unsigned NOT NULL, `name` char(64) 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(10) unsigned NOT NULL, `help_keyword_id` int(10) 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(10) unsigned NOT NULL, `name` char(64) NOT NULL, `help_category_id` smallint(5) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) 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(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `index_name` varchar(64) COLLATE utf8_bin NOT NULL, `prefix_arity` int(11) unsigned NOT NULL, `avg_frequency` decimal(12,4) 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(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(199) COLLATE utf8_bin NOT NULL, `index_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, `stat_value` bigint(20) unsigned NOT NULL, `sample_size` bigint(20) unsigned DEFAULT NULL, `stat_description` varchar(1024) 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=0; /*!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(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(199) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `n_rows` bigint(20) unsigned NOT NULL, `clustered_index_size` bigint(20) unsigned NOT NULL, `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; /*!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(64) NOT NULL DEFAULT '', `dl` varchar(128) 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(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `name` char(64) NOT NULL DEFAULT '', `type` enum('FUNCTION','PROCEDURE') NOT NULL, `specific_name` char(64) 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(141) 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(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `db_collation` char(32) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '', `Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL, `Grantor` char(141) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Proxied_user` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `With_grant` tinyint(1) NOT NULL DEFAULT 0, `Grantor` char(141) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `Role` char(80) 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(64) NOT NULL DEFAULT '', `Host` char(64) NOT NULL DEFAULT '', `Db` char(64) NOT NULL DEFAULT '', `Username` char(80) NOT NULL DEFAULT '', `Password` char(64) NOT NULL DEFAULT '', `Port` int(4) NOT NULL DEFAULT 0, `Socket` char(64) NOT NULL DEFAULT '', `Wrapper` char(64) NOT NULL DEFAULT '', `Owner` char(64) 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(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `cardinality` bigint(21) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `Grantor` char(141) 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(10) 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(20) NOT NULL, `Correction` int(11) 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(64) NOT NULL, `Time_zone_id` int(10) 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(10) unsigned NOT NULL, `Transition_time` bigint(20) NOT NULL, `Transition_type_id` int(10) 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(10) unsigned NOT NULL, `Transition_type_id` int(10) unsigned NOT NULL, `Offset` int(11) NOT NULL DEFAULT 0, `Is_DST` tinyint(3) unsigned NOT NULL DEFAULT 0, `Abbreviation` char(8) 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(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `Password` char(41) 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(11) unsigned NOT NULL DEFAULT 0, `max_updates` int(11) unsigned NOT NULL DEFAULT 0, `max_connections` int(11) unsigned NOT NULL DEFAULT 0, `max_user_connections` int(11) NOT NULL DEFAULT 0, `plugin` char(64) 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(80) COLLATE utf8_bin NOT NULL DEFAULT '', `max_statement_time` decimal(12,6) 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(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), `user_host` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) 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(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), `user_host` mediumtext NOT NULL, `query_time` time(6) NOT NULL, `lock_time` time(6) NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, `db` varchar(512) NOT NULL, `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `rows_affected` int(11) 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(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=32 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(3) unsigned NOT NULL AUTO_INCREMENT, `Class` varchar(100) DEFAULT NULL, `NumOfStu` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ClassID`) ) ENGINE=InnoDB AUTO_INCREMENT=9 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(10) unsigned NOT NULL AUTO_INCREMENT, `ClassID` tinyint(3) unsigned NOT NULL, `CourseID` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=15 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(5) unsigned NOT NULL AUTO_INCREMENT, `Course` varchar(100) NOT NULL, PRIMARY KEY (`CourseID`) ) ENGINE=InnoDB AUTO_INCREMENT=8 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(10) unsigned NOT NULL AUTO_INCREMENT, `StuID` int(10) unsigned NOT NULL, `CourseID` smallint(5) unsigned NOT NULL, `Score` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=16 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(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=32 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(5) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(100) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') DEFAULT NULL, PRIMARY KEY (`TID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 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(10) unsigned NOT NULL AUTO_INCREMENT, `CourseID` smallint(5) unsigned DEFAULT NULL, `TID` smallint(5) 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 2019-11-06 8:37:57 [root@node105.yinzhengjie.org.cn ~]#
9>.使用"-t, --no-create-info"选项只备份数据,不备份create table
[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 [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# ll total 100 -rw-r--r-- 1 root root 102248 Nov 6 08:42 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 448 -rw-r--r-- 1 root root 456552 Nov 6 08:42 alldb.sql [root@node105.yinzhengjie.org.cn ~]# [root@node105.yinzhengjie.org.cn ~]# head -100 alldb.sql -- MySQL dump 10.16 Distrib 10.2.19-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.2.19-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=385; -- -- 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','0000-00-00 00:00:00','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 (1,'Geographic',0,''),(2,'Polygon properties',34,''),(3,'WKT',34,''),(4,'Numeric Functions',38,''),(5,'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 (0,'JOIN'),(1,'HOST'),(2,'REPEAT'),(3,'SERIALIZABLE'),(4,'REPLACE'),(5,'AT'),(6,'SCHEDULE'),(7,'RETURNS'),(8,'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 ~]#
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[=#]： 此选项须启用二进制日志 1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1(一般用于主从复制使用该备份方式,"--master-data=1") 2：记录为注释的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.19-MariaDB, for Linux (x86_64) Copyright (c) 2000, 2018, 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.0 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 1, will printit as a CHANGE MASTER command in the dumped data output; if equal to 2, 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=1 or --dump-slave=1.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 1, will print it as a CHANGE MASTER command; if equal to 2, 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 ( ) 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 0 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 0 max-allowed-packet 16777216 net-buffer-length 1046528 no-autocommit FALSE no-create-db FALSE no-create-info FALSE no-data FALSE no-data-med TRUE order-by-primary FALSE port 3306 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 ~]#
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=1 --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=1 --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 2 mysql mysql 6 Nov 6 11:13 /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 0 [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 8 -rw-rw---- 1 mysql mysql 245 Nov 6 11:18 mysql-bin.000001 -rw-rw---- 1 mysql mysql 30 Nov 6 11:18 mysql-bin.index [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie 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)]> MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> CREATE DATABASE yinzhengjiedb; Query OK, 1 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 */ | +---------------+---------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yinzhengjiedb | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yinzhengjiedb | +--------------------+ 4 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(30) NOT NULL, -> sex ENUM('boy','girl') DEFAULT 'boy', -> age TINYINT UNSIGNED, -> mobile CHAR(11), -> address VARCHAR(50) -> ); Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW TABLES; +-------------------------+ | Tables_in_yinzhengjiedb | +-------------------------+ | students | +-------------------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> DESC students; +---------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | sex | enum('boy','girl') | YES | | boy | | | age | tinyint(3) unsigned | YES | | NULL | | | mobile | char(11) | YES | | NULL | | | address | varchar(50) | YES | | NULL | | +---------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086,'Taiwan'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 941 | +------------------+-----------+ 1 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 8 -rw-rw---- 1 mysql mysql 941 Nov 6 11:26 mysql-bin.000001 -rw-rw---- 1 mysql mysql 30 Nov 6 11:18 mysql-bin.index [root@node102.yinzhengjie.org.cn ~]#
2>.备份数据库
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 4 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)]> 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 | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 10485760 | 3 | 2019-11-06 11:25:20 | NULL | NULL | utf8mb4_general_ci | NULL | | |+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+- --------------------+-------------+------------+--------------------+----------+----------------+---------+1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW TABLE STATUS FROM yinzhengjiedbG *************************** 1. row *************************** Name: students Engine: InnoDB Version: 10 Row_format: Compact Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 10485760 Auto_increment: 3 Create_time: 2019-11-06 11:25:20 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 941 | +------------------+-----------+ 1 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 8 -rw-rw---- 1 mysql mysql 941 Nov 6 11:26 mysql-bin.000001 -rw-rw---- 1 mysql mysql 30 Nov 6 11:18 mysql-bin.index [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 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/ total 12 -rw-rw---- 1 mysql mysql 984 Nov 6 11:33 mysql-bin.000001 -rw-rw---- 1 mysql mysql 245 Nov 6 11:33 mysql-bin.000002 -rw-rw---- 1 mysql mysql 60 Nov 6 11:33 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.000002 | 245 | | | +------------------+----------+--------------+------------------+ [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll total 104 -rw-r--r-- 1 root root 105004 Nov 6 11:33 all_bak_2019-11-06.sql.xz [root@node102.yinzhengjie.org.cn ~]#
3>.备份后，继续模拟数据库使用一段时间(故意让现有的数据和备份时的数据不一致)
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 8 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)]> 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 | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 984 | | mysql-bin.000002 | 245 | +------------------+-----------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi'; Query OK, 1 row affected (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong'); Query OK, 1 row affected (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> FLUSH LOGS; Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 984 | | mysql-bin.000002 | 866 | | mysql-bin.000003 | 245 | +------------------+-----------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> CREATE TABLE employee SELECT * FROM students; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 984 | | mysql-bin.000002 | 866 | | mysql-bin.000003 | 361 | +------------------+-----------+ rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> CREATE TABLE custom LIKE students; Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 984 | | mysql-bin.000002 | 866 | | mysql-bin.000003 | 467 | +------------------+-----------+ 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 16 -rw-rw---- 1 mysql mysql 984 Nov 6 11:33 mysql-bin.000001 -rw-rw---- 1 mysql mysql 866 Nov 6 11:38 mysql-bin.000002 -rw-rw---- 1 mysql mysql 467 Nov 6 11:39 mysql-bin.000003 -rw-rw---- 1 mysql mysql 90 Nov 6 11:38 mysql-bin.index [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cat /data/logbin/mysql-bin.index /data/logbin/mysql-bin.000001 /data/logbin/mysql-bin.000002 /data/logbin/mysql-bin.000003 [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 28700 -rw-rw---- 1 mysql mysql 16384 Oct 25 20:49 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 25 20:49 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Nov 6 11:39 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 6 11:39 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 25 06:26 ib_logfile1 drwx------ 2 mysql mysql 4096 Oct 25 06:26 mysql srwxrwxrwx 1 mysql mysql 0 Nov 6 11:18 mysql.sock drwx------ 2 mysql mysql 4096 Oct 25 06:26 performance_schema drwx------ 2 mysql mysql 78 Nov 6 11:39 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)]>
5>.模拟通过备份文件和二进制日志进行恢复
[root@node102.yinzhengjie.org.cn ~]# ll total 104 -rw-r--r-- 1 root root 105004 Nov 6 11:33 all_bak_2019-11-06.sql.xz [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# xz -d all_bak_2019-11-06.sql.xz [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll total 504 -rw-r--r-- 1 root root 515408 Nov 6 11:33 all_bak_2019-11-06.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 4 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)]> MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SET sql_log_bin = OFF; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> SOURCE /root/all_bak_2019-11-06.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 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, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 39 rows affected (0.00 sec) Records: 39 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 464 rows affected (0.00 sec) Records: 464 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1028 rows affected (0.00 sec) Records: 1028 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 508 rows affected (0.03 sec) Records: 508 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjiedb]>
MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | yinzhengjiedb | +--------------------+ 5 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> USE yinzhengjiedb Database changed MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW TABLES; +-------------------------+ | Tables_in_yinzhengjiedb | +-------------------------+ | students | +-------------------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ 2 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]>
[root@node102.yinzhengjie.org.cn ~]# head -25 all_bak_2019-11-06.sql -- MySQL dump 10.14 Distrib 5.5.64-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 5.5.64-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=245; #不难发现这里记录着有当时备份的位置 -- -- Current Database: `mysql` [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /data/logbin/ total 1068 -rw-rw---- 1 mysql mysql 984 Nov 6 11:33 mysql-bin.000001 -rw-rw---- 1 mysql mysql 866 Nov 6 11:38 mysql-bin.000002 -rw-rw---- 1 mysql mysql 486 Nov 6 11:45 mysql-bin.000003 -rw-rw---- 1 mysql mysql 30394 Nov 6 11:45 mysql-bin.000004 -rw-rw---- 1 mysql mysql 1038814 Nov 6 11:45 mysql-bin.000005 -rw-rw---- 1 mysql mysql 245 Nov 6 11:45 mysql-bin.000006 -rw-rw---- 1 mysql mysql 180 Nov 6 11:45 mysql-bin.index [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000002 --start-position=245 > increase.sql #我们基于上面的备份文件可用看出当时备份的结束位置，通过该位置继续往后中找 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.00000{3,4,5,6} >> 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 7 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)]> 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 | +-------------------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SET sql_log_bin = OFF; Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SOURCE increase.sql; ...... Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 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 | +-------------------------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SELECT * FROM students; +----+---------------------+------+------+--------+-----------+ | id | name | sex | age | mobile | address | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 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 | +----+---------------------+------+------+--------+-----------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | | 3 | yinzhengjie | boy | 27 | NULL | shanxi | | 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong | +----+---------------------+------+------+--------+-----------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]> SET sql_log_bin = ON; Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjiedb]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [yinzhengjiedb]> MariaDB [yinzhengjiedb]>