Databases

How to Change MySQL Data Directory in Linux

Change MySQL Data Directory
Change MySQL Data Directory

Change MySQL Data Directory in Linux

This post discussing how to change MySQL Data Directory. As everyone knows the default MySQL data directory is /var/lib/mysql. You can change it using the following steps.

1. Find out where the databases are

By default, the databases (as a file) are stored in “/var/lib/mysql/” on Linux. Verify the current data directory location using any one of the following commands.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysqladmin var -u root -p | grep datadir
mysqladmin var -u root -p | grep datadir
mysqladmin var -u root -p | grep datadir

Sample Output:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
root@server [~]# mysqladmin var -u root -p | grep datadir
Enter password:
| datadir | /var/lib/mysql/
root@server [~]# mysqladmin var -u root -p | grep datadir Enter password: | datadir | /var/lib/mysql/
root@server [~]# mysqladmin var -u root -p | grep datadir
Enter password:

| datadir | /var/lib/mysql/

OR login to interactive MySQL session with the below command:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql -u root -p
mysql -u root -p
mysql -u root -p

Once logged in, run the following command on MySQL interactive session.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
select @@datadir;
select @@datadir;
select @@datadir;

Sample Output:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> select @@datadir; +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec) mysql>
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

mysql>

2. Stop MySQL Service

I am planning to move MySQL Data Directory to /home/mysql. Before making the changes, you need to make sure that the MySQL service is stopped.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
service mysqld stop
service mysqld stop
service mysqld stop

3. Change MySQL Data Directory

Now move MySQL data directory to the desired location and make sure that the directory ownership is set to mysql.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
cp -pr /var/lib/mysql /home/mysql
chown -R mysql.mysql /home/mysql
cp -pr /var/lib/mysql /home/mysql chown -R mysql.mysql /home/mysql
cp -pr /var/lib/mysql /home/mysql
chown -R mysql.mysql /home/mysql

4. Edit MySQL Configuration

Once done, edit MySQL configuration file /etc/my.cnf using your favourite editor. I am using VIM here.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
vim /etc/my.cnf
vim /etc/my.cnf
vim /etc/my.cnf

and update the following variables.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
datadir=/home/mysql
socket=/home/mysql/mysql.sock
datadir=/home/mysql socket=/home/mysql/mysql.sock
datadir=/home/mysql
socket=/home/mysql/mysql.sock

5. Start MySQL Service

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
service mysqld start
service mysqld start
service mysqld start

In this way, you can change MySQL data directory in Linux. Please check the MySQL error log if you encountered any problems.

That’s it!

Also Read:

If you like this post and wish to receive more articles from us, please like our FB page: GrepItOut

Your suggestions and feedbacks will encourage us and help to improve further, please feel free to write your comments.

For more details on our services, please drop us an E-mail at info@grepitout.com

2 Comments

Click here to post a comment

Topics