MySQL Simple backup method

Applicable objects: MySQL DB Administrator.
Applicable conditions: The LINUX environment has the basic management and operational capabilities.
Article Note: This article for free documents, welcome to reprint the non-commercial nature, and please specify the source!
Commercial Nature Reprint please write to tell!
Special Note: 1. The program and settings provided in this article are not necessarily * in accordance with your environment,
Please modify the program and settings appropriately according to your system environment.
2. Before performing any destructive actions or actions,
Please make sure you have completed the full available data backup work.

Directory content
I. Preface
Two. Error correction
Three. Start Backup
Four. How to save
Five. mysql Online backup
Six. mysql line last saved
Seven. Start scheduling backups
Eight. Conclusion
Nine. Reference materials
10. Appendix-Backup Script original file

 

I. Preface

a few weeks ago, just as I was surfing happily on the internet, in the cool! Learning Garden Happy watering, unexpectedly jumped a page "PhpBB Critical Error", query Database error message, interrupted the students in the acrimony, heated discussion, although after a while, system tube The workers repaired the webpage, but for a few weeks in a row, PhpBB discussion area is sometimes very easy to hang up, the frequency is about 1-2 weeks, usually in the weekend when everyone is the most idle, fortunately at this time the World Cup football match just to fight, can not go to learn garden irrigation, at least there are football match can see. The way it was repaired, is to stop the MySQL service, and then the previous available DB file cover back, and then restart the MySQL service to test the PhpBB is normal, such a practice is usually effective, but always lost a certain period of time, this is not what everyone wants ... In order to save a lot of valuable information and the disgusting watering of the garden, I began to prepare the DB backup work, ready to rebuild the problem db, and the future campus relocation as DB transfer preparation.

(Db:database, the following are replaced by DB Database,
PhpBB: Cool! Learning Garden Discussion area system software name)

two. Error correction

After spending some time familiar with the host environment, I began to look for errors in there ... After detecting DB PhpBB in MySQL, we found that there was a problem with the users table.
Use Myisamchk to try to repair, found that it is not a preset way to repair, but also to add a "-o" parameter, in the use of myisamchk, in order to avoid users to access DB, it is best to be able to stop the MySQL service, or at least to the next "mysqladmin flu Sh-tables ", then make MYISAMCHK instructions, like:

Myisamchk-o phpbb2_users. Myi

This action, may have to make a 2~3 time, until no wrong message appears! After the repair, restart the MySQL service, you can use the client's instructions MySQL, to Query the DB content, test to see whether it is normal. Luckily, the DB part is functioning normally at this time. Of course, before you want to back up, if you can first detect the correct data, that is the best, if there is a need, you can put the test work, scheduled in the backup before the work, but remember, this detection of DB action not scheduled in the DB high dosage of that time, late at night no one on the line is a good choice!

Three. Start Backup

The data file for the PhpBB discussion area, which has two main parts, is the PHP main program and the DB content,
The PHP main program backup is relatively simple, as long as the entire file tar up on the line, like:

Tar cvfz phpbb2_20020601.tgz phpBB

(The above phpBB refers to the phpBB PHP Web program Storage directory.)

After the change to the PhpBB page program part and then back up again on the line, its content is written in db, so the PHP program file part of the difference should not be small.

Again is the MySQL DB part, the preset MySQL db file is in the/var/lib/mysql, in the DB name called the directory, the directory is the db of all the data, such as PHPBB2 this db, is the existence of/var/lib/mysql/php BB2, before the backup, because the fear of the data has not yet been fully written to disk, and MySQL will Lock in the use of the DB file, so it should be first to Shutdown MySQL first, the entire backup of the program can be like the following instructions to complete:

/etc/rc.d/init.d/mysqld stop
Tar cvfz phpbb2_db_20020601.tgz phpbb2
/etc/rc.d/init.d/mysqld start

