Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 216 10 Older Entries

Displaying posts with tag: Backup (reset)

MEB copies binary logs and relay logs to support PITR and cloning of master/slave
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
How to shrink the ibdata file by transporting tables with Trite
+0 Vote Up -0Vote Down

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

  [Read more...]
Selective Restore of InnoDB Tables with MySQL Enterprise Backup 3.11
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
Optimistic Backup
Employee +2 Vote Up -0Vote Down
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







  [Read more...]
9 Tips for Going in Production with Galera Cluster for MySQL
+0 Vote Up -0Vote Down
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.

  [Read more...]
Which Compression Tool Should I Use for my Database Backups? (Part II: Decompression)
+0 Vote Up -0Vote Down

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

  [Read more...]
The new cloud backup option of MySQL Enterprise Backup
+1 Vote Up -0Vote Down
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










  [Read more...]
Single database backup and restore with MEB
+3 Vote Up -0Vote Down
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







  [Read more...]
MySQL backup and cleanup bash scripts with mydumper
+1 Vote Up -0Vote Down

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
  [Read more...]
MySQL Enterprise Backup Improved Compression Algorithm for 3.10
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
Showing entries 1 to 10 of 216 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.