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;

阅读全文

Transportable Tablespaces

迁移File-Per-Table Tablespaces

Transportable Tablespace示例
Transportable Tablespace Internals
本节介绍如何将file-per-table tablespace从一个server迁移到另一个server,也称为可Transportable Tablespaces功能。 在MySQL 5.7.4之前,只支持非分区的InnoDB表。 从MySQL 5.7.4开始,还支持分区的InnoDB表和各个InnoDB表分区和子分区。

There are many reasons why you might copy an InnoDB file-per-table tablespace to a different database server:

  • 生成报告而不影响生产库
  • 为备库初始化数据
  • 用于数据恢复
  • 相对于mysqldump,传输表空间有个更快的速度
  • 将每个文件的表空间移动到具有更适合系统要求的存储介质的服务器.例如,您可能希望在SSD设备上拥有繁忙的表,或在大容量HDD设备上使用大型表.

限制和使用注意事项

  • 仅当innodb_file_per_table设置为ON(这是MySQL 5.6.6的默认设置)时,传输表空间功能才可以使用。 驻留在共享系统表空间中的表不能使用此功能。
  • 在传输过程中,只有只读操作可以执行
  • page size要相同.When importing a tablespace, the page size must match the page size of the importing instance.
  • Prior to MySQL 5.7.4, DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported. If you run ALTER TABLE … DISCARD TABLESPACE on a partitioned table, the following error is returned: ERROR 1031 (HY000): Table storage engine for ‘part’ doesn’t have this option. As of MySQL 5.7.4, ALTER TABLE … DISCARD TABLESPACE is supported for partitioned InnoDB tables, and ALTER TABLE … DISCARD PARTITION … TABLESPACE is supported for InnoDB table partitions.
  • 当foreign_key_checks设置为1时,对于父 - 子(主键 - 外键)关系的表空间不支持DISCARD TABLESPACE。在放弃父子表的表空间之前,请设置foreign_key_checks = 0。 分区的InnoDB表不支持外键。
  • ALTER TABLE … IMPORT TABLESPACE不会对导入的数据实施外键约束。 如果表之间存在外键约束,则应在相同(逻辑)时间点导出所有表。 分区的InnoDB表不支持外键。
  • ALTER TABLE … IMPORT TABLESPACE和ALTER TABLE … IMPORT PARTITION … TABLESPACE不需要.cfg元数据文件来导入表空间。 但是,在不使用.cfg文件导入时,不会执行元数据检查,并且将发出类似于以下内容的警告:
    1
    2
    3
    Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
    test\t.cfg', will attempt to import without schema verification
    1 row in set (0.00 sec)

没有.cfg文件导入的能力可能更方便,当不需要模式不匹配。 此外,无法使用.cfg文件导入的能力在无法从.ibd文件收集元数据的崩溃恢复方案中很有用。

The ability to import without a .cfg file may be more convenient when no schema mismatches are expected. Additionally, the ability to import without a .cfg file could be useful in crash recovery scenarios in which metadata cannot be collected from an .ibd file.

  • 由于.cfg元数据文件限制,在为分区表导入表空间文件时,不会报告分区类型或分区定义差异的模式不匹配。但会报告列差异。
  • 在子分区表上运行ALTER TABLE … DISCARD PARTITION … TABLESPACE和ALTER TABLE … IMPORT PARTITION … TABLESPACE时,允许使用分区表和子分区表名。指定分区名称时,该分区的子分区将包括在操作中。

  • 在MySQL 5.6或更高版本中,如果两个服务器都具有GA(通用可用性)状态并且它们的版本在同一系列中,则从另一个服务器导入表空间文件是有效的。否则,该文件必须在导入它的服务器上创建。

  • 在复制方案中,在主节点和从节点上必须将innodb_file_per_table设置为ON。

  • 在Windows上,InnoDB以小写形式内部存储数据库,表空间和表名。要避免区分大小写操作系统(如Linux和UNIX)上的导入问题,请使用小写名称创建所有数据库,表空间和表。一种方便的方法是在创建数据库,表空间或表之前,在my.cnf或my.ini文件的[mysqld]节中添加以下行:

    阅读全文

调整InnoDB系统表空间大小

调整InnoDB系统表空间大小

本文介绍如何增大或缩小InnoDB system tablespace

增大InnoDB system tablespace

最简单的增大InnoDB system tablespace大小的方法是在一开始配置的时候就指定为自动扩展. 为innodb_data_file_path参数中的最后一个数据文件指定autoextend选项. InnoDB在空间不足时以64MB为单位自动增加该文件的大小. 可以通过设置innodb_autoextend_increment系统变量的值(以兆字节为单位)来更改增量大小.

您可以通过添加另一个数据文件来扩展系统表空间:

1.关闭MySQL
2.如果上一个数据文件是使用关键字autoextend定义的,则根据实际增长的大小将其定义更改为使用固定大小. 检查数据文件的大小,将其舍入到1024×1024字节(= 1MB)的最接近的倍数,并在innodb_data_file_path中显式指定舍入后的大小.
3.将新的数据文件添加到innodb_data_file_path的末尾,可以指定该文件为自动扩展. 注意,只能将innodb_data_file_path中的最后一个数据文件指定为自动扩展.
4.启动MySQL

实际例子:

初始只有一个ibdata1,现在我们想增加一个数据文件

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假设ibdata1此时已经增长到988M,那么修改配置为

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

启动MySQL后,ibdata2会被初始化

1
2
3
2017-08-11T10:27:06.014446+08:00 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2017-08-11T10:27:06.014567+08:00 0 [Note] InnoDB: Setting file './ibdata2' size to 50 MB. Physically writing the file full; Please wait ...
2017-08-11T10:27:06.182464+08:00 0 [Note] InnoDB: File './ibdata2' size is now 50 MB.

缩小InnoDB system tablespace

您不能从系统表空间中删除数据文件. 要减少系统表空间大小,请使用以下过程:

1.使用mysqldump来转储所有的InnoDB表,包括位于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
mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+---------------------------+
| TABLE_NAME |
+---------------------------+
| engine_cost |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+

2.关闭MySQL
3.删除所有现有的表空间文件( .ibd),包括ibdata和ib_log文件. 不要忘记删除位于MySQL数据库中的表的 .ibd文件.
4.删除InnoDB表的任何.frm文件.
5.配置新的表空间.
6.重启MySQL
7.导入dump文件

Note
如果您的数据库仅使用InnoDB引擎,可能会更容易地转储所有数据库,停止服务器,删除所有数据库和InnoDB日志文件,重新启动服务器以及导入转储文件.


Powered by Hexo and Hexo-theme-hiker

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

访客数 : | 访问量 :

#