Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to solve the problems encountered in using innobackup 2.4 in MySQL. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

I. Preface

Percona has released innobackup 2.4 for a long time, adding new features such as xtrabackup support non-innodb table backup, specify--safe-slave-backup, increase backup consistency, the most important point is to support 5.7 backup, 2.2 is not able to backup 5.7 version.

So we decided to update our percona pt tools and backup software to the latest version. This article mainly documents the problems we encountered during the use of version 2.4 and some of the previous changes.

II. Problems and Differences

2.1 backup-my.cnf file

innobackup version 2.4 has several more parameters than previous versions

2.2 version of the content

[mysqld]

innodb_checksum_algorithm=innodb

innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:12M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=1073741824

innodb_page_size=16384

innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

2.4 version of the content

[mysqld]

innodb_checksum_algorithm=innodb

innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:12M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=1073741824

innodb_page_size=16384

innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

server_id=0 # 2.4 New parameter

redo_log_version=0 # 2.4 New parameter

innodb_fast_checksum=false # 2.4 New parameter

Here emphasize innodb_fast_checksum, after applay log depends on backup-my.cnf to start MySQL 5.6 is not able to recognize this parameter, resulting in startup failure. [ERROR] mysqld: unknown variable 'innodb_fast_checksum=0'

Take a look at 2014 related bug statements

"Or maybe a separate feature request should be opened to copy the whole my.cnf to the backup directory as well. I will leave that up to others to decide. "

It's been 3 years, and nothing's worked out yet.

The discussion elsewhere can actually be closed directly.

https://dba.stackexchange.com/questions/6386/is-there-any-reason-not-to-use-percona-innodb-fast-checksum

2.2 scene

Due to historical reasons, we still have some databases that are based on mysqld_multi for single machine multiple instances. There are two types of configuration files for this single-machine multi-instance configuration: /etc/my.cnf and/path/my.multi.cnf. Instance level personality parameters are configured in the my.multi.cnf file. such as

[mysqld_multi]

mysqld=/usr/bin/mysqld_safe

mysqladmin=/usr/bin/mysqladmin

user=mysql

log=/data/multi.log

[3306]

port = 3306

datadir=/data/my3306

socket=/data/my3306/mysql.sock

user=mysql

pid-file=/data/my3306/mysql.pid

log=/data/my3306/mysqld.log

[3307]

port = 3307

datadir=/data/my3307

socket=/data/my3307/mysql.sock

user=mysql

pid-file=/data/my3307/mysql.pid

log=/data/my3307/mysqld.log

innobackup 2.4 reads/etc/my.cnf during backup, and fails if server_id is not configured in the file. If there is no/etc/my.cnf, it will get my.cnf instead of my.multi.cnf of database instance configuration.

innobackupex: [ERROR] /usr/bin/innobackupex: Empty value for 'server-id' specified

The solution goes back to the old version.

2.3 Changes in Backup Set File Content

Our backup commands are as follows:

/usr/bin/innobackupex --socket=/srv/my_3344/mysqld.sock --user=root --password= --no-timestamp --slave-info --rsync --compress --compress-threads=2 --parallel=1 /data/backup/rac1_3344/full/bk20170827105656 >/data/logs/zandb_agent/backup/rac1_3344_bk20170827105656.log 2>&1

Using the compress feature, version 2.2 backup sets compress database-related data files

2.4 Version of Backup Set File

The impact on automated backup systems is the need to adjust the steps for reading backup-my.cnf, which must be read after decompression.

About "how to solve the problem encountered in MySQL using innobackup 2.4" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.