mysql安装与主从复制说明文档.docx

mysql安装与主从复制说明文档.docx

《mysql安装与主从复制说明文档.docx》由会员分享，可在线阅读，更多相关《mysql安装与主从复制说明文档.docx（35页珍藏版）》请在冰豆网上搜索。

mysql安装与主从复制说明文档

一Linux下安装mysql数据库

1．安装netsarangxmanager.exe，

使用Xshell连接服务器，Xftp拷贝文件。

2．拷贝安装文件

Xftp把f文件拷贝到/etc 目录下，mysqlrpm拷贝到/root目录下。

（附录：

f文件内容：

datadir=/app/data为数据库文件目录设定）

#BEGINCONFIGINFO

#DESCR:

4GBRAM,InnoDBonly,ACID,fewconnections,heavyqueries

#TYPE:

SYSTEM

#ENDCONFIGINFO

#

#ThisisaMySQLexampleconfigfileforsystemswith4GBofmemory

#runningmostlyMySQLusingInnoDBonlytablesandperformingcomplex

#querieswithfewconnections.

#

#MySQLprogramslookforoptionfilesinasetof

#locationswhichdependonthedeploymentplatform.

#Youcancopythisoptionfiletooneofthose

#locations.Forinformationabouttheselocations,see:

#

#

#Inthisfile,youcanusealllongoptionsthataprogramsupports.

#Ifyouwanttoknowwhichoptionsaprogramsupports,runtheprogram

#withthe"--help"option.

#

#Moredetailedinformationabouttheindividualoptionscanalsobe

#foundinthemanual.

#

#

#ThefollowingoptionswillbereadbyMySQLclientapplications.

#NotethatonlyclientapplicationsshippedbyMySQLareguaranteed

#toreadthissection.IfyouwantyourownMySQLclientprogramto

#honorthesevalues,youneedtospecifyitasanoptionduringthe

#MySQLclientlibraryinitialization.

#

[client]

#password=[your_password]

port=3306

socket=/var/lib/mysql/mysql.sock

#default-character-set=gbk

#***Application-specificoptionsfollowhere***

#

#TheMySQLserver

#

[mysqld]

#skip-grant-tables

#genericconfigurationoptions

port=3306

socket=/var/lib/mysql/mysql.sock

datadir=/app/data

#back_logisthenumberofconnectionstheoperatingsystemcankeepin

lower_case_table_name=1

sql_mode=oracle

innodb_file_per_table=1

character_set_server=utf8

skip-host-cache

skip-name-resolve

#thelistenqueue,beforetheMySQLconnectionmanagerthreadhas

#processedthem.Ifyouhaveaveryhighconnectionrateandexperience

#"connectionrefused"errors,youmightneedtoincreasethisvalue.

#CheckyourOSdocumentationforthemaximumvalueofthisparameter.

#Attemptingtosetback_loghigherthanyouroperatingsystemlimit

#willhavenoeffect.

back_log=50

#skip-grant-tables

#Don'tlistenonaTCP/IPportatall.Thiscanbeasecurity

#enhancement,ifallprocessesthatneedtoconnecttomysqldrun

#onthesamehost.AllinteractionwithmysqldmustbemadeviaUnix

#socketsornamedpipes.

#NotethatusingthisoptionwithoutenablingnamedpipesonWindows

#（viathe"enable-named-pipe"option）willrendermysqlduseless!

#skip-networking

#ThemaximumamountofconcurrentsessionstheMySQLserverwill

#allow.Oneoftheseconnectionswillbereservedforauserwith

#SUPERprivilegestoallowtheadministratortologinevenifthe

#connectionlimithasbeenreached.

max_connections=32000

slow-query-log

#Maximumamountoferrorsallowedperhost.Ifthislimitisreached,

#thehostwillbeblockedfromconnectingtotheMySQLserveruntil

#"FLUSHHOSTS"hasbeenrunortheserverwasrestarted.Invalid

#passwordsandothererrorsduringtheconnectphaseresultin

#increasingthisvalue.Seethe"Aborted_connects"statusvariablefor

#globalcounter.

max_connect_errors=10

#Thenumberofopentablesforallthreads.Increasingthisvalue

#increasesthenumberoffiledescriptorsthatmysqldrequires.

#Thereforeyouhavetomakesuretosettheamountofopenfiles

#allowedtoatleast4096inthevariable"open-files-limit"in

#section[mysqld_safe]

table_open_cache=4096

#Enableexternalfilelevellocking.Enabledfilelockingwillhavea

#negativeimpactonperformance,soonlyuseitincaseyouhave

#multipledatabaseinstancesrunningonthesamefiles（notesome

#restrictionsstillapply!

）orifyouuseothersoftwarerelyingon

#lockingMyISAMtablesonfilelevel.

#external-locking

#Themaximumsizeofaquerypackettheservercanhandleaswellas

#maximumquerysizeservercanprocess（Importantwhenworkingwith

#largeBLOBs）.enlargeddynamically,foreachconnection.

max_allowed_packet=16M

#ThesizeofthecachetoholdtheSQLstatementsforthebinarylog

#duringatransaction.Ifyouoftenusebig,multi-statement

#transactionsyoucanincreasethisvaluetogetmoreperformance.All

#statementsfromtransactionsarebufferedinthebinarylogcacheand

#arebeingwrittentothebinarylogatonceaftertheCOMMIT.Ifthe

#transactionislargerthanthisvalue,temporaryfileondiskisused

#instead.Thisbufferisallocatedperconnectiononfirstupdate

#statementintransaction

