Create MySQL Database Backup | mysqldump mysqlbackup

Here, we are going to discuss about creating mysql database backup including table backup, backup of all database 

mysqldump and mysqlbackup

Physical and Logical backup

Physical backup is taking backup of database directories while database is closed (cold backup) or open (hot backup)

Logical backup is more of extracting SQL statements of INSERT, CREATE, and ALTER etc. statements. 

Below we are going to discuss about logical backup methods mysqldump and mysqlbackup (Enterprise) 

MYSQLDUMP: 

Using mysqldump one take backup of single database, multiple databases, all databases, single table or multiple tables. 

one disadvantage with mysqldump is that it takes lot of time to restore as it have created and insert statements.

Syntax

mysqldump -u <username> -p --all-databases --single-transaction > all_databases.sql

above command will help to take backup of all databases.

Taking backup of single database

mysqldump -u root -p adminDB > adminDB_bckup_dump.sql

Taking backup of mysql table 

mysqldump -u root -p  adminDB UserInfo > table_UserInfo_bckup_dump.sql

Backing up multiple tables 

mysqldump -u root -p  adminDB UserInfo AdminElement> table_UserInfo_bckup_dump.sql

Where adminDB is database and UserInfo and AdminElement are tables

Backup database is compress it using zip or gzip 

mysqldump -u root -p adminDB |gzip > adminDB_bckup_dump.sql.gzip

MYSQLBACKUP:

Below privileges are required to run mysqlbackup command.

GRANT RELOAD ON *.* TO 'test_usr'@'localhost';

GRANT CREATE, INSERT, DROP ON mysql.ibbackup_binlog_marker TO 'test_usr'@'localhost';

GRANT CREATE, INSERT, DROP ON mysql.backup_progress TO 'test_usr'@'localhost';

GRANT CREATE, INSERT, SELECT, DROP ON mysql.backup_history TO 'test_usr'@'localhost';

GRANT REPLICATION CLIENT ON *.* TO 'test_usr'@'localhost';

GRANT SUPER ON *.* TO 'test_usr'@'localhost';

GRANT CREATE TEMPORARY TABLES ON `mysql.*` TO 'test_usr'@'localhost';

FLUSH PRIVILEGES;

In below example we are using root user so not required to grant anything.

Syntax:  

$ mysqlbackup --port=3306 --protocol=tcp --user=<user> --password=<password> --with-timestamp --backup-dir=/u01/backup_t backup-and-apply-log

For example: 

mysqlbackup --port=3306 --protocol=tcp --user=root --password=DexRt#75 --with-timestamp --backup-dir=/u01/backup_t backup-and-apply-log

Where 

--user--> is account having all privileges to perform mysqlbackup operation

--with-timestamp--> this will create sub directory under backup directory mentioned in options

--backup-dir--> is directory where backup needs to take.

--backup-and-apply--> this will bring all tables up to date with changes occurred during backup in short it will take consistent backup.