MySQL platform database backs up plan defines
Tuesday, March 03, 2009 by rain
Backup database two main methods are to use Mysqldump program or document of direct copy database (if use Cp, Cpio or Tar,wait) .Every kinds of method has its actor drawback:
Mysqldump and MySQL server are operated in coordination.Direct copy method is in the server is exterior undertake,The watch that and you must take step assures to revising you to will be copied without the client.If you think with file system backup comes,back up database,Also can produce same question:If the database is expressed,be revised in process of file system backup,The condition with the watch file abhorrent subject that enters backup,And right the following restore a watch to will lose a sense.File system backup and the distinction that copy a file directly are pair of latter you controlled backup process completely,Such you can adopt measure to ensure the server lets a watch do not suffer interference.
Mysqldump wants than direct copy some slower.
Mysqldump is generated can transplant to the text file of other machine,Even on those machines that have different hardware structure.Direct copy file cannot transplant to other machine,Unless you are in,the watch of the copy uses MyISAM memory form.ISAM watch can be only on the machine of similar hardware structure copy.The form of MyISAM watch memory that introduces in MySQL 3.23 solved this problem,Because this format is a machine,have nothing to do,On the machine that so direct copy file can transplant to have different hardware structure.Should satisfy two requirements only:Another machine also must run MySQL 3.23 or later version,And the file must express with MyISAM format,is not ISAM format.
No matter you use method of which kinds of backup,If you need to restore a database,A few principles should be abided by,In order to ensure best result:
Fixed executive backup.Establish a plan and abide by strictly.
Let a server carry out newer daily record.When after you are breaking down, should needing to restore data,Newer daily record will help you.After restoring to count the state when backing up according to arriving with backup file in you,You can update the inquiry in the log to apply the modification at the back of backup again through moving,This restores the watch in the database to break down the condition when happening.
The term that backs up with file system is told,Database backup file represents complete tip (Full Dump) ,And newer daily record represents advance gradually tip (Incremental Dump) .
Use a kind to unite name a mechanism with intelligible backup file.Resembling Backup1, Buckup2 to wait is not particularly significant.When the refreshment that carries out you,You find out wasteful time is what thing in the file.You may detect to make backup file name with database name and date the meeting is very useful.For example:
%mysqldump Samp_db %26gt;/usr/archives/mysql/samp_db.1999-10-02
%mysqldump Menagerie %26gt;/usr/archives/mysql/menagerie.1999-10-02
You may want to compress them after generating backup.Backup is general very big!The backup document that you also need to let you has expire deadline in order to avoid their cram your disk,The log document that allows you just like you expires in that way.
With file system backup backs up your backup file.If was met,one breaks down thoroughly,Kept clear of not only your data catalog,The disk drive that the database that also kept clear of to include you backs up,You will meet a trouble truly.
Also should back up your newer daily record.
Put your backup document on the file system that is different from the database that is used at you.The possibility of the file system that because generate backup,this will be reduced and cram includes data list.
The technology that is used at founding backup arrives to copying a database likewise another machine is useful.The commonnest,The server that a database was gone to to move on another lead plane by move,But another server that you also can transfer same table lead plane to go up.
1 uses Mysqldump backup and copy database
When program of your use Mysqldumo generation database backs up when the file,Default ground,File content is included found the CREATE statement of the watch of dump and the INSERT statement that data goes in including a list.In other words,After the output that Mysqldump generates can be in, use as the input of Mysql will rebuild database.
You can receive whole database tip an alone text document in,As follows:
%mysqldump Samp_db %26gt;/usr/archives/mysql/samp_db.1999-10-02
The begin that outputs a file looks as if such:
# MySQL Dump 6.0# # Host: Localhost Database: Samp_db#-------------
--------------------------# Server Version 3.23.2-alpha-log## Table St
Ructure For Table Absence#CREATE TABLE Absence(Student_id Int(10)
Unsigned DEFAULT 0 NOT NULL, date Date DEFAULT 0000-00-00 NOT NUL
L, PRIMARY KEY (student_id, date));## Dumping Data For Table Absence
#INSERT INTO Absence VALUES (3, 1999-09-03);INSERT INTO Absence VALUE
S (5, 1999-09-03);INSERT INTO Absence VALUES (10, 1999-09-08); . . . . . .
The part that the file remains has more INSERT and composition of CREATE TABLE statement.
If you want to compress backup,Use similar the command that be as follows:
%mysqldump Samp_db | Gzip %26gt;/usr/archives/mysql/samp_db.1999-10-02.gz
If you want a giant database,Output a file also very will giant,The likelihood is hard to manage.If you are willing,The listed and alone watch name after the database name that you can go in Mysqldump command comes bend to their content,This divides dump file into lesser, more the file that manages easily.Next exemples show how to go to the bend of a few watches of Samp_db database in the document that submits departure:
%mysqldump Samp_db Student Score Event Absence %26gt;grapbook.sql
%mysqldump Samp_db Member President %26gt;hist-league.sql
If you generate preparation to be used at fixed refresh the backup file of another database content,You may want to use- - Add-drop-table option.This tells a server to keep statement of DROP TABLE IF EXISTS backup file,Next,When you take out backup file and lading it into the second database,If the watch has existed,You won't get an error.
If your teem so that a database can transfer the database to another server,You need not found backup file even.Should make sure data inventory depends on another lead plane,Pour a database with conduit bend next,Such Mysql can read the output that takes Mysqldump directly.For example:You want to copy database Samp_db from lead plane Pit-viper.snake.net to Boa.snake.net,Can accomplish very easily so:
%mysqladmin -h Boa.snake.net Create Samp_db
%mysqldump Samp_db | Mysql -h Boa.snake.net Samp_db
Later,If you think again the database on refresh Boa.snake.net,Jump over Mysqladmin to command,But should add to Mysqldump- - the error that Add-drop-table gets expressing already existinged with what avoid:
%mysqldump- - Add-drop-table Samp_db | Mysql -h Boa.snake.net Samp_db
The option with Mysqldump useful other includes:
- - Flush-logs and- - Lock-tables combination will examine a site to your database helpful.- - Lock-tables lock decides you to be in all watches of dump,And- - Flush-logs is shut and open file of newer daily record afresh,The inquiry that new newer daily record will include to nod the modification database since from backup only.The inspection of newer daily record that this will set you chooses a backup time.(if you have need,carry out an updated client however,The lock decides all watches is not a favour to backing up the client during is visited.)
If you are used- - when Flush-logs installs an examination to nod backup,Had better be dump likely whole database.
If your dump is alone file,Check the number of of check of worse will newer daily record and backup file synchronism.During restore,You update log content for fundamental extraction by the database normally,To the option that individual watch replaces without extraction,So you yourself must extract them.
Default ground,The whole content that Mysqldump expresses before be written is read into memory.This normally really needless,And if you have,actually one is expressed greatly,Be failure almost.You are usable- - Quick option tells Mysqldump to want it to retrieve a group to draw up only each.To optimize dump process further,Use- - Opt is not- - Quick.- - Opt option opens other option,The dump that quickens data and read them time.
With- - Opt carries out backup may be the most commonly used method,The advantage that goes up because of backup speed.However,Should warn you,- - Opt option has price really,- - the backup process that what Opt optimizes is you,Not be the other client visit to the database.- - Opt option decides all watches to prevent anybody to update you to be in through lock any watches of dump.You can see its effect very easily on general database visit.The database that becomes you is used often commonly,It is a day of ground adjustment backup only.
One is had- - the option of the adverse effect of Opt is- - Dedayed.This option makes Mysqldump draws up INSERT DELAYED statement is not INSERT statement.If you load data file another database and the impact that you think is the inquiry that this operation appears in this database to the likelihood is the least,- - Delayed is very helpful to this.
- - Compress option is copied in you when the database arrives on another machine very helpful,Because it reduces a network to transmit the amount of byte.There is a case below,Notice- - Compress just gives out to the program that with far end the server on lead plane corresponds,is not right with program of connective of this locality lead plane:
%mysqldump- - Opt Samp_db | Mysql- - Compress -h Boa.snake.net Samp_db
Mysqldump has a lot of option,Detailed sees " MySQL reference manual " .
2 uses the backup that copies a database directly and copy method
Another kind of kind that does not involve Mysqldump backup database and table is to copy a database to express a file directly.Typically,This uses such as Cp, Tar or Cpio utility program.The example of the article uses Cp.
When you use method of a kind of direct backup,You must make sure the watch is absent to be used.If the server is in,it is changed when you are copying a watch,The copy loses a sense.
The copies integrality best method that assures you is to shut a server,Copy file,Restart next server.If you do not want to shut a server,Should lock up while executive watch is checked decide a server.If the server is moving,Restrict identically also apply to copy file,And you should use identical lock to decide an agreement to let a server " quiet to come down " .
Hypothesis server is shut or you had locked up the watch that decided you to consider a copy,Following show how to back up backup of whole Samp_db database to catalog (the data list that DATADIR shows a server) :
%cd DATADIR%cp -r Samp_db /usr/archive/mysql
Individual watch can back up as follows:
%cd DATADIR/samp_db%cp Member. * /usr/archive/mysql/samp_db%cp Score. *
/ Usr/archive/mysql/samp_db. . . .
When you finished backup,You can restart server (if shut it) or release the lock that is added on the watch to decide (if you let a server move) .
Should use direct copy file to copy a database to another machine from a machine,Below the relevant data catalog that just copies the file another server lead plane can.Should ensure the file is MyIASM format or two machines have identical hardware structure,Otherwise your database has strange content on another lead plane.They are not visited when you also should assure the server on another machine to installing a database to express in you.
3 duplicate database (Replicating Database)
Duplicate (Replication) be similar to copy database to another server,But its precise meaning is the complete synchronism that assures two databases in real time.This function will appear in 3.23 edition,And very not mature still,Accordingly the article does not make detailed introduction.
4 restores data with backup
The happening that the database damages has a lot of reasons,Level is different also.If you are in good luck,You may damage two one are expressed only (if drop report) ,If you are hapless,You must replace whole data catalog possibly (if disk damages) .Also need to restore below certain circumstance,For instance the user deleted a database erroneously or express.No matter these having bad luck the reason of incident,You implement need some kind to restore.
If express attaint but did not lose,The attempt uses Myisamchk or Isamchk rehabilitate they,If such attaint can have repair program repair,You may need to use backup document far from.About expressing the process of repair,See " the database is safeguarded with repair " .
Restore a process to involve source of two kinds of news:Your backup file is mixed a newer daily record.The condition when backup file restores the watch to carry out backup,Express commonly however in backup and produce a problem had been revised inside the time between,Newer daily record included the inquiry that is used at undertaking these modification.The input that you can use log file to serve as Mysql will repeat inquiry.The reason that why should this already enable newer daily record just about.
The information that restores a process to inspect you to must restore how many and different.Actually,It is easy to restore whole database to follow than individual watch,It is easy to because use newer daily record to the database,compare individual list.
4.1 restores whole database
Above all,The Mysql database that if you think resumptive database is,includes accredit list,You need to use- - Skip-grant-table option runs a server.Otherwise,It can complain cannot find accredit to express.After you had restored to express,Executive Mysqladmin Flush-privileges tells a server to lade award mace and use them.
Copy database catalog content other a certain place,If you are in,need them later.
With database of newest backup file reshipment.If you use the document that Mysqldump produces,The input that regards Mysql as it.The file that if you are used,comes from database copy directly,them direct beat answers database catalog,However,The database is shut before right now you need to copying a file,Restart next it.
Use newer daily record to repeat make the inquiry that backs up the following modification database is expressed.To any adaptable update a daily record,The input that regards Mysql as them.Appoint- - One-database option makes Mysql carries out you to have fun at only the inquiry of resumptive database.If you know you need to use document of all newer daily record,You can use this order below the list that includes a daily record:
% Ls -t -r -1 Update. [0-9]* | Xargs Cat | Mysql- - One-database Db_name
A sheet that Ls command makes file of newer daily record lists list,The order sort that produces them according to the server (idea:If you alter any document,You will change sort order,This brings about newer daily record the order of one mistake is applied.)
Probable you can be to use some a few newer daily records.For example,Since the newer daily record that since you back up, produces be named to be Update.392, Update.393 to wait a moment,You can move afresh so:
%mysql- - One-database Db_name%26lt;Update.392
%mysql- - One-database Db_name%26lt;Update.393
. . . . .
If you are being carried out,restore and the DROP DATABASE that uses newer daily record to because a mistake suggests,restore, DROP TABLE or DELETE statement create missing news,Before using newer daily record,Should assure from delete these statements among them.
4.2 restores individual watch
Restore individual watch relatively complex.If you use a backup file that makes by Mysqldump,The data of the watch that and it does not include you is interested,Your need extracts them from inside relevant travel and use them as the input of Mysql.This is easy part.Hard part is help a segment from inside applying what express at this to update a daily record only.Utility program of Mysql_find_rows of your meeting disclosure is very helpful to this,It inquires all right more from the extraction in updating a daily record.
Another possibility is to use another server to restore whole database,The watch file that copies you to want next arrives in former database.This may be very easy really!When you answer file beat database catalog,Should ensure the server of former database is shut.