binlog_cache_size=16M#modifiedbyliugh@2012.03.2414:

30

#binlog_cache_size=1M

#MaximumallowedsizeforasingleHEAP（inmemory）table.Thisoption

#isaprotectionagainsttheaccidentialcreationofaverylargeHEAP

#tablewhichcouldotherwiseuseupallmemoryresources.

max_heap_table_size=512M

#Sizeofthebufferusedfordoingfulltablescans.

#Allocatedperthread,ifafullscanisneeded.

read_buffer_size=2M

#Whenreadingrowsinsortedorderafterasort,therowsareread

#throughthisbuffertoavoiddiskseeks.YoucanimproveORDERBY

#performancealot,ifsetthistoahighvalue.

#Allocatedperthread,whenneeded.

read_rnd_buffer_size=16M

#SortbufferisusedtoperformsortsforsomeORDERBYandGROUPBY

#queries.Ifsorteddatadoesnotfitintothesortbuffer,adisk

#basedmergesortisusedinstead-Seethe"Sort_merge_passes"

#statusvariable.Allocatedperthreadifsortisneeded.

sort_buffer_size=1024M

#ThisbufferisusedfortheoptimizationoffullJOINs（JOINswithout

#indexes）.SuchJOINsareverybadforperformanceinmostcases

#anyway,butsettingthisvariabletoalargevaluereducesthe

#performanceimpact.Seethe"Select_full_join"statusvariablefora

#countoffullJOINs.Allocatedperthreadiffulljoinisfound

join_buffer_size=256M

#Howmanythreadsweshouldkeepinacacheforreuse.Whenaclient

#disconnects,theclient'sthreadsareputinthecacheiftherearen't

#morethanthread_cache_sizethreadsfrombefore.Thisgreatlyreduces

#theamountofthreadcreationsneededifyouhavealotofnew

#connections.（Normallythisdoesn'tgiveanotableperformance

#improvementifyouhaveagoodthreadimplementation.）

thread_cache_size=1024

#Thispermitstheapplicationtogivethethreadssystemahintforthe

#desirednumberofthreadsthatshouldberunatthesametime.This

#valueonlymakessenseonsystemsthatsupportthethread_concurrency（）

#functioncall（SunSolaris,forexample）.

#Youshouldtry[numberofCPUs]*（2..4）forthread_concurrency

thread_concurrency=48

#QuerycacheisusedtocacheSELECTresultsandlaterreturnthem

#withoutactualexecutingthesamequeryonceagain.Havingthequery

#cacheenabledmayresultinsignificantspeedimprovements,ifyour

#havealotofidenticalqueriesandrarelychangingtables.Seethe

#"Qcache_lowmem_prunes"statusvariabletocheckifthecurrentvalue

#ishighenoughforyourload.

#Note:

Incaseyourtableschangeveryoftenorifyourqueriesare

#textuallydifferenteverytime,thequerycachemayresultina

#slowdowninsteadofaperformanceimprovement.

query_cache_size=1024M

#Onlycacheresultsetsthataresmallerthanthislimit.Thisisto

#protectthequerycacheofaverylargeresultsetoverwritingall

#otherqueryresults.

query_cache_limit=2M

#Minimumwordlengthtobeindexedbythefulltextsearchindex.

#Youmightwishtodecreaseitifyouneedtosearchforshorterwords.

#NotethatyouneedtorebuildyourFULLTEXTindex,afteryouhave

#modifiedthisvalue.

ft_min_word_len=4

#Ifyoursystemsupportsthememlock（）functioncall,youmightwantto

#enablethisoptionwhilerunningMySQLtokeepitlockedinmemoryand

#toavoidpotentialswappingoutincaseofhighmemorypressure.Good

#forperformance.

#memlock

#Tabletypewhichisusedbydefaultwhencreatingnewtables,ifnot

#specifieddifferentlyduringtheCREATETABLEstatement.

#default-storage-engine=InnoDB

#Threadstacksizetouse.Thisamountofmemoryisalwaysreservedat

#connectiontime.MySQLitselfusuallyneedsnomorethan64Kof

#memory,whileifyouuseyourownstackhungryUDFfunctionsoryour

#OSrequiresmorestackforsomeoperations,youmightneedtosetthis

#toahighervalue.

thread_stack=192K

#Setthedefaulttransactionisolationlevel.Levelsavailableare:

#READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE

transaction_isolation=REPEATABLE-READ

#transaction_isolation=READ-COMMITTED

#Maximumsizeforinternal（in-memory）temporarytables.Ifatable

#growslargerthanthisvalue,itisautomaticallyconvertedtodisk

#basedtableThislimitationisforasingletable.Therecanbemany

#ofthem.

tmp_table_size=512M

#Enablebinarylogging.ThisisrequiredforactingasaMASTERina

#replicationconfiguration.Youalsoneedthebinarylogifyouneed

#theabilitytodopointintimerecoveryfromyourlatestbackup.

log-bin=mysql-bin

#binaryloggingformat-mixedrecommended

binlog_format=mixed

#Ifyou'reusingreplicationwithchainedslaves（A->B->C）,youneedto

#enablethisoptiononserverB.Itenablesloggingofupdatesdoneby

#theslavethreadintotheslave'sbinarylog.

#log_slave_updates

#Enablethefullquerylog.Everyquery（evenoneswithincorrect

#syntax）thattheserverreceiveswillbelogged.Thisisusefulfor

#debugging,itisusuallydisabledinproductionuse.

#log

#Printwarningstotheerrorlogfile.Ifyouhaveanyproblemwith

#MySQLyoushould