MySql数据库备份的几种方式

备份数据库中的某个表

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql</span></span></code>

备份多个数据库

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql</span></span></code>

备份系统中所有数据库

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqldump -u root -h host -p --all-databases > backdb.sql</span></span></code>

直接复制整个数据库目录(对于InnoDB存储引擎不适用)备份

windowns: installpath/mysql/data

linux: /var/lib/mysql

在复制前需要先执行如下命令：

<code class="hljs shell"><span class="hljs-meta">MYSQL><span class="bash"> LOCK TABLES;
<span class="hljs-meta">#<span class="bash"> 在复制过程中允许客户继续查询表，
<span class="hljs-meta">MYSQL><span class="bash"> FLUSH TABLES;
<span class="hljs-meta">#<span class="bash"> 将激活的索引页写入硬盘。</span></span></span></span></span></span></span></span></code>

mysqlhotcopy工具备份

备份数据库或表最快的途径，只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。

要使用该备份方法必须可以访问备份的表文件。

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqlhotcopy -u root -p dbname /path/to/new_directory;
<span class="hljs-meta">#<span class="bash">将数据库复制到new_directory目录。</span></span></span></span></code>

mysql命令导入sql文件还原

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysql -u root -p [dbname] < backup.sql
<span class="hljs-meta">#<span class="bash"> 执行前需要先创建dbname数据库，如果backup.sql是mysqldump创建的备份文件则执行是不需要dbname。
<span class="hljs-meta">MYSQL><span class="bash"> <span class="hljs-built_in">source backup.sql;
<span class="hljs-meta">#<span class="bash"> 执行<span class="hljs-built_in">source命令前需要先选择数据库。</span></span></span></span></span></span></span></span></span></span></code>

直接复制数据库目录还原

注： 该方式必须确保原数据库和待还原的数据库主版本号一致，并且只适用于MyISAM引擎的表。

1. 关闭mysql服务。
2. 将备份的文件或目录覆盖mysql的data目录。
3. 启动mysql服务。
4. 对于linux系统，复制完文件后需要将文件的用户和组更改为mysql运行的用户和组。

mysqlhotcopy快速恢复

停止mysql服务，将备份数据库文件复制到存放数据的位置（mysql的data文件夹），重先启动mysql服务即可(可能需要指定数据库文件的所有者)。

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> cp -R /usr/backup/<span class="hljs-built_in">test /usr/<span class="hljs-built_in">local/mysql/data
<span class="hljs-meta">#<span class="bash"> 如果恢复的数据库已经存在，则使用DROP语句删除已经存在的数据库之后，恢复才能成功，还需要保证数据库版本兼容。</span></span></span></span></span></span></code>

相同版本数据库之间迁移

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqldump -h www.abc.com -uroot -p password dbname | 
<span class="hljs-meta">$<span class="bash">> mysqldump -h www.bcd.com -uroot -p password
<span class="hljs-meta">#<span class="bash"> 将服务器www.abc.com的数据库dbname迁移到服务器www.bcd.com的相同版本数据库上。</span></span></span></span></span></span></code>

不同版本的mysql数据库之间的迁移

备份原数据库。

卸载原数据库。

安装新数据库。

在新数据库中还原备份的数据库数据。

数据库用户访问信息需要备份mysql数据库。

默认字符集问题，MySQL4.x中使用latin1作为默认字符集，mysql5.x使用utf8作为默认字符集。如果有中文数据需要对默认字符集进行更改。

不同数据库之间的迁移

MyODBC工具实现MySQL和SQL Server之间的迁移。

MySQL Migration Toolkit工具。

表的导出和导入

SELECT ...... INTO OUTFILE 导出文本文件,该方法只能导出到数据库服务器上，并且导出文件不能已存在。

<code class="hljs shell"><span class="hljs-meta">MYSQL><span class="bash"> SELECT ...... INTO OUTFILE filename [OPTIONS]
<span class="hljs-meta">MYSQL><span class="bash"> SELECT * FROM test.person INTO OUTFILE <span class="hljs-string">"C:\person0.txt";
<span class="hljs-meta">#<span class="bash"> 将表person里的数据导入为文本文件person0.txt。</span></span></span></span></span></span></span></code>

mysqldump文件导出文本文件(和INTO OUTFILE不一样的是该方法所有的选项不需要添加引号)

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqldump -T path -u root -p dbname [tables] [OPTIONS]
<span class="hljs-meta">#<span class="bash"> -T参数表明导出文本文件。path导出数据的目录。
<span class="hljs-meta">$<span class="bash">> mysqldump -T C:\<span class="hljs-built_in">test person -u root -p
<span class="hljs-meta">#<span class="bash"> 将<span class="hljs-built_in">test表中的person表导出到文本文件。执行成功后<span class="hljs-built_in">test目录下会有两个文件，person.sql和person.txt</span></span></span></span></span></span></span></span></span></span></span></code>

