Everything about database,how to,example,guide,

? ? MySQL has a lot of to be able to guide into the method of data,However these just are counted according to transmitting medium half,Another it is commonly derive from inside MySQL database data.Have a lot of reasons we need to derive data.A main reason is to be used at backup database.The cost of data often costly,Need handles them carefully.Often backup can be helped prevent valuable data ground to lose;Another reason is,Perhaps you hope to derive data will share.In the world that this IT grows ceaselessly,Share data to become commonner and commonner.

The large database that says Macmillan USA safeguards the works that protecting to will be published for example.This database is shared between a lot of bookshops,Such they know what book will be published very quickly.Hospital more and more the trend uses the anamnesis that do not have paper to record,Such these anamnesis can follow you at any time.The world becomes smaller and smaller,Information also is shared increasingly.In having a lot of, derive data gets a means,They follow guide data is very similar.Because of,After all,These are a kind of fluoroscopy gets way only.The data that the data that derives from the database guides from another end namely.We do not discuss other databases to derive variously here the method of data,How do you come to the society realize data to derive with MySQL.
? ?
? ? uses Mysqldump:
? ?
? ? (Mysqldump command is located in in Mysql/bin/ catalog)

Tool of ? ? Mysqldump a lot of sides are similar and opposite the tool Mysqlimport of action.They have a few same option.But Mysqldump can do more businesses.It can lade whole database in an alone text file.This file bag contains all rebuilding the SQL that your database place needs commands.This command obtains all mode (Schema,There is an explanation from the back) and change its into DDL grammar (CREATE statement,Namely the database defines a statement) ,Obtain all data,And found INSERT statement from inside these data.All this tool in the database designs you are inverse.Because all things are included,arrived in file of a text.This text file can use a simple batch processing and statement of an appropriate SQL to guide in returning MySQL.This tool makes a person fab ground is simple and fast.Will never have the least bit let poll be fond of ground place.
? ?
? ? because of this,If you resemble lading,the content of whole database Meet_A_Geek arrives in a file,Can use the order below:

P Meet_A_Geek of - of ? ? Bin/mysqldump%26gt;MeetAGeek_Dump_File.txt
? ?
Does this statement also allow ? ? do you appoint a watch to undertake Dump(backs up / derive / lade? ) .If you are the in expressing Orders whole content that hopes a database Meet_A_Geek is medium only,derive to a file,Can use the order below:

P Meet_A_Geek Orders %26gt;MeetAGeek_Orders.txt of - of ? ? Bin/mysqldump
? ?
? ? this exceedingly agile,The record that you can use WHERE subordinate clause to choose you to need even derives in the file.Should achieve such goal,Can use be similar to the command below:

Where="Order_ID of - of P of - of ? ? Bin/mysqldump%26gt;2000" Meet_A_Geek Orders%26gt;Special_Dump.txt
? ?
Tool of ? ? Mysqldump has many option,Partial options is expressed as follows:

? ? option / Option action / Action Performed

? ?- - Add-drop-table

? ? imposes sentence of DROP TABLE IF EXISTS before this option will express in each,Can assure to guide when answering MySQL database, won't make mistake so,When because guide every time,be being answered,Can check a watch to whether exist above all,Exist to be deleted

? ?- - Add-locks

This option meets ? ? bind a LOCK TABLE and UNLOCK TABLE statement in INSERT statement.This prevents to record the operation that is guided other user undertakes to the watch when the database again in these
? ?
? ? - C Or - Complete_insert

? ? this option makes Mysqldump command produces INSERT statement to add the above-listed to each (Field) name.Should derive data guide this option when another database is very useful.

? ?- - Delayed-insert adds DELAY option in INSERT command

? ? - F Or -flush-logs uses this option,Carrying out before deriving will the Log of refresh MySQL server.

? ? - F Or -force uses this option,Although have mistake happening,Still continue to derive

? ?- - Full in the sentence that this option also imposes additional information to CREATE TABLE

? ? - L Or -lock-tables uses this option,Derive when expressing, the server will add a lock to the watch.

? ? - T Or -no-create- Info

The Mysqldump that this option makes commands ? ? not to found CREATE TABLE statement,This option needs data only in you and do not need DDL (the database defines a statement) when very convenient.
? ?
? ? - the Mysqldump that this option makes commands D Or -no-data not to found INSERT statement.

? ? when you need DDL statement only,Can use this option.

? ?- - the option of the file that Opt this option will open all meetings to raise a file to derive speed and creation can guide more quickly.

? ? - Q Or -quick this option makes MySQL won't whole derived content reads in memory to be carried out again derive,Write when be read however guide in the file.

? ? - this option will found T Path Or -tab = Path two files,A file includes DDL statement to perhaps be expressed found a statement,Another file includes data.DDL file name Table_name.sql, data file name Table_name.txt. Method name is the catalog that deposits these two files.Catalog must have existed,And jussive is used person the privilege that has pair of files.
? ?
? ? - W "WHERE Clause" Or -where = "Where Clause "

