mysq命令mysqldump详解（实现备份)

                              我们需要明白，越是在最忙的时候，越需要空出一些时间来应对一些意想不到的事

 

                           

 

    对于企业，任何数据库的数据都是核心的，如何进行数据的备份，确保数据的安全显得尤为重要。

 

    如今，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命令来处理。

 

                                             欢迎大家关注以下公众号进行数据库方面知识探讨：