Chapter 1. Getting Started with MariaDB

In this chapter, we will cover the following recipes:

Installing MariaDB on Windows
Installing MariaDB on Linux
Installing MariaDB on Mac OS X
Enabling the Feedback plugin
Switching between InnoDB and XtraDB
Creating a backup user
Making backups with XtraBackup
Making backups with mysqldump
Checking and optimizing tables automatically with mysqlcheck and cron
Using progress reporting in the mysql client

Introduction

This chapter is all about getting us up and running with MariaDB using basic recipes, which provide the foundation for the other recipes in this book.

The first three recipes are the most basic of all the recipes and cover installing MariaDB on the Windows, Linux, and Mac OS X operating systems. We'll then cover a couple of common configuration options and some common maintenance tasks.

We'll finish the chapter with a recipe on the progress reporting feature of the mysql client application.

Installing MariaDB on Windows

There was a time when installing MariaDB on Windows meant downloading and unpacking a ZIP file. From then on, it was up to us to set up a system service, making sure that the paths were correct, and so on. Today, the process is completely automated with the MariaDB MSI package. The ZIP file is still available, but unless we know we want it (and we might!), there is no reason to use it.

How to do it...

Let's get started by following these steps:

Visit http://mariadb.org/downloads and select the version of MariaDB we are interested in. There will be a development version and a stable version. For most users, the stable version is recommended.
After choosing the version of MariaDB that we want, select either the 32-bit or 64-bit version of the MariaDB MSI package for Windows. For most computers, the 64-bit version will work fine; but if we are on an older computer, we may need to use the 32-bit version.
Once it is downloaded, the installer may launch automatically, or depending on our settings, we may need to manually launch it, as shown in the following screenshot:
Once the installer starts, click through the defaults. We can change them if we want, but there is no need.
After the installation has finished, MariaDB will be up and running on our Windows computer.

How it works...

MSI stands for Microsoft installer. It's a standard package format for software installers on the Windows operating system. The MariaDB MSI package encapsulates all of the common manual steps for installing MariaDB. This includes steps such as setting up a Windows service so that MariaDB can be started automatically at boot time, creating the data directory, and so on.

There's more...

While clicking through the installer, there are some choices that we may wonder about. Two of them are HeidiSQL and the Feedback plugin.

HeidiSQL

In addition to installing MariaDB, the MSI package also, by default installs the HeidiSQL graphical client. This open source graphical client is a great way to interact with MariaDB, and the MariaDB and HeidiSQL developers have worked together to make sure that it supports all MariaDB features and options.

The Feedback plugin

One of the screens of the installer offers the option to turn on the Feedback plugin if we want to. Refer to the Enabling the Feedback plugin recipe later in this chapter for more information on this plugin and to know why it's a good idea to enable it.

Installing MariaDB on Linux

Most of the installs of MariaDB are on various flavors of Linux. This recipe will get most Linux users up and running MariaDB quickly and easily.

Getting ready

First, determine which version of Linux we are running. In most cases, we will have installed Linux ourselves, so we will know this information. But on the off chance we do not know the information, the following command will give us the information we need:

cat /etc/lsb-release

On my desktop, the preceding command shows the following output:

daniel@gandalf:~$ cat /etc/lsb-release 
DISTRIB_ID=Ubuntu 
DISTRIB_RELEASE=10.04 
DISTRIB_CODENAME=lucid 
DISTRIB_DESCRIPTION="Ubuntu 10.04.4 LTS"

From this, I see that I am running Ubuntu 10.04 "lucid". This is all the information I need.

How to do it...

Let's get started by following the ensuing steps:

