MySQL 数据库备份的几种方式
mysqldump 工具备份
备份整个数据库
$> mysqldump -u root -h host -p dbname> backdb.sql
备份数据库中的某个表
$> mysqldump -u root -h host -p dbname tbname1, tbname2> backdb.sql
备份多个数据库
$> mysqldump -u root -h host -p --databases dbname1, dbname2> backdb.sql
备份系统中所有数据库
$> mysqldump -u root -h host -p --all-databases> backdb.sql
直接复制整个数据库目录 (对于 InnoDB 存储引擎不适用) 备份
- windowns: installpath/MySQL/data
- Linux: /var/lib/MySQL
在复制前需要先执行如下命令:
- MySQL> LOCK TABLES;
- # 在复制过程中允许客户继续查询表,
- MySQL> FLUSH TABLES;
- # 将激活的索引页写入硬盘.
mysqlhotcopy 备份
备份数据库或表最快的途径, 只能运行在数据库目录所在的机器上, 并且只能备份 MyISAM 类型的表.
要使用该备份方法必须可以访问备份的表文件.
- $> mysqlhotcopy -u root -p dbname /path/to/new_directory;
- # 将数据库复制到 new_directory 目录.
MySQL 命令导入 sql 文件还原
- $> MySQL -u root -p [dbname] <backup.sql
- # 执行前需要先创建 dbname 数据库, 如果 backup.sql 是 mysqldump 创建的备份文件则执行是不需要 dbname.
- MySQL> source backup.sql;
- # 执行 source 命令前需要先选择数据库.
直接复制数据库目录还原
注: 该方式必须确保原数据库和待还原的数据库主版本号一致, 并且只适用于 MyISAM 引擎的表.
关闭 MySQL 服务.
将备份的文件或目录覆盖 MySQL 的 data 目录.
启动 MySQL 服务.
对于 Linux 系统, 复制完文件后需要将文件的用户和组更改为 MySQL 运行的用户和组.
mysqlhotcopy 快速恢复
停止 MySQL 服务, 将备份数据库文件复制到存放数据的位置(MySQL 的 data 文件夹), 重先启动 MySQL 服务即可(可能需要指定数据库文件的所有者).
- $> cp -R /usr/backup/test /usr/local/MySQL/data
- # 如果恢复的数据库已经存在, 则使用 DROP 语句删除已经存在的数据库之后, 恢复才能成功, 还需要保证数据库版本兼容.
相同版本数据库之间迁移
- $> mysqldump -h www.abc.com -uroot -p password dbname |
- $> mysqldump -h www.bcd.com -uroot -p password
- # 将服务器 www.abc.com 的数据库 dbname 迁移到服务器 www.bcd.com 的相同版本数据库上.
不同版本的 MySQL 数据库之间的迁移
备份原数据库.
卸载原数据库.
安装新数据库.
在新数据库中还原备份的数据库数据.
数据库用户访问信息需要备份 MySQL 数据库.
默认字符集问题, MySQL4.x 中使用 latin1 作为默认字符集, mysql5.x 使用 utf8 作为默认字符集. 如果有中文数据需要对默认字符集进行更改.
不同数据库之间的迁移
MyODBC 工具实现 MySQL 和 SQL Server 之间的迁移.
MySQL Migration Toolkit 工具.
表的导出和导入
SELECT ...... INTO OUTFILE 导出文本文件, 该方法只能导出到数据库服务器上, 并且导出文件不能已存在.
- MySQL> SELECT ...... INTO OUTFILE filename [OPTIONS]
- MySQL> SELECT * FROM test.person INTO OUTFILE "C:\person0.txt";
- # 将表 person 里的数据导入为文本文件 person0.txt.
mysqldump 文件导出文本文件(和 INTO OUTFILE 不一样的是该方法所有的选项不需要添加引号)
- $> mysqldump -T path -u root -p dbname [tables] [OPTIONS]
- # -T 参数表明导出文本文件. path 导出数据的目录.
- $> mysqldump -T C:\test person -u root -p
- # 将 test 表中的 person 表导出到文本文件. 执行成功后 test 目录下会有两个文件, person.sql 和 person.txt
MySQL 命令导出文本文件
- MySQL> MySQL -u root -p --execute="SELECT * FROM person;" test> C:\person3.txt;
- # 将 test 数据库中的 person 表数据导出到 person3.txt 文本文件中.--vartical 参数可以将一行分为多行显示.
- MySQL> MySQL -u root -p --vartical --execute="SELECT * FROM person;" test> C:\person3.txt;
- # --html 将表导出为 HTML 文件,--xml 文件将表导出为 xml 文件
LOAD DATA INFILE 导入文本文件
- MySQL> LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES];
- # [IGNORE number LINES]表示忽略行数
- MySQL> LOAD DATA INFILE 'C:\person0.txt' INTO TABLE test.person;
mysqlimport 导入文本文件
- $> mysqlimport -u root -p dbname filename.txt [OPSTONS]
- # 导入的表名有文件名决定, 导入数据之前表必须存在
- $> mysqlimport -uroot -p test C:\backup\person.txt
- # 将数据导入到 test 数据库的 person 表中.
使用 mysqlbinlog 恢复数据
- $> mysqlbinlog [option] filename | MySQL -u user -p password
- # filename 为二进制日志文件,
- $> mysqlbinlog --stop-date="2013-03-30 15:27:47" D:\MySQL\log\binlog\binlog.000008 | MySQL -u root -p password
- # 根据日志文件 binlog.000008 将数据恢复到 2013-03-30 15:27:47 以前的操作.
启动二进制日志
- log-bin = path/filename #日志文件存储目录和文件名
- expire_log_days = 10 #日志自动删除时间
- max_binlog_size = 100M # 日志文件最大大小
查看二进制日志
- MySQL> SHOW VARIABLES LIKE 'log_%';
- MySQL> SHOW BINARY LOGS;
- $> mysqlbinlog filename
- # filename 为二进制日志文件名.
删除二进制日志
- MySQL> RESET MASTER; #删除所有二进制日志
- MySQL> PURGE {
- MASTER | BINARY
- } LOGS TO 'log_name'; #删除文件编号小于 log_name 编号的文件
- MySQL> PURGE {
- MASTER | BINARY
- } LOGS BEFORE 'date'; #删除指定日期以前的文件
暂时停止二进制日志(不需要重启 MySQL 服务)
MySQL> SET sql_log_bin = {0|1} #暂停或启动二进制日志.
转载:
来源: http://www.bubuko.com/infodetail-3460846.html