Save mysql data in a separate directory

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.

# /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.

# 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.

When I run a mysql command in a script, "Warning: Using a password on the command line interface can be insecure" is displayed