Visit http://mariadb.org/downloads/mariadb/repositories and select our distribution, release, version, and (for some distributions) the mirror that we would like to use, as shown in the following screenshot:
Once all of the choices have been made, instructions will appear at the bottom of the page.
On Fedora, CentOS, and Red Hat, the basic instructions are to copy the provided text into the MariaDB.repo file located at /etc/yum.repos.d/ and then to issue the following command in order to install MariaDB:
```
sudo yum install MariaDB-server MariaDB-client
```
During the initial installation with yum, we will be asked to accept the key used to sign MariaDB packages. This key has a fingerprint as follows:
```
1993 69e5 404b d5fc 7d2f e43b cbcb 082a 1bb9 43db
```
Assuming that the fingerprint shown by yum matches the key fingerprint shown in step 4, go ahead and answer yes to the question.
On Debian and Ubuntu, in addition to choosing the Linux distribution, release, and MariaDB version, we need to choose the mirror that we want to use. After selecting the items in all four boxes, customized instructions for installing MariaDB will appear at the bottom of the page. As an example, the commands to install MariaDB 10.0 on Ubuntu 12.04 LTS "Precise" are as follows:
```
sudo apt-get install python-software-properties
sudo apt-key adv --recv-keys --keyserver \
  keyserver.ubuntu.com 0xcbcb082a1bb943db
sudo add-apt-repository \
  'deb http://ftp.osuosl.org/pub/mariadb/repo/10.0/ubuntu precise main'
sudo apt-get update
sudo apt-get install mariadb-server
```
After the YUM or APT-based installation has finished, we can start and stop MariaDB with the following commands:
```
sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql stop
```

How it works...

The repository configurator supports the following Linux distributions:

Red Hat
Ubuntu
Debian
Mint
Mageia
Fedora
CentOS
openSUSE

New Linux distributions are added from time to time, so it's possible that when we visit the website, another Linux distribution or two would have been added to the list.

The common feature of all of these distributions is that they use a package manager. Fedora, Red Hat, and CentOS use the Yellowdog Updater Modified (YUM) package manager. Debian, Ubuntu, and Mint use the Advanced Package Tool (APT) package manager. The MariaDB developers provide repositories for these distributions.

Other distributions such as Mageia and openSUSE are different. They use their own custom package managers. MariaDB packages for these Linux distributions are provided by the developers of those distributions. The repository configuration tool provides instructions for the commands that we need to run in order to install MariaDB.

Installing MariaDB on Mac OS X

Installing MariaDB on Mac OS X is similar to installing it on Linux (refer to the previous recipe), with one important difference: the MariaDB developers do not provide the installer; instead, it is provided by the brew project.

Getting ready

In order to install MariaDB on Mac OS X, we must first install Xcode from the Mac App Store. Once that is installed, we need to install and configure brew. The complete set of instructions for how to do this are on the brew website, http://brew.sh/, but the basic command is:

ruby -e \
  "$(curl -fsSL https://raw.github.com/Homebrew/homebrew/go/install)"

After installing brew, we will run the following doctor command to make sure that everything is set up properly:

brew doctor

When the doctor command finds an issue, and it might find several, it will print out a suggested fix for each one. To ensure that brew is happy, we need to follow the instructions until the doctor command gives us the following message:

Your system is ready to brew.

How to do it…

Let's get started by following the ensuing steps:

Run the following commands in our terminal:
```
brew update
brew install mariadb
```
If there are any dependencies, they will be installed first, and then brew will download the latest stable MariaDB source code tarball, compile it, and then install it.
Once the installation has finished, link the MariaDB startup plist to the LaunchAgents directory as follows, so that MariaDB will start automatically:
```
ln -sfv /usr/local/opt/mariadb/*.plist \
    ~/Library/LaunchAgents
```

To start MariaDB, use the following launchctl command to load the plist file:

launchctl load \
    ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist

To stop MariaDB, unload the plist file:

launchctl unload \
    ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist

How it works...

The brew installer works like a Linux package manager. Many open source software packages can be installed with it, including MariaDB.

The brew installer does not set a password for the root user, so the first thing that we should do after getting MariaDB running on Mac OS X is to run the mysql_secure_installation script. For more information, refer to the Securing MariaDB with mysql_secure_installation recipe in Chapter 13, MariaDB Security.

Enabling the Feedback plugin

The Feedback plugin gathers and submits anonymous usage information to the MariaDB developers. Enabling it is an easy way to help out the project.

