Linux下MySQL数据库服务器架设笔录演示教学.doc

Good is good, but better carries it.精益求精，善益求善。Linux下MySQL数据库服务器架设笔录-Linux下MySQL数据库服务器架设笔录1、 本文环境：CentOS+MySQLMySQL是目前最流行的开放源代码的关系数据库管理系统，是目前少有的开放源代码的数据库软件之一。开放源代码也就意味着任何人都能够使用和改变MySQL软件，任何人都能下载和使用此软件而无需支付任何费用。基于其快速、可靠、易用和免费等特点，MySQL数据库系统在Web应用以及其他各行各业中得到了非常广泛的应用。MySQL数据库软件的安装和运行CentOS5操作系统安装完成之后一般情况下没有默认安装MySQL数据库，可进入安装光盘的软件目录，找到如下几个文件：perl-DBI-1.52-2.el5.i386#Perl语言的数据APImysql-5.0.77-3.el5.i386#MySQL数据库客户端程序perl-DBD-MySQL-3.0007-2.el5.i386#MySQL与Perl语言的接口程序包unixODBC-2.2.11-7.1.i386.rpm#Linux/Unix平台的ODBC程序mysql-connector-odbc-3.51.26r1127-1.el5.i386#MySQL数据库与ODBC的连接器mysql-server-5.0.77-3.el5.i386#MySQL数据库服务器程序将上述文件拷贝到服务器合适位置，依次执行安装：rootwww1MySQL_Install#rpm-ivhperl-DBI-1.52-2.el5.i386.rpmPreparing.#100%1:perl-DBI#100%rootwww1MySQL_Install#rpm-ivhmysql-5.0.77-3.el5.i386.rpmPreparing.#100%1:mysql#100%rootwww1MySQL_Install#rpm-ivhperl-DBD-MySQL-3.0007-2.el5.i386.rpmPreparing.#100%1:perl-DBD-MySQL#100%rootwww1MySQL_Install#rpm-ivhunixODBC-2.2.11-7.1.i386.rpmPreparing.#100%1:unixODBC#100%rootwww1MySQL_Install#rpm-ivhmysql-connector-odbc-3.51.26r1127-1.el5.i386.rpmPreparing.#100%1:mysql-connector-odbc#100%rootwww1MySQL_Install#rpm-ivhmysql-server-5.0.77-3.el5.i386.rpmPreparing.#100%1:mysql-server#100%也可以在联网状态下使用在线包管理工具yum进行安装：rootwww1MySQL_Install#yum-yinstallmysql安装完MySQL之后可以输入以下命令启动数据库：rootwww1MySQL_Install#/etc/init.d/mysqldstart查看启动状态：rootwww1MySQL_Install#ps-eaf|grepmysqldroot28421009:43pts/100:00:00/bin/sh/usr/bin/mysqld_safe-datadir=/var/lib/mysql-socket=/var/lib/mysql/mysql.sock-log-error=/var/log/mysqld.log-pid-file=/var/run/mysqld/mysqld.pid-user=mysqlmysql28922842009:43pts/100:00:00/usr/libexec/mysqld-basedir=/usr-datadir=/var/lib/mysql-user=mysql-pid-file=/var/run/mysqld/mysqld.pid-skip-external-locking-socket=/var/lib/mysql/mysql.sockroot29112576009:44pts/100:00:00grepmysqld可以看到服务器启动了2个进程，其中/usr/bin/mysqld_safe是一个脚本程序，由root用户运行，它的作用是启动数据库服务进程mysqld，并一直监控其运行状态，如果发现mysqld进程死了，就会重新启动它。而数据库服务进程mysqld则有mysql用户运行，用以提供数据库服务。默认情况下mysql的监听端口是3306，可以查看一下端口状态：rootwww1MySQL_Install#netstat-anlp|grep:3306tcp000.0.0.0:33060.0.0.0:*LISTEN2892/mysqld还可使用以下命令查看MySQL的运行状态：rootwww1MySQL_Install#mysqladminversionmysqladminVer8.41Distrib5.0.77,forredhat-linux-gnuoni686Copyright(C)2000-2006MySQLABThissoftwarecomeswithABSOLUTELYNOWARRANTY.Thisisfreesoftware,andyouarewelcometomodifyandredistributeitundertheGPLlicenseServerversion5.0.77Protocolversion10ConnectionLocalhostviaUNIXsocketUNIXsocket/var/lib/mysql/mysql.sock2、 Uptime:7min1secThreads:1Questions:2Slowqueries:0Opens:12Flushtables:1Opentables:6Queriespersecondavg:0.005使用命令行客户端登录MySQL数据库最常用的MySQL客户端是mysql命令，其格式如下：Mysql-h<主机名>-u<用户名>-p数据库名其中：-h<主机名>表示要登录的MySQL数据库所在的主机，默认为127.0.0.1-u<用户名>表示使用指定的用户名登录，默认为root用户(注：此root用户不等同于操作系统的root用户)-p表示登录时需要输入密码数据库名表示登录后要使用哪一个数据库。下面为客户端登录的演示：rootwww1MySQL_Install#mysql#连接本地MySQL数据库WelcometotheMySQLmonitor.Commandsendwith;org.YourMySQLconnectionidis3Serverversion:5.0.77SourcedistributionType'help;'or'h'forhelp.Type'c'toclearthebuffer.mysql>usemysql#使用mysql数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechanged#修改root用户的密码mysql>updateusersetpassword=password('123456')whereuser='root'QueryOK,3rowsaffected(0.00sec)Rowsmatched:3Changed:3Warnings:0#在用户表新增一条user记录，即增加一个用户usermysql>insertintouser(host,user,password)values("%","user",password("123456");QueryOK,1rowaffected,3warnings(0.00sec)mysql>flushprivileges;#刷新MySQL的系统权限相关表QueryOK,0rowsaffected(0.01sec)MySQL服务器安装完成后，会首先创建一个名为mysql的数据库，里面包含了MySQL数据库系统的所有系统信息。以上操作中，user表即为用户存放表，增加一条新记录即增加一个新用户，改变password字段的值即为修改密码。每一个用户记录都有一个host字段，表示允许该用户从哪一台主机登录，“%”表示可以从所有客户机进行远程登录，但不能从本机登录。localhost/127.0.0.1表示只能从本机登录。或者可以指定主机名实现从特定主机登录。如需指定可从多台主机登录，则可增加多条记录到user表，例如root用户的记录：mysql>selectt.host,t.user,t.passwordfromusertwheret.user='root'+-+-+-+|host|user|password|+-+-+-+|localhost|root|565491d704013245|root|565491d704013245|127.0.0.1|root|565491d704013245|+-+-+-+3、 3rowsinset(0.00sec)如上结果显示root用户可从localhost、和127.0.0.1登录，其实指的都是本机。图形界面的MySQL客户端MySQL的客户端有许多种，其中有许多优秀的图形界面客户端，可以大大的方便对数据库的操作和管理，这里使用官方版本的图形管理工具MySQLQueryBrowser。MySQLQueryBrowser是MySQL官方推荐的图形管理工具，支持各种平台，可从网站由于刚才的配置，root用户是不能从客户端远程登录的，为了管理方面，在本机的客户端中修改root用户可从客户端远程登录：mysql>updateusertsett.host=%wheret.host=andt.user=root;QueryOK,3rowsaffected(0.00sec)Rowsmatched:3Changed:3Warnings:0修改后重启数据库：rootwww1dx#/etc/init.d/mysqldrestart停止MySQL：确定启动MySQL：确定此时点击上图中的OK按钮即可连接进入数据库，其余操作略。4、 除此之外，通过选择Tools|MySQLAdministrator或从开始菜单可以打开MySQL的管理工具MySQLAdministrator，该工具主要是对MySQL进行用户管理，性能监控，日志管理，数据库备份与回复以及数据库同步等操作，是一个非常实用的工具。MySQL服务器的配置与连接MySQL服务器安装完之后，其默认的配置已经可以使其正常运行，但初始的配置不一定能让服务器运行在最佳的或最合适的状态，此时我们就要对服务器进行配置，以使其达到最佳配置状态。MySQL服务器默认的配置文件是/etc目录下的my.conf文件，这个文件在服务器安装时就会创建，但里面的配置指令很少，只用于初步学习时使用。与这个文件同时被创建的还有另外5个例子配置文件，存放于/usr/share/doc/mysql-server-5.0.77目录下。这5个配置文件的名称和配置目标如下：my-f：为运行在小内存主机(<=64M)上的数据库而设计的，常见情况是MySQL进程占用资源少，数据库只是偶尔被使用。my-f：是为在运行中占用小内存的情况(占用32M-64M)而设计的，其常见情况通常是MySQL服务器与其他应用服务器一起存在于一台主机上，并且此时的MySQL起到较大的作用，使用的较为频繁。通常要求主机拥有128M内存以上。my-f：为一般数据库而设计，内存使用一般会达到512M或以上，通常要求主机拥有超过1GB的内存，适用于大多数的情况。my-f：为企业应用的MySQL数据库而设计，占用超过1GB的系统内存，同时对主机性能有更高要求。my-innodb-heavy-4G.cnf：为使用InnoDB引擎的MySQL数据库而设计的，应当运行在拥有超过4GB内存的专用主机上，通常的使用情况是数据库连接比较多，且查询非常复杂。以上配置文件中的指令种类相差不大，主要区别在于参数的设置不同。依现在的硬件水平，通常配置的内存都达到数个GB以上，通常情况下适用于my-f文件的配置，因此我们看下这个文件的内容：dxwww1mysql-server-5.0.77$catmy-f#ExampleMySQLconfigfileforlargesystems.#Thisisforalargesystemwithmemory=512Mwherethesystemrunsmainly#MySQL.#Youcancopythisfileto#/etc/ftosetglobaloptions,#mysql-data-dir/ftosetserver-specificoptions(inthis#installationthisdirectoryis/var/lib/mysql)or#/ftosetuser-specificoptions.#Inthisfile,youcanusealllongoptionsthataprogramsupports.#Ifyouwanttoknowwhichoptionsaprogramsupports,runtheprogram#withthe"-help"option.#ThefollowingoptionswillbepassedtoallMySQLclientsclient#客户端相关配置#password=your_password#提供默认的用户密码port=3306#客户端连接服务器时，使用默认的3306端口#为MySQL客户端指定一个与服务器通信的本地套接字文件socket=/var/lib/mysql/mysql.sock#Herefollowsentriesforsomespecificprograms#TheMySQLservermysqld#服务器相关配置port=3306#服务器监听端口号socket=/var/lib/mysql/mysql.sock#为MySQL服务器指定一个与客户端通信的本地套接字文件skip-locking#避免外部数据锁key_buffer=256M#设置存放索引区块的内存值max_allowed_packet=1M#设定系统最大缓冲区大小table_cache=256#设定表数据高速缓存的大小sort_buffer_size=1M#设定排序高速缓存的大小read_buffer_size=1M#设定读操作(顺序读取)缓存的大小read_rnd_buffer_size=4M#设定读操作(特定次序读取)缓存的大小myisam_sort_buffer_size=64M#创建或修复表时，分配给ISAM索引排序的缓冲区大小。thread_cache_size=8#分配给线程的缓冲区大小query_cache_size=16M#提交查询操作时缓冲区大小#TrynumberofCPU's*2forthread_concurrencythread_concurrency=8#多个CPU的线程数#Don'tlistenonaTCP/IPportatall.Thiscanbeasecurityenhancement,#ifallprocessesthatneedtoconnecttomysqldrunonthesamehost.#AllinteractionwithmysqldmustbemadeviaUnixsocketsornamedpipes.#NotethatusingthisoptionwithoutenablingnamedpipesonWindows#(viathe"enable-named-pipe"option)willrendermysqlduseless!#skip-networking#DisableFederatedbydefaultskip-federated#ReplicationMasterServer(default)#binaryloggingisrequiredforreplicationlog-bin=mysql-bin#requireduniqueidbetween1and232-1#defaultsto1ifmaster-hostisnotset#butwillnotfunctionasamasterifomittedserver-id=1#设置服务器的ID号，在设置主/从数据库时需要#ReplicationSlave(commentoutmastersectiontousethis)#Toconfigurethishostasareplicationslave,youcanchoosebetween#twomethods:#1)UsetheCHANGEMASTERTOcommand(fullydescribedinourmanual)-#thesyntaxis:#CHANGEMASTERTOMASTER_HOST=<host>,MASTER_PORT=<port>,#MASTER_USER=<user>,MASTER_PASSWORD=<password>#whereyoureplace<host>,<user>,<password>byquotedstringsand#<port>bythemaster'sportnumber(3306bydefault).#Example:#CHANGEMASTERTOMASTER_HOST='125.564.12.1',MASTER_PORT=3306,#MASTER_USER='joe',MASTER_PASSWORD='secret'#OR#2)Setthevariablesbelow.However,incaseyouchoosethismethod,then#startreplicationforthefirsttime(evenunsuccessfully,forexample#ifyoumistypedthepasswordinmaster-passwordandtheslavefailsto#connect),theslavewillcreateamaster.infofile,andanylater#changeinthisfiletothevariables'valuesbelowwillbeignoredand#overriddenbythecontentofthemaster.infofile,unlessyoushutdown#theslaveserver,deletemaster.infoandrestarttheslaverserver.#Forthatreason,youmaywanttoleavethelinesbelowuntouched#(commented)andinsteaduseCHANGEMASTERTO(seeabove)#requireduniqueidbetween2and232-1#(anddifferentfromthemaster)#defaultsto2ifmaster-hostisset#butwillnotfunctionasaslaveifomitted#server-id=2#Thereplicationmasterforthisslave-required#master-host=<hostname>#Theusernametheslavewilluseforauthenticationwhenconnecting#tothemaster-required#master-user=<username>#Thepasswordtheslavewillauthenticatewithwhenconnectingto#themaster-required#master-password=<password>#Theportthemasterislisteningon.#optional-defaultsto3306#master-port=<port>#binarylogging-notrequiredforslaves,butrecommended#log-bin=mysql-bin#Pointthefollowingpathstodifferentdedicateddisks#tmpdir=/tmp/#log-update=/path-to-dedicated-directory/hostname#UncommentthefollowingifyouareusingBDBtables#bdb_cache_size=64M#bdb_max_lock=100000#UncommentthefollowingifyouareusingInnoDBtables#innodb_data_home_dir=/var/lib/mysql/#innodb_data_file_path=ibdata1:10M:autoextend#innodb_log_group_home_dir=/var/lib/mysql/#innodb_log_arch_dir=/var/lib/mysql/#Youcanset._buffer_pool_sizeupto50-80%#ofRAMbutbewareofsettingmemoryusagetoohigh#innodb_buffer_pool_size=256M#innodb_additional_mem_pool_size=20M#Set._log_file_sizeto25%ofbufferpoolsize#innodb_log_file_size=64M#innodb_log_buffer_size=8M#innodb_flush_log_at_trx_commit=1#innodb_lock_wait_timeout=50mysqldump#用户使用mysqldump工具在不同SQL数据库之间传输数据时的配置quick#表示支持较大数据库的转储max_allowed_packet=16M#置用来传输数据库表到其他数据库的最大允许包的大小mysql#指定启动MySQL服务器的配置no-auto-rehash#快速启动数据库#RemovethenextcommentcharacterifyouarenotfamiliarwithSQL#safe-updates#如果用户对SQL不熟悉，可以启用安全更新功能isamchk#使用isamchk工具修复isam类型表时采用的配置key_buffer=128Msort_buffer_size=128Mread_buffer=2Mwrite_buffer=2Mmyisamchk#使用isamchk工具修复myisam类型表时采用的配置key_buffer=128Msort_buffer_size=128Mread_buffer=2Mwrite_buffer=2Mmysqlhotcopy#使用mysqlhotcopy热备工具时采用的配置5、 interactive-timeout#数据库热备份期间将挂起数据库连接，该指令设置最大的超时时间，默认为28800秒一般而言，这5个配置文件都是比较成熟的有针对性的设计，用户在配置MySQL服务器时可根据实际的服务器需求情况挑选其中一个配置文件，将其复制到/etc目录覆盖原f文件即可，此时该文件是对全局数据库起作用的。也可以将其复制到MySQL服务器数据库文件的存储目录，如默认创建的mysql数据库的文件存储目录/var/lib/mysql，并改名为f，则此时将只对mysql这个数据库起作用。还可以将其复制到某一个用户的个人目录下并改名为“f”，则此配置只对该用户起作用。mysqld进程配置MySQL服务器可以，启用多个mysqld进程。在/etc/f配置文件中，mysqld配置段指定了mysqld进程的配置信息，如果有多个进程，则可以使用mysqld1、mysqld2为起始标志对其他进程进行配置，以调整不同进程的配置差异。下面给出一些mysqld进程的相关配置选项：选项名称功能说明Basedir=path指定MySQL的安装目录路径Bind-address=IP指定与主机的哪个网络接口绑定，默认绑定全部网络接口Console除了写入日之外，还将错误消息写入stderr及stdoutCharacter-sets-dir=path字符集安装的目录路径Chroot=path将某一目了设定为MySQL的根目录Character-sets-server=charset指定数据库服务器的默认字符集Core-file如果mysqld进程异常终止，要求把内核文件写入磁盘Datadir=path设置数据库文件的路径Default-time-zone=type设置服务器默认的时区Init-file=fileMysqld启动时从该文件读取sql语句并执行Log=file设置常规日志的存放位置和文件名。Log-bin=file设置二进制日志文件的文件名Log-bin-index=file设置二进制日志文件的索引文件名Log-error=file设置错误日志的文件名Log-slow-queries将所有执行时间超过long-query-time秒的查询计入该文件Pid-file=path进程ID文件的路径Port=port_num监听TCP/IP连接时使用的端口号Server_id指定mysqld进程的一个编号，值为以1开始的整数Skip-bdb禁用BDB引擎，可以节省内存Tmpdir=path指定临时文件的路径User=user_name|user_id指定运行mysqld进程的操作系统用户在mysqld进程运行的过程中还可以通过命令来设置系统中使用的变量，包括两种，一种是全局变量，它影响服务器的全局操作。另一个是会话变量，它只影响与其连接的具体客户端的相关操作。服务器启动之后，可以通过连接服务器并执行以下命令来修改变量：SETGLOBAL变量名=值#修改全局变量SETSESSION变量名=值#修改会话变量任何访问全局变量的客户端都可以即时看见全局变量的改变，但修改会话变量则只能改变客户端自己的会话变量，而不能更改其他客户端的会话变量，下面是两个设置系统变量的例子：mysql>SETGLOBALsort_buffer_size=10M;6、 mysql>SETSESSIONsort_buffer_size=10M;MySQL实例管理器MySQL实例管理器用来监视和管理MySQL数据库服务器进程，包括启动和停止MySQL服务器。MySQL实例管理器可以在mysqld_safe脚本中使用，也可以在远程主机上使用。默认情况下，操作系统调用/etc/rc.d/init.d/mysqld脚本启动MySQL服务器时，先调用mtsqld_safe脚本，再由该脚本启动mysqld进程。如果把/etc/rc.d/init.d/mysqld脚本中的use_mysqld_safe变量设置为0，便可以禁用mysqld_safe脚本，而是使用MySQL实例管理器来启动服务器。此时，实例管理器的行为取决于MySQL配置文件中的配置。如果没有配置文件，MySQL实例管理器将采用编译时默认的配置来启动。如果配置文件存在，实例管理器将分析配置文件中的mysqld配置段(包括mysqld1、mysqld2等)。每一个部分指定一个实例。启动时实例管理器将启动所有找到的实例，关闭时默认关闭所有的实例。要使用MySQL实例管理器，首先要为它自己创建一个用户：rootlocalhostdx#/usr/libexec/mysqlmanager-passwd>>/etc/mysqlmanager.passwdCreatingrecordfornewuser.Enterusername:adminEnterpassword:Re-typepassword:rootlocalhostdx#more/etc/mysqlmanager.passwdadmin:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9以上为实例管理器创建了一个名为admin的用户，保存在/etc/mysqlmanager.passwd文件中。在主配置文件/etc/f中，可以按以下示例配置实例管理器，它使用manager配置段，并读取mysqld配置段来创建实例：managerdefault-mysqld-path=/usr/local/mysql/libexec/mysqld#mysqld进程文件的位置socket=/tmp/manager.sock#通信套接口文件位置pid-file=/tmp/manager.pid#指定进程PID文件password-file=/etc/mysqlmanager.passwd#实例管理器密码文件位置monitoring-interval=3#监视实例的间隔时间(秒)port=2999#连接实例管理器的TCP端口再如下配置两个实例：mysqld1datadir=/var/lib/mysql1socket=/var/lib/mysql1/mysql.sockold_password=1server_id=1port=3307user=mysqlmysqld2datadir=/var/lib/mysql2socket=/var/lib/mysql2/mysql.sockserver_id=2port=3308user=mysql-