Difference between revisions of "Backing up and restoring MySQL database"
From Notes_Wiki
m |
m |
||
| Line 1: | Line 1: | ||
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Mariadb configuration|MariaDB configuration]] > [[Backing up and restoring MySQL database]] | |||
==MySQL database backup== | ==MySQL database backup== | ||
| Line 32: | Line 31: | ||
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Mariadb configuration|MariaDB configuration]] > [[Backing up and restoring MySQL database]] | |||
Revision as of 02:55, 5 March 2022
Home > CentOS > CentOS 6.x > MariaDB configuration > 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>
Home > CentOS > CentOS 6.x > MariaDB configuration > Backing up and restoring MySQL database
