Ubuntu Server 18.04 – Managing MariaDB databases

How to Install MySQL 8.0 on Ubuntu 18.04

Now that our MariaDB server is up and running, we can finally look into managing it. In this section, I’ll demonstrate how to connect to a database server using the mariadb command, which will allow us to create databases, remove (drop) them, and also manage users and permissions.

To begin, we’ll need to create an administrative user for MariaDB. The root account already exists as the default administrative user, but it’s not a good idea to allow others to use that account. Instead, it makes more sense to create an administrative account separate from root for managing our databases. Therefore, we’ll begin our discussion on managing databases with user management. The users we’ll manage within MariaDB are specific to MariaDB, these are separate from the user accounts on the actual system.

To create this administrative user, we’ll need to enter the MariaDB shell, which again is simply a matter of executing the mariadb command with sudo. Alternatively, we can use the following command as a normal user to switch to root, without using sudo:

mariadb -u root -p

Once inside the MariaDB shell, your prompt will change to the following:

MariaDB [(none)]> 

Now, we can create our new administrative user. I’ll call mine admin in my examples, but you can use whatever name you’d like. In a company I used to work for, we used the username velociraptor as our administrative user on our servers, since nothing is more powerful than a velociraptor (and they can open doors). Feel free to use a clever name, but just make sure you remember it. Using a non-standard username has the added benefit of security by obscurity; the name wouldn’t be what an intruder would expect.

Here’s the command to create a new user in MariaDB (replace the username and password in the command with your desired credentials):

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES; 
When it comes to MySQL syntax, the commands are not case sensitive (though the data parameters are), but it’s common to capitalize instructions to separate them from data. During the remainder of this chapter, we’ll be executing some commands within the Linux shell, and others within the MariaDB shell. I’ll let you know which shell each command needs to be executed in as we come to them, but if you are confused, just keep in mind that MariaDB commands are the only ones that are capitalized.

With the preceding commands, we’re creating the admin user and restricting it to localhost. This is important because we don’t want to open up the admin account to the world. We’re also flushing privileges, which causes MariaDB to reload its privilege information. The FLUSH PRIVILEGES command should be run every time you add a user or modify permissions. I may not always mention the need to run this command, so you might want to make a mental note of it and make it a habit now.

As I mentioned, the previous command created the admin user but is only allowing it to connect from localhost. This means that an administrator would first need to log in to the server itself before he or she would be able to log in to MariaDB with the admin account. As an example of the same command (but allowing remote login from any other location), the following command is a variation that will do just that:

CREATE USER 'admin'@'%' IDENTIFIED BY 'password'; 

Can you see the percent symbol (%) in place of localhost? That basically means everywhere, which indicates we’re creating a user that can be logged in to from any source (even external nodes). By restricting our user to localhost with the first command, we’re making our server just a bit more secure. You can also restrict access to particular networks, which is desired if you really do need to allow a database administrator access to the server remotely:

CREATE USER 'admin'@'192.168.1.%' IDENTIFIED BY 'password'; 

That’s a little better, but not as secure as limiting login to localhost. As you can see, the % character is basically a wildcard, so you can restrict access to needing to be from a specific IP or even a particular subnet.

So far, all we did is create a new user, we have yet to give this user any permissions. We can create a set of permissions (also known as Grants) with the Grant
command. First, let’s give our admin user full access to the DB server when
called from localhost.

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'; 
FLUSH PRIVILEGES; 

Now, we have an administrative user we can use to manage our server’s databases. We can use this account for managing our server instead of the root account. Any logged on Linux user will be able to access the database server and manage it, provided they know the password. To access the MariaDB shell as the admin user that we created, the following command will do the trick:

mariadb -u admin -p 

After entering the password, you’ll be logged in to MariaDB as admin.

In addition, you can actually provide the password to the mariadb command without needing to be prompted for it:

mariadb -u admin -p<password> 

Notice that there is no space in between the -p option and the actual password (though it’s common to put a space between the -u option and the username). As useful as it is to provide the username and password in one shot, I don’t recommend that you ever use that method. This is because any Linux command you type is saved in the history, so anyone can view your command history and they’ll see the password in plain text. I only mention it here because I find that many administrators do this, even though they shouldn’t. At least now you’re aware of this method and why it’s wrong.

The admin account we created is only intended for system administrators who need to manage databases on the server. The password for this account should not be given to anyone other than staff employees or administrators that absolutely need it. Additional users can be added to the MariaDB server, each with differing levels of access. Keep in mind that our admin account can manage databases but not users. This is important, as you probably shouldn’t allow anyone other than server administrators to create users. You’ll still need to log in as root to manage user permissions.

It may also be useful to create a read-only user for MariaDB for employees who need to be able to read data but not make changes. Back in the MariaDB shell (as root), we can issue the following command to effectively create a read-only user:

GRANT SELECT ON *.* TO 'readonlyuser'@'localhost' IDENTIFIED BY 'password'; 

With this command (and flushing privileges afterwards), we’ve done two things. First, we created a new user and also set up Grants for that user with a single command. Second, we created a read-only user that can view databases but not manage them (we restricted the permissions to SELECT). This is more secure. In practice, it’s better to restrict a read-only user to a specific database. This is typical in a development environment, where you’ll have an application that connects to a database over the network and needs to read information from it. We’ll go over this scenario soon.

Next, let’s create a database. At the MariaDB prompt, execute:

CREATE DATABASE mysampledb; 

That was easy. We should now have a database on our server named mysampledb. To list all databases on our server (and confirm our database was created properly), we can execute the following command:

SHOW DATABASES;
Listing MariaDB databases

The output will show some system databases that were created for us, but our new database should be listed among them. We can also list users just as easily:

