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

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

2、ySQL與Perl語言的接口程序包unixODBC-#Linux/Unix平臺的ODBC程序mysql-connector-odbc-#MySQL數(shù)據(jù)庫與ODBC的連接器#MySQL數(shù)據(jù)庫服務器程序將上述文件拷貝到服務器合適位置，依次執(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進行安裝：rootwww1 MySQL_Install# yum -y install mysql安裝完MySQL之后可以輸入以下命令啟動數(shù)據(jù)庫：rootwww1 MySQL_Install# /etc/init.d/mysqld start查看啟動狀態(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可以看到服務器啟動了2個進程，其中/usr/bin/mysqld_safe是一個腳本程序，由root用戶運行，它的作用是啟動數(shù)據(jù)庫服務進程mysqld，并一直監(jiān)控其運行狀態(tài)，如果發(fā)現(xiàn)mysqld進程死了，就會重新啟動它。而數(shù)據(jù)庫服務進程mysqld則有mysql用戶運行

7、，用以提供數(shù)據(jù)庫服務。默認情況下mysql的監(jiān)聽端口是3306，可以查看一下端口狀態(tài)：rootwww1 MySQL_Install# netstat -anlp |grep :3306tcp 00 .0:3306 .0:* LISTEN 2892/mysqld還可使用以下命令查看MySQL的運行狀態(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 <主機名> -u <用戶名> -p 數(shù)據(jù)庫名其中：-u <用戶名>表示使用指定的用戶名登錄，默認為root用戶(注：此root用戶不等同于操作系統(tǒng)的root用戶)-p表示登錄時需要輸入密碼數(shù)據(jù)庫名表示登錄后要使用哪一個數(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記錄，即增加一個用戶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)權限相關表Query OK, 0 rows affected (0.01 sec)MySQL服務器安裝完成后，會首先創(chuàng)建一個名為mysql的數(shù)據(jù)庫，里面包含了MySQL數(shù)據(jù)庫系統(tǒng)的所有系統(tǒng)信息。以上操作中，user表即為用戶存放表，增加一條新記錄即增

13、加一個新用戶，改變password字段的值即為修改密碼。每一個用戶記錄都有一個host字段，表示允許該用戶從哪一臺主機登錄，“%”表示可以從所有客戶機進行遠程登錄，但不能從本機登錄。localhost表示只能從本機登錄?；蛘呖梢灾付ㄖ鳈C名實現(xiàn)從特定主機登錄。如需指定可從多臺主機登錄，則可增加多條記錄到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)如上結果顯示root用戶可從localhost、和登錄，其實指的都是本機。3、 圖形界面的MySQL客戶端MySQL的客戶端有許多種，其中有許多優(yōu)秀的圖形界面客戶端，可以大大的方便對數(shù)據(jù)庫的操作和管理，這里使用官方版本的圖形管理工具MySQL Query Browser。MySQL Query Browser是MySQ

15、L官方推薦的圖形管理工具，支持各種平臺，可從網(wǎng)站下載對應平臺的軟件，安裝好初次運行時需要建立一個命名的數(shù)據(jù)庫連接，如下圖所示：由于剛才的配置，root用戶是不能從客戶端遠程登錄的，為了管理方面，在本機的客戶端中修改root用戶可從客戶端遠程登錄：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： 確定啟動 MySQL： 確定此時點擊上圖中的OK按鈕即可連接進入數(shù)據(jù)庫，其余操作略。除此之外，通過選擇Tools|MySQL Administrator或從開始菜單可以打開MySQL的管理工具MySQL Administrator，該工具主要是對MySQL進行用戶管理，性能監(jiān)控，日志管理，數(shù)據(jù)庫備份與回復以及數(shù)據(jù)庫同步等操作，是一個非常實用的工具。4、 MySQL服務器的配置與連接MySQL服務器安裝完之后，其默認的配置已經(jīng)可以使其正常運行，但初始的配置不一定能讓服務器運行在最佳的或最合適的狀態(tài)，此時我們就要對服務器進行配置，以使其達到最佳配置狀態(tài)。

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

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

19、用于my-f文件的配置，因此我們看下這個文件的內(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#客戶端相關配置#password = your_password#提供默認的用戶密碼port = 3306#客戶端連接服務器時，使用默認的3306端口#為MySQL客戶端指定一個與服務器通信的本地套接字文件socket = /var/lib/mysql/mysql.sock# Here follows entries for some specific programs# The MySQL servermysqld#服務器相關配置port = 3306 #服務器監(jiān)聽端口號socket = /var/lib/mysql/

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

23、#創(chuàng)建或修復表時，分配給ISAM索引排序的緩沖區(qū)大小。thread_cache_size = 8 #分配給線程的緩沖區(qū)大小query_cache_size= 16M#提交查詢操作時緩沖區(qū)大小# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8#多個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#設置服務器的ID號，在設置主/從數(shù)據(jù)庫時需要# 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ù)時的配置quick #表示支持較大數(shù)據(jù)庫的轉儲max_allowed_packet = 16M#置用來傳輸數(shù)據(jù)庫表到其他數(shù)據(jù)庫的最大允許包的大小mysql#指定啟動MySQL服務器的配置no-auto-rehash#快速啟動數(shù)據(jù)庫# Remove the next comment character if you are not familiar with SQL#safe-updates#如果用戶對SQL不熟悉，可以啟用安全更新功能isamchk #使用isamchk工具修復isam類

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

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

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

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

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