mysqldumpThis guide explains how to use mysqldump for backing up MySQL databases and restoring them. mysqldump is a utility that performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database schema and data.
Lock All Tables: This method locks all tables to ensure data consistency during the backup. Note that this will prevent any write operations on the databases until the backup completes.
mysqldump -u root -p --lock-all-tables --all-databases --events > mysql_dump.sql
Enter your MySQL root password when prompted.
Without Locking Tables: Use the --single-transaction option to dump data without locking the tables. This option is suitable for InnoDB tables as it ensures data integrity by creating a consistent snapshot without locking reads.
mysqldump -u root -p --single-transaction --all-databases --events > mysql_dump.sql
Enter your MySQL root password when prompted.
To backup a specific database, specify the database name instead of using --all-databases. This command also uses the --single-transaction option to avoid locking the tables.
mysqldump -u root -p --single-transaction test_database --events > mysql_dump.sql
Enter your MySQL root password when prompted.
Before restoring, ensure the backup file (mysql_dump.sql) is transferred to the target host using tools like rsync or scp.
To restore all databases from the dump file, use the following command. This will import all SQL statements from the file into MySQL.
mysql -u root -p < mysql_dump.sql
Enter your MySQL root password when prompted.
If the backup file contains a specific database, create an empty database with the same name on the target MySQL server before importing the file.
mysql -u root -p -e "CREATE DATABASE test_database"
mysql -u root -p test_database < mysql_dump.sql
Enter your MySQL root password when prompted.