在项目中,遇到需要把MySQL数据导出到本地时,可以通过以下两种方法实现。
本博客使用的MySQL是5.1.73版本。
使用 SELECT … INTO OUTFILE 语句导出数据
select * from tb_words into outfile '/home/fuyun/datas/words.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n';
参数说明:
- fields terminated by:字段分隔符
- enclosed by:字段被包围符
- lines terminated by:行分隔符
mysql语句执行情况:
mysql> select * from tb_words;
+----+-----------+
| id | word |
+----+-----------+
| 1 | hadoop |
| 2 | spark |
| 3 | hadoop |
| 4 | spark |
| 5 | hadoop |
| 6 | mapreduce |
+----+-----------+
6 rows in set (0.00 sec)
导出文件
[fuyun@bigdata-training /]$ cat /temp/words.txt
"1","hadoop"
"2","spark"
"3","hadoop"
"4","spark"
"5","hadoop"
"6","mapreduce"
注意以下几点:
- 输出的目录必须要有写的权限
- 输出的文件不能先创建
- 你的secure_file_priv变量不能为NULL,可以设置为指定路径,或者为空字符串
通过mysqldump命令导出数据和备份SQL
mysqldump -u root -p --tab=/temp/ test tb_words
--tab
:指定输出的文件夹,输出的结果文件会有两个,分别是table_name.sql和table_name.txt,前者存储的是表的SQL语句,后者存储的是表的数据
[fuyun@bigdata-training /]$ cat /temp/tb_words.sql
-- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.1.73
/*!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' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `tb_words`
--
DROP TABLE IF EXISTS `tb_words`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb_words` (
`id` int(11) NOT NULL,
`word` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!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 2021-03-22 4:30:53
[fuyun@bigdata-training /]$ cat /temp/tb_words.txt
1 hadoop
2 spark
3 hadoop
4 spark
5 hadoop
6 mapreduce
整库导出,只会导出sql语句
mysqldump -u root -p test > /temp/database_dump.sql
备份所有库数据,只会导出sql语句
mysqldump -u root -p --all-databases > all_databases_dump.sql
将指定主机的数据库拷贝到本地
如果你需要将远程服务器的数据拷贝到本地,你也可以在 mysqldump 命令中指定远程服务器的IP、端口及数据库名。
在源主机上执行以下命令,将数据备份到 dump.txt 文件中:
请确保两台服务器是相通的
mysqldump -h other-host -P port -u root -p database_name > dump.txt