How to Backup and Restore MySQL Database in VPS
This article explains how to back up and restore a MySQL database on a VPS. First, log in to your VPS and open a command prompt or terminal. Next, run the `mysqldump` command with your database credentials to create a backup file, then verify the file was created. To restore, make sure the target database exists, then run the `mysql` command to import the backup file. Finally, log in to MySQL and check the tables to confirm the data has been restored.
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.sqlStep 4: Verify Backup File
- Check that the
.sqlfile 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].sqlis the backup file you created earlier.
Example:
mysql --user=root --password=MyPass123 mydb < mydb_backup.sqlStep 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
Outline