? ? if in front what tell,You can use this one option to choose will put derive the data of the file.
? ?
? ? assumes you need to express the account that should use in sheet to build a file for,The manager should look this year (2004) all order (Orders) ,They are not interested in DDL,And need a document have comma space,Because guide very easily so,arrive in Excel.To accomplish this individual thing,You can use the sentence below:

Where %26quot;Order_Date %26gt;='2000-01-01' of - of P of - of ? ? Bin/mysqldump %26quot;
Fields-terminated-by= of - of No-create-info of - of Tab = /home/mark of ? ? -, meet_A_Geek Orders

This will get ? ? the result that you want.
? ?
? ? Schema:Mode

? ? The Set Of Statements, expressed In Data Definition Language, that Completely Describe The Structure Of A Data Base.

? ? defines the statement market that the language will come convey one group with data, the structure that this statement collect described a database thoroughly.
? ?
? ? SELECT INTO OUTFILE:

? ? if you feel Mysqldump tool is insufficient cruel,Use SELECT INTO OUTFILE, the order that MySQL offers to have adverse effect with command of LOAD DATA INFILE likewise,This is command of SELECT INTO OUTFILE,These two place that command the likeness that has a lot of.Above all,They have all option almost identical.You need to finish now in front with the function that Mysqldump fulfils,Can the measure below according to undertakes operating:
? ?
? ? 1. Ensure Mysqld process (service) having movinging

? ? 2. Cd /usr/local/mysql

? ? 3. Bin/mysqladmin Ping;/ / if this sentence has not been connected,Can use this:Mysqladmin -u Root -p Ping

? ? Mysqladmin Ping is used at detecting the condition of Mysqld,Is Alive specification is moving,Make mistake need user name and password possibly.

? ? 4. The MySQL that start monitors a program.

? ? 5. P Meet_A_Geek of Bin/mysql -;/ / enter Mysql command to go,And open database Meet_A_Geek, need inputs a password

? ? 6. In command travel,Input a command:

? ? SELECT * INTO OUTFILE '/home/mark/Orders.txt'
? ? FIELDS
? ? TERMINATED BY = ' , '
? ? FROM Orders
? ? WHERE Order_Date %26gt;= '2000-01-01'
? ?
? ? pressed Return in you (carriage return) later,The file was founded.The SELECT statement with this regular like sentence,Just weigh the output that considers screen directional arrived in the file.This imply this advanced inquiry that you can use JOIN to realize much watch.This characteristic also can be used as generator of a forms for reporting statistics.
? ?
? ? example says,You can combine the method that discusses in this one chapter to produce an absorbing inquiry,Try this:
? ?
? ? builds a text file that the name is Report_G.rpt in Mysql catalog,Join the travel below:

? ? USE Meet_A_Geek;
? ? INSERT INTO Customers (Customer_ID, last_Name, first_Name)
? ? VALUES (NULL, "Kinnard" , "Vicky");
? ? INSERT INTO Customers (Customer_ID, last_Name, first_Name)
? ? VALUES (NULL, "Kinnard" , "Steven");
? ? INSERT INTO Customers (Customer_ID, last_Name, first_Name)
? ? VALUES (NULL, "Brown" , "Sam");
? ? SELECT Last_Name INTO OUTFILE '/home/mark/Report.rpt'
? ? FROM Customers WHERE Customer_ID%26gt;1;

? ? affirms Mysql process is moving next,And you are in Mysql catalog,The command below the input:

? ? Bin/mysql%26lt;Report_G.rpt checks you to name the file that serves as output,The last name that this file will include all clients that you input in Customers watch.Be seen like you,You can use what acquire today to guide / derive (Import/export) the method will help get forms for reporting statistics.

...

? ? , run MySQL server in order to be not prerogative user

Before ? ? is discussing how to start MySQL server,Let us consider to should run MySQL server with what user identity.The server is OK the hand is moved or start automatically.If your hand is moved,start it,The server logs onto Unix with you (Linux) user identity is started,If you are used,start a server namely,It moves with Paul;If you use Su to command switch to Root,Carry next the server that start,Criterion it moves with Root.However,You may not think the hand moves the server that start below most circumstance,It is you arrange MySQL server to be started automatically when systematic guiding likely most,As the one part of standard guiding process,Below Unix,Should guide a process to be carried out by Unix user Root of the system,The course that why and allow runs in this process all moves with Root attributive.

...

? ? one, l of d of data definition d (Data Definition Language)

? ? data decides a language is the language that the format that points to pair of data and configuration give a definition,He is every data-base should build what should face above all when moment,Data ranginging from... to... distributes what form inside relation, form have what column advocate the relation that consults each other between key, form and form is waited a moment,It is to be in what when beginning, place must have planned.

