独角兽企业重金招聘Python工程师标准>>>
mysqldump用于执行逻辑备份的工具,能复制原始schema对象、表数据成SQL语句的集合。能够备份一个或多个数据库,并支持传输备份到其他数据库服务器。也能生成CSV、XML格式的文件。
使用mysqldump产生的文件,可以在还原之前进行查看和编辑。
还原大的数据,因通过重新执行SQL语句涉及到数据插入,索引重建等操作,是非常慢的。对大规模数据的备份和还原,使用物理备份更合适。
mysqldump进行备份常需要的用户权限:
1、SELECT:备份表;
2、SHOW VIEW:备份视图;
3、TRIGGER:备份触发器;
4、LOCK TABLES:如果没有使用single-transaction选项;
5、RELOAD:使用--master-data选项时
常用mysqldump命令:
1、备份一个数据库
mysqldump db_name > backup-file.sql
2、导入备份到服务器
mysql db_name < backup-file.sql
或
mysql -e "source backup-file.sql" db_name
或使用管道将备份数据应用到远程服务器
mysqldump --opt db_name | mysql --host=remote_host -C db_name
3、备份多个数据库
mysqldump --databases db_name1 db_name2 > my_databases.sql
4、备份所有数据库
mysqldump --all-databases > all_databases.sql
5、对Innodb表,使用single-transaction进行在线备份,获得一致性快照
mysqldump --all-databases --single-transaction > all_databases.sql
single-transaction选项:
在开始dump前,使用FLUSH TABLES WITH READ LOCK语句在所有表上获得一个全局读锁,读取二进制日志文件坐标,然后释放锁,之后不影响读和写操作。
6、记录二进制日志坐标,并包含事件,存储程序和函数,用于设置slave等场景
mysqldump --all-databases -ER --master-data=2 > all_databases.sql
或
mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql
如果表使用Innodb存储引擎时,--master-data和--single-transaction两个选项能同时使用,提供一个方便的方式创建在线备份,用于某一时间点的恢复。
7、在slave上备份,并记录其master的坐标,用于设置另外一个slave,或还原slave场景
mysqldump -AER--dump-slave--include-master-host-port> all_databases.sql
-A为所有数据库,-E事件,-R存储程序和函数。如要自动开启slave,可以结合--apply-slave-statements选项,在CHANGE MASTER TO语句中添加MASTER_HOST和MASTER_PORT选项。
8、不备份表数据
mysqldump -Ad > all_databases_desc.sql
mysqldump的限制:
1、默认不备份INFORMATION_SCHEMA数据库,如要备份,需要明确指定数据库名,同时也需要使用--skip-lock-tables选项;
2、从不备份performance_schema数据库;
3、不备份Mysql Cluster的ndbinfo数据库;
4、在Mysql 5.6.6之前,mysqldump不备份mysql数据库的general_log 或slow_query_log表,5.6.6开始,包含重建这些表的语句,但是不包含日志内容;
重要选项:
--max_allowed_packet:
发送和接收的最大包长度,默认24MB,最大1GB;
--add-drop-database
--add-drop-table
--add-drop-trigger
在任何CREATE语句之前添加DROP语句。
--default-character-set=charset_name
设置默认字符集,如果没有指定,默认为utf8,早期版本为latin1,为了避免字符集问题,建议设置为binary。
--apply-slave-statements:
在CHANGE MASTER TO语句之前添加STOP SLAVE语句,在备份文件末尾添加START SLAVE语句,用于slave还原场景。
--delete-master-logs:
在复制环境master服务器,在执行备份后,发送PURGE BINARY LOGS语句删除二进制日志,当使用--master-data时自动启用该选项。
--dump-slave=[value]:
在复制环境slave服务器上进行备份,能设置另外一个slave服务器,都指向同样的master。在备份文件中包含CHANGE MASTER TO语句表明当前slave对应master的二进制日志坐标位置。该选项在备份之前停止slave的SQL线程,然后再重启SQL线程。设置该选项,忽略--master-data,类似master-data行为。
--include-master-host-port:
结合--dump-slave选项,在CHANGE MASTER TO语句中添加slave对应master主机的MASTER_HOST和MASTER_PORT选项。
--master-data=[value]:
在复制环境从master备份数据,用于设置slave,在备份文件中包含CHANGE MASTER TO语句,表明二进制日志坐标(包括文件名和pos),当导入时将执行该语句。如果设置为2,将注释CHANGE MASTER TO语句,在导入时不执行。默认值为1,将不会注释该语句。
使用--master-data选项将自动关闭--lock-tables,打开--lock-all-tables,除非也使用--single-transaction选项。
--set-gtid-purged=value:
控制是否添加SET @@global.gtid_purged语句到备份文件,默认值为AUTO。
--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=...:
结合--tab选择,指定列的分隔符等。
--hex-blob:
使用16进制格式表示二进制列,影响数据类型有BINARY,VARBINARY,BLOB,BIT。
--lines-terminated-by=...:
结合--tab选项,指定行结束符。
--tab=path,-T path:
产生文本格式数据文件,为单个表创建一个tbl_name.sql文件包含CREATE TABLE语句创建表结构,创建一个tbl_name.txt文件包含数据内容。仅仅在mysql服务器本地使用,需要有FILE权限,同时需对指定的目录有写入权限。默认使用tab分隔列值,每行后加换行符。
--xml,-X:
输出备份为XML格式。mysql 5.6.5之前,不能导出存储过程、触发器、事件。
--all-databases,-A:
备份所有数据库,mysql 5.6.4之前,不备份slave_master_info和slave_relay_log_info表。
--events,-E:
备份事件。
--ignore-table=db_name.tbl_name:
不备份给定的表,必须知道数据库名,如要忽略多个表,使用多个该选项,也能忽略视图。
--no-data,-d:
不备份任何表数据。
--routines,-R:
备份存储程序、函数。需要有对mysql.proc表有SELECT权限。但不包含创建和修改时间戳。
--triggers:
备份触发器,默认开启。
--where='where_condition',-w 'where_condition':
备份仅仅选择的行。
--disable-keys,-K:
在还原备份时更快,因索引等所有行插入后才创建。仅仅MyISAM表的非唯一索引有效。
--extended-insert,-e:
使用多行插入,减少备份文件,还原时更快。
--opt:
默认开启,包含--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick--set-charset。使用--skip-opt可以忽略这些组合选项。
--quick,-q:
当备份大表是有用。强制mysqldump每次检索一行,而不是所有行。
--add-locks:
对每个表包含LOCK TABLES和UNLOCK TABLES语句。
--flush-logs,-F:
在开始备份之前刷新mysql 日志,需要RELOAD权限,如果结合--all-databases选项,每个数据库备份都刷新一次日志。如果要保证日志刚好与备份匹配,应该结合使用--lock-all-tables,--master-data,或--single-transaction,使用这些选项只刷新一次日志。
--flush-privileges:
添加FLUSH PRIVILEGES语句到备份mysql数据库输出之后。
--lock-all-tables,-x:
锁定所有表,在备份期间获得全局读锁,如果使用--single-transaction和--lock-tables选项将自动关闭该选项。
--lock-tables,-l:
备份每个数据库之前,锁定数据库里的所有表。使用READ LOCAL,对MyISAM表允许并发插入。
--order-by-primary:
备份每个表,按主键排序,或第一个唯一索引排序。在将myisam表数据导入到Innodb表时有用。操作很耗时。
--single-transaction:
在备份之前,设置事务隔离级别为REPEATABLE READ,同时执行START TRANSACTION语句。仅仅对事务型存储引擎有用,如INNODB。不阻塞任何应用,备份此时数据库的一致状态。而非事务型表如Myisam、MEMORY表将不保证是一致的状态。
同时保证一致性,需要没有其他连接使用以下语句:ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE。
不能同--lock-tables使用。使用LOCK TABLES将隐含提交事务。备份大表结合--quick选项。
来自为知笔记(Wiz)