Everything about database,how to,example,guide,

The Linux that learned for a long time,Be necessary to be done arrange note,The installation that is Mysql below takes notes, with common a few use methods.
Because I like to debug,optimize a system, used the executive efficiency of the program after a few options increase process designing when compiling installation so, I understand some likelihoods wrong, hope everybody points out. Mail:fLashc@21cn.com

? installs Mysql
# Tar Zxvf Mysql-4.0.14.tar.gz -C /setup
# Cd /setup/mysql-4.0.14
# Groupadd Mysql
# Useradd Mysql -g Mysql -M -s /bin/false
# . / Configure- - Prefix=/web/mysql \ appoints installation catalog
- - mode of Debug of Without-debug \ purify
- - With-extra-charsets=gb2312 \ adds Gb2312 Chinese character to support
- - the collection version that Enable-assembler \ uses function of a few character
- - Isam of Without-isam \ take out expressed type support to use Isam to express rarely now is a kind of watch that depends on platform
- - the watch that Innodb of Without-innodb \ take out expresses supportive Innodb is processing of general affairs of a kind of support, suit company level application
- - library of Pthread of With-pthread \ requisition (library of Posix line Cheng)
- - Enable-thread-safe-client \ compiles client end with line Cheng means
- - With-client-ldflags=-all-static \
- - With-mysqld-ldflags=-all-static \ compiles service end and client end with means of pure static state

# Make
# Make Install
# Scripts/mysql_install_db \ makes Mysql user database and watch file
# Cp Support-files/my-medium.cnf /etc/my.cnf \copy configures a file, have Large, medium, three environments issue Small, according to machine function choice, if negative charge is bigger, the memory that can revise a few variable inside uses a value
The Mysqld file that # Cp Support-files/mysql.server /etc/init.d/mysqld \copy starts
# Chmod 700 /etc/init.d/mysqld
# Cd /web
# Chmod 750 Mysql -R
# Chgrp Mysql Mysql -R
# Chown Mysql Mysql/var -R
# Cd /web/mysql/libexec
# Cp Mysqld Mysqld.old
# Strip Mysqld
# Chkconfig- - Add Mysqld
# Chkconfig- - Level 345 Mysqld On
# Service Mysqld Start
# Netstat -atln
Look have the port of 3306 to open, if Mysqld cannot be started, look / below Web/mysql/var make mistake log, it is the issue with catalog good without the setting attributive commonly
# Ln -s /web/mysql/bin/mysql /sbin/mysql
# Ln -s /web/mysql/bin/mysqladmin /sbin/mysqladmin
# Mysqladmin -uroot Password "youpassword" # installs the password of Root account
# Mysql -uroot -p
# inputs the password that you installMysql%26gt;use Mysql;Mysql%26gt;delete From User Where Password=%26quot;%26quot;; # is deleted use at this machine account of faceless connective empty passwordMysql%26gt;flush Privileges;Mysql%26gt;quit

The interface that ? installs DBI and DBD For Mysql to be used at offerring Perl to visit Mysql database is normative, affirm you had installed Perl please, general and acquiescent system mount

# Cd /setup
# Tar Zxvf DBD-mysql-2.9002.tar.gz -C /setup
# Tar Zxvf DBI-1.38.tar.gz -C /setup
# Cd DBI-1.38
# Perl Makefile.PL
# Make
# Make Test
# Make Install
The method because of the Perl in some Perl programs is / Usr/local/bin/perl, and acquiesce of red cap system is / Usr/bin/perl, had better make a connection so, assure compatibility
# Ln -s /usr/bin/perl /usr/local/bin/perl
# Cd. . / DBD-mysql-2.9002
Generate installation to configure a file to need to add a few parameter after Perl Makefile.PL, more troublesome, we can arrive through adding / the join of this program solves Web/mysql/bin/mysql_config problem
The system can search this command automatically to generate the database parameter that installs a need
# Ln -s /web/mysql/bin/mysql_config /sbin/mysql_config
# Perl Makefile.PL
# Make
I always run Make Test here the half makes mistake,But the test scenario that can run Mysql catalog to fall is after installation, the Mysql that does not know 3.x how)
You can run test scenario now, rate is very slow nevertheless, it is good to hang backstage
# Perl /web/mysql/sql-bench/run-all-tests- - User=root- - Password="youpassword "


