Backup
mysqldump command is used to backup up MySQL databases. mysqldump has the following format:
mysqldump -u [login] -p [pass] [dbname] > [backup.sql]
where [backup.sql] is the file to which the backup should be written.
Backing up specific tables
By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows:
mysqldump -u [user] -p [pass] [dbname] [tables] > [backup.sql]
where [tables] is the list of tables you wish to back up.
Backing up all databases
mysqldump --all-databases -u [user] -p [pass] > [backup.sql]
Backing up specific databases
By default, mysqldump would backup the entire database. If you need to backup only specific tables, list the tables as follows:
mysqldump -u [user] -p [pass] --databases [dbnames] > [backup.sql]
where [dbnames] is the list of databases you wish to back up.
Adding drop table commands
mysqldump --add-drop-table -u [login] -p [pass] [dbname] > [backup.sql]
Drop the table if it already exists.
Backup only the structure
mysqldump --no-data -u [login] -p [pass] [dbname] > [backup.sql]
Writing a backup shell script
#!/bin/sh
dt=`date -I`
mysqldump --all-databases | gzip > backup-$dt.sql.gz
Restore
To restore a mysql database backup, do the following:
mysql -u [login] -p [pass] [dbname] < [backup.sql]
Resources
- http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html">
backing up db greater than 1G
To create a dump of size 1Gig or more, use the -q option
mysqldump -u user -ppassword database -q > file.sql
Mistake?!
I think there's a mistake in the above tutorial, i.e. the third sub-heading should be,
Backing up specific databases
instead of,
Backing up specific tables
kcyow
thanx. fixed.
thanx. fixed.