...

? ? when you refer an inquiry,MySQL can analyse it,Look to whether can be done a few optimize the speed that makes handle this inquiry faster.This one part optimizes introductory inquiry implement be how work.If you think those who use to know MySQL,optimize a method,Can examine MySQL reference manual.

? ? 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:


...

This one part offerred ? ? how to choose data type to help a few guidance that increase inquiry traversal speed:


? ? does not want those who grow to use when can use short data to list.If you have fixed length of a solid,spent CHAR data is listed,The length that should not let it so exceeds real need.If you are in what store in data row,the longest value has 40 character,Do not define CHAR(255) ,And should define CHAR(40) .If you can use MEDIUMINT to replace BIGINT,So your data table is a few smaller (disk I/O is a few less) ,In computational process,The processing rate of the value is a few rapidder also.If data row was indexed,So the property that uses shorter value to bring rises more remarkable.Index not only can raise inquiry rate,And short index value also is worth processing to rise than chief index should a few faster.

? ? if the memory form that you can choose data to go,Should use what suit memory engine most so the sort of.To MyISAM data table,The data that the data line that had better use fixed length replaces alterable length is listed.For example,Let all character rows replace VARCHAR kind with CHAR type.Count the cost,We can discover data table used more disk spaces,But if you can provide extra space,The data that the rate that the data of so fixed length is treated all right spends than growing changeably wants all right a few faster.To those watches that are revised often,This are highlighted especially,Below those circumstances because of,Performance gets more easily the influence of disk fragment.

When ? ? is using the data of alterable length to go,Differ as a result of record length,Carrying out for many times after delete and be being operated newlier,The fragment that data expresses wants a few more.You must use OPTIMIZE TABLE to defend its performance regularly.The data of fixed length does not have this problem all right.

If ? ? appears the circumstance that data table breaks down,So the watch that data travel length secures constructs more easily afresh.When using fixed length data to go,The beginning of every record the position can be detected,Because these positions are the multiple of fixed record length,But when using alterable length data to go not certain.This is not the problem related to the function that inquires processing,But the repair rate that it can accelerate data to express certainly.

Although ? ? changes MyISAM data table into the data line that uses fixed length to be able to improve performance,But you need a consideration above all below a few problems:

The data line rate of fixed length of ? ? is rapidder,But the space that take up is larger also.Every value that CHAR(n) lists (even if empty value) occupy N normally character,Because store it until data table is medium,this is,Blank space can be added at the back of of the value.The space that VARCHAR(n) row has is lesser,Because need to allocate necessary character only number is used at memory to be worth,Add two on one byte to store the length of the value.Because of this,When undertaking choosing between CHAR and VARCHAR row,The contrast that is time and space actually.If speed is main consideration factor,So the performance advantage that gets fixed length kind with respect to row of use CHAR data.If the space is very serious,So list with respect to use VARCHAR data.Altogether,The data that you can think to secure length can improve performance all right,Although it took up larger space.But to certain and special application program,You may hope to use two kinds of means to realize a certain data to express,The requirement that runs a test to decide which kinds of circumstance accords with applied process next.

Although ? ? is willing to use fixed length type,Occasionally you also are used without method.For example,Grow to cannot use fixed length type at the string of 255 character.

The data that table of data of ? ? MEMORY uses fixed length at present stores all right,No matter use CHAR or VARCHAR line,did not concern accordingly.Both is to serve as what CHAR type handles.

? ? is expressed to InnoDB data,In-house travel memory format does not have divisional and fixed length and alterable length row (all data use the head index that points to data to list a value all right) ,Because this is in substantially,The CHAR kind that uses fixed length is not certain row of VARCHAR of more alterable than using length is simple.Consequently,Main property factor is the memory gross that data exercise uses.The space that takes up on average as a result of CHAR over VARCHAR,Because this uses VARCHAR to come the smallest the memory gross that changes the data travel that needs processing and disk I/O are better.

? ? is expressed to BDB data,No matter use firm,the data of alterable length is listed,The difference is not big.You can use two kinds of methods try,Run test of a few tests to detect whether to put in apparent difference.

? ? defines data kind into cannot be empty (NOT NULL) .This meeting makes processing rate rapidder,The memory of need is fewer.It still simplified to inquire occasionally,The NULL property that because be below certain circumstance,you do not need to examine a cost.

Data of use ENUM of ? ? consideration is listed.The cardinal number that if you have some a certain data,lists is very small (included different worth amount is limited) ,Can consider to list its changeover for ENUM so.ENUM value can be handled by faster ground,Because of them interiorly expression is numerical value.

? ? uses PROCEDURE ANALYSE() .The case that moving PROCEDURE ANALYSE() can see be listed in data table:


...

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.

...