FreeBSD 14
Sponsored Link

MySQL 8.0 : Backup with mysqldump2024/09/03

 

For Backup and Restore MySQL Data, it's possible to run with [mysqldump].

[1] Run [mysqldump] to take dump data of MySQL.
# lock all tables and dump all data in MySQL
# during dumping data, reading is also locked, so actually impossible to use Databases

root@dlp:~ #
mysqldump --lock-all-tables --all-databases --triggers --routines --events --set-gtid-purged=OFF > mysql_dump.sql

# dump all data without locking but with transaction
# ensured data integrity by [--single-transaction] option

root@dlp:~ #
mysqldump --single-transaction --all-databases --triggers --routines --events --set-gtid-purged=OFF > mysql_dump.sql

# dump specific database

root@dlp:~ #
mysqldump test_database --single-transaction --triggers --routines --events --set-gtid-purged=OFF > mysql_dump.sql

[2] For restoring data from backup on another host, run like follows.
Before restoring, transfer dump data to the target host with [rsync] or [scp] and so on.
# for all dumped data, simply import a file

root@node01:~ #
mysql < mysql_dump.sql

# for dumped data with specific database,
# create a empty database first with the same DB name and next, import a file

root@node01:~ #
mysql test_database < mysql_dump.sql

Matched Content