SELECT HOST, USER, PASSWORD FROM mysql.user; 
Listing MariaDB users

In a typical scenario, when installing an application that needs its own database, we’ll create the database and then a user for that database. We’ll normally want to give that user permission to only that database, with as little permission as required to allow it to function properly. We’ve already created the mysampledb database, so if we want to create a user with read-only access to it, we can do so with the following command:

GRANT SELECT ON mysampledb.* TO 'appuser'@'localhost' IDENTIFIED BY 'password'; 

With one command, we’re not only creating the user appuser, but we’re also setting a password for it, in addition to allowing it to have SELECT permissions on the mysampledb database. This is equivalent to read-only access. If our user needed full access, we could use the following instead:

GRANT ALL ON mysampledb.* TO 'appuser'@'localhost' IDENTIFIED BY 'password'; 

To double-check that we’ve executed the command correctly, we can use this command to show the grants for a particular user:

SHOW GRANTS FOR 'appuser'@'localhost';

Now, our appuser has full access but only to the mysampledb database. Of course, we should only provide full access to the database if absolutely necessary. We can also provide additional permissions, such as DELETE (whether or not the user has access to delete rows from database tables), CREATE (which controls whether the user can add rows to the database), INSERT (controls whether or not the user can add new rows to a table), SELECT (allows the user to read information from the database), DROP (allows the user to fully remove a database), and ALL (which gives the user everything). There are other permissions we can grant or deny, check the MariaDB documentation for more details. The types of permissions you’ll need to grant to a user to satisfy the application you’re installing will depend on the documentation for that software. Always refer to the installation instructions for the application you’re attempting to install to determine which permissions are required for it to run.

If you’d like to remove user access, you can use the following command to do so (substituting myuser with the user account you wish to remove and host with the proper host access you’ve previously granted the user):

DELETE FROM mysql.user WHERE user='myuser' AND host='localhost'; 

Now, let’s go back to databases. Now that we’ve created the mysampledb database, what can we do with it? We’ll add tables and rows, of course! A database is useless without actual data, so we can work through some examples of adding data to our database to see how this works. First, log in to the MariaDB shell as a user with full privileges to the mysampledb database. Now, we can have some fun and modify the contents. Here are some examples you can follow:

USE mysampledb; 

The USE command allows us to select a database we want to work with. The MariaDB prompt will change from MariaDB [(none)]> to MariaDB [mysampledb]>. This is very useful, as the MariaDB prompt changes to indicate which database we are currently working with. We basically just told MariaDB that for all of the commands we’re about to execute, we would like them used against the mysampledb database.

Now, we can CREATE a table in our database. It doesn’t matter what you call yours, since we’re just practicing. I’ll call mine Employees:

CREATE TABLE Employees (Name char(15), Age int(3), Occupation char(15)); 

We can verify this command by showing the columns in the database, to ensure it shows what we expect:

SHOW COLUMNS IN Employees;

With this command, we’ve created a table named Employees that has three columns (Name, Age, and Occupation). To add new data to this table, we can use the following INSERT command:

INSERT INTO Employees VALUES ('Joe Smith', '26', 'Ninja'); 

The example INSERT command adds a new employee to our Employees table. When we use INSERT, we insert all the data for each of the columns. Here, we have an employee named Joe, who is 26 years old and whose occupation is a Ninja. Feel free to add additional employees; all you would need to do is formulate additional INSERT statements and provide data for each of the three fields. When you’re done, you can use the following command to show all of the data in this table:

SELECT * FROM Employees;
Listing database rows from a table

To remove an entry, the following command will do what we need:

DELETE FROM Employees WHERE Name = 'Joe Smith'; 

Basically, we’re using the DELETE FROM command, giving the name of the table we wish to delete from (Employees, in this case) and then using WHERE to provide some search criteria for narrowing down our command.

The DROP command allows us to delete tables or entire databases, and it should be used with care. I don’t actually recommend you delete the database we just created, since we’ll use it for additional examples. But if you really wanted to drop the Employees table, you could use:

DROP TABLE Employees; 

Or use this to drop the entire database:

DROP DATABASE mysampledb; 

There is, of course, much more to MariaDB and its MySQL syntax than the samples I provided, but this should be enough to get you through the examples in this tutorial. As much as I would love to give you a full walkthrough of the MySQL syntax, it would easily push this chapter beyond a reasonable number of pages. If you’d like to push your skills beyond the samples of this chapter, there are great tutorials available on the subject.

Before I close this section though, I think it will be worthwhile for you to see how to back up and restore your databases. To do this, we have the mysqldump command at our disposal. Its syntax is very simple, as you’ll see. First, exit the MariaDB shell and return to your standard Linux shell. Since we’ve already created an admin user earlier in the chapter, we’ll use that user for the purposes of our backup:

mysqldump -u admin -p --databases mysampledb > mysampledb.sql 

With this example, we’re using mysqldump to create a copy of the mysampledb database and storing it in a file named mysampledb.sql. Since MariaDB requires us to log in, we authenticate to MariaDB using the -u option with the username admin and the -p option that will prompt us for a password. The --databases option is necessary because, by default, mysqldump does not include the database create statement nor the tables. However, the --databases option forces this, which just makes it easier for you to restore. Assuming that we were able to authenticate properly, the contents of the mysampledb database will be dumped into the mysampledb.sql file. This export should happen very quickly, since this database probably only contains a single table and a few rows. Larger production databases can take hours to dump.

Restoring a backup is fairly simple. We can utilize the mariadb command with the backup file used as a source of input:

sudo mariadb < mysampledb.sql 

So, there you have it. The mysqldump command is definitely very handy in backing up databases. In the next section, we’ll work through setting up a slave database server.

Comments are closed.