Mysql backup

From INIwiki
Jump to: navigation, search

[edit] MySQL backup and restore

Dump all databases for backup. Backup file is sql commands to recreate all db's.

mysqldump -u root -p --opt >/tmp/alldatabases.sql

Dump one database for backup.

mysqldump -u username -p --databases databasename >/tmp/databasename.sql

Dump a table from a database.

mysqldump -c -u username -p databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup. (Example)

mysql -u username -p databasename < /tmp/databasename.sql

Also See:


[edit] Simple Script Example

This is a very simple script. In the first section I do a simple mysql dump. In the second section I do some date logic. In the third section I delete all database backup files that are older than 2 months. You can adjust this to less time by modifying the backup script time logic.

Finally I rsync my backups to a different location.

mysqldump nukeximz > /local/backup/mysql/dbname1.`date +%m-%d-%y`.sql
mysqldump wpjake > /local/backup/mysql/dbname2.`date +%m-%d-%y`.sql
mysqldump freakinfunny > /local/backup/mysql/dbname3.`date +%m-%d-%y`.sql
mysqldump goodcotton > /local/backup/mysql/dbname4.`date +%m-%d-%y`.sql
mysqldump ximzjoomla > /local/backup/mysql/dbname5.`date +%m-%d-%y`.sql
monthnow="`date +%m`"
#2monthago="`expr $monthnow - 2`"
#Check to see if 2 months ago is November or December
if [ `expr $monthnow - 2` -le 0 ]; then
		if [ `expr $monthnow - 2` -eq -1 ]; then
	monthago="`expr $monthnow - 2`"
#Delete files older than 2 months
if [ -s /local/backup/mysql/%path%.$monthago* ]; then
		echo "Deleting files older than 2 months"
		rm -rf /local/backup/mysql/dbname1.$monthago*
		rm -rf /local/backup/mysql/dbname2.$monthago*
		rm -rf /local/backup/mysql/dbname3.$monthago*
		rm -rf /local/backup/mysql/dbname4.$monthago*
		rm -rf /local/backup/mysql/dbname5.$monthago*
		echo "No Files Deleted"
#Copy backup to a different server
/usr/bin/rsync -delete-excluded -rave ssh /local/backup/mysql
Personal tools