MySQL MEB common usage, mysqlmeb usage

MySQL MEB common usage, mysqlmeb usage

1. Back up data to an image

Backup:

./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --backup-image=/images/20161008.mbi --backup-dir=/tmp/backup  backup-to-image

Backup-dir is used to store temporary files. Files in this directory can be deleted directly. You only need to back up this image file for each backup.

The/images directory must exist.

If you back up an image, you must first restore the image to the backup directory.

./mysqlbackup --backup-image=/images/20161008.mbi --backup-dir=/backup image-to-backup-dir

Application logs

./mysqlbackup --backup-dir=/backup apply-log

Restore

./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back   

 

2. Compressed backup

Backup

./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --backup-dir=/backup --compress --compress-level=9 backup

Application logs

./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --backup-dir=/backup --uncompress apply-log

Restore

./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back

 

3. Database-based Replication

Copying non-innodb tables

Mysqlbackup provides the databases parameter. Many people take it for granted that this parameter is used for database-based replication, but it is actually not. First, let's take a look at the instructions in -- help.

--databases=LIST              This option is used to filter the list of non-innodb              tables that needs to be backed up. To filter innodb tables,              use --include option.              The argument specifies space separated list of database/table              names of the following form:                   "db_name[.table_name] db_name1[.table_name1] ...".              If this option is not specified all databases will be               backed up. Also, if the specified database does not match              with any single database/table, then all databases will be              backed up.

-- Database is only used to back up non-innodb tables. Two methods are supported: -- databases = test and -- databases = test. test1.

Unexpectedly, if the test database is specified for replication, all other non-innodb databases will not be copied, for example, the user table in the mysql database and the performance_schema database.

The preceding description also mentions that "if the specified database does not match with any single database/table, then all databases will be backed up. if the specified database or table does not actually exist, all non-innodb tables will be backed up. However, in actual tests, if the specified table does not exist, all other non-innodb tables will not be copied.

Therefore, the -- databases scenario can basically be ignored, because for non-innodb tables, you can copy directly without using this method for backup, and specify a specific table, if the mysql database is not specified, the database cannot be started even if the restoration operation is performed. To copy multiple non-innodb tables, use: -- databases = 'test. test mysql'

Innodb table replication

The following describes how to copy an innodb table, which involves the -- include parameter.

  --include=REGEXP                Backup only those per-table innodb data files which match               the regular expression (REGEXP). For each table with a               per-table data file, a string of the form db_name.table_name              is checked against the regular expression (REGEXP). If the               REGEXP matches db_name.table_name, the table is included.               The REGEXP should be of POSIX 1003.2 'extended' form. 

We can see that the -- include parameter is matched by a regular expression.

For example, the following Backup statement backs up only database db1 and database test

./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --backup-dir=/backup --include='(db1|test)\..*' backup

I only backed up database db1 and database test.

However, this backup method is a bit problematic, which will be reflected during recovery.

1. I have not backed up several innodb tables in the mysql database.

2. backed up the frm files of all tables, including innodb tables without backup. In this way, the following error will be reported during restoration because only frm files are available, but no ibd files are available. Although it does not affect the normal startup of the database, it is not very good after all.

2016-09-28 10:05:16 1989 [ERROR] InnoDB: Failed to find tablespace for table '"test1"."test1"' in the cache. Attempting to load the tablespace with space id 25.2016-09-28 10:05:16 7fc1ac33e700  InnoDB: Operating system error number 2 in a file operation.InnoDB: The error means the system cannot find the path specified.2016-09-28 10:05:16 1989 [ERROR] InnoDB: Could not find a valid tablespace file for 'test1/test1'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.2016-09-28 10:05:16 7fc1ac33e700 InnoDB: cannot calculate statistics for table "test1"."test1" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

Therefore, in this backup mode, frm files of these tables need to be manually deleted.

The official documentation also mentions two parameters: -- only-innodb and -- only-innodb-with-frm.

