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