• Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us
TecAdmin
Menu
  • Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us

How to Change default MySQL Data Directory in Linux

Written by Rahul, Updated on May 1, 2014

As a System Administrator I don’t prefer to use / directory to store MySQL data files. Each time when I install MySQL for a production server, I tried to use secondary disk for storing application and database file. In that way I always change the default data directory of MySQL to secondary disk mounted on system.

Mostly MySQL uses /var/lib/mysql directory as default data directory for Linux based systems. Which are going to change to another location in this article.

linux-mysql-banner

Instruction’s to Change Default MySQL Data Directory:

Follow the below steps to make all the changes. In some cases service name, default data directory or MySQL configuration file path change. So use all the command as per you system settings.

1. Stop MySQL

Before making any changes, first make sure to stop mysql service

# service mysqld stop

2. Change Data Directory

Now copy default MySQL data directory (/var/lib/mysql) to other location as per your requirement. Also set the required MySQL ownership on new directory location. As per below command, we are relocating data directory to /data/mysql.

# cp -rap /var/lib/mysql /data/mysql
# chown mysql.mysql /data/mysql

Now edit MySQL default configuration file /etc/my.cnf and update values of datadir and socket variable.

Change From:
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Change To: 
datadir=/data/mysql
socket=/data/mysql/mysql.sock

3. Start MySQL

After making all above changes. finally start MySQL service. Now it will use new data directory path

# service mysqld start

This article has been tested with CentOS 6.5 with MySQL 5.5.37. If you face any issue during MySQL startup check mysql log file /var/log/mysqld.log for any errors.

Share it!
Share on Facebook
Share on Twitter
Share on LinkedIn
Share on Reddit
Share on Tumblr
Share on Whatsapp
Rahul
Rahul
Connect on Facebook Connect on Twitter

I, Rahul Kumar am the founder and chief editor of TecAdmin.net. I am a Red Hat Certified Engineer (RHCE) and working as an IT professional since 2009..

11 Comments

  1. Avatar Rosel Reply
    August 7, 2018 at 6:46 pm

    After changing data directory, is it necessary to check used mysql ports before restart services?

  2. Avatar altamash Reply
    March 23, 2018 at 9:04 am

    hi ,
    i did the above procedure in RHEL 7 , the files were copied also i changed the ownership of mysql data directory which has the same permission as the original file but still getting an error when i restart the mysql service
    stating “Job for mysqld.service failed. See ‘systemctl status mysqld.service’ and ‘journalctl -xn’ for details”

    please help thanks!

    • Rahul Rahul K. Reply
      April 5, 2018 at 3:28 am

      What is output of “systemctl status mysqld.service”?

  3. Avatar KaZuN Reply
    February 29, 2016 at 11:15 am

    Is any idea how its work on centos 7.cause my mysqld cannot start after done these change.thanks.

  4. Avatar Jasper vS Reply
    December 22, 2015 at 5:05 pm

    Is there any reason to move the .sock file?

    I mean it can be kept in the original location, right?

  5. Avatar John Reply
    November 25, 2014 at 9:37 am

    Hi,
    Thanks for the article !
    I’d like to add something because I was facing an issue while trying to connect to the database after I made the changes listed above.
    When I tried to connect to the database with the mysql client, I had this error :
    “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)”

    The problem where that the mysql client is using by default the /var/lib/mysql/mysql.sock socket.
    In the /etc/my.cnf file, I just added a client section specifying the socket :

    [client]
    socket=/data/mysql/mysql.sock

    Hope this will help,

    Cheers !

    • Avatar Brenda Yin Reply
      April 10, 2015 at 3:28 am

      John,

      You have to relink the sock file, which is under the new data directory.

      Here is the sample:

      ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock

    • Avatar Razor Reply
      April 28, 2019 at 6:50 pm

      Thank you it worked for me

  6. Avatar Darius Reply
    November 12, 2014 at 3:36 am

    Shouldn’t the Change To: datadir=/data/mysql in this example?

    • Rahul Rahul Reply
      November 12, 2014 at 9:47 am

      Hi Darius,

      Yes, We need to change it. We have corrected typo.

  7. Avatar sajith Reply
    October 4, 2014 at 2:11 am

    I tried to do the same in CENTOS 6.4 and https://www.centos.org/forums/viewtopic.php?t=6732 this link worked perfectly!

Leave a Reply Cancel reply

Popular Posts

  • How To Install Python 3.9 on Ubuntu 20.04 5
  • How to Install Python 3.9 on CentOS/RHEL 7 & Fedora 32/31 0
  • How To Install VNC Server on Ubuntu 20.04 1
  • How To Install NVM on macOS with Homebrew 0
  • (Solved) apt-add-repository command not found – Ubuntu & Debian 0
© 2013-2020 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy