Content

Introductory

When running websites, the size of the databases may reach our free space on the partition, or we may want to migrate our databases to an SSD drive, for example, for performance optimization purposes. Whatever the reason for this, we can learn from this description how to migrate binaries containing databases to another partition or drive.

Database engines and their versions on Debian

On Debian 8 (Jessie), the default database engine is MySQL 5.5, but there is also the MariaDB 10.0version. And for Debian 9 (Stretch), the MariaDB 10.1 version can be installed by default. Of course you can also install Debian on 9 MySQL, but since this is no longer the default database engine, its installation is different from the traditional package installation, which is covered in another description. 

Whatever is on our server, the following brief tutorial covers both database engines.

Defaults to MySQL / MariaDB

MySQL

The main MySQL configuration file is /etc/mysql/my.cnf. For example, if you look at dwarf-with,

nano /etc/mysql/my.cnf

then you will see a lot of options, but what we are interested in now is the value of "datadir" which is set by default:

[...]
datadir    = /var/lib/mysql
[...]

So this would be the directory for the MySQL binary data files, which we will change later.

MariaDB

MariaDB is a full replacement for MySQL. To maintain compatibility, there is one symbolic link on the same path, but there are only links to directories containing additional configuration files, the contents of which must be loaded when MariaDB starts:

[..]
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

If you follow the bottom of these, you will also get to a file with specific settings, which we will first look at:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

In this file you will find the datadir configuration in the same way:

[...]
datadir    = /var/lib/mysql
[...]

So these are configuration files containing the settings for the two database engines, which we will return to in due course.

Migrate database files

datadir

In the configuration, the directory configured for datadir stores binary files that store tables, indexes, etc. of the databases. First, enter the directory and assess the terrain:

cd /var/lib/mysql
du -ch .

This will tell you the exact size, the minimum space required on the new partition, and so on.

Alternatively, use the following command to check which drive and partition the current storage directory is on:

df -h .

Or all attachment points:

df -h

Based on these, we can find the right location for our database files (larger partition, SSD partition, etc.).

Copy / Sync files

Sync in developer or test environment

If the server is a development or test environment, the speed of uptime and downtime do not matter much, in which case it does not have to be complicated, but can be solved in a few steps:

Shutting down the database server a systemctl command:

systemctl stop mysqld.service

Then copy to the destination of your choice to complete / Var / lib / MySQL below the directory. The Midnight Commander is also perfect for this purpose, allowing you to conveniently copy everything recursively. If you have a large amount of data, you may want to run this operation in the background; in another terminal window.

Then change the path of the datadir to the appropriate one in the configuration file valid for your system, and save the file.

Finally, restart the database server:

systemctl start mysqld.service

And we're done, now our database engine uses the files from the new directory.

Synchronization on a production server

On a live server with high-traffic websites and high availability is a critical consideration, downtime of a few minutes is not allowed either. The operation is preferably done at night, when the traffic on the websites is the lowest.

Step by step, it looks like this:

First, copy or synchronize the database file structure. It is a good idea to use rsync now to configure the switches and check the copy results so that you only have to run your running command at standby time:

rsync -av /var/lib/mysql/ /<kiválasztott célkönyvtár útvonala>/

Switches:

• a: Archive mode, collection of various additional switches. Used in general cases.
• v: It's a verbose mode so we can see where the copy is going.

Then modify the MySQL or MariaDB configuration file to datadir value. So for MySQL:

nano /etc/mysql/my.cnf

And for MariaDB:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

[...]
datadir    = /<kiválasztott adatkönyvtár útvonala>
[...]

You don't have to end / sign here. Let's save it. At this point, it will not take effect and we will not increase the downtime by rewriting this.

Then the database server can be shut down:

systemctl stop mysqld.service

After it has stopped, repeat our rsync command, which will then copy the differences in the directory structure much sooner.

Then start the database:

systemctl start mysqld.service

And that's it. It was essentially the same process here, but we only let the database server stand for a fraction of the time.

Conclusion

Although this description goes a bit longer, it is basically a simple operation that works with both MySQL and MariaDB database engines. So if you need to move the binary files that hold the databases to another directory, you can easily solve the problem.