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()
{
[ !
-
d
'$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
-
n
1
$Datadir
/
mysql
-
bin
.index |sed
's@.\/@@'
`
mysql
-
u$User
-
p$Password
-
h $Host
-
e
"purge binary logs to '$binlog_rm'"
}
function full_backup_innodb()
{
[ !
-
d
'$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
-
n
1
$Datadir
/
mysql
-
bin
.index |sed
's@.\/@@'
`
mysql
-
u$User
-
p$Password
-
h $Host
-
e
"purge binary logs to '$binlog_rm'"
}
function incre_backup()
{
[ !
-
d
'$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
-
n
-
1
$Datadir
/
mysql
-
bin
.index |sed
's@.\/@@'
`
for
i
in
$binlog_cp
do
cp
-
p $Datadir
/
$i .
/
done
#Delete binary log
binlog_rm
=
`tail
-
n
1
$Datadir
/
mysql
-
bin
.index |sed
's@.\/@@'
`
mysql
-
u$User
-
p$Password
-
h $Host
-
e
"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，如需转载请自行联系原作者
