A beginner's guide to MySQL / MariaDB

A basic tutorial how to take control of MySQL / MariaDB server, how to make a database and a user, and how to store information in the MySQL database.

Normally, you can do everything with MySQL Workbench, HeidiSQL, DBeaver or software like that, but sometimes, you need to know how to use basic MySQL commands to complete your tasks.

How to install MySQL / MariaDB on Ubuntu, Debian and CentOS

If you don't have MySQL installed on your server, you can quickly download it. Getting the right packets depends on your system, but it's very easy if you're using Debian / Ubuntu based systems or CentOS. Use a complex password for the root account when the system asks you to type it. Do not use same password what you use for your server's root / superuser account.

Debian and Ubuntu

sudo apt-get install mysql-server

CentOS:

sudo yum install mysql-server
/etc/init.d/mysqld start

Now you should have the MySQL server up and running.

Starting server

You can start, restart and stop the server easily:

sudo /etc/init.d/mysql restart | stop | start

How to access the MySQL / MariaDB server

I prefer a graphical interface for creating and managing databases and users (ex. MySQL Workbench, HeidiSQL, phpMyAdmin, but you can also connect to the MySQL server by typing the following command into terminal:

mysql -u username -p

If you're logging in the first time, then you probably don't have any other users than root and the password for that user is whatever you put when you installed the MySQL server.

Remember, all MySQL commands end with a semicolon (;). The command will not execute without it.

It is common practise that MySQL commands are written in uppercase and everything else, like database and user names, are in lowercase to make them easier to distinguish. However, the MySQL command line is not case sensitive and you can write everything in lowercase if you like to.

How to create, access and delete a MySQL / MariaDB Database

Let's make a database called test. Log in to the MySQL server as root and type:

CREATE DATABASE test;
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

If you want to know what databases are available:

SHOW DATABASES;

The command above shows all the databases that are created in the MySQL server.

After creating a database, you can start using it and store information in it. Open the database by typing:

USE test;

Now you're in the database and can create tables. Remember, table and database names are case sensitive.

How to create a table inside of the database

Let's create a table called test. There has to be a PRIMARY KEY in every table and in this case the key is AUTO_INCREMENT meaning it automatically numbers each row. The key must be unique, because rows are identified by it.

There will also be a name and birthday. Name is using VARCHAR and bday is DATE. MySQL requires that dates be written as yyyy-mm-dd.

CREATE TABLE test (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    bday DATE
);

Now you can see what you just did by typing:

SHOW TABLES;

Structure

DESCRIBE test;

How to add information to a MySQL table

Use this format to insert information into each row:

INSERT INTO 'test' ('id','name','food','confirmed','signup_date')
VALUES (NULL, "Max", '1977-07-17');

If you want to see what table has been eating:

SELECT * FROM test;

You can also update existing data:

UPDATE 'test' SET 'bday' = '1981-07-17' WHERE 'test'.'name' ='Max';

How to delete a database

When the database is no longer needed you can drop it.

DROP DATABASE test;

How to use mysqldump to get backups or schema

I usually use the mysqldump command to take database backups, but it can also take out a schema without data. I sometimes use it when saving initial settings to Github.

mysqldump -u youruser -p yourdatabase

The -u flag indicates the username and the -p flag that a password will be supplied. After pressing <enter> you will be prompted for the password.

Alternatively, the password can be supplied on the command line, but there must be no space between the -p flag and the password. For example, if the password was "tuspe" do this:

mysqldump --d -u youruser -ptuspe yourdatabase

Schema only

The -d flag says not to include data in the dump. Alternatively you can use –no-data instead if you find that easier to remember:

mysqldump -d -u youruser -p yourdatabase  
mysqldump --no-data -u youruser -p yourdatabase

How to create a new MySQL / MariaDB user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

There should be only one database per user. It is more secure.

MySQL user permission

The next command will give all the rights to the certain database but the user can not grant privileges to other users or take anything from them. The user can control the database, create tables and data, and drop everything. That is the most common way to give access to the database.

GRANT ALL PRIVILEGES ON test.* TO 'newuser'@'localhost';

If you really want to, you can also give the user superuser rights with the grant option. That is not recommended when working with CMS or apps.

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;

You can also give certain permissions to the database or the table if you don't want to give all the rights.

GRANT [type of permission] ON [database name].[table name] TO 'username'@'localhost';

Or you can revoke some rights if the user don't need them:

REVOKE [type of permission] ON [database name].[table name] FROM 'username'@'localhost';

After making a change, you should flush privileges.

FLUSH PRIVILEGES;

Change user password

ALTER USER 'timo'@'localhost' IDENTIFIED BY 'newpassword?';

Delete a MySQL / MariaDB user

DROP USER 'demo'@'localhost';

Optimize databases

mysqlcheck -u username -p --optimize --all-databases

Thanks for reading!