Handling MySQL backups and restores
8:45 PM
MySQL backups using options of mysqldump and restores(imports) using mysql client utility.
Daily automated script takes backup of all databases using:
IF the databases are DB_1, DB_2, DB_3 etc., the above command will
backup all these DB's including system DB's mysql, information_schema
& performance_schema(from version 5.5 onwards).
So the backup file will have statements like
If we have to restore all the DB's including system DB's we can run:
The above we generally use if we have to setup a new slave server or redo existing slave server if there are slave issues.
In other scenarios where you just have to restore only one or two DB's and not overwrite system DB's
we need to restore as below
If we have to restore DB_1 to a different schema(DB) name for eg., DB_1_backup we will have to:
Daily automated script takes backup of all databases using:
mysqldump -uopsback -p<opsbackup-password> --single-transaction --opt --routines --triggers --all-databases --master-data=2 > /data/mysql-backups/alldb-backup-%date%.sql |
So the backup file will have statements like
create database DB_1 if not existuse DB_1( drop table table_name if exists create table table_name insert into table_name.. ) for all tablescreate database DB_2 if not existsuse DB_2....etc., |
mysql -uopsbackup -p<opsbackup-password> < alldb-backup-%date%.sql |
In other scenarios where you just have to restore only one or two DB's and not overwrite system DB's
we need to restore as below
For DB_1 onlymysql -uopsbackup -p<opsbackup-password> --one-database DB_1 < alldb-backup-%date%.sqlFor Db_2 onlymysql -uopsbackup -p<opsbackup-password> --one-database DB_2 < alldb-backup-%date%.sql |
backup single-dbmysqldump -uopsback -p<opsbackup-password> --single-transaction --opt --routines --triggers DB_1 > /data/mysql-backups/DB_1-%date%.sqlthe important thing to note here is when you dump single Db there will not be statements likecreate database DB_1 if not existsuse DB_1in the backup file DB_1-%date%.sqlTo restore to a different schema DB_1_backup run:mysql -uopsbackup -p<opsbackup-password> DB_1_backup < DB_1-%date%.sql
0 comments