1、备份与复制备份与复制 A backup is a static consistent snapshot of the data;it will never change.A replication slave repeats all the operations performed by the master,so its databases constantly change.物理备份物理备份/恢复工具恢复工具 官方:mysqlbackup of MySQL Enterprise Backup 需licence 第三方:xtrabackup逻辑备份逻辑备份/恢复工具恢复工具 备份工具 mys
2、qldump SELECT.INTO OUTFILE 自5.7.8开始,增加了mysqlpump,其功能与用法与mysqldump相似 恢复工具 mysql client LOAD DATA INFILE or mysqlimport(to load delimited-text files)mysqldump的时点恢复的时点恢复(完整恢复完整恢复)备份 执行full backup 开启binary log,即执行增量备份 恢复 恢复full backup 应用增量备份,即binary log*系统变量系统变量secure_file_priv 设置可导出导入的目录 影响select into
3、outfile,load data,mysqldump-tab 默认为/var/lib/mysql-files 设置系统变量secure_file_priv为空,则不再限制导出导入目录mysqldsecure_file_priv=若系统变量secure_file_priv设置为null,则禁止执行导出导入操作mysqldump-导出导出 mysqldump-all-databases file_name 含建库及use db_name命令 哪些库未导出?mysqldump-databases db1 db2 file_name 含建库及use db_name命令 mysqldump other
4、_opts db_name file_name mysqldump other_opts db_name table1 table2 file_name 不含建库命令*mysqldump常用选项参数常用选项参数-single-transaction 导出时,执行一致读取-master-data 指定在导出文件头部附加注释,以说明利用此导出文件恢复后要使用的binlog序号和位置-ignore-table=db_name.table_1 指定要或忽略的表,若忽略多个表,指定此参数多次-no-data 只导出建表语句-no-create-info 不导出建表语句-routines 导出过程和函数(
5、默认不导出,trigger默认导出)mysqldump常用选项参数常用选项参数-tab 每个表可导出两个文本文件,创建空表的.sql,包含数据的.txt文件,可以附加目录 此参数受系统变量secure_file_priv的影响 secure_file_priv默认值为/var/lib/mysql-files/,-tab参数默认只能取此目录mysqldump-导入导入 把dump文件作为SQL脚本文件在mysql客户端执行即可mysqldump导出导入实例导出导入实例1 导出law数据库内容rootlaw mysql#mysqldump-uroot-proot law law.sql说明:导出文
6、件在当前目录下 导入至新建的dmp数据库rootlaw mysql#mysql-uroot-prootmysql create database dmp;mysql use dmp;mysql source law.sqlmysqldump导出导入实例导出导入实例2 导出law数据库的emp表的指定行rootlaw mysql#mysqldump-uroot-proot law emp-where deptno=10 emp_dump.sqlemp-where deptno=10此子句可多表多次 导入bak数据库rootlaw mysql#mysql-uroot-proot bak creat
7、e table t(a int not null,b char(10)not null)-engine=csv;说明:所有列均需附加not null约束,表不能附加主键或唯一约束 输出重定向mysql-uroot-proot law-e select*from dept dept.dmp 导入文本文件的方法导入文本文件的方法*mysqlimport LOAD DATA INFILE*mysqlimport-local:dump文件在客户端(默认在服务器端)-ignore-lines:略过的行数,如-ignore-lines=1-delete:导入前,先清空表-replace/-ignore:替
8、换或忽略已存在的行 select into outfile 导出数据mysql select*from emp -into outfile/var/lib/mysql-files/law.dmp -FIELDS ENCLOSED BY -TERMINATED BY|-ESCAPED BY -LINES TERMINATED BY n;导入数据mysql create table t like emp;mysql load data infile/var/lib/mysql-files/law.dmp -into table t -FIELDS ENCLOSED BY -TERMINATED B
9、Y|-ESCAPED BY -LINES TERMINATED BY n;示例示例(每组的两个命令效果相同每组的两个命令效果相同)导出mysql show global variables like secure_file_priv;-/var/lib/mysql-files/rootlaw tmp#mysqldump-uroot-pRoot1995 law dept-tab=/var/lib/mysql-files/-fields-terminated-by=,-fields-enclosed-by=-fields-escaped-by=/mysql SELECT*FROM dept INT
10、O OUTFILE/var/lib/mysql-files/dept.txt FIELDS TERMINATED BY,ENCLOSED BY ESCAPED BY/;删除dept后重建 导入#mysqlimport-uroot-pRoot1995 law-fields-terminated-by=,-fields-enclosed-by=-fields-escaped-by=/var/lib/mysql-files/dept.txtmysql LOAD DATA INFILE/var/lib/mysql-files/dept.txt INTO TABLE law.dept -FIELDS T
11、ERMINATED BY,ENCLOSED BY ESCAPED BY/;说明:ESCAPED BY/,在表示null等特殊值前附加的符号查看查看binary log内容内容 设置binlog_format为statement导出SQL命令 MySQL 5.7.7之后,binlog_format默认为row 查看row格式的日志内容#mysqlbinlog-start-position=1398-base64-output=DECODE-ROWS-verbose law-bin.000012 bin.log说明:若只使用verbose选项,则会在binlog后面列出与其对应的SQL命令,若使用
12、-vv,则会列出列的类型信息。注意当前目录为导出二进制文件所在目录,否则要使用绝对路径 mysqlbinlog查看日志内容常用选项-start-datetime/-stop-datetime-start-position/-stop-position-database-verbose备份恢复完整步骤备份恢复完整步骤mysql use law;mysql show tables;+-+|Tables_in_law|+-+|dept|emp|+-+2 rows in set(0.00 sec)mysql show master status;+-+-+-|File|Position|+-+-+-|
13、binlog.000037|155|+-+-+-1 row in set(0.00 sec)备份恢复完整步骤备份恢复完整步骤(续续)rootlaw#mysqldump-uroot-pRoot1995 law-flush-logs law_dmp_11271937.sqlrootlaw#more law_dmp_11271937.sqlmysql show master status;+-+-+-|File|Position|+-+-+-|binlog.000038|155|+-+-+-1 row in set(0.00 sec)mysql create table t(a int,b int)
14、;Query OK,0 rows affected(0.10 sec)mysql insert into t values(1,10),(2,20),(3,30);Query OK,3 rows affected(0.03 sec)Records:3 Duplicates:0 Warnings:0备份恢复完整步骤备份恢复完整步骤(续续)mysql flush binary logs;Query OK,0 rows affected(0.01 sec)mysql show master status;+-+-+-+-+-|File|Position|Binlog_Do_DB|Binlog_Ign
15、ore_DB|+-+-+-+-+-|binlog.000039|155|+-+-+-+-+-1 row in set(0.00 sec)备份恢复完整步骤备份恢复完整步骤(续续)mysql drop database law;Query OK,3 rows affected(0.12 sec)mysql create database law;Query OK,1 row affected(0.06 sec)rootlaw#cd/var/lib/mysqlrootlaw mysql#mysqlbinlog-uroot-pRoot1995 binlog.000038|mysql-uroot-pRoot1995 law