MySQL Backup

The two common methods of Backup in MySQL are the following: 

shell> mysqldump –tab=/path/to/some/dir –opt db_name

OR

shell> mysqlhotcopy db_name /path/to/some/dir

You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn’t updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM tables.) Stop mysqld if it is running, then start it with the –log-bin[=file_name] option. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump. For InnoDB tables, it is possible to perform an online backup that takes no locks on tables, mysqldump.

Advertisements

One thought on “MySQL Backup

  1. Tim Archer

    You may also be interested in an open source script named automysqlbackup.
    I did a writeup on how to quickly setup this tool that might help you:
    http://timarcher.com/?q=node/11

    Its a really quick and easy way to backup your MySQL databases. I like the fact that it keeps daily, weekly, and monthly backups in separate folders, and manages purging old backups.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s