Backup and Restore in MySQL

Backup and Restore MySQL Database

·        Mysql include mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT intosql-statements of the source database. To restore the database, execute the *.sql file on destination database.

Backup and Restore command line syntax

·        backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

·        restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

   1>    Backup a single database:

          # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

   2>    Backup multiple database:

               # mysqldump -u root -p[root_password] [database_name] [database_name_2] > dumpfilename.sql

   3>    Verify the dumpfilename.sql dump file contains both the database backup

         # grep -i "Current database:" /tmp/dumpfilename.sql

           Current Database: `database_name`

           Current Database: `database_name_2`

   4>    Backup all the databases:

         # mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

   5>    Backup a specific table:

         # mysqldump -u root -ptmppassword database1 accounts_contacts /tmp/database1_accounts_contacts.sql

    6>    Different mysqldump group options:

–opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.

–compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks

How to Restore MySQL database

    1>    Restore a database: When you are restoring the dumpfilename.sql on a remote database, make         sure to create the database before you can perform the restore.

# mysql -u root –ptmppassword

# mysql> create database database_name;

# mysql -u root -ptmppassword database_name < /tmp/dumpfilename.sql

    2>    Backup a local database and restore to remote server using single command: if you want to         create read only database in remote computer. For that you need to create database in remote               computer and then run below command.

[local-server]# mysqldump -u root -ptmppassword database_name  | mysql  \   -u root -ptmppassword --host=remote-server -C database_name1