You are here
马哥 35_03 _MySQL系列之十五——使用LVM快照进行数据库备份 有大用
二进制日志:
基于语句 statement
基于行 row
混合方式 mixed
mysqldump:
还原过程涉及到写操作,
一大堆 dml ddl
假如还原的sql语句有3G
二进制日志会增长,应该大于3G,因为里面涉及到许多额外信息,
还原过程没必要让二进制记录下来(假如记录进二进制,既占据磁盘空间,又产生大量的IO,导致还原速度变慢)
基于逻辑备份还原数据库的时候,应该关掉二进制日志,然后再还原,还原后,再启动二进制日志
马哥使用快照恢复mysql后增加PATH路径
mysql> show global variables like '%log%'; #显示跟日志相关的全局变量 (如果当前会话里使用 source 命令(任何命令吧),可以不使用 global 全局 )
+-----------------------------------------+-----------------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /mydata2/data/mail.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| 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_error | /mydata2/data/mail.magedu.com.err |
| 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_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /mydata2/data/mail-slow.log |
| sql_log_bin | ON | #是否记录二进制日志的,对当前会话来讲,改成off,就不会记入二进制日志了
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+-----------------------------------+
41 rows in set (0.00 sec)
mysql>
mysql> show master status; # 当前二进制日志
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 526507 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> create database mydb2;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show master status; #position发生改变,所以这己经记录入二进制日志了
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 526592 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> show binlog events in 'mysql-bin.000003'\G
*************************** 200. row ***************************
Log_name: mysql-bin.000003
Pos: 526297
Event_type: Query
Server_id: 1
End_log_pos: 526405
Info: use `studb`; insert into tutors (tname) values ('stu123')
*************************** 201. row ***************************
Log_name: mysql-bin.000003
Pos: 526405
Event_type: Xid
Server_id: 1
End_log_pos: 526432
Info: COMMIT /* xid=457 */
*************************** 202. row ***************************
Log_name: mysql-bin.000003
Pos: 526432
Event_type: Query
Server_id: 1
End_log_pos: 526507
Info: flush privileges
*************************** 203. row ***************************
Log_name: mysql-bin.000003
Pos: 526507
Event_type: Query
Server_id: 1
End_log_pos: 526592
Info: create database mydb2 #记录入日志了
203 rows in set (0.00 sec)
mysql>
mysql> set sql_log_bin=0; #关掉二进制日志 ( 0 或 off 都可以) 临时性禁用二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql> create database hellodb2;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql>
mysql> show binlog events in 'mysql-bin.000003'\G # 最后没看到 create database hellodb2 说明二进制日志不记录了
*************************** 201. row ***************************
Log_name: mysql-bin.000003
Pos: 526405
Event_type: Xid
Server_id: 1
End_log_pos: 526432
Info: COMMIT /* xid=457 */
*************************** 202. row ***************************
Log_name: mysql-bin.000003
Pos: 526432
Event_type: Query
Server_id: 1
End_log_pos: 526507
Info: flush privileges
*************************** 203. row ***************************
Log_name: mysql-bin.000003
Pos: 526507
Event_type: Query
Server_id: 1
End_log_pos: 526592
Info: create database mydb2
203 rows in set (0.01 sec)
mysql>
mysql> set sql_log_bin=1; #开启二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop database hellodb3;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show binlog events in 'mysql-bin.000003'\G #看最后,删除数据库记录进来了
*************************** 202. row ***************************
Log_name: mysql-bin.000003
Pos: 526432
Event_type: Query
Server_id: 1
End_log_pos: 526507
Info: flush privileges
*************************** 203. row ***************************
Log_name: mysql-bin.000003
Pos: 526507
Event_type: Query
Server_id: 1
End_log_pos: 526592
Info: create database mydb2
*************************** 204. row ***************************
Log_name: mysql-bin.000003
Pos: 526592
Event_type: Query
Server_id: 1
End_log_pos: 526681
Info: drop database hellodb3
204 rows in set (0.00 sec)
mysql>
马哥 下图的操作是得到 jiaowu.sql 这个备份文件
[root@mail ~]# ls -lt *.sql
-rw-r--r-- 1 root root 1603 10-20 16:58 temp.sql
-rw-r--r-- 1 root root 1527 10-20 16:17 mon-incremental.sql
-rw-r--r-- 1 root root 526607 10-20 16:00 alldatabases.sql
-rw-r--r-- 1 root root 526607 10-20 15:36 all.sql
-rw-r--r-- 1 root root 6170 10-20 14:48 studb-2020-10-20-14-48-1603176502.sql
-rw-r--r-- 1 root root 6022 10-20 14:46 studb-2020-10-20-14-46-1603176415.sql
-rw-r--r-- 1 root root 0 10-20 14:46 studb-2020-10-20-14-46-1603176408.sql
-rw-r--r-- 1 root root 5997 10-20 14:14 studb.sql
-rw-r--r-- 1 root root 5998 10-20 13:27 jiaowu.sql #我备份的jiaowu.sql语句在这里
-rw-r--r-- 1 root root 1642 10-10 17:17 a.sql
-rw-r--r-- 1 root root 71 09-15 16:49 test.sql
[root@mail ~]#
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql> set sql_log_bin=0; #当前会话禁用二进制日志,所以不能使用 "# mysql < 文件名.sql" 命令来导入(因为全局未禁用二进制日志,mysql命令算全局吗? 不管算不算全局,反正肯定不能算当前会话) 使用 " > source 文件名.sql " 来导入
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create database jiaowu;
mysql> use jiaowu
mysql> source /root/jiaowu.sql (或者 \. /root/jiaowu.sql )
mysql> show tables; # 看到所有表了
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses |
| sct |
| students |
| tutors |
+------------------+
4 rows in set (0.00 sec)
mysql>
mysql> select * from tutors; #表中也有数据
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 70 |
| 13 | stu111 | F | 60 |
| 14 | stu22 | F | 50 |
| 15 | stu100 | M | 30 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql>
mysql> set sql_log_bin=1; #再打开二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql>
mysqldump:
--databases DB1,DB2...... 备份指定数据库
--all-databases 备份所有数据库
DB_NAME [tb1_name tb2_name] 备份指定数据库(指定表)
--lock-all-tables 对于MyISAM只能执行温备份
--lock-tables 只备份一张表
--single-transaction 对于InnoDB,可以执行热备份
--flush-logs 备份前日志滚动
--events 备份事件
--routines 备份例程(存储过程和存储函数)
--triggers 备份触发器
--master-data={0|1|2} 只要不是用来做从服务器时,一般为2,,,, 2表示记录备份的日志及其位置
完全备份后做即时点还原,用到其二进制日志及其位置之后的操作
逻辑备份:
1,会使浮点数据丢失精度
2,mysqldump导出的文本文件可能占据比原有数据更大的存储空间,,,,,所有类型的数据都保存为文件信息了,(比如数值3200保存为文本字符时,占据的空间会更大);;;;; 文本文件压缩比很大,所以压缩后会很节约空间
3,逻辑备份的速度通常很慢,相当消耗资源,不适合对大数据库做完全备份 (小型数据库非常普遍使用)
4,mysqldump 对MyISAM只能做温备份,所以备份之前要锁定表
mysqldump 对 InnoDB引擎做备份时,建议使用热备
mysql> flush tables with read lock; 如果有许多其它事务正在进行的话,加锁过程需要等待很长时间,(生产过程中,可能施加两个小时都施加不上锁;;;因为写锁优先级有时比读锁要高,如果别人启动一个大事务,半天都锁不上,)
就算锁上了,并不意味着,锁上后,背后就没有写操作了;;;;因为对于InnoDB来讲,有事务日志,这些事务日志中的数据,是在后台每隔一段时间,由mysql的后台进程(后台线程)自我调度的将后台事务中的日志同步到数据文件中去;;所以得等待InnoDB的存储引擎能够将事务日志同步到数据文件中去( > show engine innodb status 查看事务日志是否已同步到数据文件);;;;;;只不过前端用户无法写数据了
mysql> show engine innodb status; #如果在某个IO上,仍然在执行写出操作的话,就需要等待了 ;;写buf仍然有大小,也需要等待
我们要观察这边发现缓冲当中没有数据以后,才能开始执行mysqldump写操作的
所以建议对 InnoDB 做热备(使用 --single-transaction 此时,它会自动启动一个事务,事务式存储引擎有个MVCC(多版本并发控制)的功能,而且事务级别是 repeatable-read的时候,--single-transaction会启动一个较长的大事务,直到我们完成备份;;;;;;;当我们启动repeatable-read的事务级别的时候,意味着自己在整个读过程当中,所有的数据都是一致的)
.................................
.................................
.................................
select into outfile 命令也可以实现逻辑备份的,备份出来的数据都是纯文本信息,不是sql语句,没有额外的空间开销,没有create table,insert into 这样的语句,纯粹把一个表备份成你所看到的select语句的样子,
select into outfile;;;;;备份单张表的时候,很有用
速度比mysqldump要快,只能一张表一张表的备份
比mysqldump更节约空间,还原的速度更快;通常拿它做某单张表的备份
备份:
select * into outfile '/path/to/somefile.txt' from tb_name [ where clause ]
还原:
load data infile '/path/to/somefile' into table tb_name; ( tb_name表 得事先自己创建) (这个并没有写进二进制日志?任何数据的改变都会写进二进制日志的,,,只是它没有ddl,dml语句写入二进制日志罢了,它是基于row写进二进制日志的)
mysql> use jiaowu;
Database changed
mysql> select * from tutors;
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 70 |
| 13 | stu111 | F | 60 |
| 14 | stu22 | F | 50 |
| 15 | stu100 | M | 30 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql> select * into outfile '/tmp/tutor.txt' from tutors; #备份表的数据 #mysql用户需要对 /tmp/ 有写权限才行
(可以加 where 子句)
Query OK, 15 rows affected (0.00 sec)
mysql>
[root@mail ~]# cd /tmp
[root@mail tmp]# ls
mapping-root scim-panel-socket:0-root test.xls tutor.txt
[root@mail tmp]#
[root@mail tmp]# cat tutor.txt #纯粹就是表中的数据,各字段之间使用tab制表符分隔
# 不能使用 mysql > source tutor.txt 只能使用 load data infile
1 HongQigong M 93
2 HuangYaoshi M 63
3 Miejueshitai F 72
4 OuYangfend M 76
5 YiDeng M 90
6 YuCanghai M 56
7 Jinlunfawang M 67
8 HuYidao M 42
9 NingZhongze F 49
10 tom F 30
11 DingDian M 25
12 HuFei M 70
13 stu111 F 60
14 stu22 F 50
15 stu100 M 30
[root@mail tmp]#
mysql> create table tutor like tutors; # 仿照某表创建一个空表
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> desc tutor;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(50) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
mysql> desc tutors; # tutor 和 tutors 格式是相同的
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(50) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
mysql> drop table tutors; # 删掉原表
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select * from tutor; # 表中数据是空的
Empty set (0.00 sec)
mysql>
mysql> load data infile '/tmp/tutor.txt' into table tutor; # 从/tmp/tutor.txt文件中加载数据into到tutor表中 ( 从文件中导入数据到表 )
Query OK, 15 rows affected (0.02 sec)
Records: 15 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql>
mysql> select * from tutor; # 数据有了
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 70 |
| 13 | stu111 | F | 60 |
| 14 | stu22 | F | 50 |
| 15 | stu100 | M | 30 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003'\G #看二进制日志 load data infile 还原时,并没有写到二进制日志,因为 我们执行的并不是DML语句 ( 但是二进制日志文件中是有信息的,当然不是基于语句的,是基于行的 )
#通过 show binlog events 好像看不清楚
*************************** 208. row ***************************
Log_name: mysql-bin.000003
Pos: 526956
Event_type: Table_map
Server_id: 1
End_log_pos: 527009
Info: table_id: 94 (jiaowu.tutor)
*************************** 209. row ***************************
Log_name: mysql-bin.000003
Pos: 527009
Event_type: Write_rows
Server_id: 1
End_log_pos: 527324
Info: table_id: 94 flags: STMT_END_F
*************************** 210. row ***************************
Log_name: mysql-bin.000003
Pos: 527324
Event_type: Xid
Server_id: 1
End_log_pos: 527351
Info: COMMIT /* xid=798 */
210 rows in set (0.01 sec)
mysql>
[root@mail ~]# mysqlbinlog /mydata2/data/mysql-bin.000003 # 通过 mysqlbinlog来看看吧
............................
# at 526681
#201021 15:13:41 server id 1 end_log_pos 526776 Query thread_id=18 exec_time=0 error_code=0
use jiaowu/*!*/;
SET TIMESTAMP=1603264421/*!*/;
create table tutor like tutors
/*!*/;
# at 526776
#201021 15:15:14 server id 1 end_log_pos 526886 Query thread_id=18 exec_time=0 error_code=0
SET TIMESTAMP=1603264514/*!*/;
DROP TABLE `tutors` /* generated by server */
/*!*/;
# at 526886
#201021 15:17:13 server id 1 end_log_pos 526956 Query thread_id=18 exec_time=0 error_code=0
SET TIMESTAMP=1603264633/*!*/;
BEGIN
/*!*/;
# at 526956
# at 527009 #我们把这个之后的内容(即从 527009 到 527324(也就是最后吧) )重新在数据库上执行一遍,看看结果
#201021 15:17:13 server id 1 end_log_pos 527009 Table_map: `jiaowu`.`tutor` mapped to number 94
#201021 15:17:13 server id 1 end_log_pos 527324 Write_rows: table id 94 flags: STMT_END_F
#这些是基于行的复制,,因为 load data infile 既不是dml,又不是ddl,但是 load data infile 引起了数据的变化(写到数据库里面去了),,所以记录下来了 ,,,,,,,,,, 这些是二进制格式的数据,它记录的是内容,是基于行的复制,而不是基于语句
BINLOG '
eeCPXxMBAAAANQAAAKEKCAAAAF4AAAAAAAEABmppYW93dQAFdHV0b3IABAMP/gMEMgD3AQ4=
eeCPXxcBAAAAOwEAANwLCAAAAF4AAAAAAAEABP/wAQAAAApIb25nUWlnb25nAV0AAADwAgAAAAtI
dWFuZ1lhb3NoaQE/AAAA8AMAAAAMTWllanVlc2hpdGFpAkgAAADwBAAAAApPdVlhbmdmZW5kAUwA
AADwBQAAAAZZaURlbmcBWgAAAPAGAAAACVl1Q2FuZ2hhaQE4AAAA8AcAAAAMSmlubHVuZmF3YW5n
AUMAAADwCAAAAAdIdVlpZGFvASoAAADwCQAAAAtOaW5nWmhvbmd6ZQIxAAAA8AoAAAADdG9tAh4A
AADwCwAAAAhEaW5nRGlhbgEZAAAA8AwAAAAFSHVGZWkBRgAAAPANAAAABnN0dTExMQI8AAAA8A4A
AAAFc3R1MjICMgAAAPAPAAAABnN0dTEwMAEeAAAA
'/*!*/;
# at 527324
#201021 15:17:13 server id 1 end_log_pos 527351 Xid = 798
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
马哥的目的是验证下 这些基于行的复制 (load data infile )转化成 sql ,,能不能运行出结果
[root@mail ~]# mysqlbinlog --start-position=527009 /mydata2/data/mysql-bin.000003 > /root/a.sql # 把日志文件中527009 之后的内容保存至 /root/a.sql
[root@mail ~]#
mysql> use jiaowu;
Database changed
mysql> create table tutors like tutor; #依tutor创建一张空表tutors
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> truncate table tutor; # 清空tutor表
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutor; #无数据了
Empty set (0.00 sec)
mysql> select * from tutors; #也无数据了
Empty set (0.00 sec)
mysql>
mysql> set sql_log_bin=0; #关闭二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> source /root/a.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutors; #没有数据,为什么?
Empty set (0.00 sec)
mysql> select * from tutor; #没有数据,为什么?
Empty set (0.00 sec)
mysql>
[root@mail data]# mysqlbinlog 'mysql-bin.000003' > /root/m-tmp.txt
[root@mail data]# vim /root/m-tmp.txt # 二进制日志记录里面的操作有问题
1 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #201020 16:47:37 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 201020 16:47:37 at startup
#这里是警告?好像警告离我们执行的东西太远(正在使用当中,是不是要执行下 flush logs 看看?)
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 BINLOG '
9 KaSOXw8BAAAAZwAAAGsAAAABAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A
10 AAAAAAAAAAAAAAAAAAAppI5fEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
11 '/*!*/;
[root@mail data]# tail /mydata2/data/mail.magedu.com.err #没看到错
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
201020 16:47:36 InnoDB: Waiting for the background threads to start
201020 16:47:37 InnoDB: 1.1.8 started; log sequence number 0
201020 16:47:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
201020 16:47:37 [Note] - '0.0.0.0' resolves to '0.0.0.0';
201020 16:47:37 [Note] Server socket created on IP: '0.0.0.0'.
201020 16:47:37 [Note] Event Scheduler: Loaded 0 events
201020 16:47:37 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.28-log' socket: '/usr/local/mysql/mysql.sock' port: 3306 Source distribution
[root@mail data]#
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
[root@mail data]# mysqlbinlog --start-position=527009 /mydata2/data/mysql-bin.000003 > /root/a.sql #重新执行一次日志
[root@mail data]#
[root@mail data]# vim /root/a.sql #好像没有看到警告了
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201020 16:47:37 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 201020 16:47:37 at startup
ROLLBACK/*!*/;
BINLOG '
KaSOXw8BAAAAZwAAAGsAAAAAAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAppI5fEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 527009
#201021 15:17:13 server id 1 end_log_pos 527324 Write_rows: table id 94 flags: STMT_END_F
BINLOG '
eeCPXxcBAAAAOwEAANwLCAAAAF4AAAAAAAEABP/wAQAAAApIb25nUWlnb25nAV0AAADwAgAAAAtI
dWFuZ1lhb3NoaQE/AAAA8AMAAAAMTWllanVlc2hpdGFpAkgAAADwBAAAAApPdVlhbmdmZW5kAUwA
AADwBQAAAAZZaURlbmcBWgAAAPAGAAAACVl1Q2FuZ2hhaQE4AAAA8AcAAAAMSmlubHVuZmF3YW5n
AUMAAADwCAAAAAdIdVlpZGFvASoAAADwCQAAAAtOaW5nWmhvbmd6ZQIxAAAA8AoAAAADdG9tAh4A
AADwCwAAAAhEaW5nRGlhbgEZAAAA8AwAAAAFSHVGZWkBRgAAAPANAAAABnN0dTExMQI8AAAA8A4A
AAAFc3R1MjICMgAAAPAPAAAABnN0dTEwMAEeAAAA
'/*!*/;
"~/a.sql" 49L, 2139C
mysql> source /root/a.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1050 (42S01): Table 'tutors' already exists
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from tutor; #还是没有数据
Empty set (0.00 sec)
mysql> select * from tutors; #还是没有数据
Empty set (0.00 sec)
mysql>
马哥 (load data infile 的二进制日志产生的sql文件,运行后没有产生出日志 )暂时放在这儿,以后再说了
几乎热备:LVM (逻辑卷管理器) 1)锁表 2)创建快照卷 3)解锁
snapshot:
前提:
1,数据文件在逻辑卷上,
2,此逻辑卷所在的卷组必须要有足够的空间来创建快照卷 (超出快照卷大小,快照卷会崩溃的)
3,事务日志(当然要是 InnoDB 的数据库引擎)和数据文件要在同一个逻辑卷上,,无论如何二进制日志应该分开存储到其它存储设备上.........本来建议二进制与数据文件分开存放,甚至事务日志与数据文件也要分开存放,但是如果用快照对数据库进行备份,事务日志一定得与数据文件在同一个卷上(因为在快照的情况下,如果事务日志与数据文件不在同一个卷 上,那得对两个卷各自做快照;;两个卷的快照的时间点 未必是精确到一模一样的;;;事务日志的主要目的是满足ACID功能的,如果事务日志与数据文件的时间点不一致的话,它是不能拿来做数据恢复的,(不一致放,有一个事务日志那个时间点的快照己经提交了,但是数据文件点的那个时间点的快照未提交,,,这种不一致性的东西,是不是能拿来执行恢复,回滚之类的操作的))
步骤:
1,打开会话,施加读锁,锁定所有表
mysql > flush tables with read lock;
mysql > flush logs;
2,通过另一个终端,保存二进制日志及其位置的相关信息 (要在shell提示符下执行)
$ mysql -uroot -p -e 'show master status\G' > /path/to/master-年-月-日-时-分秒.info
3,创建快照卷(要在shell提示符下执行)
# lvcreate -L # -s -p r -n snap_lv_name /path/to/source_lv
4,释放锁
mysql> unlock tables;
5,挂载快照卷,备份
# mount
# cp
6,删除快照卷
7,增量备份二进制日志
这样子我们使用逻辑卷的方式来通过几乎热备(基于物理备份的方式实现了mysql数据库的备份)
备份策略:
逻辑卷(数据文件)(完全备份)+二进制日志(增量备份)
执行 flush table with read lock后,外面的数据写不进来,但是事务日志与数据文件仍可能在同步
如果事务日志与数据文件在不同的卷上,两个卷分别进行快照,两个快照的时间点不可能一模一样的,下次(服务器重新启动后会恢复或回滚事务)事务恢复或回滚时就会数据出错,
所以事务日志与数据文件必须在同一个卷上,所以此时它们的快照的时间点是一致的
事务日志正在写到数据文件,此时,进行快照是可用的(就相当于断电,mysql崩溃吧),只是以后mysql服务器启动时 把事务 还原(恢复,回滚,撤销)回来就可以
mysql> use jiaowu
Database changed
mysql>
mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses |
| sct |
| students |
| tutor |
| tutors |
+------------------+
5 rows in set (0.00 sec)
mysql>
mysql> drop table tutor;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select * from tutors;
Empty set (0.00 sec)
mysql> load data infile '/tmp/tutor.txt' into table tutors;
Query OK, 15 rows affected (0.00 sec)
Records: 15 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
第一个putty
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql>
模拟一下,正在执行一个事务的时候,能不能施加锁
mysql> use jiaowu;
Database changed
mysql>
mysql> desc tutors;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(50) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
mysql> insert into tutors (tname) values ('stu001');
Query OK, 1 row affected (0.04 sec)
mysql>
开启第二个putty窗口
mysql> use jiaowu
Database changed
mysql> select @@tx_isolation; #默认是可重读的
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors; #不能看到 第一个putty的插入数据
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 70 |
| 13 | stu111 | F | 60 |
| 14 | stu22 | F | 50 |
| 15 | stu100 | M | 30 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush tables with read lock; # 可以加锁
Query OK, 0 rows affected (0.05 sec)
mysql> flush logs; # 做日志滚动
Query OK, 0 rows affected (0.01 sec)
mysql>
第一个putty 窗口
mysql> insert into tutors (tname) values ('stu002'); #因为第二个 putty 施加了读锁,所以第一个putty插数据就插不进去了
第二个putty 窗口
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
第三个putty窗口
[root@mail ~]# mysql -e 'show master status\G' > /backup/maseter-`date +%F`.info # 在第二个putty窗口 flush tables with read lock; 千万不要退出
[root@mail ~]#
[root@mail ~]# ls /backup/maseter-2020-10-22.info
/backup/maseter-2020-10-22.info
[root@mail ~]#
[root@mail ~]# lvcreate -L 50M -s -p r -n mydata2-snap /dev/myvg/mydata2 #以/dev/myvg/mydata2 逻辑卷(mysql数据文件放在这个逻辑卷上)创建快照卷mydata2-snap -L 表示大小 -s 表示快照卷 -p( 就是 perm 吧 ) r 表示权限为只读 -n 后面指定名称
Rounding up size to full physical extent 52.00 MB
Logical volume "mydata2-snap" created
[root@mail ~]#
[root@mail ~]# mount
/dev/mapper/VolGroup00-LogVol00 on / type ext3 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/sda1 on /boot type ext3 (rw)
tmpfs on /dev/shm type tmpfs (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
nfsd on /proc/fs/nfsd type nfsd (rw)
/dev/mapper/myvg-mydata2 on /mydata2 type ext3 (rw) #/dev/mapper/myvg-mydata2挂载在 mydata2 目录(mysql数据库所在的目录)
[root@mail ~]#
第二个putty 窗口
mysql> unlock tables; #快照卷创建好之后,就可以释放锁了
Query OK, 0 rows affected (0.00 sec)
mysql>
第一个putty 窗口
mysql> insert into tutors (tname) values ('stu002'); #第二个窗口一旦释放锁,这里就插入成功了
Query OK, 1 row affected (38 min 42.19 sec)
mysql> commit; #一提交的话,二进制日志事件会立即写入的
Query OK, 0 rows affected (0.01 sec)
mysql>
第二个putty 窗口
mysql> show master status; # position 变化了,说明第一个窗口的insert into 写入二进制日志了
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 478 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
第三个putty窗口
[root@mail ~]# mount /dev/myvg/mydata2-snap /mnt -o ro #只读挂载快照卷
[root@mail ~]#
[root@mail ~]# cd /mnt
[root@mail mnt]# ls
data lost+found
[root@mail mnt]#
[root@mail mnt]# cd data
[root@mail data]# pwd
/mnt/data
[root@mail data]# ls # 看到了所有备份的内容
#里面的二进制日志不需要动,因为这里的二进制日志内容是没有 生成 快照卷后新生的数据的,
#这里的二进制日志是没有用的 把除二进制日志之外的所有的内容备份一下 就是备份整个数据库
#如果想备份 jiaowu 数据库,jiaowu 数据库是 InnoDB存储引擎,(若所有表共用表空间的话,就是ibdata1为 所有innodb的数据) 同时,我们要备份事务日志( ib_logfile0 ib_logfile1 )
cactidb ib_logfile1 mydb mysql-bin.000003 studb
edb ibdata1 mydb2 mysql-bin.000004 students
hellodb jiaowu mysql mysql-bin.000005 test
hellodb2 mail.magedu.com.err mysql-bin.000001 mysql-bin.index test2
ib_logfile0 mail.magedu.com.pid mysql-bin.000002 performance_schema testdb
[root@mail data]#
[root@mail data]# ls jiaowu/ #我这里使用了每表一个表空间 (数据在数据库目录下面) (此时可以单独备份)
courses.frm courses.ibd db.opt sct.frm students.frm students.ibd tutors.frm tutors.ibd
马哥未使用每表一个表空间, jiaowu 数据库(jiaowu 数据库是 InnoDB存储引擎 )里面的数据 在 ibdata1里面 (所以要尽量使用 每表一个表空间
即 innodb_file_per_table )
[root@mail data]# pwd
/mnt/data
[root@mail data]# mkdir /backup/full-backup-`date +%F`;
[root@mail data]# cp -a ./* /backup/full-backup-`date +%F`
[root@mail data]#
[root@mail data]# cd
[root@mail ~]# umount /mnt #卸载快照卷
[root@mail ~]#
[root@mail ~]# lvremove --force /dev/myvg/mydata2-snap #删除快照卷
Logical volume "mydata2-snap" successfully removed
[root@mail ~]#
[root@mail ~]# cd /backup/full-backup-2020-10-22/
[root@mail full-backup-2020-10-22]# ls
cactidb ib_logfile1 mydb mysql-bin.000003 studb
edb ibdata1 mydb2 mysql-bin.000004 students
hellodb jiaowu mysql mysql-bin.000005 test
hellodb2 mail.magedu.com.err mysql-bin.000001 mysql-bin.index test2
ib_logfile0 mail.magedu.com.pid mysql-bin.000002 performance_schema testdb
[root@mail full-backup-2020-10-22]#
[root@mail full-backup-2020-10-22]# rm -f mysql-bin.* #删除所有的(也就是没有用的)二进制日志文件
[root@mail full-backup-2020-10-22]#
[root@mail full-backup-2020-10-22]# ls
cactidb ib_logfile0 mail.magedu.com.err mysql test
edb ib_logfile1 mail.magedu.com.pid performance_schema test2
hellodb ibdata1 mydb studb testdb
hellodb2 jiaowu mydb2 students
[root@mail full-backup-2020-10-22]#
[root@mail full-backup-2020-10-22]# cd /mydata2/data
[root@mail data]# ls
cactidb ib_logfile1 mydb mysql-bin.000003 studb
edb ibdata1 mydb2 mysql-bin.000004 students
hellodb jiaowu mysql mysql-bin.000005 test
hellodb2 mail.magedu.com.err mysql-bin.000001 mysql-bin.index test2
ib_logfile0 mail.magedu.com.pid mysql-bin.000002 performance_schema testdb
[root@mail data]# cat /backup/maseter-2020-10-22.info #只需要快照点那一刻之后的二进制日志及其位置
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 107 #(因为 107之前是头部,所以指定--start-position=107 与不指定是没有区别的)
Binlog_Do_DB:
Binlog_Ignore_DB:
[root@mail data]#
假如 mysql-bin.000005 之后 还有二进制日志文件怎么办?
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> use jiaowu
Database changed
mysql> insert into tutors (tname) values('stu0003');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tutors (tname) values('stu0004');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show master status; #看到了日志滚动
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 575 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
难道需要把这两个文件都是 导出为 sql 文件?
[root@mail data]# mysqlbinlog --start-position=107 mysql-bin.000005 > 11.sql #未执行
[root@mail data]# mysqlbinlog mysql-bin.000006 > 12.sql #未执行
[root@mail data]# mysqlbinlog --start-position=107 mysql-bin.000005 #记下时间点
[root@mail data]# pwd
/mydata2/data
[root@mail data]# mysqlbinlog --start-datetime='2020-10-22 13:30:54' mysql-bin.000005 mysql-bin.000006 #指定开始时间点 --start-datetime 它可以导出多个文件,就可以把它们保存在一块了
下图只截了最后的部分
[root@mail data]# pwd
/mydata2/data
# 下面的语句 以 --start-datetime 选项把多个二进制日制文件导出为一个 sql 了
[root@mail data]# mysqlbinlog --start-datetime='2020-10-22 13:30:54' mysql-bin.000005 mysql-bin.000006 > /backup/increment-`date +%F-%H-%M-%S`.sql
[root@mail data]#
模拟数据损坏
[root@mail data]# service mysqld stop
Shutting down MySQL... [ OK ]
[root@mail data]#
[root@mail data]# pwd
/mydata2/data
[root@mail data]#
[root@mail data]# ls
cactidb ib_logfile1 mydb2 mysql-bin.000004 studb
edb ibdata1 mysql mysql-bin.000005 students
hellodb jiaowu mysql-bin.000001 mysql-bin.000006 test
hellodb2 mail.magedu.com.err mysql-bin.000002 mysql-bin.index test2
ib_logfile0 mydb mysql-bin.000003 performance_schema testdb
[root@mail data]#
[root@mail data]# rm -rf ./* # 删掉数据库所有数据
[root@mail data]#
# cp -a 能够复制属主属组
[root@mail ~]# cp -a /backup/full-backup-2020-10-22/* /mydata2/data/ #不需要初始化数据库,,,因为/backup/full-backup-2020-10-22/* 里面包含了mysql数据库
[root@mail ~]#
[root@mail ~]# ls /mydata2/data/
cactidb ib_logfile0 mail.magedu.com.err mysql test
edb ib_logfile1 mail.magedu.com.pid performance_schema test2
hellodb ibdata1 mydb studb testdb
hellodb2 jiaowu mydb2 students
[root@mail ~]#
[root@mail ~]# ls /mydata2/data/ -l #确保属主属组 (属主肯定要是mysql)
total 28825
drwx------ 2 mysql mysql 1024 Oct 20 16:50 cactidb
drwx------ 2 mysql mysql 1024 Oct 20 16:50 edb
drwx------ 2 mysql mysql 1024 Oct 20 16:50 hellodb
drwx------ 2 mysql mysql 1024 Oct 21 13:28 hellodb2
-rw-rw---- 1 mysql mysql 5242880 Oct 22 12:35 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Oct 20 16:47 ib_logfile1
-rw-rw---- 1 mysql mysql 18874368 Oct 22 12:35 ibdata1
drwx------ 2 mysql mysql 1024 Oct 22 12:29 jiaowu
-rw-rw---- 1 mysql root 1886 Oct 20 16:47 mail.magedu.com.err
-rw-rw---- 1 mysql mysql 6 Oct 20 16:47 mail.magedu.com.pid
drwx------ 2 mysql mysql 1024 Oct 20 16:50 mydb
drwx------ 2 mysql mysql 1024 Oct 21 13:24 mydb2
drwx------ 2 mysql root 3072 Oct 20 16:50 mysql
drwx------ 2 mysql mysql 1024 Oct 20 16:43 performance_schema
drwx------ 2 mysql mysql 1024 Oct 20 16:50 studb
drwx------ 2 mysql mysql 1024 Oct 20 16:50 students
drwx------ 2 mysql root 1024 Oct 20 16:43 test
drwx------ 2 mysql mysql 1024 Oct 20 16:50 test2
drwx------ 2 mysql mysql 1024 Oct 20 16:50 testdb
[root@mail ~]#
[root@mail ~]# service mysqld start # 启动了
Starting MySQL [ OK ]
[root@mail ~]#
[root@mail ~]# ls /mydata2/data/ #它会自动创建新的二进制日志 mysql-bin.000001
cactidb ib_logfile0 mail.magedu.com.err mysql studb testdb
edb ib_logfile1 mail.magedu.com.pid mysql-bin.000001 students
hellodb ibdata1 mydb mysql-bin.index test
hellodb2 jiaowu mydb2 performance_schema test2
[root@mail ~]#
mysql> use jiaowu;
Database changed
mysql> select * from tutors; #stu001 stu002 stu003 stu004 都没有
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 70 |
| 13 | stu111 | F | 60 |
| 14 | stu22 | F | 50 |
| 15 | stu100 | M | 30 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
mysql>
#关掉二进制日志,目的是为了即时点还原 执行sql 不记入二进制日志
mysql> set sql_log_bin=0;
mysql> source /backup/increment-2020-10-22-15-06-55.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
.....................................
.....................................
mysql> select * from tutors; #可以看到 #stu001 stu002 stu003 stu004
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 70 |
| 13 | stu111 | F | 60 |
| 14 | stu22 | F | 50 |
| 15 | stu100 | M | 30 |
| 16 | stu001 | NULL | NULL |
| 17 | stu002 | NULL | NULL |
| 18 | stu0003 | NULL | NULL |
| 19 | stu0004 | NULL | NULL |
+-----+--------------+--------+------+
19 rows in set (0.00 sec)
mysql>
mysql> set sql_log_bin=1; #开启二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show master status; #二进制日志还在 107的位置(说明刚才source 对二进制日志文件没有影响)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
我们执行做快照卷之前,
需要 flush tables with read lock;
再执行下 flush logs;
再 show master status; ( 保存二进制日志文件及其位置 通过 第三个putty窗口 mysql -e 'show master status\G' > /backup/maseter-`date +%F`.info 保存一下 ) (这样将来即时点还原时,知道,从哪一刻开始还原)