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.
MySQL-Med 