How to Set Up MariaDB on Debian 9
Updated by Linode Contributed by Nashruddin Amin
What is MariaDB?
MariaDB is a drop-in replacement for MySQL. It strives to be the logical choice for database professionals looking for a robust, scalable and reliable SQL Server. This guide will help beginners install and configure MariaDB on Debian 9 (Stretch).
NoteThe steps required in this guide require root privileges. Be sure to run the steps below as
rootor with the sudo prefix. For more information on privileges see our Users and Groups guide.
In this section, you will install MariaDB and set the password for the MariaDB root user. MariaDB maintains a shell script that automatically sets up the necessary package repositories.
Update your system and install dependencies:
apt update && apt upgrade apt install dirmngr
Add the MariaDB signing key:
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
Download and execute the script:
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
Update your sources list:
apt install mariadb-server
You will be prompted to set a password for the MariaDB root user:
Choose a strong password to secure your server.
After installation, Debian will start the MariaDB server and also set the service to start automatically on reboot.
In this section you will learn how to connect to MariaDB and perform basic SQL commands.
The standard tool for interacting with MariaDB is the MySQL client. To get started, issue the following command to connect to MariaDB as the root user:
mysql -u root -p
When prompted, enter the root password you set when you installed MariaDB. You’ll see output like the following:
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 30 Server version: 5.5.37-MariaDB-1~wheezy-log mariadb.org binary distribution Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
(none)text in the MariaDB prompt. It will be used to display the current working database. Since you haven’t selected any database yet, it is displayed as
Create a sample database, to be populated with sample data. Use the following commands to create a database named testdb, which is owned by a new user testuser. These commands also set the password secretpassword for the new user:
CREATE DATABASE testdb; CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON testdb.* TO testuser@localhost; FLUSH PRIVILEGES; quit
The final line logs out the root user from MariaDB.
Log in to MariaDB as testuser:
mysql -u testuser -p
Use the database testdb:
Databases are composed of multiple tables. Create a new table and populate it with sample data:
CREATE TABLE products (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), price DECIMAL(6,2)); INSERT INTO products (name, price) VALUES ('MacBook Pro', 1600.0); INSERT INTO products (name, price) VALUES ('Dell', 850.0); INSERT INTO products (name, price) VALUES ('Acer', 775.0);
Verify that the new data has been inserted:
SELECT * FROM products;
Which should show this output:
+----+-------------+---------+ | id | name | price | +----+-------------+---------+ | 1 | MacBook Pro | 1600.00 | | 2 | Dell | 850.00 | | 3 | Acer | 775.00 | +----+-------------+---------+ 3 rows in set (0.00 sec)
Exit the MariaDB client by typing:
For more information about SQL commands, refer to the SQL Commands page on the MariaDB Knowledge Base. To view MariaDB’s command list from within the client, type:
MariaDB [(none)]> \h General information about MariaDB can be found at http://mariadb.org List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents'
To configure MariaDB to run the way you want, edit the
/etc/mysql/my.cnf file. This file controls most of the server system variables, which you will generally want to leave at the default setting.
Whenever you make changes to
/etc/mysql/my.cnf, restart the server by issuing the following command:
service mysql restart
Secure MariaDB Server
MariaDB recommends that you secure your installation by executing the following command:
You will be asked to change the root password, remove anonymous users, disable root logins outside of localhost, and remove the test database. It is recommended that you answer Y for all questions.
Remote User Connections
This section will demonstrate how to allow the previously created user, testuser, to connect to MariaDB remotely (by default, MariaDB will allow connections from only localhost).
CautionOpening a MariaDB server up to the internet makes it less secure. If you need to connect from somewhere other than localhost, make sure you implement firewall rules that allow connections only from specific IP addresses.
Log in to MariaDB as root:
mysql -u root -p
Allow testuser to connect from remote hosts:
GRANT ALL PRIVILEGES ON testdb.* TO testuser@'%' IDENTIFIED BY 'secretpassword'; FLUSH PRIVILEGES; quit
Configure MariaDB to listen to all network interfaces. Open the
/etc/mysql/my.cnffile and edit the
bind-addressvariable to listen to all network interfaces:
bind-address = 0.0.0.0
Restart the server:
service mysql restart
Test the connection from your local computer to the MariaDB server, replacing testuser with your username, and example.com with your domain or IP address:
mysql -u testuser -h example.com -p
If the login is successful, you should see the MariaDB welcome message and the shell prompt.
MySQL Tuner is a useful tool that connects to a running instance of MariaDB and provides configuration recommendations based on workload. You should let your MariaDB instance run for at least 24 hours before running the tuner. The longer the instance has been running, the better advice the tuner will provide.
Install MySQL Tuner by issuing the following command:
apt install mysqltuner
Run MySQL tuner with the following command:
Reset MariaDB’s Root Password
If you forget your root password, reset it with these steps:
Stop the MariaDB server:
systemctl restart mysql
Start the server with the
skip-grant-tablessetting so you can log in to MariaDB without the password:
mysqld_safe --skip-grant-tables --skip_networking &
Now you can connect to the MariaDB server as root without a password:
mysql -u root
Within the MariaDB client, issue the following commands to reset the password for the root user and log out:
FLUSH PRIVILEGES; USE mysql; ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; FLUSH PRIVILEGES; quit
Restart the MariaDB server:
service mysql restart
Connect to the MariaDB server using your new password:
mysql -u root -p
You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.
Join our Community
This guide is published under a CC BY-ND 4.0 license.