Everything about database,how to,example,guide,

The part before ? ? basically is focusing inquires at how letting the rate that implement is rapidder alone.The first step that MySQL still allows you to change a statement to attemper,The inquiry that it can make come from many clients to carry better cooperation,End of so single client won't be decided as a result of the lock and await for a long time.Change first step to still can ensure the inquiry of specific type is handled more quickly.Of the acquiesce that this one part explains MySQL attemper strategy and can use the option that affects these strategy.The influence of the use that it still spoke of intercurrent sex inserting an operation and the intercurrent sex that memory engine locks up calm administrative levels to be carried to the client.For discussion convenience,We carry out retrieve (SELECT) client end calls " to read " of the person that take,Execute modification operation (DELETE, INSERT, REPLACE or UPDATE) " of the person that client end calls " to be written.

...

Explain in the part before ? ? optimizing measure is non-privileged MySQL user can carry out.The administrator can carry out the system that can control MySQL server or computer to optimize measure additionally.For example,Some servers parameter handles a process at inquiry accessorily,And be OK adjust,And element of certain hardware configuration has immediate effect to inquiring processing speed.Below a lot of circumstances,These function that optimized measure to raise whole server,Because this can allow all MySQL users,be benefited.

? ? generally speaking,When your executive administrator is optimized,Should write down the following regulation closely:

The data in memory of visit of ? ? is fast the data that goes up at visitting disk.

? ? saves data in memory to be able to reduce disk to operate as far as possible.

The content that reservation of ? ? indexes medium information compares reservation data logging is more serious.

? ? we will discuss how to use these regulation at the back.

The size that ? ? increases server cache.The server has a lot of parameter (the system is variable) ,The operation that you can change these parameter to affect a server.The rate that a few among them parameter affect inquiry to handle forthrightly.The size that the most important parameter that you can change is data table cache and the cache size that store engine is used at amortize index to handle information.If you have practicable memory,With respect to the cache that allocates it to the server,Memory is in memory and reduce disk in order to allow information to operate.This meeting has very good effect,Because visit the information in memory to take the rate of information than be being read from disk,get quickly much.

? ? when the server opens watch file,It tries to hold these files open position,The amount that opens file operation in order to decrease.To realize such function,It safeguards the information that opens a file in expressing cache.The bulk that Table_cache system variable is controlling this cache.If the server visited many watch,Watch cache is met by cram,The list that and the server can shut those period of time was not used,Give a space to open new table to stay.The effect that you can pass indicator of examination Opened_tables position to visit watch cache:

SHOW STATUS LIKE ' Opened_tables ' ;

The number that ? ? Opened_tables showed to table of a certain data must be opened (had not opened because of it) .This value also shows the Opens in exporting information for Mysqladmin condition jussive is worth.If this number is stable or slow growth,So its setting may be correct.If this number rises very quickly,Mean this cache too small,Often must shut data table to be the data that opens other to express put apart space.If you have a file to describe information,The amount that increases watch cache size to will reduce number to open an operation according to the watch.

The index that MyISAM of ? ? stores engine uses key amortize to maintain the operation related to index believes breath piece.Its size is by pilot of Key_buffer_size system variable.This value is greater,MySQL is one-time more index maintain to believe breath piece in memory,Can increase in memory (and need not read from disk take new information piece) the possibility that finds key value.The acquiescent size of key cache is 8MB.If you have very much memory,This is a very conservative value,The size that you can increase it directly,The performance that build and revises an operation of the retrieval that and can see is based on index, index has very big improvement.

? ? is in version of MySQL 4.1 above,You can be built for MyISAM data table additional key cache,Appoint certain watch to use them.Can help the inquiry that raises these data tables to go up treat rate so.

The is used at amortize data and index value cache that InnoDB of ? ? and BDB engine have him.Their size is by Innodb_buffer_pool_size and Bdb_cache_size variable pilot.InnoDB engine still safeguarded amortize of a log.The bulk that Innodb_log_buffer_size variable can control it.

? ? another appropriative cache is inquiry cache,We use the explanation in inquiring cache " part in " .

? ? changes these when you when parameter is worth,Should abide by below a few principles:

? ? changes a parameter only every time.If you change the variable of many mutual independence,So evaluated the effect of every kinds of change very hard.

? ? increases systematic variable gradually to be worth.According to theory,The amount is more,Performance is better,But if you make a certain variable becomes too big,Cause systematic natural resources to short likely,Cause converse effect,Slowdown.

The test that adjusts parameter is done on the server that ? ? does not want to running business MySQL database,The test server that had better establish an independence.

