Backing up and restoring MySQL database

From Notes_Wiki
Revision as of 13:56, 9 April 2021 by Saurabh (talk | contribs)

<yambe:breadcrumb self="Backing up and restoring MySQL database">Mariadb configuration | Mariadb configuration</yambe:breadcrumb>

Backing up and restoring MySQL database

MySQL database backup

For large production databases refer CentOS 8.x mariadb taking backup of large production databases

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 self="Backing up and restoring MySQL database">Mariadb configuration | Mariadb configuration</yambe:breadcrumb>

Retrieved from "http://www.sbarjatiya.com/notes_wiki/index.php?title=Backing_up_and_restoring_MySQL_database&oldid=4987"