How to Backup and Restore MySQL Database

Backing up and restoring MySQL databases is essential for data protection and disaster recovery. There are multiple methods to perform backups and restores, but I’ll provide you with two common approaches: using mysqldump for logical backups and using file copies for physical backups.

Before proceeding with any backups or restores, make sure you have appropriate privileges, and if you’re performing a restore, ensure that you have a recent backup file.

Method 1: Logical Backup using mysqldump

Backing up a MySQL Database:

To create a logical backup of a MySQL database using mysqldump, follow these steps:

Open a terminal or Command Prompt and run this for a single database backup:

$mysqldump -u your_username -p your_database_name > backup.sql

Replace your_username with your MySQL username, and your_database_name with the name of the database you want to back up. You will be prompted to enter your MySQL password.

For multiple databases backup (useful when backing up multiple databases):

$mysqldump -u your_username -p --databases db1 db2 db3 > backup.sql

Replace your_username, db1, db2, db3, etc. with your MySQL username and the names of the databases you want to back up.

Restoring a MySQL Database:

To restore the logical backup created by mysqldump, follow these steps:

Open a terminal or Command Prompt.

First, create a new empty database (if it doesn’t exist already):

$mysql -u your_username -p -e "CREATE DATABASE your_database_name;"

Replace your_username with your MySQL username and your_database_name with the name of the database you want to restore.

Then, restore the data from the backup file into the newly created database:

$mysql -u your_username -p your_database_name < backup.sql

Replace your_username with your MySQL username and your_database_name with the name of the database you want to restore. You will be prompted to enter your MySQL password.

Method 2: Physical Backup using File Copies

Backing up a MySQL Database:

For physical backups, you need to copy the MySQL data directory to a safe location. The data directory typically contains subdirectories with the database names, and each subdirectory represents a database.

First, stop the MySQL server to ensure a consistent backup:

$sudo service mysql stop
# or
$sudo systemctl stop mysql   # On Linux (Ubuntu/Debian)

$sudo systemctl stop mysqld   # On Linux (CentOS/RHEL)

Then, copy the data directory to the backup location (e.g., /path/to/backup/):

$sudo cp -r /var/lib/mysql /path/to/backup/

Replace /path/to/backup/ with the path to your backup destination.

Finally, start the MySQL server again:

$sudo service mysql start
# or 
$sudo systemctl start mysql   # On Linux (Ubuntu/Debian)

$sudo systemctl start mysqld   # On Linux (CentOS/RHEL)

Restoring a MySQL Database:

To restore a physical backup, follow these steps:

First, stop the MySQL server:

$sudo service mysql stop   
# or
$sudo systemctl stop mysql   # On Linux (Ubuntu/Debian)

$sudo systemctl stop mysqld   # On Linux (CentOS/RHEL)

Then, remove the current MySQL data directory (backup your data before performing this step):

$sudo rm -r /var/lib/mysql

Next, copy the backed-up data directory back to the original location:

$sudo cp -r /path/to/backup/mysql /var/lib/

Replace /path/to/backup/mysql with the path to your backed-up data directory.

Finally, start the MySQL server again:

$sudo service mysql start
# or
$sudo systemctl start mysqld   # On Linux (Ubuntu/Debian)

$sudo systemctl start mysqld   # On Linux (CentOS/RHEL)

Please note that the paths and commands may vary based on your MySQL installation and operating system. Always be cautious when performing backups and restores to avoid accidental data loss. It’s also a good practice to test your backups regularly to ensure they are working correctly.

Found this article interesting? Follow Brightwhiz on Facebook, Twitter, and YouTube to read and watch more content we post.