MySql数据备份恢复 与日志 杜 俊 2012.03 目 录 1、MySql日志分析 3 1.1 log-bin 二进制日志 3 1.1.1 二进制日志属性 3 1.1.2 刷新到新二进制日志文件 4 1.1.3 删除二进制日志文件 4 1.1.4 mysqlbinlog 工具 4 1.2 log-error 错误日志 5 1.3 log 通用查询日志 5 1.4 log-slow-queries 慢速查询日志 5 1.5 log-queries-not-using-indexes 没有使用索引的查询 5 1.6 log-update 更新日志 5 2、MySql数据备份与恢复 5 2.1 mysqldump全量备份与恢复 5 2 使用直接拷贝数据库全量备份与恢复 6 3 MySql增量备份与恢复 6 4、项目数据库备份设计 7 1、MySql日志分析 MySql的日志类型有很多种，可以通过show variables like 'log_%'; 命令来查看目前系统中支持的日志类型。这些日志都是通过在my.cnf中设置启动的。 1.1 log-bin 二进制日志 此二进制日志记录所有数据库的变更，用于数据复制与数据恢复，当mysql创建二进制日志文件时，首先创建一个以“mysql_log_bin”为名称，以“.index”为后缀的文件；再创建一个以“mysql_log_bin”为名称，以“.000001”为后缀的文件。当mysql服务重新启动一次以“.000001”为后缀的文件会增加一个，并且后缀名加1递增；如果日志长度超过了max_binlog_size的上限（默认是1G）也会创建一个新的日志文件； log-bin 设置二进制日志文件路径 expire_logs_days 日志过期多少天后自动删除 1.1.1 二进制日志属性 使用mysql > show binary logs; 或 show master logs; 命令可以查看二进制日志文件名和文件大小。 1.1.2 刷新到新二进制日志文件 使用mysql > flush logs 会创建一个新的日志文件。 1.1.3 删除二进制日志文件 mysql > purge binary logs to 'mysql-bin.000023'; --删除到000023 之前的日志 mysql > reset master --清除所有二进制日志文件，生成新日志文件 1.1.4 mysqlbinlog 工具 将二进制日志转化为普通sql日志 mysqlbinlog mysql-bin.000023 > 000023.sql 将日志中的sql执行执行 mysqlbinlog mysql-bin.000023|mysql -u root -p123456 指定恢复截止时间 mysqlbinlog --stop-date="2007-03-29 8:59:59" /var/lib/mysql/bin.000001 | mysql -u root -p 指定恢复开始时间 mysqlbinlog --start-date="2007-03-29 9:00:00" /var/lib/mysql/bin.000001 | mysql -u root -p 指定恢复开始 mysqlbinlog --stop-position='8879916' /var/log/mysql-bin.000001 | mysql -uroot -p 指定截止位置 mysqlbinlog --start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p 1.2 log-error 错误日志 log-error=d:/mysql_log_error.txt 1.3 log 通用查询日志 log=d:/mysql_log.txt 1.4 log-slow-queries 慢速查询日志 mysqld 写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件 log-slow-queries==d:/mysql_log-slow-queries.txt 1.5 log-queries-not-using-indexes 没有使用索引的查询 log-queries-not-using-indexes=d:/mysql_log-not-using-indexes.txt 1.6 log-update 更新日志 log-update==d:/mysql_log-log-update.txt 2、MySql数据备份与恢复 2.1 mysqldump全量备份与恢复 以文件系统备份的术语讲，数据库备份文件代表完全倾倒（full dump），而更新日志代表渐进倾倒（incremental dump）。 备份MySQL数据库的命令 mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式 mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql 直接将MySQL数据库压缩备份 mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz 备份MySQL数据库某个(些)表 mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql 同时备份多个MySQL数据库 mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql 仅仅备份数据库结构 mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql 备份服务器上所有数据库 mysqldump –all-databases > allbackupfile.sql 将数据库转移到新服务器 mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename 将数据库备份(增加删表语句、锁表、刷新日志) mysqldump --add-drop-table --flush-log --lock-tables -uroot -p123456 testslave > testslave0544.sql 从备份文件恢复MySQL数据库的命令 mysql -hhostname -uusername -ppassword databasename < backupfile.sql 从备份文件恢复压缩的MySQL数据库 gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename 2 使用直接拷贝数据库全量备份与恢复 另一种不涉及 mysqldump 备份数据库和表的方式是直接拷贝数据库表文件。典型地，这用诸如 cp、tar 或 cpio 实用程序。恢复时直接拷贝。但此方法一般适用于MyISAM存储引擎。 3 MySql增量备份与恢复 执行增量备份的前提条件是MySQL打开log-bin日志开关，例如在my.ini或my.cnf中加入log-bin=/opt/Data/MySQL-bin “log-bin=”后的字符串为日志记载目录，一般建议放在不同于MySQL数据目录的磁盘上。恢复命令参见mysql二进制日志部分。注意：此次恢复过程亦会写入日志文件，如果数据量很大，建议先关闭日志功能。Mysql技术文档http://club.topsage.com/thread-921100-1-1.html 4、项目数据库备份设计 1、全量备份部分：凌晨业务操作低谷时期，对数据库进行全量备份，刷新刷新二进制日志文件。 2、增量备份：采取二进制日志实时备份。 2、数据恢复：首先停止数据库日志记录，然后领用全量备份文件恢复到之前版本，最后使用增量备份恢复到最新状态。