Everything about database,how to,example,guide,

One, the MySQL that how obtains newest version?

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

Two, the note that installs MySQL moment?

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

Three, start and stop MySQL

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

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


Four, the use person that builds a MYSQL with Root

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


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

String type---STRINGS

Of string matching must be didymous,For example:

' MY IS OCP '

" MY IS OCPISCAI "


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

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

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

- %26gt; Hi! " Ocp " !


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

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

?

Digital type---NUMBERS

A few lawful numbers express kind in light of us:

7

777

- 777

77.77

77.777

- 777.7777o+7


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

?

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

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


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

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

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

ROUND (X) :Round arrives integral.

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

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

ABS (X) :Take absolute value.

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

Trigonometric function:

ATAN (X,Y) :Return trigonometric function

ATAN2 (X,Y) :Return trigonometric function

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

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

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

RADIANS (X) :Change degree to radian.

...

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

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

...

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

...

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

...

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

...

Be in before ? ? group in had discussed this issue, the grammar of relative interesting.mysql is really amused. The implementation that they think to use PHP so random, but take out many to seem to want to undertake above inquires twice. Translated manual, found below this statement, can finish the job.
? ?
? ? SELECT * FROM Table_name ORDER BY Rand() LIMIT 5;
? ?
? ? Rand is in manual so say:
? ? RAND()
? ? RAND(N)
? ? is returned in limits 0 be worth to the 1 random floating-point inside.If N of an integral parameter is appointed,It is used as a seed to be worth.
? ? Mysql%26gt;Select RAND();
? ? -%26gt;0.5925
? ? Mysql%26gt;Select RAND(20);
? ? -%26gt;0.1811
? ? Mysql%26gt;Select RAND(20);
? ? -%26gt;0.1811
? ? Mysql%26gt;Select RAND();
? ? -%26gt;0.2079
? ? Mysql%26gt;Select RAND();
? ? -%26gt;0.7888
? ?
? ? you cannot be worth use row with RAND() in clause of an ORDER BY,Because ORDER BY will repeat computation to be listed for many times.It is however in MySQL3.23,You can be done:SELECT * FROM Table_name ORDER BY RAND() ,This is to be helpful for getting one comes from SELECT * FROM Table1, the aggregate of Table2 WHERE A=b AND C%26lt;d ORDER BY RAND() LIMIT 1000 random sample.Notice a RAND() in clause of a WHERE will be in every time the reappraise when WHERE is carried out.
? ?
? ? but I tried, the watch of thousand 8 record, carry out need 0.08 Sec, . some slower
? ?
? ? consulted Google later, get following code
? ? SELECT *
? ? FROM Table_name AS R1 JOIN
? ? (SELECT ROUND(RAND() *
? ? (SELECT MAX(id)
? ? FROM Table_name)) AS Id)
? ? AS R2
? ? WHERE R1.id %26gt;= R2.id
? ? ORDER BY R1.id ASC
? ? LIMIT 5;
? ?
? ? carries out efficiency to need 0.02 Sec. Regrettablly is, have Mysql 4.1 only. * above just supports such child inquiry.

...

? ? lists the memory that the type needs to measure
? ?
Byte of ? ? TINYINT 1
? ?
? ? SMALLINT 2 byte
? ?
? ? MEDIUMINT 3 byte
? ?
? ? INT 4 byte
? ?
? ? INTEGER 4 byte
? ?
? ? BIGINT 8 byte
? ?
? ? FLOAT(X) 4 if X%26lt;= 24 or 8 if 25%26lt;= X%26lt;= 53
? ?
? ? FLOAT 4 byte
? ?
? ? DOUBLE 8 byte
? ?
? ? DOUBLE PRECISION 8 byte
? ?
? ? REAL 8 byte
? ?
? ? DECIMAL(M, d) M byte (D+2, if M%26lt;D)
? ?
? ? NUMERIC(M, d) M byte (D+2, if M%26lt;D)
? ?
? ? date and time kind
? ?
? ? lists the memory that the type needs to measure
? ?
? ? DATE 3 byte
? ?
? ? DATETIME 8 byte
? ?
? ? TIMESTAMP 4 byte
? ?
? ? TIME 3 byte
? ?
Byte of ? ? YEAR 1
? ?
? ? strings together a type
? ?
? ? lists the memory that the type needs to measure
? ?
Byte of ? ? CHAR(M) M,1 %26lt;= M %26lt;= 255
? ?
Byte of ? ? VARCHAR(M) L+1, in this L %26lt;= M and 1 %26lt;= M %26lt;= 255
? ?
? ? TINYBLOB, byte of TINYTEXT L+1, in this L%26lt;2 ^ 8
? ?
? ? BLOB, byte of TEXT L+2, in this L%26lt;2 ^ 16
? ?
? ? MEDIUMBLOB, byte of MEDIUMTEXT L+3, in this L%26lt;2 ^ 24
? ?
? ? LONGBLOB, byte of LONGTEXT L+4, in this L%26lt;2 ^ 32
? ?
? ? ENUM('value1' , 'value2' , . . . ) 1 or 2 byte, depend on the amount that enumerate is worth (maximum 65535)
? ?
? ? SET('value1' , 'value2' , . . . ) 1,2,3,4 or 8 byte, depend on the amount of gather member (most 64 members)

