MySQL and MySQL Community information

  • March 19, 2015

Distributing innodb tables made simpler!

With the support for cloud backups in MEB, distributing innodb tables across multiple MySQL instances has become easier.

1. Backup - take a cloud(Amazon S3) backup of the tables to be shared using the --use-tts=with-full-locking option.

./mysqlbackup \
--host=localhost --user=mysqluser --protocol=TCP --port=3306 \
--cloud-service=s3 --cloud-aws-region=us-east-1 \
--cloud-bucket=mebbackup –cloud-object-key=items.img \
--cloud-access-key-id=<access-key> --cloud-secret-access-key=<secret-key> \
--include-tables=^mycompany\.items.* --use-tts=with-full-locking \
--backup-dir=/tmp/bkups/backupdir --compress --backup-image=- \

(here the regex used will backup the tables whose name match “items*” in the database “mycompany”.)

2. Change Permissions - from the Amazon S3 (web) console, change the permissions of the backup file.

For a restore by users without S3 accounts, the file “Open/Download” permissions should be granted to “Everyone”. To restrict restore only to users with valid S3 accounts grant “Open/Download” permissions for “Authenticated Users”.

3. Restore - any number users(/MySQL applications) from anywhere (with/without S3 accounts) can use MEB to restore the new tables into a running MySQL instance.

./mysqlbackup \
--host=localhost --user=mysqluser --protocol=TCP --port=3306 \
--cloud-service=s3 --cloud-aws-region=us-east-1 \
--cloud-bucket=mebbackup –cloud-object-key=items.img \
--backup-dir=/tmp/bkups/cpbackdir --uncompress \
--datadir=/home/mysql-5.6/datadir --backup-image=- \

(options --cloud-access-key-id=<access-key> --cloud-secret-access-key=<secret-key> needed if “Open/Download” permissions are set for “Authenticated Users”)

Make sure the tables being imported do not exist in the target database. This operation is same as importing tables from a backup taken with --use-tts option.

4. Done!

Join the discussion

Comments ( 2 )
  • guest Monday, March 23, 2015

    Your commands use "--backup-image=-" without redirecting standard output (on backup) or input (on restore), so in ordinary Unix/Linux convention the backup image would be written to (read from) your screen.

    I strongly doubt this will work at all, you better specify a true file name.

    If, however, "mysqlbackup" does not assign the meaning "standard output / input" to the name '-', you have silently shown what I consider a bug.

  • Maheedhar Pv Tuesday, March 24, 2015

    As of meb-3.12.0, the cloud options take precedence, i.e. specifying any of the "--cloud-*" options makes MEB treat it as a cloud operation.

    The backup file name to be used on the cloud is to be specified by a "--cloud-" option. Any value represented by "--backup-image=<value>" is not interpreted by MEB(in case of cloud operations), and the above commands would work correctly as expected.

    I agree, this dependency on the unused option of "--backup-image=<value>" can be done away with, if possible and thanks a lot for pointing it.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha