在表空间的定义中指定最后数据文件自动扩展

 

 

 

14.5 InnoDB Tablespace Management

 

Preface

Preface

Preface

 

Preface

 

 

 

14.5.1 Resizing the InnoDB System Tablespace

 

    ibdata1 file is a shared system tablespace of innodb engine.Although we always set variable of "innodb_file_per_table" to "on" which means put the data of table into their individual ibd files.The ibdata file will still store undo log of our innodb tables.As the ibdata1 file is inevitable to make the MySQL database running properly.Today,I'm gonna demonstrate a way to rescue the table in an instance whose ibdata file is destroied by a certain way such as write several unmeaningful characters into it.

    We all know that Xtrabackup is a backup tool of percona for innodb or Xtradb.It's usually used to back up whole databases physically.But how to restore a single innodb table from a full Xtrabackup after dropping it by accident?We can also make use of the feature of transportable tablespace to accomplish it.

    There're many ways in backing up or migrating data from one server to another one.Logically,we can use mysqldump,mydumper,mypump to do that kind of job.Physically,we can use Xtrabackup even cold copy way.What I'm gonna introduce is a special method to transmit data between MySQL servers which called “transportable tablespace”.

14.5.2 Changing the Number or Size of InnoDB Redo Log Files

    Yesterday,I've demonstratated how to rescue a droped and a truncated table based on mysqldump backup.Let's see what's different in rescuing them based on a backup set made by Xtrabackup.Binlog is also indispensable,if we want to rerieve all the incremental data of them.

 

    I've demonstrated how to restore a table on a server to another using transportable tablespace in my yesterday's blog.Today,we'll use another tool called "mysqlfrm" to fetch the structure of table in .frm files.

 

14.5.3 Using Raw Disk Partitions for the System Tablespace

 

Porcedure

 

Introduction

14.5.4 InnoDB File-Per-Table Tablespaces

Framework

 

Introduce

 

14.5.5 Creating a File-Per-Table Tablespace Outside the Data Directory

 

Backup the instance using Xtrabackup first.

 

    What's transportable tablespace?It is supported only on innodb engine and based on export/import grammer of "alter table ... ;" clause since MySQL 5.6.6 version.As we all know,innodb supports putting data of tables in their own tablespaces instead of shared system tablespace by setting parameter "innodb_file_per_table=1".It's different from the conception of oracle database.Business tables in oracle can be stored togerther with each other in the same tablespace while MySQL oblying the rule of "one table one ibd".That is,these ".ibd" files is what we really need to transport.**

