mysql--Failedtoopenlog--datadir物理迁移报错_MySQL
1.1.1. mysql5.6.14的datadir迁移时遇到报错
【环境描述】
在机器A上安装了perconamysql 5.6.14，数据库停启正常，datadir路径为pathA，并且已经做了应用数据库的初始化工作，然后关闭了这个数据库实例，把它的datadir和/etc/my.cnf迁移到另外一台机器B上的percona mysql 5.6.14，迁移后的datadir路径修改成路径pathB，在启动mysql服务的时候遇到问题，启动失败。
操作步骤：
1） 停掉机器A上的mysql
service mysql stop
2） 对机器A上mysql的datadir（路径pathA）和my.cnf做打包，传输到机器B，并把datadir解压到pathB
3） 在机器B上安装percona mysql 5.6.14
4） 使用机器A传输过来的my.cnf覆盖机器B的/etc/my.cnf
5） 修改机器B的/etc/my.cnf中datadir路径为pathB
6） 在机器B上执行service mysql start启动mysql服务
7） 启动失败，发生报错
【mysql报错】
启动时的报错servicemysql start：
Starting MySQL(Percona Server)......Theserver quit without[FAILED]ng PID file(/home/mysql_3306_bak/mysql.pid).
错误日志中的报错：
/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'
已经修改了my.cnf配置文件中所有的路径，但是Mysql仍然说找不到'/home/mysql_3306/mysql-bin.000003'路径的文件，从报错看上去很诡异。
【问题原因】
Mysql报错提示找不到binlog，是由于my.cnf中配置了：
log-bin= /home/mysql_3306/mysql-bin
log-bin-index= /home/mysql_3306/bin-index
mysql会在log-bin-index参数指定的文件中维护log-bin的索引列表，并且它是以绝对路径的方式记录的：
/home/mysql_3306?mysql-bin.000001
/home/mysql_3306?mysql-bin.000002
/home/mysql_3306?mysql-bin.000003
虽然已经把/etc/my.cnf中的所有路径都修改正确了，但是mysql服务在启动时，是通过读取log-bin-index来查找log-bin日志文件的，查找的文件还是在机器A上指定的位置，所以mysql服务启动失败。
【解决方法】
手动修改log-bin-index指定的二进制日志索引文件，修改里面所有log-bin的路径，指定到当前datadir下的二进制日志，然后尝试启动mysql服务，启动成功，问题解决。
【问题思考】
Whatis log-bin-index paramter ?
Mysql官方手册中说明“如果没有在my.cnf中配置log-bin-index参数指定，mysql会自动创建一个以host_name-bin.index命名的二进制索引文件（实验证明是mysql-bin.index）”。
所以，尝试去掉my.cnf中配置的log-bin-index参数，然后启动mysql服务，此时mysql服务正常启动，查看log-bin-index文件：
#cat mysql-bin.index
/home/mysql_3306_bak/mysql-bin.000006
我们发现mysql自动创建了名为“mysql-bin.index”的二进制索引文件，并且文件中只包含在启动时重新生成的二进制文件路径信息。
此时，mysql只知道此次启动时生成的二进制文件路径信息，那么也就意味着此时mysql丢失了编号000006之前的所有日志文件，我们进行如下的测试：
执行flush logs命令，让mysql再刷出来几个二进制日志；
#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
然后，执行purgebinary logs to 'mysql-bin.000004' 命令：
>purge binary logs to 'mysql-bin.000004';
ERROR1373 (HY000): Target log not found in binlog index
此时，mysql提示无法找到000004号二进制日志文件，接下来尝试删除000006号二进制日志文件：
> purge binary logs to'mysql-bin.000007';
QueryOK, 0 rows affected (0.03 sec)
查看mysql-bin.index二进制日志索引文件：
#cat mysql-bin.index
/home/mysql_3306_bak/mysql-bin.000007
/home/mysql_3306_bak/mysql-bin.000008
查看二进制日志文件：
#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已经彻底删除了编号000006的二进制日志文件。
接下来，我们尝试欺骗mysql，配置一个指向虚机路径的二进制日志文件：
#cat mysql-bin.index
/tmp/mysql-bin.0000005
/home/mysql_3306_bak/mysql-bin.000007
/home/mysql_3306_bak/mysql-bin.000008
在mysql中尝试删除编号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在读取log-bin-index日志索引文件删除日志的时候发现日志文件不存在，报错；
我们在尝试重启mysql，来判断它是如何读取和加载log-bin-index日志索引文件 以及索引文件中指定的二进制日志文件的：
# service mysql stop
Shutting down MySQL (PerconaServer)... [ OK ]
# service mysql start
Starting MySQL (PerconaServer).... [ OK ]
查看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] Could not open log file
【总结】
虽然mysql的error日志中有error信息，但是mysql仍然成功启动了，也就是说mysql在启动过程会读取log-bin-index文件，然后也会判断索引指定的log-bin文件是否存在，结合本案例中遇到的情况，可以知道只有当最后一个路径指定的log-bin不存在时，mysql服务才会中断启动操作，即启动失败。
【Sum Up】
1） 如果mysql没有开启binlog，则不会遇到这个问题；
2） 如果mysql开启了binlog，并且在Datadir物理迁移的过程中，修改了datadir的路径，就会遇到这个问题，此时，可以编辑log-bin-index文件修复log-bin文件的路径或者直接删除，然后启动mysql服务；
3） 为了避免遇到这个问题，在进行datadir迁移的时候，尽量不要改变datadir的路径；
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1423
52
1318
25
1268
29
1248
24
iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos
Jul 18, 2024 am 05:48 AM
Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps
How does Hibernate implement polymorphic mapping?
Apr 17, 2024 pm 12:09 PM
Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.
How to handle database connection errors in PHP
Jun 05, 2024 pm 02:16 PM
To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
Detailed tutorial on establishing a database connection using MySQLi in PHP
Jun 04, 2024 pm 01:42 PM
How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())
How to use database callback functions in Golang?
Jun 03, 2024 pm 02:20 PM
Using the database callback function in Golang can achieve: executing custom code after the specified database operation is completed. Add custom behavior through separate functions without writing additional code. Callback functions are available for insert, update, delete, and query operations. You must use the sql.Exec, sql.QueryRow, or sql.Query function to use the callback function.
How to save JSON data to database in Golang?
Jun 06, 2024 am 11:24 AM
JSON data can be saved into a MySQL database by using the gjson library or the json.Unmarshal function. The gjson library provides convenience methods to parse JSON fields, and the json.Unmarshal function requires a target type pointer to unmarshal JSON data. Both methods require preparing SQL statements and performing insert operations to persist the data into the database.
How to connect to remote database using Golang?
Jun 01, 2024 pm 08:31 PM
Through the Go standard library database/sql package, you can connect to remote databases such as MySQL, PostgreSQL or SQLite: create a connection string containing database connection information. Use the sql.Open() function to open a database connection. Perform database operations such as SQL queries and insert operations. Use defer to close the database connection to release resources.
PHP connections to different databases: MySQL, PostgreSQL, Oracle and more
Jun 01, 2024 pm 03:02 PM
PHP database connection guide: MySQL: Install the MySQLi extension and create a connection (servername, username, password, dbname). PostgreSQL: Install the PgSQL extension and create a connection (host, dbname, user, password). Oracle: Install the OracleOCI8 extension and create a connection (servername, username, password). Practical case: Obtain MySQL data, PostgreSQL query, OracleOCI8 update record.
