How to change MySQL data directory

Information managed by the MySQL server is stored under a directory known as the data directory. The following list briefly describes the items typically found in the data directory, with cross references for additional information:

  • Data directory subdirectories. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server. All MySQL installations have certain standard databases:
    • The mysql directory corresponds to the mysql system schema, which contains information required by the MySQL server as it runs. This database contains data dictionary tables and system tables.
    • The performance_schema directory corresponds to the Performance Schema, which provides information used to inspect the internal execution of the server at runtime.
    • The sys directory corresponds to the sys schema, which provides a set of objects to help interpret Performance Schema information more easily.
    • The ndbinfo directory corresponds to the ndbinfo database that stores information specific to NDB Cluster (present only for installations built to include NDB Cluster).

Here is the steps to change Data directory in MySQL

  1. Stop MySQL using the following command:sudo /etc/init.d/mysql stop
  2. Copy the existing data directory (default located in /var/lib/mysql) using the following command:sudo cp -R -p /var/lib/mysql /newpath
  3. edit the MySQL configuration file with the following command:sudo gedit /etc/mysql/my.cnf # or perhaps /etc/mysql/mysql.conf.d/mysqld.cnf
  4. Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.
  5. Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path.
  6. Save and close the file
  7. Restart MySQL with the command:sudo /etc/init.d/mysql restart
  8. Now login to MySQL and you can access the same databases you had before.

Leave a Reply