...

The Master/slave data synchronism of ? ? MYSQL is configured
? ?
? ? my test environment. Basically data is instantaneous synchronism,The hope is helpful to everybody
? ?
? ? Redhat 9.0
? ? Mysql3.23.57
? ?
? ?Mysql data synchronism backs up
? ?
Server of ? ? A:192.168.1.2 advocate server Master
? ?
Server of ? ? B:Slave of 192.168.1.3 deputy server
? ?
Server of ? ? A is installed
? ?
P of - of U Root of - of ? ? #mysql
? ? Mysql%26gt;GRANT FILE ON * . * TO Backup@192.168.1.3 IDENTIFIED BY ' 1234 ' ;
? ? Mysql%26gt;\exit
? ?
It is Master opens password of Backup of date of a Zhang above ? ? 1234 give IP:192.168.1.3 have the limits of authority that archives handles
? ?
P Shutdown of - of U Root of - of ? ? Mysqladmin
? ?
? ? backs up Master place know exactly about sth occupies a library. . Use Tar statement normally.
? ?
Cvf /tmp/mysql.tar /var/lib/mysql of - of ? ? #tar
? ?
? ? notices: TAr when, mySQL is to should be below Stop circumstance
? ?
? ? is revised on A machine / Etc/my.cnf
? ?
? ? is in [parameter is joined inside Mysqld] section
? ?
? ? Log-bin
? ? Server-id=1
? ? Sql-bin-update-same
? ? Binlog-do-db=vbb
? ?
? ?Restart A server Mysql
? ?
? ? because have,join Log-bin parameter right now, because this begins to Index arose, be in / the transaction Log that there is database of.index record record below Var/lib/mysql catalog.
? ?
Server of ? ? B is installed
? ?
? ? set / Etc/my.cnf
? ?
? ? is in [Mysqld] section is joined
? ?
? ? Master-host=192.168.1.2
? ? Master-user=backup
? ? Master-password=1234
? ? Master-port=3306
? ? Server-id=2
? ? Master-connect-retry=60 is set beforehand try interval again 60 seconds
What ? ? Replicate-do-db=vbb tells Slave to do Vbb database only is newer
? ? Log-slave-updates
? ?
? ? goes to the Mysql.tar Copy on A on B
? ?
? ? passes B with Ftp / Tmp
? ?
? ? 9. Solution is compressed
? ? #cd /var/lib/
? ?
? ?The state checks
? ?
It is normal that ? ? 1.A follows B network and service below the circumstance, after carrying metabolic data by A, to B end scans data, whether does postmortem have undertake Replication? !
? ?
? ? 2. Imitate B is become machine, or it is B does not need to follow A to have link all the time certainly.
? ?
After ? ? will carry metabolic data by A, to the B end data that browse,B dot should change without data
? ?
? ? #tar Xvf /tmp/mysql-snapshot.tar
R Mysql:m of - of ? ? #chownYsql Mysql
? ?
? ? 1 restart the Mysql of B server
? ?
? ? is in at this moment / Var/lib/mysql catalog can appear Master.info, this record record the information of Master MySQL Server.
? ?
? ?The state checks
? ?
It is normal that ? ? 1.A follows B network and service below the circumstance, after carrying metabolic data by A, to B end scans data, whether does postmortem have undertake Replication? !
? ?
? ? 2. Imitate B is become machine, or it is B does not need to follow A to have link all the time certainly.
? ?
After ? ? will carry metabolic data by A, to the B end data that browse,B dot should change without data

