Managing Databases

The Linux platform has long been a very popular choice for hosting databases. Given the fact that databases power a large majority of popular websites across the internet nowadays, this is a very important role for servers to fill. Ubuntu Server is also a very popular choice for this purpose, as its stability is a major benefit to the hosting community. This time around, we’ll take a look at MariaDB, a popular fork of MySQL. The goal won’t be to provide a full walkthrough of MySQL’s syntax (as that would be a full book in and of itself), but we’ll focus on setting up and maintaining database servers utilizing MariaDB, and we’ll even go over how to set up a primary/secondary relationship between them. If you already have a firm understanding of how to architect databases, you’ll still benefit from this chapter as we’ll be discussing Ubuntu’s implementation of MariaDB in particular, which has its configuration...

Preparations for setting up a database server

Before we get started with setting up our database server, there are a few odds and ends to get out of the way. As we go through this chapter, we’ll set up a basic database server using MariaDB. I’m sure more than a few of my readers are familiar with MySQL. MySQL is a tried and true solution that is still in use in many data centers today, and that will probably continue to be the case for the foreseeable future. There’s a good chance that a popular website or two that you regularly visit utilizes it on the backend. So, you may be wondering then, why not go over that instead of MariaDB?

There are two reasons why this book will focus on MariaDB. First, the majority of the Linux community is migrating over to it (more on that later), and it’s also a drop-in replacement for MySQL. This means that any databases or scripts you’ve already written for MySQL will most likely work just fine with MariaDB,...

Installing MariaDB

Now we’ve come to the fun part, installing MariaDB. To get the ball rolling, we’ll install the mariadb-server package:

sudo apt install mariadb-server 

If your organization prefers to stick with MySQL, the package to install is mysql-server instead:

sudo apt install mysql-server 

Although it might be tempting to try out both MySQL and MariaDB to compare and contrast their differences, I don’t recommend switching from MariaDB to MySQL (or vice versa) on the same server. I’ve seen some very strange configuration issues occur on servers that had one installed and then were switched to the other (even after wiping the configuration). For the most part, it’s best to pick one solution per server and stick with it. As a general rule, MySQL should be used if you have legacy databases to support. For brand-new installations, go with MariaDB.

After you install the mariadb-server package, check to make sure the service...

Understanding the MariaDB configuration files

Now that we have MariaDB installed, let’s take a quick look at how its configuration is stored. While we won’t be changing much of the configuration in this chapter (aside from adding parameters related to setting up a secondary database instance), it’s a good idea to know where to find the configuration, since you’ll likely be asked by a developer to tune the database configuration at some point in your career. This may involve changing the storage engine, buffer sizes, or countless other settings. A full walkthrough on performance tuning is outside the scope of this book, but it will be helpful to know how the settings for MariaDB are read, since Ubuntu’s implementation is fairly unique.

The configuration files for MariaDB are stored in the /etc/mysql directory. In that directory, you’ll see the following files by default:

debian.cnf 
debian-start 
mariadb.cnf 
my.cnf 
my.cnf.fallback...

Managing MariaDB databases

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 manage and interact with databases, we’ll need to enter the MariaDB shell, and the same goes for when it comes to creating...

Setting up a secondary database server

Redundancy is an amazing thing. If a primary server fails for some reason, you can keep your applications running by having a secondary database server available in case the original meets its demise. Of course, you can always create regular backups of your database servers and restore if necessary, but it’s very hard to keep up with databases that are always changing, so backups have a tendency to become stale quite fast. A secondary database server enables you to have a copy that is always up to date. This doesn’t mean that you no longer need backups, but it does give you another option for recovery when faced with a problem.

The industry is moving away from terms like “Master” and “Slave” to describe a primary and secondary server. In this chapter and moving forward, we will use the terms “Primary” and “Secondary” to describe the relationship of one main database server...

Summary

Depending on your skill set, you’re either an administrator who is learning about SQL databases for the first time, or you’re a seasoned veteran who is curious about how to implement a database server with Ubuntu Server. In this chapter, we dove into Ubuntu’s implementation of this technology and worked through setting up our own database server. We also worked through some examples of the MariaDB syntax, such as creating databases, as well as setting up users and their grants. We also worked through setting up primary and secondary servers for replication.

Database administration is a vast topic, and we’ve only scratched the surface here. Being able to manage MySQL and MariaDB databases is a very sought-after skill for sure. If you haven’t worked with these databases before, this chapter will serve as a good foundation for you to start your research.

In the next chapter, we’ll use our database server to act as a foundation...

Further reading

• Configuring MariaDB with Option Files: https://mariadb.com/kb/en/configuring-mariadb-with-option-files/

Join our community on Discord

Join our community’s Discord space for discussions with the author and other readers:

https://packt.link/LWaZ0