---
title: How to Create a Backup of MySQL Databases in your VPS
description: "MySQL backups are mostly created as .sql dump files. These are flexible and can be imported to another MySQL instance or transferred$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction \"$dbname\" > /var/backups/databases/\"$dbname\".sql;…"
url: "https://truehost.com/support/knowledge-base/how-to-create-a-backup-of-mysql-databases-in-your-vps/"
updated: "2024-06-07"
---

# How to Create a Backup of MySQL Databases in your VPS

MySQL backups are mostly created as .sql dump files. These are flexible and can be imported to another MySQL instance or transferred$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /var/backups/databases/"$dbname".sql; done around for safe keeping.

The guide below will help you make backups of mysql

## Using phpMyAdmin

If your server is running phpMyAdmin, you can use it to create backups as follows

1.Login to phpMyAdmin

2.In the left column, click the name of the database you wish to backup.

3.Click on the **Export** tab on the right.

4.Choose the tables you wish to backup, or click **Select All** to backup all of your tables.

5.Scroll down and ensure the **Save as File** box is checked off. Click the **Go** button found in the lower right corner. You will be prompted to find a place on your hard drive to store the backup file, and then the download will begin.

## Using SSH

You can run mysqldump command to backup your database.

1.Login to the server via SSH as root user

2.Create a folder you want to save your backup files. In this case, we will call this folder /var/backups/databases

To create a backup for a single database, run this command

```
$ mysqldump database_name > database_name.sql
```

To create a backup of several databases, with each database having its own mysql file, run the command below

```
$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /var/backups/databases/"$dbname".sql; done
```

You will see a list of .sql files created and saved on the specified folder.

**Note:** The commands above need to be run as root user. Otherwise, you would need to specify database usernames and passwords on the commands.

If you still get asked for a password, use the commands below and type in a password when prompted.

For single database backup:

```
$ mysqldump -uroot -p database_name > database_name.sql
```

For all databases, use this command

```
$ mysql -uroot -pPASS_HERE -N -e 'show databases' | while read dbname; do mysqldump -uroot -pPASS_HERE --complete-insert --routines --triggers --single-transaction "$dbname" > /var/backups/databases/"$dbname".sql; done
```

Replace **PASS_HERE** with your actual mysql password. This prevents MySQL prompting you for passwords since if databases are many, you will have to enter the passwords very many times which is not practical.