通过mysqlbinlog --skip-gtids恢复后再备份可能造成的坑

通过mysqlbinlog –skip-gtids恢复后再备份可能造成的坑

版本

1
2
3
4
5
6
7
8
[root@uz22199 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@uz22199 backup]# mysql -e"select @@version"
+------------+
| @@version |
+------------+
| 5.7.18-log |
+------------+

源库

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
表结构与数据
root@mysqldb 21:51: [fandb]> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`email` varchar(10) DEFAULT NULL,
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@mysqldb 18:43: [fandb]> select* from users;
+-------+
| email |
+-------+
| 1 |
| 10 |
| 20 |
| 30 |
| 5 |
+-------+
插入一条数据
insert into users values(50); --GTID=1297
再删掉
delete from users where email=50; ----GTID=1298
当前Executed_Gtid_Set
root@mysqldb 18:35: [fandb]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000005 | 495 | | | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

源库再次应用一下已经执行过得binlog, 再次应用insert into users values(50); 这一条

这里先不考虑有没有可能这样子去恢复数据,只做实验

阅读全文

在datadir外创建InnoDB表

在datadir外创建表

要在MySQL datadir外的特定位置创建新的InnoDB file-per-table tablespace,请在create table时指定DATA DIRECTORY = absolute_path_to_directory子句

提前规划好位置,因为无法使用alter语句修改一个表的DATA DIRECTORY属性.

MySQL会在目标目录中创建一个对应于数据库名称的子目录,并在改目录中创建表的.ibd文件用于存储表数据.在MySQL datadir目录下的数据库目录中,MySQL创建一个包含表的路径名称的table_name.isl文件. .isl文件由MySQL处理,像一个符号链接(不过InnoDB表不支持实际的符号链接)

示例:

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
版本
[root@test43100 ~]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@test43100 ~]# mysql -e "\s"
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
root@mysqldb 11:00: [(none)]> use fandb
Database changed
root@mysqldb 11:00: [fandb]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
root@mysqldb 11:00: [fandb]> create table t_out(id int auto_increment primary key) data directory='/data/outdir';
Query OK, 0 rows affected (0.37 sec)
查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
└── t_out.ibd
查看datadir
[root@test43100 fandb]# ls -lt
total 412
-rw-r----- 1 mysql mysql 28 Sep 6 11:03 t_out.isl
-rw-r----- 1 mysql mysql 8556 Sep 6 11:03 t_out.frm
包含.isl和.frm文件

您还可以将CREATE TABLE … TABLESPACE与DATA DIRECTORY子句结合使用,以便在MySQL数据目录之外创建一个file-per-table tablespace。 为此,您必须指定innodb_file_per_table作为表空间名称。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@mysqldb 11:03: [fandb]> create table t_out2(id int auto_increment primary key) TABLESPACE = innodb_file_per_table data directory='/data/outdir';
Query OK, 0 rows affected (0.42 sec)
查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
├── t_out2.ibd
└── t_out.ibd
查看datadir
[root@test43100 fandb]# ls -lt
total 428
-rw-r----- 1 mysql mysql 29 Sep 6 11:08 t_out2.isl
-rw-r----- 1 mysql mysql 8556 Sep 6 11:08 t_out2.frm

使用第二章方法无需启用innodb_file_per_table

使用说明

  • MySQL最初保持.ibd文件打开,阻止您卸载设备,但如果服务器正忙,最终可能会关闭该表。 当MySQL运行时,请注意不要意外卸载外部设备,或者在设备断开连接时启动MySQL。 当相关的.ibd文件丢失时尝试访问表会导致严重的错误,需要重新启动服务器。
    如果.ibd文件仍然不在预期路径,服务器重新启动可能会失败。 在这种情况下,请手动删除数据库目录中的table_name.isl文件,并在重新启动后执行DROP TABLE以删除.frm文件,并从数据字典中删除有关该表的信息。

  • Before tables on an NFS-mounted volume, review potential issues outlined in Using NFS with MySQL.

  • If you use an LVM snapshot, file copy, or other file-based mechanism to back up the .ibd file, always use the FLUSH TABLES … FOR EXPORT statement first to make sure all changes that were buffered in memory are flushed to disk before the backup occurs.

  • DATA DIRECTORY子句是使用符号链接的一个支持的替代方法.InnoDB直接使用符号链接是不支持的

innobackupex遇到的坑

stream选择使用tar方式压缩到时slave_info信息不完整

版本

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
[root@test1 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@test1 backup]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@test1 backup]# gzip --version
gzip 1.3.12
Copyright (C) 2007 Free Software Foundation, Inc.
Copyright (C) 1993 Jean-loup Gailly.
This is free software. You may redistribute copies of it under the terms of
the GNU General Public License <http://www.gnu.org/licenses/gpl.html>.
There is NO WARRANTY, to the extent permitted by law.
Written by Jean-loup Gailly.
[root@test1 backup]# mysql -e"\s"
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 11
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /data/mysqldata/3306/mysql.sock
Uptime: 15 min 52 sec
Threads: 3 Questions: 61 Slow queries: 0 Opens: 116 Flush tables: 5 Open tables: 0 Queries per second avg: 0.064
--------------

