MySql数据导入导出及解决ERROR2006(MySQL server has gone away)错误

转载

思月行云 2023-01-10 00:31:47

文章标签 mysql sql 数据库 文章分类 运维

mysql数据导入导出方法总结

MySQL数据备份还原方式总结：

一、将数据导入到指定的数据库

第一种导入方式：

（linux下和Windows 下语法是一样的，只是路劲的书写方式不同而已）

1、创建一个空数据库

2、进入MySQL安装目录bin下

3、导入命令 mysql -uroot –p密码数据库名 < 要导入的表数据（d:\bbs.sql）(将选定的表导入到指定的数据库)

mysql -uroot –padmin databaseName <d:\dump.sql （window）

mysql -uroot –padmin databaseName < /dump.sql （Linux）
（将备份的整个数据库导入）

4、导入命令： mysql -u用户名 -p密码 < 要导入的数据库数据

mysql -uroot -padmin < d:\dump.sql (window)

mysql -uroot -padmin < /dump.sql (linux)

(将备份出来的数据库导入，这里没有写数据库名，也可以写但要求是要写同样的名称)

第二种使用Load Data infile 命令导入：

(这里的格式是在 linux下的在window 下修改路劲的写法就可以)

// 要导入的数据
           Load data infile  '/tmp/dump.txt'   
            // 数据要导入到那个 数据库的那个表，  前提是要有这个表，否则会报 表不存在错误！         
           into  table    linuxtest1.test      
            // fields (属性 字段)    terminated (终止，结束) 属性之间的分隔符 为逗号               
           fields  terminated  by  ','         
            //  optionally(需要，可选择 )  enclosed(封闭)   也就是字符串使用引号                  
           optionally  enclosed by '  " '        
            //   换行符结束一行           
           lines   terminated  by '\n' ;

第三种导入方式即使用source命令：

   Mysql> Use databasename;
   Mysql>Source d:\dump.sql
  导入成功之后
*** 使用第一种方式比较好，推荐使用第一种方式

二、从数据库导出数据

第一种方式使用 mysqldump工具：

（linux 和window 下是同样的语法只是路径的写法不同而已）

1、导出mysql 中的数据库 databaseName

（1）、进入mysql的安装目录 …….bin

（2）、mysqldump –hlocalhost –uroot –p databaseName >d:\dump.sql (window)

mysqldump –hlocalhost –uroot –pdatabaseName >/dump.sql (linux)

（3）、mysqldump -hlocalhost -uroot -padmin --databases databaseName > d:\dump.sql (2 3 是一样的)
2、导出数据库服务器上的所有数据库
mysqldump -hlocalhost -uroot -padmin --all-databases > dump.sql
3、导出mysql数据库databaseName的tb_user表及数据
进入mysql安装目录C:\Program Files\MySQL\MySQLServer 5.5\bin>
mysqldump –hlocalhost –uroot –padmin databaseName tb_user > d:\dump.sql

mysqldump -hlocalhost -uroot -padmin databaseName tb_user --result-file=dump.sql

( 这样写是为了在windows下方便)

4、导出mysql数据库databaseName的结构未实现
进入mysql安装目录C:\ProgramFiles\MySQL\MySQL Server 5.5\bin>
mysqldump –hlocalhost –uroot –padmin databaseName -add-drop-table>d:\dump.sql
5、mysql对导入文件大小有限制，最大为2M，可修改配置
在PHP.ini中修改参数：memory_limit=128M,upload_max_filesize=2M,post_max_size=8M
修改upload_max_filesize=200M；memory_limit=250M,post_max_size=2000M

第二种方式使用： select into outfile ：SQL命令如下

(这种格式也是在linux下，window修改路劲书写方式)

•    // 将你选中的数据导出到这个指定的目录下
•  select  * into  outfile  '/tmp/bump.txt'                         
•     //  属性使用 逗号分开
•  fields terminated  by ','                                              
•    //    
•  optionally   enclosed  by ' " '                                    
•    //  一行结束的标志位换行符
•  lines   terminated  by '\n'       
•    //  这是你要导出的数据库表                               
•  from  linuxtest.test2  ;

三、两种导入导出方式的比较：

使用select into outfile 以及load data infile 这种方式比导出 .sql这种文件更加紧凑，而且速度也更快！

解决ERROR 2006 (HY000) at line XX: MySQL server has gone away问题

MySQL server has gone away就是服务器去了的意思，那么你的mysql数据库提示MySQL server has gone away了我们主要不是看这个是看什么导致它去了哦，下面来看看吧。

今天有在解决公司一个客户网站迁移VPS主机的时候导入数据库出现"ERROR 2006 (HY000) at line 534: MySQL server has gone away"的错误提示问题，一般这样的问题会发生在数据库过大，而且配置文件可能限制最大数据库文件导致的，顺带把解决方法记录下来，以便于下次有使用的时候直接解决。

第一、找到my.cnf文件

根据不同的服务器配置WEB环境，找到当前服务器中的my.cnf文件，然后需要编辑和修改里面的配置参数。

第二、修改max_allowed_packet配置参数

修改mysqld和mysqldump中两处max_allowed_packet参数，尽量将后面的加大一些。

第三、添加wait_timeout

我们需要在上面截图中的MYSQLD中添加一行wait_timeout = 6000参数。

补充一下个人对wait_timeout 的看法:

wait_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题，通常来说，我觉得把wait_timeout设置为10是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：

# vi /etc/my.cnf
[mysqld]
 wait_timeout=10# /etc/init.d/mysql restart

不过这个方法太生硬了，线上服务重启无论如何都应该尽可能避免，看看如何在MySQL命令行里通过SET来设置：

mysql> set global wait_timeout=10;
mysql> show global variables like 'wait_timeout';
 ---------------------------- -------
 | Variable_name              | Value |
 ---------------------------- -------
 | wait_timeout               | 10    |
 ---------------------------- -------

这里一个容易把人搞蒙的地方是如果查询时使用的是show variables的话，会发现设置好像并没有生效，这是因为单纯使用show variables的话就等同于使用的是show session variables，查询的是会话变量，只有使用show global variables，查询的才是全局变量。

网络上很多人都抱怨说他们set global之后使用show variables查询没有发现改变，原因就在于混淆了会话变量和全局变量，如果仅仅想修改会话变量的话，可以使用类似set wait_timeout=10;或者set session wait_timeout=10;这样的语法。

第四、保存替换和重启MYSQL

编译文件之后我们保存文件，然后重启当前WEB服务器数据库，然后我们在导入数据库到是没有错误提示的。