How to install and configure MariaDB on Ubuntu Server 18.04

**LHammonds** · July 13th, 2018

Migrate Database from Old Server

You can use the full backup automatically made by the "db-backup.sh" script (/bak/mysql/db-all.sql) if you have that already running or you can create a manual backup. The steps below will document how to do it manually but you could just as easily use your existing backup file(s).

Here are the steps to import the databases from the old server into the new server:

On the old server, make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all.sql
Transfer db-all.sql to the new server (via WinSCP or other means)
On the new server, make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all-before.sql
Import the db-all.sql file by typing: mysql < /tmp/db-all.sql
Make a full backup of the current databases by typing: mysqldump --all-databases > /tmp/db-all-after.sql
Verify that your databases, tables, rows and users are now on the new server
Archive the /tmp/*.sql files for safe-keeping until you no longer need them. If you leave them in /tmp, they will be automatically deleted upon next reboot.
Now would be a good time to run your backup script: /var/scripts/prod/db-backup.sh

Now you can point your apps to the new database server or shutdown the old server and then change the IP of this server to match the old server.

NOTE: Saving the following lines in case I expand this further by showing how to extract an archive 1st and then restore from that.

Make a restore folder by typing: mkdir /tmp/restore
Extract the archive by typing: 7za x -so -w/tmp /tmp/*.7z | tar -C /tmp/restore --strip-components=3 -xf -

NOTE: If you see the following error in the syslog, especially after doing an "apt-get upgrade" and noticing the mysqld service stopped, you need to simply run "mysql_upgrade" to fix the problem. Reference Bug

Code:

Jun  1 14:25:48 srv-mysql mysqld_safe[23145]: ERROR: 1136  Column count doesn't match value count at row 1
Jun  1 14:25:48 srv-mysql mysqld_safe[23145]: 2017-06-01 14:25:48 140594627692800 [ERROR] Aborting
Jun  1 14:25:50 srv-mysql mysqld_safe[23145]: Installation of system tables failed!

Migrating Individual Databases from Old Server

If you export one database at a time, it will not include the grants. You will need to export the grants and then import the database, then import the grants on the new server.

Example: Let us assume the database we want to migrate is called "mydb"

On the old server:

Code:

mysqldump --databases mydb > /tmp/mydb.sql
mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g' > /tmp/mydb-grants.sql

Now edit the mydb-grants.sql file and delete everything not related to the DB you want.

Copy both .sql files to the new server and then execute the following commands:

Code:

mysql < /tmp/mydb.sql
mysql < /tmp/mydb-grants.sql

Restoring a database without generating a log

If you are restoring/migrating a huge database and do not want a log generated for the import, you can temporarily disable logging for your session as follows:

Code:

mysql
SET sql_log_bin = 0;
source /tmp/mydb.sql
exit

**LHammonds** · July 13th, 2018

Crontab Schedule

I would not advise anyone to ever "edit" a live crontab schedule by typing "crontab -e" but rather edit a saved schedule file and then load the schedule file. This will allow you to make backups of the schedule so you can always go back to a known-good schedule or at least back to the way it was before you made a change...assuming you always work with a copy of the schedule 1st.

Here is my root crontab scheduling file:

/var/scripts/data/crontab.root

Code:

########################################
# Name: Crontab Schedule for root user
# Author: LHammonds
############# Update Log ###############
# 2012-05-20 - LTH - Created schedule
########################################
 
SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
 
# m h dom mon dow command
 
#
# Backup Databases
#
0 23 * * * /var/scripts/prod/db-backup.sh > /dev/null 2>&1
#
# Database On Demand Backup
#
0-59 * * * * /var/scripts/prod/db-ondemand-backup.sh > /dev/null 2>&1
#
# Daily check for available space on /var
#
0 1 * * * /var/scripts/prod/check-storage.sh opt 50 50 > /dev/null 2>&1
#
# Daily check for available space on /backup
#
0 2 * * * /var/scripts/prod/check-storage.sh bak 50 50 > /dev/null 2>&1
#
# Daily check for available space on /temp
#
0 3 * * * /var/scripts/prod/check-storage.sh tmp 50 50 > /dev/null 2>&1

Once you have created the file, make sure appropriate permissions are set by typing the following:

Code:

chown root:root /var/scripts/data/crontab.root
chmod 0600 /var/scripts/data/crontab.root

To enable the root schedule using this file, type the following:

Code:

crontab -u root /var/scripts/data/crontab.root

To disable the root schedule, type the following:

Code:

touch /tmp/deleteme
crontab -u root /tmp/deleteme
rm /tmp/deleteme

If you need to modify the schedule, make a backup copy 1st. For example:

Code:

cp /var/scripts/data/crontab.root /var/scripts/data/2011-11-28-crontab.root
vi /var/scripts/data/crontab.root (make your changes)
crontab -u root /var/scripts/data/crontab.root

**LHammonds** · July 13th, 2018

Fail2Ban

If you have Fail2Ban installed and protecting SSH as part of the base install. You can add these few changes to also watch for database login failures.

Code:

vi /etc/fail2ban/jail.local

Add the following to the bottom. If your data path is different, be sure to update "logpath"

Code:

[DEFAULT]
## 127.0.0.1/8 = ignore login failures on the local machine
## 192.168.107.0/24 = ignore login failures on an entire subnet
## 192.168.107.55 = ignore login failures for this specific IP
ignoreip = 127.0.0.1/8 192.168.107.55

## "bantime" is the number of seconds that a host is banned.
##  300 =  5 minutes
##  600 = 10 minutes
##  900 = 15 minutes
## 1800 = 30 minutes
## 3600 = 60 minutes
bantime = 1800
## "findtime" is the length of time between login attempts before a ban is set.
findtime = 300
## "maxretry" is how many attempts can be made to access the server from a single IP before a ban is imposed.
maxretry = 5
## "destemail" is the email address where you would like to receive the emails.
destemail = lhammonds@mydomain.com
## "sender" is the FROM: address when it arrives in your mailbox.
sender = ubuntu@mydomain.com
## Use the lightweight sendemail instead of sendmail
mta = sendemail
## Email notify with whois report and relevant log lines when a ban occurs
action = %(action_mwl)s

[mysqld-auth]
enabled = true
filter  = mysqld-auth
port    = 3306
logpath = /var/log/mysql/error.log

**LHammonds** · July 13th, 2018

Web Front-end

NOTE: I have not tested this on 18.04 yet

Someone asked if there was a graphical way to manage the server. After a long sigh, I mentioned that phpmyadmin is a popular front-end.

Here is how you install it:

Code:

apt install phpmyadmin

This will install Apache web server along with PHP and various libraries. I would recommend uninstalling it after your initial setup since having an Apache web service tends to eat away at your CPU/RAM. My corporate DB server runs happily on 512 MB of RAM. It is also a greater security risk with PHPMyAdmin installed due to additional services which can be attacked for vulnerabilities.

Code:

apt remove phpmyadmin

**LHammonds** · July 13th, 2018

Rename root

For increased security, you can rename the root account to something else with the following commands:

Code:

USE mysql;
UPDATE USER SET User='NewSecretRoot' WHERE User='root';
FLUSH PRIVILEGES;

Review User Accounts

Code:

SELECT User,Host,Password FROM mysql.user;

Make sure everyone has a password set.
If there are any hosts as "%" then review to make sure it is absolutely necessary. If possible, use "localhost" first, then use the specific hostname/IP after that. The "%" wildcard should be a last resort for special cases like the app server name/IP changes all the time or there are multiple app servers connecting using the same ID.

You can check the permissions of each login to make sure they have the least permissions necessary to get the job done:

Code:

show grants for 'someuser'@'localhost';

If said user has DELETE privilege when it is not needed, you could remove it using a command like the following:

Code:

REVOKE DELETE ON appdatabase.* FROM 'someuser'@'localhost';

**thuexemaycamranh79** · July 14th, 2018

thank you very much

**LHammonds** · October 24th, 2018

FYI - My HammondsLegacy.com site might go offline for a while due to financial issues but it will return once things become stable again.

EDIT 2018-12-05: My company closed down and my site will go offline soon. Once things become stable, I will get the site back up and all the links should start working again.