March 14, 2007

Full Backup of MySQL DataBase

Here are some methods for FULL backup of MySQL Databases. This way all the constraints, stored procedures, triggers etc will be copied for backup and/or replicating the database.

Depending on your access to server, you can choose the different options.
e.g. you will not be able to copy the files from a shared hosting server (Method 3)

METHOD 1:
shell> mysqldump --tab=/path/to/some/dir --opt --full

METHOD 2:
shell> mysqlhotcopy database /path/to/some/dir

METHOD 3:
simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files)

METHOD 4:
You can write your own script which you can use to backup your database with various options. An example script can be downloaded from here

Please comment to this post to suggest more methods which can be added here.
Thanks

No comments: