Full Backup using MySQL Enterprise Backup

Install the software

Before we can configure backups and the like, you’ll need to install the MySQL Enterprise Backup software:

$ tar xvzf meb-3.9.0-linux2.6-x86-64bit.tar.gz 
meb-3.9.0-linux2.6-x86-64bit/
meb-3.9.0-linux2.6-x86-64bit/bin/
meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup
meb-3.9.0-linux2.6-x86-64bit/README.txt
meb-3.9.0-linux2.6-x86-64bit/LICENSE.mysql
meb-3.9.0-linux2.6-x86-64bit/manual.html
meb-3.9.0-linux2.6-x86-64bit/mysql-html.css

I then placed the mysqlbackup binary in my MySQL “bin” directory :

$ cp meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup /usr/local/mysql/bin/
$ which mysqlbackup 
/usr/local/mysql/bin/mysqlbackup

Now we’ve installed the software, we can go on and prepare our database for backup.

How to Back Up MySQL Server using MySQL Enterprise Backup (MEB)

 In order to be able to use MySQL Enterprise Backup to perform a backup of a MySQL Server instance, the following MySQL options must be specified in the configuration file of the server instance, under the [mysqld] section (substitute ... with any valid values):

datadir=...
innodb_data_home_dir=...
innodb_data_file_path=...
innodb_log_group_home_dir=...
innodb_log_files_in_group=...
innodb_log_file_size=...

Alternately you can use a new configuration file, that contains just the options above, and pass it to MySQL Enterprise Backup.

My.cnf examples

[root@kiloccnp ~]# cat /etc/my.cnf
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.

[mysqld]
datadir=/usr/local/mysql/data
#tmpdir=/var/log/mysqld/
tmpdir=/db/mytmp
log-error=/var/log/mysqld/mysqld.err
# as of MySQL 5.1.29, log-slow-queries is deprecated, use the 2 options below
#log-slow-queries=/var/log/mysqld/mysqld-slow.log
slow-query-log=1
slow-query-log-file=/var/log/mysqld/mysqld-slow.log
performance_schema_consumer_events_statements_history_long = ON

How To Setup MariaDB Galera Cluster

MariaDB is a relational database management system (RDBMS) and  MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines. This article explains how to setup MariaDB Galera Cluster 10.0 with 2 nodes running on CentOS 6.5 x86_64 resulting in a HA (high-availability) database cluster.

How to Deploy and Configure MaxScale for SQL Load Balancing with Read-Write Split

There are two models of load balancing: transport and application layer. HAProxy is a great TCP load balancer, but it’s lack of SQL awareness effectively limits its ability to address certain scaling issues in distributed database environments. In the open source world, there’s been a few SQL-aware load balancers, namely MySQL Proxy, ProxySQL and MaxScale, but they all seemed to be in beta status and unfit for production use. So we were pretty excited when the MariaDB team released a GA version of MaxScale earlier this year. In this blog, we’ll have a look at MaxScale and see how it compares with HAProxy.