If the -- only-innodb parameter is specified, only the ibd file of the specified table is backed up, And the frm file of the specified innodb table and any non-innodb table (including mysql and performance_schema) are not backed up)

If the -- only-innodb-with-frm parameter is specified, ibd and frm files of the specified table are backed up, but non-innodb tables are not backed up.

In my opinion, these two parameters are quite tricky. To replicate an innodb table, you can only specify the -- include parameter and manually delete the unwanted frm file.

 

4. Use the replicasable tablespace to replicate partial Databases

Copy

 ./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --backup-dir=/backup --include='db1\.t1' --use-tts backup

In this example, only one table db1.t1 is backed up. You can also back up multiple tables or databases.

Application logs

./mysqlbackup --backup-dir=/backup/ apply-log

Restore

./mysqlbackup --defaults-file=/backup/server-my.cnf --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --backup-dir=/backup/ copy-back

However, the following error is reported:

160929 11:37:35 mysqlbackup: INFO: Importing table: db1.t1. mysqlbackup: ERROR: mysql query: 'ALTER TABLE db1.t1 IMPORT TABLESPACE': Internal error: Cannot reset LSNs in table '"db1"."t1"' : Tablespace not found mysqlbackup: ERROR: Failed to import tablespace db1.t1.mysqlbackup failed with errors!

View the permissions of t1 files in the db1 directory

[root@localhost db1]# lltotal 116-rw-rw---- 1 mysql mysql    65 Sep 29 11:17 db.opt-rw-r--r-- 1 root  root    371 Sep 29 11:37 t1.cfg-rw-rw---- 1 mysql mysql  8556 Sep 29 11:37 t1.frm-rw-r--r-- 1 root  root  98304 Sep 29 11:37 t1.ibd

Found that the permission for the two files is root.

Note: Even if you set the backup Directory/backup permission to mysql, the permissions for these two files are still root.

You can modify the permissions of these two files.

[root@localhost db1]# chown mysql.mysql *

Manually import tablespaces on the client

mysql> alter table db1.t1 import tablespace;Query OK, 0 rows affected (1.55 sec)mysql> select * from db1.t1;+------+| id   |+------+|    1 |+------+1 row in set (0.00 sec)

Import successful.

Note: Replication of some tables or databases that can transmit tablespaces does not need to be restored on a new instance. It can be performed on any online instance, the premise is that the tables to be imported in these instances do not exist.

 

5. incremental replication

Full Replication

./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --backup-dir=/backup backup

First incremental copy

./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --incremental --incremental-backup-dir=/increment_backup_1 --incremental-base=dir:/backup backup

Second incremental replication

./mysqlbackup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock --incremental --incremental-backup-dir=/increment_backup_2 --incremental-base=dir:/increment_backup_1 backup

Incremental replication-based recovery

First, you must apply logs.

./mysqlbackup --backup-dir=/backup apply-log

First incremental recovery

./mysqlbackup --incremental-backup-dir=/increment_backup_1 --backup-dir=/backup apply-incremental-backup

Second incremental recovery

./mysqlbackup --incremental-backup-dir=/increment_backup_2 --backup-dir=/backup apply-incremental-backup

Restore

./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup copy-back

 

For Incremental backup, mysqlbackup supports two methods of Incremental backup.

Data file-based

--incremental --incremental-backup-dir=PATH   --start-lsn=LSN | --incremental-base=BACKUP 

Based on redo files

 --incremental-with-redo-log-only --incremental-backup-dir=PATH   --start-lsn=LSN | --incremental-base=BACKUP 

There is a premise for redo file-based backup, that is, the redo log after the specified LSN is not overwritten.

 

For incremental Backup Based on data files, in addition to the -- incremental-base method specified above, you can also specify -- start-lsn,

Start-lsnis the value of end_lsn in the backup_variables.txt file in the metadirectory.

6. other usage

Verify that the image is damaged

./mysqlbackup --backup-dir=/image --backup-image=2016_09_27_image validate