MySQL 备份和恢复
时间：2010-10-26 来源：haiquan517
一． 备份和恢复
1．查看当前的二进制日志文件的文件名：show binlog events;
或：mysql -h hostname -u username -ppwd -e 'show binlog events;'
2． MYSqlhotcopy 备份命令：
mysqlhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库，可自动完成数据锁定工作.它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上。mysqlhotcopy只用于备份MyISAM。
3. 重命名数据库命令：
①. 在MySQL中没有为数据库重命名的命令，但我们可用mysqldump转储数据库，再创建一个新的空数据库，把转储文件加载到该新数据库，这样就完成数据库重命名的工作。如：
% mysqldump old_db >db.sql #转储db数据库数据
% mysqladmin create new_db #新建一个空的数据库
% mysql new_db < db.sql #把db数据库的数据加载到新的数据库中
% mysqladmin drop old_db #删除旧的数据库
②．一个更简单的重命名数据库的方法是直接修改数据库目录名，但该方法不适用于InnoDB和BDB数据库。注意，在更名后，需在权限表中更新相关数据表信息，需执行以下语句：
mysql> UPDATE db SET db='new_db' WHERE db='old_db';
mysql> UPDATE tables_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE columns_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE host SET db='new_db' WHERE db='old_db';
1． 备份所有数据库：
Mysqldump –opt –uroot –p –all-database > all.sql
2． 备份数据库test:
Mysqldump –opt –u root –p test > test.sql
3． 备份数据库test下的表emp:
Mysqldump –opt –u root –p test emp > emp.sql
4． 备份数据库test 下的表emp and dept ：
Mysqldump –opt –u root –p test emp dept > emp_dept.sql
5． 恢复全备：
Mysql –u root –p dbname < bakfile
6． 恢复增量/binlog：
Mysqlbinlog binlog-file | mysql –u root –p
7． 基于时间点恢复
①． 如上午10点发生了误操作，可用下面语句用备份和BINLOG将数据库恢复到故障前：
Mysqlbinlog –stop-date=”2010-07-12 9:59:59” /var/log/mysql/bin.12345 | mysql –u root –p
②．跳过故障时的时间点，继续执行后面的BINLOG，以完成恢复：
Mysqlbinlog –start-data=”2010-07-12 10:01:00” /var/log/mysql/bin.12345 |mysql –u root –p
8． 基于位置恢复
①．在/tmp目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，如前后位置号分别是：36 和 39
Mysqlbinlog –start-date=”2010-07-12 9:55:00” –stop-data=”2010-07-12 10:05:00” /var/log/mysql/bin.12345 > /tmp/mysql_restore.sql
②．开始恢复
Mysqlbinlog –stop-position=”36” /var/log/mysql/bin.12345 |mysql –uroot –p
Mysqlbinlog –start-position=”39” /var/log/mysql/bin.12345 |mysql –uroot –p
上面一行将恢复到停止位置为止的所有事务，下面一行将恢复从给定的起始位置直到二进制日志结束的所有事务。
9． 在线备份InnoDB 引擎数据库：
Mysqldump –all-databases –single-transaction > all_databases.sql
10． 对于InnoDB点对点恢复备份
Mysqldump –all-databases –master-data=2 > all_databases.sql
Mysqldump –all-databases –flush-logs –master-data=2 > all_databases.sql
11． Mysqlhotcopy 备份MyISAM数据库
Mysqlhotcopy db_name /path/to/new_directory
12． Mysqlhotcopy 备份多MyISAM数据库
Mysqlhotcopy db_name_1…db_name_n /path/to/new_directory
13． 压缩备份
Mysqldump –u root –p db_name | gzip > db_name_date.sql.gz
14． 恢复压缩
Gzip < db_name_date.sql.gz |mysql –u root –p
15． FLUSH LOGS SQL语句或mysqladmin flush-logs命令，告诉服务器关闭当前的二进制日志文件并创建一个新文件。
16． 在线备份InnoDB后生成新二进制日志文件
Mysqldump –single-transaction –flush-logs –master-data=2 –all-databases > all.sql
17． 在线备份InnoDB后删除旧的二进制日志文件，并生成新一个二进制日志文件
Mysqldump –single-transaction –flush-logs –master-data=2 –all-databases –delete-master-logs > all.sql
相关阅读 更多 +
