MariaDB Cookbook

5 (1 reviews total)
By Daniel Bartholomew
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Getting Started with MariaDB

About this book

MariaDB is a new database that has become very popular. It is easy to install and use, even on personal systems. It is evolving and expanding to meet the database needs of today, tomorrow, and into the future. MariaDB is a drop in replacement for MySQL it also has a lot of new features and performance enhancements that differentiate it from MySQL. It is also powerful enough to be the database of choice for some of the biggest and most popular websites in the world.

This practical guide unlocks the advanced features of MariaDB's capabilities, including new storage engines, performance enhancing optimizations, and other advanced abilities. If you're looking to backend your enterprise, cloud, or embedded or mobile apps with a fast, free, open source, and familiar SQL database, MariaDB is perfect for you.

We begin with installing MariaDB and configuring it to unlock powerful optimizations that can speed up certain queries exponentially. We will then move on to using the extra storage engines included with MariaDB, such as TokuDB and the CONNECT engine. With those mastered, it's then time to branch out from single to multi-server installations, so we will dive into MariaDB's clustering and replication features, learning how to efficiently search and index our data, work with NoSQL-style data, and connect MariaDB with a Cassandra cluster. It's a dangerous world out there, so last of all, we wrap things up with recipes on securing MariaDB.

This is your ideal guide for getting the most out of MariaDB.

Publication date:
March 2014
Publisher
Packt
Pages
282
ISBN
9781783284399

 

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:

  1. 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.

  2. 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.

  3. 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:

  4. Once the installer starts, click through the defaults. We can change them if we want, but there is no need.

  5. 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.

See also

 

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:

[email protected]:~$ 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:

  1. 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:

  2. Once all of the choices have been made, instructions will appear at the bottom of the page.

  3. 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
    
  4. 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
    
  5. Assuming that the fingerprint shown by yum matches the key fingerprint shown in step 4, go ahead and answer yes to the question.

  6. 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
    
  7. 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.

See also

 

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:

  1. Run the following commands in our terminal:

    brew update
    brew install mariadb
    
  2. 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.

  3. 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
    
  4. To start MariaDB, use the following launchctl command to load the plist file:

    launchctl load \
        ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
    
  5. 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:

  1. Stop MariaDB by following the directions in the recipe that we followed when installing MariaDB.

  2. 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.

  3. 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
  4. 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.

See also

 

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:

  1. Stop MariaDB by following the directions in the recipe we followed when installing MariaDB.

  2. 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.

  3. 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
  4. 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.

See also

  • 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/

 

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:

  1. Launch the mysql command-line client.

  2. 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';
    
  3. 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';
    
  4. 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:

  1. 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
    
  2. 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:

  1. 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
    
  2. 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>
    
  3. 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:

  1. 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.

  2. 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.

  3. 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
  4. Next, change the mode of the .my.cnf file to only be readable by the sysuser:

    sudo chmod 600 /home/sysuser/.my.cnf
    
  5. 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:

  1. 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
    
  2. 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.

Progress reporting in mytop

The mytop script included with MariaDB shows the progress of long running commands in the '%' column.

About the Author

  • Daniel Bartholomew

    Daniel Bartholomew has been using Linux since 1997 and databases since 1998. In addition to this book, he has also written MariaDB Cookbook, Packt Publishing, and dozens of articles for various magazines, including The Linux Journal, Linux Pro, Ubuntu User, and Tux. He became involved with the MariaDB project shortly after it began in early 2009 and continues to be involved to this day. He currently works for MariaDB, Inc. and splits his time between managing MariaDB releases, documentation, and maintaining various bits and pieces that keep the MariaDB project running smoothly.

    Browse publications by this author

Latest Reviews

(1 reviews total)
I have purchased this amazing book at discount and it helped me a lot at my first few days as a newbie MySQL DBA. It is the perfect choice for fast learning and incredible results. I will definitely use it in the future and consider another Packt titles withing the MySQL database domain!
Book Title
Access this book, plus 7,500 other titles for FREE
Access now