mysql[1] 逻辑卷备份mysqldump

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

今天就和大家聊聊mysqldump备份工具:


mysqldump

  • MySQL数据库自带的一款(逻辑)备份工具;

  • 能够对数据库、表、触发器、存储过程、事件等备份;

  • 备份的文件是可执行的sql语句纯文本文件中。


mysqldump engines

  • 对MyISAM存储引擎进行温备,必须加参数--lock-all-tables

  • 对InnoDB存储引擎进行热备,必须加参数--single-transaction


mysqldump usage

1、备份单个数据库和单个数据库下的某些表

    mysqldump [OPTIONS] database [tables]

2、备份多个数据库

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

3、备份所有数据库

    mysqldump [OPTIONS] --all-databases [OPTIONS]


mysqldump的可选参数很多,这里只说比较常用的参数:

   --master-data[=#]

1
2
3
4
5
6
该选项将binlog的位置和文件名追加到输出文件中;
该选项有三个可选值: 0 1 2
 
0 :不记录二进制日志文件及路位置
1 :以CHANGE MASTER TO的方式记录位置,可用于恢复后直接启动从服务器
2 :以CHANGE MASTER TO的方式记录位置,但默认被注释

   --flush-logs

1
在开始dump之前刷新二进制日志

   --single-transaction

1
2
3
4
适合InnoDB存储引擎(不适用MyISAM),此选项与 - - lock - tables选择是互斥的,因为lock tables会使任何挂起的事务隐含提交;要想提交大表的话,应结合使用 - - quick选项
 
如果指定库中的表类型均为InnoDB,可使用 - - single - transaction自动热备
注意: - - single - transaction不要和 - - lock - all - tables一起使用, - - single - transaction会中实加锁

    --lock-all-tables

1
提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭--single-transaction 和--lock-tables 选项。

    --events

1
导出事件调度器

    --triggers 

1
导出触发器

     --routines 

1
导出存储过程以及自定义函数

    --quick

1
不缓冲查询,直接导出到标准输出。默认为打开状态,使用--skip-quick取消该选项

    --no-create-db 

1
如果导出数据库的时候,使用 - - all - databases或 - - databases这两个选项,则表示只导出数据,而不添加CREATE DATABASE语句。

     --no-create-info

1
只导出数据,而不添加CREATE TABLE 语句。

     --no-data

1
不导出任何数据,只导出数据库表结构。



mysqldump的备份策略:

  • 完全备份 + 增量备份(相对于差异备份节约磁盘空间)

  • 完全备份 + 差异备份(相对于增量备份恢复时间短)


概念

  • 完全备份:备份全部数据库

  • 增量备份:仅备份上次完全备份或增量备份以后变化的数据

  • 差异备份:仅备份上次完全备份以来变化的数据


前面提过mysqldump对MyISAM存储引擎温备,对InnoDB存储引擎热备:

多种方式查看表的存储引擎:

方式一:

1
mysql> select TABLE_NAME,ENGINE  from  information_schema.tables where table_schema = '数据库' ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select TABLE_NAME,ENGINE  from  information_schema.tables where table_schema = 'mysql' ;
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - +
| TABLE_NAME                | ENGINE |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - +
| columns_priv              | MyISAM |
| db                        | MyISAM |
| event                     | MyISAM |
| func                      | MyISAM |
| general_log               | CSV    |
| help_category             | MyISAM |
| help_keyword              | MyISAM |
| help_relation             | MyISAM |
| help_topic                | MyISAM |
| host                      | MyISAM |
| ndb_binlog_index          | MyISAM |
| plugin                    | MyISAM |
| proc                      | MyISAM |
| procs_priv                | MyISAM |
| proxies_priv              | MyISAM |
| servers                   | MyISAM |
| slow_log                  | CSV    |
| tables_priv               | MyISAM |
| time_zone                 | MyISAM |
| time_zone_leap_second     | MyISAM |
| time_zone_name            | MyISAM |
| time_zone_transition      | MyISAM |
| time_zone_transition_type | MyISAM |
| user                      | MyISAM |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - +


方式二:

1
mysql> show table status from 数据库 where name= '表名' ;
1
2
3
4
5
6
7
mysql> show table status from mysql where name= 'user' ;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment                     |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| user | MyISAM |      10 | Dynamic    |    4 |             53 |         212 | 281474976710655 |         2048 |         0 |           NULL | 2015-03-05 21:34:03 | 2015-03-05 21:34:04 | NULL       | utf8_bin  |     NULL |                | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
1 row  in  set  (0.00 sec)


方式三:

1
mysql> show create table 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| user  | CREATE TABLE `user` (
   `Host` char( 60 ) COLLATE utf8_bin NOT NULL DEFAULT '',
   `User` char( 16 ) COLLATE utf8_bin NOT NULL DEFAULT '',
   `Password` char( 41 ) CHARACTER  SET  latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
   `Select_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Insert_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Update_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Delete_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Create_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Drop_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Reload_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Shutdown_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Process_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `File_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Grant_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `References_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Index_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Alter_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Show_db_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Super_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Create_tmp_table_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Lock_tables_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Execute_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Repl_slave_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Repl_client_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Create_view_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Show_view_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Create_routine_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Alter_routine_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Create_user_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Event_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Trigger_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `Create_tablespace_priv` enum( 'N' , 'Y' ) CHARACTER  SET  utf8 NOT NULL DEFAULT  'N' ,
   `ssl_type` enum(' ',' ANY ',' X509 ',' SPECIFIED ') CHARACTER SET utf8 NOT NULL DEFAULT ' ',
   `ssl_cipher` blob NOT NULL,
   `x509_issuer` blob NOT NULL,
   `x509_subject` blob NOT NULL,
   `max_questions`  int ( 11 ) unsigned NOT NULL DEFAULT  '0' ,
   `max_updates`  int ( 11 ) unsigned NOT NULL DEFAULT  '0' ,
   `max_connections`  int ( 11 ) unsigned NOT NULL DEFAULT  '0' ,
   `max_user_connections`  int ( 11 ) unsigned NOT NULL DEFAULT  '0' ,
   `plugin` char( 64 ) COLLATE utf8_bin DEFAULT '',
   `authentication_string` text COLLATE utf8_bin,
   PRIMARY KEY (`Host`,`User`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin COMMENT = 'Users and global privileges'  |

方式四:

1
# mysqlshow -uroot -p --status 数据库名字 表名(不加表名查看所有的)
1
2
3
4
5
6
7
8
[root@localhost ~] # mysqlshow -uroot -p --status mysql user;
Enter password: 
Database: mysql  Wildcard: user
+ - - - - - - + - - - - - - - - + - - - - - - - - - + - - - - - - - - - - - - + - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment                     |
+ - - - - - - + - - - - - - - - + - - - - - - - - - + - - - - - - - - - - - - + - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| user | MyISAM |  10       | Dynamic    |  4     53              212          281474976710655  2048          0          |                |  2015 - 03 - 05  21 : 34 : 03  2015 - 03 - 05  21 : 34 : 04  |            | utf8_bin  |          |                | Users  and  global  privileges |
+ - - - - - - + - - - - - - - - + - - - - - - - - - + - - - - - - - - - - - - + - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +


查看数据库所支持的存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     |  /dev/null  storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored  in  memory, useful  for  temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows  in  set  (0.00 sec)


修改现有表的存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> use mydb;
Database changed
mysql> show create table tutor;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| Table | Create Table                                                                                                                                                                              |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| tutor | CREATE TABLE `tutor` (
   `TID`  int ( 11 ) DEFAULT NULL,
   `Tname` char( 20 ) DEFAULT NULL,
   `Gender` char( 10 ) DEFAULT NULL,
   `Age`  int ( 11 ) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = utf8 |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
1  row  in  set  ( 0.00  sec)
 
mysql> alter table tutor engine = innodb;
Query OK,  9  rows affected ( 0.15  sec)
Records:  9   Duplicates:  0   Warnings:  0
 
mysql> show create table tutor;
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| Table | Create Table                                                                                                                                                                              |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| tutor | CREATE TABLE `tutor` (
   `TID`  int ( 11 ) DEFAULT NULL,
   `Tname` char( 20 ) DEFAULT NULL,
   `Gender` char( 10 ) DEFAULT NULL,
   `Age`  int ( 11 ) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8 |
+ - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
1  row  in  set  ( 0.00  sec)


基于二进制日志可以对MySQL数据库做完整性恢复

  • 查看是否开启二进制日志

1
2
3
4
5
6
7
8
9
mysql> show  global  variables like  '%log_bin%' ;
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - +
| Variable_name                   | Value |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - +
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - +
3  rows  in  set  ( 0.00  sec)
  • 刷新二进制日志、查看当前使用的二进制日志文件和所处的偏移位Position

1
2
3
4
5
6
7
8
9
10
mysql> flush logs;
Query OK,  0  rows affected ( 0.01  sec)
 
mysql> show master status;
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - +
File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - +
| mysql - bin . 000005  |       107  |              |                  |
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - +
1  row  in  set  ( 0.00  sec)
  • 向数据库插入数据、删除数据、建立授权用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> use mydb;
mysql> show tables;
+ - - - - - - - - - - - - - - - - +
| Tables_in_mydb |
+ - - - - - - - - - - - - - - - - +
| tutor          |
+ - - - - - - - - - - - - - - - - +
1  row  in  set  ( 0.00  sec)
 
mysql> insert into tutor values( 10 , 'huojianhua' , 'M' , 32 );
Query OK,  1  row affected ( 0.00  sec)
 
mysql> insert into tutor values( 11 , 'luozhixiang' , 'M' , 35 );
Query OK,  1  row affected ( 0.00  sec)
 
mysql> delete  from  tutor where TID = 10 ;
Query OK,  1  row affected ( 0.00  sec)
 
mysql> grant select on mydb.tutor to  'zhengyansheng' @ '192.168.10.15'  identified by  'password' ;
Query OK,  0  rows affected ( 0.00  sec)
  • 再次查看二进制日志文件所处的偏移位Position

1
2
3
4
5
6
7
mysql> show master status;     #Position发生变化
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - +
File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - +
| mysql - bin . 000005  |       313  |              |                  |    
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - +
1  row  in  set  ( 0.00  sec)
  • 查看二进制日志事件信息(不能看到命令的执行时间)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show binlog events  in  'mysql-bin.000005' ;
+ - - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                               |
+ - - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| mysql - bin . 000005  |    4  | Format_desc |          1  |          107  | Server ver:  5.5 . 35 - log, Binlog ver:  4                                                               |
| mysql - bin . 000005  107  | Query       |          1  |          175  | BEGIN                                                                                              |
| mysql - bin . 000005  175  | Query       |          1  |          286  | use `mydb`; insert into tutor values( 10 , 'huojianhua' , 'M' , 32 )                                       |
| mysql - bin . 000005  286  | Xid         |          1  |          313  | COMMIT  / *  xid = 60  * /                                                                                 |
| mysql - bin . 000005  313  | Query       |          1  |          381  | BEGIN                                                                                              |
| mysql - bin . 000005  381  | Query       |          1  |          493  | use `mydb`; insert into tutor values( 11 , 'luozhixiang' , 'M' , 35 )                                      |
| mysql - bin . 000005  493  | Xid         |          1  |          520  | COMMIT  / *  xid = 69  * /                                                                                 |
| mysql - bin . 000005  520  | Query       |          1  |          588  | BEGIN                                                                                              |
| mysql - bin . 000005  588  | Query       |          1  |          681  | use `mydb`; delete  from  tutor where TID = 10                                                          |
| mysql - bin . 000005  681  | Xid         |          1  |          708  | COMMIT  / *  xid = 71  * /                                                                                 |
| mysql - bin . 000005  708  | Query       |          1  |          873  | use `mydb`; grant select on mydb.tutor to  'zhengyansheng' @ '192.168.10.15'  identified by  'password'  |
+ - - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
11  rows  in  set  ( 0.00  sec)
  • 通过linux命令行查看二进制日志事件记录信息(可以看到执行命令的时间)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
[root@localhost ~] # mysqlbinlog --start-position=175 /mydata/data/mysql-bin.000005
/ * ! 50530  SET  @@SESSION.PSEUDO_SLAVE_MODE = 1 * / ;
/ * ! 40019  SET  @@session.max_insert_delayed_threads = 0 * / ;
/ * ! 50003  SET  @OLD_COMPLETION_TYPE = @@COMPLETION_TYPE,COMPLETION_TYPE = 0 * / ;
DELIMITER  / * ! * / ;
# at 4
#150306 16:51:46 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.35-log created 150306 16:51:46
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
omr5VA8BAAAAZwAAAGsAAAABAAQANS41LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA = =
' / * ! * / ;
# at 175
#150306 16:55:01 server id 1  end_log_pos 286   Query   thread_id=13    exec_time=0 error_code=0
use `mydb` / * ! * / ;
SET  TIMESTAMP = 1425632101 / * ! * / ;
SET  @@session.pseudo_thread_id = 13 / * ! * / ;
SET  @@session.foreign_key_checks = 1 , @@session.sql_auto_is_null = 0 , @@session.unique_checks = 1 , @@session.autocommit = 1 / * ! * / ;
SET  @@session.sql_mode = 0 / * ! * / ;
SET  @@session.auto_increment_increment = 1 , @@session.auto_increment_offset = 1 / * ! * / ;
/ * !\C utf8  * / / * ! * / ;
SET  @@session.character_set_client = 33 ,@@session.collation_connection = 33 ,@@session.collation_server = 33 / * ! * / ;
SET  @@session.lc_time_names = 0 / * ! * / ;
SET  @@session.collation_database = DEFAULT / * ! * / ;
insert into tutor values( 10 , 'huojianhua' , 'M' , 32 )
/ * ! * / ;
# at 286
#150306 16:55:01 server id 1  end_log_pos 313   Xid = 60
COMMIT / * ! * / ;
# at 313
#150306 17:05:23 server id 1  end_log_pos 381   Query   thread_id=15    exec_time=0 error_code=0
SET  TIMESTAMP = 1425632723 / * ! * / ;
BEGIN
/ * ! * / ;
# at 381
#150306 17:05:23 server id 1  end_log_pos 493   Query   thread_id=15    exec_time=0 error_code=0
SET  TIMESTAMP = 1425632723 / * ! * / ;
insert into tutor values( 11 , 'luozhixiang' , 'M' , 35 )
/ * ! * / ;
# at 493
#150306 17:05:23 server id 1  end_log_pos 520   Xid = 69
COMMIT / * ! * / ;
# at 520
#150306 17:06:03 server id 1  end_log_pos 588   Query   thread_id=15    exec_time=0 error_code=0
SET  TIMESTAMP = 1425632763 / * ! * / ;
BEGIN
/ * ! * / ;
# at 588
#150306 17:06:03 server id 1  end_log_pos 681   Query   thread_id=15    exec_time=0 error_code=0
SET  TIMESTAMP = 1425632763 / * ! * / ;
delete  from  tutor where TID = 10
/ * ! * / ;
# at 681
#150306 17:06:03 server id 1  end_log_pos 708   Xid = 71
COMMIT / * ! * / ;
# at 708
#150306 17:07:25 server id 1  end_log_pos 873   Query   thread_id=15    exec_time=0 error_code=0
SET  TIMESTAMP = 1425632845 / * ! * / ;
grant select on mydb.tutor to  'zhengyansheng' @ '192.168.10.15'  identified by  'password'
/ * ! * / ;
DELIMITER ;
# End of log file
ROLLBACK  / *  added by mysqlbinlog  * / ;
/ * ! 50003  SET  COMPLETION_TYPE = @OLD_COMPLETION_TYPE * / ;
/ * ! 50530  SET  @@SESSION.PSEUDO_SLAVE_MODE = 0 * / ;
  • mysqlbinlog导出备份文件

1
[root@localhost ~] # mysqlbinlog --start-position=175 /mydata/data/mysql-bin.000005 > 05.sql
  • 导入数据库

1
mysql> source  / root / 05.sql


  • 总述:开启二进制日志后,所有对数据库进行的操作(引起数据库改变或将要引起数据库改变的)都会被记录到二进制日志文件当中,所以二进制日志是在MySQL数据库做及时点恢复的重要日志文件,应该每天做备份,如果空间允许的情况下,不建议删除任何二进制日志文件,可以将其保存到备份目录中,或许以后会用到。


备份策略:

星期天进行全备+每天进行二进制日志增量备份

Bash脚本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
#!/bin/bash
#Author:Allentuns
#backup mysql database
#soft:mysqldump
#
 
User = 'root'
Datadir = "/mydata/data"
Week = `date  + % w`
Curtime = `date  + % F`
Backupdir = '/backup'
 
/ etc / profile
 
function full_backup_myisam()
{
     [ !  - '$Backupdir/$Curtime'  ] && mkdir  - p $Backupdir / $Curtime
     cd $Backupdir / $Curtime
 
     #Start backup full mysql(engines:MyISAM)
         mysqldump  - u$User  - p$Password  - h $Host  - - master - data = 2  - - flush - logs  - - lock - all - tables  - - events  - - all - databases |gzip > full_`date  + % Y - % m - % d - % H - % M - % S`.sql.gz
         
     #Delete binary log
     binlog_rm = `tail  - 1  $Datadir / mysql - bin .index |sed  's@.\/@@' `
     mysql  - u$User  - p$Password  - h $Host  - "purge binary logs to '$binlog_rm'"
}
 
 
function full_backup_innodb()
{
     [ !  - '$Backupdir/$Curtime'  ] && mkdir  - p $Backupdir / $Curtime
     cd $Backupdir / $Curtime
 
     #Start backup full mysql(engines:InnoDB)
     mysqldump  - u$User  - p$Password  - h $Host  - - master - data = 2  - - flush - logs  - - single - transaction  - - quick  - - events  - - all - databases |gzip > full_`date  + % Y - % m - % d - % H - % M - % S`.sql.gz
 
     #Delete binary log
     binlog_rm = `tail  - 1  $Datadir / mysql - bin .index |sed  's@.\/@@' `
     mysql  - u$User  - p$Password  - h $Host  - "purge binary logs to '$binlog_rm'"
}
 
function incre_backup()
{
     [ !  - '$Backupdir/$Curtime'  ] && mkdir  - p $Backupdir / $Curtime
     cd $Backupdir / $Curtime
 
     #Start cp binary log
     mysqladmin  - u$User  - p$Password  - h $Host flush - logs
     binlog_cp = `head  - - 1  $Datadir / mysql - bin .index |sed  's@.\/@@' `
     for  in  $binlog_cp
     do
         cp  - p $Datadir / $i . /
     done
 
     #Delete binary log
     binlog_rm = `tail  - 1  $Datadir / mysql - bin .index |sed  's@.\/@@' `
     mysql  - u$User  - p$Password  - h $Host  - "purge binary logs to '$binlog_rm'"
}
 
if  [[ $Week  - eq  0  ]];then
     full_backup_myisam
     #full_backup_innodb
else
     incre_backup
fi


mysqldump.sh分库备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#!/bin/bash
PATH= /bin : /sbin : /usr/bin : /usr/sbin : /usr/local/bin : /usr/local/sbin :~ /bin
export  PATH
 
User= 'root'
Password= '123456'
hostname =` hostname `
Backupdir= '/data/backup'
email= "13260071987@139.com"
 
inet=` /sbin/ip  a | grep  "BROADCAST"  | wc  -l`
if  [ $inet - eq  2 ]; then
     local_ip= "$(/sbin/ifconfig eth1|grep 'inet addr'|awk -F : '{print $2}'|cut -d ' ' -f1)"
else
     local_ip= "$(/sbin/ifconfig eth0|grep 'inet addr'|awk -F : '{print $2}'|cut -d ' ' -f1)"
fi
 
if  [ ! -d  "$Backupdir"  ]; then
     mkdir  -p $Backupdir
fi
 
dbs=`mysql -u$User -p$Password -e  'show databases;' | sed  1d | egrep  - v  "information_schema|test|mysql" `
for  db  in  $dbs
do
     mysqldump -u$User -p$Password --databases $db --lock-all-tables --events | gzip  > $Backupdir/${db}_` date  +%F`.sql.gz
done
 
if  [ $? - ne  0 ]; then  
     echo  "Server_name:$(hostname) Server_ip:$local_ip $(date +" %y-%m-%d %H:%M:%S ") mysql full backup Fail!" | /bin/mail  -s  "Database: [$dbs} Daily Full Backup Fail!"  $email  
     exit  1  
fi 
 
find  "$Backupdir"  -name  "*.sql.gz"  -ctime +7 - type  f - exec  rm  -rf {} \;


添加crond任务计划

1
2
[root@localhost script] # crontab -e
30 23 * * *  /usr/local/script/mysqldump_full_backup .sh





     本文转自zys467754239 51CTO博客,原文链接:http://blog.51cto.com/467754239/1617909,如需转载请自行联系原作者



相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
347
分享
相关文章
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
325 3
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
183 3
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
127 3
MySQL如何备份?
【10月更文挑战第2天】MySQL如何备份?
418 3
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
250 4
分享一篇mysql数据库备份脚本
分享一篇mysql数据库备份脚本
61 0
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。
云数据库:从零到一,构建高可用MySQL集群
在互联网时代,数据成为企业核心资产,传统单机数据库难以满足高并发、高可用需求。云数据库通过弹性扩展、分布式架构等优势解决了这些问题,但也面临数据安全和性能优化挑战。本文介绍了如何从零开始构建高可用MySQL集群,涵盖选择云服务提供商、创建实例、配置高可用架构、数据备份恢复及性能优化等内容,并通过电商平台案例展示了具体应用。
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等