3

I need to make a back-up of a database on CentOS 5.5, running MySQL server from the command line. Basically I need to export everything in it.

Starfish
  • 2,716
  • 24
  • 28
zozo
  • 753
  • 3
  • 11
  • 22
  • http://serverfault.com/questions/139593/optimal-way-to-make-mysql-backups-for-fairly-large-databases-myisam-innodb May provide interesting reading on this topic. – sysadmin1138 Mar 21 '11 at 13:03

3 Answers3

4

mysqldump - read the man page for details.

Note that it may take a time to generate the full backup file - if you want a consistent backup then consider setting up a replication slave (and turn off replication while taking the backup).

Wesley
  • 32,320
  • 9
  • 80
  • 116
symcbean
  • 19,931
  • 1
  • 29
  • 49
4

If you have a large database which you want to keep online (i.e. not read lock) during backups you can also consider the innobackupex script which uses xtrabackup from percona which supports online backups of both inno and myisam (to some degree of consistency)

Zmanda has a ZRM manager product which wraps the mysqldump tool and combines a scheduler and reporting agent which is convenient for managing cron jobs and recovery.

xtrabackup manager is also a command line tool that wraps the invocations and manages the scheduling to some degree... http://code.google.com/p/xtrabackup-manager/

There is also holland, another command line mysql backup wrapper which supports a number of backends.

Following on from @symcbean this is my attempt at taking a consistent command line dump of the system, with a mixed myisam and innodb engine configuration... (this will obvious make your database read only until it has completed, and any INSERT/UPDATE/DELETE queries will be blocked etc)

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

#--single-transaction is only useful if all the tables are innodb
SYSTEM mysqldump --lock-all-tables --quick --all-databases > all-databases.sql

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

the caveats are that GLOBAL read_only does not respect root users, so if you run your web apps as root then you would have to make sure they were stopped also.

also if your filesystem is xfs, or on LVM you can use the native fs tools to get a READ lock at the fs level which obviates the requirement for database level locking. for example for xfs;

SYSTEM xfs_freeze

and for lvm there is the dmsetup.

Tom
  • 10,886
  • 5
  • 39
  • 62
1

Use mylvmbackup to take snapshots of the filesystem or (if the database is small) mysqldump to get a SQL dump. I don't recommend mysqldump for large datasets.

Also, mysqlhotcopy is perfect for MyISAM.

For InnoDB try Hot-Backup, but it costs.

Community
  • 1
Sacx
  • 2,541
  • 15
  • 13