What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

MariaDB Cookbook

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

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.

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.

What you will learn

Enable various MariaDB optimizations

Link MariaDB to a Cassandra cluster

Enable and use the TokuDB storage engine

Read from and write to various data formats including XML and CSV

Search through your data with Sphinx

Connect to other databases with ODBC

Tune MariaDB for the best performance

What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

Frequently bought together

$54.99

$54.99

$48.99

Total $ 158.97

Filter reviews by

All

Amazon verified reviews

123monkey45 Apr 16, 2014

This is a very useful book but the title is not exactly correct. This is not what one usually thinks of as a cookbook and to be fair the author states this in the introduction.This book isn't chock full of the usual how to create users. how to create tables ......, rather the author recommends you get an intro book for that. What this book does have is good coverage of advanced features of MariaDB. To name a few , changing storage engines (Tokudb,Connect,NoSQL), replication including Galera cluster, Sphinx text search, dynamic and virtual columns.This book is excellent in this regard it will make you aware of features you may not have known even existed and for that reason I would recommend buying it.

Amazon Verified review

Minh Tran May 18, 2014

I worked for MySQL for 7, 8 years and i am very interesting to read this book. The author has very much experience on Maria DB. He explained most of the thing short and clear that you could follow immediately.You could found most of good stuffs related to MariaDB like: * Migrate your current MySQL database to MariaDB * Talked about some interesting engines: Aria, CONNECT, TokuDB engines. * Optimization and Tuning are also mentioned * Some very specific features for MariaDB like Dynamic & Virtual Columns are understandable and able to apply easily.Besides talking about normal full text search support like other normal database, author also shows us about the search feature with Sphinx.For someone who wants to set up your database as clustering, you could found in a chapter with MariaDB Cluster. Author puts every detail for you that could follow quickly.Even author has a chapter talks about HandlerSocket plugin that allows programming languages (Perl, Python, Ruby) to interact with MariaDB directly. I would like to see more about other programming languages like Java and .NET, how do they interact with MariaDBIf author could provide some comparison for Dynamic & Virtual Columns from Maria DB with Schema less feature from NoSQL database, that would help much for us to choose the right database.This book is a very good start for Developers, DBAs, Consultants working with MariaDB. This is definitely in my must-read-book collection

LM Baker May 03, 2014

Written by someone who has been on board since the MariaDB project started, this cookbook is the ideal guide for those who want to unlock advanced features of MariaDB.

Pethuru Raj May 14, 2014

MariaDB is being prescribed as a venerable offshoot of the proven MySQL database technology yet it has surpassed comfortably its parent on several aspects in order to be a quick choice for many kinds of enterprise-scale and multi-tier applications. This book, not only provides a comprehensive introduction but also details all the latest developments around and on this fast-evolving database system. Besides the explanation of the installation, configuration, optimization and tuning of MariaDB, the author had tried to convey a lot of advanced topics that have a direct synchronization with the database in a seamless and sequential fashion. For example, he has described about the replication aspect in MariaDB. The chapter titled as "Replication with MariaDB Galera Cluster" includes recipes that cover how to install and use this new clustering solution. It seems that the author has a leaning towards multiple search engines (TokuDB Storage Engine CONNECT Storage Engine and Cassandra Storage Engine) and has explained the nitty-gritty in a spectacular style. There is an interesting chapter "Searching Data Using Sphinx" that covers how to install and use this useful full-text database indexer and search engine. Finally there is a separate chapter for MariaDB SecurityPrecisely speaking, this book is telling a compact and convincing story about the new database "MariaDB". I am doubly sure that this book is an awesome for developers, architects, evangelists, DB administrators, and consultants.

David Villalobos Jul 02, 2014

It is a bit complicated give my opinion about this book, the thing is that I expected more, let's see it this way, I bought the digital copy and it is OK, but, I will be very disappointed if I had bought the printed book since I think $45 is to much for this book.All the recipes are good, but I like a bit more technical explanation since I like to understand the thinks I am doing.