我们需要明白,越是在最忙的时候,越需要空出一些时间来应对一些意想不到的事
对于企业,任何数据库的数据都是核心的,如何进行数据的备份,确保数据的安全显得尤为重要。
如今,mysql数据库使用越来约广泛,如何进行mysql数据库的备份也是dba的一个基本技能。本文将详细介绍mysql中的mysqldump命令的使用。
1,命令格式
mysqldump -u root -p dbname > dbname.sql
2,查看备份结果
egrep -v "#|\*|--|^$" dbname.sql
3,参数
--compact:
让输出内容更简洁,适合调试,不是很建议使用
-B:
用于备份多个数据库,如果没有该选项,mysqldump把第一个名字参数作为 数据库名,后面的作为表名
压缩:
mysqldump -u root -p dbname|gzip > dbname.sql
--all-databases, -A:
备份所有数据库
--databases, -B:
用于备份多个数据库,如果没有该选项,mysqldump把第一个名字参数 作为数据库名,后面的作为表名。使用该选项,mysqldum把每个名字都 当作为数据库名。
--force, -f:
即使发现sql错误,仍然继续备份
--host=host_name, -h host_name:
备份主机名,默认为localhost
--no-data, -d:
只导出表结构
--password[=password], -p[password]:
密码
--port=port_num, -P port_num:
制定TCP/IP连接时的端口号
--quick, -q:
快速导出
--master-data:
可以输出日志所在位置(binlog)
-x:--lock-all-tables
-F:
切换binlog
-l: --lock-tables
--single-transaction:
适合innodb事务数据库备份
--routines:
来备份存储过程和函数,触发器默认会备份
4,shell命令生成备份脚本
mysql -u root -p '123456' -e "show databases;" | grep -Evi "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -u root -p '123456' --events -B \1|gzip >/opt/\1.sql.gz#g'
脚本编写:
vi dbbak.sh
for dbname in `mysql -uroot -p'123456' -e "show databases;"|grep -Evi "database|infor|perfor"`do mysqldump -uroot -p'123456' --events -B $dbname|gzip > /opt/bak/${dbname}_bak.sql.gzdone
innodb:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;
5,生产备份
myisam:
mysqldump -uroot -p123456 -A -B --master-data=2 -x | gzip > /opt/all.sql.gz
innodb:
mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction| gzip > /opt/all.sql.gz
6,搭建slave环境
一般有两种方法,对于规模不大的库,可以采用mysqldump来搭建;对 于规模很大的库,最好采用xtrabackup来搭建,速度要快很多。
6.1,说明:
首先 分别在master和slave上设置不同的server_id=1/101,启用master 上的log-bin=1,启用slave上的relog-log=relay-bin; 在master上设置:
binlog_format=row;二进制日志的格式。maser上最好还设置 sync_binlog=1 和 innodb_flush_log_at_trx_commit=1防止发生服务器崩溃时
导致复制破坏。在slave上最好还配置:read-only=1 和 skip-slave-start=1 前者可以防止没有super权限的用户在slave上进行写,后者防止在启动
slave数据库时,自动启动复制线程。以后需要手动start slave来启动复制线程。注意slave没有必要启用 log-bin=1,除非需要搭建二级slave。
因为slave的搭建需要一致性的备份,所以需要启用 --lock-all-tables(master-data=1/2会自动启用--lock-all-tables)或者--single-transaction;
另外还需要知道该一致性备份的数据,对应的master上的binary log的文件名,以及在该文件中的position,所以必须启用 master-data选项。
因为--master-data会启用--lock-all-tables 所以数据才是一致性的;但是导致了全局锁,不能进行任何修改操作;下面我们使用--single-transaction进行优化
6.2,流程:
1)全备:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;
如果要备份mysql库:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases > alldb.sql;
有时,还需要加入:--default-character-set=utf8/utf8mb4 ,该选项一般也可以配置在/etc/my.cnf中。
增量备份:flush logs; 然后将binary log存储起来即可。
2)搭建slave时的最佳选项:
mysqldump -uxxx -p --single-transaction --master-data=2 --routines --databases db1 db2 db3 > alldb.sql;
搭建slave,没有必要 --flush-logs。当然搭建slave的最佳方式是使用 xtrabackup,物理备份。
3)先还原数据库,然后应用增量日志和最新日志,binary log在应用之前需要使用mysqlbinlog命令来处理。