Everything about database,how to,example,guide,

? ? VSFTPD is a kind of special in UNIX/Linux safety and fast FTP server,Had been used by a lot of large site place at present.VSFTPD support keeps user name and countersign existence database file or in database server.The user that VSFTPD weighs this kind of form is fictitious user.Relative to this locality at FTP (system) for the user,Fictitious user is FTP server only have an user only,The resource that fictitious user can visit FTP server place to offer only,The security that this enhances systematic itself greatly.Relative to at faceless user character,Name of user of fictitious user need and password ability get the document in FTP server,Increased user and pair of download can manage a gender.To needing to offer download to serve,But do not hope everybody is OK and faceless again download;Need to undertake administrative to downloading an user already,For the FTP site that goes to the lavatory considering lead plane safety and management again,Fictitious user is a kind of wonderful solution.Article introduction is on RedHat Linux 9 in how maintaining the fictitious user name of VSFTPD and code server of existence MySQL database.
? ?
? ?One, the installation of VSFTPD

? ? is current,The newest version of VSFTPD is 1.2.0 edition.Official download address is Ftp://vsftpd.beasts.org/users/cevans/vsftpd-1.2.0.tar.gz.Before installation,Need does the following preparation to work first:
? ?
The need in acquiescent configuration of ? ? VSFTPD " Nobody " user.This user is added in the system,If the user has existed,Useradd command has corresponding clew.
? ? [Root@hpe45 Root]# Useradd Nobody
? ? Useradd: User Nobody Exists
? ?
The need in acquiescent configuration of ? ? VSFTPD " / Usr/share/empty " catalog.In the system this catalog,If catalog has existed,Mkdir command has corresponding clew.
? ? [Root@hpe45 Root]# Mkdir /usr/share/empty/
? ? Mkdir: Cannot Create Directory '/usr/share/empty' : File Exists
? ?
When ? ? VSFTPD offers faceless FTP to serve,Need " Ftp " user and an active faceless list.
? ? [Root@hpe45 Root]# Mkdir /var/ftp/
? ? [Root@hpe45 Root]# Useradd -d /var/ftp Ftp
? ? next whether the operation has existed to Ftp user is useful.
? ? [Root@hpe45 Root]# Chown Root.root /var/ftp
? ? [Root@hpe45 Root]# Chmod Og-w /var/ftp
? ?
After ? ? above prepares the job to finish,We can begin to compile source code.The Vsftpd-1.2.0.tar.gz that assumes we download is in / Root catalog,Execute the following order:
? ? [Root@hpe45 Root]# Tar Zxvf Vsftpd-1.2.0.tar.gz
? ? [Root@hpe45 Root]# Cd Vsftpd-1.2.0
? ? [Root@hpe45 Vsftpd-1.2.0]# Make
? ? [Root@hpe45 Vsftpd-1.2.0]# Make Install
? ?
Above ? ? " Make Install " the binary document that the command will compiled, manual duplicate corresponding catalog.On RHL9,The likelihood needs a hand to move carry out duplicate below:
? ? [Root@hpe45 Vsftpd-1.2.0]# Cp Vsftpd /usr/local/sbin/vsftpd
? ? [Root@hpe45 Vsftpd-1.2.0]# Cp Vsftpd.conf.5 /usr/local/share/man/man5
? ? [Root@hpe45 Vsftpd-1.2.0]# Cp Vsftpd.8 /usr/local/share/man/man8
? ?
? ? next,We are duplicate a simple configuration document is offerred as the foundation from the back revise.
? ? [Root@hpe45 Vsftpd-1.2.0]# Cp Vsftpd.conf /etc
? ? [Root@hpe45 Vsftpd-1.2.0]# Cp RedHat/vsftpd.pam /etc/pam.d/ftp
File of test and verify of ? ? duplicate PAM,Log onto VSFTPD in order to allow this locality user.
? ? [Root@hpe45 Vsftpd-1.2.0]# Cp RedHat/vsftpd.pam /etc/pam.d/ftp
? ?
? ?Two, found Guest user

