How to create a MySQL database backup

The following will create a gz compressed SQL dump from your chosen database

# mysqldump --lock-tables --opt DBNAME -u UNAME --password=PASS | gzip > OUTFILE

An alternative is to use mysqlhotcopy

This will make a hotcopy of SQL database files instead of a dump to a text file, you can use the mysqlhotcopy tool. This tool locks a table, copies it and than unlocks again.

If you want to transfer the MySQL data to a directory on the same server.

# mysqlhotcopy --user=UNAME --password=PASS DBNAME PATH

If you want to transfer the MySQL data to an other server with SSH

# mysqlhotcopy --method=scp --user=UNAME --password=PASS DBNAME PATH SSHUSER@SSHSERVER:PATH

tip: add this to your crontab for scheduled backups

 

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

MySQL Optimization / Repair Information

How MySQL Uses MemoryThis page lists some of the ways that the mysqld server uses memory, and...

How can I monitor what my MySQL server is doing?

A handy little Linux application called mytop fits this just perfectly. This is a near-time...

How do I backup MySQL in Linux?

1. Copying from the mysql directoryBy default, MySQL databases on servers that use Linux are...

Repairing MySQL tables that will not open

This should be handled on a case by case basis, but if you are using the default MySQL table type...

Securing MySQL

Due to differing needs and requirements this is difficult to answer except on a case by case...