MySQL Backup and Restore on Debian 12 Server

In this tutorial, you will learn how to create a MySQL backup on a Debian 12 server, store it in a named folder, and later restore the backup. The backup will be named based on the date and time.

What You Need

  • A Debian 12 server with MySQL or MariaDB installed.
  • SSH access to your server.

Step-by-Step Guide

Step 1: SSH Access to Your Server

Connect to your Debian 12 server via SSH using the following command, replacing username and server-IP with your own information:

1ssh username@server-IP

Enter your SSH password when prompted.

Step 2: Log in to MySQL

Use the following command to log in to the MySQL database. You will be prompted for the MySQL password:

1mysql -u root -p

Step 3: Select the Database

Select the database you want to back up. For example, to select the mydb database, use:

1USE mydb;

Replace mydb with the name of your database.

Step 4: Create a MySQL Dump

Create a MySQL dump with the following command. The dump will be created in the current directory.

1mysqldump -u root -p mydb > mydb-$(date +%Y%m%d%H%M%S).sql

This command creates an SQL dump with the name "mydb-YYYYMMDDHHMMSS.sql", where the date and time are inserted into the filename.

Step 5: Move Dump to a Folder

Move the created dump to a dedicated folder. For example, to move it to a folder named "backups," use:

1mkdir -p backups
2mv mydb-*.sql backups/

Step 6: Restore MySQL Backup

To restore a MySQL backup, navigate to the folder where your backup is stored and use the following command:

1mysql -u root -p mydb < mydb-YYYYMMDDHHMMSS.sql

Replace mydb-YYYYMMDDHHMMSS.sql with the actual filename of your backup. You will be prompted for the MySQL password.

Step 7: Summary

You have successfully created a MySQL backup on your Debian 12 server, stored it in a named folder, and restored the backup. Remember to replace mydb with your specific database name.

Conclusion

Creating regular backups and knowing how to restore them is crucial for protecting your data on a server. This tutorial allows you to create and restore MySQL dumps efficiently. Be sure to perform backups regularly and customize the database name as needed to prevent data loss.