? ? VSFTPD uses PAM means test and verify fictitious user.The user name as a result of fictitious user / countersign bed sheet is saved alone,When test and verify because of this,The capacity that VSFTPD needs to use user of a system will read access to occupy library file or database server with finishing test and verify,The Guest user that this is VSFTPD.This no less than also needs to have Ftp of user of a system with faceless user same.Of course,We also can regard Guest user as the fictitious user delegate in the system.Vsftpdguest user is added in the system below,The Guest that serves as VSFTPD.
? ? [Root@hpe45 Vsftpd-1.2.0]# Useradd Vsftpdguest
? ? after fictitious user logins,The oneself catalog that the position of the place is Vsftpdguest / Home/vsftpdguest.If want to let fictitious user login / the other table of contents such as Var/ftp,The oneself catalog of modification Vsftpdguest can.
? ?
? ?Three, setting VSFTPD configures a file

? ? is in / in Etc/vsftpd.conf file,Add the following option:
? ? Guest_enable=YES
? ? Guest_username=vsftpdguest
? ?
? ? executes the following order next,Let VSFTPD backstage move:
? ? [Root@hpe45 Vsftpd-1.2.0]# /usr/local/sbin/vsftpd %26amp;
? ?
? ?Four, in protecting fictitious user server of existence MySQL database

We build ? ? database Vsftpdvu,Express Users,Field Name and Passwd are used at saving the user name of fictitious user and countersign, increase Xiaotong of two fictitious users and Xiaowang at the same time.
? ?
? ? [Root@hpe45 Vsftpd-1.2.0]# Mysql -p
? ? Mysql%26gt;create Database Vsftpdvu;
? ? Mysql%26gt;use Vsftpdvu;
? ? Mysql%26gt;create Table Users(name Char(16) Binary, passwd Char(16) Binary);
? ? Mysql%26gt;insert Into Users (name, passwd) Values ('xiaotong' , password('qqmywife'));
? ? Mysql%26gt;insert Into Users (name, passwd) Values ('xiaowang' , password('ttmywife'));
? ? Mysql%26gt;quit
? ?
? ? next,The Users that accredit Vsftpdguest can read Vsftpdvu database is expressed.Execute the following order:
? ? [Root@hpe45 Vsftpd-1.2.0]# Mysql -u Root Mysql -p
? ? Mysql%26gt;grant Select On Vsftpdvu.users To Vsftpdguest@localhost Identified By 'i52serial0';
? ? Mysql%26gt;quit
? ?
If ? ? wants test and verify a moment ago operation can be carried out whether successfully below command:
? ? [Root@hpe45 Vsftpd]#mysql -u Vsftpdguest -pi52serial0 Vsftpdvu
? ? Mysql%26gt;select * From Users;
If ? ? is successful,Will list Xiaotong, Xiaowang is mixed add close the password after
? ?
? ?Five, the PAM test and verify that installs MySQL