14.5.6 Copying File-Per-Table Tablespaces to Another Server

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row
 1 [root@zlm1 13:46:27 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
 3 180805 13:46:40 innobackupex: Starting the backup operation
 4 ... //Omitted.
 5 
 6 [root@zlm1 13:47:14 /data/backup]
 7 #ls -l
 8 total 4
 9 drwxr-x--- 7 root root 4096 Aug  5 13:47 2018-08-05_13-46-40
10 
11 [root@zlm1 13:47:17 /data/backup]

    mysqlfrm is a tool designed to diagnose information in .frm file when in recovery scenario.mysqlfrm provides two modes of operatins.By default,it creates a new instance referencing the base directory using "--basedir" it also need a port specified by "--port" which ought to be diffrent from the one used in the original instance.The other mode is connecting to the already exist instance using "--server".The new instance will be shutdown and all temperary files will be deleted after it reads data in .frm files.Further more,there're two exclusions when using mysqlfrm,one is foreign key constraints,the other one is auto increment number sequences.

 

14.5.7 Storing InnoDB Undo Logs in Separate Tablespaces

 

 

 

Scenarios

 

Precedure

Check the target table which we want to rescue in plan.

Example

  • Transport a single table to report server without influencing loads on product.
  • Transport a single table to slave server for correcting the replication errors about the table.
  • Transport a single table to better storages such as ssd device for special purpose.
  • Restore a big table efficiently and swiftly as mysqldump needs to reinsert data and rebuild indexes.

14.5.1 Resizing the InnoDB System Tablespace

 

 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
13 +----------+
14 | count(*) |
15 +----------+
16 |    10000 |
17 +----------+
18 1 row in set (0.05 sec)
19 
20 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest4 limit 1000;
21 Query OK, 1000 rows affected (0.17 sec)
22 
23 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
24 +----------+
25 | count(*) |
26 +----------+
27 |     9000 |
28 +----------+
29 1 row in set (0.00 sec)

 

 

 

Step 1: Destroy the table.

 

Install mysqlfrm tool.

Limitations

This section describes how to increase or decrease the size of the InnoDB system tablespace.

 

Destroy the ibdata1 file with "dd" command.

 1 [root@zlm1 10:03:25 ~]
 2 #yum install mysql-utilities
 3 
 4 Installed:
 5   mysql-utilities.noarch 0:1.3.6-1.el7                                                                                                               
 6 
 7 Dependency Installed:
 8   mysql-connector-python.noarch 0:1.1.6-1.el7                                                                                                        
 9 
10 Complete!
  • "innodb_file_per_table" should be set to "on"(the same to slave server if in replication structure).
  • Page size on instance of target server should be same as the one on source server.
  • It doesn't support partition table and tables which contains fulltext indexes.
  • "foreign_key_checks" should be set to "0" if there's a paraent-child relationship in a table.
  • It doesn't check the foreign key constraints when importing,so all relevant tables should be exported at the same time.
  • Target instance must has the same version of series with the source instance.
  • it's recommended to set "lower_case_table" to "1" to avoid import problems.

这一章节陈说了怎么样充实裁减InnoDB系统表空间的尺寸。

Generate a backup with Xtrabackup first.

 1 [root@zlm1 13:57:01 ~]
 2 #cd /data/mysql/mysql3306/data
 3 
 4 [root@zlm1 13:59:35 /data/mysql/mysql3306/data]
 5 #ls -l
 6 total 433892
 7 -rw-r----- 1 mysql mysql        56 Mar 18 15:10 auto.cnf
 8 -rw-r----- 1 mysql mysql     81490 Aug  5 13:50 error.log
 9 -rw-r----- 1 mysql mysql       882 Jul 31 16:36 ib_buffer_pool
10 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ibdata1
11 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile0
12 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile1
13 -rw-r----- 1 mysql mysql 104857600 Jul  1 14:32 ib_logfile2
14 -rw-r----- 1 mysql mysql  12582912 Aug  5 13:45 ibtmp1
15 -rw-r----- 1 mysql mysql      3924 Aug  5 13:53 innodb_status.3799
16 -rw-r----- 1 mysql mysql         0 Jul 14 02:52 innodb_status.3828
17 -rw-r----- 1 mysql mysql      4008 Jun  1 21:38 innodb_status.4131
18 drwxr-x--- 2 mysql mysql      4096 Jul  7 09:57 mrbs
19 drwxr-x--- 2 mysql mysql      4096 Mar 18 15:10 mysql
20 -rw-r----- 1 mysql mysql         5 Aug  5 13:45 mysql.pid
21 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 performance_schema
22 -rw-r----- 1 mysql mysql       177 Jun  4 16:48 relay-bin.000001
23 -rw-r----- 1 mysql mysql        19 Jun  4 16:48 relay-bin.index
24 -rw-r----- 1 mysql mysql    526773 Aug  5 13:45 slow.log
25 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 sys
26 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:35 sysbench
27 -rw-r----- 1 mysql mysql  11610419 Jul 29 09:52 zlm1.log
28 
29 [root@zlm1 13:59:38 /data/mysql/mysql3306/data]
30 #dd if=/dev/zero of=./ibdata1 bs=1024 count=262144
31 262144+0 records in
32 262144+0 records out
33 268435456 bytes (268 MB) copied, 1.61997 s, 166 MB/s
34 
35 [root@zlm1 14:02:34 /data/mysql/mysql3306/data]
36 #ls -l|grep ibdata1
37 -rw-r----- 1 mysql mysql 268435456 Aug  5 14:06 ibdata1 //The ibdata1 turned out to be 256M and filled with zero.

 

Example

 

 1 [root@zlm2 07:57:40 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 4 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1013306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1013306 
 5 180727 07:57:43 innobackupex: Starting the backup operation
 6 
 7 IMPORTANT: Please check that the backup run completes successfully.
 8            At the end of a successful backup run innobackupex
 9            prints "completed OK!".
10 
11 ... //Omitted the procedure.
12 
13 180727 07:57:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
14 xtrabackup: The latest check point (for incremental): '10141351562'
15 xtrabackup: Stopping log copying thread.
16 .180727 07:57:56 >> log scanned up to (10141351571)
17 
18 180727 07:57:56 Executing UNLOCK TABLES
19 180727 07:57:56 All tables unlocked
20 180727 07:57:56 [00] Copying ib_buffer_pool to /data/backup/2018-07-27_07-57-43/ib_buffer_pool
21 180727 07:57:56 [00]        ...done
22 180727 07:57:56 Backup created in directory '/data/backup/2018-07-27_07-57-43/'
23 MySQL binlog position: filename 'mysql-bin.000035', position '954887', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220'
24 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/backup-my.cnf
25 180727 07:57:56 [00]        ...done
26 180727 07:57:56 [00] Writing /data/backup/2018-07-27_07-57-43/xtrabackup_info
27 180727 07:57:56 [00]        ...done
28 xtrabackup: Transaction log of lsn (10141351562) to (10141351571) was copied.
29 180727 07:57:56 completed OK!

 

**Generate a Xtrabackup backup.**

 

Increasing the Size of the InnoDB System Tablespace

 

Restart the MySQL instance.

 1 [root@zlm1 10:07:36 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --host=localhost --user=root --password=Passw0rd --sock=/tmp/mysql3306.sock /data/backup
 3 180706 10:09:22 innobackupex: Starting the backup operation
 4 
 5 IMPORTANT: Please check that the backup run completes successfully.
 6            At the end of a successful backup run innobackupex
 7            prints "completed OK!".
 8 
 9 //Omitts the intermedia output.
10 
11 180706 10:10:27 Finished backing up non-InnoDB tables and files
12 180706 10:10:27 [00] Writing xtrabackup_binlog_info
13 180706 10:10:27 [00]        ...done
14 180706 10:10:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
15 xtrabackup: The latest check point (for incremental): '1703733455'
16 xtrabackup: Stopping log copying thread.
17 .180706 10:10:27 >> log scanned up to (1703733464)
18 
19 180706 10:10:27 Executing UNLOCK TABLES
20 180706 10:10:27 All tables unlocked
21 180706 10:10:27 [00] Copying ib_buffer_pool to /data/backup/2018-07-06_10-09-22/ib_buffer_pool
22 180706 10:10:27 [00]        ...done
23 180706 10:10:27 Backup created in directory '/data/backup/2018-07-06_10-09-22'
24 MySQL binlog position: filename 'mysql-bin.000071', position '194', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715494'
25 180706 10:10:27 [00] Writing backup-my.cnf
26 180706 10:10:27 [00]        ...done
27 180706 10:10:27 [00] Writing xtrabackup_info
28 180706 10:10:27 [00]        ...done
29 xtrabackup: Transaction log of lsn (1703733455) to (1703733464) was copied.
30 180706 10:10:27 completed OK!
31 
32 [root@zlm1 10:10:27 ~]
33 #cd /data/backup
34 
35 [root@zlm1 10:13:14 /data/backup]
36 #ls -l
37 total 4
38 drwxr-x--- 9 root root 4096 Jul  6 10:10 2018-07-06_10-09-22
39 
40 [root@zlm1 10:13:15 /data/backup]
41 #cd 2018-07-06_10-09-22/
42 
43 [root@zlm1 10:13:19 /data/backup/2018-07-06_10-09-22]
44 #ls -l
45 total 102468
46 drwxr-x--- 2 root root        51 Jul  6 10:10 aaron8219
47 -rw-r----- 1 root root       433 Jul  6 10:10 backup-my.cnf
48 drwxr-x--- 2 root root        19 Jul  6 10:10 -help
49 -rw-r----- 1 root root      9492 Jul  6 10:10 ib_buffer_pool
50 -rw-r----- 1 root root 104857600 Jul  6 10:09 ibdata1
51 drwxr-x--- 2 root root      4096 Jul  6 10:10 mysql
52 drwxr-x--- 2 root root      8192 Jul  6 10:10 performance_schema
53 drwxr-x--- 2 root root      8192 Jul  6 10:10 sys
54 drwxr-x--- 2 root root      4096 Jul  6 10:10 sysbench
55 -rw-r----- 1 root root        69 Jul  6 10:10 xtrabackup_binlog_info
56 -rw-r----- 1 root root       119 Jul  6 10:10 xtrabackup_checkpoints
57 -rw-r----- 1 root root       639 Jul  6 10:10 xtrabackup_info
58 -rw-r----- 1 root root      2560 Jul  6 10:10 xtrabackup_logfile
59 drwxr-x--- 2 root root      4096 Jul  6 10:10 zlm

Check the table which you want to transport first(eg. ”sbtest2” in database "sysbench" here).

 

**Continue to execute dml operation.**

 1 zlm@192.168.56.100:3306 [sysbench]>exit
 2 Bye
 3 
 4 [root@zlm1 14:13:06 ~]
 5 #mysqladmin shutdown
 6 
 7 [root@zlm1 14:13:10 ~]
 8 #ps aux|grep mysqld
 9 root      4002  0.0  0.0 112640   960 pts/0    R+   14:13   0:00 grep --color=auto mysqld
10 
11 [root@zlm1 14:13:15 ~]
12 #./mysqld.sh
13 
14 [root@zlm1 14:13:53 ~]
15 #mysql
16 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
17 
18 [root@zlm1 14:13:56 ~]
19 #mysql
20 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
21 
22 [root@zlm1 14:13:58 ~]
23 #cd /data/mysql/mysql3306/data
24 
25 [root@zlm1 14:14:07 /data/mysql/mysql3306/data]
26 #tail error.log
27 ... /Omitted.
28 2018-08-05T12:13:53.242723Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
29 2018-08-05T12:13:53.242806Z 0 [Note] mysqld (mysqld 5.7.21-log) starting as process 4008 ...
30 2018-08-05T12:13:53.249168Z 0 [Note] InnoDB: PUNCH HOLE support available
31 2018-08-05T12:13:53.249207Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
32 2018-08-05T12:13:53.249213Z 0 [Note] InnoDB: Uses event mutexes
33 2018-08-05T12:13:53.249218Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
34 2018-08-05T12:13:53.249222Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
35 2018-08-05T12:13:53.249227Z 0 [Note] InnoDB: Using Linux native AIO
36 2018-08-05T12:13:53.249426Z 0 [Note] InnoDB: Number of pools: 1
37 2018-08-05T12:13:53.249507Z 0 [Note] InnoDB: Using CPU crc32 instructions
38 2018-08-05T12:13:53.251488Z 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
39 2018-08-05T12:13:53.256630Z 0 [Note] InnoDB: Completed initialization of buffer pool
40 2018-08-05T12:13:53.257913Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
41 2018-08-05T12:13:53.280321Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
42 12:13:53 UTC - mysqld got signal 11 ;
43 This could be because you hit a bug. It is also possible that this binary
44 or one of the libraries it was linked against is corrupt, improperly built,
45 or misconfigured. This error can also be caused by malfunctioning hardware.
46 Attempting to collect some information that could help diagnose the problem.
47 As this is a crash and something is definitely wrong, the information
48 collection process might fail.
49 
50 key_buffer_size=8388608
51 read_buffer_size=2097152
52 max_used_connections=0
53 max_threads=100
54 thread_count=0
55 connection_count=0
56 It is possible that mysqld could use up to 
57 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 227127 K  bytes of memory
58 Hope that's ok; if not, decrease some variables in the equation.
59 
60 Thread pointer: 0x0
61 Attempting backtrace. You can use the following information to find out
62 where mysqld died. If you see no messages after this, something went
63 terribly wrong...
64 stack_bottom = 0 thread_stack 0x30000
65 mysqld(my_print_stacktrace+0x35)[0xf4a495]
66 mysqld(handle_fatal_signal+0x4a4)[0x7ce2f4]
67 /lib64/libpthread.so.0(+0xf130)[0x7f6bbee76130]
68 mysqld(_Z26page_cur_search_with_matchPK11buf_block_tPK12dict_index_tPK8dtuple_t15page_cur_mode_tPmS9_P10page_cur_tP8rtr_info+0x148)[0x1074478]
69 mysqld(_Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t+0x1598)[0x11806d8]
70 mysqld(_Z30btr_pcur_open_on_user_rec_funcP12dict_index_tPK8dtuple_t15page_cur_mode_tmP10btr_pcur_tPKcmP5mtr_t+0x212)[0x1184b62]
71 mysqld[0x11df28d]
72 mysqld(_Z19dict_load_sys_tableP12dict_table_t+0x69)[0x11e0609]
73 mysqld(_Z9dict_bootv+0xdfb)[0x11bf48b]
74 mysqld(_Z34innobase_start_or_create_for_mysqlv+0x3212)[0x11150a2]
75 mysqld[0x100023a]
76 mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x51)[0x819721]
77 mysqld[0xd39226]
78 mysqld(_Z40plugin_register_builtin_and_init_core_sePiPPc+0x3e4)[0xd397a4]
79 mysqld[0x7c48f7]
80 mysqld(_Z11mysqld_mainiPPc+0x92f)[0x7c7e9f]
81 /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f6bbd857af5]
82 mysqld[0x7be479]
83 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
84 information that should help you find out what is causing the crash.
85 
86 [root@zlm1 14:14:36 /data/mysql/mysql3306/data]
87 
88 //Okay,becasue of the lack of normal ibdata1 file in datadir,the instance cannot be started again.
89 //Then,how can we resuce the data in target table 'sbtest4'?
90 //First of all,let's get the ibd and frm file of it from the crashed server.

 

 1 (root@localhost mysql3306.sock)[sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest10           |
 7 | sbtest2            |
 8 | sbtest3            |
 9 | sbtest4            |
10 | sbtest5            |
11 | sbtest6            |
12 | sbtest7            |
13 | sbtest8            |
14 | sbtest9            |
15 +--------------------+
16 10 rows in set (0.00 sec)
17 
18 (root@localhost mysql3306.sock)[sysbench]>show create table sbtest2G
19 *************************** 1. row ***************************
20        Table: sbtest2
21 Create Table: CREATE TABLE `sbtest2` (
22   `id` int(11) NOT NULL AUTO_INCREMENT,
23   `k` int(11) NOT NULL DEFAULT '0',
24   `c` char(120) NOT NULL DEFAULT '',
25   `pad` char(60) NOT NULL DEFAULT '',
26   PRIMARY KEY (`id`),
27   KEY `k_2` (`k`)
28 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
29 1 row in set (0.00 sec)
30 
31 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest2;
32 +----------+
33 | count(*) |
34 +----------+
35 |   100000 |
36 +----------+
37 1 row in set (0.07 sec)
38 
39 (root@localhost mysql3306.sock)[sysbench]>show variables like '%innodb_file_per_table%';
40 +-----------------------+-------+
41 | Variable_name         | Value |
42 +-----------------------+-------+
43 | innodb_file_per_table | ON    |
44 +-----------------------+-------+
45 1 row in set (0.00 sec)

The easiest way to increase the size of the InnoDB system tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically in 8MB increments when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in megabytes.

 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11 | sbtest7            |
12 +--------------------+
13 7 rows in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
16 +----------+
17 | count(*) |
18 +----------+
19 |    10000 |
20 +----------+
21 1 row in set (0.00 sec)
22 
23 (zlm@192.168.1.101 3306)[sysbench]>show master status;
24 +------------------+----------+--------------+------------------+------------------------------------------------+
25 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 | mysql-bin.000035 |   954887 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220 |
28 +------------------+----------+--------------+------------------+------------------------------------------------+
29 1 row in set (0.00 sec)
30 
31 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
32 Query OK, 0 rows affected (0.02 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest7 limit 10;
35 Query OK, 10 rows affected (0.00 sec)
36 
37 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
38 +----------+
39 | count(*) |
40 +----------+
41 |     9990 |
42 +----------+
43 1 row in set (0.00 sec)
44 
45 (zlm@192.168.1.101 3306)[sysbench]>show master status;
46 +------------------+----------+--------------+------------------+------------------------------------------------+
47 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
48 +------------------+----------+--------------+------------------+------------------------------------------------+
49 | mysql-bin.000036 |     2338 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
50 +------------------+----------+--------------+------------------+------------------------------------------------+
51 1 row in set (0.00 sec)

 

***Prepare the backup.***

 

充实InnoDB系统表空间尺寸最简便易行的艺术是在一开首的时候就把它配置成机动扩展的。在表空间的定义中钦命最终数据文件自动扩张,当空间满的时候InnoDB会自动为特别文件扩展8MB。增进的值能够透过innodb_autoextend_increment系统参数设定,单位是M。

 

Copy ibd and frm file of table "sbtest4" to another newly initialized instance on zlm2.

 1 [root@zlm1 10:17:32 /data/backup/2018-07-06_10-09-22]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --host=localhost --user=root --password=Passw0rd --sock=/tmp/mysql3306.sock --apply-log /data/backup/2018-07-06_10-09-22/
 3 180706 10:18:21 innobackupex: Starting the apply-log operation
 4 
 5 IMPORTANT: Please check that the apply-log run completes successfully.
 6            At the end of a successful apply-log run innobackupex
 7            prints "completed OK!".
 8            
 9 //Omitts the intermedia output.
10 
11 InnoDB: Database was not shutdown normally!
12 InnoDB: Starting crash recovery.
13 InnoDB: xtrabackup: Last MySQL binlog file position 139807334, file name mysql-bin.000069
14 InnoDB: Removed temporary tablespace data file: "ibtmp1"
15 InnoDB: Creating shared tablespace for temporary tables
16 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
17 InnoDB: File './ibtmp1' size is now 12 MB.
18 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
19 InnoDB: 32 non-redo rollback segment(s) are active.
20 InnoDB: Waiting for purge to start
21 InnoDB: 5.7.13 started; log sequence number 1703733781
22 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
23 InnoDB: page_cleaner: 1000ms intended loop took 10865ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
24 InnoDB: FTS optimize thread exiting.
25 InnoDB: Starting shutdown...
26 InnoDB: Shutdown completed; log sequence number 1703733800
27 180706 10:18:36 completed OK!

**Create the structure of table sbtest2 in database "tt” of target instance.**

 

**Drop table "sbtest7" in "sysbench" database on master in a certain time(our miss operation point).**

1 [root@zlm1 14:20:39 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest4.* zlm2:/data/backup
3 sbtest4.frm                                                                                                        100% 8632     8.4KB/s   00:00    
4 sbtest4.ibd                                                                                                        100%   10MB  10.0MB/s   00:01

 

 1 (root@localhost mysql3306.sock)[tt]>CREATE TABLE `sbtest2` (
 2     ->   `id` int(11) NOT NULL AUTO_INCREMENT,
 3     ->   `k` int(11) NOT NULL DEFAULT '0',
 4     ->   `c` char(120) NOT NULL DEFAULT '',
 5     ->   `pad` char(60) NOT NULL DEFAULT '',
 6     ->   PRIMARY KEY (`id`),
 7     ->   KEY `k_2` (`k`)
 8     -> ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;
 9 Query OK, 0 rows affected (0.02 sec)
10 
11 (root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
12 +----------+
13 | count(*) |
14 +----------+
15 |        0 |
16 +----------+
17 1 row in set (0.00 sec)
18 
19 (root@localhost mysql3306.sock)[tt]>show variables like '%innodb_file_per_table%';
20 +-----------------------+-------+
21 | Variable_name         | Value |
22 +-----------------------+-------+
23 | innodb_file_per_table | ON    |
24 +-----------------------+-------+
25 1 row in set (0.00 sec)

You can expand the system tablespace by a defined amount by adding another data file:

 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest7;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 5 +--------------------+
 6 | Tables_in_sysbench |
 7 +--------------------+
 8 | sbtest1            |
 9 | sbtest2            |
10 | sbtest3            |
11 | sbtest4            |
12 | sbtest5            |
13 | sbtest6            |
14 +--------------------+
15 6 rows in set (0.00 sec)
16 
17 (zlm@192.168.1.101 3306)[sysbench]>show master status;
18 +------------------+----------+--------------+------------------+------------------------------------------------+
19 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
20 +------------------+----------+--------------+------------------+------------------------------------------------+
21 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 |
22 +------------------+----------+--------------+------------------+------------------------------------------------+
23 1 row in set (0.00 sec)

 

**Drop two tables to mimic misoperation.**

 

你能够通过扩展另叁个数据文件来扩充系统表空间:

 

Install mysql-utilities package on zlm2.

 1 root@localhost:mysql3306.sock [(none)]>show tables from zlm;
 2 +----------------+
 3 | Tables_in_zlm  |
 4 +----------------+
 5 | customer       |
 6 | goods          |
 7 | semi_sync_test |
 8 | test_flashbk   |
 9 | test_myisam    |
10 +----------------+
11 5 rows in set (0.00 sec)
12 
13 root@localhost:mysql3306.sock [(none)]>show tables from sysbench;
14 +--------------------+
15 | Tables_in_sysbench |
16 +--------------------+
17 | sbtest1            |
18 | sbtest10           |
19 | sbtest2            |
20 | sbtest3            |
21 | sbtest4            |
22 | sbtest5            |
23 | sbtest6            |
24 | sbtest7            |
25 | sbtest8            |
26 | sbtest9            |
27 +--------------------+
28 10 rows in set (0.00 sec)
29 
30 root@localhost:mysql3306.sock [(none)]>drop table zlm.test_flashbk,sysbench.sbtest1;
31 Query OK, 0 rows affected (0.11 sec)

**Detach the tablespace of table "sbtest2"  on target.**

 

Step 2: Begin to restore backup set.

 1 [root@zlm2 14:23:13 ~]
 2 #mysqlfrm --help
 3 -bash: mysqlfrm: command not found
 4 
 5 [root@zlm2 14:23:17 ~]
 6 #yum install mysql-utilities
 7 Loaded plugins: fastestmirror
 8 base                                                                                                                          | 3.6 kB  00:00:00     
 9 epel/x86_64/metalink                                                                                                          | 7.0 kB  00:00:00     
10 epel                                                                                                                          | 3.2 kB  00:00:00     
11 extras                                                                                                                        | 3.4 kB  00:00:00     
12 updates                                                                                                                       | 3.4 kB  00:00:00     
13 (1/5): epel/x86_64/group_gz                                                                                                   |  88 kB  00:00:00     
14 (2/5): epel/x86_64/updateinfo                                                                                                 | 932 kB  00:00:00     
15 (3/5): extras/7/x86_64/primary_db                                                                                             | 173 kB  00:00:00     
16 (4/5): epel/x86_64/primary                                                                                                    | 3.6 MB  00:00:01     
17 (5/5): updates/7/x86_64/primary_db                                                                                            | 4.3 MB  00:00:04     
18 Loading mirror speeds from cached hostfile
19  * base: mirrors.shu.edu.cn
20  * epel: mirrors.tongji.edu.cn
21  * extras: mirrors.163.com
22  * updates: mirrors.163.com
23 epel                                                                                                                                     12629/12629
24 Resolving Dependencies
25 --> Running transaction check
26 ---> Package mysql-utilities.noarch 0:1.3.6-1.el7 will be installed
27 --> Processing Dependency: mysql-connector-python for package: mysql-utilities-1.3.6-1.el7.noarch
28 --> Running transaction check
29 ---> Package mysql-connector-python.noarch 0:1.1.6-1.el7 will be installed
30 --> Finished Dependency Resolution
31 
32 ... //Omitted.
33 
34 Installed:
35   mysql-utilities.noarch 0:1.3.6-1.el7                                                                                                               
36 
37 Dependency Installed:
38   mysql-connector-python.noarch 0:1.1.6-1.el7                                                                                                        
39 
40 Complete!

 

1 (root@localhost mysql3306.sock)[zlm]>alter table sbtest2 discard tablespace;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 [root@zlm3 10:30:44 /data/mysql/mysql3306/data/tt]
5 #ls -l
6 total 16
7 -rw-r----- 1 mysql mysql   61 Jul  5 10:13 db.opt
8 -rw-r----- 1 mysql mysql 8632 Jul  5 10:31 sbtest2.frm //The sbtest2.ibd file has been deleted.

1.Shut down the MySQL server.

 

 

***Diagnose .frm file from Xtrabackup using mysqlfrm.***

 

1.关闭MySQL实例。

Copy the backup set to another server(mine is zlm3) with newly initialized instance of MySQL.

Restore the table structure by mysqlfrm.

 1 [root@zlm1 10:35:56 /data/backup/2018-07-06_10-09-22]
 2 #mysqlfrm --basedir=/usr/local/mysql --port=8219 --diagnostic /data/backup/2018-07-06_10-09-22/sysbench/sbtest1.frm /data/backup/2018-07-06_10-09-22/zlm/zlm:test_flashbk.frmsbtest1.frm /data/backup/2018-07-06_10-09-22/zlm/zlm:tes
 3 # WARNING The --port option is not used in the --diagnostic mode.
 4 # WARNING: Cannot generate character set or collation names without the --server option.
 5 # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
 6 # Reading .frm file for /data/backup/2018-07-06_10-09-22/sysbench/sbtest1.frm:
 7 # The .frm file is a TABLE.
 8 # CREATE TABLE Statement:
 9 
10 CREATE TABLE `sysbench`.`sbtest1` (
11   `id` int(11) NOT NULL AUTO_INCREMENT, 
12   `k` int(11) NOT NULL, 
13   `c` char(360) NOT NULL, 
14   `pad` char(180) NOT NULL, 
15 PRIMARY KEY `PRIMARY` (`id`),
16 KEY `k_1` (`k`)
17 ) ENGINE=InnoDB;
18 
19 # Reading .frm file for /data/backup/2018-07-06_10-09-22/zlm/test_flashbk.frm:
20 # The .frm file is a TABLE.
21 # CREATE TABLE Statement:
22 
23 CREATE TABLE `zlm`.`test_flashbk` (
24   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
25   `name` varchar(80) NOT NULL, 
26 PRIMARY KEY `PRIMARY` (`id`)
27 ) ENGINE=InnoDB;
28 
29 #...done.
30 
31 //If you want to get the information of character set,"--server" is indispensable.
32 //you can either use <dbname>:<tablename>.frm or just <tablename>.frm.
33 //"--port" can be omitted.

**Flush the "sbtest2" table on source.**

 

 1 [root@zlm2 08:03:12 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 6 root root 4096 Jul 27 07:57 2018-07-27_07-57-43
 5 
 6 [root@zlm2 08:03:15 /data/backup]
 7 #tar cf innobkex_full.tar 2018-07-27_07-57-43/
 8 
 9 [root@zlm2 08:03:42 /data/backup]
10 #ls -l
11 total 187384
12 drwxr-x--- 6 root root      4096 Jul 27 07:57 2018-07-27_07-57-43
13 -rw-r--r-- 1 root root 191877120 Jul 27 08:03 innobkex_full.tar
14 
15 [root@zlm2 08:03:48 /data/backup]
16 #scp innobkex_full.tar zlm3:/data/backup
17 root@zlm3's password: 
18 innobkex_full.tar                                                                                                  100%  183MB  91.5MB/s   00:02    
19 
20 [root@zlm2 08:04:39 /data/backup]
21 #
 1 [root@zlm2 14:30:41 ~]
 2 #cd /data/backup/
 3 
 4 [root@zlm2 14:30:48 /data/backup]
 5 #ls -l|grep sbtest4
 6 -rw-r----- 1 root  root      8632 Aug  5 14:30 sbtest4.frm
 7 -rw-r----- 1 root  root  10485760 Aug  5 14:30 sbtest4.ibd
 8 
 9 [root@zlm2 14:31:25 /data/backup]
10 #mysqlfrm --basedir=/usr/local/mysql --port=8219 --diagnostic ./sbtest4.frm
11 # WARNING The --port option is not used in the --diagnostic mode.
12 # WARNING: Cannot generate character set or collation names without the --server option.
13 # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
14 # Reading .frm file for ./sbtest4.frm:
15 # The .frm file is a TABLE.
16 # CREATE TABLE Statement:
17 
18 CREATE TABLE `sbtest4` (
19   `id` int(11) NOT NULL AUTO_INCREMENT, 
20   `k` int(11) NOT NULL, 
21   `c` char(360) NOT NULL, 
22   `pad` char(180) NOT NULL, 
23 PRIMARY KEY `PRIMARY` (`id`),
24 KEY `k_4` (`k`)
25 ) ENGINE=InnoDB;
26 
27 #...done.

 

 1 (root@localhost mysql3306.sock)[sysbench]>flush table sbtest2 for export;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 10:36:01 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l|grep sbtest2
 6 -rw-r----- 1 mysql mysql      559 Jul  5 10:35 sbtest2.cfg
 7 -rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
 8 -rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd //A .cfg file has been created now.
 9 
10 2018-07-05T08:35:03.515902Z 9 [Note] InnoDB: Sync to disk of `sysbench`.`sbtest2` started.
11 2018-07-05T08:35:03.515929Z 9 [Note] InnoDB: Stopping purge
12 2018-07-05T08:35:03.516147Z 9 [Note] InnoDB: Writing table metadata to './sysbench/sbtest2.cfg'
13 2018-07-05T08:35:03.516276Z 9 [Note] InnoDB: Table `sysbench`.`sbtest2` flushed to disk
14 
15 //error log shows the information after flush operation.
16 //table metadata has been written into the .cfg file.

2.If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

 

 

**Create vacant table using above create statement.**

 

2.万一从前的最后贰个文书已经定义自动扩大了,那就依靠表空间的莫过于拉长状态修改增加值。检查数据文件的分寸, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

Prepare to restore the table "sbtest7".

Create a same table structure in the new instance using the restored "create table" statement.

 1 root@localhost:mysql3306.sock [(none)]>CREATE TABLE `sysbench`.`sbtest1` (
 2     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
 3     ->   `k` int(11) NOT NULL, 
 4     ->   `c` char(360) NOT NULL, 
 5     ->   `pad` char(180) NOT NULL, 
 6     -> PRIMARY KEY `PRIMARY` (`id`),
 7     -> KEY `k_1` (`k`)
 8     -> ) ENGINE=InnoDB;
 9 ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
10 root@localhost:mysql3306.sock [(none)]>CREATE TABLE `zlm`.`test_flashbk` (
11     ->   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
12     ->   `name` varchar(80) NOT NULL, 
13     -> PRIMARY KEY `PRIMARY` (`id`)
14     -> ) ENGINE=InnoDB;
15 Query OK, 0 rows affected (0.02 sec)
16 
17 //We get an error when creating table sysbench.sbtest1 beause of the overload value of char.

**Copy .ibd & .cfg file to target.**

 

 1 [root@zlm3 08:05:13 /data/backup]
 2 #ls -l
 3 total 187380
 4 -rw-r--r-- 1 root root 191877120 Jul 27 08:04 innobkex_full.tar
 5 
 6 [root@zlm3 08:05:15 /data/backup]
 7 #tar xf innobkex_full.tar 
 8 
 9 [root@zlm3 08:05:20 /data/backup]
10 #ls -l
11 total 187384
12 drwxr-x--- 6 root root      4096 Jul 27 07:57 2018-07-27_07-57-43
13 -rw-r--r-- 1 root root 191877120 Jul 27 08:04 innobkex_full.tar
14 
15 [root@zlm3 08:06:06 /data/backup]
16 #cd 2018-07-27_07-57-43/
17 
18 [root@zlm3 08:06:22 /data/backup/2018-07-27_07-57-43]
19 #ls -l
20 total 102464
21 -rw-r----- 1 root root       495 Jul 27 07:57 backup-my.cnf
22 -rw-r----- 1 root root      8988 Jul 27 07:57 ib_buffer_pool
23 -rw-r----- 1 root root 104857600 Jul 27 07:57 ibdata1
24 drwxr-x--- 2 root root      4096 Jul 27 07:57 mysql
25 drwxr-x--- 2 root root      8192 Jul 27 07:57 performance_schema
26 drwxr-x--- 2 root root      8192 Jul 27 07:57 sys
27 drwxr-x--- 2 root root      4096 Jul 27 07:57 sysbench
28 -rw-r----- 1 root root        71 Jul 27 07:57 xtrabackup_binlog_info
29 -rw-r----- 1 root root       121 Jul 27 07:57 xtrabackup_checkpoints
30 -rw-r----- 1 root root       587 Jul 27 07:57 xtrabackup_info
31 -rw-r----- 1 root root      2560 Jul 27 07:57 xtrabackup_logfile
32 
33 [root@zlm3 08:06:23 /data/backup/2018-07-27_07-57-43]
34 #cd sysbench/
35 
36 [root@zlm3 08:06:25 /data/backup/2018-07-27_07-57-43/sysbench]
37 #ls -l
38 total 71768
39 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
40 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest1.frm
41 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest1.ibd
42 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest2.frm
43 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest2.ibd
44 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest3.frm
45 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest3.ibd
46 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest4.frm
47 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest4.ibd
48 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest5.frm
49 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest5.ibd
50 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest6.frm
51 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest6.ibd
52 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest7.frm
53 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest7.ibd
54 
55 [root@zlm3 08:06:27 /data/backup/2018-07-27_07-57-43/sysbench]
56 #mv sbtest7* ../
57 
58 [root@zlm3 08:06:38 /data/backup/2018-07-27_07-57-43/sysbench]
59 #ls -l
60 total 61516
61 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
62 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest1.frm
63 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest1.ibd
64 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest2.frm
65 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest2.ibd
66 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest3.frm
67 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest3.ibd
68 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest4.frm
69 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest4.ibd
70 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest5.frm
71 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest5.ibd
72 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest6.frm
73 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest6.ibd
74 
75 [root@zlm3 08:06:39 /data/backup/2018-07-27_07-57-43/sysbench]
76 #rm -f sbtest* //Delete all those irrelevant tables which I'm not going to rescue.It can extremly reduce the content I need.
77 
78 [root@zlm3 08:06:45 /data/backup/2018-07-27_07-57-43/sysbench]
79 #mv ../sbtest* .
80 
81 [root@zlm3 08:06:57 /data/backup/2018-07-27_07-57-43/sysbench]
82 #ls -l
83 total 10256
84 -rw-r----- 1 root root       61 Jul 27 07:57 db.opt
85 -rw-r----- 1 root root     8632 Jul 27 07:57 sbtest7.frm
86 -rw-r----- 1 root root 10485760 Jul 27 07:57 sbtest7.ibd
 1 [root@zlm2 14:39:02 /data/backup]
 2 #mysql
 3 Welcome to the MySQL monitor.  Commands end with ; or g.
 4 Your MySQL connection id is 7
 5 Server version: 5.7.21-log MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
14 
15 zlm@192.168.56.101:3306 [(none)]>show databases;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mrbs               |
21 | mysql              |
22 | performance_schema |
23 | sys                |
24 +--------------------+
25 5 rows in set (0.00 sec)
26 
27 zlm@192.168.56.101:3306 [(none)]>create database sysbench;
28 Query OK, 1 row affected (0.00 sec)
29 
30 zlm@192.168.56.101:3306 [(none)]>use sysbench
31 Database changed
32 zlm@192.168.56.101:3306 [sysbench]>show tables;
33 Empty set (0.00 sec)
34 
35 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
36     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
37     ->   `k` int(11) NOT NULL, 
38     ->   `c` char(360) NOT NULL, 
39     ->   `pad` char(180) NOT NULL, 
40     -> PRIMARY KEY `PRIMARY` (`id`),
41     -> KEY `k_4` (`k`)
42     -> ) ENGINE=InnoDB;
43 ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
44 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
45     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
46     ->   `k` int(11) NOT NULL, 
47     ->   `c` char(120) NOT NULL, 
48     ->   `pad` char(60) NOT NULL, 
49     -> PRIMARY KEY `PRIMARY` (`id`),
50     -> KEY `k_4` (`k`)
51     -> ) ENGINE=InnoDB;
52 ERROR 1146 (42S02): Table 'sysbench.sbtest4' doesn't exist
53 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4_bak` (
54     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
55     ->   `k` int(11) NOT NULL, 
56     ->   `c` char(120) NOT NULL, 
57     ->   `pad` char(60) NOT NULL, 
58     -> PRIMARY KEY `PRIMARY` (`id`),
59     -> KEY `k_4` (`k`)
60     -> ) ENGINE=InnoDB;
61 Query OK, 0 rows affected (0.01 sec)
62 
63 
64 zlm@192.168.56.101:3306 [sysbench]>rename table sbtest4_bak to sbtest4;
65 Query OK, 0 rows affected (0.00 sec)
66 
67 zlm@192.168.56.101:3306 [sysbench]>show tables;
68 +--------------------+
69 | Tables_in_sysbench |
70 +--------------------+
71 | sbtest4            |
72 +--------------------+
73 1 row in set (0.00 sec)
74 
75 zlm@192.168.56.101:3306 [sysbench]>show create table sbtest4G
76 *************************** 1. row ***************************
77        Table: sbtest4
78 Create Table: CREATE TABLE `sbtest4` (
79   `id` int(11) NOT NULL AUTO_INCREMENT,
80   `k` int(11) NOT NULL,
81   `c` char(120) NOT NULL,
82   `pad` char(60) NOT NULL,
83   PRIMARY KEY (`id`),
84   KEY `k_4` (`k`)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
86 1 row in set (0.00 sec)
87 
88 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
89 +----------+
90 | count(*) |
91 +----------+
92 |        0 |
93 +----------+
94 1 row in set (0.00 sec)

 

1 [root@zlm2 10:36:03 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest2.{ibd,cfg} zlm3:/data/mysql/mysql3306/data/tt/
3 root@zlm3's password: 
4 sbtest2.ibd                                                                                                        100%   29MB  29.0MB/s   00:00    
5 sbtest2.cfg                                                                                                        100%  559     0.6KB/s   00:00

3.Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

 

 

**Check the structure of  sbtest2 table in sysbench.**

 

3.在innodb_data_file_path的尾巴加多叁个新的文书,并钦点自动扩张。只有innodb_data_file_path里最终五个数据文件可以被钦命为全自动扩展。

Apply the redo logfile.

Discard the tablesapce of new table "sbtest4".

 1 root@localhost:mysql3306.sock [(none)]>show create table sysbench.sbtest2G
 2 *************************** 1. row ***************************
 3        Table: sbtest2
 4 Create Table: CREATE TABLE `sbtest2` (
 5   `id` int(11) NOT NULL AUTO_INCREMENT,
 6   `k` int(11) NOT NULL DEFAULT '0',
 7   `c` char(120) NOT NULL DEFAULT '', //In the counterpart table,the value is 120.
 8   `pad` char(60) NOT NULL DEFAULT '', //In the counterpart table,the value is 60.
 9   PRIMARY KEY (`id`),
10   KEY `k_2` (`k`)
11 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
12 1 row in set (0.00 sec)

**Release the lock resources on source instance.**

 

 1 [root@zlm3 08:06:58 /data/backup/2018-07-27_07-57-43/sysbench]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-27_07-57-43/
 3 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 4 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 5 180727 08:08:41 innobackupex: Starting the apply-log operation
 6 
 7 IMPORTANT: Please check that the apply-log run completes successfully.
 8            At the end of a successful apply-log run innobackupex
 9            prints "completed OK!".
10 
11 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
12 xtrabackup: cd to /data/backup/2018-07-27_07-57-43/
13 
14 ... //Omitted.
15 
16 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
17 InnoDB: New log files created, LSN=10141352841
18 InnoDB: Highest supported file format is Barracuda.
19 InnoDB: Log scan progressed past the checkpoint lsn 10141352972
20 InnoDB: Doing recovery: scanned up to log sequence number 10141352981 (0%)
21 InnoDB: Database was not shutdown normally!
22 InnoDB: Starting crash recovery.
23 InnoDB: xtrabackup: Last MySQL binlog file position 954219, file name mysql-bin.000035
24 InnoDB: Removed temporary tablespace data file: "ibtmp1"
25 InnoDB: Creating shared tablespace for temporary tables
26 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
27 InnoDB: File './ibtmp1' size is now 12 MB.
28 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
29 InnoDB: 32 non-redo rollback segment(s) are active.
30 InnoDB: Waiting for purge to start
31 InnoDB: 5.7.19 started; log sequence number 10141352981
32 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
33 InnoDB: page_cleaner: 1000ms intended loop took 18267ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
34 InnoDB: FTS optimize thread exiting.
35 InnoDB: Starting shutdown...
36 InnoDB: Shutdown completed; log sequence number 10141353000
37 180727 08:09:03 completed OK!
38 
39 [root@zlm3 08:09:03 /data/backup/2018-07-27_07-57-43/sysbench]
40 #cd ..
41 
42 [root@zlm3 08:11:14 /data/backup/2018-07-27_07-57-43]
43 #ls -l
44 total 430144
45 -rw-r----- 1 root root       495 Jul 27 07:57 backup-my.cnf
46 -rw-r----- 1 root root      8988 Jul 27 07:57 ib_buffer_pool
47 -rw-r----- 1 root root 104857600 Jul 27 08:09 ibdata1
48 -rw-r----- 1 root root 104857600 Jul 27 08:09 ib_logfile0
49 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile1
50 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile2
51 -rw-r----- 1 root root  12582912 Jul 27 08:09 ibtmp1
52 drwxr-x--- 2 root root      4096 Jul 27 07:57 mysql
53 drwxr-x--- 2 root root      8192 Jul 27 07:57 performance_schema
54 drwxr-x--- 2 root root      8192 Jul 27 07:57 sys
55 drwxr-x--- 2 root root        55 Jul 27 08:06 sysbench
56 -rw-r----- 1 root root        71 Jul 27 07:57 xtrabackup_binlog_info
57 -rw-r--r-- 1 root root        24 Jul 27 08:08 xtrabackup_binlog_pos_innodb
58 -rw-r----- 1 root root       121 Jul 27 08:08 xtrabackup_checkpoints
59 -rw-r----- 1 root root       587 Jul 27 07:57 xtrabackup_info
60 -rw-r----- 1 root root   8388608 Jul 27 08:08 xtrabackup_logfile
61 -rw-r--r-- 1 root root         1 Jul 27 08:08 xtrabackup_master_key_id
62 
63 [root@zlm3 08:11:15 /data/backup/2018-07-27_07-57-43]
64 #cat xtrabackup_binlog_info
65 mysql-bin.000035    954887    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220
66 
67 [root@zlm3 08:11:21 /data/backup/2018-07-27_07-57-43]
68 #cat xtrabackup_binlog_pos_innodb
69 mysql-bin.000035    954219
70 
71 //The result of "show master status;" on zlm2.
72 (zlm@192.168.1.101 3306)[(none)]>show master status;
73 +------------------+----------+--------------+------------------+------------------------------------------------+
74 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
75 +------------------+----------+--------------+------------------+------------------------------------------------+
76 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 | //The newest GTID is "uuid:1-3730222"
77 +------------------+----------+--------------+------------------+------------------------------------------------+
78 1 row in set (0.00 sec)
 1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 discard tablespace;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 15:01:45 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l
 6 total 16
 7 -rw-r----- 1 mysql mysql   61 Aug  5 14:39 db.opt
 8 -rw-r----- 1 mysql mysql 8632 Aug  5 14:54 sbtest4.frm
 9 
10 //After the discard operation,the new ibd file has gone.

 

 1 (root@localhost mysql3306.sock)[sysbench]>unlock tables;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 10:37:57 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l|grep sbtest2
 6 -rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
 7 -rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd
 8 
 9 2018-07-05T08:38:09.256442Z 9 [Note] InnoDB: Deleting the meta-data file './sysbench/sbtest2.cfg'
10 2018-07-05T08:38:09.256458Z 9 [Note] InnoDB: Resuming purge
11 
12 //The .cfg file will be deleted after execute "unlock tables;"

4.Start the MySQL server again.

 

 

***Change the create statement reference to the value in sbtest2.***

 

4.再度启航MySQL实例。

Shutdown the instance on zlm3 and copy back the datafiles

Copy the ibd file of table "sbtest4" to the sysbench directory in datadir(notice the owner of file).

 1 when creating table sysbench.sbtest1 beause of the overload value of char.
 2 
 3 
 4 Check the structure of  sbtest2 table in sysbench.
 5  1 root@localhost:mysql3306.sock [(none)]>show create table sysbench.sbtest2G
 6  2 *************************** 1. row ***************************
 7  3        Table: sbtest2
 8  4 Create Table: CREATE TABLE `sbtest2` (
 9  5   `id` int(11) NOT NULL AUTO_INCREMENT,
10  6   `k` int(11) NO

**Check the files "sbtest2" table needs and give it the mysql privileges.**

 

 1 [root@zlm3 08:11:28 /data/backup/2018-07-27_07-57-43]
 2 #ps aux|grep mysqld
 3 mysql     5079  0.0 20.2 1110576 205684 pts/1  Sl   07:35   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 4 root      5341  0.0  0.0 112640   956 pts/2    R+   08:16   0:00 grep --color=auto mysqld
 5 
 6 [root@zlm3 08:16:38 /data/backup/2018-07-27_07-57-43]
 7 #mysqladmin shutdown
 8 
 9 [root@zlm3 08:16:43 /data/backup/2018-07-27_07-57-43]
10 #ps aux|grep mysqld
11 root      5352  0.0  0.0 112640   960 pts/2    R+   08:16   0:00 grep --color=auto mysqld
12 
13 [root@zlm3 08:16:45 /data/backup/2018-07-27_07-57-43]
14 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
15 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
16 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
17 180727 08:17:34 innobackupex: Starting the copy-back operation
18 
19 IMPORTANT: Please check that the copy-back run completes successfully.
20            At the end of a successful copy-back run innobackupex
21            prints "completed OK!".
22 
23 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
24 Original data directory /data/mysql/mysql3306/data is not empty! //The data directory need to be cleaned.
25 
26 [root@zlm3 08:17:34 /data/backup/2018-07-27_07-57-43]
27 #cd /data/mysql/mysql3306/data
28 
29 [root@zlm3 08:18:09 /data/mysql/mysql3306/data]
30 #ls -l
31 total 409700
32 -rw-r----- 1 mysql mysql        56 Jul 27 07:35 auto.cnf
33 -rw-r----- 1 mysql mysql     27980 Jul 27 08:16 error.log
34 -rw-r----- 1 mysql mysql       904 Jul 27 08:16 ib_buffer_pool
35 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ibdata1
36 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ib_logfile0
37 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile1
38 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile2
39 drwxr-x--- 2 mysql mysql      4096 Jul 27 07:34 mysql
40 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 performance_schema
41 -rw-r----- 1 mysql mysql       276 Jul 27 07:59 relay-bin.000004
42 -rw-r----- 1 mysql mysql      2562 Jul 27 08:16 relay-bin.000005
43 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_applier.000001
44 -rw-r----- 1 mysql mysql        45 Jul 27 07:35 relay-bin-group_replication_applier.index
45 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_recovery.000001
46 -rw-r----- 1 mysql mysql        46 Jul 27 07:35 relay-bin-group_replication_recovery.index
47 -rw-r----- 1 mysql mysql        38 Jul 27 07:59 relay-bin.index
48 -rw-r----- 1 mysql mysql       167 Jul 27 07:35 slow.log
49 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 sys
50 drwxr-x--- 2 mysql mysql        55 Jul 27 07:34 sysbench
51 -rw-r----- 1 mysql mysql       584 Jul 27 07:34 xtrabackup_info
52 
53 [root@zlm3 08:18:18 /data/mysql/mysql3306/data]
54 #rm -rf *
55 
56 [root@zlm3 08:18:19 /data/mysql/mysql3306/data]
57 #ls -l
58 total 0
59 
60 [root@zlm3 08:18:22 /data/mysql/mysql3306/data]
61 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
62 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
63 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
64 180727 08:18:53 innobackupex: Starting the copy-back operation
65 
66 IMPORTANT: Please check that the copy-back run completes successfully.
67            At the end of a successful copy-back run innobackupex
68            prints "completed OK!".
69 
70 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
71 180727 08:18:53 [01] Copying ib_logfile0 to /data/mysql/mysql3306/data/ib_logfile0
72 180727 08:18:54 [01]        ...done
73 180727 08:18:55 [01] Copying ib_logfile1 to /data/mysql/mysql3306/data/ib_logfile1
74 180727 08:18:55 [01]        ...done
75 180727 08:18:59 [01] Copying ib_logfile2 to /data/mysql/mysql3306/data/ib_logfile2
76 180727 08:19:00 [01]        ...done
77 180727 08:19:04 [01] Copying ibdata1 to /data/mysql/mysql3306/data/ibdata1
78 180727 08:19:06 [01]        ...done
79 
80 ... //Omitted.
81 
82 180727 08:19:13 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
83 180727 08:19:13 [01]        ...done
84 180727 08:19:13 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
85 180727 08:19:13 [01]        ...done
86 180727 08:19:13 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
87 180727 08:19:13 [01]        ...done
88 180727 08:19:13 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
89 180727 08:19:13 [01]        ...done
90 180727 08:19:13 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
91 180727 08:19:13 [01]        ...done
92 180727 08:19:14 completed OK!
 1 [root@zlm2 15:05:42 /data/backup]
 2 #cp sbtest4.ibd /data/mysql/mysql3306/data/sysbench/
 3 
 4 [root@zlm2 15:05:54 /data/backup]
 5 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
 6 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
 7 -rw-r----- 1 root  root  10485760 Aug  5 15:05 sbtest4.ibd
 8 
 9 [root@zlm2 15:06:12 /data/backup]
10 #chown mysql.mysql /data/mysql/mysql3306/data/sysbench/sbtest4.ibd
11 
12 [root@zlm2 15:06:39 /data/backup]
13 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
14 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
15 -rw-r----- 1 mysql mysql 10485760 Aug  5 15:05 sbtest4.ibd

 

 1 [root@zlm3 10:39:13 /data/mysql/mysql3306/data/tt]
 2 #ls -l
 3 total 29716
 4 -rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
 5 -rw-r----- 1 root  root       559 Jul  5 10:36 sbtest2.cfg
 6 -rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
 7 -rw-r----- 1 root  root  30408704 Jul  5 10:36 sbtest2.ibd
 8 
 9 //change the root.root to mysql.mysql
10 
11 [root@zlm3 10:39:30 /data/mysql/mysql3306/data/tt]
12 #chown mysql.mysql sbtest2.*
13 
14 [root@zlm3 10:39:41 /data/mysql/mysql3306/data/tt]
15 #ls -l
16 total 29716
17 -rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
18 -rw-r----- 1 mysql mysql      559 Jul  5 10:36 sbtest2.cfg
19 -rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
20 -rw-r----- 1 mysql mysql 30408704 Jul  5 10:36 sbtest2.ibd

For example, this tablespace has just one auto-extending data file ibdata1:

 

 

**Add a write lock on these two tables.**

 

譬喻,那么些表空间唯有一个电动扩充数据文件ibdata1:

Start the instance and check the table "sbtest7".

Import the original tablespace of table "sbtest4".

 1 root@localhost:mysql3306.sock [(none)]>lock tables sysbench.sbtest1 write;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 root@localhost:mysql3306.sock [(none)]>lock tables zlm.test_flashbk write;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 discard tablespace;
 8 ERROR 1100 (HY000): Table 'sbtest1' was not locked with LOCK TABLES //when locks another table,the lock on previous table will be released.
 9 root@localhost:mysql3306.sock [(none)]>lock tables sysbench.sbtest1 write; //This time,lock one and discard one in order.
10 Query OK, 0 rows affected (0.00 sec)
11 
12 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 discard tablespace;
13 Query OK, 0 rows affected (0.00 sec)
14 
15 root@localhost:mysql3306.sock [(none)]>lock tables zlm.test_flashbk write;
16 Query OK, 0 rows affected (0.00 sec)
17 
18 root@localhost:mysql3306.sock [(none)]>alter table zlm.test_flashbk discard tablespace;
19 Query OK, 0 rows affected (0.00 sec)

**Import the tablespace of "sbtest2" table.**

 

 1 [root@zlm3 08:21:05 /data/mysql/mysql3306/data]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 08:21:22 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 root      5416  0.0  0.0 112640   960 pts/2    R+   08:21   0:00 grep --color=auto mysqld
 7 
 8 [root@zlm3 08:21:26 /data/mysql/mysql3306/data]
 9 #tail -f error.log 
10 2018-07-27T08:21:22.902146+01:00 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
11 2018-07-27T08:21:22.902161+01:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
12 2018-07-27T08:21:23.503758+01:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
13 2018-07-27T08:21:23.503784+01:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
14 2018-07-27T08:21:23.503792+01:00 0 [ERROR] Failed to initialize builtin plugins.
15 2018-07-27T08:21:23.503796+01:00 0 [ERROR] Aborting
16 
17 2018-07-27T08:21:23.503801+01:00 0 [Note] Binlog end
18 2018-07-27T08:21:23.504149+01:00 0 [Note] mysqld: Shutdown complete
19 
20 ^C
21 
22 [root@zlm3 08:21:33 /data/mysql/mysql3306/data]
23 #chown -R mysql.mysql *
24 
25 [root@zlm3 08:22:05 /data/mysql/mysql3306/data]
26 #ls -l
27 total 421944
28 -rw-r----- 1 mysql mysql      1910 Jul 27 08:21 error.log
29 -rw-r----- 1 mysql mysql      8988 Jul 27 08:19 ib_buffer_pool
30 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:19 ibdata1
31 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:18 ib_logfile0
32 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:18 ib_logfile1
33 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:19 ib_logfile2
34 -rw-r----- 1 mysql mysql  12582912 Jul 27 08:19 ibtmp1
35 -rw-r----- 1 mysql mysql         0 Jul 27 08:21 innodb_status.5398
36 drwxr-x--- 2 mysql mysql      4096 Jul 27 08:19 mysql
37 drwxr-x--- 2 mysql mysql      8192 Jul 27 08:19 performance_schema
38 drwxr-x--- 2 mysql mysql      8192 Jul 27 08:19 sys
39 drwxr-x--- 2 mysql mysql        55 Jul 27 08:19 sysbench
40 -rw-r----- 1 mysql mysql        24 Jul 27 08:19 xtrabackup_binlog_pos_innodb
41 -rw-r----- 1 mysql mysql       587 Jul 27 08:19 xtrabackup_info
42 -rw-r----- 1 mysql mysql         1 Jul 27 08:19 xtrabackup_master_key_id
43 
44 [root@zlm3 08:22:12 /data/mysql/mysql3306/data]
45 #sh /root/mysqld.sh
46 
47 [root@zlm3 08:22:25 /data/mysql/mysql3306/data]
48 #ps aux|grep mysqld
49 mysql     5437  3.0 17.7 1110004 180944 pts/2  Sl   08:22   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
50 root      5470  0.0  0.0 112640   956 pts/2    R+   08:22   0:00 grep --color=auto mysqld
51 
52 (zlm@192.168.1.102 3306)[(none)]>show databases;
53 +--------------------+
54 | Database           |
55 +--------------------+
56 | information_schema |
57 | mysql              |
58 | performance_schema |
59 | sys                |
60 | sysbench           |
61 +--------------------+
62 5 rows in set (0.01 sec)
63 
64 (zlm@192.168.1.102 3306)[(none)]>use sysbench
65 Reading table information for completion of table and column names
66 You can turn off this feature to get a quicker startup with -A
67 
68 Database changed
69 (zlm@192.168.1.102 3306)[sysbench]>show tables;
70 +--------------------+
71 | Tables_in_sysbench |
72 +--------------------+
73 | sbtest7            |
74 +--------------------+
75 1 row in set (0.00 sec)
76 
77 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
78 +----------+
79 | count(*) |
80 +----------+
81 |    10000 |
82 +----------+
83 1 row in set (0.00 sec)
84 
85 //Compare the output of server zlm2.
86 (zlm@192.168.1.101 3306)[sysbench]>show tables;
87 +--------------------+
88 | Tables_in_sysbench |
89 +--------------------+
90 | sbtest1            |
91 | sbtest2            |
92 | sbtest3            |
93 | sbtest4            |
94 | sbtest5            |
95 | sbtest6            |
96 +--------------------+
97 7 rows in set (0.00 sec)
 1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (1.77 sec)
 3 
 4 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
 5 +----------+
 6 | count(*) |
 7 +----------+
 8 |     9000 |
 9 +----------+
10 1 row in set (0.01 sec)
11 
12 //Now the table has been rescued.
13 //Because of the destroying of ibdata1 in the original instance,it should be restored by Xtrabackup again.

 

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (2.68 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>show tables;
 5 +--------------+
 6 | Tables_in_tt |
 7 +--------------+
 8 | sbtest2      |
 9 +--------------+
10 1 row in set (0.00 sec)
11 
12 (root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
13 +----------+
14 | count(*) |
15 +----------+
16 |   100000 |
17 +----------+
18 1 row in set (0.06 sec)
19 
20 2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Importing tablespace for table 'sysbench/sbtest2' that was exported from host 'zlm2'
21 2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Phase I - Update all pages
22 2018-07-05T08:40:03.859485Z 10 [Note] InnoDB: Sync to disk
23 2018-07-05T08:40:04.936351Z 10 [Note] InnoDB: Sync to disk - done!
24 2018-07-05T08:40:04.962775Z 10 [Note] InnoDB: Phase III - Flush changes to disk
25 2018-07-05T08:40:04.975519Z 10 [Note] InnoDB: Phase IV - Flush complete
26 2018-07-05T08:40:04.975722Z 10 [Note] InnoDB: `tt`.`sbtest2` autoinc value set to 100001
27 
28 //The error log shows details of this import operation.

innodb_data_home_dir =

 

 

**Copy .ibd files from Xtrabackup and change privilege.**

 

innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Step 3: Implement a slave with replication filter option.

Summary

 1 [root@zlm1 11:06:18 /data/backup/2018-07-06_10-09-22]
 2 #cp sysbench/sbtest1.ibd /data/mysql/mysql3306/data/sysbench
 3 
 4 [root@zlm1 11:07:50 /data/backup/2018-07-06_10-09-22]
 5 #cp zlm/test_flashbk.ibd /data/mysql/mysql3306/data/zlm
 6 
 7 [root@zlm1 11:08:05 /data/backup/2018-07-06_10-09-22]
 8 #chown -R mysql.mysql /data/mysql/mysql3306/data
 9 
10 [root@zlm1 11:11:25 /data/backup/2018-07-06_10-09-22]
11 #ls -l /data/mysql/mysql3306/data/sysbench | grep sbtest1.ibd
12 -rw-r----- 1 mysql mysql 33554432 Jul  6 11:07 sbtest1.ibd
13 
14 [root@zlm1 11:12:39 /data/backup/2018-07-06_10-09-22]
15 #ls -l /data/mysql/mysql3306/data/zlm | grep test_flashbk.ibd
16 -rw-r----- 1 mysql mysql  12582912 Jul  6 11:08 test_flashbk.ibd

**If you detach an inexistent tablespace,it will show below errors.**

 

 

  • This method is only used to resuce a single table without backup when MySQL instance cannot startup beause of the destoryed ibdata file.
  • mysqlfrm is a tool which can load table structure from .frm files.We need to install the mysql-utilities package first.
  • If we don't have a properly full Xtrabackup and binlog,the .ibd file may lose the undo information on target table.In this situation,it's an incompletely recovery.

 

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
 5 Query OK, 0 rows affected, 1 warning (0.00 sec)
 6 
 7 (root@localhost mysql3306.sock)[tt]>show warnings;
 8 +---------+------+-----------------------------------------------------+
 9 | Level   | Code | Message                                             |
10 +---------+------+-----------------------------------------------------+
11 | Warning | 1812 | InnoDB: Tablespace is missing for table tt/sbtest2. |
12 +---------+------+-----------------------------------------------------+
13 1 row in set (0.00 sec)
14 
15 2018-07-05T08:52:55.055225Z 11 [ERROR] InnoDB: Cannot delete tablespace 494 because it is not found in the tablespace memory cache.
16 2018-07-05T08:52:55.055226Z 11 [Warning] InnoDB: Cannot delete tablespace 494 in DISCARD TABLESPACE: Tablespace not found
17 
18 //error log shows the ERROR & Warning because of the .ibd file has been deleted in first discard operation.

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to use a fixed size and adding a new auto-extending data file:

Execute a "change master to ... " on zlm3.

 

**Import tablespaces and check data of tables.**

 

万一那些数据文件,随着年华已经进步到988MB。这里就布局修改了原本数据应用三个稳住的高低并足够贰个新的自行扩大的数据文件:

 1 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 2 Empty set (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show master status;
 5 +------------------+----------+--------------+------------------+------------------------------------------------+
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
 7 +------------------+----------+--------------+------------------+------------------------------------------------+
 8 | mysql-bin.000003 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
 9 +------------------+----------+--------------+------------------+------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[sysbench]>change master to 
13     -> master_host='192.168.1.101',
14     -> master_port=3306,
15     -> master_user='repl',
16     -> master_password='repl4slave',
17     -> master_auto_position=1;
18 Query OK, 0 rows affected, 2 warnings (0.05 sec)
 1 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (1.73 sec)
 3 
 4 root@localhost:mysql3306.sock [(none)]>show warnings;
 5 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
 6 | Level   | Code | Message                                                                                                                                          |
 7 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
 8 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sysbench/sbtest1.cfg', will attempt to import without schema verification |
 9 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 root@localhost:mysql3306.sock [(none)]>alter table zlm.test_flashbk import tablespace;
13 Query OK, 0 rows affected, 1 warning (1.01 sec)
14 
15 root@localhost:mysql3306.sock [(none)]>show warnings;
16 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
17 | Level   | Code | Message                                                                                                                                          |
18 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
19 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './zlm/test_flashbk.cfg', will attempt to import without schema verification |
20 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 root@localhost:mysql3306.sock [(none)]>select count(*) from sysbench.sbtest1;
24 +----------+
25 | count(*) |
26 +----------+
27 |   100000 |
28 +----------+
29 1 row in set (0.25 sec)
30 
31 root@localhost:mysql3306.sock [(none)]>select count(*) from zlm.test_flashbk;
32 +----------+
33 | count(*) |
34 +----------+
35 |   100000 |
36 +----------+
37 1 row in set (0.10 sec)
38 
39 //The warnings show that tthe message about missing of .cfg file what rally doesn't matter.
40 //The .cfg file is usually create by executing "flush table ... for export;"
41 //We can benifit in crash recover scenario with the support of ignoring the missing of .cfg in transportable tablespace feature.

**Copy those files to target again.**

 

 

 

1 [root@zlm2 11:00:05 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest2.{ibd,cfg} zlm3:/data/mysql/mysql3306/data/tt/
3 root@zlm3's password: 
4 sbtest2.ibd                                                                                                        100%   29MB  29.0MB/s   00:01    
5 sbtest2.cfg: No such file or directory
6 
7 //Because of "unlock tables" operation,the .cfg file has gone now.

innodb_data_home_dir =

Start slave IO_Thread.

*Summary*

 

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

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave io_thread;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000001
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: 
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 776
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222 //The newest transaction has been retrieved.
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
  • *mysqlfrm is a tool of mysql-utilities which is specialized in analyzing .frm files in order to gain the missing structure of tables.*
  • mysqlfrm provides two modes of operation:1. connecting to server with "--server"(defaut mode);2. create a new instance with "--basedir".
  • With the help of parameter "--diagnostic",we can even get information from a .frm file without installing a MySQL server on the host.
  • We cannot get character set and collation information on tables if we forget to use "--server" option.
  • Of course,in order to use transportable tablespace properly,the parameter "innodb_file_per_table=1" is necessary.
  • In my case above,the structure of table about char datatype changed accidently which I'm still baffled with.
  • Also,we can restore these table to any other server like transportable tablespace does.

**Import the "sbtest2" tablespace again.**

 

 

 

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (2.34 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>show warnings;
 5 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 6 | Level   | Code | Message                                                                                                                                    |
 7 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 8 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './tt/sbtest2.cfg', will attempt to import without schema verification |
 9 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 //There's a warning about importing without .cfg file which won't impact the result.

When you add a new data file to the system tablespace configuration, make sure that the filename does not refer to an existing file. InnoDB creates and initializes the file when you restart the server.

Change replication filter "replicate_do_table" option.

  

当您要像系统表空间增添一个新的数据文件的时候,要确认保障文件名不会援用到现存的公文。InnoDB会在你重启实例的时候创造并初叶化那几个文件。

 1 (zlm@192.168.1.102 3306)[sysbench]>change replication filter replicate_do_table=(sysbench.sbteset7);
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000001
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: 
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: sysbench.sbteset7
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 776
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

Summary

 

 

  • ***Transportable Tablespace(TT) of innodb provids a different way in backing up and restoring a single table between servers.
  • ***TT merely supports innodb engine which can store data in tablespaces of their own by setting "innodb_file_per_table=1".
  • TT supports importing tablespace without .cfg file what brings about us much convenience in crash recovery.***
  • Notice that there will be shared read locks on the tables after execute "flush table ... for export;" what really influences the tables need to be write.

Decreasing the Size of the InnoDB System Tablespace

Analyze the binlog file on zlm2 to get the GTID before dropping operation.

 

 

 1 [root@zlm2 08:35:06 ~]
 2 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.
 3 mysql-bin.000011  mysql-bin.000015  mysql-bin.000019  mysql-bin.000023  mysql-bin.000027  mysql-bin.000031  mysql-bin.000035  
 4 mysql-bin.000012  mysql-bin.000016  mysql-bin.000020  mysql-bin.000024  mysql-bin.000028  mysql-bin.000032  mysql-bin.000036  
 5 mysql-bin.000013  mysql-bin.000017  mysql-bin.000021  mysql-bin.000025  mysql-bin.000029  mysql-bin.000033  mysql-bin.index   
 6 mysql-bin.000014  mysql-bin.000018  mysql-bin.000022  mysql-bin.000026  mysql-bin.000030  mysql-bin.000034  
 7 
 8 [root@zlm2 08:35:06 ~]
 9 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000036 > 36.log
10 
11 [root@zlm2 08:35:43 ~]
12 #tail -20 36.log
13 ###   @2=5039
14 ###   @3='32005756334-91141508143-50354766020-63389724388-71947328293-08845712532-56754824208-47077542818-55826128113-56514840054'
15 ###   @4='22255110541-96175917050-58709546750-68026622026-27581577227'
16 # at 2311
17 #180727  7:59:57 server id 1013306  end_log_pos 2338     Xid = 232
18 COMMIT/*!*/;
19 # at 2338
20 #180727  8:01:50 server id 1013306  end_log_pos 2399     GTID    last_committed=1    sequence_number=2    rbr_only=no
21 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222'/*!*/; //Here's the GTID we need.
22 # at 2399
23 #180727  8:01:50 server id 1013306  end_log_pos 2523     Query    thread_id=21    exec_time=0    error_code=0
24 use `sysbench`/*!*/;
25 SET TIMESTAMP=1532671310/*!*/;
26 DROP TABLE `sbtest7` /* generated by server */
27 /*!*/;
28 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
29 DELIMITER ;
30 # End of log file
31 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
32 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

You cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:

 

你不能够重系统表空间里移除数据文件。要削减系统表空间尺寸的话能够展开如下的操作:

Start slave SQL_Thread with until_option clause.

 

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave sql_thread until sql_before_gtids='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000002
14                 Relay_Log_Pos: 398
15         Relay_Master_Log_File: mysql-bin.000036
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: sysbench.sbteset7
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 2338
28               Relay_Log_Space: 776
29               Until_Condition: SQL_BEFORE_GTIDS
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
64 
65 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
66 +----------+
67 | count(*) |
68 +----------+
69 |    10000 |
70 +----------+
71 1 row in set (0.00 sec)
72 
73 //Why the amount of record isn't 9990?

1.Use mysqldump to dump all your InnoDB tables, including InnoDB tables located in the MySQL database. As of 5.6, there are five InnoDB tables included in the MySQL database:

 

1.采用mysqldump导出你有着的InnoDB表,包罗MySQL数据Curry的InnoDB表。从5.6始发,包含在MySQL数据Curry的有5个InnoDB表:

Try to stop and start the slave again.

 

 1 (zlm@192.168.1.102 3306)[sysbench]>stop slave;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>start slave;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 8 *************************** 1. row ***************************
 9                Slave_IO_State: Waiting for master to send event
10                   Master_Host: 192.168.1.101
11                   Master_User: repl
12                   Master_Port: 3306
13                 Connect_Retry: 60
14               Master_Log_File: mysql-bin.000036
15           Read_Master_Log_Pos: 2523
16                Relay_Log_File: relay-bin.000003
17                 Relay_Log_Pos: 438
18         Relay_Master_Log_File: mysql-bin.000036
19              Slave_IO_Running: Yes
20             Slave_SQL_Running: Yes
21               Replicate_Do_DB: 
22           Replicate_Ignore_DB: 
23            Replicate_Do_Table: sysbench.sbteset7
24        Replicate_Ignore_Table: 
25       Replicate_Wild_Do_Table: 
26   Replicate_Wild_Ignore_Table: 
27                    Last_Errno: 0
28                    Last_Error: 
29                  Skip_Counter: 0
30           Exec_Master_Log_Pos: 2523
31               Relay_Log_Space: 1064
32               Until_Condition: None
33                Until_Log_File: 
34                 Until_Log_Pos: 0
35            Master_SSL_Allowed: No
36            Master_SSL_CA_File: 
37            Master_SSL_CA_Path: 
38               Master_SSL_Cert: 
39             Master_SSL_Cipher: 
40                Master_SSL_Key: 
41         Seconds_Behind_Master: 0
42 Master_SSL_Verify_Server_Cert: No
43                 Last_IO_Errno: 0
44                 Last_IO_Error: 
45                Last_SQL_Errno: 0
46                Last_SQL_Error: 
47   Replicate_Ignore_Server_Ids: 
48              Master_Server_Id: 1013306
49                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
50              Master_Info_File: mysql.slave_master_info
51                     SQL_Delay: 0
52           SQL_Remaining_Delay: NULL
53       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
54            Master_Retry_Count: 86400
55                   Master_Bind: 
56       Last_IO_Error_Timestamp: 
57      Last_SQL_Error_Timestamp: 
58                Master_SSL_Crl: 
59            Master_SSL_Crlpath: 
60            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
61             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222
62                 Auto_Position: 1
63          Replicate_Rewrite_DB: 
64                  Channel_Name: 
65            Master_TLS_Version: 
66 1 row in set (0.00 sec)
67 
68 (zlm@192.168.1.102 3306)[sysbench]>show tables;
69 +--------------------+
70 | Tables_in_sysbench |
71 +--------------------+
72 | sbtest7            |
73 +--------------------+
74 1 row in set (0.00 sec)
75 
76 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
77 +----------+
78 | count(*) |
79 +----------+
80 |    10000 |
81 +----------+
82 1 row in set (0.01 sec)
83 
84 //Even though it has executed the newest GTID "uuid:3730222",the amount of record is unchanged.

mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';

 

+----------------------+

Try to analyze the binlog on slave zlm3.

| table_name |

 1 [root@zlm3 09:06:44 /data/mysql/mysql3306/data]
 2 #cd ../logs
 3 
 4 [root@zlm3 09:06:46 /data/mysql/mysql3306/logs]
 5 #ls -l
 6 total 16
 7 -rw-r----- 1 mysql mysql  169 Jul 27 07:32 mysql-bin.000001
 8 -rw-r----- 1 mysql mysql 1473 Jul 27 08:16 mysql-bin.000002
 9 -rw-r----- 1 mysql mysql  396 Jul 27 09:02 mysql-bin.000003
10 -rw-r----- 1 mysql mysql  132 Jul 27 08:22 mysql-bin.index
11 
12 [root@zlm3 09:06:47 /data/mysql/mysql3306/logs]
13 #mysqlbinlog -v --base64-output=decode-rows mysql-bin.000003 > 3.log
14 
15 [root@zlm3 09:07:26 /data/mysql/mysql3306/logs]
16 #cat 3.log
17 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
18 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
19 DELIMITER /*!*/;
20 # at 4
21 #180727  8:22:26 server id 1023306  end_log_pos 123     Start: binlog v 4, server v 5.7.21-log created 180727  8:22:26 at startup
22 # Warning: this binlog is either in use or was not closed properly.
23 ROLLBACK/*!*/; //It shows that transactions in this binlog file have been rolled back!!!
24 # at 123
25 #180727  8:22:26 server id 1023306  end_log_pos 190     Previous-GTIDs
26 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730215-3730221 //The rolled back GTID is from 3730215 to 3730221.
27 # at 190
28 #180727  8:01:50 server id 1013306  end_log_pos 251     GTID    last_committed=0    sequence_number=1    rbr_only=no
29 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222'/*!*/;
30 # at 251
31 #180727  8:01:50 server id 1013306  end_log_pos 323     Query    thread_id=21    exec_time=3665    error_code=0
32 SET TIMESTAMP=1532671310/*!*/;
33 SET @@session.pseudo_thread_id=21/*!*/;
34 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
35 SET @@session.sql_mode=1436549152/*!*/;
36 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
37 /*!C utf8 *//*!*/;
38 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
39 SET @@session.lc_time_names=0/*!*/;
40 SET @@session.collation_database=DEFAULT/*!*/;
41 BEGIN
42 /*!*/;
43 # at 323
44 #180727  8:01:50 server id 1013306  end_log_pos 396     Query    thread_id=21    exec_time=3665    error_code=0
45 SET TIMESTAMP=1532671310/*!*/;
46 COMMIT
47 /*!*/;
48 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
49 DELIMITER ;
50 # End of log file
51 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
52 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

+----------------------+

 

| innodb_index_stats |

    Okay,I didn't get my supposed consequences in the above test this time.In my opinion,the miss dropped table "sbtest7" should have been recovered to the state of "9990" instead of "10000" what really astonished me.

| innodb_table_stats |

    The transaction of deleting 10 records befor dropping "sbtest7" ,together with those other transactions in mysql-bin.000035,was rolled back.Even if the slave had executed the newest GTID "uuid:3720222",the incremtental modifications on the table couldn't be rescued.

| slave_master_info |

    I guess the result is due to that the redo log files in backup set didn't contain the transactions information,thus,it chose to roll back the transactions.

| slave_relay_log_info |

 

| slave_worker_info |

+----------------------+

5 rows in set (0.00 sec)

 

 

2.Stop the server.

2.关门实例。

 

3.Remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database.

3.移除全部现成的表空间文件(*.ibd),包括ibdata 和ib_log文件。还应该有要不忘怀移除MySQL数据Curry的*.ibd文件。

 

4.Remove any .frm files for InnoDB tables.

4.移除InnoDB表的.frm文件。

 

5.Configure a new tablespace.

5.安顿八个新的表空间。

 

在表空间的定义中指定最后数据文件自动扩展。6.Restart the server.

6.重启实例。

 

7.Import the dump files.

7.导入dump文件。

 

Note

 

If your databases only use the InnoDB engine, it may be simpler to dump all databases, stop the server, remove all databases and InnoDB log files, restart the server, and import the dump files.

若果您的数据库只行使了InnoDB引擎,那么最简易的法门是导出全体的数据库,甘休实例,移除全部的数据库和InnoDB日志文件,重启实例,再导入dump文件。

 

14.5.2 Changing the Number or Size of InnoDB Redo Log Files

 

To change the number or size of InnoDB redo log files in MySQL 5.6.7 or earlier, perform the following steps:

在MySQL5.6.7要么更早的版本改换InnoDB redo log文件的数额或大小,要实行下边包车型客车步骤:

 

1.If innodb_fast_shutdown is set to 2, set innodb_fast_shutdown to 1:

1.如果innodb_fast_shutdown的安装成2,那么改成1:

 

mysql> SET GLOBAL innodb_fast_shutdown = 1;

 

2.After ensuring that innodb_fast_shutdown is not set to 2, stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log).

2.在确保innodb_fast_shutdown设置的不是2从此,停止MySQL实例并确定保证未有不当(确定保证在log里面未有未成功职业的新闻)。

 

3.Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace.

3.把老的log文件复制到多个安然还是的地方,以抗御关闭报错的时候用来recover表空间。

 

4.Delete the old log files from the log file directory.

4.在日记文件目录删除老的日志文件。

 

5.Edit my.cnf to change the log file configuration.

5.编辑my.cnf修改日志文件配置。

 

6.Start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.

6.开发银行MySQL实例。mysqld的在运转的时候看到没有InnoDB日志文件会创立一个新的。

 

As of MySQL 5.6.8, the innodb_fast_shutdown setting is no longer relevant when changing the number or the size of InnoDB log files. Additionally, you are no longer required remove old log files, although you may still want to copy the old log files to a safe place, as a backup. To change the number or size of InnoDB log files, perform the following steps:

本文由必威发布于必威-数据,转载请注明出处:在表空间的定义中指定最后数据文件自动扩展

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。