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.
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).
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;
and for lvm there is the dmsetup.