Backup & Restore in MySQL

-- Logical backup with mysqldump
mysqldump -u root -p database_name > backup.sql
mysqldump -u root -p --all-databases > all_backup.sql

-- Backup with compression
mysqldump -u root -p database_name | gzip > backup.sql.gz

-- Backup specific tables
mysqldump -u root -p database_name table1 table2 > tables.sql

-- Restore from backup
mysql -u root -p database_name < backup.sql

-- DETAILED_TAB:
-- Consistent backup with single transaction
mysqldump --single-transaction --quick \
  --lock-tables=false -u root -p database > backup.sql

-- Binary backup with Percona XtraBackup
xtrabackup --backup --target-dir=/backup/
xtrabackup --prepare --target-dir=/backup/
xtrabackup --copy-back --target-dir=/backup/

-- Point-in-time recovery
-- 1. Restore full backup
mysql -u root -p < full_backup.sql
-- 2. Apply binary logs
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

-- Export/Import with MySQL Shell
mysqlsh -u root -p
util.dumpSchemas(['database'], '/backup/dir')
util.loadDump('/backup/dir')

-- Verify backup
mysql -u root -p -e "SELECT COUNT(*) FROM database.table"