–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 employees3;
drop database employees9;

And now prepare my backup to be applied:

mysqlbackup --backup-dir=/home/mysql/MEB/restore/2014-02-04_23-09-42/ apply-log

(Note: There’s a little hint in the log output that says:

"mysqlbackup: INFO: Backup was originally taken with the --include regexp option"


And now, to restore:

mysqlbackup --defaults-file=/home/mysql/MEB/restore/2014-02-04_23-09-42/backup-my.cnf -uroot -poracle \
--socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore/2014-02-04_23-09-42 \
--datadir=/opt/mysql/5615/data copy-back

sit back, and watch all the schemas and tables be restored:

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
140204 23:43:02 mysqlbackup: INFO: Copy-back operation starts with following threads
                1 read-threads    1 write-threads
140204 23:43:02 mysqlbackup: INFO: Creating table: employees.departments.
140204 23:43:03 mysqlbackup: INFO: Creating table: employees.dept_emp.
140204 23:43:04 mysqlbackup: INFO: Creating table: employees.dept_manager.
140204 23:43:04 mysqlbackup: INFO: Creating table: employees.employees.
140204 23:43:05 mysqlbackup: INFO: Creating table: employees.salaries.
140204 23:43:06 mysqlbackup: INFO: Creating table: employees.titles.
140204 23:43:06 mysqlbackup: INFO: Creating table: employees1.departments.
140204 23:43:07 mysqlbackup: INFO: Creating table: employees1.dept_emp.
140204 23:43:08 mysqlbackup: INFO: Creating table: employees1.dept_manager.
140205 00:01:04 mysqlbackup: INFO: Importing table: employees9.titles.
140205 00:01:09 mysqlbackup: INFO: Copy-back operation completed successfully.
140205 00:01:09 mysqlbackup: INFO: Finished copying backup files to '/opt/mysql/5615/data/'

mysqlbackup completed OK!

And that’s it! All restored.

And if we want to get creative with the backup options, not only can we get all the ‘salaries’ tables from all the employeesn databases, but we could also backup as many databases as we want, using the regexp connotation such that:

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

will work too.

Note: When taking these types of backups, please take into consideration the requirements.


About Keith Hollman

Focused on RDBMS' for over 25 years, both Oracle and MySQL on -ix's of all shapes 'n' sizes. Small and local, large and international or just cloud. Whether it's HA, DnR, virtualization, containered or just plain admin tasks, a philosophy of sharing out-and-about puts a smile on my face. Because none of us ever stop learning. Teams work better together.
This entry was posted in Backup, MEB, MySQL, MySQL Enterprise Backup, Recovery and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s