How can I put mysql databases in different disk partitions (linux - Ubuntu)?

Question

Possible Duplicate:
multiple file systems for mysql

hi,

How can I put mysql databases in different partition (linux - Ubuntu)?

Thanks, yosef

Solution 1

Create the new partition and filesystem. Mount it somewhere. Stop mysql and copy the entire contents of /var/lib/mysql to the new location. Be sure to preserve permissions and ownership.

You now have a few options (all assuming you mounted the new filesystem in /opt/mysql_data:

1. Delete /var/lib/mysql and create a symlink from it to the new directory. For example:

   ln -s /opt/mysql_data /var/lib/mysql
   

2. Create a file in /etc/mysql/conf.d called "local_configs.cnf" and put the following lines in that file:

   [mysqld]
   datadir=/opt/mysql_data
   

3. After the data is copied, mount the new filesystem directly on /var/lib/mysql.

Whichever path you take, when you start mysql back up again it will be using the new location.

Solution 2

See multiple file systems for mysql

Comments

[MrGigu]

Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.

[MD. Nazmul Kibria]

can I install directly on 2ndary location instead of moving?

[Return_Of_The_Archons]

If you build your own mysql package, sure. Some package managers may have hooks for modifying this, but I haven't looked into it. The easiest would be to create /var/lib/mysql and mount a new device on it prior to beginning the install.

[FGM]

On some distributions, including Ubuntu 16.04 LTS, AppArmor will get in your way. This will be visible using journalctl -xe which will give Denied messages. To avoid this, declare your change to AppArmor, as described at blogs.oracle.com/jsmyth/entry/apparmor_and_mysql . In short, to put it on /data, add two lines to etc/apparmor.d/local/usr.sbin.mysqld (Cannot put them in a comment since it only supports inline markdown), the reload apparmor.

[klewis]

would there be a 2018 solution to this answer? It appears that this solution is good, but just a little outdated. Thanks!

[Rekamanon]

This solution worked for me, specifically option #2. I also had to run sudo chown -R mysql /var/lib/mysql, sudo chgrp -R mysql /var/lib/mysql, and sudo chmod 755 /var/lib/mysql to get the permissions right, otherwise systemctl was failing to start the service.