How to solve the problem that MySQL fails to start after moving the data directory

Background Overview

Since the MySQL data directory was placed in the root directory when installing the database, the storage space is now insufficient. I want to move the data directory to another directory through mv, but the data directory is moved to After other data directories, starting the database fails.

Problem Reproduction

This test is based on MySQL 8.0.31

1. Close the database

mysql> shutdown;
Query OK, 0 rows affected (0.02 sec)

2. View the location of the current data directory

shell> pwd
/mysql80

3. Move the entire MySQL data directory to another directory through mv

shell>  mv /mysql80 /data
shell>  cd /data/mysql80/svr
shell>  ln -s mysql-8.0.31-linux-glibc2.12-x86_64 mysql

4. Modify the owner group

shell> chown -R mysql.mysql /data

5. Modify the address of the data directory in the configuration file

shell> sed -i 's#/mysql80#/data/mysql80#g' my5001.cnf

6. Start the database

shell> /data/mysql80/svr/mysql/bin/mysqld_safe \
--defaults-file=/data/mysql80/conf/my5001.cnf --user=mysql &

At this time, the database failed to start, and the error log reported the following error:

mysqld: File '/mysql80/dbdata/data5001/log/binlog .000012' not found (OS errno 2 - No such file or directory)
2023-02-27T10:38:09.240576 08:00 0 [ERROR] [MY-010958] [Server] Could not open log file.
2023-02-27T10:38:09.240657 08:00 0 [ERROR] [MY-010041] [Server] Can't init tc log
2023-02-27T10:38:09.240718 08:00 0 [ERROR] ] [MY-010119] [Server] Aborting
2023-02-27T10:38:10.548605 08:00 0 [System] [MY-010910] [Server] /data/mysql80/svr/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.31) MySQL Community Server - GPL.

The error reported here shows that the binlog file cannot be found, and the error message shows binlog The directory is still the same as before, but the directory in the configuration file has been modified

shell> grep 'log-bin' my5001.cnf 
log-bin=/data/mysql80/dbdata/data5001/log/binlog
log-bin-trust-function-creators

7. Problem solving

Finally, through searching, it was found that the binlog.index file stores each binlogThe absolute path address of the file. The path here is still the previous path. The content is as follows:

shell> cat binlog.index 
/mysql80/dbdata/data5001/log/binlog.000001
/mysql80/dbdata/data5001/log/binlog.000002
/mysql80/dbdata/data5001/log/binlog.000003
/mysql80/dbdata/data5001/log/binlog.000004
/mysql80/dbdata/data5001/log/binlog.000005
/mysql80/dbdata/data5001/log/binlog.000006
/mysql80/dbdata/data5001/log/binlog.000007
/mysql80/dbdata/data5001/log/binlog.000008
/mysql80/dbdata/data5001/log/binlog.000009
/mysql80/dbdata/data5001/log/binlog.000010
/mysql80/dbdata/data5001/log/binlog.000011
/mysql80/dbdata/data5001/log/binlog.000012

Modify the absolute path of binlog in the binlog.index file. Path:

shell> sed -i 's#/mysql80#/data/mysql80#g' binlog.index
shell> cat binlog.index 
/data/mysql80/dbdata/data5001/log/binlog.000001
/data/mysql80/dbdata/data5001/log/binlog.000002
/data/mysql80/dbdata/data5001/log/binlog.000003
/data/mysql80/dbdata/data5001/log/binlog.000004
/data/mysql80/dbdata/data5001/log/binlog.000005
/data/mysql80/dbdata/data5001/log/binlog.000006
/data/mysql80/dbdata/data5001/log/binlog.000007
/data/mysql80/dbdata/data5001/log/binlog.000008
/data/mysql80/dbdata/data5001/log/binlog.000009
/data/mysql80/dbdata/data5001/log/binlog.000010
/data/mysql80/dbdata/data5001/log/binlog.000011
/data/mysql80/dbdata/data5001/log/binlog.000012

8. Start the database

shell> /data/mysql80/svr/mysql/bin/mysqld_safe 
--defaults-file=/data/mysql80/conf/my5001.cnf --user=mysql &

The database started successfully.

9. As a slave node

It should be noted that if the instance is also used as a slave node for other instances, the absolute path of the relay log in the relaylog.index file needs to be set, otherwise it will be reported The following error: Error log error:

2023-02-27T15:56:55.224372 08:00 0 [ERROR] [MY-010599] [Repl] log /mysql80/dbdata/data5002/log/ relaylog.000002 listed in the index, but failed to stat.
2023-02-27T15:56:55.224422 08:00 0 [ERROR] [MY-011059] [Repl] Error counting relay log space.
2023 -02-27T15:56:55.226571 08:00 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table , consider deleting it.
2023-02-27T15:56:55.226622 08:00 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.

An error will also be reported when executing start replica:

# Client error
mysql> start replica;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
# error log error
2023-02-27T15:57:53.858798 08:00 8 [ERROR] [MY-013124] [Repl] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124

10. Problem solving

Modifyrelaylog.index The absolute path of relay log in the file

sed -i 's#/mysql80#/data/mysql80#g' relaylog.index

Restart the database and start master-slave replication

# 重启实例
mysql> restart;
# 启动主从复制
mysql> start replica;

At this time, master-slave replication returns to normal.

The above is the detailed content of How to solve the problem that MySQL fails to start after moving the data directory. For more information, please follow other related articles on the PHP Chinese website!