...

? ? environment: SCO OpenServer 5.0.6, mysql 3.23.42, gcc 2.95.2
? ?
? ? remarks: GCc is compositive already the function that supports Mysql, can call a database directly in C source code, need not want to embed with ESQL/C in that way like Informix call.
? ?
File of ? ? 1.makefile
? ?
? ? CC=gcc
? ?
? ? LIB=-lcurses -lsocket -lmysqlclient -lm -lc -lnsl
? ?
? ? All:gSpser
? ?
? ? Gspser:gEt_data.o Getfileargv.o
? ?
? ? $(CC) -o Gj Get_data.o Getfileargv.o $(LIB)
? ?
? ? Get_data.o:gEt_data.c
? ?
? ? $(CC) -c Get_data.c
? ?
? ? Getfileargv.o:gEtfileargv.c
? ?
? ? $(CC) -c Getfileargv.c
? ?
? ? Clean:
? ?
? ? Rm -f *.o
? ?
File of ? ? 2.get_data.c
? ?
? ? #include %26lt;stdio.h%26gt;
? ?
? ? #include %26lt;/usr/local/mysql/include/mysql.h%26gt;
? ?
? ? #define DBCFG " . / Gsp.conf "
? ?
? ? Main()
? ?
? ? {
? ?
? ? Char Dbhost[32] , dbuser[16] , dbpasswd[16] , db[16];
? ?
? ? Char Query[256];
? ?
? ? Int Count;
? ?
? ? MYSQL * Mysql; /* expresses connective of pair of a database sentence handle */
? ?
? ? MYSQL_RES *mysql_res; /* represents the result */ that returns an inquiry of travel
? ?
*/ of array of string of ? ? MYSQL_ROW Mysql_row; /*
? ?
? ? My_ulonglong Rows;
? ?
? ? / * this type is used at travel number and Mysql_affected_rows() , Mysql_num_rows() and Mysql_insert_id()
? ?
? ? */
? ?
? ? Getfileargv(DBCFG, "DBHOST" , dbhost); /* is read from inside the file take Gsp.conf*/
? ?
? ? Getfileargv(DBCFG, "DBUSER" , dbuser);
? ?
? ? Getfileargv(DBCFG, "DBPASSWD" , dbpasswd);
? ?
? ? Getfileargv(DBCFG, "DATABASE" , db);
? ?
? ? If(! (Mysql=mysql_init(NULL))) /* is obtained or initialization */ of structure of a MYSQL
? ?
? ? {
? ?
? ? Rintf("mysql_init fails! ") ;
? ?
? ? Mysql_close(mysql);
? ?
? ? Exit(0);
? ?
? ? }
? ?
? ? If(! Mysql_real_connect(mysql, dbhost, dbuser, dbpasswd, db, 0, NULL, 0) )
? ?
? ? {/* joins server */ of a MySQL
? ?
? ? Rintf(" joins the server fails, contact systematic administrator please! ") ;
? ?
? ? Mysql_close(mysql);
? ?
? ? Exit(0);
? ?
? ? }
? ?
? ? Trcpy(query, "Select * From Zuh Where Jgm=\"11110501\" Order By Zuh");
? ?
? ? If(mysql_query(mysql, query)) /* carries out the SQL that appoints the string that is an empty ending to inquire */
? ?
? ? {
? ?
? ? Rintf("mysql_query makes mistake! ") ;
? ?
? ? Mysql_close(mysql);
? ?
? ? Exit(0);
? ?
? ? }
? ?
? ? Mysql_res=mysql_store_result(mysql); /* retrieves a whole result rally to give a client */
? ?
? ? Rows=mysql_num_rows(mysql_res); /* returns the quantitative */ of the row with heavy gather of a result
? ?
? ? If(rows==Code of 0) /* this government is nonexistent return */
? ?
? ? {
? ?
Return of value of ? ? Rintf(" is empty ");
? ?
? ? Mysql_free_result(mysql_res); /* releases the memory */ that gather of a result uses
? ?
? ? Mysql_close(mysql); /* shuts */ of join of a server
? ?
? ? Exit(0);
? ?
? ? }
? ?
? ? For(count=0; Count%26lt;rows; Count++ )
? ?
? ? {
? ?
? ? Mysql_row=mysql_fetch_row(mysql_res); /* acquires next group from inside assembling as a result */
? ?
Name of company of ? ? Rintf(" : %S " , mysql_row[0]);
? ?
Number of set of ? ? Rintf(" : %S " , mysql_row[1]);
? ?
Name of team of ? ? Rintf(" : %S\n" , mysql_row[2]);
? ?
? ? }
? ?
? ? Mysql_free_result(mysql_res);
? ?
? ? Mysql_close(mysql);
? ?
? ? }
? ?
File of ? ? 3.getfileargv.c
? ?
? ? #include %26lt;stdio.h%26gt;
? ?
? ? Int Getfileargv(const Char * Filename, const Char * Argvname, char * Retbuf)
? ?
? ? {
? ?
? ? FILE *fp;
? ?
? ? Char Line[128];
? ?
? ? Char Tempbuf[40];
? ?
? ? Trncpy(tempbuf, argvname, 30);
? ?
? ? Tempbuf[39]=0;
? ?
? ? If ((fp=fopen(filename, "R") )==NULL)
? ?
? ? Return -1;
? ?
? ? Trcat(tempbuf, "=");
? ?
? ? While(fgets(line, 120, fp) )
? ?
? ? {
? ?
? ? If(line[0]=='#' )
? ?
? ? Continue;
? ?
? ? If (! Strncmp(line, tempbuf, strlen(tempbuf)) )
? ?
? ? {
? ?
? ? Fclose(fp);
? ?
? ? Line[strlen(line)-1]=0;
? ?
? ? Trcpy(line, (Char *)(strchr(line, '=')+1));
? ?
? ? Trcpy(retbuf, line);
? ?
? ? Return 0;
? ?
? ? }
? ?
? ? }
? ?
? ? Fclose(fp);
? ?
? ? Return -2;
? ?
? ? }

