Everything about database,how to,example,guide,

Everything about database,how to,example,guide, - MySQL Tutorial http://teachmesql.net/MSSQL Tutorial,MySql Tutorial,access Tutorial,Foxpro Tutorial,Oracle Tutorial,PowerBuilder,how to database,intro to database,intro to mysql,intro to sql,intro to foxpro, - RainbowSoft Studio Z-Blog 1.8 Arwen Build 81206zh-CNCopyright 2008 teachmesql.net. Some Rights Reserved.Fri, 30 Jul 2010 03:29:22 +0800The inquiry that MySQL inquiry optimizes series lecture is optimized implementa@b.com (rain)http://teachmesql.net/post/599.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/599.html
? ? of course,MySQL inquiry is optimized implement also used index,But it also used other a few information.For example,If you refer following shows inquiry,No matter data is expressed,have so how old,The rate that MySQL implements it is met very fast:

...]]>MySQL Tutorial http://teachmesql.net/post/599.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=599http://teachmesql.net/cmd.asp?act=tb&id=599&key=c6df5213Mysql database optimizes five paces to goa@b.com (rain)http://teachmesql.net/post/589.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/589.html?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.
? ?

...]]>MySQL Tutorial http://teachmesql.net/post/589.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=589http://teachmesql.net/cmd.asp?act=tb&id=589&key=00025643Database study: MYSQL Join detailed is solveda@b.com (rain)http://teachmesql.net/post/590.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/590.html? ?
? ? 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");

...]]>MySQL Tutorial http://teachmesql.net/post/590.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=590http://teachmesql.net/cmd.asp?act=tb&id=590&key=1a4a5240About the perfect means of settlement of problem of Mysql 4.1 languagea@b.com (rain)http://teachmesql.net/post/591.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/591.html 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:

...]]>MySQL Tutorial http://teachmesql.net/post/591.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=591http://teachmesql.net/cmd.asp?act=tb&id=591&key=0500031fIn the note of the installation on the server, use MySQLa@b.com (rain)http://teachmesql.net/post/592.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/592.html
? ? 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.

...]]>MySQL Tutorial http://teachmesql.net/post/592.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=592http://teachmesql.net/cmd.asp?act=tb&id=592&key=5c9a0c3fThe means of settlement of mistake of MySQL Chinese sorta@b.com (rain)http://teachmesql.net/post/593.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/593.html
? ? 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.

...]]>MySQL Tutorial http://teachmesql.net/post/593.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=593http://teachmesql.net/cmd.asp?act=tb&id=593&key=eefb371dMysql learns an a bita@b.com (rain)http://teachmesql.net/post/594.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/594.htmlBecause ? ? 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.

...]]>MySQL Tutorial http://teachmesql.net/post/594.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=594http://teachmesql.net/cmd.asp?act=tb&id=594&key=35dd14aaPassword and solution of detailed of visit limitation setting are revised in MySQLa@b.com (rain)http://teachmesql.net/post/595.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/595.html
? ?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: ? ?

...]]>MySQL Tutorial http://teachmesql.net/post/595.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=595http://teachmesql.net/cmd.asp?act=tb&id=595&key=11b47e07MySQL inquires those who optimize the administrator of the lecture to optimize measurea@b.com (rain)http://teachmesql.net/post/596.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/596.htmlExplain 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 Tutorial http://teachmesql.net/post/596.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=596http://teachmesql.net/cmd.asp?act=tb&id=596&key=2e5477e7Fictitious user of VSFTP + MySQL configures a process to explaina@b.com (rain)http://teachmesql.net/post/655.htmlTue, 03 Mar 2009 09:37:52 +0800http://teachmesql.net/post/655.html? ?
? ?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.

...]]>MySQL Tutorial http://teachmesql.net/post/655.html#commenthttp://teachmesql.net/http://teachmesql.net/feed.asp?cmt=655http://teachmesql.net/cmd.asp?act=tb&id=655&key=c48b9786