mysqldump和mysqlbinlog实现完全备份和增量备份

我会带着你远行 2022-06-05 11:43 227阅读 0赞

**一、数据备份基础：**

**1、备份类型：**

完全备份：整个数据集；

部分部分：只备份数据子集；

增量备份:仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据；

差异备份：仅备份最近一次完全备份以来变化的数据；

**2、备份方式：**

（1）、与存储引擎有关：热备份、温备份、冷备份:

热备：读写操作均可执行；

温备：读操作可执行；但写操作不成；

冷备：读写操作均不可进行；

（2）、与存储引擎无关：物理备份、逻辑备份

物理备份：直接复制数据文件进行备份；

逻辑备份：从数据库中“导出”数据另存而进行的备份；

**3、引擎支持的备份方式：**

MyISAM:支持温备，不能热备

InnoDB:支持热备

**4、备份时需要考虑的因素：**

持续时长、备份过程的时长、备份负载、恢复过程的时长

**5、备份什么**

数据

二进制日志、innodb的事务日志

代码（存储过程、存储函数、触发器、事件调度器）

服务器的配置文件

**6、备份方案的设计：**

数据集：完全+增量；

备份手段：物理、逻辑；

**7、备份工具：**

mysqldump:逻辑备份工具，适用所有存储引擎，温备；完全备份、部分备份；对InnoDB存储引擎支持热备；

cp,tar等复制归档工具：物理备份工具，适用所有存储引擎；冷备：完全备份，部分备份；

mysql> SHOW BINARY|MASTER LOGS; 查看当前服务器所使用的二进制状态

mysql> SHOW MASTER STATUS;查看当前正在使用的二进制状态

mysql> SHOW BINLOG EVENTS IN '' FROMpos; 查看某一个指定的二进制日志中的文件内容

mysqlbinlog:查看二进制日志文件并利用其做增量备份

mysqlbinlog \[OPTION\] log\_file

--start-position        从哪个位置开始做增量备份

--stop-position        增量备份到哪个位置结束

--start-datetime=    从哪个时间点开始做增量备份

--stop-datetime=     增量备份到哪个时间点结束

**二、备份工具详解：mysqldump、mysqlbinlog     **

1、逻辑备份工具：mysqldump，mydumper,phpMyAdmin

（1）、mysqldump：客户端命令，通过mysql协议连接至mysqld服务器

man mysqldump

mysqldump \[options\] \[db\_name \[tbl\_name...\]\]

shell> mysqldump \[options\] db\_name\[tbl\_name ...\]

shell> mysqldump \[options\]--databases db\_name ...

shell> mysqldump \[options\]--all-databases

备份的数据集：

-A，--all-databases              备份所有数据库

-B db\_name,...                备份指定数据库

--databases db\_name,...       备份指定数据库

（2）引擎支持备份方式及实现：

MyISAM:只支持温备；必须锁定备份库，而后才能启动备份操作，

支持锁定的参数如下：

--lock-all-tables, -x ：锁定所有库的所有表

--lock-tables, -l：对于每个单独的数据库，在启动备份之前锁定其所有表；

注意：这两个参数对InnoDB表一样生效，实现温备；

InonoDB：支持热备，可以直接启用备份，无需锁定表。

--single-transaction

（3）、其他选项：

-E,--events:备份指定数据库相关的所有event scheduler;

--routines, -R：备份指定数据库相关的所有存储过程和存储函数；

--triggers：备份表的相关的触发器；

--master-data ：

1)、记录为CHANGE MASTER TO语句，此语句不被注释；

2)、记录为注释的CHANGE MASTER TO语句

mysqldump--master-data=2 --all-databases > dumpfile

--flush-logs:日志滚动

锁定表完成后，执行flush logs命令；

**2、备份建议：         **

二进制日志文件不应该与数据文件放在同一磁盘；

建议还原时关闭二进制日志记录

mysql> SHOW GLOBAL VARIABLES LIKE'%log%';

sql\_log\_bin  ON

mysql> SET sql\_log\_bin = OFF；

**三、利用mysqldump做完全备份还原**

**1、mysqldump做部分备份和完全备份：                    **

\[root@www ~\]\# cphellodb.sql /root/

cp:"hellodb.sql" 与"/root/hellodb.sql" 为同一文件

\[root@www ~\]\#file hellodb.sql

