Case of failed Toopen log--datadir physical migration error in MySQL

1.1.1. mysql5.6.14 encountered an error while migrating datadir

"Environment description"

The Perconamysql 5.6.14 is installed on machine A, the database is deactivated, the DataDir path is Patha, and the initialization of the application database has been done, and then the database instance is closed, DataDir and/etc/ My.cnf migrated to the Percona MySQL 5.6.14 on another machine B, the migrated DataDir path was modified to a path PATHB, and a problem was encountered when starting the MySQL service and failed to start.

Operation Steps:

1 shut down MySQL on machine a

Service MySQL Stop

2 to machine a MySQL datadir (path Patha) and my.cnf do packaging, transfer to Machine B, and datadir extract to PATHB

3 Install Percona MySQL 5.6.14 on machine b

4 using machine A to transmit over the my.cnf covering machine B/etc/my.cnf

5) Modify the/etc/my.cnf of Machine B datadir path is PATHB

6 Perform service on machine b mysql start start MySQL service

7) Failed to start, error occurred

"MySQL Error"

Error at startup Servicemysql start:

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

Errors in the error log:

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

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

2014-04-25 22:26:47 27048 [ERROR] could notopen log file

2014-04-25 22:26:47 27048 [ERROR] Can ' tinit TC Log

2014-04-25 22:26:47 27048 [ERROR] Aborting

2014-04-25 22:26:47 27048 [note] Binlog end

2014-04-25 22:26:47 27048 [note] shuttingdown plugin ' partition '

All the paths in the MY.CNF configuration file have been modified, but MySQL still says it can't find the '/home/mysql_3306/mysql-bin.000003 ' path file, and it looks weird from the error.

"Cause of the problem"

The MySQL error prompt could not find binlog because it was configured in MY.CNF:

log-bin=/home/mysql_3306/mysql-bin

log-bin-index=/home/mysql_3306/bin-index

MySQL maintains the Log-bin index list in the file specified by the Log-bin-index parameter, and it is logged as an absolute path:

/home/mysql_3306?mysql-bin.000001

/home/mysql_3306?mysql-bin.000002

/home/mysql_3306?mysql-bin.000003

Although all the paths in/ETC/MY.CNF have been modified correctly, the MySQL service failed to start by reading Log-bin-index to find the Log-bin log file, or the location specified on machine A.

"Workaround"

Manually modify the binary log index file specified by Log-bin-index, modify all log-bin paths inside, specify binary log under current DataDir, and then try to start the MySQL service, start the success, solve the problem.

"Question thinking"

Whatis log-bin-index paramter?

The MySQL official manual explains that "If you do not configure the Log-bin-index parameter designation in MY.CNF, MySQL automatically creates a host_name-bin.index-named binary index file (experimentally proven to be mysql-bin.index)."

So, try to remove the Log-bin-index parameters configured in the MY.CNF, and then start the MySQL service, at which time the MySQL service starts normally, viewing 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," and that the file contains only the binary path information that was regenerated at startup.

This column more highlights: http://www.bianceng.cn/database/MySQL/

At this point, MySQL only knows the binary file path information generated at this startup, then it means that MySQL lost all the log files before number 000006, we do the following test:

Execute flush logs command, let MySQL again brush out several binary log;

#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, execute 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 point, the MySQL prompt could not find the No. 000004 binary log file, and then try to delete the No. 000006 binary log file:

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

Queryok, 0 rows affected (0.03 sec)

To 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

To view the binary log file:

#ls-ltr mysql-bin.00000*

mysql-bin.000004

mysql-bin.000001

mysql-bin.000002