How to Backup and Restore MySQL Database in VPS

Introduction

Managing your database properly is crucial for data security and disaster recovery. This guide will show you step by step how to back up and restore a MySQL database inside your VPS using command-line tools. The instructions are written for beginners, so even if you have never worked with MySQL before, you can follow along easily.

Steps to Backup MySQL Database

Step 1: Log in to your VPS

• Connect via SSH or Remote Desktop, depending on your VPS type.

Step 2: Open Command Prompt or Terminal

• On Windows VPS: Open Command Prompt (cmd).
• On Linux VPS: Open Terminal.

Step 3: Run the Backup Command

Use the following syntax (replace placeholders with your details):

mysqldump --user=[DatabaseUserName] --password=[Password] [DatabaseName] > [DatabaseBackupFile].sql

[DatabaseUserName]: Your MySQL username
[Password]: Your MySQL password
[DatabaseName]: The database you want to backup
[DatabaseBackupFile].sql: The name of your backup file, e.g. backup.sql

Example:

mysqldump --user=root --password=MyPass123 mydb > mydb_backup.sql

Step 4: Verify Backup File

• Check that the .sql file is created in your chosen directory.
• This file contains all SQL statements needed to recreate your database.

Steps to Restore MySQL Database

Step 1: Log in to your VPS

Connect via SSH or Remote Desktop.

Step 2: Open Command Prompt or Terminal

Step 3: Run the Restore Command

Use the following syntax:

mysql --user=[DatabaseUserName] --password=[Password] [DatabaseName] < [DatabaseBackupFile].sql

• Note:
  • [DatabaseName] must already exist in MySQL before running restore.
  • [DatabaseBackupFile].sql is the backup file you created earlier.
    Example:

mysql --user=root --password=MyPass123 mydb < mydb_backup.sql

Step 4: Confirm Restore

• Log in to MySQL with:

mysql -u root -p

• Run SHOW TABLES; inside the restored database to confirm the data is back.

Related Keywords:

backup mysql database, restore mysql database, mysql backup, mysql restore, database backup, mysqldump command tutorial, mysql restore from sql file, how to backup mysql database command line, mysql database backup and recovery, VPS MySQL data protection, restore mysql database step by step