mysql数据库的备份与恢复（命令行操作）

mysql数据库的备份与恢复（命令行操作）

备份

数据库的导出

1.语法：

该命令是在命令行界面执行的，我没有配mysql的环境变量，所以需要进入mysql的bin目录下去执行命令。

mysqldump -h 主机ip -u 用户名 -p dbname > 位置\文件名

2.练习：

注意在命令行界面的路径写法与mysql环境中的路径写法的区别。

D:\Tool\mysql-8.0.25\bin>mysqldump -h localhost -u root -p productsales > d:\kaifamiao\DB\productsales.sql
Enter password: ***

D:\Tool\mysql-8.0.25\bin>

导出表

1.导出数据表的表结构

在后面加参数 -d ，就能只导出表的结构了

D:\Tool\mysql-8.0.25\bin>mysqldump -h localhost -u root -p -d productsales product > d:\kaifamiao\DB\product.sql
Enter password: ***

D:\Tool\mysql-8.0.25\bin>

2.导出数据表的表结构和数据

D:\Tool\mysql-8.0.25\bin>mysqldump -h localhost -u root -p productsales product > d:\kaifamiao\DB\product.sql
Enter password: ***

D:\Tool\mysql-8.0.25\bin>

恢复

1.导入外部的数据库sql文件：

需要进入mysql的环境下删除以前的数据库文件

drop database if exists dbname;

在命令行界面下执行命令

mysqldump -h ip -u 用户名 -p < 位置\文件名

例子：

D:\Tool\mysql-8.0.25\bin>mysqldump -h localhost -u kaifamiao -p productsales < d:\kaifamiao\DB\productsales.sql
Enter password: ***
-- MySQL dump 10.13  Distrib 8.0.25, for Win64 (x86_64)
--
-- Host: localhost    Database: productsales
-- ------------------------------------------------------
-- Server version       8.0.25

/*!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 */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 `product`
--

DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `product` (
  `productid` int NOT NULL AUTO_INCREMENT,
  `productname` varchar(100) DEFAULT NULL,
  `price` float DEFAULT NULL,
  PRIMARY KEY (`productid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `product`
--

LOCK TABLES `product` WRITE;
/*!40000 ALTER TABLE `product` DISABLE KEYS */;
INSERT INTO `product` VALUES (1,'HP1200鎵撳嵃鏈?,2000)mysqldump: Got errno 2 on write

2.在数据库中导入外部的表sql文件：

• 先创建数据库
• 使用数据库
• 执行source命令导入表结构和数据

mysql> create database if not exists testdb;
Query OK, 1 row affected (0.01 sec)
mysql> use testdb;
Database changed
mysql> source D:/kaifamiao/DB/product.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> 

检查

D:\Tool\mysql-8.0.25\bin>mysql -ukaifamiao -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaifamiaodb        |
| mysql              |
| performance_schema |
| productsales       |
| sys                |
| testdb             |
+--------------------+
7 rows in set (0.00 sec)

mysql> use productsales;
Database changed
mysql> show tables;
+------------------------+
| Tables_in_productsales |
+------------------------+
| product                |
| sales                  |
+------------------------+
2 rows in set (0.00 sec)

mysql> select * from product;
+-----------+--------------+-------+
| productid | productname  | price |
+-----------+--------------+-------+
|         1 | HP1200打印机 |  2000 |
|         2 | LX360兼容机  |  4800 |
|         3 | IBM350笔记本 | 11000 |
|         4 | IBM360笔记本 | 12000 |
+-----------+--------------+-------+
4 rows in set (0.00 sec)

mysql> use testdb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| product          |
+------------------+
1 row in set (0.00 sec)

mysql> select * from product;
+-----------+--------------+-------+
| productid | productname  | price |
+-----------+--------------+-------+
|         1 | HP1200打印机 |  2000 |
|         2 | LX360兼容机  |  4800 |
|         3 | IBM350笔记本 | 11000 |
|         4 | IBM360笔记本 | 12000 |
+-----------+--------------+-------+
4 rows in set (0.00 sec)

mysql>

导出为文本文件（txt，csv）

mysql> use testdb;
Database changed
mysql> select * from product;
+-----------+--------------+-------+
| productid | productname  | price |
+-----------+--------------+-------+
|         1 | HP1200打印机 |  2000 |
|         2 | LX360兼容机  |  4800 |
|         3 | IBM350笔记本 | 11000 |
|         4 | IBM360笔记本 | 12000 |
+-----------+--------------+-------+
4 rows in set (0.01 sec)

mysql> select * from product into outfile 'D:\\kaifamiao\\DB\\test_product.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> select * from product into outfile 'D:\\kaifamiao\\DB\\test_product.csv'fields terminated by '\,';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql>

报错了：由于secure_file_priv 值为null，不允许导出，
第一条的双\表示转义为一个，第二个sql语句field terminated by ‘,’ 是指用逗号分隔

mysql> show variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

在配置文件中设置一下 secure_file_priv 路径就可以了。

在my.cnf 或者 my.ini 中修改路径为空或者具体路径也行：

secure_file_priv=''