Save mysql data in a separate directory

2016.05.26

My name is Ito and I am an infrastructure engineer

While the location for storing MySQL data should ideally be decided during the design phase,
there are times when the data grows and needs to be moved to a different 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 lies in the startup script.
You need to change the specification of the MySQL data directory within 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 application and confirm that there are no problems.
Finally, check if the data save 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 save location has been successfully changed!
All's well that ends well.

I've written other articles about MySQL, so.please check those out as well