[Osaka/Yokohama/Tokushima] Looking for infrastructure/server side engineers!

[Deployed by over 500 companies] AWS construction, operation, maintenance, and monitoring services

[Successor to CentOS] AlmaLinux OS server construction/migration service

[For WordPress only] Cloud server “Web Speed”

Save mysql data in a separate directory

2016.05.26

Server construction/operation

My name is Ito and I am an infrastructure engineer.

I think the storage location of MySQL data should be decided at the design stage, but
even then, there are times when data increases and you need to move it to a different directory.

I would like to introduce the necessary steps in such a case!

actual steps

First of all, stop the MySQL process.

1	`# /etc/init.d/mysql stop`

Next, set the config file.

Data storage location
socket file location
Client-side socket file location

These three items are required for setup.

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

Move the original data.

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

Now, let's run 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"

Don't worry! ! !

The real problem is 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"

Start it again and check if it's OK.
Next, check if the data storage destination 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 destination has been successfully changed!
Congratulations!

I have written other articles about MySQL, so them out as well.

If you found this article helpful , please give it a like!