[Mysql Case] Failedtoopenlog -- datadir physical migration error

When migrating data from 1.1.1.mysql5.6.14 to datadir, an error is reported. [environment description] perconamysql5.6.14 is installed on machine A, the database is stopped and started normally, the datadir path is pathA, and application database initialization has been performed, then shut down the database instance and put its datadir and etcmy. migrate cnf to pe on another machine B

When migrating data from 1.1.1.mysql5.6.14 to datadir, an error is reported. [environment description] perconamysql 5.6.14 is installed on machine A, the database is stopped and started normally, the datadir path is pathA, and application database initialization has been performed, then shut down the database instance and put its datadir and/etc/my. migrate cnf to pe on another machine B

1.1.1. An error is reported during datadir migration of mysql5.6.14.

[Environment description]

Perconamysql 5.6.14 is installed on machine A. When the database is stopped and started normally, the datadir path is pathA and application database initialization has been performed. Then, the database instance is closed, set its datadir and/etc/my. cnf is migrated to percona mysql 5.6.14 on another machine B, and the migrated datadir path is changed to pathB. when mysql service is started, the startup fails.

Procedure:

1) Stop mysql on machine.

Service mysql stop

2) package mysql datadir (path pathA) and my. cnf on machine A, transfer them to machine B, and decompress datadir to pathB.

3) install percona mysql 5.6.14 on machine B

4) use my. cnf transmitted by machine A to overwrite/etc/my. cnf of machine B.

5) change the datadir path in/etc/my. cnf of machine B to pathB.

6) run service mysql start on machine B to start the mysql service.

7) an error occurs when startup fails.

[Mysql Error]

Servicemysql start:

Starting MySQL (Percona Server) ...... Theserver quit without [FAILED] ng PID file (/home/mysql_3306_bak/mysql. pid ).

Error in error log:

/Usr/sbin/mysqld: File '/home/mysql_3306/mysql-bin.000003' not found (Errcode: 2-No such file or directory)

22:26:47 27048 [ERROR] Failed toopen log (file '/home/mysql_3306/mysql-bin.000003', errno 2)

22:26:47 27048 [ERROR] cocould notopen log file

22:26:47 27048 [ERROR] Can 'tinit tc log

22:26:47 27048 [ERROR] Aborting

22:26:47 27048 [Note] Binlog end

22:26:47 27048 [Note] Shuttingdown plugin 'partition'

I have modified all the paths in my. cnf configuration file, but Mysql still says that the '/home/mysql_3306/mysql-bin.000003' path file is not found, it looks strange from the error.

[Cause]

Mysql error message: the binlog cannot be found because the binlog is configured in my. cnf:

Log-bin =/home/mysql_3306/mysql-bin

Log-bin-index =/home/mysql_3306/bin-index

Mysql maintains the index list of log-bin in the file specified by the log-bin-index parameter, and records it in an absolute path:

/Home/mysql_3306? Mysql-bin.000001

/Home/mysql_3306? Mysql-bin.000002

/Home/mysql_3306? Mysql-bin.000003

Although/etc/my. all paths in cnf are modified correctly. However, when mysql is started, it reads log-bin-index to find the log file, the file to be searched is still in the specified location on machine A, so mysql service fails to start.

[Solution]

Manually modify the binary log index file specified by log-bin-index, modify all log-bin paths in the file, specify the binary logs under the current datadir, and then start the mysql service, the startup is successful and the problem is resolved.

[Problem thinking]

Whatis log-bin-index paramter?

The Mysql official manual states that "if you have not. with the log-bin-index parameter specified in cnf, mysql automatically creates a binary index file named after the host_name-bin.index (Experimental Proof is mysql-bin.index )".

Therefore, try to remove the log-bin-index parameter configured in my. cnf, and then start the mysql service. At this time, the mysql service starts normally. view the log-bin-index file:

# Cat mysql-bin.index

/Home/mysql_3306_bak/mysql-bin.000006

We found that mysql automatically created a binary index file named "mysql-bin.index" that contains only the binary file path information that was regenerated at startup.

At this point, mysql only knows the path information of the binary file generated at the startup, which means that mysql has lost all the log files before 000006. We will perform the following test:

Run the flush logs command to allow mysql to fl several binary logs;

# Cat mysql-bin.index

/Home/mysql_3306_bak/mysql-bin.000006

/Home/mysql_3306_bak/mysql-bin.000007

/Home/mysql_3306_bak/mysql-bin.000008

Then, run the purgebinary logs to 'mysql-bin.000004 'command:

> Purge binary logs to 'mysql-bin.000004 ';

ERROR1373 (HY000): Target log not found in binlog index

At this time, mysql prompts that the 000004 binary log file cannot be found. Next, try to delete the 000006 binary log file:

> Purge binary logs to 'mysql-bin.000007 ';

QueryOK, 0 rows affected( 0.03 sec)

View the mysql-bin.index binary log index file:

# Cat mysql-bin.index

/Home/mysql_3306_bak/mysql-bin.000007

/Home/mysql_3306_bak/mysql-bin.000008

View binary log files:

# Ls-ltr mysql-bin.00000 *

Mysql-bin.000004

Mysql-bin.000001

Mysql-bin.000002

Mysql-bin.000003

Mysql-bin.000005

Mysql-bin.000007

Mysql-bin.000008

Mysql has completely deleted the binary log file numbered 000006.

Next, we try to fool mysql and configure a binary log file pointing to the Virtual Machine path:

# Cat mysql-bin.index

// Tmp/mysql-bin.0000005

/Home/mysql_3306_bak/mysql-bin.000007

/Home/mysql_3306_bak/mysql-bin.000008

In mysql, try to delete the log before 000007:

> Purge binary logs to 'mysql-bin.000007 ';

ERROR 29 (HY000): File '/tmp/mysql-bin.0000005' not found (Errcode: 2-No such file or directory)

Mysql finds that the log file does not exist and reports an error when reading the log-bin-index log index file to delete the log;

We are trying to restart mysql to determine how it reads and loads the log index file of log-bin-index and the binary log file specified in the index file:

# Service mysql stop

Shutting down MySQL (PerconaServer)... [OK]

# Service mysql start

Starting MySQL (PerconaServer)... [OK]

View error. log:

2014-04-2600: 53: 28 7f752cddc700 InnoDB: Buffer pool (s) load completed at 140426 0:53:28

^ G/usr/sbin/mysqld: File '/tmp/mysql-bin.0000005' not found (Errcode: 2-No such file or directory)

2014-04-2600: 53: 28 16723 [ERROR] Failed to open log (file '/tmp/mysql-bin.0000005', errno2)

2014-04-2600: 53: 28 16723 [ERROR] cocould not open log file

[Summary]

Although the mysql error log contains error information, mysql is still successfully started, that is, mysql reads the log-bin-index file during the startup process, then, the system will determine whether the specified log-binfile exists. In this case, we can know that only when the log-bin specified in the last path does not exist, mysql service will interrupt the start operation, that is, the start fails.

[Sum Up]

1) if binlog is not enabled for mysql, this problem will not occur;

2) If mysql enables binlog and the Datadir path is modified during the physical migration of datadir, this problem occurs, you can edit or delete the path of the log-bin-index file to repair the log-binfile, and then start the mysql service;

3) To avoid this problem, do not change the datadir path during datadir migration;