MariaDB and PostgreSQL are the two leading SQL databases. Both adhere to many ANSI SQL standards, but these ANSI standards don’t specify all behaviors. The SQL statement themselves (such as SELECT, INSERT, UPDATE, and DELETE) are the areas where they have the most commonality. Where they differ the most is in their DDL (data definition language) commands, and the administrative/server components. In this tutorial, we’ll cover MySQL.
History
MariaDB is a fork of MySQL. MySQL was originally an independent company but when they were acquired by Oracle, some users got the willies and forked the project to start MariaDB. Both products (MySQL and MariaDB) have continued to be developed. In this tutorial, we’ll focus on MariaDB, which is the more license-friendly option. Note that while the product is called MariaDB, you will still see the term “mysql” in many areas, such as directory paths (/var/lib/mysql), etc.
Install
To install MariaDB on Debian systems:
apt-get install mariadb-server
On CentOS systems:
yum -y install mariadb-server
Post-Install Steps
After installing MariaDB, you should immediately run mysql_secure_installation to secure the MySQL configuration. You should set a root password and answer yes to all questions.
# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! #
Configuration Files
MariaDB is installed with reasonable defaults.
On CentOS systems, /etc/my.cnf is the main configuration file, and it sources files in /etc/my.cnf.d. There’s no need to edit /etc/my.cnf. Instead, edit the relevant files in /etc/my.cnf.d.
On Debian systems, configurations are stored in /etc/mysql. mariadb.cnf is the “mother” file, which sources files from /etc/mysql/conf.d/mariadb. /etc/mysql/mariadb.cnf explains priority with a refreshing clarity the, ahem, “enterprise” distribution seems incapable of:
# The MariaDB/MySQL tools read configuration files in the following order: # 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults, # 2. "/etc/mysql/conf.d/*.cnf" to set global options. # 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options. # 4. "~/.my.cnf" to set user-specific options.
Starting and Stopping
MariaDB starts and starts like a normal systemd service:
systemctl start mariadb systemctl stop mariadb
Note that for legacy compatibility purposes, “systemctl start|stop mysql” continues to work.
Connecting
Normally you would connect to MariaDB with a command like this:
$ mysql -u username -p somedb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 45708 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [employees]>
If connecting from an application, note that by default PostgreSQL runs on port 3306.
Super User Access
In MySQL, the system ‘root’ account is the superuser.
mysql -u root -p
And then enter your password when prompted.
Scripted Access
If you are scripting MariaDB (perhaps for backups), you can eliminate the need to enter a password. To do this, create a .my.cnf password in the relevant user’s home directory. It must be owned by that user and set to read/write by that user only:
# touch /root/.my.cnf # chmod 600 /root/.my.cnf
Then populate it with something like this:
[client] password = "my-password"
Now you can simply call “mysql” and (if you’re root) you’ll be logged in as root. Note that if you execute “mysql -p”, MariaDB will ask for your password since you asked it to.
Backing Up Databases
MariaDB supports the venerable mysqldump program, which dumps your database to a .sql file that contains the SQL instructions for recreating the database. Basic usage for dumping a database called ‘mydb’ looks like this:
mysqldump mydb > /some/path/mydb.sql
To dump all databases:
mysqldump -A > /some/path/all.sql
Creating a New Database
# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 45704 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create database new1; Query OK, 1 row affected (0.000 sec)
Switching to a Database
MariaDB [(none)]> use new1; Database changed MariaDB [new1]>
Creating a User
MariaDB users are in the form “user@place” where “place” can be a hostname or “%” for wildcard. We typically create a user and grant the user permission a database:
MariaDB [new1]> create user 'someone'@'localhost' identified by 'something'; Query OK, 0 rows affected (0.000 sec) MariaDB [new1]> grant all on new1.* to 'someone'@'localhost'; Query OK, 0 rows affected (0.001 sec)
We can also do this in one step:
MariaDB [new1]> grant all on new1.* to 'someone'@'localhost' identified by 'something'; Query OK, 0 rows affected (0.000 sec)
Regardless of how you do it, be sure to flush privileges when you’re done:
MariaDB [new1]> flush privileges; Query OK, 0 rows affected (0.001 sec)
Listing Databases and Tables
To list databases, use the show databases command:
MariaDB [new1]> show databases; +--------------------+ | Database | +--------------------+ | employees | | information_schema | | mysql | | new1 | | performance_schema | | tester | +--------------------+ 6 rows in set (0.001 sec)
To list tables, use show tables:
MariaDB [employees]> show tables; +----------------------+ | Tables_in_employees | +----------------------+ | current_dept_emp | | departments | | dept_emp | | dept_emp_latest_date | | dept_manager | | employees | | salaries | | titles | +----------------------+ 8 rows in set (0.000 sec)
Creating Tables
Creating tables is a very standard SQL command. In this case, we are setting the ‘id’ column to be the primary key and also declaring it as auto-increment, which means that it will automatically increase with every insert.
CREATE TABLE superheroes ( id integer primary key auto_increment, hero_name varchar(30), secret_identity varchar(30), team varchar(30) ); MariaDB [heroes]> CREATE TABLE superheroes ( -> id integer primary key auto_increment, -> hero_name varchar(30), -> secret_identity varchar(30), -> team varchar(30) -> ); Query OK, 0 rows affected (0.006 sec)
Inserting Data
We’ll add some rows, but we don’t need to include the ‘id’ column in our insert statement because it will auto-increment for us. The “START TRANSACTION” is optional, as we’ll explain next;
START TRANSACTION;
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Iron Man', 'Tony Stark', 'Avengers');
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Captain America', 'Steve Rogers', 'Avengers');
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Thor', 'Donald Blake', 'Avengers');
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('She-Hulk', 'Jennifer Walters', 'Avengers');
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Wolverine', 'Logan', 'X-Men');
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Cyclops', 'Scott Summers', 'X-Men');
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Shadowcat', 'Kitty Pryde', 'X-Men');
INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Colossus', 'Piotr Rasputin', 'X-Men');
MariaDB [heroes]> start transaction;
Query OK, 0 rows affected (0.000 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Iron Man', 'Tony Stark',
'Avengers');
Query OK, 1 row affected (0.002 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Captain America', 'Steve
Rogers', 'Avengers');
Query OK, 1 row affected (0.001 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Thor', 'Donald Blake', '
Avengers');
Query OK, 1 row affected (0.001 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('She-Hulk', 'Jennifer Wal
ters', 'Avengers');
Query OK, 1 row affected (0.001 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Wolverine', 'Logan', 'X-
Men');
Query OK, 1 row affected (0.001 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Cyclops', 'Scott Summers
', 'X-Men');
Query OK, 1 row affected (0.001 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Shadowcat', 'Kitty Pryde
', 'X-Men');
Query OK, 1 row affected (0.001 sec)
MariaDB [heroes]> INSERT INTO superheroes (hero_name, secret_identity, team) VALUES ('Colossus', 'Piotr Rasput
in', 'X-Men');
Query OK, 1 row affected (0.001 sec)Transactions
We’re in the middle of a transaction thanks to the START TRANSACTION statement. From our perspective, this is what the superheroes table looks like:
MariaDB [heroes]> select count(*) from superheroes; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.000 sec)
However, logging in from another session and running the same query produces this:
MariaDB [heroes]> select count(*) from superheroes; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.000 sec)
This is because we haven’t committed the data yet. Let’s do so.
MariaDB [heroes]> commit; Query OK, 0 rows affected (0.002 sec)
Now both queries show:
MariaDB [heroes]> select count(*) from superheroes; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.001 sec)
If you had not used “START TRANSACTION”, then every command would immediately commit.
Let’s try one more transaction. I’m going to delete one of the heroes:
START TRANSACTION; DELETE FROM superheroes WHERE hero_name = 'Thor'; MariaDB [heroes]> start transaction; Query OK, 0 rows affected (0.000 sec) MariaDB [heroes]> DELETE FROM superheroes WHERE hero_name = 'Thor'; Query OK, 1 row affected (0.001 sec) MariaDB [heroes]> select count(*) from superheroes; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.000 sec)
However, in the other session, I still see all rows:
MariaDB [heroes]> select count(*) from superheroes; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.000 sec)
I could COMMIT in session #1, but I also have the option to ROLLBACK – in other words, abandon my changes. In this case, it’s only one statement, but I could have hundreds or thousands of statements pending commit. A COMMIT would apply them all, and a ROLLBACK would roll them all back. In this case, I’ll rollback:
MariaDB [heroes]> rollback; Query OK, 0 rows affected (0.002 sec) MariaDB [heroes]> select count(*) from superheroes; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.001 sec)
Querying Data
To query data, we use SELECT in the from “SELECT (columns) FROM (table)” and then optionally adding clauses to filter data (WHERE), group data (GROUP BY), and sort data (ORDER BY). Some examples:
MariaDB [heroes]> select secret_identity from superheroes where hero_name = 'Iron Man'; +-----------------+ | secret_identity | +-----------------+ | Tony Stark | +-----------------+ 1 row in set (0.000 sec)
I can use “*” to mean all columns:
MariaDB [heroes]> select * from superheroes where hero_name = 'Thor'; +----+-----------+-----------------+----------+ | id | hero_name | secret_identity | team | +----+-----------+-----------------+----------+ | 3 | Thor | Donald Blake | Avengers | +----+-----------+-----------------+----------+ 1 row in set (0.000 sec)
Of course, I may have many rows:
MariaDB [heroes]> select hero_name, secret_identity from superheroes where team = 'Avengers' order by hero_nam e; +-----------------+------------------+ | hero_name | secret_identity | +-----------------+------------------+ | Captain America | Steve Rogers | | Iron Man | Tony Stark | | She-Hulk | Jennifer Walters | | Thor | Donald Blake | +-----------------+------------------+ 4 rows in set (0.001 sec)
I can also use various database functions to summarize data:
MariaDB [heroes]> select team, count(*) from superheroes group by team order by team; +----------+----------+ | team | count(*) | +----------+----------+ | Avengers | 4 | | X-Men | 4 | +----------+----------+ 2 rows in set (0.001 sec)
Deleting Data
MariaDB uses a standard DELETE statement:
MariaDB [heroes]> delete from superheroes where team = 'X-Men'; Query OK, 4 rows affected (0.002 sec)
Updating Data
Perhaps She-Hulk decides to leave the Avengers (nerds: she joined in Avengers #221 and left in Avengers #243):
MariaDB [heroes]> update superheroes set team = NULL where hero_name = 'She-Hulk'; Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [heroes]> select hero_name from superheroes where team = 'Avengers' order by hero_name; +-----------------+ | hero_name | +-----------------+ | Captain America | | Iron Man | | Thor | +-----------------+ 3 rows in set (0.000 sec)
Learning More
- The official MariaDB page and KnowledgeBase
- Oracle’s MySQL page and documentation
- The “employees” database is the official MySQL test database
Related Posts:
I also enjoy using LEBs! I have an empire of little guys for VPNs, larger guys for apps, and big guys for databases and storage. When I'm not in front a screen I'm into German Shepherd dogs, quality knives, target shooting, theology, tabletop roleplaying games, and forest hiking.
I enjoy writing technical articles here on LowEndBox to help people get more out of their systems. You can find me on LowEndTalk @raindog308
- LowEndBoxTV: Are All Hetzner Locations Alike?No!And Piotr Has the Proof: “This is Clearly Not the Same Processor” - November 22, 2024
- One Week From Tomorrow…THE WORLD WILL LOSE THEIR MINDS!Lines Are Already Forming! - November 21, 2024
- Crunchbits Discontinuing Popular Annual Plans – The Community Mourns! - November 20, 2024
Very well organized and detailed guide. This cover the the most essential tasks for working with database. It is like SQL cheatsheet.
Great tutorial Andrew! This one is really useful.