? ? is planted to which understand roughly the system that parameter variable may suit him,You can examine MySQL to release the My-small.cnf that includes in documentation, My-medium.cnf, My-large.cnf and My-huge.cnf option document (on Unix system,The supportive file directory that you can release a document in the source shares catalog to always find these documents with what the binary system releases a document.On Windows,They are located in in basic installation catalog,Its expand the name may be.ini) .The use administrative levels that these files may let you know to had better change those parameter on the server to differ in order to suit,And provided a few models for these parameter be worth.

A few strategy still include the operates property other that ? ? uses at raising a server:

The memory engine that ? ? prohibits needing.The server won't allocate any memory for illicit engine,Accordingly we can use this.If build MySQL from source file,So when configure,Most memory engine can be eliminated to be besides the server.It is to those including for the engine in the server,Use the proper option that start to be able to prohibit when move among them most.
The simple sex that maintains accredit to express a license.Although the server is in memory cache accredit expresses content,But if if you are in Tables_priv or Columns_priv watch,a few data go,The attributive that the server must inquire to the statement examines watch administrative levels and row arrangement for every.If these watches are empty,So the attributive check out procedure that the server can optimize him,Skip over these administrative levels.

? ? if you build MySQL from source file,So configure it to use a static state kind library,And do not use share kind of library.Use the document of dynamic binary system that shares kind of library managing disk space,However rate of file of static binary system is rapidder.But,If you used an user to define function oneself (UDF) mechanism,So some of system asks to use dynamic link.On this kind of system,File of static binary system cannot work.

? ?Use MyISAM key cache

? ? carries out a certain statement that used MyISAM data to express index when MySQL when,It can use key cache to maintain index to be worth.This kind of cache reduced disk I/O:If be in cache,the key that found a certain data to express need is worth,Do not need to be read from inside disk again take.Unfortunate is,Cache of this kind of key is finite,And be below acquiescent circumstance,It is all MyISAM data tables are shared use.If was not found in key cache,key cache is full,Contention will be brought about:Must discard the certain value in cache,Take a space for new worth.If need those already cast off values next time,Must read from disk again take.

? ? if you very data of MyISAM of rely heavily on sb's service is expressed,So the effect in saving its key in memory will be very good,But the contention in cache can cause adverse effect however.From same piece of watch or diverse list read access to occupy pose contention possibly.The overall index that you can become enough to save a certain specific data to express through installing key cache,Avoid thereby same the contention of piece of data table,But the key that other data expresses still needs contention cache space.

Version of above of ? ? MySQL 4.1 offerred a kind of solution for this problem:It supports us to build many key cache,The index that allows us to express some piece of data is appointed and load a certain cache beforehand.If your data table is used very often,And you have enough memory,Can be record in its index in cache,So this kind of operation is useful.This kind of ability allows you to avoid at the same time same the contention of piece of watch and diverse list:Build enough large cache,The overall index that lets it save data to express,And appoint this cache use technically at that piece of data to express.Be record in in key after cache,Do not operate in need disk I/O.At the same time,Key value won't be discarded forever,Of the key that expresses to data in examining an operation to be able to be put inside, finish.

The example below ? ? showed the Member data table that how is Sampdb database builds cache of a key,The name of this cache is Member_cache,Size is 1MB.When implementing these directives,You must have super (SUPER) attributive.

? ? 1. Build enough the independent cache that accommodates data to express index:
Mysql%26gt;SET GLOBAL Member_cache.key_buffer_size = 1024*1024;

? ? 2. To data the watch appoints key cache:
Mysql%26gt;CACHE INDEX Member IN Member_cache;
+---------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------------+----------+----------+
| Sampdb.member | Assign_to_keycache | Status | OK |
+---------------+--------------------+----------+----------+

? ? 3. Bolt what data expresses index to read in it beforehand in cache:
Mysql%26gt;LOAD INDEX INTO CACHE Member;
+---------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------+----------+----------+
| Sampdb.member | Preload_keys | Status | OK | +---------------+--------------+----------+----------+

? ? if your hope is record in the data table of other same a cache is medium,The data table that perhaps is other builds key cache,The operation above is enough.

...

? ? MySQL is server of database of SQL of Cheng of a true much user, multi-line.MySQL is with a client computer / the implementation of server structure,It guards program Mysqld and composition of a lot of different client programs and library by a server.As a result of the open sex of its source and stability,And with the perfect union that website popularity organizes brandish americium PHP,A lot of sites use his to regard as now back end database,Make its won wide application.The consideration that is in safe respect,Need is endowed with for each user at be being restricted to the visit of different database,In order to satisfy the requirement of different user.Discuss respectively below,Consult for everybody.? ?

? ?One, summary of method of MySQL modification password

What ? ? should explain one a little bit above all is:Usually,Modification MySQL password is to need those who have the Root limits of authority in Mysql,So average user is cannot change a code,Unless request administrator help to revise.? ?

? ? method one ? ?

? ? uses PhpMyAdmin

