MySQL instead of MariaDB on Debian

mysqlMySQL instead of MariaDB… At my current employer, I’ve introduced Debian as the poison of choice for all managed servers, instead of the more exotic FreeBSD. I believe there’re nothing wrong with FreeBSD, on the contrary, but getting support or fully qualified personnel is way more difficult for a company our size.

Anyway, what does this this have to do with MySQL? Nothing really, just a bit of background. Debian has MariaDB in its repo and at first I went with it but developers complained that certain functions are incompatible with the former. Aarghh. So i’ve got to get rid of MariaDB and het MySQL going!

In all honesty I’ve written about this one before but for repetition sake and my own piece of mind, a little edit here and there, let’s do a quick rerun. I’m assuming you haven’t installed MariaDB yet.

We’re pulling the software directly from Oracle, but with an apt plugin so that we can easily keep it up to date:

# cd
# wget https://dev.mysql.com/get/mysql-apt-config_0.8.11-1_all.deb
# dpkg -i mysql-apt-config_0.8.11-1_all.deb
# apt update
# apt install mysql-community-client mysql-community-server

We really have to secure the default installation. First, enable mysql_native_password for the root user.

# mysql
> use mysql;
> update user set plugin='mysql_native_password' where User='root';
> flush privileges;
> exit

Then we can set the password and secure the installation. Be sure to NOT enable the password validation plugin because it’s a pain in the butt.

# mysql_secure_installation

Create a .my.cnf file in the /root directory for backups i.e. Contents of the file:

[client]
user = root
password = <putinpassword>
host = localhost

Don’t forget to secure the file:

# chmod 600 /root/.my.cnf

For logrotating to work we also have to edit the /etc/mysql/debian.cnf file to read this:

[client]
host = localhost
user = root
password = <putinpassword>
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = root
password = <putinpassword>
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

Again, secure the file:

# chmod 600 /etc/mysql/debian.cnf

Finally create a database and user:

# mysql
> CREATE DATABASE yourdatabase;
> CREATE USER 'yourdbuser'@'localhost' IDENTIFIED BY 'yourstrongpass';
> GRANT ALL ON yourdatabase.* TO 'yourdbuser'@'localhost';
> flush privileges;
> exit

The above is a default user with full access to a database he needs read and write access to. I’m also adding a monitoring user:

# mysql
> GRANT USAGE ON *.* TO 'nagios'@'localhost' identified by 'insertpass';
> flush privileges;
> exit

And for certain situations, we need a read-only user as well:

mysql -u root -p
> GRANT SELECT ON *.* TO 'developer'@'localhost' identified by 'insertpass';
> flush privileges;
> exit

Update: check out this post if you already have MariaDB up and running and want to move to MySQL.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.