MySQL BACKUP & RESTORE

DATABASE BACKUP METHOD

 

·       mysqldump (Mostly Used)

·       mysqlhotcopy

·       percona

·       mysql replication

mysqldump:

        We can take backup of Single DB, Multiple DB or All DB.

 

Take Backup of Single DB.

# mysqldump -u root -p<password> <Database_Name> > /opt/filename.sql

i.e.

# mysqldump -u root –ppassword@123 db > /opt/db.sql

 

OR

 

# mysqldump -u root -p <Database_Name> > /opt/filename.sql

i.e.

# mysqldump -u root –p db > /opt/db.sql

 

 

Take Backup of Multiple DB’s.

# mysqldump -u root -p --databases <Database_Name1> < Database_Name2> < Database_Name3> > /opt/filename.sql

i.e.

# mysqldump -u root -p --databases db1 db2 db3 > /opt/multiple_databases.sql

 

 

Take Backup of All DB’s.

# mysqldump -u root -p --all-databases > /opt/filename.sql

i.e.

# mysqldump -u root -p --all-databases > /opt/all_databases.sql

 

 

RESTORE

# mysql –u root –p <Database_Name> < /opt/filename.sql

i.e.

# mysql –u root –p db < /opt/db.sql