hellodb.sql:ASCII text, with very long lines

\[root@www ~\]\#less hellodb.sql

\-- MySQL dump10.13  Distrib 5.5.33, for Linux (x86\_64)

\--

\-- CurrentDatabase: \`hellodb\`

CREATE DATABASE/\*!32312 IF NOT EXISTS\*/ \`hellodb\` /\*!40100 DEFAULT CHARACTER SET utf8 \*/;

USE \`hellodb\`;

\-- Tablestructure for table \`classes\`

DROP TABLE IFEXISTS \`classes\`;

/\*!40101 SET@saved\_cs\_client     =@@character\_set\_client \*/;

/\*!40101 SETcharacter\_set\_client = utf8 \*/;

CREATE TABLE\`classes\` (

\`ClassID\` tinyint(3) unsigned NOT NULLAUTO\_INCREMENT,

\`Class\` varchar(100) DEFAULT NULL,

\`NumOfStu\` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (\`ClassID\`)

) ENGINE=MyISAMAUTO\_INCREMENT=9 DEFAULT CHARSET=utf8;

/\*!40101 SETcharacter\_set\_client = @saved\_cs\_client \*/;

\#修改MyISAM引擎为InnoDB，使其支持热备

\[root@www ~\]\#sed -i 's/MyISAM/InonoDB/g' hellodb.sql

\[root@www ~\]\#sed -i 's/InonoDB/InnoDB/g' hellodb.sql

\[root@www ~\]\#less hellodb.sql

\-- MySQL dump10.13  Distrib 5.5.33, for Linux (x86\_64)

\--

\-- Host:localhost    Database: hellodb

\--------------------------------------------------------

\-- Serverversion       5.5.33-log

\-- MySQL dump10.13  Distrib 5.5.33, for Linux (x86\_64)

\--

\-- Host:localhost    Database: hellodb

\--------------------------------------------------------

\-- Serverversion       5.5.33-log

DROP TABLE IFEXISTS \`classes\`;

/\*!40101 SET@saved\_cs\_client     =@@character\_set\_client \*/;

/\*!40101 SETcharacter\_set\_client = utf8 \*/;

CREATE TABLE\`classes\` (

\`ClassID\` tinyint(3) unsigned NOT NULLAUTO\_INCREMENT,

\`Class\` varchar(100) DEFAULT NULL,

\`NumOfStu\` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (\`ClassID\`)

) ENGINE=InnoDBAUTO\_INCREMENT=9 DEFAULT CHARSET=utf8;

/\*!40101 SETcharacter\_set\_client = @saved\_cs\_client \*/;

\--

\-- Dumping datafor table \`classes\`

\--

mysql> SHOWDATABASES;

\+--------------------+

| Database          |

\+--------------------+

|information\_schema |

| hellodb             |

| mysql               |

| test                  |

| testdb              |

\+--------------------+

5 rows in set(0.04 sec)

mysql> usehellodb

Reading tableinformation for completion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql> SHOWTABLES;

\+-------------------+

|Tables\_in\_hellodb |

\+-------------------+

| classes            |

| coc                 |

| courses           |

| scores             |

| students          |

| teachers          |

| toc                  |

\+-------------------+

7 rows in set(0.00 sec)

mysql> SELECT\* FROM students;

\+-------+---------------+-----+--------+---------+-----------+

\+-------+---------------+-----+--------+---------+-----------+

|     1 | Shi Zhongyu   |  22| M      |       2 |         3 |

|     2 | Shi Potian    |  22| M      |       1 |         7 |

|     3 | Xie Yanke     | 53 | M      |       2 |        16 |

|     4 | Ding Dian     | 32 | M      |       4 |         4 |

|     5 | Yu Yutong     | 26 | M      |       3 |         1 |

|     6 | Shi Qing      | 46 | M      |       5 |     NULL |

|     7 | Xi Ren        | 19 | F      |       3 |     NULL |

|     8 |Lin Daiyu     |  17 | F     |       7 |      NULL |

|     9 | Ren Yingying  |  20 |F      |       6 |     NULL |

|    10 | Yue Lingshan  |  19 |F      |       3 |     NULL |

|    11 | Yuan Chengzhi |  23 | M     |       6 |      NULL |

|    12 | Wen Qingqing  |  19 |F      |       1 |     NULL |

|    13 | Tian Boguang  |  33 |M      |       2 |     NULL |

|    14 | Lu Wushuang   |  17| F      |       3 |     NULL |

|    15 | Duan Yu       | 19 | M      |       4 |     NULL |

|    16 | Xu Zhu        | 21 | M      |       1 |     NULL |

|    17 | Lin Chong     | 25 | M      |       4 |     NULL |

|    18 | Hua Rong      | 23 | M      |       7 |     NULL |

|    19 | Xue Baochai   |  18| F      |       6 |     NULL |

|    20 | Diao Chan     | 19 | F      |       7 |     NULL |

|    21 | Huang Yueying |  22 | F     |       6 |    NULL|

|    22 | Xiao Qiao     | 20 | F      |       1 |       NULL |

|    23 | Ma Chao       | 23 | M      |       4 |     NULL |

|    24 | Xu Xian       |  27| M      |    NULL |     NULL |

|    25 | Sun Dasheng   | 100 | M      |   NULL |  NULL |

\+-------+---------------+-----+--------+---------+-----------+

25 rows in set(0.01 sec)

mysql> \\q

Bye

\[root@www ~\]\#mysqldump -uroot

Usage: mysqldump\[OPTIONS\] database \[tables\]

OR     mysqldump \[OPTIONS\] --databases \[OPTIONS\]DB1 \[DB2 DB3...\]

OR     mysqldump \[OPTIONS\] --all-databases\[OPTIONS\]

For moreoptions, use mysqldump --help

\[root@www ~\]\#mysqldump -uroot --databases hellodb

没有输出重定向则默认输出

\-- MySQL dump10.13  Distrib 5.1.73, forredhat-linux-gnu (x86\_64)

\-- Host:localhost    Database: hellodb

\--------------------------------------------------------

\-- Serverversion       5.1.73

CREATE DATABASE/\*!32312 IF NOT EXISTS\*/ \`hellodb\` /\*!40100 DEFAULT CHARACTER SET utf8 \*/;

.......

部分备份：将hellodb.sql备份到/root/hellodb.sql

\[root@www ~\]\#mysqldump -uroot --databases hellodb > /root/hellodb.sql

\[root@www ~\]\# ls

hellodb.sql

\[root@www ~\]\#mkdir /root/sqlbak

\[root@www ~\]\#mysqldump -uroot --database hellodb >/root/sqlbak/hellodb.sql

\[root@www ~\]\# cdsqlbak

\[root@wwwsqlbak\]\# ls

hellodb.sql

\[root@wwwsqlbak\]\# less hellodb.sql

\-- MySQL dump10.13  Distrib 5.1.73, forredhat-linux-gnu (x86\_64)

\--

\-- Host:localhost    Database: hellodb

\--------------------------------------------------------

\-- Serverversion       5.1.73

CREATE DATABASE/\*!32312 IF NOT EXISTS\*/ \`hellodb\` /\*!40100 DEFAULT CHARACTER SET utf8 \*/;

DROP TABLE IFEXISTS \`classes\`;

/\*!40101 SET@saved\_cs\_client     =@@character\_set\_client \*/;

/\*!40101 SETcharacter\_set\_client = utf8 \*/;

CREATE TABLE\`classes\` (

\`ClassID\` tinyint(3) unsigned NOT NULLAUTO\_INCREMENT,

\`Class\` varchar(100) DEFAULT NULL,

\`NumOfStu\` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (\`ClassID\`)

) ENGINE=InnoDBAUTO\_INCREMENT=9 DEFAULT CHARSET=utf8;

/\*!40101 SETcharacter\_set\_client = @saved\_cs\_client \*/;

......

\#将hellodb、testdb两个数据库都备份到/root/sqlbak/hellodb\_mydb.sql下

\[root@www ~\]\#mysqldump -uroot --databases hellodb testdb > /root/sqlbak/hellodb\_mydb.sql

\[root@www ~\]\# cdsqlbak

\[root@wwwsqlbak\]\# ls

hellodb\_mydb.sql  hellodb.sql

\[root@wwwsqlbak\]\#

\[root@wwwsqlbak\]\# cd

备份所有数据库的数据到/root/sqlbak/all.sql文件下

\[root@www ~\]\#mysqldump -uroot --all-databases --lock-all-tables --master-data=2 >/root/sqlbak/all.sql

mysqldump:Error: Binlogging on server not active

提示Binlogging为启动，

编辑/etc/my.cnf文件，设置log\_bin=ON

\[root@www etc\]\#vim /etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

\# Disablingsymbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log\_bin=ON

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

\[root@www ~\]\#service mysqld restart

停止 mysqld： \[确定\]

正在启动 mysqld： \[确定\]

\[root@www ~\]\#mysql

Welcome to theMySQL monitor.  Commands end with ; or\\g.

mysql> SHOWGLOBAL VARIABLES LIKE '%log%';

\+-----------------------------------------+---------------------------------+

|Variable\_name                           |Value                              |

\+-----------------------------------------+---------------------------------+

| back\_log                                | 50                                      |

|binlog\_cache\_size                       |32768                             |

|binlog\_direct\_non\_transactional\_updates | OFF                      |

|binlog\_format                           |STATEMENT                      |

|expire\_logs\_days                        |0                                    |

| general\_log                             | OFF                                 |

|general\_log\_file                        |/var/run/mysqld/mysqld.log      |

|innodb\_flush\_log\_at\_trx\_commit          |1                               |

|innodb\_locks\_unsafe\_for\_binlog          |OFF                             |

|innodb\_log\_buffer\_size                  |1048576                         |

|innodb\_log\_file\_size                    |5242880                         |

|innodb\_log\_files\_in\_group               |2                               |

|innodb\_log\_group\_home\_dir               |./                              |

|innodb\_mirrored\_log\_groups              |1                               |

| log                                     | OFF                             |

| log\_bin                                 | ON                              |

|log\_bin\_trust\_function\_creators         |OFF                             |

|log\_bin\_trust\_routine\_creators          |OFF                             |

| log\_error                               |/var/log/mysqld.log             |

|log\_output                              |FILE                            |

|log\_queries\_not\_using\_indexes           |OFF                             |

| log\_slave\_updates                       | OFF                             |

|log\_slow\_queries                        |OFF                             |

|log\_warnings                            |1                               |

|max\_binlog\_cache\_size                   | 18446744073709547520  |

|max\_binlog\_size                         |1073741824                      |

|max\_relay\_log\_size                      |0                               |

| relay\_log                               |                                 |

|relay\_log\_index                        |                                |

|relay\_log\_info\_file                     |relay-log.info                  |

|relay\_log\_purge                         |ON                              |

|relay\_log\_space\_limit                   |0                               |

|slow\_query\_log                          |OFF                             |

|slow\_query\_log\_file                     |/var/run/mysqld/mysqld-slow.log |

|sql\_log\_bin                             | ON                              |

|sql\_log\_off                               |OFF                                  |

|sql\_log\_update                          |ON                                  |

|sync\_binlog                               |0                                     |

\+-----------------------------------------+---------------------------------+

38 rows in set(0.00 sec)

mysql> SHOWMASTER LOGS;

\+-----------+--------------+

| Log\_name  | File\_size |

\+-----------+--------------+

| ON.000001|       106 |

\+-----------+--------------+

1 row in set(0.00 sec)

mysql>

\[root@www ~\]\#vim /etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

\# Disablingsymbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log\_bin=mysql-bin

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

"/etc/my.cnf"11L, 269C 已写入

\[root@www ~\]\#service mysqld restart

停止 mysqld： \[确定\]

正在启动 mysqld： \[确定\]

\[root@www ~\]\#mysql

Welcome to theMySQL monitor.  Commands end with ; or\\g.

Your MySQLconnection id is 2

Server version:5.1.73-log Source distribution

Oracle is aregistered trademark of Oracle Corporation and/or its

affiliates.Other names may be trademarks of their respective

owners.

Type 'help;' or'\\h' for help. Type '\\c' to clear the current input statement.

mysql> SHOWGLOBAL VARIABLES LIKE '%log%';

\+-----------------------------------------+---------------------------------+

|Variable\_name                           |Value                              |

\+-----------------------------------------+---------------------------------+

| back\_log                                | 50                                      |

|binlog\_cache\_size                       |32768                            |

|binlog\_direct\_non\_transactional\_updates | OFF                     |

|binlog\_format                           |STATEMENT                       |

|expire\_logs\_days                        |0                                    |

|general\_log                             |OFF                                   |

| general\_log\_file                        | /var/run/mysqld/mysqld.log  |