How to Change MySQL Data Directory

From IHRIS Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

The need for changing the default MySQL data files location comes from the fact that MySQL data files grow to be very large over time and as the number of MySQL databases in that server increases. Since by default these files are stored in the same partition the operating system is running, you may experience the system becoming very slow and of course you will prompted to increase your storage size.

This being the case, you can decide to add another hard disk or in cases where you have multiple partitions, you can set MySQL data files to be stored in a dedicated partition or hard disk. In whatever case, the steps to changing the path will be the same.

For this case, we hosting the system on an Amazon Cloud Instance and we have an extra storage partition set at /mnt/data and this is where we will pointing MySQL to write its files to.

Procedures

  1. It is recommended you stop MySQL before initializing this process. Use the following command
    sudo service mysql stop
  2. After MySQL is stopped, copy the data directory to the location you want to set it to. (For this case we are going to put it in /mnt/data directory). The argument -p is for preserving ownership and permissions, otherwise you will have to change ownership and permissions separately)
    sudo cp -Rap /var/lib/mysql /mnt/data/
  3. Then we need to edit the MySQL configuration file to set the new path. For this open the MySQL configuration file:
    • Open the file
      sudo pico /etc/mysql/my.cnf
    • Then look for the line that reads datadir = /var/lib/mysql, and change the path (/var/lib/mysql) to the new data directory so that it reads.
      datadir = /mnt/data/mysql
    • Save and close the file.
  4. We also need to change the path in AppArmor.
    • Open the file
      sudo pico /etc/apparmor.d/usr.sbin.mysqld
    • Look for lines beginning with /var/lib/mysql and change to /mnt/data/mysql.
    • Save and close the file.
  5. Restart AppArmor and MySQL daemons with the commands:
    • AppArmor
      sudo /etc/init.d/apparmor reload
    • MySQL
      sudo service mysql start

Notes

  • Command to save a file in pico Ctrl + O
  • Command to exit pico: Ctrl + X
  • AppArmor ("Application Armor") is a Linux kernel security module released under the GNU General Public License that allows the system administrator to associate a security profile with each program, which restricts the capabilities of that program. Details here
Retrieved from "https://wiki.ihris.org/mediawiki/index.php?title=How_to_Change_MySQL_Data_Directory&oldid=54720"