Steps to manage MySQL Database Backup

Steps to manage MySQL Database Backup

  1. Bhawani Singh
    Backup of Database is one of the most important things in Websites as if you don’t have proper backup and restore technique then all your data may get lost. The following text is to tell you about MySQL Databases’ Backup Creation and Restoration. In Linux, this task can be easily performed via Command Line Interface as well as via Control Panel too. In this article we are going to explain the commands to back up the mysql database and if you can also check the article “How do I take backup Mysql database in cpanel”. The Backup file will be saved with “.sql” extension. More details are described in the text ahead.

    You can easily take Backup of Database by using the following Command Line -:
    • For Single Database :
    Code:
    mysqldump db_name > db_name.sql
    • For more than One Database :
    Code:
    mysqldump --databases one_database two_database > twodatabases.sql
    • For all the Databases :
    Code:
    mysqldump --all-databases > alldatabases.sql
    You can easily Restore the MySQL Backup by using the following Command Line -:
    • For Restoring Single Backup :
    Code:
    mysql db_name < db_name.sql
    • For Restoring Single Database from all Backup :
    Code:
    mysql --one-database db_name < alldatabases.sql
    If you want to Backup all Databases in the Server as different-different sql files then use following -:

    Code:
    for db in `echo 'show databases;' |mysql |grep –Ev
    "Database|information_schema|performance_schema"`; do mysqldump $db | gzip > /backup/$db.sql.gz ;done
    This process will create a backup of all the available Databases on the Server and then it will create a zip all the created sql files and will save the zipped file to the location /backup. Backup of all the databases will be available in the location /backup and the format will be databasename.sql.gz.