Getting ready

We'll need a running install of MariaDB. Refer to the previous recipes for instructions on how to do this.

How to do it...

Let's get started by following the ensuing steps:

Stop MariaDB by following the directions in the recipe that we followed when installing MariaDB.
Open our my.cnf or my.ini file in a text editor such as Vim, Emacs, TextWrangler, or Notepad. On Windows, there is a helpful link under the MariaDB group that will automatically open the my.ini file in Notepad. On Linux, the my.cnf file is located at either /etc/mysql/my.cnf or /etc/my.cnf depending on the Linux distribution we are using.
Add the following line of code to the [mysqld] section of the system's my.cnf or my.ini file (if the section does not exist, create it):
```
feedback=on
```
Save the file and then start MariaDB by following the instructions in the recipe we followed when installing MariaDB, and the plugin will be enabled.

How it works...

The Feedback plugin is turned off by default. Adding feedback=on to the configuration file lets MariaDB know that we want it enabled.

This plugin automatically sends anonymous usage data to the MariaDB developers, which helps them to prioritize development resources. Examples of the type of data it collects includes what operating system we're running, how much memory we have, what plugins we have enabled, and so on.

The collected data can be viewed at http://mariadb.org/feedback_plugin.

There's more...

The Feedback plugin can be customized in various ways. For example, we can choose the data that we want to send back. We can also configure the plugin to send the data to our own server instead of sending it to the MariaDB developers.

Switching between InnoDB and XtraDB

By default, MariaDB uses the XtraDB storage engine in place of InnoDB because it contains improvements to InnoDB that are useful for all users. If we want to use the InnoDB storage engine for some reason, it is easy to do so.

How to do it...

Let's get started by following the ensuing steps:

Stop MariaDB by following the directions in the recipe we followed when installing MariaDB.
Open our my.cnf or my.ini file in a text editor such as Vim, Emacs, TextWrangler, or Notepad. On Windows, there is a helpful link under the MariaDB group that will automatically open the my.ini file in Notepad. On Linux, the my.cnf file is located at either /etc/mysql/my.cnf or /etc/my.cnf depending on the Linux distribution we are using.
Add the following lines of code to the [mysqld] section of the system's my.cnf or my.ini file. If the section does not exist, add it.
```
ignore_builtin_innodb
plugin_load=innodb=ha_innodb.so
```
Save the file and then start MariaDB by following the instructions in the recipe we followed when installing MariaDB.

How it works...

To check if we are using InnoDB or XtraDB, we use the SHOW ENGINES command. If we are using XtraDB, the InnoDB line of the output will begin as shown in the following command line:

| InnoDB | DEFAULT | Percona-XtraDB,Supports...

And, if we are using the InnoDB plugin, the InnoDB line will begin as shown in the following command line:

| InnoDB | DEFAULT | Supports...

Only one of the storage engines can be loaded at one time. It is not possible to have both the InnoDB and XtraDB plugins loaded at the same time.

Creating a backup user

It is a bad idea to use a super user like root for making backups. One main reason is that backups often run automatically, and so the password has to be stored somewhere (for example, in the my.cnf file). If the user that is being used for backups has full access to the database, it could be abused, or an error in a backup script could cause all sorts of trouble.

In this recipe, we will create a backup user with the minimum permissions necessary to run both the mysqldump and XtraBackup programs.

How to do it…

Let's get started by following the ensuing steps:

Launch the mysql command-line client.
Create the backup user. For this recipe, we'll call the user backupuser and give the user the password p455w0rd. The user can be named anything we wish, and the password should definitely be changed to something unique:
```
CREATE USER 'backupuser'@'localhost'
    IDENTIFIED BY 'p455w0rd';
```

Next, we will grant our new user a minimal set of permissions, just enough so that it can make backups as follows:

GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD,
    REPLICATION CLIENT
    ON *.* TO 'backupuser'@'localhost';

