Difference between revisions of "Backing up and restoring MySQL database"

From Notes_Wiki
m
m
Line 1: Line 1:
<yambe:breadcrumb>Mysql configuration</yambe:breadcrumb>
=Backing up and restoring MySQL database=
=Backing up and restoring MySQL database=


Line 29: Line 30:




Back to [[Mysql configuration]]
<yambe:breadcrumb>Mysql configuration</yambe:breadcrumb>

Revision as of 08:09, 2 December 2012

<yambe:breadcrumb>Mysql configuration</yambe:breadcrumb>

Backing up and restoring MySQL database

MySQL database backup

To backup MySQL database one can use 'mysqldump' program. Syntax for mysqldump is:

mysqldump -u <mysql_username> -p <database_name> > <database_name>.sql

To automatically compress the database before it is stored on disk the output of mysqldump can be passed through bzip2 as:

mysqldump -u <mysql_username> -p <database_name> | bzip2 > <database_name>.sql.bz2


Restoring MySQL database

To restore MySQL database we can use 'mysql' command line client. Command to restore database is:

cat <dump_file>.sql | mysql -u <mysql_username> -p <database_name>


To restore a compressed database along with password we can take help of 'bunzip2' and 'sshpass' utilities in following manner:

bunzip2 -c <dump_file>.sql.bz2 | sshpass -p <mysql_database_password> mysql -u <mysql_username> -p <database_name>


<yambe:breadcrumb>Mysql configuration</yambe:breadcrumb>