...

? ? hardware is configured
? ?
Environment of the smallest group needs ? ? common PC Server * 2 (4 servers)
? ?
Environment of ? ? imitate
? ?
) of version of above of ? ? Red Hat Linux9 For X86 (or Red Hat AS 2, glibc-2.2, static, gcc
? ?
4.1.12 Binares of version of ? ? MySQL
? ?
Version of ? ? Mysql-max Binary supports Linux, Max Os X and Solaris only at present
? ?
This ? ? plan is not involved compile installation from source code
? ?
Utility of address of IP of ? ? lead plane
? ?
? ? Ndb1_mgmd_sqld 1192.168.1.100 Ndb Node1+mgmd Node1+sqld Node1
? ? Ndb2_sqld2 192.168.1.200 Ndb Node2+sqld Node2
? ?
? ? Mgmd:mAnagement Server
? ? Sqld:mYsql Server
? ? Ndb:sToraged Node (share-nothing, base In Memory)
? ?
? ? is installed
? ?
? ? arrives from Http://dev.mysql.com/downloads/mysql/4.1.html download Mysql-max-4.1.12-pc-linux-gnu-i686.tar.gz / Var/tmp
? ? Storage And SQL Node Installation
? ?
? ? carries out following processes on two lead plane
? ?
? ? Shell%26gt;groupadd Mysql
? ? Shell%26gt;useradd -g Mysql Mysql
? ? Shell%26gt;tar Zxfv Mysql-max-4.1.12-pc-linux-gnu-i686.tar.gz
? ? Shell%26gt;cp -vr Mysql-max-4.1.12-pc-linux-gnu-i686 /usr/local/mysql-max-4.1.12-pc-linux-gnu-i686
? ? Shell%26gt;cd /usr/local
? ? Shell%26gt;ln -s Mysql-max-4.1.12-pc-linux-gnu-i686 Mysql
User=mysql of - of ? ? Shell%26gt;cd Mysql;scripts/mysql_install_db
? ? Shell%26gt;chown -R Root. ;chown -R Mysql Data;chgrp -R Mysql.
? ? Shell%26gt;cp Support-files/mysql.server /etc/rc.d/init.d/
? ? Shell%26gt;chmod +x /etc/rc.d/init.d/mysql.server
? ? Shell%26gt;chkconfig- - Add Mysql.server
Level 3 Mysql.server Off of - of ? ? Shell%26gt;chkconfig
? ? Management Node Installation
? ?
? ? carries out following processes on lead plane Ndb1_mgmd_sqld1
? ?
? ? Shell%26gt;cd /var/tmp
? ? Shell%26gt;tar -zxvf Mysql-max-4.1.12a-pc-linux-gnu-i686.tar.gz /usr/local/bin '*/bin/ndb_mgm*'
? ? Configuration
? ? Configuring The Storage And SQL Nodes
? ?
? ? carries out following processes on two lead plane:
? ?
? ? Shell%26gt;vi /etc/my.cnf
? ? [# Options For Mysqld Process of ? of ? of ? of ? of ? of ? of ? of ? of ? of ? of MYSQLD] ? ? :
# Run NDB Engine of ? of ? of ? of ? of ? of ? of ? of ? of ? of ? of ? ? Ndbcluster
# Location Of MGM Node of ? of ? ? Ndb-connectstring=192.168.1.100
? ?
? ? [# Options For Ndbd Process of ? of ? of ? of ? of ? of ? of MYSQL_CLUSTER] ? ? :
# Location Of MGM Node of ? of ? ? Ndb-connectstring=192.168.1.100
? ?
? ? Configuring The Management Node
? ?
? ? carries out following processes on lead plane Ndb1_mgmd_sqld1
? ?
? ? Shell%26gt;mkdir /var/lib/mysql-cluster
? ? Shell%26gt;cd /var/lib/mysql-cluster
? ? Shell%26gt;vi Config.ini
? ? [# Options Affecting Ndbd Processes On All Data Nodes of ? of NDBD DEFAULT] ? :
# Number Of Replicas of ? of ? of ? ? NoOfReplicas=2
# How Much Memory To Allocate For Data Storage of ? of ? of ? ? DataMemory=80M
# How Much Memory To Allocate For Index Storage of ? of ? ? IndexMemory=52M
? ? # For DataMemory And IndexMemory, we Have Used The
? ? # Default Values. Since The "world" Database Takes Up
? ? # Only About 500KB, this Should Be More Than Enough For
? ? # This Example Cluster Setup.
? ? [TCP DEFAULT]
? ?
? ? [# Management Process Options of ? of ? of ? of ? of ? of NDB_MGMD] ? ? :
? ? Hostname=192.168.1.100 # Hostname Or IP Address Of MGM Node
# Directory For MGM Node Logfiles of ? of ? ? Datadir=/var/lib/mysql-cluster
? ?
? ? [# Options For Data Node "A" of ? of ? of ? of ? of ? of ? of ? of ? of ? of ? of NDBD] ? ? :
? ? # (one [NDBD] Section Per Data Node)
# Hostname Or IP Address of ? of ? of ? of ? of ? of ? ? HostName=192.168.1.100
# Directory For This Data Node's Datafiles of ? of ? ? DataDir=/usr/local/mysql/data
? ?
? ? [# Options For Data Node "B" of ? of ? of ? of ? of ? of ? of ? of ? of ? of ? of NDBD] ? ? :
# Hostname Or IP Address of ? of ? of ? of ? of ? of ? ? Hostname=192.168.1.200
# Directory For This Data Node's Datafiles of ? of ? ? Datadir=/usr/local/mysql/data
? ?
? ? [# SQL Node Options of ? of ? of ? of ? of ? of ? of ? of ? of ? of ? of MYSQLD] ? ? :
# Hostname Or IP Address of ? of ? of ? of ? of ? ? Hostname=192.168.1.100
? ? # Directory For SQL Node's Datafiles
? ? # (additional Mysqld Connections Can Be
? ? # Specified For This Node For Various
? ? # Purposes Such As Running Ndb_restore)
? ?
? ? [MYSQLD] # SQL Node Options:
# Hostname Or IP Address of ? of ? of ? of ? of ? ? Hostname=192.168.1.200
? ? # Directory For SQL Node's Datafiles
? ? # (additional Mysqld Connections Can Be
? ? # Specified For This Node For Various
? ? # Purposes Such As Running Ndb_restore)
? ?
? ? first time is started
? ?
? ? carries out following processes on lead plane Ndb1_mgmd_sqld1
? ?
? ? Shell%26gt;Ndb_mgmd -f /var/lib/mysql-cluster/config.ini
? ?
? ? carries out following processes on two lead plane
? ?
Initial (note:- of - of ? ? Shell%26gt;ndbd- Initial option can be used when be started for the first time only)
? ? Shell%26gt;/etc/init.d/mysql.server Start
? ?
? ? checks
? ?
? ? carries out following processes on lead plane Ndb1_mgmd_sqld1
? ? Shell%26gt;Ndb_mgm
? ?- - NDB Cluster- - Management Client- -
? ? Ndb_mgm%26gt;Show
? ? Connected To Management Server At: Localhost:1186
? ? Cluster Configuration
? ?---------------------
? ? [Ndbd(NDB)] ? ? 2 Node(s)
? of @192.168.0.100 of ? of ? of ? ? Id=2 (Version: 4.1.12, nodegroup: 0, master)
? of @192.168.0.200 of ? of ? of ? ? Id=3 (Version: 4.1.12, nodegroup: 0)
? ?
? ? [Ndb_mgmd(MGM)] 1 Node(s)
? of @192.168.0.100 of ? of ? of ? ? Id=1 (Version: 4.1.12)
? ?
? ? [Mysqld(SQL)] ? 1 Node(s)
? of ? ? Id=4 (Version: 4.1.12)
? ?
Information of ? ? occurrence as above states Mysql teeming installation is successful
? ?
Sampling test of ? ? data
? ?
? ? carries out following processes on lead plane Ndb1_mgmd_sqld1
? ?
? ? Shell%26gt;/usr/local/mysql/bin/mysql -u Root Test
? ? MySQL%26gt;DROP TABLE IF EXISTS City;
? ? CREATE TABLE City (
? ? ID Int(11) NOT NULL Auto_increment,
? ? Name Char(35) NOT NULL Default '' ,
? ? CountryCode Char(3) NOT NULL Default '' ,
? ? District Char(20) NOT NULL Default '' ,
? ? Population Int(11) NOT NULL Default '0' ,
? of ? ? PRIMARY KEY (ID)
? ? ) ENGINE=NDBCLUSTER;
? ?
? ? MySQL%26gt;INSERT INTO City VALUES (1, 'Kabul' , 'AFG' , 'Kabol' , 1780000);
? ? INSERT INTO City VALUES (2, 'Qandahar' , 'AFG' , 'Qandahar' , 237500);
? ? INSERT INTO City VALUES (3, 'Herat' , 'AFG' , 'Herat' , 186800);
? ?
? ? carries out following processes on lead plane Ndb2_sqld2
? ?
? ? Shell%26gt;/usr/local/mysql/bin/mysql -u Root Mysql
? ? MySQSL%26gt;select * From City;
? ?
If ? ? is successful indication data information,State group is started already successfully
? ?
? ? Safe Shutdown And Restart
? ?
? ? carries out following processes on lead plane Ndb1_mgmd_sqld1
? ?
? ? Shell%26gt;ndb_mgm -e Shutdown (shut group server,Storage Node also is met be shut automatically)
? ?
? ? carries out following processes on two lead plane
? ?
? ? Shell%26gt;/etc/init.d/mysql.server Stop
? ?
? ? starts group afresh (cannot make a mistake orderly)
? ?
? ? carries out following processes on lead plane Ndb1_mgmd_sqld1
? ?
? ? Shell%26gt;Ndb_mgmd -f /var/lib/mysql-cluster/config.ini
? ?
? ? carries out following processes on two lead plane
? ?
? ? Shell%26gt;/usr/local/mysql/bin/ndbd
? ?
Sqld process is started after ? ? starts Ndbd process
? ?
? ? Shell%26gt;/etc/init.d/mysql.server Start
? ?
? ? is added:
? ?
Departmental cent explains in ? ? Config.ini
? ?
? ? [COMPUTER] : Define teeming lead plane.
? ?
? ? [NDBD] : Define teeming data node.
? ?
? ? [MYSQLD] : Define Sql Server node.
? ?
? ? [MGM | NDB_MGMD] : Defines The Management Server Node In The Cluster.
? ? [TCP] : Defines TCP/IP Connections Between Nodes In The Cluster, with TCP/IP Being The Default Connection Protocol.
? ? [SHM] : Defines Shared-memory Connections Between Nodes. Before MySQL 4.1.9,This function must be used- - With-ndb-shm Option is compiled go in, begin from MySQL 4.1.9-max version, it Is Enabled By Default

...