备份命令

1
innobackupex --user=backup --password='backup' --slave-info --stream=tar /tmp | gzip -> /data/mysqldata/backup/xtra_full.tar.gz

期望得到的完整信息

1
2
3
[root@test1 slave_info]# more xtrabackup_slave_info
SET GLOBAL gtid_purged='5c351518-78ec-11e7-8e7a-005056a610c3:1-1164';
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

阅读全文

主从传输表空间的坑

主库import tablespace只会在binlog中记录alter table xxx import tablespace语句,而不会记录表中的数据的插入语句

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
[mysql@master2 ~]$ mysqlbinlog -vv --base64-output=decode-rows /data/mysqldata/3306/binlog/mysql-bin.000013 --start-position=694
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 694
#170713 7:46:11 server id 23306 end_log_pos 759 CRC32 0xfa604449 GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= '5691c701-382a-11e5-bbc4-000c293d13e1:19'/*!*/;
# at 759
#170713 7:46:11 server id 23306 end_log_pos 869 CRC32 0x35860c26 Query thread_id=8 exec_time=0 error_code=0
use `fandb`/*!*/;
SET TIMESTAMP=1499903171/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
alter table dept import tablespace
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

假如在源端flush table xxx for export后,只将xxx.{ibd,cfg}拷贝到主库,那么当主库alter table xxx discard tablespace时,从库也会执行discard tablespace
而当主库执行alter table xxx import tablespace时,由于主库有拷贝过来的xxx.{ibd,cfg},所以可以执行成功,而从库没有,会失败

1
2
3
2017-07-10T21:48:13.649264Z 25 [Warning] Slave: InnoDB: ALTER TABLE `fandb`.`dept4` IMPORT TABLESPACE failed with error 44 : 'Tablespace not found' Error_code: 1816
2017-07-10T21:48:13.649293Z 25 [Warning] Slave: Tablespace is missing for table `fandb`.`dept4`. Error_code: 1812
2017-07-10T21:48:13.649321Z 25 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 1015

slave会停止

阅读全文

Transportable Tablespace Internals

Transportable Tablespace Internals

以下信息描述了InnoDB的传输表空间复制过程的内部原理和error log中输出的信息

当在目标端执行ALTER TABLE ... DISCARD TABLESPACE命令时:

  • The table is locked in X mode.
  • 表空间会与表分离.The tablespace is detached from the table.

当在源端执行FLUSH TABLES ... FOR EXPORT命令时:

  • The table being flushed for export is locked in shared mode.
  • 清除协调程序线程已停止The purge coordinator thread is stopped.
  • 脏块会被写入磁盘
  • 表的元数据信息会被写入二进制文件.cfg中

此操作的预期错误日志消息:

1
2
3
4
2013-09-24T13:10:19.903526Z 2 [Note] InnoDB: Sync to disk of '"test"."t"' started.
2013-09-24T13:10:19.903586Z 2 [Note] InnoDB: Stopping purge
2013-09-24T13:10:19.903725Z 2 [Note] InnoDB: Writing table metadata to './test/t.cfg'
2013-09-24T13:10:19.904014Z 2 [Note] InnoDB: Table '"test"."t"' flushed to disk

当在源端执行UNLOCK TABLES命令时:

  • 二进制文件.cfg会被删除
  • The shared lock on the table or tables being imported is released ,并且清除协调程序线程会重启purge coordinator thread is restarted.

此操作的预期错误日志消息:

1
2
2013-09-24T13:10:21.181104Z 2 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
2013-09-24T13:10:21.181180Z 2 [Note] InnoDB: Resuming purge

当在目标端执行ALTER TABLE ... IMPORT TABLESPACE时,导入算法会执行如下操作:

  • 将检查每个表空间页是否损坏.
  • 每个页面上的空间ID和日志序列号(LSN)都会更新
  • 标志被验证,LSN被更新为头页.
  • Btree页面更新.
  • 页面状态设置为dirty,以便它将被写入磁盘.

此操作的预期错误日志消息:

1
2
3
4
5
6
2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete

注意
您还可能会收到一个警告,表明丢弃了表空间(如果您丢弃了目标表的表空间)和一条消息,指出由于缺少.ibd文件而无法计算统计信息:

1
2
3
2013-07-18 15:14:38 34960 [Warning] InnoDB: Table "test"."t" tablespace is set as discarded.
2013-07-18 15:14:38 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t" because the .ibd file is missing. For help, please refer to
http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html

Transportable Tablespace示例

Transportable Tablespace示例

http://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html

例1:将InnoDB表从一个服务器复制到另一个服务器

此过程演示如何将InnoDB表从正在运行的MySQL服务器实例复制到另一个正在运行的实例.经过一些小调整相同过程可用于在同一实例上执行完整表恢复.
1.source端

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
(mysql@localhost) [test]> show table status like 'dept'\G
*************************** 1. row ***************************
Name: dept
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2016-08-26 14:25:50
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
(mysql@localhost) [test]> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.01 sec)

2.target端

1
2
3
4
5
6
CREATE TABLE `dept` (
`deptno` int(11) NOT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.在目标端discard tablespace.Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table

1
mysql> alter table dept discard tablespace;

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#