Lastly, we will use the FLUSH PRIVILEGES command to force MariaDB to reread the privileges table, which is always a good idea after granting new privileges to a user.
```
FLUSH PRIVILEGES;
```

How it works...

There's no need for the user we use to make backups in order to have every privilege on our databases. They only need a specific subset. For example, they don't need the INSERT or ALTER TABLE privileges since backup users just need to read the tables in our databases. The set of privileges in this recipe are enough for both the XtraBackup and mysqldump programs, and will likely be sufficient for other backup programs as well.

Making backups with XtraBackup

XtraBackup is a backup tool from Percona.

Getting ready

The precompiled XtraBackup packages are only available for Linux. Percona provides both YUM and APT repositories.

You can follow the XtraBackup installation instructions on the Percona website available at http://www.percona.com/doc/percona-xtrabackup/. Also, create a backup user by following the instructions in the Creating a backup user recipe.

How to do it...

Let's get started by following the ensuing steps:

Run the following command by changing the --user, --password, and /path/to/backups parts to the correct values:
```
sudo innobackupex --user=backupuser \
    --password=p455w0rd /path/to/backups
```
The innobackupex script will call XtraBackup and copy all of the files to a timestamped subdirectory of the specified backup directory. When it has finished, if everything went well, it will print a line similar to the following line of output:
```
130729 12:05:12  innobackupex: completed OK!
```

How it works...

The innobackupex script is a wrapper around XtraBackup. By itself, the XtraBackup program only backs up InnoDB and XtraDB databases. When the innobackupex script is used, MyISAM, Aria, and other non-InnoDB tables are also backed up.

There's more...

Backups created by XtraBackup and the innobackupex scripts are not ready to be used to restore a database as is. Backups must be prepared prior to restoring. There are also some things that we need to be aware of when backing up to an NFS-mounted disk.

Restoring from a backup

In order to prepare an XtraBackup backup to be restored, we must first prepare it as follows:

sudo innobackupex --apply-log /path/to/backups

Then, we can restore it with the following command:

sudo innobackupex --copy-back /path/to/backup

As with running the script for the initial backup, look for the completed OK! message at the end of the preparing and restoring steps.

The innobackupex script will refuse to overwrite the files in the data directory, so it must be empty before a restore can happen.

As a final step, we will also likely need to fix permissions on the restored files with something similar to the following command:

sudo chown -R mysql:mysql /var/lib/mysql

XtraBackup and NFS

When backing up to an NFS-mounted volume, check to make sure that it is mounted with the sync option. Data may appear corrupt if our NFS volume is mounted with the async option. Refer to the XtraBackup documentation for more information.

Making backups with mysqldump

The mysqldump program is included with MariaDB and works well as a simple backup tool.

Getting ready

Create a backup user by following the instructions in the Creating a backup user recipe.

How to do it…

Let's get started by following the ensuing steps:

To make a complete backup of all the data to a file named my-backup.sql, run the following command:
```
mysqldump --user=backupuser -p \
  --all-databases > my-backup.sql
```
If it completes successfully, mysqldump will place a line similar to the following command at the end of the output file:
```
-- Dump completed on <date> <time>
```
If a dump fails, an error message will be printed to the screen and the data in the backup file will end right before the error took place. Checking both the error message and the end of the backup file can give us important clues to figure out the failure.

How it works...

The mysqldump program generates backups in SQL formatted text. These backups can then be restored to the same MariaDB install, to a different MariaDB server, or because they are in SQL format, to a different database altogether.

Depending on the sizes of the databases in our database server, and whether we choose to backup all of the databases or just one or two, the backup file created by mysqldump could potentially be very large. We need to keep this in mind when using this program.

There's more...

The mysqldump program has many options. We will discuss some of the most useful ones here.

--add-drop-database

The --add-drop-database option causes mysqldump to add SQL commands to the backup output to drop a given database and then recreate it prior to restoring the data. This helps us to prevent duplicate data from being written to the database.

--add-drop-table

Similar to the previous option, the --add-drop-table option causes mysqldump to add SQL commands to the backup output in order to drop the tables prior to recreating them and inserting data.

