From Wsms

Note: This page should be titled mysqldump (all lowercase). It is Mysqldump due to technical limitations of Mediawiki.

mysqldump is a command-line utility for backing up mysql databases to a set of sql commands that can recreate the databases.

Contents 1 Sample 2 tips 3 automated backup 4 see also

Sample

[ggeller@ws05 sampdb]$ mysqldump -u root -p --all-databases > backup.sql
Enter password:
[ggeller@ws05 sampdb]$ head backup.sql
-- MySQL dump 10.10
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       5.0.27

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

tips

To get the database tables, but not the actual data:

[ggeller@arthur ~]$ mysqldump --all-databases --no-data --tables --compact
...

automated backup

To automate backups:

1. Make a mysql account with read-only privileges to the database:

mysql > grant select, lock tables on wikidb.* to 'wikibackup'@'localhost';

2. Make a cron script that runs mysqldump:

The command would be something like:

mysqldump -u wikibackup wikidb > mysqldump-`date +%Y%m%d`.out

Still need to write the cron script.

see also

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
20070315a
20070316a
man mysqldump