How to upgrade to MariaDB 10.1 on Serverpilot and Ubuntu 14.04
So you have setup your new server with ServerPilot and found there is no easy way to upgrade its default MySQL 5.5 installation to MariaDB. There are numerous reasons for making the change to MariaDB, including a more active release schedule and MySQL ‘s future with its new owners, Oracle. MariaDB is actively maintained and updated by the creator of MySQL and has many features only found in the Enterprise MySQL packages. Running MariaDB 10.1 on Serverpilot is a lot easier then you would think!
This tutorial requires knowledge of managing servers using SSH, if you are not comfortable using the CLI and administrating Linux, then please seek the assistance of a qualified server admin or contact us via the comments or email. We would be glad to help!
The first step would be to setup your new server droplet or dedicated hardware using Ubuntu 14.04. After getting your new server up and running, login using SSH and install ServerPilot. At this point, do not install any other apps or WordPress installations. If you have a server that already has active websites running via Serverpilot and Ubuntu 14.04, you can still follow the next steps, but be aware that upgrading MySQL to MariaDB can have unforeseen consequences. Always preform a MySQL dump of your existing databases. Solutions like AutoMySQLBackup are great for this, and regularly take daily, weekly and monthly backups of all your sites. This is a must have for server admins, and an AutoMySQLBackup ServerPilot install guide can be found here. The final safety check will be to verify you are using MySQL 5.5, You may do so by running the following command in the CLI.
It should return something like this
[email protected]:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13368 Server version: 5.5.52-0ubuntu0.14.04.1-log (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Note Line 4, stating the MySQL version and Ubuntu Version.
After getting your server setup and installing Serverpilot, open a SSH console window and enter the following commands. Each line starts with “sudo” and should be ran individually to avoid conflicts.
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable sudo invoke-rc.d apparmor restart sudo replace log_slow_queries slow_query_log_file -- /etc/mysql/conf.d/mysqld_slow_log.cnf sudo bash -c 'echo "slow_query_log = 1" >> /etc/mysql/conf.d/mysqld_slow_log.cnf' sudo apt-get purge --assume-yes mysql-server mysql-server-5.5 mysql-server-core-5.5 mysql-client mysql-client-5.5 mysql-client-core-5.5 sudo apt-get autoremove --assume-yes sudo apt-get install software-properties-common sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db sudo add-apt-repository 'deb [arch=amd64,i386] http://mariadb.mirror.anstey.ca/repo/10.1/ubuntu trusty main' sudo apt-get update sudo apt-get install --assume-yes mariadb-server
After these commands are completed, you will see a message asking you to set a root password. Please enter your root password, twice, and make sure to keep note of what you have made it. If you lose this password, Its not the end of the world but it is a hassle to reset.
Optional: From this point forward, you will no longer be able to escalate using serverpilots sudo -i command or just typing mysql. In order to return that functionality, you must edit the my.cnf located
This is a special file that MySQL reads when you run it from the command line. As the MySQL root user’s credentials are in that file, you don’t have to remember them or enter them yourself.
The contents of that file look like this:
user = root password = fdsf87897we87w98r7we89fsxcsf
Since we have updated the password when we reinstalled MariaDB, be sure to update it in the that file to the password you entered twice before in the purple MariaDB installation dialog. This will return your ability to just call mysql from the command line without invoking the -uroot -ppassword method.
Congrats! Your are now running MariaDB 10.1!
Thanks to oliverds @ github for providing the base code.
March 13, 2017
March 2, 2017
March 2, 2017