What do you get with eBook?

Instant access to your Digital eBook purchase

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 eBook?

Instant access to your Digital eBook purchase

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 Stars icon

Filter reviews by

All

Amazon verified reviews

DarthSHO Nov 17, 2021

I bought this book because I have recently created a system based on MariaDB and although I have many years experience with MySQL, there’s some very unique features of MariaDB that I’m trying to understand better.I expected this to be a little deeper dive into the advanced features provided in MariaDB. Not so much. It’s more of a how to than a cookbook.I’d recommend if you are a newbie to either DB since both use InnoDB as the default engine. It’s a great guide for setting up the DB and concise directions for configuring the basics.Not at all what I needed or expected, but a great reference book.Most of the content is current but some of the recipes are not correct which is expected for a quickly evolving product like MariaDB.That’s exactly where my issues with MariaDB stem. The CE of the DB is feature packed. I like it’s capabilities, BUT…Implementation of the advanced features are not documented well in their online docs or GitHub. Speaking with MariaDB’s Sales and Tech support they as much admitted this. It wasn’t a “push” to get MariaDB EE @ $10k/year tho, but apparently it’s evolving so quickly they have not been able to keep up the Docs, even on the EE vs the CE (Enterprise/Community).I love the concept of MariaDB, it’s powerful and could be a real player in the Enterprise DB Space, especially since it has Enterprise features built in to the CE that rival MS and the other big platforms.However, I really wanted to get a solid Proof If Concept created in the CE before I budgeted the money for the EE. I hoped this book would help, but nope.Which leaves me in a position of either dumping the platform altogether or taking a $10k risk for a year of the EE.NOT this books issue, but it’s not the book for exploring how to use advanced features.I’d still recommend this for a general setup reference.

Amazon Verified review

User0910 May 25, 2016

Contrairement à ce que suggère le titre, le livre n'est pas vraiment un livre de recettes pour MariaDB. Il s'agit plutôt d'une revue d'un certain nombre de fonctionnalités qui sont spécifiques à MariaDB, notamment par rapport à MySQL.C'est donc un livre informatif pour quiconque travaille avec MariaDB et souhaite en connaître les spécificités et capacités propres. Bien sûr, ces spécificités sont souvent techniques et à la marge d'une utilisation "paisible" de ce genre de SGDB, donc toutes ces recettes n'ont pas vocation à être utiles à tout le monde. C'est néanmoins sympathique d'avoir rapidement un coup d'oeil aussi large sur les spécificités de MariaDB.

McIntosh Aug 14, 2014

Disclosure -- I received a copy of this book from the publisher for review.The MariaDB Cookbook is well written by someone who knows the MariaDB database server quite well. Writing a 300 page book by yourself is no small feat and Daniel has done it with aplomb. I am most familiar with Percona server and Oracle's MySQL server but I have been interested in MariaDB for some of its new features -- especially the multi-source replication. I took a look at this section in particular and it was well done with clear step by step instructions on how to accomplish multi-source replication. Each of the recipes follow this pattern with clear, concise steps. My only complaint about the cookbook items are that they might be that they are a little too concise at times -- although to Daniel's credit there are frequent See Also sections that point to deeper details.As I read through the cookbook I kept thinking "this needs a comparison chart". For those who aren't familiar with the MySQL ecosystem there are three primary systems: MySQL Server (by Oracle), Percona Server (by Percona) and MariaDB which is more of a community-driven server supported primarily by the MariaDB Foundation and SkySQL. There are many similarities of course, and a database administrator would have no issue with moving from system to system. However, there are enough differences that a comparison chart would be very useful for the reader. I assume that the author and publisher feel that a comparison chart isn't relevant, but a one page chart in the front of the book comparing systems would be useful for a beginner.This book would be very useful in my consulting business. An intermediate level consultant could take this cookbook and use it to perform many tasks with minimal oversight. It's a great reference manual for common problems that come up in your day to day administration work.Keith Murphy

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.

Daniel Lee May 26, 2014

The MariaDB Cookbook says it's a book for people wanting to get to know MariaDB, specifically if they already have experience with databases and want to know what sets MariaDB apart. True to its word, the book starts by introducing a concise but comprehensive set of recipes ranging from simple backups to the first steps in setting up a highly performant, secure and scalable database infrastructure.I would highly recommend this book to anyone who already has set up and/or administrated a database and is considering taking a closer look at MariaDB, or for anyone who has done simple things with MariaDB and would like to see a few options on how to scale their infrastructure. I found the book contained quite a few inspiring chapters on how to tune performance or usability in ways tailored to your specific needs. It also has some really interesting sections on setting up durable database clusters for the purpose of redundance, load balancing, etc. I would not recommend it to anybody who is unfamiliar with databases in general or to people who already know what they want to do and are wanting to understand the theory and processes involved on a more in-depth level. This is not the MariaDB Cookbook's intended audience.That said, if you are looking for quick tutorials that explain exactly what you need to know - and not more - in order to familiarize yourself with MariaDB, you'll find the MariaDB Cookbook fits your needs precisely. It gives you a well-organized and concise glimpse into the world of MariaDB and also tells you where you can find additional information if you're interested in pursuing the background.Although the author did a good job of explaining the basics, like how to install MariaDB on the various major OS, my favorite chapters were the somewhat more advanced topics. There's lots there about checking and optimizing tables, optimizing queries, installing plugins, switching to different storage engines, avoiding deadlocks and managing your threadpool in the first chapters. They're followed by (among other topics) sections on the very interesting CONNECT engine, which allows accessing data from heterogeneous sources from MariaDB, and various strategies for replicating the database and working with clusters. Shortly afterwards, the author discusses auditing before moving on to more exotic topics, such as virtual and dynamic columns,as well as full-text searches with Sphinx. The book ends by explaining some basic ways of securing your database on different platforms.All in all, a fast read that's well worth your time if you have intermediate database knowledge at the get-go. I will definitely be looking things up in it again when I start my next project requiring a relational database.