Logical backup of database in MySQL
Two types of backup:
Physical backup tools include the mysqlbackup of MySQL Enterprise Backup for InnoDB or any other tables,
or file system-level commands (such as cp, scp, tar, rsync) for MyISAM tables.
Logical backup tools include the mysqldump program. Its generate output in forms of SQL scripts.
–For InnoDB tables, perform an online backup that takes no locks on tables using the –single-transaction option to mysqldump.
–For MyISAM tables, perform backup by copying table files (*.MYD, *.MYI files, and associated *.sdi files). To get a consistent backup, stop the server or lock and flush the relevant tables: FLUSH TABLES tbl_list WITH READ LOCK;
–Incremental backup is possible with help of binary logs, you need to rotate binary log at time of backup using FLUSH LOGS or mysqldump –flush-logs.
Example of taking full logical backup InnoDB tables:
-- Take full backup of all database mysqldump -u root -p --all-databases --master-data --single-transaction > E:\Full_backup.sql -- Take sales and order databases backup mysqldump --databases sales order > E:\dump.sql For InnoDB tables command options: --single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change. --flush-logs option causes the server to flush its logs. --master-data option causes mysqldump to write binary log information to its output. Help in restore. --delete-master-logs option deleting the binary logs that are no longer needed
Restore command used for restore backup file
mysql -u root -p < c:\full_backup.sql Enter password: ********* Table Op Msg_type Msg_text world.city histogram status Histogram statistics created for column 'Name'. Table Op Msg_type Msg_text world.city histogram status Histogram statistics created for column 'District'.
Applied Binary logs as incremental restore:
mysqlbinlog RAC1-bin.000002 RAC1-bin.000003 | mysql Example: mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\RAC1-bin.000003" | mysql -u root -p Enter password: ********* Table Op Msg_type Msg_text world.city histogram status Histogram statistics created for column 'Name'. Table Op Msg_type Msg_text world.city histogram status Histogram statistics created for column 'District'.