3

Possible Duplicate:
multiple file systems for mysql

hi,

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

Thanks, yosef

Community
  • 1
Ben
  • 775
  • 4
  • 9
  • 19

2 Answers2

5

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.

Insyte
  • 9,394
  • 3
  • 28
  • 45
  • 1
    can I install directly on 2ndary location instead of moving? – MD. Nazmul Kibria Jan 09 '17 at 05:42
  • 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. – Insyte Jan 31 '17 at 17:39
  • 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 https://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. – FGM Mar 12 '17 at 17:45
  • would there be a 2018 solution to this answer? It appears that this solution is good, but just a little outdated. Thanks! – klewis Apr 04 '18 at 21:32
  • 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. – Rekamanon Sep 09 '20 at 17:00
2

See multiple file systems for mysql

Community
  • 1
Yves Junqueira
  • 691
  • 4
  • 8
  • Whilst this may theoretically answer the question, [it would be preferable](http://meta.stackexchange.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Mark Henderson Sep 25 '12 at 04:39