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.

Note:
–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'.