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.