In the last article we discussed how to backup data in csv format via PHP and command line. In this part we will backup MySQL databases via mysqldump command. I assume you are logged in to your Amazon instance.

How to backup:

1- Normal database backup

mysqldump -u root -p databasename > /var/www/html/databasename.sql

Enter your root password, change database name to the database you want to backup. Change the path from /var/www/html to your path if necessary. Change databasename.sql to your database.sql[any name can be given].

2- Backup MySQL database without data

mysqldump -d -u root -p databasename > /var/www/html/databasename_wo_data.sql

Same as 1 except it will dump database without any data.

3- Backup MySQL database with one table

mysqldump -u root -p databasename tablename > /var/www/html/database_table.sql

Here we will have one addition, enter the table name which we want to backup.

4- Backup MySQL database with one table without data

mysqldump -d -u root -p databasename tablename > /var/www/html/database_table_wo_data.sql

Same as 3 except this will dump it without any data.

How to restore:

mysql -u root -p databasename < /home/ec2-user/databasename.sql

Enter root password, change databasename and path to the backup sql file. Remember to create the database 1st if your sql file does not have the create database command. Login to MySQL via: mysql -u root -p and create database:

create database databasename default character set utf8;

That will do it, just change the database name.

Series:
Part-1: Setup web server on Amazon AMI or CentOS
Part-2: Backup MySQL data into CSV
Part-3: Backup MySQL data into CSV with PHP
Part-4: Backup and Restore MySQL databases
Part-5: Speed up your website loading time by using PHP APC