(The above phpbb2 refers to the directory where phpbb2 this DB is stored.) ok! this is done! (What? that's it! 3 lines are over?!)

Yes, that's OK! However, it is necessary to note that the DB in the data between each tables related to the relevance, so it is best to put the entire db at once, only one of the tables of the file, and then back to save, fear that there will be inconsistent data related issues! To cool! The discussion area of the learning garden, for example, has 21000 or so articles plus 1200 registered members, database
The entire tar up about 30 MB or so per day, backed up by a week-by-cycle calculation, backup about only need
(35*7=245) More than 200 MB of space, a week's backup burn on a CD is enough!

This news total 3 pages, current in 1th page 1 2 3

Four. How to save

PhpBB discussion area of the back, just the file back to the original location of the Web page can be, with the following instructions to unlock:

Tar Xvfz phpbb2_20020601.tgz

DB is not difficult to get back when there is an error, first find the most recent complete normal backup, the current wrong page or DB first renamed or tar up, and then the good backup to untie back to the original directory location on the line, it is necessary to note that the MySQL service is the best to stop first, back to the completion of the service after the start, The entire program to back up DB
It might look like this:

/etc/rc.d/init.d/mysqld.stop
MV phpBB2 Phpbb2_error
Tar Xvfz phpbb2_db_20020601.tgz
/etc/rc.d/init.d/mysqld.start

And then test the Web and database! See whether the use of normal on the line ...

Five. mysql Online backup

It's a good idea to use a backup file like the one above. It can at least maintain a full file backup of the host at a point in time, but there are some problems to consider, some hosts are not only to build a DB, the total can not in order to back up a db and the entire MySQL service to stop, the way to back up the file, Back to the existence of the original machine must apply, but if the MySQL version of the upgrade, or on that day, the page space needs to move machine to other hosts, then no one can guarantee that the files available, so we can consider another way of backup, is the use of MySQL itself to provide the function: "MySQL Data Dump ", the instruction is" mysqldump ".

Using MySQL's dump function to dump DB's "structure", "material" or "structure plus" into a text file, the mysqldump directive provides a lot of flexibility, you can choose to dump the entire db into a file, or each Table is a file, or even the structure file It can be stored separately from the data. Testing cool! The PhpBB database of the school park found that the structure plus data full Dump into a file, the size of the file is about 30 MB or so. It is best to update the LOG more next flush-logs before the dump, so the entire dump command is as follows:

Mysqladmin-uroot-p Flush-logs
Mysqldump phpbb2-uroot-p opt > Phpbb2_20020601.sql

(phpBB2 is the DB name, opt is a use full Dump parameter)

Then use the time instruction to test execution times, this 30 MB DB, dump out unexpectedly as long as 15 seconds, Query speed is really fast, if just want to dump a table alone, as long as the above instructions, turn out the symbol ">" before adding a table Name is OK, as long as you phpbb2_users the Dump of this Table, just the following:

mysqldump phpbb2-uroot-p opt phpbb2_users > Phpbb2_users_20020601.sql

The Dump file is a plain text file, you can use tar to press it up, take the above 30 MB of DB as an example, the size of the file can be compressed to about 1/3 of the size, because Dump is a text file, all the data is in clear text, so you must pay attention to the security of backup file save, and recommended Backup files are best stored offsite and other easy to save media, such as CD-ROM or tape, such a backup makes sense.

Six. mysql line last saved

To save the entire repository, simply restore the compressed backup file to the Dump file, and then save it back with the following instruction:

MySQL Phpbb2-uroot-p < Phpbb2_20020601.sql

What you should be aware of here is that if the backup is established with "opt" or "add-drop-table" as the parameter, the return action is to first delete the old table, rebuild the structure of the table and then import data, so back to save, all the data will return to the time you back up that point, So when you're saving your data, consider backing up a copy of the existing error or incomplete DB, in case of a rainy hour, or where the wrong place is, of course, you can also save the data to another test db, just change the DB name of the above instruction to your test DB name.

With this type of callback, the MySQL service does not need to be stopped or moved to any other db that is in use, and it can be used for your own DB backup and storage in some virtual hosts that provide MySQL services.

In addition, if you are a system to rebuild or move the machine, cut in the new MySQL, a new blank DB, only to do the back of the work, you can use the following instructions to establish:

Mysql-uroot-p-E "CREATE DATABASE phpbb2"

(The above phpbb2 is the DB name you want to create)

Another approach is to use the "MySQL" command to enter the "MySQL client console", and then use:

CREATE DATABASE phpBB2;

This will do, remember the end to add a ";" symbol that the line instructions will not execute.

Total 2 page: previous 1 [2] Next page