mysql命令导出文本文件

<code class="hljs shell"><span class="hljs-meta">MYSQL><span class="bash"> mysql -u root -p --execute=<span class="hljs-string">"SELECT * FROM person;" <span class="hljs-built_in">test > C:\person3.txt;
<span class="hljs-meta">#<span class="bash"> 将<span class="hljs-built_in">test数据库中的person表数据导出到person3.txt文本文件中。--vartical参数可以将一行分为多行显示。
<span class="hljs-meta">MYSQL><span class="bash"> mysql -u root -p --vartical --execute=<span class="hljs-string">"SELECT * FROM person;" <span class="hljs-built_in">test > C:\person3.txt;
<span class="hljs-meta">#<span class="bash"> --html将表导出为html文件，--xml文件将表导出为xml文件</span></span></span></span></span></span></span></span></span></span></span></span></span></code>

LOAD DATA INFILE导入文本文件

<code class="hljs shell"><span class="hljs-meta">MYSQL><span class="bash"> LOAD DATA INFILE <span class="hljs-string">‘filename.txt‘ INTO TABLE tablename [OPTIONS] [IGNORE number LINES];
<span class="hljs-meta">#<span class="bash"> [IGNORE number LINES]表示忽略行数
<span class="hljs-meta">MYSQL><span class="bash"> LOAD DATA INFILE <span class="hljs-string">‘C:\person0.txt‘ INTO TABLE test.person;</span></span></span></span></span></span></span></span></code>

mysqlimport导入文本文件

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqlimport -u root -p dbname filename.txt [OPSTONS]
<span class="hljs-meta">#<span class="bash"> 导入的表名有文件名决定，导入数据之前表必须存在
<span class="hljs-meta">$<span class="bash">> mysqlimport -uroot -p <span class="hljs-built_in">test C:\backup\person.txt
<span class="hljs-meta">#<span class="bash"> 将数据导入到<span class="hljs-built_in">test数据库的person表中。</span></span></span></span></span></span></span></span></span></span></code>

使用mysqlbinlog恢复数据

<code class="hljs shell"><span class="hljs-meta">$<span class="bash">> mysqlbinlog [option] filename | mysql -u user -p password
<span class="hljs-meta">#<span class="bash"> filename为二进制日志文件，
<span class="hljs-meta">$<span class="bash">> mysqlbinlog --stop-date=<span class="hljs-string">"2013-03-30 15:27:47" D:\MySQL\<span class="hljs-built_in">log\binlog\binlog.000008 | mysql -u root -p password
<span class="hljs-meta">#<span class="bash"> 根据日志文件binlog.000008将数据恢复到2013-03-30 15:27:47以前的操作。</span></span></span></span></span></span></span></span></span></span></code>

启动二进制日志

<code class="hljs makefile">log-bin = path/filename <span class="hljs-comment">#日志文件存储目录和文件名
expire_log_days = 10    <span class="hljs-comment">#日志自动删除时间
max_binlog_size = 100M  <span class="hljs-comment"># 日志文件最大大小</span></span></span></code>

查看二进制日志

<code class="hljs shell"><span class="hljs-meta">MYSQL><span class="bash"> SHOW VARIABLES LIKE <span class="hljs-string">‘log_%‘;
<span class="hljs-meta">MYSQL><span class="bash"> SHOW BINARY LOGS;
<span class="hljs-meta">$<span class="bash">> mysqlbinlog filename
<span class="hljs-meta">#<span class="bash"> filename为二进制日志文件名。</span></span></span></span></span></span></span></span></span></code>

删除二进制日志

<code class="hljs shell"><span class="hljs-meta">MYSQL><span class="bash"> RESET MASTER; <span class="hljs-comment">#删除所有二进制日志
<span class="hljs-meta">MYSQL><span class="bash"> PURGE {MASTER | BINARY} LOGS TO <span class="hljs-string">‘log_name‘;  <span class="hljs-comment">#删除文件编号小于log_name编号的文件
<span class="hljs-meta">MYSQL><span class="bash"> PURGE {MASTER | BINARY} LOGS BEFORE <span class="hljs-string">‘date‘;  <span class="hljs-comment">#删除指定日期以前的文件</span></span></span></span></span></span></span></span></span></span></span></code>

暂时停止二进制日志（不需要重启mysql服务）

<code class="hljs shell"><span class="hljs-meta">MYSQL><span class="bash"> SET sql_log_bin = {0|1}  <span class="hljs-comment">#暂停或启动二进制日志。</span></span></span></code>

MySql数据库备份的几种方式

标签：user   base   load data   master   服务器   reset   表数据   sql   存储