Select Page

Advent

Databases develop through the years, occasionally outgrowing the gap at the record gadget. You’ll be able to additionally run into I/O competition after they’re positioned at the identical partition as the remainder of the working gadget. RAID, community block garage, and different gadgets can be offering redundancy and different fascinating options. Whether or not you’re including extra space, comparing tactics to optimize efficiency, or having a look to make the most of different garage options, this instructional will information you thru relocating MySQL’s information listing.

Necessities

To finish this information, you are going to want:

On this instance, we’re shifting the information to a block garage instrument fixed at /mnt/volume-nyc1-01. You’ll be able to learn to set one up within the How To Use Block Storage on DigitalOcean information.

It doesn’t matter what underlying garage you employ, this information assist you to transfer the information listing to a brand new location.

Step 1 — Transferring the MySQL Knowledge Listing

To organize for shifting MySQL’s information listing, let’s check the present location through beginning an interactive MySQL consultation the usage of the executive credentials.

When brought on, provide the MySQL root password. Then from the MySQL instructed, make a choice the information listing:

Output

+-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec)

This output confirms that MySQL is configured to make use of the default information listing, /var/lib/mysql/, in order that’s the listing we want to transfer. As soon as you have got showed this, sort go out to go away the track.

To verify the integrity of the information, we’ll close down MySQL prior to we in fact make adjustments to the information listing:

  • sudo systemctl prevent mysql

systemctl does not show the result of all provider control instructions, so if you wish to be sure to’ve succeeded, use the next command:

  • sudo systemctl standing mysql

You’ll be able to ensure it’s close down if the general line of the output tells you the server is stopped:

Output

. . . Jul 18 11:24:20 ubuntu-512mb-nyc1-01 systemd[1]: Stopped MySQL Group Server.

Now that the server is close down, we’ll reproduction the present database listing to the brand new location with rsync. The use of the -a flag preserves the permissions and different listing homes, whilst-v supplies verbose output so you’ll be able to practice the development.

Notice: Make sure that there is not any trailing slash at the listing, that could be added in the event you use tab of entirety. When there’s a trailing slash, rsync will sell off the contents of the listing into the mount level as a substitute of shifting it right into a containing mysql listing:

  • sudo rsync -av /var/lib/mysql /mnt/volume-nyc1-01

As soon as the rsync is whole, rename the present folder with a .bak extension and stay it till we’ve showed the transfer used to be a hit. Via re-naming it, we’ll steer clear of confusion that would stand up from information in each the brand new and the outdated location:

  • sudo mv /var/lib/mysql /var/lib/mysql.bak

Now we’re in a position to show our consideration to configuration.

Step 2 — Pointing to the New Knowledge Location

MySQL has a number of tactics to override configuration values. Via default, the datadir is ready to /var/lib/mysql within the /and so on/mysql/mysql.conf.d/mysqld.cnf record. Edit this record to mirror the brand new information listing:

  • sudo nano /and so on/mysql/mysql.conf.d/mysqld.cnf

In finding the road that starts with datadir= and alter the trail which follows to mirror the brand new location.

In our case, the up to date record seems like the output beneath:

/and so on/mysql/mysql.conf.d/mysqld.cnf

. . .
datadir=/mnt/volume-nyc1-01/mysql
. . .

This turns out like the correct time to convey up MySQL once more, however there’s yet one more factor to configure prior to we will be able to do this effectively.

Step 3 — Configuring AppArmor Get right of entry to Regulate Regulations

We’ll want to inform AppArmor to let MySQL write to the brand new listing through growing an alias between the default listing and the brand new location. To try this, edit the AppArmor alias record:

  • sudo nano /and so on/apparmor.d/tunables/alias

On the backside of the record, upload the next alias rule:

/and so on/apparmor.d/tunables/alias

. . .
alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/,
. . .

For the adjustments to take impact, restart AppArmor:

  • sudo systemctl restart apparmor

Notice: In case you skipped the AppArmor configuration step, you could run into the next error message:

Output

Activity for mysql.provider failed for the reason that keep an eye on procedure exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for main points.

The output from each systemctl and journalctl concludes with:

Output

Jul 18 11:03:24 ubuntu-512mb-nyc1-01 systemd[1]: mysql.provider: Major procedure exited, code=exited, standing=1/FAILURE

For the reason that messages don’t make an particular connection between AppArmor and the information listing, this mistake can take a while to determine.

Step 4 — Restarting MySQL

The next move is to start out MySQL, however in the event you do, you’ll run into any other error. This time, as a substitute of an AppArmor factor, the mistake occurs for the reason that script mysql-systemd-start assessments for the lifestyles of both a listing, -d, or a symbolic hyperlink, -L, that fits two default paths. It fails if they are now not discovered:

/usr/percentage/mysql/mysql-systemd-start

. . .
if [ ! -d /var/lib/mysql ] && [ ! -L /var/lib/mysql ]; then
 echo "MySQL data dir not found at /var/lib/mysql. Please create one."
 go out 1
fi

if [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then
 echo "MySQL system database not found. Please run mysql_install_db tool."
 go out 1
fi

. . .

Since we want those to start out the server, we can create the minimum listing construction to cross the script’s atmosphere take a look at.

  • sudo mkdir /var/lib/mysql/mysql -p

Now we are in a position to start out MySQL.

  • sudo systemctl beginning mysql
  • sudo systemctl standing mysql

To ensure that the brand new information listing is certainly in use, beginning the MySQL track.

Take a look at the price for the information listing once more:

Output

+----------------------------+ | @@datadir | +----------------------------+ | /mnt/volume-nyc1-01/mysql/ | +----------------------------+ 1 row in set (0.01 sec)

Now that you simply’ve restarted MySQL and showed that it’s the usage of the brand new location, take the chance to be sure that your database is totally purposeful. If you’ve verified the integrity of any current information, you’ll be able to take away the backup information listing:

  • sudo rm -Rf /var/lib/mysql.bak

Restart MySQL one ultimate time to make sure that it really works as anticipated:

  • sudo systemctl restart mysql
  • sudo systemctl standing mysql

Conclusion

On this instructional, we’ve moved MySQL’s information listing to a brand new location and up to date Ubuntu’s AppArmor ACLs to house the adjustment. Even though we have been the usage of a Block Garage instrument, the directions right here will have to be appropriate for redefining the site of the information listing irrespective of the underlying era.

For extra on managing MySQL’s information directories, see those sections within the reputable MySQL documentation: