Familie Lahme
Dinge, die keinen interessieren…..
Familie Lahme

MariaDB, moving databases

In this post I describe the steps I took to move my scattered databases of “WordPress”, “Nextcloud”, “Postfix/Dovecot” and “Horde” to my new dedicated DB server.

Keep in mind, I changed all sets of internal names and IP addresses to some random strings. You need to adopt to your environment. Before you try anything from the below stuff, please read it completely including the resume at the end.

Find here the setup of the base system.

Of course the first step is to install the most current version of MariaDB, so I checked the RePo:

apt-cache search "mariadb"
[...]
mariadb-server - MariaDB database server (metapackage depending on the latest version)
mariadb-server-10.0 - MariaDB database server binaries
mariadb-server-10.3 - MariaDB database server binaries
mariadb-server-10.5 - MariaDB database server binaries
[...]

As I have made some pain full experiences using the generic package, I point now to the specific version:

apt install mariadb-server-10.5

After installation I check the version:

mysql --version

mysql Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnueabihf (armv8l) using EditLine wrapper

…and secure the installation:

sudo mariadb_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n]
Enabled successfully!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Now we need to make sure, that MariaDB is listening to the correct IP:

/etc/mariadb/mariadb.conf.d/50-server.cnf
....
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 123.456.789.012
....

Restart MariaDB and check with netstat -tulpn:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 123.456.789.012:3306 0.0.0.0:* LISTEN 3125/mariadbd

After the basic installation I now create the users for each database and the remote privileges for their respective connection:

mysql -u root

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.5.15-MariaDB-0+deb11u1 Raspbian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

CREATE DATABASE wordpressDB;
CREATE USER 'wordpressDBuser'@'wordpress.contoso.com' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON wordpressDB.* TO "wordpressDBuser"@"wordpress.contoso.com";

CREATE DATABASE nextcloudDB;
CREATE USER 'nextcloudDBUser'@'nextcloud.contoso.com' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON nextcloudDB.* TO "nextcloudDBuser"@"nextcloud.contoso.com";

CREATE DATABASE hordedb;
CREATE USER 'hordeDBuser'@'horde.contoso.com' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON hordedb.* TO "hordeDBuser"@"horde.contoso.com";

CREATE DATABASE postfixadmindb;
CREATE USER 'postfixadminDBuser'@'postfixadmin.contoso.com' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON postfixadmindb.* TO "postfixadminDBuser"@"postfixadmin.contoso.com";

now we need to switch to the application hosts and stream the database to the new server. The following stanzas repeat themself. Each consists of 4 steps:

  1. Stop the related services to prevent changes during switch over. In our case it is always the apache server.
  2. transfer database from source to destination
  3. change the database variable in the applications configuration
  4. start the application services

nextcloud.contoso.com


systemctl stop apache2

mysqldump --lock-tables -h localhost --user=nextcloudDBuser --quick --max_allowed_packet=512M --password='password' nextclouddb | mysql --user=nextcloudDBuser --password='password' -h database.contoso.com nextclouddb

sed -i s/\'dbhost\'\ \=\>\ \'localhost:3306\'\,/\'dbhost\'\ \=\>\ \'database\.contoso\.com\:3306\'\,/ /var/www/config/config.php

systemctl start apache2

wordpress.contoso.com


systemctl stop apache2 

mysqldump --lock-tables -h localhost --user=wordpressDBuser --quick --max_allowed_packet=512M --password='password' wordpressdb | mysql --user=wordpressDBuser --password='password' -h database.contoso.com wordpressdb

sed -i s/define\(\ \'DB_HOST\'\,\ \'localhost\'\ \)\;/define\(\ \'DB_HOST\'\,\ \'database\.contoso\.com\'\ \)\;/ /var/www/wp-config.php

systemctl start apache2

horde.contoso.com:


systemctl stop apache2 

mysqldump --lock-tables -h localhost --user=hordeDBuser --quick --max_allowed_packet=512M --password='password' hordedb | mysql --user=hordeDBuser --password='password' -h database.contoso.com hordedb

sed -i s/\$conf\[\'sql\'\]\[\'hostspec\'\]\ \=\ \'localhost\'\;/\$conf\[\'sql\'\]\[\
'hostspec\'\]\ \=\ \'database\.contoso\.com\'\;/ /var/www/config/conf.php

systemctl start apache2

postfixadmin.contoso.com


systemctl stop apache2 

mysqldump --lock-tables -h localhost --user=postfixadminDBuser --quick --max_allowed_packet=512M --password='password' postfixadmindb | mysql --user=postfixadminDBuser --password='password' -h database.contoso.com postfixadmindb

sed -i s/\$dbserver\=\'localhost\'\;/\$dbserver\=\'database\.contoso\.com\'\;/ /etc/postfixadmin/dbconfig.inc.php

systemctl start apache2

Now we are running all databases on the central database server. The mariadb service on the hosts now can be disabled:

update-rc.d mariadb disable

Resume

Moving databases away from their applications makes sense for different reasons. One may be having a dedicated and special secured zone for the server, or performance goals.
In my case it was the thought, that I could gain some performance out of it, but I was disappointed at the end. You cannot expect too much from a single core 700MHz CPU compared to a 900MHz dual Core.
I did a measure after the switch over using powershell and my database restore script.

Used command:

measure-command {.\database-restore.ps1 $DataBaseName $DataBaseServer}

The command loads the last backup of a given database from the Nextcloud server.

Database => Localhost

Seconds : 13
Milliseconds : 237
Ticks : 132376582
TotalDays : 0,000153213636574074
TotalHours : 0,00367712727777778
TotalMinutes : 0,220627636666667
TotalSeconds : 13,2376582
TotalMilliseconds : 13237,6582

Database => Central Server

Seconds : 46
Milliseconds : 105
Ticks : 461057056
TotalDays : 0,000533630851851852
TotalHours : 0,0128071404444444
TotalMinutes : 0,768428426666667
TotalSeconds : 46,1057056
TotalMilliseconds : 46105,7056

You can see, the timing is 3 times bigger, that with localhost. So it does not make sense from performance perspective. If you want to have a significant revenue from this, you need to have the appropriate hardware for it.