标签：SET OLD -- 备份 mysql leo mysqldump Mysql root
文档课题：使用mysqldump对Mysql进行备份.
1、理论知识
Mysqldump是用于转存储Mysql数据库的实用程序，它可以转储一个或多个MySQL数据库，对其进行备份或传输到远程服务器.其主要产生一组SQL语句，使用 mysqldump 可以更加灵活地控制备份内容，如某个表或某个数据库都能单独备份.
2、准备测试数据
2.1、建测试库
[root@leo-mysql ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database leo;
Query OK, 1 row affected (0.09 sec)
mysql> use leo;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| leo |
+------------+
1 row in set (0.00 sec)
2.2、建测试表
mysql> create table test (id int(2) not null auto_increment,name varchar(10) not null,sex char(5) not null,primary key (id));
Query OK, 0 rows affected (0.04 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(2) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | char(5) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into test values(1,'LEO','man');
Query OK, 1 row affected (0.04 sec)
mysql> insert into test values(2,'ALINA','woman');
Query OK, 1 row affected (0.02 sec)
--验证表信息.
mysql> select * from test;
+----+-------+-------+
| id | name | sex |
+----+-------+-------+
| 1 | LEO | man |
| 2 | ALINA | woman |
+----+-------+-------+
2 rows in set (0.00 sec)
3、备份演练
3.1、备份单表
备份语法：mysqldump -u[用户名] -p[密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名.sql
--使用mysqldump对leo库中的test表进行备份.
[root@leo-mysql ~]# mysqldump -uroot -pmysql_4U leo test > /tmp/test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@leo-mysql tmp]# ll
total 8
srwxrwxrwx 1 mysql mysql 0 Mar 19 15:13 mysqlx.sock
-rw------- 1 mysql mysql 6 Mar 19 15:13 mysqlx.sock.lock
drwx------ 2 liujun liujun 24 Mar 19 15:18 ssh-V60QtYkaxfIy
drwx------ 3 root root 17 Mar 19 15:13 systemd-private-db9bb610a8e34c5d91a1f680a9393b6e-bolt.service-Q6wDEB
drwx------ 3 root root 17 Mar 19 15:13 systemd-private-db9bb610a8e34c5d91a1f680a9393b6e-colord.service-lGr1Uw
drwx------ 3 root root 17 Mar 19 15:13 systemd-private-db9bb610a8e34c5d91a1f680a9393b6e-cups.service-qn5q3d
drwx------ 3 root root 17 Mar 19 15:19 systemd-private-db9bb610a8e34c5d91a1f680a9393b6e-fwupd.service-ieKHg9
drwx------ 3 root root 17 Mar 19 15:13 systemd-private-db9bb610a8e34c5d91a1f680a9393b6e-rtkit-daemon.service-fWSAlF
-rw-r--r-- 1 root root 1903 Mar 19 16:36 test.sql
drwx------. 2 liujun liujun 6 Feb 11 15:05 tracker-extract-files.1000
drwx------ 2 root root 6 Mar 19 15:13 vmware-root_736-2991268455
--查看mysqldump生成的备份内容.
[root@leo-mysql tmp]# cat test.sql
-- MySQL dump 10.13 Distrib 8.0.11, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: leo
-- ------------------------------------------------------
-- Server version 8.0.11
/*!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 */;
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 `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `test` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`sex` char(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1,'LEO','man'),(2,'ALINA','woman');
/*!40000 ALTER TABLE `test` 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 2023-03-19 16:36:19
3.2、单库备份
--对某个库进行完全备份.
[root@leo-mysql ~]# mkdir -p /opt
[root@leo-mysql ~]# mysqldump -uroot -pmysql_4U leo > /opt/test01.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
3.3、多库备份
--对leo sys两个库进行完全备份.
[root@leo-mysql ~]# mysqldump -uroot -pmysql_4U --databases leo sys > /opt/test02.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@leo-mysql opt]# ll
total 296
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
-rw-r--r-- 1 root root 1903 Mar 19 16:51 test01.sql
-rw-r--r-- 1 root root 298616 Mar 19 16:54 test02.sql
[root@leo-mysql opt]# pwd
/opt
3.4、全库备份
--对所有数据库进行完全备份.
[root@leo-mysql ~]# mysqldump -uroot -pmysql_4U --all-databases > /opt/test02.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@leo-mysql opt]# ll
total 860
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
-rw-r--r-- 1 root root 1903 Mar 19 16:51 test01.sql
-rw-r--r-- 1 root root 875504 Mar 19 16:56 test02.sql
-rw-r--r-- 1 root root 0 Mar 19 16:56 test03.sql
3.5、备份表结构
--直接备份表结构.
[root@leo-mysql ~]# mysqldump -uroot -pmysql_4U -d leo test > /opt/test04.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
查看备份内容
[root@leo-mysql opt]# ll
total 864
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
-rw-r--r-- 1 root root 1903 Mar 19 16:51 test01.sql
-rw-r--r-- 1 root root 875504 Mar 19 16:56 test02.sql
-rw-r--r-- 1 root root 0 Mar 19 16:56 test03.sql
-rw-r--r-- 1 root root 1669 Mar 19 16:58 test04.sql
[root@leo-mysql opt]# cat test04.sql
-- MySQL dump 10.13 Distrib 8.0.11, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: leo
-- ------------------------------------------------------
-- Server version 8.0.11
/*!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 */;
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 `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `test` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`sex` char(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 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 2023-03-19 16:58:35
说明：使用-d选项只保存数据库的表结构，可以看到先删除表，再进行创建.删除和创建都是普通的命令，
任何一条在MySQL中都是可以执行的语句，有了这些语句就可以创建出和现在表结构相同的表.
标签：SET,
OLD,
--,
备份,
mysql,
leo,
mysqldump,
Mysql,
root
From： https://blog.51cto.com/u_12991611/6131314