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

From Notes_Wiki
m
m
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Backing up and restoring MySQL database=
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Mariadb configuration|MariaDB configuration]] > [[Backing up and restoring MySQL database]]


==MySQL database backup==
==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 '<tt>mysqldump</tt>' program. Syntax for mysqldump is:
To backup MySQL database one can use '<tt>mysqldump</tt>' program. Syntax for mysqldump is:
Line 12: Line 14:
mysqldump -u <mysql_username> -p <database_name> | bzip2 > <database_name>.sql.bz2
mysqldump -u <mysql_username> -p <database_name> | bzip2 > <database_name>.sql.bz2
</pre>
</pre>
===Dump each database in separate dump file===
If a system has multiple MySQL databases we can dump each on a separate file using:
<pre>
#!/bin/bash
cd /opt
for A in $(mysql -N --batch -e 'show databases'); do
  mysqldump --single-transaction --quick --skip-lock-tables $A | bzip2 > database-backups/$A.sql.bz2 ; 
done
   
exit 0
</pre>
We can call such backup script via [[CentOS 7.x rsnapshot|rsnapshot]] to ensure that during every run of [[CentOS 7.x rsnapshot|rsnapshot]] we get a separate DB backup file for each database.




Line 29: Line 47:




Back to [[Mysql configuration]]
 
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Mariadb configuration|MariaDB configuration]] > [[Backing up and restoring MySQL database]]

Latest revision as of 03:06, 26 February 2024

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


Dump each database in separate dump file

If a system has multiple MySQL databases we can dump each on a separate file using: 

#!/bin/bash

cd /opt
for A in $(mysql -N --batch -e 'show databases'); do 
   mysqldump --single-transaction --quick --skip-lock-tables $A | bzip2 > database-backups/$A.sql.bz2 ;  
done
    
exit 0

We can call such backup script via rsnapshot to ensure that during every run of rsnapshot we get a separate DB backup file for each database.


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

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