Save mysql data in a separate directory

2016.05.26

My name is Ito and I am an infrastructure engineer

The location where MySQL data is stored should be decided during the design phase, but
there are still times when the data increases and you need to move it to another directory.

I would like to introduce the steps you need to take in such a situation!

Actual procedure

First of all, stop the MySQL process

# /etc/init.d/mysql stop

Next, set up the config file

Where is the data stored?
Socket file location
Client-side socket file location

These are the three parts you need to set up

# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql (where to save) socket=/var/lib/mysql/mysql.sock (where to put the socket file) [client] socket=/var/lib/mysql/mysql.sock

Move the original data

# cp -pR /var/lib/mysql/ /path/to/datadir

Now, let's start the MySQL process and log in

# /etc/init.d/mysql start # mysql -uroot -phogehoge login to mysql prompt Warning: Using a password on the command line interface can be insecure. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) /etc/init.d/mysqld get_mysql_option mysqld datadir "/var/lib/mysql"

That's not good!!!

The problem is in the startup script.
You need to change the MySQL data directory specification in the startup script.

# vi /etc/init.d/mysqld get_mysql_option mysqld datadir "/var/lib/mysql" ↓ get_mysql_option mysqld datadir "/path/to/datadir"

Restart the computer and check that there are no problems.
Then check if the data storage location has been changed.

mysql> show variables like 'datadir'; +-------------------+-------------------+ | Variable_name | Value | +-------------------+-------------------+ | datadir | /path/to/datadir/ | +-------------------+-------------------+ 1 row in set (0.01 sec)

It looks like the data storage location has been changed successfully!
Happily ever after.

I have written other articles about MySQL, so please take a look at these