MySQL Database Maintenance, Backups, and Replication

Every MySQL administrator hopes to avoid having to deal with corrupted or destroyed database tables. But hope alone won’t keep problems from occurring.You should take steps to minimize your risks and learn what to do if bad things do happen.

Preventive maintenance. A regular program of preventive maintenance should be put in place to minimize the likelihood of database corruption or damage.You should also be making backups, of course, but preventive maintenance reduces the chance that you’ll need to use them.

Database backups. In the event of a severe system crash, database backups are of crucial importance. You want to be able to restore your databases to the state they were in at the time of the crash with as little data loss as possible. Note that backing up your databases is not the same thing as performing general system backups (as is done, for example, by using the Unix dump program).The files corresponding to your database tables might be in flux due to server activity when system backups take place, so restoring those files will not give you internally consistent tables.The mysqldump program generates backup files that are more useful for database restoration, and it enables you to create backups without taking down the server. You might also need the backup files for moving databases to a different location in the event of a full disk.

Crash recovery. Should disaster strike in spite of your best efforts, you should know how to repair or restore your tables. Crash recovery should be necessary only rarely, but when it is, it’s an unpleasant, high-stress business (especially with the phone ringing and people knocking on the door while you’re scrambling to fix things). Nevertheless, you must know how to do it because your users will be quite unhappy otherwise! Be familiar with MySQL’s table-checking and repair programs. Know how to recover data using your backup files and how to use the binary log to recover changes that were made after your most recent backup.

Database migration. If you decide to run MySQL on a faster host, you’ll need to copy your databases to a different machine.You should understand the procedure for doing this, should the need arise. Database file contents might be machine dependent; if so, you can’t just copy them from one system to another.

Database replication. Making a backup or a copy of a database takes a snapshot of its state at one point in time. Another option available to you is to use replication, which involves setting up two servers in cooperative fashion such that changes to databases managed by one server are propagated on a continuing basis to the corresponding databases managed by the other server. To use replication, you should know how to set up a server as a master replication server, and how to set up slave servers that replicate the master. If trouble occurs and replication stops, you must know where to look to identify the problem and how to get replication started again.

Source of Information : MySQL (4th Edition)


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner