下MySQL數(shù)據(jù)庫服務(wù)器架設(shè)筆錄

1、Linux下MySQL數(shù)據(jù)庫服務(wù)器架設(shè)筆錄本文環(huán)境：CentOS+MySQLMySQL是目前最流行的開放源代碼的關(guān)系數(shù)據(jù)庫管理系統(tǒng)，是目前少有的開放源代碼的數(shù)據(jù)庫軟件之一。開放源代碼也就意味著任何人都能夠使用和改變MySQL軟件，任何人都能下載和使用此軟件而無需支付任何費(fèi)用?；谄淇焖?、可靠、易用和免費(fèi)等特點(diǎn)，MySQL數(shù)據(jù)庫系統(tǒng)在Web應(yīng)用以及其他各行各業(yè)中得到了非常廣泛的應(yīng)用。1、 MySQL數(shù)據(jù)庫軟件的安裝和運(yùn)行CentOS 5操作系統(tǒng)安裝完成之后一般情況下沒有默認(rèn)安裝MySQL數(shù)據(jù)庫，可進(jìn)入安裝光盤的軟件目錄，找到如下幾個(gè)文件：#Perl語言的數(shù)據(jù)API#MySQL數(shù)據(jù)庫客戶端程序#M

2、ySQL與Perl語言的接口程序包unixODBC-#Linux/Unix平臺(tái)的ODBC程序mysql-connector-odbc-#MySQL數(shù)據(jù)庫與ODBC的連接器#MySQL數(shù)據(jù)庫服務(wù)器程序?qū)⑸鲜鑫募截惖椒?wù)器合適位置，依次執(zhí)行安裝：rootwww1 MySQL_Install# rpm -ivh perl-DBI-1.52-2.el5.i386.rpm Preparing. # 100% 1:perl-DBI # 100%rootwww1 MySQL_Install# rpm -ivh mysql-5.0.77-3.el5.i386.rpm Preparing. # 100% 1:

3、mysql # 100%rootwww1 MySQL_Install# rpm -ivh perl-DBD-MySQL-3.0007-2.el5.i386.rpm Preparing. # 100% 1:perl-DBD-MySQL # 100%rootwww1 MySQL_Install# rpm -ivh unixODBC-7.1.i386.rpm Preparing. # 100% 1:unixODBC # 100%rootwww1 MySQL_Install# rpm -ivh mysql-connector-odbc-Preparing. # 100% 1:mysql-connect

4、or-odbc # 100%rootwww1 MySQL_Install# rpm -ivh mysql-server-5.0.77-3.el5.i386.rpm Preparing. # 100% 1:mysql-server # 100%也可以在聯(lián)網(wǎng)狀態(tài)下使用在線包管理工具yum進(jìn)行安裝：rootwww1 MySQL_Install# yum -y install mysql安裝完MySQL之后可以輸入以下命令啟動(dòng)數(shù)據(jù)庫：rootwww1 MySQL_Install# /etc/init.d/mysqld start查看啟動(dòng)狀態(tài)：rootwww1 MySQL_Install# ps -ea

5、f|grep mysqldroot 2842 1 0 09:43 pts/1 00: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=mysqlmysql 2892 2842 0 09:43 pts/1 00:00:00 /usr/libexec/mysqld -basedir=/usr -datadir=/var

6、/lib/mysql -user=mysql -pid-file=/var/run/mysqld/mysqld.pid -skip-external-locking -socket=/var/lib/mysql/mysql.sockroot 2911 2576 0 09:44 pts/1 00:00:00 grep mysqld可以看到服務(wù)器啟動(dòng)了2個(gè)進(jìn)程，其中/usr/bin/mysqld_safe是一個(gè)腳本程序，由root用戶運(yùn)行，它的作用是啟動(dòng)數(shù)據(jù)庫服務(wù)進(jìn)程mysqld，并一直監(jiān)控其運(yùn)行狀態(tài)，如果發(fā)現(xiàn)mysqld進(jìn)程死了，就會(huì)重新啟動(dòng)它。而數(shù)據(jù)庫服務(wù)進(jìn)程mysqld則有mysql用戶運(yùn)行

7、，用以提供數(shù)據(jù)庫服務(wù)。默認(rèn)情況下mysql的監(jiān)聽端口是3306，可以查看一下端口狀態(tài)：rootwww1 MySQL_Install# netstat -anlp |grep :3306tcp 00 .0:3306 .0:* LISTEN 2892/mysqld還可使用以下命令查看MySQL的運(yùn)行狀態(tài)：rootwww1 MySQL_Install# mysqladmin versionmysqladmin Ver 8.41 Distrib 5.0.77, for redhat-linux-gnu on i686Copyright (C) 2000-2006 MySQLABThis softwar

8、e comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL licenseProtocol version 10Connection Localhost via UNIX socketUNIX socket /var/lib/mysql/mysql.sockUptime: 7 min 1 secThreads: 1 Questions: 2 Slow queries: 0 Opens: 12 Flush tab

9、les: 1 Open tables: 6 Queries per second avg: 0.0052、 使用命令行客戶端登錄MySQL數(shù)據(jù)庫最常用的MySQL客戶端是mysql命令，其格式如下：Mysql -h <主機(jī)名> -u <用戶名> -p 數(shù)據(jù)庫名其中：-u <用戶名>表示使用指定的用戶名登錄，默認(rèn)為root用戶(注：此root用戶不等同于操作系統(tǒng)的root用戶)-p表示登錄時(shí)需要輸入密碼數(shù)據(jù)庫名表示登錄后要使用哪一個(gè)數(shù)據(jù)庫。下面為客戶端登錄的演示：rootwww1 MySQL_Install# mysql#連接本地MySQL數(shù)據(jù)庫Welcome

10、 to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 3Server version: 5.0.77 Source distributionType 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> use mysql#使用mysql數(shù)據(jù)庫Reading table information for completion of table and column namesYo

11、u can turn off this feature to get a quicker startup with -ADatabase changed#修改root用戶的密碼mysql> update user set password=password('123456') where user='root'Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0#在用戶表新增一條user記錄，即增加一個(gè)用戶usermysql> insert into us

12、er(host,user,password) values("%","user",password("123456");Query OK, 1 row affected, 3 warnings (0.00 sec)mysql> flush privileges;#刷新MySQL的系統(tǒng)權(quán)限相關(guān)表Query OK, 0 rows affected (0.01 sec)MySQL服務(wù)器安裝完成后，會(huì)首先創(chuàng)建一個(gè)名為mysql的數(shù)據(jù)庫，里面包含了MySQL數(shù)據(jù)庫系統(tǒng)的所有系統(tǒng)信息。以上操作中，user表即為用戶存放表，增加一條新記錄即增

13、加一個(gè)新用戶，改變password字段的值即為修改密碼。每一個(gè)用戶記錄都有一個(gè)host字段，表示允許該用戶從哪一臺(tái)主機(jī)登錄，“%”表示可以從所有客戶機(jī)進(jìn)行遠(yuǎn)程登錄，但不能從本機(jī)登錄。localhost表示只能從本機(jī)登錄?；蛘呖梢灾付ㄖ鳈C(jī)名實(shí)現(xiàn)從特定主機(jī)登錄。如需指定可從多臺(tái)主機(jī)登錄，則可增加多條記錄到user表，例如root用戶的記錄：mysql> select t.host,t.user,t.password from user t where t.user='root'+-+-+-+| host | user | password |+-+-+-+| localhos

14、t | root | 565491d704013245 | | | root | 565491d704013245 | | | root | 565491d704013245 | +-+-+-+3 rows in set (0.00 sec)如上結(jié)果顯示root用戶可從localhost、和登錄，其實(shí)指的都是本機(jī)。3、 圖形界面的MySQL客戶端MySQL的客戶端有許多種，其中有許多優(yōu)秀的圖形界面客戶端，可以大大的方便對(duì)數(shù)據(jù)庫的操作和管理，這里使用官方版本的圖形管理工具M(jìn)ySQL Query Browser。MySQL Query Browser是MySQ

15、L官方推薦的圖形管理工具，支持各種平臺(tái)，可從網(wǎng)站下載對(duì)應(yīng)平臺(tái)的軟件，安裝好初次運(yùn)行時(shí)需要建立一個(gè)命名的數(shù)據(jù)庫連接，如下圖所示：由于剛才的配置，root用戶是不能從客戶端遠(yuǎn)程登錄的，為了管理方面，在本機(jī)的客戶端中修改root用戶可從客戶端遠(yuǎn)程登錄：mysql> update user t set t.host=% where t.host= and t.user=root;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0修改后重啟數(shù)據(jù)庫：rootwww1 dx# /etc/init.d/my

16、sqld restart停止 MySQL： 確定啟動(dòng) MySQL： 確定此時(shí)點(diǎn)擊上圖中的OK按鈕即可連接進(jìn)入數(shù)據(jù)庫，其余操作略。除此之外，通過選擇Tools|MySQL Administrator或從開始菜單可以打開MySQL的管理工具M(jìn)ySQL Administrator，該工具主要是對(duì)MySQL進(jìn)行用戶管理，性能監(jiān)控，日志管理，數(shù)據(jù)庫備份與回復(fù)以及數(shù)據(jù)庫同步等操作，是一個(gè)非常實(shí)用的工具。4、 MySQL服務(wù)器的配置與連接MySQL服務(wù)器安裝完之后，其默認(rèn)的配置已經(jīng)可以使其正常運(yùn)行，但初始的配置不一定能讓服務(wù)器運(yùn)行在最佳的或最合適的狀態(tài)，此時(shí)我們就要對(duì)服務(wù)器進(jìn)行配置，以使其達(dá)到最佳配置狀態(tài)。

17、MySQL服務(wù)器默認(rèn)的配置文件是/etc目錄下的my.conf文件，這個(gè)文件在服務(wù)器安裝時(shí)就會(huì)創(chuàng)建，但里面的配置指令很少，只用于初步學(xué)習(xí)時(shí)使用。與這個(gè)文件同時(shí)被創(chuàng)建的還有另外5個(gè)例子配置文件，存放于/usr/share/doc/mysql-server-5.0.77目錄下。這5個(gè)配置文件的名稱和配置目標(biāo)如下：my-f ：為運(yùn)行在小內(nèi)存主機(jī)(<=64M)上的數(shù)據(jù)庫而設(shè)計(jì)的，常見情況是MySQL進(jìn)程占用資源少，數(shù)據(jù)庫只是偶爾被使用。my-f：是為在運(yùn)行中占用小內(nèi)存的情況(占用32M-64M)而設(shè)計(jì)的，其常見情況通常是MySQL服務(wù)器與其他應(yīng)用服務(wù)器一起存在于一臺(tái)主機(jī)上，并且此時(shí)的MySQL起

18、到較大的作用，使用的較為頻繁。通常要求主機(jī)擁有128M內(nèi)存以上。my-f ：為一般數(shù)據(jù)庫而設(shè)計(jì)，內(nèi)存使用一般會(huì)達(dá)到512M或以上，通常要求主機(jī)擁有超過1GB的內(nèi)存，適用于大多數(shù)的情況。my-f ：為企業(yè)應(yīng)用的MySQL數(shù)據(jù)庫而設(shè)計(jì)，占用超過1GB的系統(tǒng)內(nèi)存，同時(shí)對(duì)主機(jī)性能有更高要求。my-innodb-heavy-4G.cnf ：為使用InnoDB引擎的MySQL數(shù)據(jù)庫而設(shè)計(jì)的，應(yīng)當(dāng)運(yùn)行在擁有超過4GB內(nèi)存的專用主機(jī)上，通常的使用情況是數(shù)據(jù)庫連接比較多，且查詢非常復(fù)雜。以上配置文件中的指令種類相差不大，主要區(qū)別在于參數(shù)的設(shè)置不同。依現(xiàn)在的硬件水平，通常配置的內(nèi)存都達(dá)到數(shù)個(gè)GB以上，通常情況下適

19、用于my-f文件的配置，因此我們看下這個(gè)文件的內(nèi)容：dxwww1 mysql-server-5.0.77$ cat my-f # Example MySQL config file for large systems.# This is for a large system with memory = 512M where the system runs mainly# MySQL.# You can copy this file to# /etc/f to set global options,# mysql-data-dir/f to set server-specific options

20、 (in this# installation this directory is /var/lib/mysql) or# /f to set user-specific options.# In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the "-help" option.# The following options will b

21、e passed to all MySQL clients client#客戶端相關(guān)配置#password = your_password#提供默認(rèn)的用戶密碼port = 3306#客戶端連接服務(wù)器時(shí)，使用默認(rèn)的3306端口#為MySQL客戶端指定一個(gè)與服務(wù)器通信的本地套接字文件socket = /var/lib/mysql/mysql.sock# Here follows entries for some specific programs# The MySQL servermysqld#服務(wù)器相關(guān)配置port = 3306 #服務(wù)器監(jiān)聽端口號(hào)socket = /var/lib/mysql/

22、mysql.sock#為MySQL服務(wù)器指定一個(gè)與客戶端通信的本地套接字文件skip-locking#避免外部數(shù)據(jù)鎖key_buffer = 256M #設(shè)置存放索引區(qū)塊的內(nèi)存值max_allowed_packet = 1M#設(shè)定系統(tǒng)最大緩沖區(qū)大小table_cache = 256 #設(shè)定表數(shù)據(jù)高速緩存的大小sort_buffer_size = 1M#設(shè)定排序高速緩存的大小read_buffer_size = 1M #設(shè)定讀操作(順序讀取)緩存的大小read_rnd_buffer_size = 4M#設(shè)定讀操作(特定次序讀取)緩存的大小myisam_sort_buffer_size = 64M

23、#創(chuàng)建或修復(fù)表時(shí)，分配給ISAM索引排序的緩沖區(qū)大小。thread_cache_size = 8 #分配給線程的緩沖區(qū)大小query_cache_size= 16M#提交查詢操作時(shí)緩沖區(qū)大小# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8#多個(gè)CPU的線程數(shù)# Don't listen on a TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to m

24、ysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the "enable-named-pipe" option) will render mysqld useless!# #skip-networking# Disable Federated by defaultskip-

25、federated# Replication Master Server (default)# binary logging is required for replicationlog-bin=mysql-bin# required unique id between 1 and 232 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 1#設(shè)置服務(wù)器的ID號(hào)，在設(shè)置主/從數(shù)據(jù)庫時(shí)需要# Replication Slave (comment

26、 out master section to use this)# To configure this host as a replication slave, you can choose between# two methods :# 1) Use the CHANGE MASTER TO command (fully described in our manual) -# the syntax is:# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,# MASTER_USER=<user>

27、;, MASTER_PASSWORD=<password> ;# where you replace <host>, <user>, <password> by quoted strings and# <port> by the master's port number (3306 by default).# Example:# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,# MASTER_USER='joe', M

28、ASTER_PASSWORD='secret'# OR# 2) Set the variables below. However, in case you choose this method, then# start replication for the first time (even unsuccessfully, for example# if you mistyped the password in master-password and the slave fails to# connect), the slave will create a master.inf

29、o file, and any later# change in this file to the variables' values below will be ignored and# overridden by the content of the file, unless you shutdown# the slave server, delete and restart the slaver server.# For that reason, you may want to leave the lines below untou

30、ched# (commented) and instead use CHANGE MASTER TO (see above)# required unique id between 2 and 232 - 1# (and different from the master)# defaults to 2 if master-host is set# but will not function as a slave if omitted#server-id = 2# The replication master for this slave - required#master-host = &l

31、t;hostname># The username the slave will use for authentication when connecting# to the master - required#master-user = <username># The password the slave will authenticate with when connecting to# the master - required#master-password = <password># The port the master is listening on

32、.# optional - defaults to 3306#master-port = <port># binary logging - not required for slaves, but recommended#log-bin=mysql-bin# Point the following paths to different dedicated disks#tmpdir = /tmp/#log-update = /path-to-dedicated-directory/hostname# Uncomment the following if you are using B

33、DB tables#bdb_cache_size = 64M#bdb_max_lock = 100000# Uncomment the following if you are using InnoDB tables#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/# You can set ._buffer_p

34、ool_size up to 50 - 80 %# of RAM but beware of setting memory usage too high#innodb_buffer_pool_size = 256M#innodb_additional_mem_pool_size = 20M# Set ._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 64M#innodb_log_buffer_size = 8M#innodb_flush_log_at_trx_commit = 1#innodb_lock_wai

35、t_timeout = 50mysqldump#用戶使用mysqldump工具在不同SQL數(shù)據(jù)庫之間傳輸數(shù)據(jù)時(shí)的配置quick #表示支持較大數(shù)據(jù)庫的轉(zhuǎn)儲(chǔ)max_allowed_packet = 16M#置用來傳輸數(shù)據(jù)庫表到其他數(shù)據(jù)庫的最大允許包的大小mysql#指定啟動(dòng)MySQL服務(wù)器的配置no-auto-rehash#快速啟動(dòng)數(shù)據(jù)庫# Remove the next comment character if you are not familiar with SQL#safe-updates#如果用戶對(duì)SQL不熟悉，可以啟用安全更新功能isamchk #使用isamchk工具修復(fù)isam類

36、型表時(shí)采用的配置key_buffer = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2Mmyisamchk#使用isamchk工具修復(fù)myisam類型表時(shí)采用的配置key_buffer = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2Mmysqlhotcopy#使用mysqlhotcopy熱備工具時(shí)采用的配置interactive-timeout#數(shù)據(jù)庫熱備份期間將掛起數(shù)據(jù)庫連接，該指令設(shè)置最大的超時(shí)時(shí)間，默認(rèn)為28800秒一般而言，這5個(gè)配置文件

37、都是比較成熟的有針對(duì)性的設(shè)計(jì)，用戶在配置MySQL服務(wù)器時(shí)可根據(jù)實(shí)際的服務(wù)器需求情況挑選其中一個(gè)配置文件，將其復(fù)制到/etc目錄覆蓋原f文件即可，此時(shí)該文件是對(duì)全局?jǐn)?shù)據(jù)庫起作用的。也可以將其復(fù)制到MySQL服務(wù)器數(shù)據(jù)庫文件的存儲(chǔ)目錄，如默認(rèn)創(chuàng)建的mysql數(shù)據(jù)庫的文件存儲(chǔ)目錄/var/lib/mysql，并改名為f，則此時(shí)將只對(duì)mysql這個(gè)數(shù)據(jù)庫起作用。還可以將其復(fù)制到某一個(gè)用戶的個(gè)人目錄下并改名為“f”，則此配置只對(duì)該用戶起作用。5、 mysqld進(jìn)程配置MySQL服務(wù)器可以，啟用多個(gè)mysqld進(jìn)程。在/etc/f配置文件中，mysqld配置段指定了mysqld進(jìn)程的配置信息，如果有多個(gè)

38、進(jìn)程，則可以使用mysqld1、mysqld2為起始標(biāo)志對(duì)其他進(jìn)程進(jìn)行配置，以調(diào)整不同進(jìn)程的配置差異。下面給出一些mysqld進(jìn)程的相關(guān)配置選項(xiàng)：選項(xiàng)名稱功能說明Basedir=path指定MySQL的安裝目錄路徑Bind-address=IP指定與主機(jī)的哪個(gè)網(wǎng)絡(luò)接口綁定，默認(rèn)綁定全部網(wǎng)絡(luò)接口Console除了寫入日之外，還將錯(cuò)誤消息寫入stderr及stdoutCharacter-sets-dir=path字符集安裝的目錄路徑Chroot=path將某一目了設(shè)定為MySQL的根目錄Character-sets-server=charset指定數(shù)據(jù)庫服務(wù)器的默認(rèn)字符集Core-file如果my

39、sqld進(jìn)程異常終止，要求把內(nèi)核文件寫入磁盤Datadir=path設(shè)置數(shù)據(jù)庫文件的路徑Default-time-zone=type設(shè)置服務(wù)器默認(rèn)的時(shí)區(qū)Init-file=fileMysqld啟動(dòng)時(shí)從該文件讀取sql語句并執(zhí)行Log=file設(shè)置常規(guī)日志的存放位置和文件名。Log-bin=file設(shè)置二進(jìn)制日志文件的文件名Log-bin-index=file設(shè)置二進(jìn)制日志文件的索引文件名Log-error=file設(shè)置錯(cuò)誤日志的文件名Log-slow-queries將所有執(zhí)行時(shí)間超過long-query-time秒的查詢計(jì)入該文件Pid-file=path進(jìn)程ID文件的路徑Port=port_

40、num監(jiān)聽TCP/IP連接時(shí)使用的端口號(hào)Server_id指定mysqld進(jìn)程的一個(gè)編號(hào)，值為以1開始的整數(shù)Skip-bdb禁用BDB引擎，可以節(jié)省內(nèi)存Tmpdir=path指定臨時(shí)文件的路徑User=user_name|user_id指定運(yùn)行mysqld進(jìn)程的操作系統(tǒng)用戶在mysqld進(jìn)程運(yùn)行的過程中還可以通過命令來設(shè)置系統(tǒng)中使用的變量，包括兩種，一種是全局變量，它影響服務(wù)器的全局操作。另一個(gè)是會(huì)話變量，它只影響與其連接的具體客戶端的相關(guān)操作。服務(wù)器啟動(dòng)之后，可以通過連接服務(wù)器并執(zhí)行以下命令來修改變量：SET GLOBAL 變量名=值 #修改全局變量SET SESSION 變量名=值 #修改會(huì)話變量任何訪問全局變量的客戶端都可以即時(shí)看見全局變量的改變，但修改會(huì)話變量則只能改變客戶端自己的會(huì)話變量，而不能更改其他客戶端的會(huì)話變量，下面是兩個(gè)設(shè)置系統(tǒng)變量的例子：mysql> SET GLOBAL sort_buffer_size = 10M;mysql&