Showing entries 1 to 10 of 20
10 Older Entries »
Displaying posts with tag: MySQL Enterprise Backup (reset)
MySQL Enterprise Transparent Data Encryption (TDE) – provides at-rest encryption for physical InnoDB tablespace data files

With MySQL version 5.7.11 and up, Oracle continues to improve MySQL’s security features by adding MySQL Enterprise Transparent Data Encryption (TDE) for InnoDB tables stored in innodb_file_per_table tablespaces. This feature provides at-rest encryption for physical tablespace data files.

MySQL Enterprise TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted with the master key and the encrypted value of the tablespace key is stored in the tablespace header. When encrypting tablespace data, InnoDB transparently uses the master encryption key …

[Read more]
Throttling MySQL Enterprise Backup with cgroups

Today I encountered a situation where MySQL Enterprise Backup caused to much load on the I/O subsystem of the server to cause the application to be so slow that it wasn't usable any longer. So I wanted to limit the mysqlbackup process so it wouldn't cause any more issues.

The mysqlbackup command has settings to for the number of read, write and process threads. The defaults are 1 read, 1 write and 6 process threads. So that isn't really useful for throttling as I was using the defaults.

Using the ionice utility wouldn't work as that requires the CFG I/O scheduler.

I found a solution in this blog post. It is to use cgroups on Linux. I had used cgroups before to test how a galera setup works when one of the three servers had a much slower CPU.

# mkdir /cgroup/blkio
# mount -t …
[Read more]
The new cloud backup option of MySQL Enterprise Backup

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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 …
[Read more]
Which Compression Tool Should I Use for my Database Backups? (Part I: Compression)

This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.

I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?

The testing environment

In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250 …
[Read more]
Upgrade MySQL to a new version with a fresh installation & use shell scripts and mysqldump to reload your data

There are several ways to upgrade MySQL. In this post, we will use a combination of shell scripts and the mysqldump application to export our MySQL data, and then re-import it back into the upgraded version of MySQL.

In this example, we will be doing a minor version upgrade. We will be going from 5.6.17 to 5.6.19. This method may not work if you are upgrading from one major release to another – from 5.1 to 5.5, or 5.5 to 5.6. You will want to check each version and review the new features/functions and also what features/functions have been deprecated. We are also assuming that no one will be using the database during the time it takes for us to do the upgrade.

If you want to upgrade from a …

[Read more]
Single database backup and restore with MEB

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 …

[Read more]
Using mysqldump and the MySQL binary log – a quick guide on how to backup and restore MySQL databases

Be sure to check out my other posts on mysqldump:
Scripting Backups of MySQL with Perl via mysqldump
Splitting a MySQL Dump File Into Smaller Files Via Perl
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2
– Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2

I have already written several posts on using mysqldump. This post will be a quick guide to using …

[Read more]
MySQL Enterprise Backup 3.10: Teasing compression.

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

 

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp …
[Read more]
MySQL Enterprise Backup 3.10: Teasing compression.

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

 

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp …
[Read more]
–use-tts backup & restore

In addition to my recent post, I just had to go into using the –use-tts for specific tables and selective backup sets.

As all my schemas were employeesn, I thought it would be a good idea to run:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees* backup

as I want all the tables. If I only wanted a specific table, say ‘salaries’ I could have done:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees*\.*salaries backup

and backup just the ‘salaries’ tables but from all the employeesn databases.

then.. be a mean little dba:

drop database employees;
drop database employees10;
drop database employees11;
..
..
drop database employees2;
drop database employees20;
drop database …
[Read more]
Showing entries 1 to 10 of 20
10 Older Entries »