LowEndBox - Cheap VPS, Hosting and Dedicated Server Deals

Getting Started With the Dolphin: MySQL 101 - Full Setup and How to Guide

How to Setup MySQL - A Full Setup and How to GuideMariaDB 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

raindog308

2 Comments

  1. Very well organized and detailed guide. This cover the the most essential tasks for working with database. It is like SQL cheatsheet.

    March 1, 2021 @ 4:15 am | Reply
  2. Great tutorial Andrew! This one is really useful.

    March 3, 2021 @ 10:34 am | Reply

Leave a Reply to Jon Biloh Cancel reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Some comments may be held temporarily for manual approval.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

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