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
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.
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.
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.
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.
While clicking through the installer, there are some choices that we may wonder about. Two of them are HeidiSQL and the Feedback plugin.
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 full documentation of the MariaDB MSI installer for Windows can be found at https://mariadb.com/kb/en/installing-mariadb-msi-packages-on-windows/
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.
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.
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 answeryes
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
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.
The full documentation on installing MariaDB on Linux can be found at https://mariadb.com/kb/en/mariadb-binary-packages/
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.
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.
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 theLaunchAgents
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 theplist
file:launchctl load \ ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
To stop MariaDB, unload the
plist
file:launchctl unload \ ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
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.
The Feedback plugin gathers and submits anonymous usage information to the MariaDB developers. Enabling it is an easy way to help out the project.
We'll need a running install of MariaDB. Refer to the previous recipes for instructions on how to do this.
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
ormy.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 themy.ini
file in Notepad. On Linux, themy.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'smy.cnf
ormy.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.
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.
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.
The full documentation of the Feedback plugin is available at https://mariadb.com/kb/en/feedback-plugin/
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.
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
ormy.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 themy.ini
file in Notepad. On Linux, themy.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'smy.cnf
ormy.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.
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.
Refer to another InnoDB- and XtraDB-specific recipe, Using extended keys with InnoDB and XtraDB, in Chapter 3, Optimizing and Tuning MariaDB
The InnoDB and XtraDB section of the MariaDB Knowledgebase has lots of great information on these storage engines, which is available at https://mariadb.com/kb/en/xtradb-and-innodb/
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.
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 passwordp455w0rd
. 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;
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.
XtraBackup is a backup tool from Percona.
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.
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 callXtraBackup
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!
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.
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.
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
The mysqldump
program is included with MariaDB and works well as a simple backup tool.
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.
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.
The mysqldump
program has many options. We will discuss some of the most useful ones here.
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.
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.
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
andINSERT
privileges on all the databases. Those are the privileges that are needed formysqlcheck
. For this recipe, we'll name this usermaint
.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
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.
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.
One relatively unknown feature of MariaDB is the ability of the client to show progress reports for long commands.
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
, andLOAD DATA INFILE
commands. It also works with theCHECK TABLE
,REPAIR TABLE
,ANALYZE TABLE
, andOPTIMIZE 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.
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!
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.