文章目录
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=''