Build a better database
Stuart Burns shows how SQL management isn’t something to be feared if you live with it from an admin’s point of view. Wake up at the back!
Stuart Burns shows how SQL management doesn’t have to be something to be feared if you live with it from an admin’s point of view.
Databases and their management can often seem like a black art to those who don’t need or perhaps want to understand them. For most administrators, databases are a setup task – configured only when an application is installed and then left alone, forever. This is not good practice. In this article we’ll look at some basic database administration while trying to understand what goes into the database in terms of components and language – and learn how to keep them in tip-top condition, along with some tips and tricks to make life easier. A well-maintained database server makes for happy administrators.
First, a quick overview of what databases are. As the name implies, databases hold masses of structured data. For the uninitiated, a simple example of structured data could be thought of as a spreadsheet of names and addresses in a consistent format, with each column heading having a unique name (field name) and each row being a record of the data – akin to an index card with all the bits of data required. In the SQL world, these datasets are kept in tables. This is often why we see people using Excel when they should really be using a proper database.
Each database row is consistent and structured, hence the word structured in the name SQL (Structured Query Language). Structure is also a recurring theme throughout the entire SQL world. While this may all sound very useful, what’s to stop someone accidentally putting in wildly incorrect data or leaving an important column blank? There are two ways (at a low level) that this can be ensured: data-typing and field management.
When building a table, we can apply specific constraints to fields. An example of a constraint is that a user can’t enter text into a field that expected an integer. The user can try it, but the database server will not allow it. Another important item is ensuring that all those fields that require data have data in them before the record – the row of data, if we continue using our spreadsheet analogy.
To ensure data is entered, there is an important key called ‘not null’. All these options are set up when the database table is created. Often data entry fields can
only select from a specific range of data, often another table. This helps ensure that the data is as correct as can be achieved.
A database can have many tables. More information about creating tables and data types can be found at https://mariadb.com/kb/en/library/data-types/. There are more complex scenarios, such as normalised form, foreign keys and such but they are beyond the remit of this administration-focused article.
Hopefully this clears up what a database table is and how at a table level, data can be entered and managed. There are also a few other key things that need to be introduced to give the full picture. Indexes, for example, can speed up searches exponentially. Indexes are essentially indexed fields, so rather than having to read through the entire table, the database server can quickly look up a value in the sorted list, akin to an index in a book. Finding information in a large book without an index would take an age, and databases are no different – although speed is relative in terms of a computer as opposed to a human.
Finally, keys are a way to give each row a unique entry; for example, an employee ID would be a great key, as there should be no duplicate entries. If you had three employees with identical names, how could you consistently be sure that whatever you are doing is to the right entry? Unique entries are important.
Now that we have an understanding of how tables, rows and constraints work, let’s turn our attention to
investigating some of that data. There are actually more items that are applicable but the main gist here is to help undertstand what makes up a database.
A problem like Maria
To get started with databases you can follow along by installing Mariadb on an Ubuntu 18.04 system by using the series of commands below. These will install the official Mariadb repository for Ubuntu LTS server (18.04). That way it ensures that the version of Mariadb running is the latest stable release, rather than what comes with the Ubuntu official installation and update channels. To find the best Mariadb repo, check out http://bit.ly/lxf253maria and substitute as needed. sudo apt-get install software-properties-common sudo apt-key adv --recv-keys --keyserver hkp:// keyserver.ubuntu.com:80 0XF1656F24C74CD1D8 sudo add-apt-repository ‘deb [ arch= amd64,arm64,p pc 64 el] http :// mirror.ja le co. com/mariadb/repo/10.3/ubuntu bionic main’ sudo apt-get update sudo apt-get install mariadb-server mariadb-client -y
As part of the installation process you’ll need to supply a password for the Mariadb server root user. To be clear, this is an SQL user we are discussing, not an Os-level user that we all know. We’ll discuss the differences shortly.
To make management easy we highly recommended that the database(s) is placed on a separate disk partition: the last thing the administrator needs is a database running out of space and causing multiple issues. This may sound complex but is actually very straightforward. We’ll assume that you have already set up a new ext4 filesystem that is mounted as /data.
This can also help improve security when correctly configured. To move a server to a new partition, stop the database server:
sudo systemctl stop mariadb
Copy the database to its new home:
sudo cp /var/lib/mysql /data/ -Rp
Finally, we need to edit the Mariadb configuration file to point to the new database server. This file is called my.cnf. Before editing it, be sure to make a copy of the file.
sudo vi /etc/mysql/my.cnf
Within the file, locate and change datadir option to point from its original location to the new one. Save it and exit. All being well, at this point you should be able to start up the database server using:
sudo systemctl start mariadb
The system should start up again. To check its status, use the command:
sudu systemctl status mariadb
Should it fail, look in the error log for the exact issue. Looking to the future, databases grow – and quickly. Therefore we strongly suggest that the SQL data volume sits on top of an LVM (Logical Volume Management–see LXF252) disk configuration.
That way, it becomes simple to extend the disk when needed with minimal issues, without having to move data around, reformat partitions and so on, causing more downtime than is required. There are other ways to optimise database performance by using different setups, but they are beyond the scope of this article (bored of hearing that yet?). www.techradar.com/pro/linux
Managing users
One of the administrator’s jobs is to manage the security of the database. The default Mariadb installation contains several items that shouldn’t be disabled in a production DB server – for example, databases, weak access control settings and other items. Fortunately the removal process is quite straightforward because Mariadb ships with a script to toughen up the configuration, including removal of the example database and disabling of direct access to the SQL server from outside the server. This is done by running the script: sudo mysql_secure_ installation
Running this will ask several questions and for our setup, you can go with the defaults. Depending on your configuration, you may need to select alternative options. As it is a standard Bash script, you can review what it does before executing it by viewing it in Vi, Nano. or similar.
The next part of the security setup is to add users to the DB server and assign it to the appropriate DB, after creating the databases. To use the database correctly
each database needs to have the appropriate users with the appropriate rights (privileges). The possible range of privileges include:
SELECT Query a database
INSERT Inserts a new record
UPDATE Changes an existing record
INDEX Create an index according to user specification
CREATE Create the database
ALTER Make alterations to the database schema
DROP Remove a database – very dangerous option!
GRANT OPTION Used to provide access rights to users for the database
ALL As the name implies, grants all rights to the user in question.
Most of these are quite descriptive (for example, SELECT, INSERT and UPDATE are options required to query databases). It should also be quite obvious regarding the rest. Some however are very dangerous (DROP deletes databases and GRANT is used to give rights to users).
To open the SQL client, use the following command, substituting names as needed:
mysql -u root -p
Before the user set up commences, we need to create a database. As the root user, creating an empty database is simple – an example is shown below. The language is highly structured. Also note that every single command is terminated by a semicolon. Without that, the command will never execute.
CREATE DATABASE `mytestdb`;
At this point we have the empty database. Granting rights to one is very much an art. Be very cautious about the rights given as too many rights may allow access to features and functions that really should be granted with caution – for example the DROP capability can be extremely destructive.
Before any rights are granted, the administrator needs to create users upon which the rights previously discussed can be conferred. The ability to create and modify rights should not be available to standard users or, even worse, user-accessible applications. Treat those rights with extreme caution.
Create an example by using the command as root.
CREATE USER ‘mynewuser’ IDENTIFIED BY ‘password’;
Obviously, the password should be long and strong. Now we need to assign the appropriate rights to the user. This is done using the GRANT option: GRANT ALL ON mytestdb.* TO ‘mynewuser’@ ’localhost’;
In this example we are granting ALL rights to a user – not good practice. Depending on your needs, you can change the rights as per the MYSQL/MARIADB documentation pages. Alternatively, if the user only needs to update the database, something like the following would work:
GRANT SELECT, INSERT, UPDATE on mytestdb.* TO ‘mynewuser’@ ’localhost’;
To make things a bit more complex, that last GRANT statement ended in ‘localhost’ . Depending on your configuration you may want to allow remote access. You can grant access to other people on other hosts by changing this appropriately. Doing this will make using desktop GUI tools possible, though it’s not recommended on production systems. The better way to manage the SQL is to SSH into the host and use the local MYSQL/MARIADB client. Obviously the SSH service should be protected appropriately and not be visible to people outside the network – that is, on the internet.
To apply those changes we need to flush the database configuration to apply the new settings:
FLUSH PRIVILEGES;
At this point our user ‘mynewuser’ can log in and will be able to add data to mytestdb. To further show how all the configuration is just another database, we can display all the users and their rights to the database by using the command
select * from mysql.user;
That may seem a little ‘junkish’, so to just see the users and the hashed password use the following:
select User,password from mysql.user;
Note that all the user data is stored in the mysql.user database, so it acts as a centralised point for providing database rights.
At this point the database will be installed and in good shape. Like any other system, a database needs periodic management and it is good to perform regular maintenance on the Mariadb server. If the DB server crashes through power loss, hardware failure etc and so on, it becomes doubly important to check and repair the databases to ensure data integrity. It is important that the application/web server and dependencies are
not started. Doing this could worsen the corruption and create other issues. To verify the database, use the following command. This checks all the databases for consistency and corrects any issues.
sudo mysqlcheck -u root -p --auto-repair --check --alldatabases
Backup and restore
All this lovely data is great, but as an administrator it is important to be able to back up and restore the data. Because databases run pretty much 24/7, stopping them to take a copy of the databases is not really viable. If the data and references are constantly changing, it needs to be tracked and managed to be consistent.
Thankfully the Mariadb platform comes with a tool called mysqldump. This tool can be used to create consistent backups. This is a command line tool, not an SQL tool. You’d use something like the following to make a backup:
mysqldump -u root --password= passsword --alldatabases > mybackup.sql
It’s actually quite straightforward to script the database backup procedure using cron and the script above – just change the mysqldump line to: mysqldump -u root --password= passsword --alldatabases > $(date “+%b_ %d_ %y_ %h_ %m_%s”).sql
A note of caution: the SQL dump file is not encrypted, therefore be careful where it gets dumped. We recommend a directory (or ideally partition) that only the administrators have access to. Also, keeping it on the local filesystem means it’s not really a backup, just a copy. Therefore place them on another trusted system, such as the backup server.
Obviously, when creating a backup the administrator needs to be able to restore from that backup. Restoring is just as easy. To restore the database, while logged into the SQL client the administrator needs to first create the empty database:
CREATE database mynewdatabase;
Use the following command:
mysql -u root -p mynewdatabase < mybackup.sql;
At this point the database should be restored. For most small databases such an approach would work fine. However once the databases get very large, it is often more prudent to use one of the more flexible DB backup tools available.
Now that our database is safely backed up, it’s time to restore it. Let’s delete the old database first. The command to be used is DROP. It cannot be overestimated how important it is that this command is used with extreme caution, and the command line checked several times before executing! To drop our test database use the following command (logged in to the SQL client):
DROP mynewdatabase;
Be extremely cautious when using this command, as it’s a one-shot affair. Also something to be aware of is that many databases use data from other databases, so be triply sure before performing the drop.
Now that we understand how databases work at a basic level, let’s discuss some ideas on making life
easier for the administrator. Making major structural changes to databases can be a very stressful affair, even when the application upgrade does this for the administrator. A half-upgraded database is pretty useless. The smart money, however, has a couple of shortcuts, depending on the server and the disk format.
If the database server in question is virtualised, there will be an option to take a snapshot. Using the snapshot creates an exact copy and all the changes at the block level are tracked. If the change or upgrade goes wrong, the changes can be reverted by rolling back the snapshot. How these are created varies between virtualisation platforms, but it’s a feature well worth using, allowing mistakes to be rectified easily.
Getting physical
On a physical server, similar functionality is provided by the LVM snapshot feature (assuming the administrator had the forethought to use LVM!). In order to use this feature, the LVM group must have spare (unallocated) capacity in the LVM. When using this, bear in mind that the snapshot can at most occupy the remainder of the storage in the LVM. This is something the administrator needs to plan for and implement when developing the disk layout.
One note of caution, irrespective of how the snapshot is performed: due to the way snapshots work, every changed block is tracked. As the delta (changes) grow, the more resources are required to manage the snapshot, as it has to do multiple reads of the disk and then the delta file. This gets worse over time, and even more so with multiple snapshots on the same server. Moral of the story: don’t leave the snapshot around for longer than it’s needed.
In summary, the key to keeping a sysadmin’s life simple is proactive management of the infrastructure. SQL databases are no different. The introduction and tips above should go some way to help keeping the server running smoothly.