Add:
A few useful option with ? common Mysql and commandMysqld-----Open log supports
- - Log
- - Log-update
- - Log-long-formart
? MysqlGrant All On Database. * To User Identified By "password" builds one User user, gift all attributive of its Database databaseThe droit that Revoke All On Database From User moves to go up in Database database except User user is restrictedMysql -uroot -p- - One-database Databasename%26lt;File.sql guides from File.sql file Sql statement arrives Databasename in this database
? MysqladminMysqladmin Flush-logs commands Mysqld opens a new log document afresh, it is file of clear sky old daily record, be equivalent to metempsychosis
? Mysqldump
- - Add-drop-table derives statement of Droptable If Exists is added when Sql statement
- - Quick derives quickly
- - Opt optimizes option (recommend)
? Myisamchk
The examination expresses option
- - Extend-check patulous examination
- - an examination option with Medium-check more eclectic career
Repair expresses option
- - Recover rehabilitate expresses a file
- - Safe-recover safe rehabilitate expresses a file, than- - Recover wants slow
Got-up rehabilitate is expressed
- - Force keeps clear of compulsively last temporarily file
- - the content that Quick makes a file according to element only has repair
The management of ? Mysql
The environment that can use Phpmyadmin(to need Php supports) support with Mysqlcc(linux graph interface, WINDOWS system supports)

...

Debug an environment:The installation of system of Redhat9.0 Apache1.3.29 Mysql3.23.58 PHP4.3.4 Linux I was not told,This is basic skill,Actually this article is in the other Linux of similar Redhat also should general,The method that everybody wants to master me to offer only goes.Remember installing Redhat9.0 when the Apache that does not install systematic acquiesce,Mysql and Php and relevant software.Had installed delete the bag that has installed with Rpm -e * please.

1. installs Mysql3.23.58

The Rpm package that actually website of government of Mysql of plainspoken direct installation offers also is a more feasible method,The Rpm of his official website wraps offer be being issued basically with Tar bag is synchronism,At this o'clock I am preferred,The circumstance that Mysql library file won't appear to cannot be found in installing the retrospective that Rpm wraps to debug at least.But here still is necessary to interpret the move that installs from the definition,After all the netizen still says to hold out from what define installation much.

Software is gotten:http://www.mysql.com/downloads/index.html

Install measure:


...

# Cd /usr/src
# Tar -zvxf Mysql-3.22.25-pc-linux-gnu-i686.tar.gz (produces Mysql-3.22.25-pc-linux-gnu-i686 catalog)
# Cd Mysql-3.22.25-pc-linux-gnu-i686
# . / Configure- - Prefix=/usr/local/mysql (among them parameter set purpose installs method)
# Make
# Make Install

When the attention is running Configure,The installation destination route of set MySQL is / Usr/local/mysql,This method is used even in succeed installation process.Next,Build original database:

# Scripts/mysql_install_db

This command will be in / two databases are built below Usr/local/mysql/var/ :Mysql and Test,Among them former the attributive management database that is MySQL,Latter is to let you do what the exercise uses.Notice:If you install fruit MySQL in used,Initiative database has existed,Do not need to be reinstalled again.

Finally,The MySQL that start:
# Cd /usr/local/mysql/bin
# . / Safe_mysqld%26amp;

If you hope linage of voluntary when the machine is started movement occupies a library,serve,Can join the starting command above / in Etc/rc.d/rc.local file.

Now,The data processing that MySQL has run to rose and await you commands!But,Must take care:The Root administrator that you had not been you installs a password,The database that anybody can revise you follow one's inclinationsly (include most main attributive library) !If believing,Make the following exercise (assume our present list is / Usr/local/mysql/bin) :

Try user of try out random to login,Move:

$ . / Mysql -u Root

Can carry software in order to enter %26quot;mysql%26gt;%26quot; client to hint accord with keeps abreast of instantly any data processing are operated and do not need any password all right;This explains,The password that place of Zhang date and your Unix Zhang date uses the administrator "root" of MySQL can be different.To add countersign to Root Zhang date,Carry out:

