您当前的位置:首页 > 互联网教程

mysql如何导出sql文件

发布时间:2025-05-18 23:04:18    发布人:远客网络

mysql如何导出sql文件

一、mysql如何导出sql文件

在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先我们简单了解一下mysqldump命令用法:

mysqldump-u root-p--databases数据库1数据库2> xxx.sql

mysqldump-uroot-p123456-A>/data/mysqlDump/mydb.sql

2.2备份全部数据库的结构(加-d参数)

mysqldump-uroot-p123456-A-d>/data/mysqlDump/mydb.sql

2.3备份全部数据库的数据(加-t参数)

mysqldump-uroot-p123456-A-t>/data/mysqlDump/mydb.sql

2.4备份单个数据库的数据和结构(,数据库名mydb)

mysqldump-uroot-p123456 mydb>/data/mysqlDump/mydb.sql

mysqldump-uroot-p123456 mydb-d>/data/mysqlDump/mydb.sql

mysqldump-uroot-p123456 mydb-t>/data/mysqlDump/mydb.sql2

2.7备份多个表的数据和结构(数据,结构的单独备份方法与上同)

mysqldump-uroot-p123456 mydb t1 t2>/data/mysqlDump/mydb.sql

mysqldump-uroot-p123456--databases db1 db2>/data/mysqlDump/mydb.sql

二、mysqldump怎样导出多条insert语句

有人问mysqldump出来的insert语句,是否可以按每 10 row一条insert语句的形式组织。

思考1:参数--extended-insert回忆过去所学:

表示使用长 INSERT,多 row在合并一起批量 INSERT,提高导入效率

--skip-extended-insert一行一个的短INSERT

均不满足群友需求,无法控制按每 10 row一条 insert语句的形式组织。

思考2:“避免大事务”之前一直没有考虑过这个问题。这个问题的提出,相信主要是为了“避免大事务”。所以满足 insert均为小事务即可。下面,我们来探讨一下以下问题:1.什么是大事务?

2.那么 mysqldump出来的 insert语句可能是大事务吗?

定义:运行时间比较长,操作的数据比较多的事务我们称之为大事务。

∘锁定太多的数据,造成大量的阻塞和锁超时,回滚所需要的时间比较长。

∘执行时间长,容易造成主从延迟。

避免大事务:我这里按公司实际场景,规定了,每次操作/获取数据量应该少于5000条,结果集应该小于2M

mysqldump出来的SQL文件有大事务吗?

前提,MySQL默认是自提交的,所以如果没有明确地开启事务,一条 SQL语句就是一条事务。在 mysqldump里,就是一条 SQL语句为一条事务。

按照我的“避免大事务”自定义规定,答案是没有的。原来,mysqldump会按照参数--net-buffer-length,来自动切分 SQL语句。默认值是 1M。按照我们前面定义的标准,没有达到我们的 2M的大事务标准。--net-buffer-length最大可设置为 16777216,人手设置大于这个值,会自动调整为 16777216,即 16M。设置 16M,可以提升导出导入性能。如果为了避免大事务,那就不建议调整这个参数,使用默认值即可。[root@192-168-199-198~]# mysqldump--net-buffer-length=104652800-uroot-proot-P3306-h192.168.199.198 test t>16M.sqlmysqldump: [Warning] option'net_buffer_length': unsigned value 104652800 adjusted to 16777216#设置大于16M,参数被自动调整为16M

注意,指的是 mysqldump的参数,而不是 mysqld的参数。官方文档提到: If you increase this variable, ensure that the MySQL server net_buffer_length system variable has a value at least this large.

意思是 mysqldump增大这个值,mysqld也得增大这个值,测试结论是不需要的。怀疑官方文档有误。

不过,在导入的时候,受到服务器参数 max_allowed_packet影响,它控制了服务器能接受的数据包的最大大小,默认值是 4194304,即 4M。所以导入数据库时需要调整参数 max_allowed_packet的值。set global max_allowed_packet=16*1024*1024*1024;

不调整的话,会出现以下报错:[root@192-168-199-198~]# mysql-uroot-proot-P3306-h192.168.199.198 test<16M.sqlmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2006(HY000) at line 46: MySQL server has gone away

相关测试最后,我放出我的相关测试步骤mysql> select version();+------------+| version()|+------------+| 5.7.26-log|+------------+1 row in set(0.00 sec)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t values(1,1,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyztuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');

insert into t select* from t;#重复执行20次

#直到出现Records: 524288 Duplicates: 0 Warnings: 0

mysql> select count(*) from t;

数据大小如下,有 284MB[root@192-168-199-198 test]# pwd/data/mysql/mysql3306/data/test[root@192-168-199-198 test]# du-sh t.ibd284M t.ibd

--net-buffer-length=1M[root@192-168-199-198~]# mysqldump-uroot-proot-S/tmp/mysql3306.sock test t>1M.sql[root@192-168-199-198~]# du-sh 1M.sql225M 1M.sql[root@192-168-199-198~]# cat 1M.sql|grep-i insert|wc-l226

默认--net-buffer-length=1M的情况下,225M的SQL文件里有 226条 insert,平均下来确实就是每条 insert的 SQL大小为 1M。

[root@192-168-199-198~]# mysqldump--net-buffer-length=16M-uroot-proot-S/tmp/mysql3306.sock test t>16M.sql[root@192-168-199-198~]# du-sh 16M.sql225M 16M.sql[root@192-168-199-198~]# cat 16M.sql|grep-i insert|wc-l15

默认--net-buffer-length=16M的情况下,225M的 SQL文件里有 15条 insert,平均下来确实就是每条 insert的 SQL大小为 16M。所以,这里证明了--net-buffer-length确实可用于拆分 mysqldump备份文件的SQL大小的。

性能测试insert次数越多,交互次数就越多,性能越低。但鉴于上面例子的 insert数量差距不大,只有 16倍,性能差距不会很大(实际测试也是如此)。我们直接对比--net-buffer-length=16K和--net-buffer-length=16M的情况,他们insert次数相差了 1024倍。

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16K.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@192-168-199-198~]# mysql-uroot-proot-S/tmp/mysql3306.sock-e"reset master";

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16M.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

结果明显。--net-buffer-length设置越大,客户端与数据库交互次数越少,导入越快。

结论mysqldump默认设置下导出的备份文件,符合导入需求,不会造成大事务。性能方面也符合要求,不需要调整参数。

三、mysql怎么导出数据库sql文件

1、对于数百万条数据的MySQL数据库,导入导出可以使用以下方法:

2、导出:使用mysqldump命令将数据库导出为.sql文件。例如,要导出名为my_database的数据库,请使用以下命令:mysqldump-u username-p my_database> my_database.sql

3、导入:将.sql文件导入到另一个MySQL服务器或同一服务器上的不同数据库中。例如,要将my_database.sql导入到名为my_new_database的数据库中,请使用以下命令:mysql-u username-p my_new_database< my_database.sql

4、使用MySQL Workbench进行导出和导入

5、导出:使用MySQL Workbench连接到MySQL服务器,选择要导出的数据库,右键单击数据库并选择“导出”。选择要导出的表和数据,将导出文件保存为.sql文件。

6、导入:使用MySQL Workbench连接到另一个MySQL服务器或同一服务器上的不同数据库。选择要导入的数据库,右键单击该数据库并选择“导入”,选择要导入的.sql文件并开始导入过程。

7、请注意,在导入导出大量数据时,可能需要考虑以下因素:导出文件的大小、导出过程的时间、导入过程的时间、MySQL服务器的性能和可用空间等等。