mysql迁移-----拷贝mysql目录/load data/mysqldump/into outfile

摘要；本文简单介绍了mysql的三种备份，并解答了有一些实际备份中会遇到的问题。备份恢复有三种（除了用从库做备份之外）， 直接拷贝文件，load data 和 mysqldump命令。少量数据使用mysqldump命令，存储的是SQL语句，比较通用，但是也会遇到版本兼容性问题。整个数据库迁移使用拷贝文件的方式

(1)数据库到文件  

最快的是登陆到mysql服务器上使用into oufile(mysql线程操作，比较快)

（2）文件到数据库

（3）数据库到数据库

0 使用工具   xtrabackup

1 直接拷贝文件（这个速度最快）

经试验证明，直接拷贝整个目录/opt/mysql,到新的机器上，然后修改my.cnf，重启服务即可。

直接把my.cnf中datadir，innodb_data_home_dir ，innodb_log_group_home_dir目录的文件拷贝至对应的目录下

 数据迁移，换磁盘阵列
-- ===============================================================
新的磁盘阵列上建一个目录/mysqldata/

1 停止mysql数据库
service mysqld stop;

2 复制data目录下所有文件夹到新目录
cp -rp /usr/local/mysql/data/ /mysqldata/

3 修改/etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data/
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/

变换成

[mysqld]
datadir=/mysqldata/data/
innodb_data_home_dir = /mysqldata/data/
innodb_log_group_home_dir = /mysqldata/data/

4 重启数据库
service mysqld start;

近日更换服务器，要做数据库迁移，将数据库内的数据从服务器A迁移到服务器B。
由于数据量较大，直接做dump耗时太长，故而采用如下方式处理：
首先，在服务器B上安装了与服务器A同版本的MySQL，停止MySQL服务，将安装后的data目录删除；
然后，服务器A锁住全部表，从服务器A将整个data目录和数据文件直到拷贝到服务器B上，修改服务器B上MySQL的my.cnf文件中的datadir指向新的data目录。
最后，启动服务器B上的MySQL服务。
结果启动失败，报出 “无法启动MySQL服务”异常，查看错误日志，发现问题描述如下：
InnoDB: Error: log file ./ib_logfile0 is of different size 0 63963136 bytes
InnoDB: than specified in the .cnf file 0 6291456 bytes!
这段信息的意思是日志文件比my.cnf中设置的日志文件配额要大，服务不能启动。原因是日志文件从服务器A复制而来，在服务器A上的my.cnf中日志配额要比服务器B上的my.cnf日志配额大，
计算日志文件大小：63963136/(1024*1024)=61M,6291456/(1024*1024)=6M，怪不得呢？
将服务器B上的my.cnf中的innodb_log_file_size的参数设置为61M，再次启动mysql，启动成功。

总结需要注意事项：
1.修改datadir为新的data目录。
2.合理修改innodb_log_file_size的值为实际迁移过来的日志文件大小。
3.字符集/默认引擎的修改，要与迁移前统一。

详述：

同时拷贝innodb数据库表“*.frm”文件和innodb数据“ibdata1”文件到合适的位置。启动MySQL的Windows服务
由于MySQL这样数据混杂的形式, 往往很容易让使用者在备份时忘记了备份InnoDB, 从而导致了上述错误.

 

意思就是说在数据库引擎类型为InnoDB时，拷贝数据文件的同时还需要拷贝ibdata1，于是把ibdata1也拷贝过去覆盖，发现还是有点问题，于是停止mysql服务，将目录下的ib_logfile*文件全部删除掉，重新启动mysql服务，well done，可以了

此部分详解：http://wenku.baidu.com/link?url=LhTsXpjm36BTkGSyJJQbMi4hHZqkkcVdM1bDY8KBA1pasUDW6rpiJuFprhwLJwOX4IUDsRqCc0jOKxEqYyw3g0J1xHnO69xTop4Zi48UX2S

1   LOAD DATA

（1）mysql> load data infile '/opt/user.txt' into table user;

把本地文件导入服务器的mysql中，添加  LOCAL INFILE

典型的示例

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '
'

（ENCLOSEDBY 指名输出字段被什么所包围，上例中输出字段两边均添加"）

如果您只想载入一个表的部分列，则应指定一个列清单：

LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2);

在客户端也可以向远程MySQL服务器执行 ‘load data‘ 命令，
比如，客户端IP： 192.168.204.132
服务器IP： 192.168.204.131
可以在192.168.204.132上，
执行命令： mysql -h 192.168.204.131 -utest -ptest test -e'load  data local infile "/opt/xxxxx.txt" into table loadtest;'