# . / ' of new word of command of Mysqladmin -u Root Password '

Carry out with average user body again:

$ . / Mysql -u Root

Criterion the system can hint input password,The password is incorrect reject a visit.Actually,Although you are Root user,If ambiguous demonstrate uses a code,The system also is to refuse all the same:

# . / Mysql -u Root

Or

# . / Mysql The system is answered:
ERROR 1045: Access Denied For User: 'root@localhost' (Using Password: NO)

Use only - P parameter,Explain you want to use a code clearly,The system just can hint you input a password,It is when input code is right square accessibility:

# . / Mysql -u Root -pPassword:******** (inputs countersign)
Welcome To The MySQL Monitor. Commands End With; Or G.
Your MySQL Connection Id Is 33 To Server Version: 3.22.25

Type 'help' For Help.

Mysql%26gt;

Key in Exit to return Shell.

Script Mysql.server (in be located in `share/mysql' catalog) be used at starting or stop MySQL server:
Shell%26gt;Mysql.server StartShell%26gt;Mysql.server Stop

This script starts a server through carrying out Safe_mysqld actually.Stop a server to also can come true through running a program:
Mysqladmin Shutdown

Also can join `/etc/rc.local' file through will undermentioned command,Start MySQL automatically when the system is started:

/ Bin/sh -c 'cd /usr/local/mysql; . / Bin/safe_mysqld %26amp;'

...

A lot of use a project, all sorts of functional requirement basically can come true with MYSQL database when just starting,As the grow in quantity that uses an user,The addition of data bulk,MYSQL appears gradually the circumstance that can'ts bear heavy burden:Join delays very slow even machine,Have then to the demand of ORACLE from MYSQL change data,Applied process also should be done accordingly a few revise.Oneself sum up a the following note,The hope is helped somewhat to everybody.

1 .The data type that grows automatically is handled
The data kind that MYSQL has automatic growth,This field need not be operated when inserting a record,Can obtain data automatically to be worth.The data type that ORACLE did not grow automatically,The alignment order that needs to build one each to move growth,The next value that an alignment name wants when inserting a record is endowed with hereat field.

The name of number of serial of CREATE SEQUENCE (had better be watch name + serial number labels) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;
Among them the greatest value is decided by the length of the field, if the serial number NUMBER(6) of the automatic growth of the definition, maximum is 999999
INSERT statement inserts this field value to be: The name.NEXTVAL of serial number

2.The processing of only quote
String can have with double quote bag in MYSQL,A string can be wrapped with only quote only in ORACLE.Replace in what only quote must make before insert and altering string:Replace an only quote that all occurrence into two only quote.

3. Turn over the processing of the SQL statement of the page
The SQL statement that MYSQL processing reverses a page is simpler,Begin the position with LIMIT, record a number;The place that result market still can reach with SEEK fixed position in PHP.The SQL statement that ORACLE processing reverses a page is trivaller.Every result collect has field of a ROWNUM to indicate only its position, and can use ROWNUM%26lt;100 only, cannot use ROWNUM%26gt;80.
It is two kinds of better ORACLE after the course is analysed below statement of SQL turning over a page (the field name that ID is only key word) :
Statement one:
SELECT ID, [FIELD_NAME, . . . ] FROM TABLE_NAME WHERE ID IN (SELECT ID FROM (SELECT ROWNUM AS NUMROW, 2) WHERE NUMROW of condition of 1 ORDER BY of condition of ID FROM TABLE_NAME WHERE%26gt;80 AND NUMROW%26lt;3; of condition of 100) ORDER BY

Statement two:
SELECT * FROM ((SELECT ROWNUM AS NUMROW, c. * From (select [FIELD_NAME, . . . ] 2) C) WHERE NUMROW of condition of 1 ORDER BY of condition of FROM TABLE_NAME WHERE%26gt;80 AND NUMROW%26lt;3; of condition of 100) ORDER BY

4 .Grow the processing of string
The processing ORACLE of long string also has its special place.Length of string of the biggest exercisable is less than when INSERT and UPDATE be equal to 4000 monomial sections, if want to insert longer string, consider a field please with CLOB type,The method uses the DBMS_LOB course that takes oneself in ORACLE to wrap.Before inserting modification record, must do undertake sky and length are judged is not,Cannot be worth for empty field and exceed length field value to should put forward to warn, return operate last.

5. The processing of date field
DATE of cent of MYSQL date field and TIME two kinds,ORACLE date field has DATE only,Contain information of date time second,The systematic time that uses current database is SYSDATE, accurate to the second,Perhaps change into date function TO_DATE(with string ' 2001-08-01 ' , ' YYYY-MM-DD ' ) year - month - : ?ind the format YYYY-MM-DD HH24:M of ? secondI:SS TO_DATE() still has pattern of a lot of kinds of date, can consult ORACLE DOC. Date field is changed into string function TO_CHAR(' 2001-08-01 ' , ' YYYY-MM-DD HH24:MI:SS ' )

The mathematical operation formula of date field has very big different.MYSQL is found from present time 7 days use DATE_FIELD_NAME%26gt;SUBDATE (NOW () ,INTERVAL 7 DAY) ORACLE is found use DATE_FIELD_NAME %26gt;SYSDATE - 7; 7 days from present time

6. The processing of empty character
The is not empty field to also be free content of MYSQL,Blame empty field was defined to not allow to praise available content in ORACLE.The NOT NULL that presses MYSQL will define ORACLE to express a structure, the mistake can arise when derivative is occupied.Because this guides want to undertake to empty character when data,judge,If be NULL or empty character,The string that needs to change it into a blank space.

7.The faintness of string is compared
Field name is used in MYSQL Like '% string %' , field name also can be used in ORACLE %' of Like '% string but this kind of method cannot use index, speed is unhappy,Compare name of function Instr(field with string, ' string ')%26gt;0 can get more accurate search a result.

8.In program and function,Result collect and finger notice please after the work that handles a database is finished release.

...

The article described MySQL,One kind uses tripartite database to develop electronic commerce and other the effective tool of complex, dynamic website.The SQL server that MySQL is Cheng of a kind of fast, multi-line and complete function.Besides the basic architecture that describes MySQL system,The article still offerred the simple give a demonstration that writes with Tcl and C++ ,The Web application that assists you to develop supportive database.

An applied process that must store or visits a large number of information can benefit a lot from inside using product of tripartite database.When undertaking, must be in the visit to information on the many example of the program is such more.The application that is based on Web (include electronic trade) be it is good and paradigmatic.

Why to use independent database?

Web server must make its handle script to method will store about the state information that after offerring its, visits.Although use a few more primitive method likely- - for example dump confuses your database to what text file or development abstain- - but all services that only mature database application ability provides more complex Web application place to need.Because have a few,the software package that acquires freely can be used at this purpose,Write so custom-built do not have too auspicious place on database engine of application especially.Additional,In using tripartite database to still make Web developer need not throw development and the job that maintain a database.

MySQL database

Compile through using script language make peace model systematic language (for example C) ,the database compositive it is easy to comparative possibly to Linux application.The MySQL that can obtain freely (issue below GNU Public License) the database provided a series of complex SQL functions,And easily compositive in application.MySQL is fast, of multi-line Cheng,Support ANSI and ODBC SQL level.Add software of on tripartite,The watch of the clerical security that MySQL uses at general affairs to handle manage application with respect to support.

Note:What is clerical processing?

General affairs is the logarithm that need carries out with atomic means do according to library place a series of changing.Their or must be carried out entirely,Or is not carried out.For example,When the product is sold on Web all indispensible databases change composition a general affairs.

Database need subtracts at the same time client account remaining sum and product inventory,Fail otherwise and an operation is not carried out.

Why to no matter the server is stemmed from,plant reason happening breaks down should not cause general affairs to be carried out by the part.For example bill calculates more, the product does not have consign,Or inventory is disloyal the result that waiting is the work that completes partly likely.

The database that supports clerical processing is OK in enclosing code of a group of databases in a general affairs,Any failure during general affairs is carried out can let a database answer the condition before rolling general affairs to begin.

This is the daily record that handles through maintaining all databases,And of the deputy original implementation that its savagery expresses,Allow to answer when the server is started afresh the next time after failure roll an operation.The expense on this kind of time and space is clerical safety a database system place is indispensible kind eclectic.

Onefold MySQL server is controlling a series of databases,They can be visited through the server with similar pattern.The watch that every database is a group of aleatoric amounts actually,The user of concept and other SQL database is similar.Every watch lists composition by the data that takes a kind.Data can be integer, real value, string or other type,Include primitive binary system to flow.Expressing medium every group is a memory record in the database.

MySQL is designed and construction becomes server of client computer / .Server Mysqld can be in can move from any machines that Internet visits (had better be with Web server on same table or a the most adjacent machine,Answer time reasonably in order to ensure) .MySQL client computer uses a request to be contacted with MySQL server,The database that revise or inquires server place to have.In the Web application program that supports a database,Database client computer is Web server or the CGI script that arise by Web server.These client computer can use high-class script language or elementary system language is written,The database API that should be put in this kind of language only can.In Linux,Most script language is with what C comes true,Because of existence MySQL C API,The script language that should add MySQL support so or tool should be very easy.Majority script language had completed this one step.

MySQL API

MySQL API is usable at all sorts of languages,Include almost all the language that writes place of website back end to be used actually.Use these API,We are OK compose is built by client computer of MySQL of Web server pilot.

API (use at the database to visit) work in order to be based on connective mode.The first thing that client computer must do is to open the join with MySQL server.The user name that this includes to use a server to know appropriately and countersign will undertake identity attestation to join.After establishing link,The specific database that the server chooses to want to use.After deciding initialization,Process of client computer application (script of server square CGI is for us) undertake with one of two kinds of means with respect to can free land and database alternant:Can run groovy SQL to command,Include to add and delete a watch,And add a record to them;Also can run inquiry to returning the database of the result.The record that inquiry makes group and one inquiry match,Next,Client computer is OK one by one visits a record,Until examine all records,Or the record that client computer cancels of short duration to hang retrieves.After the operation that once script finished logarithm,occupies a library,Be shut with the join of the server.

The website that wants compose to build compositive database to visit,Need writes CGI script to make dynamic kill according to database condition.Web server starts CGI script,In outputting proper formatted HTML their standard output to flow next.A client computer delivers it after Web server catchs HTML,As the request be undertake to static HTML page in that way.In the course that builds HTML,Script can revise a database,Also can inquire and incorporate the result in their output.

As a give typical examples that explains afore-mentioned processes simply,The code below (write with C and Tcl) the database of the product detailed account that inquiry includes some company supply and marketing to make work.All character that this did not use MySQL API of two kinds of languages absolutely,But offerred simple and easy and fast, patulous give a demonstration,Can carry out any SQL to command to database content.In this in,Script showed all products under specific price.In practice,The user may input this price in Web browser,Send a server it next.Our leave out the detail that undertakes from inside environmental variable fetch decides HTML expresses only cost reading,Do not have what difference because of it and the circumstance that in supporting the CGI script of the database, carry out.For clear for the purpose of,We were installed beforehand suppose a few more specific parameter (the price that wants inquiry for example) .

The following code is to use what the Tcl Generic Database Interface that obtains freely comes true with Tcl.The advantage of a kind of such interface depends on Tcl is an explanation model,Can undertake be developed quickly and be modificationed quickly to code.

Tcl give typical examples


...

? ? if you are a cycle racing hand and can change instantly by pushbutton engine and do not need to drive the car in the garage to change,That meeting is how to feel?MySQL database is what development staff place does to be like is to press pushbutton to change engine;It lets you select database engine,Come to your simple way switch it.

Of ? ? MySQL taking engine oneself was to be used quite for certain,But below some circumstances,Other engine may want to suit to finish the job more than what at hand uses.If be willing,The database engine that you can use MySQL++ API to found your even,Resemble hitting the carburettor that wears air cylinder him mount.Let us see you how should select engine now,And how be between usable engine switch.

? ?Select your engine

When MySQL is being installed, the database engine that you can use ? ? depends on is how be compiled.Should add a new engine,Must compile MySQL afresh.The concept that compiles applied process to add a character merely may be very strange to the development staff of Windows,But in UNIX world,This had made a standard.Below default circumstance,MySQL supports three engine:ISAM, MyISAM and HEAP.Additionally two sort InnoDB and Berkley (BDB) ,Often also can use.

? ?ISAM

? ? ISAM is a definition is made clear and the data table of trial of time of all previous classics manages a method,The frequency that when it is being designed, is inquired considering the database wants ambitious at updated number.Because of this,ISAM is carried out read the rate that takes an operation very fast,And do not take up many memory and memory resource.Of two of ISAM main inadequacy at at,It does not support general affairs to handle,Also cannot enough fault tolerance:If your hard disk broke down,So data file cannot restore.If you are using ISAM in process of crucial task application,That often must back up you all real time data,Duplicate through its characteristic,MySQL can support such backup application program.

? ?MyISAM

The ISAM patulous format that ? ? MyISAM is MySQL and default database engine.A large number of functions besides the reference that provides the place in ISAM to do not have and field management,The mechanism that MyISAM still uses lock of a kind of form to decide,Will optimize many concurrent read keep an operation.Its cost is you need to often run OPTIMIZE TABLE to command,The space that will restore to be wasted by newer mechanism place.MyISAM still has a few to expand usefully,The MyISAMChk tool that uses repair database document for example and with the MyISAMPack tool that will restore to waste a space.

? ? MyISAM emphasized fast speedread taking an operation,This may be why the main reason that MySQL got Web develops such favour:The mass data operation that in Web development you have is to read take an operation.So,Provider of most fictitious lead plane and Internet platform provider (Internet Presence Provider,IPP) allow to use MyISAM form only.

? ?HEAP

? ? HEAP allows to be stationed in those who stay in memory only temporarily form.Halt stay in memory to let HEAP want to compare ISAM and MyISAM fast,But the data that it manages is not stable,And if closing,did not undertake saving before machine,So all data can be lost.When data is deleted all right,HEAP also won't waste much space.HEAP form needs to use SELECT expression to choose and be held in you when dominating data very useful.Should remember,Delete form after gone form.Let me again come again:Give out in you after form,Do not forget delete form.

? ?InnoDB and Berkley DB

? ? InnoDB and Berkley DB (BDB) the direct product that database engine is the technique that trains MySQL flexibility,This technology is MySQL++ API.When using MySQL,Each challenge that you face results from almost ISAM and MyISAM database engine do not support clerical processing to also do not support ab extra key.Although want to compare ISAM and MyISAM engine a lot of slower,But the support that InnoDB and BDB included pair of general affairs processing and ab extra key,It is place of before two engine these two bits those who do not have.The place before be like is narrated,If your design needs these character medium one person both perhaps,One of two engine after then you are about to be forced to use.

If ? ? feels him really the technology is excellent,The database engine that you still can use MySQL++ to found your.The function that this API offerred operation field, record, form, database, join, safe account for you,And build such as MySQL what such DBMS place need is all and other countless functions.The limits that explained API to had exceeded the article deep,But the presence that you need to understand MySQL++ reachs his the technology of exchangeable engine backside,This are very important.The model that estimates engine of database of type of this plug-in unit can be used to even the XML that founds this locality for MySQL is offerred implement (XML Provider) .(any MySQL++ that read the article develop personnel to be able to regard this as is a requirement.)

...

Express an operation more

In a database,The likelihood is put in many watches,These watches are mutual correlation.We continue to use the case in front.A few basic information that stuff contained in the list that builds in front,Like full name, sexual distinction, give unripe date, birthplace.We found a watch again,The article that this watch uses at describing stuff place to publish,Content includes title of author full name, article, publish date.

1, the content that examines Mytable of the first watch:


...

Administrator of ? ? MySQL should know to be able to join how through appointing what user server, have link from where,And what to do when join,Will set date of MySQL user Zhang.The statement that MySQL3.22.11 introduced two to have this job more easily:GRANT statement founds MySQL user and appoint its attributive,REVOKE statement deletes attributive.The front that the accredit in appropriative Mysql database expresses these two statements,Offer operate directly these optional methods that express content.GRANT and effect of REVOKE statement are expressed four times below:

? ? accredit expresses content

Any global prerogatives that ? ? User can join the user of the server and they are had

The prerogative of database class of ? ? Db

? ? Tables _ Priv expresses class prerogative

? ? C O L Um N S _ Priv lists class prerogative

? ? still has the fiveth accredit to express (Host) ,But the effect that it does not suffer GRANT or REVOKE.

When ? ? issues GRANT statement for a certain user when you,One should be founded for this user in User watch.If this statement assigned privilege of all overall situation (administrative limits of authority or the limits of authority that are used at all databases) ,Criterion these designation also be by the record in User watch.If appointed the attributive of database, watch or row,They are the record in Db, Tables_priv and Columns_priv watch.

? ? use GRANT and REVOKE sentence are lighter than revising accredit to express directly.But,Suggest you had better complement through reading the 12nd rule the content of this chapter,The bat around in the 12nd chapter accredit is expressed.These watches are very important,Serving as an administrator to should know these tables is how to be what GRANT and go to work of form of REVOKE statement make.

The Zhang number that ? ? will discuss below the section how to install MySQL user partly originally and accredit,How to still cancel the introduction attributive and delete all user from inside accredit watch,The difficult problem that and will consider puzzles a lot of new MySQL managers.

You consider ? ? even use Mysqlaccess and Mysql_setpermission script,They are the component that MySQL issues a package.These script that are Perl,The substitute that they provided the GRANT statement that sets user Zhang date.The supportive environment that Mysql_setpermission needs to have DBI.

? ?Found new user and accredit

The grammar of statement of ? ? GRANT is as follows:

? ? GRANT Privileges (columns)

? ? ON What

? ? TO User IDENTIFIEDBY "password "

? ? WITH GRANT OPTION

? ? should use this statement,Need fills in to be divided with bottom:

The attributive of user of admeasure of cent of ? ? Privileges.Next watches listed the limits of authority that can use in GRANT statement shows symbol:

? ? attributive shows the operation that accord with attributive allows


...

When working to go up in very big list,The small subclass that you may need to run a lot of inquiry to obtain a mass data now and then,Either run these inquiry to whole watch,The minority that allows MySQL to find out place to need every time however is recorded,the record the choice reachs to express a likelihood temporarily some faster,Next these many watches run inquiry.

...

? ? is in Soundbreak we everyday 24 hours of uninterrupted ground broadcast frequency of what is actually happening and video,So to the adds newly duplicate character of MySQL,We cannot be made make a person very much the test of be convinced.Through checking us to discover,Can use this character to maintain data synchronism with backup database server,Such becoming advocate when the server handles be no longer in force because of some kind of reason,Can use backup machine to handle all inquiry.To such requirement,It is not difficult to configure two servers.My general bat around is whole handle a process,Discuss to become at the same time advocate when server invalidation,How to use PHP to weigh directional inquiry.

Interior of ? ? MySQL is duplicate the function is to build be in two or between two above server,Adopt set between them advocate - will come true from the relation.Among them one is made give priority to a server,Of other as from the server.How do I configure bat around two servers,Set give priority to a server,Another is set for from the server.And be described the process is handled in what switch undertakes between them.I am the configuration setting process that has on the 3.23.23 version of MySQL,The test that and also be has on this version.Proposal of MySQL development staff had better use newest version,And advocate - all use identical version from the server.At the same time MySQL 3.23 version remains Beta test version,And this version cannot is down possibly compatible.So because of this reason,In actual website,I had not used this version now.Having fault tolerance ability to have a profit is,Below the condition that does not need to suspend any inquiry,Undertake upgrading to the server.

The first pace:Configuration advocate server
? ? is in of this article in leaving space,My general appoints two servers.A (IP is 10.1.1.1) make give priority to a server (abbreviation is lead plane) .B (IP is 10.1.1.2) as mothball server (abbreviation is equipment aircraft) .

The implementation process of the duplicate function of ? ? MySQL is:Equipment aircraft (B) and lead plane (A) joins,Numerate next the binary system of lead plane updates a daily record,Incorporate the change that produce again from already in the database.Equipment aircraft needs an user account to join with lead plane,Establish an account on lead plane so,Give it FILE limits of authority only,Operate as follows:

GRANT FILE ON * . * TO Replicate@10.1.1.2 IDENTIFIED BY 'password';

? ? joins with lead plane quite for equipment function,'FLUSH PRIVILEGES' should move on lead plane,Not afraid nevertheless,The server will stop in the measure below because of us.

We need ? ? now a snapshot of lead plane database,And undertake to lead plane configure,The newer daily record that allows to make a binary system.Compile 'my.cnf' document above all,So that allow a binary system to update a daily record,Be in so [of Mysqld] part below a certain place adds group:'log-bin' .Be in the next time when the server is started,Lead plane will generate a binary system to update a daily record (the name is:%26lt; leader name %26gt; - Bin. #%26gt;) of %26lt; increment serial number.Update a daily record to allow a binary system effective,Shut MySQL to serve a program,The place know exactly about sth that boards lead plane next arrives according to library catalog in another catalog,Start Mysqld afresh then.
Got all databases certainly please,When undertaking duplicating otherwise,If a watch exists on lead plane but nonexistent on equipment aircraft,Will because of make mistake and exit.The snapshot that you had gotten data now,Mix from the binary log since building snapshot,There is what any logarithms occupy a library to revise above.Notice MySQL data file please (*.MYD,*.MYI and *.frm) are those who rely on file system,So you cannot be transmitted into travel file merely,If arrive from Solaris Linux.If you are in a heterogeneous service system environment,You get the custom-built base that must use Mysqldump utility program or other data snapshot originally.

The second pace:Deploy equipment aircraft
? ? lets us continue.The MySQL that stops equipment aircraft to go up serves a program,And move the database catalog that comes from the copy on lead plane to the Data catalog on equipment aircraft fall.Affirm the owner catalog is mixed please belong to group of changes to be worth accordingly for MySQL user,And revise file mode mix to owner only for 660(belong to the group is readable, can write) ,Catalog itself is mixed to owner only for 770(belong to the group is readable, can be written and executable) .

? ? continues.On equipment aircraft the MySQL that start serves a program,It is normal to affirm MySQL works.Run a few Select to inquire (do not want Update or Insert inquiry) ,The data snapshot that has a look to get in the first pace is successful.Then,MySQL is put out to serve a program after the test is successful.

The leader that ? ? needs to visit in the configuration on equipment aircraft,So that receive leader change.The 'my.cnf' document that needs to edit Wu machine to go up so,In [increase in Mysqld] part below a few:
Master-host=10.1.1.1Master-user=replicateMaster-password=password

After ? ? is starting equipment aircraft to serve a program,Program of equipment aircraft service will examine the leader that appoints in 'my.cnf' file,Examine whether to have a change,And incorporate these changes arrive from already in the database.The newer record that equipment aircraft held lead plane,These records are receive from inside the 'master.info' file of lead plane come down.The condition of Cheng of equipment aircraft line can command through Sql 'SHOW SLAVE-STATUS' sees.If,be in on equipment aircraft in log of processing binary system
Produce a mistake,Exit those who bring about Cheng of equipment aircraft line,And be in the log file of *.err make a news.Next the mistake can be corrected,Can use Sql statement 'SLAVE START' to start Cheng of equipment aircraft line afresh then.Line Cheng continues the place that interrupts from processing of log of lead plane binary system to handle.

? ? so far,On lead plane the data change of place happening should have duplicated to go up to equipment aircraft,Should check it,You can be inserted on lead plane or update a record,And on equipment aircraft the choice is recorded this.

We had ? ? to arrive from A machine now of B machine this kind advocate - from the relation,May become when A machine so machine when,Allow us to weigh all inquiry directional go up to B machine,But when A machine restores,The change that we will produce without method restores to go in A machine.To solve this problem,We are founded arrive from B machine of A machine advocate - from the relation.

...