The following steps can be executed as a non privileged user on the server, however, the username and password you use with the commands needs to be a database user that has r/w privileges within MySQL (think “sa” account for Microsoft SQL Server).
1. Export the database to a SQL file.
[user@host]$ mysqldump --opt --user=dbuser \
--password database > dumpfile.sql
2. Edit the dump file and put these lines at the beginning:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
3. Put these lines at the end:
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
4. Import the database SQL file into MySQL. This will overwrite the contents of the current database.
[user@host]$ mysql --user=dbuser \
--password database < dumpfile.sql
Click here for a good shell script that automates the backup of MySQL databases. Version 2.5 is available here for download.
