Every system administrator who uses MySQL will be forced to dump the database to move it to another server with more resources or to make a backup of it. In this article, I will explain the simplest but also the most downtime-intensive dumping method.

This method is ideal for servers that can afford downtime at some point in the day, so you can schedule the backup using CRON at a reasonable time, and no one will notice what happened.

Below are the steps to backup and restore the database.

• Backup of all databases:
  

  mysqldump -A -uroot -p’XXXX’ > DB.sql
  mysql -uroot -p’XXXX’ < DB.sql

• Backup of only one database:
  

  mysqldump DB -uroot -p’XXXX’ > DB.sql
  mysql -uroot -p’XXXX’ < DB.sql

• Backup of only one table (it can be restored to another database if necessary):
  

  mysqldump -u root -p’XXXX’ DB TABLE > TABLE.sql
  mysql -u root -p’XXXX’ DB < TABLE.sql

• Backup of only the structure:
  

  mysqldump –no-data DB -uroot -p’XXXX’ > STRUCT.sql
  mysql -u root -p’XXXX’ DB < STRUCT.sql

NOTE: If you only want to restore a table, you have to do a workaround by grepping the insertion statements that involve that table.

• If we are short of space on the MySQL server, we can send the dump directly over the network:
  

  mysqldump DB -u root -p’XXXX’ | gzip -c | ssh root@IP ‘cat > ~/DB.sql.gz’

• Or even insert directly:
  

  mysqldump DB -uroot -p’XXXX’ | ssh root@IP ‘cat | mysql DB -uroot -p’XXXX’'

• If we want to schedule the job at midnight:
  

  crontab -e

  00 00 * * * mysqldump -A -uroot -p'XXXX' > DB.sql