Backup MySQL database via SSH without PhpMyAdmin

Backup MySQL database via SSH without PhpMyAdmin

Backup MySQL database via SSH without PhpMyAdmin

I am a PhpMyAdmin fan but one day when I need a quick backup my PhpMyAdmin failed to load and I wondered how do I backup my database which was huge in size and un encrypted backups was sized several GB’s

Here is what i did..

Log in to SSH (root access required)

If you dont have root/shell access login to WHM or cpanel or plesk panel and enable shell access.

If you are using linux desktop open terminal and type 

if you are on windows download ssh utility like PuTTy

Insert password and connect to your server

After successfully logging in to the SSH next step is to create  the directory where you want to save your backups.

 mkdir /home/username/backups 

Remember to replace your username with username of your site.. in my case its “itshacked”

Then browse to that directory

 cd /home/username/backups/ 

If you don’t want to follow above steps you can create directory by logging in via FTP client such as FileZilla

Next Step is to make our backup directory writeable. If you are using filezilla right click on the directory and change permissions and set them to 777

if you are using SSH as explained above write this command on console

 chmod 777 /home/username/backups/ 

Then the final step is to dump (import) the database using mysqldump here the command goes.

mysqldump --add-drop-table -u db_username -p db_name > mybackup.sql

About the command:

  • Omit the –add-drop-table argument if you’ll want to merge this backup with an existing database upon restore.
  • Replace db_username with the name of your database user.
  • Replace db_name with the name of your database.
  • Replace mybackup.sql with the name of your backup file.

Note: You can find your database name, user, and password information in your wp-config.php file (if you are using WordPress  CMS) for other CMS its usually config.php or configuration.php file.

Enter your database password when prompted and your backups shall be automatically created and you can download from the respective folder.

Have a Happy Backups !!! 🙂

Feel free to comment if you have questions.

abhijeet on Facebookabhijeet on Linkedinabhijeet on Twitter
Abhijeet specializes in developing software. A full-stack developer and Entrepreneur, he takes an idea and crafts it into a beautiful product - front to back. He develops on the LAMP Stack (PHP, MVC, Web API, Perl, Python, Azure, AWS, Google Cloud) and utilizes AngularJS and Angular Material for a structured client. Abhijeet is a self-starter with experience working in remote, agile environments mainly focusing on the security constraints. This is the developer, Project Manager and Consultant you are looking for.