MYSQL备份恢复

因为MySQL表保存为文件方式，很容易备份。要想保持备份的一致性，对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES。你只需要读锁定；这样当你复制数据库目录中的文件时，允许其它客户继续查询表。需要FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

备份方法：
A.备份的是SQL脚本
    1.导出结构不导出数据
 cmd>mysqldump -B repltest -d --opt > xxx.sql
    2.导出数据不导出结构
 cmd>mysqldump -B repltest -t --opt > xxx.sql
    3.导出数据和表结构
 cmd>mysqldump repltest --opt > xxx.sql
 cmd>mysqldump -B repltest --opt > xxx.sql
 cmd>mysqldump --databases repltest --opt > xxx.sql
    4.导出特定表
 cmd>mysqldump -B repltest --table t1 t2 --opt > xxx.sql
    5.导出跳过特定表
 cmd>mysqldump -B repltest --ignore-table=repltest.t1 --ignore-table=repltest.t2 --opt > xxx.sql

   
B.备份的是纯数据
    6.导出数据，从特定表
 mysql>select * into outfile 'xxx.txt' fields terminated by ',' from t1
    7.导出表结构的SQL脚本及数据文件
 cmd>mysqldump repltest --tab=q:/dbbackup

恢复方法：
A.针对SQL脚本类的备份执行恢复
    1.恢复并覆盖现有数据库。针对完整的数据库备份（SQL脚本包含CREATE DATABASE）
 CMD>mysql -u root -p <xxx.sql
    2.恢复到新的数据库。针对完整的数据库备份（SQL脚本包含CREATE DATABASE）
 首先，删除SQL脚本里CREATE DATABASE的部分
 然后，CMD>mysql -u root -p newDB<xxx.sql
    3.恢复特定的SQL脚本备份
 CMD>mysql -u root -p <xxx.sql
B.针对纯数据类的备份执行恢复
    4.导入数据到特定表（针对select into outfile）
 mysql>load data infile 'xxx.txt' into table db.t1. fields terminated by ',';
    5.导入数据到特定表（使用mysqlimport）
 cmd>mysqlimport DB -u root -p -l -d 't1.txt'

MySQLimport的常用选项介绍:
　　-d or --delete 新数据导入数据表中之前删除数据数据表中的所有信息
　　-f or --force 不管是否遇到错误，MySQLimport将强制继续插入数据
　　-i or --ignore MySQLimport跳过或者忽略那些有相同唯一关键字的行， 导入文件中的数据将被忽略。
　　-l or -lock-tables 数据被插入之前锁住表，这样就防止了，你在更新数据库时，用户的查询和更新受到影响。
　　-r or -replace 这个选项与－i选项的作用相反；此选项将替代表中有相同唯一关键字的记录。
　　--fields-enclosed- by= char 指定文本文件中数据的记录时以什么括起的， 很多情况下数据以双引号括起。 默认的情况下数据是没有被字符括起的。
　　--fields-terminated- by=char 指定各个数据的值之间的分隔符，在句号分隔的文件中，分隔符是句号。您可以用此选项指定数据之间的分隔符。
　　默认的分隔符是跳格符（Tab）
　　--lines-terminated- by=str 此选项指定文本文件中行与行之间数据的分隔字符串或者字符。

备份策略：
1.全库备份：将数据库结构和数据以SQL语句的形式导入到文本中。
 适合数据不是特别大的数据库。因为恢复大量数据时，众多SQL语句使恢复的效率降低。
     注意这种方法将不能指定数据表：
    1.1 以下方式，每次都要手动输入密码
 cmd> mysqldump -u root -p --databases repltest --opt repltest>q:/repltest_full_01.sql
 cmd> mysqldump -u root -p --opt --all-databases>q:/all_db_full_01.sql
    1.2 在选项文件中提供参数，从而避免手动交互式输入密码
 首先，在选项文件中提供参数：
　　  [mysqldump]
　　  user=root
　　  password=mypass
 然后执行：
 cmd> mysqldump --databases repltest --flush-logs --opt repltest>q:/repltest_full_01.sql
 执行之后，数据目录里会产生新的二进制日志文件。

2.增量备份：
    2.1 每次备份后，刷新日志。即会建立新的日志文件，备份后的更新语句都记录在了新的日志文件里，日志文件里的数据就相当于增量备份了。
 cmd> mysqladmin -u root -p flush-logs;
 
备份计划：
1.每周一次全备份，将备份文件转存到备份目录
2.首先，每天一次增量备份，将备份文件转存到备份目录，
  然后，清理一周内无效的日志文件。
3.如出现系统崩溃，先将最后的日志文件转存到备份目录。

查看二进制日志文件
1 mysql> show binary logs;
2 mysql> show master logs;
3 mysql> show binlog events in 'binlog.000004' /G;
4 cmd> mysqlbinlog "fullFilePath/binlog.000004"
5 cmd> mysqlbinlog --start-position=4 --stop-position=20 "fullFilePath/binlog.000004"
6 从增量备份的日志文件中导出数据。
  cmd> mysqlbinlog "fullFilePath/binlog.000004" > q:/increment_log_01.txt

恢复增量备份方法：
将二进制日志文件中的数据，恢复到数据库中
1. cmd> mysqlbinlog "fullFilePath/binlog.000004"  | mysql -u root -p
2. cmd> mysqlbinlog "fullFilePath/binlog.000004" > q:/increment_log_01.txt
   mysql> source q:/increment_log_01.txt
3. cmd> mysql --one-database DB < fullFilePath/binlog.000004
从多个日志文件中恢复数据：
1. cmd> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql
2. cmd> mysqlbinlog hostname-bin.000001 >  q:/increment_log_01.txt
   cmd> mysqlbinlog hostname-bin.000002 >> q:/increment_log_01.txt
   cmd> mysql -e "source q:/increment_log_01.txt"

 

清理无效的二进制日志
PURGE MASTER LOGS to 'binlog.000003'
PURGE MASTER LOGS before '2003-04-02 22:46:26'
注意：如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。