This tutorial describes how to install the latest version of MariaDB on an Ubuntu Bionic server.
Requirements:
- You have an account and are logged into console.scaleway.com
- You have an Ubuntu Bionic server
- You have configured your SSH Key
- You have sudo privileges or access to the root user.
MariaDB is a fork of the MySQL (Structured Query Language) relational database management system which allows switching from MySQL to MariaDB without having to alter your applications since the data and data structures will not need to change.
This means that:
Even the command line tools are similar to mysqldump
and mysqladmin
still having the original names, allowing MariaDB to be a drop-in replacement.
1 . Connect to your server using SSH
ssh root@SERVER_IP
If you do not know your server IP, you can list your existing servers using scw ps
(Scaleway CLI).
Type yes
when prompted to confirm that we wish to proceed. If the Scaleway CLI is not installed, you have several options:
brew install scw
Install the latest stable release on Mac OS X manually:
# prepare for first install and upgrade
mkdir -p /usr/local/bin
mv /usr/local/bin/scw /tmp/scw.old
# get latest release
wget "https://github.com/scaleway/scaleway-cli/releases/download/v1.19/scw-darwin-amd64" -O /usr/local/bin/scw
# test
scw version
Install the latest release on Linux:
# get latest release
export ARCH=amd64 # can be 'i386', 'amd64' or 'armhf'
wget "https://github.com/scaleway/scaleway-cli/releases/download/v1.19/scw_1.19_${ARCH}.deb" -O /tmp/scw.deb
dpkg -i /tmp/scw.deb && rm -f /tmp/scw.deb
# test
scw version
Note: If you use the root user, you can remove the
sudo
before each command.
2 . Update Ubuntu packet manager
sudo apt-get update
3 . Upgrade the Ubuntu packages already installed
sudo apt-get upgrade
4 . Install MariaDB server and MariaDB client
sudo apt-get install mariadb-server mariadb-client
The lines below allow you to stop, start and enable MariaDB server to automatically start up everytime you reboot your machine.
sudo systemctl stop mysql
sudo systemctl start mysql
sudo systemctl enable mysql
This program enables you to improve the security of your MySQL installation in the following ways:
test_
.mysql_secure_installation
To log in to MariaDB database server, run the command:
mysql -u root -p
If you forget or lose the root password to your MariaDB database, you can still gain access and reset the password if you have access to the server and a sudo-enabled user account.
Depending on the database used and its version, you’ll need to use different commands to recover the root password.
1 . Determine the SQL version
mysql --version
Which returns
mysql Ver 15.1 Distrib 10.1.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Note which database and which version you are running, as you will need it later.
2 . Shut down the database server to change the root password
sudo systemctl stop mariadb
If you run MySQL and MariaDB without loading information about user privileges, it allows you to access the database command line with root privileges without providing a password. It enables to gain access to the database without knowing it.
3 . Start the database without loading the grant tables or enabling networking:
sudo mysqld_safe --skip-grant-tables --skip-networking &
Note: The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.
4 . Connect to the database as the root user (the password should not be required)
mysql -u root
Which returns
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.29-MariaDB-6 Ubuntu 18.04
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
5 . Reload the grant tables by issuing the FLUSH PRIVILEGES
command
FLUSH PRIVILEGES;
6 . Change the root password
Note: Make sure to replace
new_password
with your new password of choice.
For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
If the ALTER USER
command does not work, run
UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
Which, in any case should return:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
7 . Reload the grant tables by issuing the FLUSH PRIVILEGES
command
8 . Restart the database server
sudo kill `cat /var/run/mariadb/mariadb.pid`
9 . Restart the service
sudo systemctl start mariadb
10 . Confirm that the new password has been applied correctly
mysql -u root -p
The command should now prompt for the newly assigned password. You should gain access to the database prompt as expected.