here<\/a> the setup of the base system.<\/p>\n\n\n\nOf course the first step is to install the most current version of MariaDB, so I checked the RePo:<\/p>\n\n\n\n
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
[...]<\/code><\/pre>\n\n\n\nAs I have made some pain full experiences using the generic package, I point now to the specific version:<\/p>\n\n\n\n
apt install mariadb-server-10.5<\/code><\/pre>\n\n\n\nAfter installation I check the version:<\/p>\n\n\n\n
mysql --version\n\nmysql Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnueabihf (armv8l) using EditLine wrapper<\/code><\/pre>\n\n\n\n…and secure the installation:<\/p>\n\n\n\n
sudo mariadb_secure_installation\n\nNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB\n SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!\n\nIn order to log into MariaDB to secure it, we'll need the current\npassword for the root user. If you've just installed MariaDB, and\nhaven't set the root password yet, you should just press enter here.\n\nEnter current password for root (enter for none):\nOK, successfully used password, moving on...\n\nSetting the root password or using the unix_socket ensures that nobody\ncan log into the MariaDB root user without the proper authorisation.\n\nYou already have your root account protected, so you can safely answer 'n'.\n\nSwitch to unix_socket authentication [Y\/n]\nEnabled successfully!\nReloading privilege tables..\n ... Success!\n\n\nYou already have your root account protected, so you can safely answer 'n'.\n\nChange the root password? [Y\/n]\nNew password:\nRe-enter new password:\nPassword updated successfully!\nReloading privilege tables..\n ... Success!\n\n\nBy default, a MariaDB installation has an anonymous user, allowing anyone\nto log into MariaDB without having to have a user account created for\nthem. This is intended only for testing, and to make the installation\ngo a bit smoother. You should remove them before moving into a\nproduction environment.\n\nRemove anonymous users? [Y\/n]\n ... Success!\n\nNormally, root should only be allowed to connect from 'localhost'. This\nensures that someone cannot guess at the root password from the network.\n\nDisallow root login remotely? [Y\/n]\n ... Success!\n\nBy default, MariaDB comes with a database named 'test' that anyone can\naccess. This is also intended only for testing, and should be removed\nbefore moving into a production environment.\n\nRemove test database and access to it? [Y\/n]\n - Dropping test database...\n ... Success!\n - Removing privileges on test database...\n ... Success!\n\nReloading the privilege tables will ensure that all changes made so far\nwill take effect immediately.\n\nReload privilege tables now? [Y\/n]\n ... Success!\n\nCleaning up...\n\nAll done! If you've completed all of the above steps, your MariaDB\ninstallation should now be secure.\n\nThanks for using MariaDB!<\/code><\/pre>\n\n\n\nNow we need to make sure, that MariaDB is listening to the correct IP:<\/p>\n\n\n\n
\/etc\/mariadb\/mariadb.conf.d\/50-server.cnf\n....\n# Instead of skip-networking the default is now to listen only on\n# localhost which is more compatible and is not less secure.\nbind-address = 123.456.789.012\n....<\/code><\/pre>\n\n\n\nRestart MariaDB and check with netstat -tulpn<\/strong><\/code>:<\/p>\n\n\n\nActive Internet connections (only servers)\nProto Recv-Q Send-Q Local Address Foreign Address State PID\/Program name\ntcp 0 0 123.456.789.012:3306 0.0.0.0:* LISTEN 3125\/mariadbd<\/code><\/pre>\n\n\n\nAfter the basic installation I now create the users for each database and the remote privileges for their respective connection:<\/p>\n\n\n\n
mysql -u root\n\nWelcome to the MariaDB monitor. Commands end with ; or \\g.\nYour MariaDB connection id is 40\nServer version: 10.5.15-MariaDB-0+deb11u1 Raspbian 11\n\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nMariaDB [(none)]>\n\nCREATE DATABASE wordpressDB;\nCREATE USER 'wordpressDBuser'@'wordpress.contoso.com' IDENTIFIED BY 'password';\nGRANT ALL PRIVILEGES ON wordpressDB.* TO \"wordpressDBuser\"@\"wordpress.contoso.com\";\n\nCREATE DATABASE nextcloudDB;\nCREATE USER 'nextcloudDBUser'@'nextcloud.contoso.com' IDENTIFIED BY 'password';\nGRANT ALL PRIVILEGES ON nextcloudDB.* TO \"nextcloudDBuser\"@\"nextcloud.contoso.com\";\n\nCREATE DATABASE hordedb;\nCREATE USER 'hordeDBuser'@'horde.contoso.com' IDENTIFIED BY 'password';\nGRANT ALL PRIVILEGES ON hordedb.* TO \"hordeDBuser\"@\"horde.contoso.com\";\n\nCREATE DATABASE postfixadmindb;\nCREATE USER 'postfixadminDBuser'@'postfixadmin.contoso.com' IDENTIFIED BY 'password';\nGRANT ALL PRIVILEGES ON postfixadmindb.* TO \"postfixadminDBuser\"@\"postfixadmin.contoso.com\";\n<\/code><\/pre>\n\n\n\nnow 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:<\/p>\n\n\n\n
- Stop the related services to prevent changes during switch over. In our case it is always the apache server.<\/li>
- transfer database from source to destination<\/li>
- change the database variable in the applications configuration<\/li>
- start the application services<\/li><\/ol>\n\n\n\n
nextcloud.contoso.com<\/h2>\n\n\n\n\nsystemctl stop apache2\n\nmysqldump --lock-tables -h localhost --user=nextcloudDBuser --quick --max_allowed_packet=512M --password='password' nextclouddb | mysql --user=nextcloudDBuser --password='password' -h database.contoso.com nextclouddb\n\nsed -i s\/\\'dbhost\\'\\ \\=\\>\\ \\'localhost:3306\\'\\,\/\\'dbhost\\'\\ \\=\\>\\ \\'database\\.contoso\\.com\\:3306\\'\\,\/ \/var\/www\/config\/config.php\n\nsystemctl start apache2<\/code><\/pre>\n\n\n\nwordpress.contoso.com<\/h2>\n\n\n\n\nsystemctl stop apache2 \n\nmysqldump --lock-tables -h localhost --user=wordpressDBuser --quick --max_allowed_packet=512M --password='password' wordpressdb | mysql --user=wordpressDBuser --password='password' -h database.contoso.com wordpressdb\n\nsed -i s\/define\\(\\ \\'DB_HOST\\'\\,\\ \\'localhost\\'\\ \\)\\;\/define\\(\\ \\'DB_HOST\\'\\,\\ \\'database\\.contoso\\.com\\'\\ \\)\\;\/ \/var\/www\/wp-config.php\n\nsystemctl start apache2<\/code><\/pre>\n\n\n\nhorde.contoso.com:<\/h2>\n\n\n\n\nsystemctl stop apache2 \n\nmysqldump --lock-tables -h localhost --user=hordeDBuser --quick --max_allowed_packet=512M --password='password' hordedb | mysql --user=hordeDBuser --password='password' -h database.contoso.com hordedb\n\nsed -i s\/\\$conf\\[\\'sql\\'\\]\\[\\'hostspec\\'\\]\\ \\=\\ \\'localhost\\'\\;\/\\$conf\\[\\'sql\\'\\]\\[\\\n'hostspec\\'\\]\\ \\=\\ \\'database\\.contoso\\.com\\'\\;\/ \/var\/www\/config\/conf.php\n\nsystemctl start apache2<\/code><\/pre>\n\n\n\npostfixadmin.contoso.com<\/h2>\n\n\n\n\nsystemctl stop apache2 \n\nmysqldump --lock-tables -h localhost --user=postfixadminDBuser --quick --max_allowed_packet=512M --password='password' postfixadmindb | mysql --user=postfixadminDBuser --password='password' -h database.contoso.com postfixadmindb\n\nsed -i s\/\\$dbserver\\=\\'localhost\\'\\;\/\\$dbserver\\=\\'database\\.contoso\\.com\\'\\;\/ \/etc\/postfixadmin\/dbconfig.inc.php\n\nsystemctl start apache2<\/code><\/pre>\n\n\n\nNow we are running all databases on the central database server. The mariadb service on the hosts now can be disabled:<\/p>\n\n\n\n
update-rc.d mariadb disable<\/code><\/pre>\n\n\n\nResume<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Used command:<\/h2>\n\n\n\nmeasure-command {.\\database-restore.ps1 $DataBaseName $DataBaseServer}<\/code><\/pre>\n\n\n\nThe command loads the last backup of a given database from the Nextcloud server.<\/p>\n\n\n\n
Database => Localhost<\/h2>\n\n\n\nSeconds : 13
Milliseconds : 237
Ticks : 132376582
TotalDays : 0,000153213636574074
TotalHours : 0,00367712727777778
TotalMinutes : 0,220627636666667
TotalSeconds : 13,2376582
TotalMilliseconds : 13237,6582<\/code><\/pre>\n\n\n\nDatabase => Central Server<\/h2>\n\n\n\nSeconds : 46
Milliseconds : 105
Ticks : 461057056
TotalDays : 0,000533630851851852
TotalHours : 0,0128071404444444
TotalMinutes : 0,768428426666667
TotalSeconds : 46,1057056
TotalMilliseconds : 46105,7056<\/code><\/pre>\n\n\n\nYou 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.<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":2,"featured_media":494,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,32,1],"tags":[37,36,16,33,13],"class_list":["post-487","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-computer","category-raspberry","category-uncategorized","tag-horde","tag-mariadb","tag-nextcloud","tag-raspbianos","tag-wordpress"],"_links":{"self":[{"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/posts\/487","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/comments?post=487"}],"version-history":[{"count":8,"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/posts\/487\/revisions"}],"predecessor-version":[{"id":496,"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/posts\/487\/revisions\/496"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/media\/494"}],"wp:attachment":[{"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/media?parent=487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/categories?post=487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.familie-lahme.de\/index.php\/wp-json\/wp\/v2\/tags?post=487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}