MySQL学习9–MySQL5.1的备份恢复

Posted on by
使用mysqldump工具备份
  1. mysql client is a backup program,it can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server(not necessarily a mysql server).The dump contains Sql statements to create the table or populate it, or both;
  2. 它可以导出所有的数据库,指定的一个或者几个数据库,或者一张表;
  3. mysqldump可以备份本地的或者远程的服务器,导出的文件包含CREATE TABLE和INSERT语句用于重新创建表,服务器发送表内容到mysqldump程序,它把文件写到客户端;
  4. 它可以备份各种引擎的表;
  5. 导出的文件是文本文件,所以有很强的灵活性,可以被用于在不同的服务器上;
mysqldump的参数
  1. -A,–all-databases:导出所有的数据库,跟使用–databases后面跟上所有的数据库是一样的;
  2. –add-drop-database:在创建数据库前添加drop database的语句;
  3. –add-drop-table:在创建表之前添加drop table语句;
  4. –add-locks:在插入语句前加锁;
  5. –allow-keywords:创建的列允许使用关键字;
  6. -i,–comments:写入附加信息,即添加注释;
  7. -c,–complete-insert:使用完全插入语句,个人觉得还是-e参数好,数据量小用-e,数据量大用-c;
  8. -B,–databases:备份多个数据库,把要备份的数据库跟在参数后面即可,当前数据库也会被包涵进来;
  9. –delete-master-logs:备份完成后删除主机日志,自动打开–master-data选项;
  10. -e,–extended-insert:使用multiple-row INSERT语句,即一个insert语句后面有多个值的列表,这是一种更高效的插入方式;
  11. -F,–flush-logs:开始备份前切换一下日志,如果你一次备份多个数据库(使用–databases或者–all-databases选项时),则在备份每个数据库前都会切换日志.当使用–lock-all-tables or –master-data时,日志只会被切换一次,因为此时所有的表都被锁住,数据库保持一致.所以当你想要备份和日志组切换同时发生时,要用–lock-all-tables or –master-data和–flush-logs一起使用;
  12. -h,–host=name:连接到主机;-u,–user-name:用户名;-p,–password:用户密码;
  13. –ignore-table=name:不备份指定的表,如果要指定多个表,则要数据库和表明一起指定,如:–ignore-table=database.table;
  14. -x,–lock-all-tables:会锁住所有数据库的表,会在备份期间加全局只读锁,自动关闭–single-transaction和–lock-tables选项;
  15. –master-data[=#]:使得二进制日志的位置和和名称被添加到输出文件中,如果等于1,会像CHANGE MASTER命令一样打印它,如果等于2,命令会以注释的形式出现.这个选项会打开–lock-all-table选项,除非–single-transaction选项也被指定(此时全局只读锁知会在开始备份时有效),可以通过show master status命令查看当前日志信息,在恢复和复制功能时有用.
  16. -n,–no-create-db:不包括创建数据库的语句;
  17. -t,–no-create-info:不包括创建表结构语句;
  18. -d,–no-data:只包含表定义,不包含表数据;
  19. –order-by-primary:使每个表中的记录按照主键排序,如果没有主键,则使用第一个唯一索引.当导出一个MyISAM表到一个InnoDB表时有用,但是会延长导出时间;
  20. –quick:不缓存query,直接导出到标准输出;
  21. -R,–routines:导出stored routines(存储过程和函数);
  22. –single-transaction:在一个事务中创建一个一致性的快照,只在支持多版本控制的引擎中起作用,目前只有innodb引擎.当–single-transaction进程工作时,为了保持数据一致性,则不能使用ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLEY语句,此选项自动关闭–lock-tables选项;
  23. –opt:与同时指定–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys相同.默认开启,要关闭使用–skip-opt;
  24. -w,–where=name:只导出选择的记录;
如何使用mysqldump备份
  1. 非事务表的一致备份:mysqldump –opt –lock-all-tables –master-data=2 -B db_name1 db_name2 > /tmp/backup.sql,备份时表是只读的;
  2. 事务表的一致备份:mysqldump –opt –single-transaction –master-data=2 -B db_name1 db_name2 > /tmp/backup.sql,备份时表是可读写的;
mysqlbinlog工具
  1. 服务器生成的binary log files是二进制文件,想要查看这些文件,必须使用mysqlbinlog工具.还可以使用mysqlbinlog工具读取复制功能slave服务器产生的relay log files,它和二进制日志有相同的结构;
  2. 用法:mysqlbinlog [options] log-files;
mysqlbinlog的参数
  1. -d,–database=name:列出某一个数据库的日志,只用于本地日志;
  2. -f,–force-read:如果mysqlbinlog读到它不能识别的二进制日志,会打印警告而忽略该事件并继续,如果没有该事件则停止;
  3. -o,–offset=#：忽略前N个实体;
  4. -R,–read-from-remote-server:从远程服务器读取二进制日志,如果没有指定此选项,则–host, –user, –password, –port, –protocal, –socket选项都被忽略;
  5. -r,–result-file=name:直接输出到给定的文件;
  6. –start-datetime=time:读取二进制日志的生成开始时间,可以使用任何mysql服务器的时间格式,datetime和timestamp类型,如:’YYYY-MM-DD HH24:MI:SS’;
  7. –stop-datetime=time:读取二进制日志的生成结束时间;
  8. -j,–start-position=#:读取二进制日志的生成开始位置,是一个整型参数;
  9. –stop-position=#:读取二进制日志的生成结束位置,一个整型参数;
  10. -t,–to-last-log:在mysql服务器中请求的二进制日志结尾处不停止,而是继续打印直到最后一个二进制日志的结尾,如果将输出发送给同一台mysql服务器,会导导致无限循环,要与–read-from-remote-server连用;
  11. -D,–disable-log-bin:禁用二进制日志,如果使用–to-last-logs选项将输出发送给同一台mysql服务器,可以避免无限循环,该选项在崩溃恢复也很有用,可以避免复制已经记录的语句;
binlog解读
  1. 二进制日志记录的方式与binlog_format参数有关;
  2. 查看binlog的语法:? binlog;
  3. 记录的内容有:时间戳, 主机server id, 时间结束位置, 时间对象, 时间标志位, 内部binlog命令和相关注释;
mysql数据库的备份与恢复
  1. 只备份routines(存储过程和函数,-R参数,在information_schema.routines表中)和events(作业,-E参数,在information_schema.events表中)信息:mysqldump -n -t -d -R -E > /tmp/routines.sql;
  2. 备份表结构,视图,函数,存储过程和作业的信息:mysqldump -d -R -E -S /mysql/logs/mysqld.sock –databases db_name > /tmp/objects.sql;
  3. 备份数据库test,包括视图信息:mysqldump –opt –lock-all-tables -R -E –master-data=2 -B test > /tmp/test_backup.sql;(查看当前二进制日志的名称和位置:show master logs;);                                                       
  4. 对表进行修改,然后删除:
    1. insert into t values(1, now());
    2. insert into t values(2, now());
    3. insert into t values(3, now());
    4. drop table t;
  5. 查看表删除的位置:mysql>show binlog events;如果要查询某一个日志文件中的内容使用:show binlog events in ‘binlog file name’;                     
  6. 查看表备份文件的位置:grep “CHANGE MASTER” /tmp/test_backup.sql;                           
  7. 还原数据库:mysql < /tmp/test_backup.sql;直接执行备份的脚本文件,还原到备份的状态;
  8. 恢复数据库(注意mysqlbinlog的版本,应该使用mysql basedir下的mysqlbinlog):mysqlbinlog –start-position=117 –stop-position=480 /path/mysql-bin.000001 | mysql; 可以使用–disable-log-bin:禁用二进制日志;                                                   
  9. 建议把备份的数据库恢复到测试服务器,然后把数据导入到生产环境;
表结构的复制
  1. 第一种做法是:CREATE TABLE table_name AS SELECT * FROM tb_name;
    1. 可以复制表接口和表中数据,如果只想要表接口可以加一个false的过滤;
    2. 但是会丢失表中列上面的属性(如自增属性)和索引(主外键);
  2. 第二种做法是:CREATE TABLE table_name(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT * FROM table_name;
    1. 可以在创建表时指定所有的属性,并同步数据;
    2. 但是语法太麻烦;
  3. 第三种做法是:CREATE TABLE table_name LIKE table_name;
    1. 只复制表结构,而且保留列的属性和索引;
    2. 如果想要数据的话可以INSERT table_name SELECT * FROM table_name;

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注