? ? (the tool that the graph turns administrative MySql database) ,This is the simplest,The User that revises Mysql database library with SQL statement directly is expressed,Did not forget to use PASSWORD function nevertheless,Insert an user to command with Insert,Modification user commands with Update,Delete command with Delete.The detailed introduction that there is data to express User field at the back of this section.? ?

? ? method two ? ?

? ? uses Mysqladmin.Input ?
? of ? of Mysqladmin -u Root -p Oldpassword Newpasswd
After ? ? executes this order,The former password that needs to input Root,The password of such Root instead Newpasswd.Same,the Root instead in the command your user name,You can change yourself's code.

? ? if your Mysqladmin does not join,go up of course Mysql Server,Or you carry out Mysqladmin without method,So this kind of method disables namely,And Mysqladmin cannot password clear sky.? ?

The method below ? ? is used below Mysql clew accord with,And the Root limits of authority that must have Mysql:? ?

? ? method three ? ?
Mysql > INSERT INTO Mysql.user (Host, user, password) VALUES

('%' , 'system' , PASSWORD('manager'));
? of ? of Mysql > FLUSH PRIVILEGES
? ? says this is to increasing an user exactly,User name is System,The password is Manager.The attention should use PASSWORD function,Use FLUSH PRIVILEGES to carry out even next affirm.? ?

? ? method four ? ?

? ? and method three same,Just used REPLACE statement
Mysql > REPLACE INTO Mysql.user (Host, user, password)

VALUES('%' , 'system' , PASSWORD('manager'));
? of Mysql > FLUSH PRIVILEGES
? ? method five ? ?

? ? uses ? of ? of SET PASSWORD statement
? of ? of Mysql > SET PASSWORD FOR System@"%" = PASSWORD('manager');
You also must use ? ? PASSWORD() function,But do not need to use FLUSH PRIVILEGES to carry out affirm.? ?

? ? method six ? ?

? ? uses GRANT. . . IDENTIFIED BY statement,Will have authorization.? ?
Mysql > GRANT USAGE ON * . ? of ? of * TO System@"%" IDENTIFIED BY 'manager';
PASSWORD() function is needless here,Also do not need to use FLUSH PRIVILEGES to carry out affirm.? ?

? ? is noted:PASSWORD() function action is to be countersign word to add close,In the program MySql explains automatically.?

? ? two, the setting method that limitation visits in MySql

We use ? ? two kinds of methods will install an user.

? ? enters Mysql to carry out catalog to fall (it is C:\ normallyMysql\bin) .Input Mysqld-shareware.exe,Input Mysql- - User=root Mysql, cannot add new user otherwise.Enter Mysql > to hint undertake operating below accord with.? ?

? ? assumes we want to build a super user,User name is System,User countersign is Manager.?

? ? method one ?

? ? commands accredit with Grant,The code of the input is as follows:
Mysql > GRANT ALL PRIVILEGES ON * . * TO System@localhost IDENTIFIED BY

? of ? of 'manager' WITH GRANT OPTION;
? ? should show: QUery OK, ? of ? of 0 Rows Affected (0.38 Sec)

? ? method two ? ?

? ? undertakes installing to each attributive of the user:? ?
Mysql > INSERT INTO User

VALUES('localhost' , 'system' , PASSWORD('manager') ,

'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y' , 'Y');
? ? the MySQL to 3.22.34 version, here in all 14 "Y" , its corresponding attributive is as follows (arrange) by field order: ? ?


...

Because ? ? needs to use Mysql in the job,The author passes the study on the net,Start work practice,The " that turned can skilled operation into Mysql from " of bird of database dish of a " eventually allowed prawn " ,:) .Arrange study result as follows now.

? ? MySQL is complete network cross system of platform relation database,Server of database of SQL of Cheng of a true much user, multi-line,It is to have client computer at the same time / the system of distributed database management of server architecture.It has a function strong, use handy, government convenient, traversal speed sex of fast, on the safe side is strong wait for an advantage,The program that the user can use a lot of languages to write database of visit MySQL,To the medium, small-sized applying that the system is special ideal.Besides the ANSI SQL statement that supports a level,More important is,It still supports a variety of platform,And on Unix system this software supports multi-line Cheng run mode,Can acquire quite good property thereby.To using the user of Unix,It can serve means to move with the system on Windows NT system,Perhaps move with ordinary course means on Windows 95/98 system.And on Unix/Linux system,MySQL supports multi-line Cheng run mode,Can acquire quite good property thereby,And it is to belong to open source code soft.

? ? MySQL is with a client computer / the implementation of server structure,It guards program Mysqld and composition of a lot of different client programs and library by a server,The executive performance of MySQL is very high,Traversal speed is very fast,Use very easily,The database that is a special club.The official pronunciation of MySQL is "My Ess Que Ell"(not be MY-SEQUEL) .