--add-locks

The --add-locks option surrounds the table output of the backup with LOCK TABLES and UNLOCK TABLES statements. When restoring from a backup, locking the tables speeds up the restore.

Checking and optimizing tables automatically with mysqlcheck and cron

The mysqlcheck command can check, repair, and optimize tables. When paired with cron, this bit of regular maintenance can be automated. This recipe is only for Linux operating systems.

How to do it…

Let's get started by following the ensuing steps:

Create a new user on the server or choose an existing user account. For this recipe, we'll say that we have a user called sysuser created just for this purpose.
Create a user in MariaDB that has SELECT and INSERT privileges on all the databases. Those are the privileges that are needed for mysqlcheck. For this recipe, we'll name this user maint.
Create a .my.cnf file at /home/sysuser/.my.cnf (or wherever sysuser's home is located) with the following contents:
```
[client] 
user = maint 
password=maintuserpassword
```
Next, change the mode of the .my.cnf file to only be readable by the sysuser:
```
sudo chmod 600 /home/sysuser/.my.cnf
```

Add the following lines of code to /etc/cron.d/mariadb (create the file if it doesn't exist):

# m h dom mon dow user command 
15 23 * * 1   sysuser /usr/bin/mysqlcheck -A --auto-repair 
15 23 * * 2-7 sysuser /usr/bin/mysqlcheck -A --optimize

How it works...

The /etc/cron.d/ folder contains cron snippet files. The cron daemon looks in this folder and executes the commands just as it does for the user crontab files. The one key difference is that because this is a system folder and not a user folder, we need to tell cron which user to run the command as, which we do between the datetime command and the actual command.

When mysqlcheck is run, like other MariaDB utilities, it will automatically check for a .my.cnf file in the home directory of the user running it and will pick up options in the [client] section of that file. This is a perfect place to stick the login information as we can make the file readable only by that user. This way, we don't need to specify the username and password of our database maintenance user on the command line.

Two commands are run by the recipe. The first command runs only once a week, and it checks every database and autorepairs any problems it finds. The second command runs every other day of the week and optimizes the tables in every database.

There's more…

The mysqlcheck program has many options. Refer to https://mariadb.com/kb/en/mysqlcheck/ or run the command with --help for a complete list.

One thing to note is that the --analyze (-a), --check (-c), --optimize (-o), and --repair (-r) options are exclusive. Only the last option on the command line will be used.

Security

Using a nonroot user to run mysqlcheck automatically is a good security precaution. To make the sysuser even more secure, lock the account so that it can't log in. Refer to our distribution documentation for how to do this.

Using progress reporting in the mysql client

One relatively unknown feature of MariaDB is the ability of the client to show progress reports for long commands.

How to do it…

Let's get started by following the ensuing steps:

There's nothing to configure as progress reporting is turned on by default and works with the ALTER TABLE, ADD INDEX, DROP INDEX, and LOAD DATA INFILE commands. It also works with the CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE commands when using the Aria storage engine. For example, if we needed to change a large table from using the MyISAM storage engine to the Aria storage engine, it might look similar to the following command:
```
MariaDB [test]> ALTER TABLE my_big_table engine=aria;
Stage: 1 of 2 'copy to tmp table'  29.26% of stage done
```
The progress report line will update every 5 seconds until the operation is complete.

How it works...

For the clients that support it, mysqld (the MariaDB server) sends progress report messages every 5 seconds. The mysql command-line client supports it, as does the mytop shell script included with MariaDB.

You can easily add support for progress messages on other clients by following the instructions at https://mariadb.com/kb/en/progress-reporting/. If our favorite client application does not support progress reporting, encourage the developers to add it!

There's more…

We can change the default 5 second update by setting the progress_report_time variable to a value greater than 5. Values ranging from 1 to 5 are ignored.

Disabling progress reporting

To disable progress reporting, set the progress_report_time variable to 0 or use the --disable-progress-reports option when launching the mysql client. Progress reporting is automatically disabled in batch mode.