条件：
如果使用源码编译的MySQL，在configure的时候，需要添加参数：--enable-local-infile 
客户端和服务器端都需要，否则不能使用local参数

导出

SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限

果您想要在服务器主机之外的部分客户主机上创建结果文件，您不能使用SELECT...INTO OUTFILE。在这种情况下，您应该在客户主机上使用比如“mysql –e "SELECT ..." > file_name”的命令，来生成文件。

（2）mysqlimport -uroot -p123 17rumen /opt/user.txt

mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。LOAD DATA命令参见( http://www.wzxue.com/db/mysql/05/15/20/) 

这样调用 mysqlimport： 
shell> mysqlimport [options] db_name textfile1 [textfile2 ...] 
对于在命令行中命名的每个文本文件， mysqlimport去掉文件名的扩展名并使用结果来确定将导入文件内容的表名。例如，文件 patient.txt、patient.text和patient均将导入表patient。 

2  mysqldump备份

mysqldump -u用户名 -p密码 -h主机 数据库 a -w "sql条件" --lock-all-tables > 路径

mysql还原

mysql -halg  -ubroot -ppassword database <./division.sql

使用管道：

mysqldump -u user -p'password' db-name foo | ssh user@remote.box.com mysql -u user -p'password' db-name foo

mysqldump命令详解

几个常用用例： 
1.导出整个数据库 
mysqldump -u 用户名 -p 数据库名 > 导出的文件名 
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 
2.导出一个表 
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql 
3.导出一个数据库结构 
mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql 
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table 
4.导入数据库 
常用source 命令 
进入mysql数据库控制台， 
如mysql -u root -p 
mysql>use 数据库 
然后使用source命令，后面参数为脚本文件（如这里用到的.sql） 
mysql>source d:wcnc_db.sql 

直接用mysqldump导出的文件是文本的，所以会很大。导成文本文件再压缩，过程中仍然要占用额外的空间，如果使用管道，则可以直接导成压缩文件。既迅速，又少占空间。比如：

mysqldump sms | gzip > sms.sql.gz

如果不压缩，我直接导成的文件有3G，压缩了则只有100M，还是非常显著的。

本文综合转载自网上。

3 补充

  Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写） 

参考： http://www.cnblogs.com/cosiray/archive/2012/03/02/2376595.html

4   问题收录

1   大量备份时 出现以下问题。mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `Mapping_event_20131018` at row: 357917

官方解释

This section also covers the related Lost connection to server during queryerror.

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent).

Error Code	Description
CR_SERVER_GONE_ERROR	The client couldn't send a question to the server.
CR_SERVER_LOST	The client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question.

By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you startmysqld. See Section 5.1.4, “Server System Variables”.

分析：

转自：http://www.myexception.cn/operating-system/486737.html

在使用mysqldump的时候（尤其是向NFS上备份的时候），很多人都被’mysqldump:Got error:2013: Lost connection to MySQL server during query when dumping table’的问题困扰，在Manual中对这个问题有一些简单的说明。

在向NFS上备份的时候，数据的流向是这样的：MySQL Server端从数据文件中检索出数据，然后分批将数据返回给mysqldump客户端，然后mysqldump将数据写入到NFS上。一般地，向NFS上写入数据的速度较之Server端检索发送数据的速度要慢得多，这就会导致mysqldump无法及时的接受Server端发送过来的数据，Server端的数据就会积压在内存中等待发送，这个等待不是无限期的，当Server的等待时间超过net_write_timeout（默认是60秒）时它就失去了耐心，mysqldump的连接会被断开，同时抛出错误Got error: 2013: Lost connection。

增加net_write_timeout可以解决上述的问题的。在实践中发现，在增大net_write_timeout后，Server端会消耗更多的内存，有时甚至会导致swap的使用（并不确定是不是修改net_write_timeout所至）。建议在mysqldump之前修改net_write_timeout为一个较大的值（如1800），在mysqldump结束后，在将这个值修改到默认的60。

可能方法：

调整以下参数变大

1) max_allowed_packet 
2)connect_timeout 
3)net_write_timeout 

4）使用quick选项 

 Use the "quick" option when you start mysqldump (to skip 
memory buffering the dump file) and write the data straight to disk as it 
arrives. With a 20GB file it will be very easy to exceed available system 
memory allocation limits.

net_write_timeout

max_allowed_packet = 16M 

net_buffer_length = 32K 

2  额外参数

--single-transaction参数，这样可以让锁表时间变得很短

--single-transaction如果是innodb不加锁啊!相当于对表SELECT，其他会话不能更改表结构。

我10:00备份，中间一直有数据insert和update，一直到10:10，那我备份出来的是10:00的快照？而备份期间的insert和update可以执行？ 对