The project opening a source that we want to be used to to use Mysql to undertake Pam test and verify here (Http://sourceforge.net/projects/pam-mysql/ ) .The program that downloads it from the website above all wraps Pam_myql-0.5.tar.gz,Duplicate / in Root catalog.Before compiling installation,Should ensure the RPM bag of Mysql-devel has been installed on your machine,If did not install the installation in CD from RHL please,should wrap.Next,Execute the following order:
? ? [Root@hpe45 Root]#tar Xvzf Pam_mysql-0.5.tar.gz
? ? [Root@hpe45 Root]#cd Pam_mysql
? ? [Root@hpe45 Pam_mysql]#make
? ? [Root@hpe45 Pam_mysql]#make Install
? ? Make Install this one pace may appear mistake,That ace moves the Pam_mysql.o that will generate below this catalog to duplicate / below Lib/security catalog.
? ? next,The document of PAM test and verify that we want to install Vsftpd.Open / Etc/pam.d/ftp file,Add the following content:
? ? Auth Required Pam_mysql.o User=vsftpdguest Passwd=i52serial0 Host=localhost Db=vsftpdvu Table=users Usercolumn=name Passwdcolumn=passwd Crypt=2
? ? Account Required Pam_mysql.o User=vsftpdguest Passwd=i52serial0 Host=localhost Db=vsftpdvu Table=users Usercolumn=name Passwdcolumn=passwd Crypt=2
The parameter that involves above ? ? ,Want the implication that the setting of the database can understand them before correspondence only.What the specification needs here is Crypt parameter.Crypt indicates the word of command in countersign field impose close kind:Crypt=0,Countersign with means of proclaimed in writing (do not add close) in maintaining existence database;Crypt=1,The DES of system of countersign use UNIX adds close means close hind in maintaining existence database;Crypt=2,Countersign is added through the Password() function of MySQL close hind save.
? ?
? ?Six, farther fictitious user setting

The measure of above of ? ? course,Fictitious user can be used normally.The fictitious user with farther introduction is installed here.Above all,The attributive that introduces fictitious user is installed.
? ?
? ? VSFTPD-1.2.0 added Virtual_use_local_privs parameter newly,Activation when this parameter (YES) when,Fictitious user uses as identical as this locality user limits of authority.Shut when this parameter (NO) when,Fictitious user uses as identical as faceless user limits of authority,This namely version is right before VSFTPD-1.2.0 the processing technique of fictitious user attributive.This is both kind of practice photograph than,Latter is more a few stricter,Be in especially the case that writes a visit falls.This parameter is below acquiescent circumstance shut (NO) .
? ? when Virtual_use_local_privs=YES,Need to install Write_enable=YES only,Fictitious user can be had write attributive.And when Virtual_use_local_privs=NO,A few morer to the setting of fictitious user attributive a few stricter.
? ? controls fictitious user to scan list:If let an user cannot scan list,But still can operate to the file,Need to carry out so the following two measure:One,In configuring a file,Anon_world_readable_only=YES.Two,The attributive instead of fictitious user catalog can be operated by Vsftpdguest only:
? ? [Root@hpe45 Root]# Chown Vsftpdguest.vsftpdguest /home/vsftpdguest
? ? [Root@hpe45 Root]# Chmod 700 /home/vsftpdguest
? ? allows fictitious user to upload a file:
? ? Write_enable=YES
? ? Anon_upload_enable=YES
? ? allows fictitious user to alter a document name and delete a file:
? ? Anon_other_write_enable=YES
? ? is met likewise as a result of the setting of above option pair of faceless user become effective.If do not think faceless user takes the chance to have similar limits of authority,Had better be to prohibit faceless user logins.
? ?
? ? next,Because fictitious user is in the system,be Vsftpdguest identity,The other table of contents that can visit a system so.For more safe,We can be fictitious user limitation below oneself catalog.Have two kinds of ways:One,The following option adds in configuring a file
? ? Chroot_local_user=NO
? ? Chroot_list_enable=YES
? ? Chroot_list_file=/etc/vsftpd.chroot_list
? ? next,In / fictitious user name is added in Etc/vsftpd.chroot_list file Xiaotong and Xiaowang.
? ? the second kind of practice,Chroot_local_user=YES is revised in configuring a file.
After ? ? course is revised,After fictitious user logins, its root catalog is in with respect to limitation / below Home/vsftpdguest,Cannot visit other table of contents.
? ?
? ?Seven, the individual catalog of fictitious user

Everybody can discover ? ? ,No matter be which fictitious user,The catalog of the place after loginning is / Home/vsftpdguest,The oneself catalog that is Guest_username user namely.Below,How does the introduction build oneself list for every fictitious user.Above all,In advocate the following option is added in configuring a file:
? ? User_config_dir=/etc/vsftpd/vsftpd_user_conf
? ? next,Generate / Etc/vsftpd/vsftpd_user_conf catalog,Build as homonymic as specific and fictitious user file below this catalog:
? ? [Root@hpe45 Root]# Mkdir /etc/vsftpd/vsftpd_user_conf
? ? [Root@hpe45 Root]# Cd /etc/vsftpd/vsftpd_user_conf
? ? [Root@hpe45 Vsftpd_user_conf]# Touch Xiaowang
The operation of ? ? above built an individual to configure a file for fictitious user Xiaowang / Etc/vsftpd/vsftpd_user_conf/xiaowang.Next,The oneself catalog that configures Xiaowang of file lieutenant general in the individual of Xiaowang repairs instead / Home/xiaowang,Configuration option is:
? ? Local_root=/home/xiaowang
? ? next,Build Xiaowang list,Establish limits of authority for Vsftpdguest:
? ? [Root@hpe45 Vsftpd_user_conf]# Mkdir /home/xiaowang
? ? [Root@hpe45 Vsftpd_user_conf]# Chown Vsftpdguest.vsftpdguest. / Xiaowang
? ? [Root@hpe45 Vsftpd_user_conf]# Chmod 600 /home/xiaowang
? ? is installed through above,After Xiaowang logs onto VSFTPD,With " Pwd " the instruction can discover be gone to by fixed position by oneself oneself " / Home/xiaowang " catalog.
? ? looks from file system administrative levels,As a result of " / Home/xiaowang " the attributive of catalog is belong to Vsftpdguest,The oneself catalog that so other fictitious users also can visit Xiaowang likewise.Solve this problem very simple also,We need to make VSFTPD responsible restrict fictitious user only in its oneself catalog,The mutual visit that can prevent fictitious user.Particular way consults in front the place in the sixth pace is narrated,Here no longer give uncecessary details.After be being installed through above,Fictitious user can have the list that belongs to his.

...

What is index?

The record that index uses apace to search those to have specific value,The form that all MySQL index cultivates with B- is saved.If do not have index,The MySQL when executive inquiry must begin to scan from the first record all records of whole watch,Till find the record that accords with a requirement.The record amount of face of exterior and interior is more,The price of this operation is higher.If serve as the file that searchs a condition to went up to had been founded,index,MySQL need not scan the seat that any records can get quickly the target records a place.If the watch has 1000 records,Search a record to want to be recorded than ordinal scanning at least through index 100 times faster.

Assume we founded a watch that the name is People:


...

criterion expression (Regex) is a when define complex inquiry strong tool.

A simple data is here,Its oversight a few detailed information.

criterion the regulation that expression defined a string.The simplest criterion expression does not contain any key word.For example,criterion expression Hello is mixed only string " Hello " match.

General criterion expression used certain and special structure,So it can match more string.For example,criterion expression Hello | Word can match string already " Hello " also can match string " Word " .Lift more complex the one example of the dot,criterion expression B[an]*s can match string " Bananas " , " Baaaaas " , " Bs " hold the post of with etc why B begin with the string of S ending,Random can include among A and aleatoric a combination of N.



One is using the following key word in expression

^

After the string that matchs the string begin of the face
Mysql%26gt;Select "fonfo" REGEXP "^fo$"; -%26gt;0 (express not to match)
Mysql%26gt;Select "fofo" REGEXP "^fo"; -%26gt;1 (express to match)

$

The string of the face is terminal before the string that matchs
Mysql%26gt;Select "fono" REGEXP "^fono$"; -%26gt;1 (express to match)
Mysql%26gt;Select "fono" REGEXP "^fo$"; -%26gt;0 (express not to match)

.

Match any character (include new travel)
Mysql%26gt;Select "fofo" REGEXP "^f. *"; -%26gt;1 (express to match)
Mysql%26gt;Select "fonfo" REGEXP "^f. *"; -%26gt;1 (express to match)
A*

Match random many A (include null string)
Mysql%26gt;Select "Ban" REGEXP "^Ba*n"; -%26gt;1 (express to match)
Mysql%26gt;Select "Baaan" REGEXP "^Ba*n"; -%26gt;1 (express to match)
Mysql%26gt;Select "Bn" REGEXP "^Ba*n"; -%26gt;1 (express to match)
A+

Match random many A (do not include null string)
Mysql%26gt;Select "Ban" REGEXP "^Ba+n"; -%26gt;1 (express to match)
Mysql%26gt;Select "Bn" REGEXP "^Ba+n"; -%26gt;0 (express not to match)
A?

Match or zero A
Mysql%26gt;Select "Bn" REGEXP "^Ba? N"; -%26gt;1 (express to match)
Mysql%26gt;Select "Ban" REGEXP "^Ba? N"; -%26gt;1 (express to match)
Mysql%26gt;Select "Baan" REGEXP "^Ba? N"; -%26gt;0 (express not to match)
De | Abc

Match De or Abc
Mysql%26gt;Select "pi" REGEXP "pi | Apa"; -%26gt;1 (express to match)
Mysql%26gt;Select "axe" REGEXP "pi | Apa"; -%26gt;0 (express not to match)
Mysql%26gt;Select "apa" REGEXP "pi | Apa"; -%26gt;1 (express to match)
Mysql%26gt;Select "apa" REGEXP "^(pi | Apa)$"; -%26gt;1 (express to match)
Mysql%26gt;Select "pi" REGEXP "^(pi | Apa)$"; -%26gt;1 (express to match)
Mysql%26gt;Select "pix" REGEXP "^(pi | Apa)$"; -%26gt;0 (express not to match)

(Abc)*

Match random many Abc (include null string)
Mysql%26gt;Select "pi" REGEXP "^(pi)*$"; -%26gt;1 (express to match)
Mysql%26gt;Select "pip" REGEXP "^(pi)*$"; -%26gt;0 (express not to match)
Mysql%26gt;Select "pipi" REGEXP "^(pi)*$"; -%26gt;1 (express to match)

{1}

{2, 3}

This is a more comprehensive method,It can come true in front the function of several kinds of key word
A*

Can write into A{0, }
A+

Can write into A{1, }
A?

Can write into A{0, 1}

Only inside {} one is rectified model parameter I,State character can appear only I second;There is to rectify inside {} model parameter I,One follows from the back " ," ,State character can appear I second or I second above;Only inside {} one is rectified model parameter I,One follows from the back " ," ,Rectify with again model parameter J, state character can appear only I second above,J second the following (include I second with J second) .Among them whole model parameter must is more than be equal to 0,Be less than be equal to RE_DUP_MAX (acquiesce is 255) .If have two parameter,The second must is more than be equal to the first

[A-dX]

Match " A " , " B " , " C " , " D " or " X "

[^a-dX]

Match except " A " , " B " , " C " , " D " , " X " any character beyond." [" , " ] " must use in couples
Mysql%26gt;Select "aXbc" REGEXP "[a-dXYZ]"; -%26gt;1 (express to match)
Mysql%26gt;Select "aXbc" REGEXP "^[a-dXYZ]$"; -%26gt;0 (express not to match)
Mysql%26gt;Select "aXbc" REGEXP "^[a-dXYZ]+$"; -%26gt;1 (express to match)
Mysql%26gt;Select "aXbc" REGEXP "^[^a-dXYZ]+$"; -%26gt;0 (express not to match)
Mysql%26gt;Select "gheis" REGEXP "^[^a-dXYZ]+$"; -%26gt;1 (express to match)
Mysql%26gt;Select "gheisa" REGEXP "^[^a-dXYZ]+$"; -%26gt;0 (express not to match)

------------------------------------------------------------

[[.characters. ] ]

The order that expresses to compare an element.The character order inside bracket is exclusive.But can include in bracket match accord with, so he can match more character.E.g. :criterion expression [[.ch. ] ] before five character that *c matchs Chchcc.

[=character_class= ]

Express equal kind,Can replace kind in other and equal element,Include itself.For example,If O is mixed (+ ) be equal kind member,So [[=o=]] , [[=(+)=]] and [O(+)] is completely equivalent.

[: CHaracter_class:]

Inside bracket,Be in [: ?: ] among it is character kind name,Can represent belong to this kind all character.

Character kind the name has: Alnum, Digit, Punct, Alpha, Graph, Space, Blank, Lower, Upper, Cntrl, Print and Xdigit
Mysql%26gt;Select "justalnums" REGEXP "[[:aLnum:] ] + "; -%26gt;1 (express to match)
Mysql%26gt;Select " ! ! "REGEXP "[[:aLnum:] ] + "; -%26gt;0 (express not to match)

[[: ] ]

The string that matchs begin of a word and terminal sky respectively,This word begin and ending are not including the character in Alnum also cannot be underline.
Mysql%26gt;Select "a Word A" REGEXP "[[: ] ] "; -%26gt;1 (express to match)
Mysql%26gt;Select "a Xword A" REGEXP "[[: ] ] "; -%26gt;0 (express not to match)
Mysql%26gt;Select "weeknights" REGEXP "^(wee | Week)(knights | Nights)$"; -%26gt;1 (express to match)

...

The person that talks minute of page it seems that is very few,everybody indulge Limit M, n? Below the case that has index,Limit M, n speed is enough,But be when complex condition search,Meeting search of Where Somthing Order By Somefield+somefieldmysql alls over a database,Find out " all " the record that accords with a condition,Take out M next, n record.If your data bulk has hundred thousands of,The user searchs a few very common terms again,Want next ordinal read finally a few pages revive an old dream.Mysql this very solemn and stirring ceaseless operation hard disk.

...

It is the file code that carries the Apache+PHP backup to Mysql database below Linux below:

File one, Listtable.php (the file is listed all form in the database,Back up for the choice)


Choose to want the form of backup please:
%26lt; ?
$con=mysql_connect('localhost' , 'root' , 'xswlily');
$lists=mysql_list_tables("embed" , $con);
/ / database join code
$i=0;While($i$tb_name=mysql_tablename($lists, $i);Echo "" . $tb_name. $tb_name..
";
/ / listed all form
$i++;}

? %26gt;


File two, Backup.php
%26lt; ? If ($table=="") Header("Location:lIsttable.php"); ? %26gt;

%26lt; ?
$con=mysql_connect('localhost' , 'root' , 'xswlily');
$query="select * From $table ";
/ / database inquiry
$result=mysql_db_query("embed" , $query, $con);
$filestr=%26quot;%26lt;%26quot; . "? Xml Version=\"1.0\" Encoding=\"GB2312\" ? ". "%26Gt;";
$filestr. =%26quot;%26lt;%26quot; . $table. "S%26gt; %26quot;;While ($row=mysql_fetch_array($result) )
/ / listed all records
{$filestr. =%26quot;%26lt;%26quot; . $table. "%26Gt;";
$fields=mysql_list_fields("embed" , $table, $con);
$j=0;
/ / $num_fields=mysql_field_name($fields, $j);
/ / Echo $num_fields;While ($j$num_fields=mysql_field_name($fields, $j);
$filestr. =%26quot;%26lt;%26quot; . $num_fields. "%26Gt;";
$filestr. =$row[$j];
$filestr. = "";
$j++;}
$filestr. = "";
}
$filestr. = "";Echo $filestr;
/ / it is file operation code below
$filename=$table. ". Xml";
$fp=fopen("$filename" , "W");Fwrite($fp, $filestr);Fclose($fp);
Echo " data expresses " . $table. "?oes bifurcation ? mound does Fu bask in ? ? ; ? %26gt;


The form of the make choice of in can realizing pair of databases through the operation of above file undertakes backup.

The operation method that above basically introduced to realize XML backup database through PHP,Actually not complex,Through XML,We can back up various databases,In also can passing relevant method to restore the XML documentation of backup the database of course,Did not describe in detail here.

...

The database system that whether is your WebApp system using a MySQL?What your client always complains page result feedbacks is very slow?Systematic load always maintains your MySQL to fall in a very high position?The article will offer to partake for you the laden method of MySQL system,And from this the development project of a MySQL-HA-Proxy that derive comes out.The means that use article provides,You will alter with the least source code,The efficient movement that acquires MySQL system.

The current situation of technique of group of the first database

At present database group system applies more successfully,Applied range is wider is:The Oracle9 of Oracle company and IBM company DB2.The technology that Oracle9 uses Shared-storage,The technology that DB2 chose Shared-nothing,Both has accident each.

The theoretical foundation of newest database group system is distributed computation,Distributing data to every node,All computational node are collateral process data,result collect.Such means is the most perfect undoubtedly.But still cannot realize all function at present.

The relevant data that asks referenced Oracle and IBM website to go up to the technology of Shared-storage and Shared-nothing.

The second at present database application state

At present database application state is divided roughly it is two kinds,The first kind is data bulk is under 100G,Database visit is frequent,The request is concentrated.The application that basically is Web APP type,For example:Website,Forum.The applies visit database characteristic of these Web APP types is:The visit is frequent,The inquiry that database per sec. wants to accept above thousands of times,Need often increases data,At the same time logarithm occupies answer speed the demand is higher.Another kind is the application that is used at data of history of scientific computation, memory,Data bulk often achieves hundreds of G.These characteristics that apply visit database are:Operate for inquiry more,Data is group by group, time, concentration enters a database,The record of the database is very much,Gathered much data,To the database answer speed to do not have too high demand.

The third reveals exposed problem

The first kind of application,Because the visit is more frequent,And to support more visits,Web Server used laden and balanced group commonly,But to the database,Because cannot realize group to operate,The request of per sec. increases ceaselessly,As the addition of server load,The rate that answers individual plea is slower and slower,If library file is bigger,Appear the time that lock up a watch still can appear to grow the business that waits for an influence to visit efficiency too when keeping an operation.

The second kind of application,Basically be data file too big,Handle data to need many time every time,If a statement needs clerical error,spend a few hours to come redo inquires.

How is the fourth solved

Above all ought to from statement of hardware, software, program, index, SQL these a few respects undertake optimizing,If insoluble still problem,The group that we are about to consider database system (collateral processing) .

To the first kind application,Move normally in database server,Below the case with not high load,Of the situation that uses pair of database systems or satisfaction.But after database system load is exorbitant,Can appear the time lengthen of the request that finish,The requirement time of short of system.Since because cross much request,load is,cause,We are adopted partake the means of the request,The request that lets one share goes visitting a server,The load of server of stage letting sheet is reduced,Solve a problem thereby.

To the second kind application,With respect to need distributed calculative system was solved,Average system is helpless.

The first kind of application of the fiveth needle to "Linux+Apache+PHP+MySQL" of the problem settle way

The settlement of a real case:

I encountered such problem between the job,The group that three machines that our Web Server is Linux+Apache+Php form,MySQL moves in SUN450,On the platform of 2G memory.Because the visit of WEB is measured in the height when almost full load runs,LoadAvg(is one minute in the process amount that is in Running condition) it is between 10-20,Report comes out even if many request was hanged when visitting a database,Bring about a request to was not finished,Next request comes in again,Final vicious circle.LoadAvg can rise in instantaneous violent wind to 800 above.Database there worse,LoadAvg is achieved 300 much,The line Cheng of the database is very much,CPU at condition of switch line Cheng,This moment unless Restart MySQL,Otherwise how won't good.Be opposite SQL statement is optimized the problem still cannot be solved very well after finishing,We increased server of a database,Through the data synchronism mechanism of MySQL,The data that lets two databases go up keeps synchronous,Revised one part to be able to happen only read the Php course that takes an operation,Make these program join additional a database,It is a laden depart to go out one part,The problem got the abecedarian is solved.But business is done later big,We increased much stage server again,Modified a lot of programs,Detached their logarithm occupies a library read take an operation,The server with different visit.

Of plan of the sixth MySQL-HA-Proxy put forward

The means implementation that adopts modification program departs systematic load,It is a very thorny issue,Project huge,And cannot make a mistake,Because besides advocate the server can be written, modification data,And the data that the server of other can pass data synchronism to update oneself only,If you undertook writing to those databases,operate so,The result will be disastrous.

If we can have sentence of SQL of sorting of a program,The type according to him (read take / write) ,The server that conveys to differ respectively,Return the result again next.The means of the PROXY that uses a kind of similar HTTP,Such kind that we do not need to adopt modification source program will divide a load,If again can according to the laden state of the server,The position that perhaps expresses (usable / the lock is decided) ,Will judge should request this to allocate which server,The result that revises source program to be able to be achieved than us then still is close friends.

How to correspond between the seventh MySQL Client and Server

Look about,Also did not find an article about Mysql communication agreement,Look analysed the source program of Mysql only.The code that looks for Mysql 3.23.49 then,Open Sniffer tool.The communication agreement of MySQL may have been changed for many times,Inside the version of 3.23.49,The version of communication agreement is actually 10.

Analysed communication agreement simply,Neat now as follows,Some places still are not very perfect,The code that because I do not have too much time to grind carefully really,reads Mysql,At present I understood these only.


...

Summary: The problem with the record successional operation that exists in the light of current network database,The principle that the article uses two-way catenary to express,Kept away from groovy reflection method,The successful logic that realized data logging is successive,Very good solved a record to handle successional issue.The example that gave out to carry script PHP to use this principle to operate MySql database with the server at the same time.

Of one problem put forward
Information is data,The support that cannot leave a database in order to offer the website that information and information give priority to interactively necessarily.For instance common forum, news releases a system is complete those who be based on a database,What all programs offer user and governor is an operation interface only,User and controller should pass this interface to be able to realize logarithm occupy the data in the library to undertake additive only,Edit and delete wait for an operation.
To every data logging in the database,We can add an only label to it (ID) in order to distinguish the data logging at other.Be examined for what convenient user logarithm occupies record information and browse,The person that supply often is carried to use on the program is OK and direct from this the notes skips to the function that issue or attends to record.And this kind of function is based on ID just about increase and reduce those who will come true.To the data logging with successive ID the implementation of this kind of function does not have problem; however,After to the database medium a few data are deleted or discarding,Corresponding ID is nonexistent,The problem that if still use this kind of method to be able to appear,records for nothing in great quantities.If where,accordingly the circumstance with discontinuous ID is realizing every record to jump freely up and down turn,Be the problem that the article should solve.At present,The method that already a few solve this kind of problem,Commonly used is to appear every time when data logging ID is discontinuous,Undertake to data again sort,This kind of method is when data bulk is very great systematic workload is very breathtaking,The biggest drawback that this also is a method.Used two-way catenary watch to keep away from above the problem with groovy implicit method,And do not importune data logging ID successive,What just make they go up in logic is successive with achieving our goal,The principle that we let see two-way list first below.

The principle that two two-way catenary express
In data structure,What following graphs show the memory structure that two-way catenary expresses:

...

Everybody has used the database inside Phpmyadmin to guide certainly,Derive function,Very convenient.But in applying actually,I discover a few following problems:

1 database exceeds certain measure,For instance 6M is used at this moment derive be no problem commonly,Can save this machine hard disk correctly above,But guide be no good!The reason is:Install inside general PHP.INI temporarily file / the size limitation that uploads a file is 2M,And Phpmyadmin used uploading means,Cause failure.

2 derives the.SQL file of hard disk is in guide when answering,Because the problem of certain only quote causes failure,often appear,Cause guide into failure,Can guide with the applied process such as Mysql only.

My database has exceeded 10M, must solve this problem so.My train of thought:

Derive:Save a database with Phpmyadmin / form structure,Read access to save a file according to library content with script inside!
Guide:Restore a database with Phpmyadmin / form structure,Read with script take a file,Save a library next inside!

Derive the program is as follows:Call a method to be ****.php? Table=tablename
This simple procedure saves a form at present!!Every behavior the data of a field!!

...

? to MySQL,The first each travel command that what you must remember well is it is to use semicolon (;) serves as an end,But... without completely very thing,Also be same here,When a MySQL is inserted to be in PHP code,Had better drop the semicolon from the back elliptically,For example:

...

Below the circumstance that database watch is lost or damages,It is very important to back up your database is.If produce a system to break down,You think for certain can lose your watch least data to restore to break down as far as possible the condition when happening.Sometimes,Be MySQL administrator is caused destroy.The administrator has known the watch already was destroyed,Try to edit them directly with the editor such as such as Vi or Emacs,This is not a favour absolutely to the watch!

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.

...