One .Obtain MySQL

? ? must download MySQL above all.The official website of Mysql is:Http://www.mysql.com,The mirror that is in China is:Http://www.freecode.net.cn/mirror/mysql/ or Http://www2.linuxforum.net/mirror/mysql/ ,Till the stable version 3.22.32(that can download MySQL ends to distribute news dispatches to the author) .Its version name is Mysql-shareware-3.22.32-win,Not only free and use deadline " without angel of so called "30.

Two .The installation of MySQL

(One) the installation below Win98/Winnt

? ? to a catalog,Moving Setup program,Meeting clew is whole the process of installation.Its acquiesce installs C:\Below Mysql,If want to install other catalog to fall to still have,alter something,As abecedarian,Can first without giving thought to,Install with acquiescent catalog.good,After installation,Enter C:\Below Mysql\bin catalog,Moving Mysqld-shareware.exe file moves directly below Win98,Below normal circumstance,Without what clew information.

? ? 1.the file after download solution pressing solution overwhelms temporarily catalog.
? ? 2.Click Setup.exe to install C:\Mysql(is acquiescent) .
? ? 3.Copy Mysql_example.cnf C:\My.cnf,the #basedir=d:/mysql/ instead Basedir=c:\ in the fileMysql.
? ? 4.Enter Msdos,To catalog C:\Mysql\bin.
? ? 5.Input Mysqld-shareware -install,This serves to start Mysql database.
? ? 6.Mysqladmin - new code of U Root -p Password,The password that this is modification Root user (acquiescent before password is empty.The password that this is modification Root user (acquiescent before password is empty.
? ? 7.Input Mysql -u Root -p.
8 . of ? ?Then,According to clew input new code,Information of a few clew can appear below normal circumstance clew symbol:
Welcome To The MySQL Monitor of ? of ? ? ? . Commands End With; Or \g.
Your MySQL Connection Id Is 6268 To Server Version of ? of ? ? ? : 3.22.32
Type 'help' For Help of ? of ? ? ? .
Mysql%26gt; of ? of ? ? ?

Come here,You installed MySQL already successfully.

...

One. Method 1

? ? is in MySQL,Undertake Chinese sort is mixed when searching,The sort to the Chinese character and searching a result is wrong.This kind of circumstance exists in a lot of version of MySQL.If this problem is not solved,So MySQL will not handle Chinese actually.

? ? appears the reason of this problem is:When MySQL is inquiring string, be big the ordinary form of a Chinese numeral is not sensitive,The character set that serves as acquiesce with ISO-8859 character set commonly when the MySQL that make up sort out,Because this is comparing the Chinese in the process to code character size writes changeover,created this kind of appearance,A kind of means of settlement is to including the field of Chinese to increase "binary" property,Compare what make as the binary system,Change "name Char(10)" "name Char(10)binary" for example.

Two. Method 2

? ? if your use source compiles MySQL,When can compiling MySQL, use- - With- - Charset=gbk parameter,Such MySQL are searched with respect to Chinese of can direct support and sort.

...

One, the MySQL that how obtains newest version?

? ? should install MySQL, the newest version that should want to obtain it of course above all,Although everybody knows,MySQL can be found in the Packages of FreeBSD,But I or the MySQL that suggest everybody seeks newest version to the network.I introduce everybody to download to Http://www.mysql.org,Here
newest version is MySQL 3.23 Versions.

Two, the note that installs MySQL moment?

1, if you are with MySQL+Apache,The word that those who use is FreeBSD network operating system,You should press installation moment the version issue that notices FreeBSD,Say originally in the 3 the following edition of FreeBSD,It is normal that the MIT-pthread with embedded MySQL Source moves,But in this version above,You must be usedNative Threads, the option that adds a With-named-thread-libs=-lc_r namely.
2, if you are in COMPILE process,gave an issue,Whether is the Gcc version that checks you first please 2.81 version above,Whether is Gmake version 3.75 above.
3, if not be the problem of version,The memory that may be you then is insufficient,Use please. / Configure- - With-low-memory will join.
4, if you want to do your Configure afresh,So you can key in Rm Config.cache and Make Clean to keep clear of record.
5, we install MySQL in commonly / below Usr/local catalog,This is default value,You also can need the list that you install set according to yours.

Three, start and stop MySQL

If ? ? never has installed the word of MySQL on your machine,When installing MySQL,The last the command that key in is / Usr/local/mysql-3.23X/scripts/mysql_install_db
If you are the word that installs much station machine at the same time,You can compile this record,The has set each limits of authority that can make you convenient so.

1, installing catalog / the Usr/local Mysql that start can be used / Usr/local/share/mysql/mysql.server Start remembers installing the attribute of Mysql.server into 777
2, should stop Mysql Daemon to be able to be used / Usr/local/bin/mysqladmin Shutdown will come true
3, if your Mysql had installed a password,You must use Mysqladmin -u Root -p Shutdown to come true


Four, the use person that builds a MYSQL with Root

You need ? ? to record a machine with Root first,Change catalog to access attributive Chown -R Mysql /usr/local/var next,Alter Mysql.server record then,This file is located in / below Usr/local/share/mysql/ ,Change Mysql_daemon_user=root into Mysql_daemon_user=mysql.
But after increasing database newly later,Remember the relevant archives you accessing attributive to change the past:Chown -R Mysql /usr/local/var/some_new_dbs
If you hope your computer starts time Mysql of automatic to load,You should be in only / Etc/rc.local joins group / Usr/local/mysql/share.server Start


Five, the Mysql that checks you
After ? ? starts Mysql,You can use Ps -aux to check,You discover much the syntactic general survey that MYSQL of two Process ? uses

String type---STRINGS

Of string matching must be didymous,For example:

' MY IS OCP '

" MY IS OCPISCAI "


They must be it is to use ' ' or " " will express!

We will cite a few case that contain only quote and double quote inside string,For example:

Repeat double quote:
Mysql%26gt;select %26quot; Hi! " " Ocp " " ! "

- %26gt; Hi! " Ocp " !


Because a lot of only quote and double quote are contained in our HTML network,So program

Can encounter some of problem that makes mistake because of only quote and double quote here,Should try to take care so.

?

Digital type---NUMBERS

A few lawful numbers express kind in light of us:

7

777

- 777

77.77

77.777

- 777.7777o+7


Of data-base name a problem,TATBLE, INDEX, COLUMN, and of ALIAS NAME it is OK to name a principle the number as initial,Can have a bottom line,But cannot be a number completely.As far as possible avoid to use $ character,In MYSQL $ character is completely lawful,Do not pass even if be met and PHP is promiscuous,So as far as possible unused is good.In name in cannot have " . " character.ALIAS NAME can use 256 character,The length of other is restricted commonly inside 60 character.The length that you can revise MYSQL_INSTALL_DB to need your place to revise according to your him need changes,When changing, the attention does not change too too exaggerative,MYSQL data-base is to be able to support,But I had not tried the core of MYSQL,Perhaps can appear mistake.

?

Numeric function:When numeric function makes mistake,Those who pass is NULL

LOG10 (X) :Be worth with the 10 LOG that are a bottom
Mysql%26gt;SELECT LOG (EXP (1) ) ;
- ? .000000


EXP (X) :The X that passes an E second square,Among them the truth of a matter that E is NATURAL LOG.

FLOOR (X) :Pass be less than those who be equal to X the biggest integer.

CEILING (X) :The smallest integer that passes a heavy rain to be equal to X.

ROUND (X) :Round arrives integral.

MOD (N,M) or % :Take N to divide the remainder with M.

SIGN (X) :X is more than pass 1,X is equal to 0 pass 0,X is less than 0 pass - 1.

ABS (X) :Take absolute value.

ROUND (X,D) :Round arrives D fraction.D is equal to 0 with ROUND (X) identical.

Trigonometric function:

ATAN (X,Y) :Return trigonometric function

ATAN2 (X,Y) :Return trigonometric function

LEAST (X,Y,...) :At least two parameter,Pass the smallest cost.

GREATEST (X,Y,...) :At least two parameter,Send a maximum.

DEGREES (X) :Changeover radian (RADIAN) to degree.

RADIANS (X) :Change degree to radian.

...

Need not need to revise My.ini.When building a database,Not quite important to the character set setting of library and watch,But had better install to the field of text type for GBK character set.

To already some databases can use the following method to change a field to code:

...

? ? or first Create Table
? ?
? ? Create Table Emp(
? ? Id Int Not Null Primary Key,
? ? Name Varchar(10)
? ? ) ;
? ?
? ? Create Table Emp_dept(
? ? Dept_id Varchar(4) Not Null,
? ? Emp_id Int Not Null,
? ? Emp_name Varchar(10) ,
? ? Primary Key (dept_id, emp_id));
? ?
? ? Insert Into Emp() Values
? ? (1, "Dennis-1") ,
? ? (2, "Dennis-2") ,
? ? (3, "Dennis-3") ,
? ? (4, "Dennis-4") ,
? ? (5, "Dennis-5") ,
? ? (6, "Dennis-6") ,
? ? (7, "Dennis-7") ,
? ? (8, "Dennis-8") ,
? ? (9, "Dennis-9") ,
? ? (10, "Dennis-10");
? ?
? ? Insert Into Emp_dept() Values
? ? (%26quot;R%26amp;D%26quot; , 1, "Dennis-1") ,
? ? ("DEv" , 2, "Dennis-2") ,
? ? (%26quot;R%26amp;D%26quot; , 3, "Dennis-3") ,
? ? ("Test" , 4, "Dennis-4") ,
? ? ("Test" , 5, "Dennis-5");

...

?of ? ? 1:Discharge ?of grapes dark excessive ancienting name for a water catltrop, with high speed hard disk (7200 turn / second) , theoretic every second is searched. This is changed without method, optimized method is----With many hard disk, perhaps store data dispersedly.
? ?
?of ? ? 2:Ability is phlegmy mu of row this Yu ?, this rate is exceedingly rapid, this easier solution definitely- - can run paralell from many hard disk read write.
? ?
? ? 3:cPu.cpu processes the data in memory, when having the list with opposite lesser memory, this is the commonnest limitation element.
? ?
?of ? ? 4:? of the bend so as to breakstubborn austral Gua adept needs to exceed the data that suits Cpu cache when Cpu when, the bandwidth of cache became a bottleneck of memory---Memory is old now nevertheless breathtaking, won't appear commonly this problem.
? ?
? ?The second pace: (The Linux platform that what oneself use is school website (Linux ADVX.Mandrakesoft.com 2.4.3-19mdk) )
? ?
?of ? ? 1:Da of Jin of Gu Shen ? asks ?BR>? ?
? ? commands acoustical factory this with Shell%26gt;mysqld-help option of a piece of all Mysql and the watch that can configure variable. Export the following information:
? ?
? ? Possible Variables For Option- - Set-variable(-o) Are:
? ?
? ? Back_log Current Value:5/ / requirement Mysql can some join amount.back_log point out in Mysql time-out accepts connective there is how many join to request to be able to be existed inside time in stack
? ?
? ? Connect_timeout Current Value:5/ / Mysql server is using Bad Handshake(bad interpreter) respondent before await time of a connective
? ?
? ? Delayed_insert_timeout Current Value:2An Insert Delayed is in 00 // the time that Insert awaits before stopping
? ?
? ? Delayed_insert_limit Current Value:5Whether does processor of 0 //insert Delayed have the examination any Select statements to was not carried out, if have, these statements are carried out before continueing
? ?
? ? Delayed_queue_size Current Value:1000 // distributes how old line for Insert Delayed
? ?
? ? Flush_time Current Value:0/ / if be to be not by the setting 0, so time of every Flush_time, all watches are shut
? ?
? ? Interactive_timeout Current Value:28800 // server is before shutting it on foreign alternant link the time that await
? ?
? ? Join_buffer_size Current Value:131072 // is used with size of all connective buffer
? ?
? ? Key_buffer_size Current Value:1048540 // concordance piece the size of buffer, it is OK to increase it better processing index
? ?
? ? Lower_case_table_names Current Value:0/ /
? ?
? ? Long_query_time Current Value:10 // if an inquiry place is more than this time with time, slow_queried computation will increase
? ?
? ? Max_allowed_packet Current Value:1The size that 048576 // includes
? ?
? ? Max_connections Current Value:300 // allows at the same time connective amount
? ?
? ? Max_connect_errors Current Value:1If 0 // has over of this amount sever link, will prevent farther join, can be solved with Flush Hosts
? ?
? ? Max_delayed_threads Current Value:1The handles Insert Delayed amount that 5 // can start
? ?
? ? Max_heap_table_size Current Value:16777216 //
? ?
? ? Max_join_size Current Value:4294967295 // allows to read extraction connective measure
? ?
? ? Max_sort_length Current Value:1024 // is in the byte measure that sort Blob perhaps uses when Text
? ?
? ? Max_tmp_tables Current Value:3The expresses temporarily amount that a join opens 2 // at the same time
? ?
? ? Max_write_lock_count Current Value:4294967295 // assigns a value (normally very small) will start Mysqld, make after a certain quantity of Write lock is decided occurrence Read lock is decided
? ?
? ? Net_buffer_length Current Value:1The size of buffer of 6384 // communication- - be by replacement when inquiry this size
? ?
? ? Query_buffer_size Current Value:0/ / size of the buffer when inquiry
? ?
? ? Record_buffer Current Value:1Every pieces of expresses distributive buffer size that the join that 31072 // scans every orderly scans for its
? ?
? ? Sort_buffer Current Value:2097116 // has the volume of the join distributive buffer of sort every
? ?
? ? Table_cache Current Value:64 // is the amount of the table that all join open
? ?
? ? Thread_concurrency Current Value:10 //
? ?
? ? Tmp_table_size Current Value:1The size that 048576 // expresses temporarily
? ?
? ? Thread_stack Current Value:131072 // the size of every line Cheng
? ?
? ? Wait_timeout Current Value:28800 // server is in shut the time that awaits on its 3 join before
? ?
? ? configures above information to be able to be helped to you according to his need.
? ?

...

A few experience that use MYSQL service is under ? ? ,The MYSQL service that basically considers from the following respects plans to design.
? ?
The installation that ? ? 1 MYSQL serves / the versatility of configuration;
? ?
Of system of ? ? 2 upgrade and data is migratory and convenient quality;
? ?
Backup of ? ? 3 and system restore quickly;
? ?
? ?The program of MYSQL server
? ?
? ? to be safeguarded later,Upgrade the security of the convenience of backup and data,Had better install MYSQL program document and data respectively in " different hardware " on.
? ?
? ? /
? ? / Usr %26lt;==Operating system}==%26gt;Hard disk 1
? ? / Home/mysql %26lt;==Mysql uses a program
? ? . . .
? ? / Data/app_1/ %26lt;==Apply data and script}==%26gt;Hard disk 2
? ? / Data/app_2/
? ? / Data/app_3/
? ?
Of the installation that ? ? Mysql serves and service start:
? ?
The version that ? ? MYSQL uses current STABLE commonly,Do not use as far as possible- - With-charset= option,I feel With-charset is in only ability is useful when pressing alphabetical sort,What meeting logarithm occupies these option is migratory bring a lot of troubles.
? ?
? ? Configure- - Prefix=/home/mysql
? ? Make
? ? Make Install
? ?
? ?Of the service start and stop
? ?
? ? 1 is duplicate default Mysql/var/mysql arrives / below Data/app_1/ catalog
? ?
? ? the starting script of 2 MYSQLD:
? ? Start_mysql.sh
? ? # ! / Bin/sh
? ? Rundir=`dirname "$0"`
? ? Echo "$rundir "
? ? / Home/mysql/bin/safe_mysqld- - User=mysql- - Pid-file="$rundir"/mysql.pid- - Datadir="$rundir"/var "$@"-O Max_connections=500 -O Wait_timeout=600 -O Key_buffer=32M- - Port=3402- - Socket=%26quot;$rundir%26quot;/mysql.sock %26amp;
? ?
? ? annotate:
? ?
? ?- - Pid-file="$rundir"/mysql.pid- - Socket="$rundir"/mysql.sock- - Datadir="$rundir"/var
? ?
? ? purpose is will relevant data and application temporarily the file is put together;
? ?
? ? - it is the server starts global variable to optimize parameter commonly at the back of O,Occasionally need is adjusted according to specific applying;
? ?
? ?- - Port: Different application goes on the service that use PORT parameter distributings to differ,The main bottleneck that the join number that a service can provide is MYSQL service commonly;
? ?
After ? ? revises the different port that serves to differ,Join in Rc.local file:
? ?
? ? / Data/app_1/start_mysql.sh
? ? / Data/app_2/start_mysql.sh
? ? / Data/app_3/start_mysql.sh
? ?
? ? notices:Must write whole way
? ?
? ? of 3 MYSQLD stop script:Stop_mysql.sh
? ? # ! / Bin/sh
? ? Rundir=`dirname "$0"`
? ? Echo "$rundir "
? ? / Home/mysql/bin/mysqladmin -u Mysql -S"$rundir"/mysql.sock Shutdown
? ?
? ?The profit that uses this script depends on:
? ?
Many services of ? ? 1 are started:In needing to revise script only- - Port= parameter.The data below individual catalog and service script are OK bale independently.
? ?
All services of ? ? 2 are corresponding the file is located in / below Data/app_1/ catalog:For instance:Mysql.pid Mysql.sock,When many services are started on a server,Many services won't interact.But put default / Tmp/ issues likely by other applying by accident cutout.
? ?
? ? 3 is become hard disk after a problem,Direct hard disk the service can restore immediately on the 2 servers that are put to to install good MYSQL (if be put in My.cnf to still need backup,configure a file accordingly) .
? ?
After ? ? service is started / the corresponding file below Data/app_1/ and catalog distributing as follows:
? ? / Data/app_1/
? ? Start_mysql.sh serves the script that start
Service of ? ? Stop_mysql.sh stops script
The process ID that ? ? Mysql.pid serves
The SOCK that ? ? Mysql.sock serves
Division of data of ? ? Var/
Library of user of ? ? Mysql/
? ? App_1_db_1/ uses a warehouse
? ? App_2_db_2/
? ? . . .
? ? / Data/app_2/
? ? . . .
? ?
? ? examines all applied process ID:
? ? Cat /data/*/mysql.pid
? ?
The wrong daily record that ? ? examines all databases:
? ? Cat /data/*/var/*.err
? ?
? ? individual suggests:The main bottleneck of MYSQL is on the join number of PORT,Because of this,After the watch the structure has been optimized,The CPU that corresponding and individual MYSQL serves takes up still in 10 % above,Be about to consider to will serve fractionation to move to many PORT.
? ?
? ?The backup of the service
? ?
? ? uses MYSQL DUMP as far as possible and not be file of direct backup data,It is one presses Weekday below will the script that data annulus abide backs up:The interval of backup and cycle are OK the demand that the basis backs up is firm
? ?
? ? / Home/mysql/bin/mysqldump -S/data/app_1/mysql.sock -umysql Db_name | Gzip -f%26gt;/path/to/backup/db_name. `data +%w`.dump.gz
? ?
? ? is written in CRONTAB accordingly is commonly:
? ? * 6 * * * /home/mysql/bin/mysqldump -S/data/app_1/mysql.sock -umysql Db_name | Gzip -f%26gt;/path/to/backup/db_name. `data +\%w`.dump.gz
? ?
? ?Notice:
? ?
? ? 1 needs transferred meaning to become '\%' in the '%' in Crontab
? ?
? ? 2 according to log statistic,When applying laden lowest, be commonly in the morning at 6 o'clock
? ?
? ? backs up first pass to long-range backup server next in this locality,Perhaps establish account of backup of a database directly,Back up on long-range server directly,Back up remotely a script of need general above is medium - S /path/to/msyql.sock changes - H IP.ADDRESS can.
? ?
Of the refreshment of ? ? data and system upgrade
? ?
? ? is safeguarded daily and data is migratory:In data dish the hardware that the hard disk below the circumstance that was not destroyed is lowest of the life in the system commonly.And system (include operating system and MYSQL application) upgrade with hardware,Can encounter the problem with migratory data.As long as data is constant,Install good server first,Next direct data dish (hard disk 2) on installation,In needing to join the script that start Rc.local file afresh only,The system is restored very well.
? ?
? ? disaster restores:The time that destroys certainly below the circumstance that data itself is destroyed is nodded,Restore from inside backup data next.
? ?
? ?Applied design wants a place
? ?
? ? 1. Blame with the database cannot?
? ?
? ? database can simplify really the structure of a lot of application is designed,But itself also is natural resources of a system uses up bigger application.So if a lot of application do not have the word of demand of very high real time statistic,In can recording file daily record completely first,Periodic guides follow-up statistic is done to analyse in the database.If still need to record 2 dimension to express a structure,DBM structure can be used if the structure is enough and simple.Although need service data warehouse,If do not have too complex data integrality demand,apply change,The commercial database that need not use those completely to support external key.
? ?
? ? 2. The main bottleneck that the database serves:The join of single service is counted to an application,If if the database expresses structural design to be able to come according to the model type of database principle,be being designed,And had used the MYSQL of newest version,And moved according to the means that optimizes quite,The join that so final main bottleneck depends on single service commonly is counted,Although a database is OK support is intercurrent 500 join,Also had better not use application to this degree,Because intercurrent join has counted majority to serve itself to be used at attempered line according to the library,the expense of Cheng also is met very big.If if apply,allowing so:Make a machine much run service of a few MYSQL partakes.the service balanced program serves port to go up to many MYSQL:For instance App_1==%26gt;3301 App_2==%26gt;3302. . .app_9==%26gt;3309.The machine of memory of a 1G runs on 10 MYSQL are very normal.Let 10 MYSQLD assume efficiency of 1000 intercurrent join to should assume 1000 efficiency than letting 2 MYSQLD tall much.Of course,What also can bring process designing of a few application to go up so is complex degree;
? ?
? ? 3. Use alone database server (do not grab memory with downstage WEB service) ,MYSQL has more memory with respect to the has result market cache with can effective likelihood;
? ?
? ? 4. Application uses PCONNECT and Polling mechanism as far as possible,Use at saving MYSQL to serve to build connective to pay expenses;
? ?
? ? 5. The transverse fractionation of the watch:Let most the of 10% data that often is visited is put in a small the outside and the inside,The historical data of 90% is put in the outside and the inside of a file,Pass among data fixed " move " and delete useless data to save regularly.Always undertake choosing in 10% data to application so,The cache that is helpful for data quite,Number is recorded to there still is taller efficiency in above of 100 thousand class to odd souvenir in counting on MYSQL.
? ?
? ? 6. The fore-and-aft fractionation of the watch (transfer model change) :all fixed length fields (Char, int) put in a the outside and the inside,All lengthening field (Varchar, text, blob) put in another the outside and the inside,Pass between 2 watches advocate key is associated,Such,Watch of fixed length field can get very big optimize (can use HEAP to express a kind even,Data is accessed completely in memory) ,Another principle also explains here,To us,The loss that uses fixed length field to be able to pass a space as far as possible exchanges those who visit efficiency to rise.MYSQL backs a variety of watch kinds,It is to was aimed at different applying to offer those who differ to optimize means actually;
? ?
? ? 7. The index of careful examination application is designed,Serving even in starting, join- - Log-slow-queries[=file] is used at dogging to analyse applied bottleneck.

...