The latest from the MySQL community

With MySQL Enterprise Backup(MEB) 3.9.0 we had introduced full instance backup feature for cloning the MySQL server. Now with MEB 3.11.0 we have enhanced the feature by copying all the master-slave setup files like MySQL server binary logs(will be referred as 'binlogs'), binary log index files, relay logs of slave, relay log index files, master info of slave, slave info files. As part of full instance backup, copying of binlog files is default behavior MEB-3.11.0 onwards. DBA should be aware of the fact that current full instance backup is bigger than the backups with old MEB's.

As every event on MySQL production database goes as a entry to binlog files in particular format, binlog files could be huge. Backing of huge binlog and/or relaylog files should not impact the performance of MySQL server. Hence, all the binlog files, …

You’ve probably had some troubles with the shared InnoDB tablespace stored in the ibdata file. Especially when it has grown for some reasons and reached a critical size.

This behavior occurs in some cases, due to excessive rollback segments growth or during a migration from a unique shared tablespace to a file-per-table configuration for example.

In this post, I would like to explain how to shrink the ibdata file after an unwanted file growth in a file-per-table configuration.
Note that the process could be done without Trite but the tool avoids to write the script used to transport tables yourself.

Initial situation

Here is a sample of the InnoDB configuration:

innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_per_table

And the status of your datafiles in the datadir directory:

drwx------ 2 mysql mysql 4,0K déc. 20 2012 …

Introduction

Sometimes the best way to repair data issues and problems within a MySQL database is to restore only some of the tables from a backup. For example, suppose that some data was accidentally deleted from one table due to a software error, then the easiest way to recover the lost data might be to restore only one table from a backup. Previously this kind of partial restore was not supported by MySQL Enterprise Backup (MEB). However, MEB 3.11 introduces support for selective restore from backups created with the --use-tts option (or TTS backups).

TTS backups are backups that are created with the transportable tablespaces feature of InnoDB. These backups consist of InnoDB tables that …

Introduction 

MySQL Enterprise Backup (MEB) is a highly efficient tool for taking backups of your MySQL databases. In the 3.11.0 release, we are taking that one step further by introducing a new concept called "optimistic" backup. Optimistic backup leverages the patterns we saw frequently especially as related to very large databases.

For backups the goals are:

1 - Quality and Consistency - the backup and more importantly the restore just "works".
2 - Size, time, and overhead - like in the game of golf - low score wins - for backups and for
     restores.
3 - Flexibility – It’s not always one size fits all - whether how the backup is run, where it goes,
     how it is recovered.

With optimistic backup - we look at mostly the read aspects of your database to enable us to create a backup that is smaller, faster to backup, faster to …

August 25, 2014 By Severalnines

Are you going in production with Galera Cluster for MySQL? Here are 9 tips to consider before going live. These are applicable to all 3 Galera versions (Codership, Percona XtraDB Cluster and MariaDB Galera Cluster). 

1. Galera strengths and weaknesses

There are multiple types of replication and cluster technologies for MySQL, make sure you understand how Galera works so you set the right expectations. Applications that run on single instance MySQL might not work well on Galera, you might need to make some changes to the application or the workload might not be appropriate. We’d suggest you have a look at these resources: 

• Tutorial on …

On my post last week, I analysed some of the most common compression tools and formats, and its compression speed and ratio. While that could give us a good idea of the performance of those tools, the analysis would be incomplete without researching the decompression. This is particularly true for database backups as, for those cases where the compression process is performed outside of the production boxes, you may not care too much about compression times. In that case, even if it is relatively slow, it will not affect the performance of your MySQL server (or whatever you are using). The decompression time, however, can be critical, as it may influence in many cases the MTTR of your whole system.

Testing …

MySQL Enterprise Backup 3.10 support backups to the cloud. The only supported cloud service is Amazon S3.

When the cloud destination is used mysqlbackup will upload the backup as an image file.

You can specify all options on the commandline:

mysqlbackup --cloud-service=s3 --cloud-aws-region=eu-west-1 \

--cloud-access-key-id=AKIAJLGCPXEGVHCQD27B \

--cloud-secret-access-key=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 \

--cloud-bucket=meb_myserver --cloud-object-key=firstbackup --cloud-trace=0 \

--backup-dir=/tmp/firstbackup --backup-image=- --with-timestamp backup-to-image

But you can also put the settings in the my.cnf

[mysqlbackup_cloud]
cloud-service=s3
cloud-aws-region=eu-west-1
cloud-access-key-id=AKIAJLGCPXEGVHCQD27B
cloud-secret-access-key=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
cloud-bucket=meb_myserver …

I was recently asked about if MySQL Enterprise Backup would be able to restore single databases.

My initial answer was that this was complicated, but might be doable with the Transportable Table Space (TTS) option.

But first let's go back to the basics. A common way of working with mysqldump is to get a list of databases and then loop through the databases and dump the data and schema to a SQL file. But both backups and restores will take a lot of time if the size of the database grows. And it's a luke-warm backup at best instead of a hot backup. So that's why we have MySQL Enterprise Backup.

MySQL Enterprise Backup allows you to make a hot backup of InnoDB tables by copying the datafiles while watching the InnoDB redo log files.

On disk the data from the InnoDB storage engine consists of a system tablespace (one of more ibdataX files), the redo log files (iblogfileX) and zero or more …

1. Backup script

#!/bin/sh
# Fri Jun 27 10:44:49 2014
# done by dragkh
# usage: 
# cat /etc/cron.d/backupmysql 
# 0  3  *  *  *       root    /root/bin/clean.backup.hyperion.mysql.mydumper.daily.sh >>  /var/log/clean.backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1
# 35  3  *  *  *       root    /root/bin/backup.hyperion.mysql.mydumper.daily.sh >> /var/log/backup.${HOSTNAME}.mysql.mydumper.daily.log 2>&1

ROOT_BACKUP_DIR="/home/mydumper"

seik_date () {
if [ -z $1 ]
then
# cdate=`date +%Y-%m-%d\ %H:%M:%S\ %Z`; export cdate; echo $cdate
cdate=`date -R`; export cdate; echo $cdate
else

if [ -z ${2} ]
then
cdate=`date +%Y-%m-%d.%H.%M.%S`; export cdate; echo $cdate
else
cdate=`date "+%Y-%m-%d %H:%M:%S"`; export cdate; echo $cdate
fi

fi
}


function check_dir {
 test ! -d "${1}" && mkdir -p "${1}"
}


function set_cpu_threads {
    # set the threads one less than the existing 
    threads=$(cat /proc/cpuinfo  |  grep processor | tail -1 | awk …

Background:

Prior to version 3.10, MySQL Enterprise Backup (MEB) used zlib compression for in-memory compression of datafiles. The compression worked by splitting the innodb datafiles into fixed size blocks and compressing each block independently.After searching on the web we found there are many compression algorithms available which can be used for compression. This triggered the idea of testing the performance of available compression algorithms. If the benchmark shows improved performance we can make backup and/or restore faster by adding the new compression algorithm to MEB.

Implementation :

The idea to implement the algorithms procceded as follows .

1. Select a "long list" of algorithms based on literature and what Google and other databases are using.
2. Create a prototype of MEB supporting the algorithms in the long list.
3. Run comparison tests of algorithms with the MEB …