六：备份与恢复 (优先掌握)
语法
mysqldump -u用户名 -p密码 -h主机名 -P端口 -A > 文件名.sql ------------------mysqldump的参数------------------------ -u, --user=name //指定连接的用户名 -p, --password[=name] //指定用户的密码，这里可以指定参数名， -S, --socket=name //指定socket文件连接 用于本地连接 -h, --host=name //指定连接的服务器名 -P, --port=端口 //指定连接的服务器端口号 –tables //导出指定的表对象，格式为 ‘dbname tablename’,如mysqldump -S /tmp/mysql3306.sock test t1，默认会覆盖-B, --databases参数 –single-transaction //在备份开始前，先执行start transaction命令，以此来获得备份的一致性，当前该参数只对InnoDB存储引擎有效。当启用该参数并进行备份时，确保没有其他任何的DDL语句执行（ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE）,因为一致性读并不能隔离DDL操作。启用此参数时，参数--lock-tables将自动禁用。 -A, --all-databases //备份所有数据库 -B, --databases //备份指定的数据库，如mysqldump -S /tmp/mysql3306.sock --database db1 db2 –default-character-set=name //设置字符集，默认为以服务器设置的字符集进行导出。 -l, --lock-tables //以只读方式依次锁住每个库下的所有表，默认启用。使用--skip-lock-tables禁用。不能保证所有库下的表备份一致。 -x, --lock-all-tables //在备份过程中，对所有库的所有表，同时锁定。若指定了本参数，则会自动禁用--single-transaction和-l, --lock-tables参数。 –add-drop-database //在任何创建库语句前，附加drop database语句。 –add-drop-table //在任何建表语句钱，附加drop table语句。默认启用。如果不希望生成drop table语句，可以通过--skip-add-drop-table参数禁用。 –add-drop-trigger //创建任何触发器前，附加drop trigger语句。 –add-locks //在生成的insert语句钱附加lock语句，默认启用。使用 --skip-add-locks来禁用。 –allow-keywords //允许创建使用关键字的列名。 –master-data[=#] //该参数有1和2两个值，如果值等于1，就会在备份文件中添加一个change master语句。如果值为2，就会在备份文件中添加一个带有注释符号的change master语句。 –dump-slave[=#] //该参数用于在从库备份数据，在线搭建新的从库时使用。此参数也有1和2两个值。值为1时，在备份文件中添加一个change master语句。值为2时，就会在备份文件中添加一个带有注释符号的change master语句。 -t, --no-create-info //备份过程中，只备份表数据，不备份表结构 -d, --no-data //备份过程中，只备份表结构，不备份表数据 -c, --complete-insert //使用完整的insert语句会包含表中的列信息，这么做可以提高插入效率。 -q, --quick //表示导出时不会先将数据加载至buffer中，而是直接输出。默认启用，使用 --skip-quick禁用 -w, --where=name //导出给定条件的数据备份与恢复
# 备份数据 ---------------------1. 备份命令---------------------- # 格式： mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql # 示例 mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql -----------------------2. 备份压缩----------------------- # 格式 mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 | gzip > 文件名.sql.gz # 示例 mysqldump -h192.168.1.100 -p 3306 -uroot -ppassword --database cmdb | gzip > /data/backup/cmdb.sql.gz -------------------------3. 备份同个库多个表------------ # 格式： mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 表1 表2 .... > 文件名.sql # 示例 mysqldump -h192.168.1.100 -p3306 -uroot -ppassword cmdb t1 t2 > /data/backup/cmdb_t1_t2.sql ----------------------4. 同时备份多个库-------------- # 格式 mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 数据库名3 > 文件名.sql # 示例 mysqldump -h192.168.1.100 -uroot -ppassword --databases cmdb bbs blog > /data/backup/mutil_db.sql ----------------------5. 备份实例上所有的数据库------------------- # 格式： mysqldump -h主机名 -P端口 -u用户名 -p密码 --all-databases > 文件名.sql # 示例 mysqldump -h192.168.1.100 -p3306 -uroot -ppassword --all-databases > /data/backup/all_db.sql ---------------6. 备份数据出带删除数据库或者表的sql备份----------- # 格式： mysqldump -h主机名 -P端口 -u用户名 -p密码 --add-drop-table --add-drop-database 数据库名 > 文件名.sql # 示例 mysqldump -uroot -ppassword --add-drop-table --add-drop-database cmdb > /data/backup/all_db.sql --------------------7. 备份数据库结构，不备份数据------------- # 格式： mysqldump -h主机名 -P端口 -u用户名 -p密码 --no-data 数据库名1 数据库名2 数据库名3 > 文件名.sql # 示例 mysqldump --no-data –databases db1 db2 cmdb > /data/backup/structure.sql ---------------# 恢复数据--------------------- 第一种：退出数据库之后的恢复 mysql -u用户名 -p密码 < filename.sql; 第二种：在数据库中 source filename; 第三种：迁移数据库 mysqldump -h 源IP -u用户名 -B 数据库名称 | mysql -h 目标IP -u用户名 -p用户密码常用的备份工具：链接
备份方法 备份速度 恢复速度 便捷性 功能 一般用于 cp 快 快 一般，灵活性低 很弱 少量数据备份 mysqldump 慢 慢 一般，可无视存储引擎 一般 中小型数据量备份 lvm2快照 快 快 一般，支持热备，速度快 一般 中小型数据量备份 xtrabackup 较快 较快 实现innodb热备，对存储引擎有要求 强大 较大规模的备份