MySQL 5.5.35 單機多例項配置詳解

weixin_33766168發表於2017-11-12
MySql

一、前言

二、概述

三、環境準備

四、安裝MySQL 5.5.35

五、新建支援多例項的配置檔案（我這裡配置的是四個例項）

六、初始化多例項資料庫

七、提供管理指令碼 mysqld_multi.server

八、整體備份方便後續遷移

九、管理MySQL多例項

十、登入MySQL多例項

十一、其它管理配置

十二、總結

注，測試環境 CentOS 6.4 x86_64，軟體版本 MySQL 5.5.35，軟體下載地址：http://dev.mysql.com/downloads/mysql/5.1.html#downloads。

1.應用場景

  • 採用了資料偽分散式架構的原因，而專案啟動初期又不一定有那多的使用者量，為此先一組物理資料庫伺服器，但部署多個例項，方便後續遷移；

  • 為規避mysql對SMP架構不支援的缺陷，使用多例項繫結處理器的辦法，把不同的資料庫分配到不同的例項上提供資料服務；

  • 一臺物理資料庫伺服器支撐多個資料庫的資料服務，為提高mysql複製的從機的恢復效率，採用多例項部署；

  • 已經為雙主複製的mysql資料庫伺服器架構，想部分重要業務的資料多一份異地機房的熱備份，而mysql複製暫不支援多主的複製模式，且不給使用者提供服務，為有效控制成本，會考慮異地機房部署一臺效能超好的物理伺服器，甚至外加磁碟櫃的方式，為此也會部署多例項；

  • 傳統遊戲行業的MMO/MMORPG，以及Web Game，每一個服都對應一個資料庫，而可能要做很多資料查詢和資料訂正的工作，為減少維護而出錯的概率，也可能採用多例項部署的方式，按區的概念分配資料庫；

上面的應用場景介紹主要參考這篇文章：http://www.zhdba.com/mysqlops/2011/07/30/multi-mysqld/，我們這裡應用主要是基於前面三種場景。下面我們來說一下要注意的問題……

2.背景/需求、注意事項

(1).背景與需求

  • 將所有的安裝檔案、配置檔案、資料目錄全部放存/data/mysql目錄中，便於今後實現快速遷移、整體備份和快速複製；

  • 在一臺伺服器上執行四個MySQL例項，分別繫結在3306、3307、3308、3309埠上；

  • 四個例項都開啟binlog日誌，資料目錄分別存放在/data/mysql/data、/data/mysql/data2、/data/mysql/data3、/data/mysql/data4

  • 四個例項均採用InnoDB作為預設的儲存引擎，字元編碼採用UTF-8；

  • 四個例項均採用相同的效能優化配置引數；

(2).注意事項

  • 在編譯安裝時，將資料庫的配置檔案my.cnf以及data目錄等均指向到/data/mysql目錄中；

  • 通過mysqld_multi的方式來管理四個不同的例項，採用相同的配置檔案共享效能優化配置引數；

  • 在同一個配置檔案中，利用[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]標籤實現不同例項的差異化配置；

 

三、環境準備

1.安裝yum源

1
2
[root@node1 src]# wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@node1 src]# rpm -ivh epel-release-6-8.noarch.rpm

2.同步時間

1
2
3
[root@node1 src]# yum install -y ntp
[root@node1 src]# ntpdate 202.120.2.101
[root@node1 src]# hwclock –w

3.安裝mysql5.5依賴包

1
[root@node1 ~]# yum install -y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*

4.安裝cmake

1
[root@node1 ~]# yum install -y cmake

 

四、安裝MySQL 5.5.35

1.建立安裝目錄與資料存放目錄

1
2
[root@node1 ~]# mkdir /data/mysql
[root@node1 ~]# mkdir /data/mysql/data

2.建立mysql使用者與組

1
2
3
[root@node1 ~]# useradd mysql
[root@node1 ~]# id mysql 
uid=500(mysql) gid=500(mysql) 組=500(mysql)

3.授權安裝目錄與資料目錄

1
2
[root@node1 ~]# chown -R mysql.mysql /data/mysql/ 
[root@node1 ~]# chown -R mysql.mysql /data/mysql/data

4.安裝mysql

1
2
3
4
5
[root@node1 ~]# cd src/ 
[root@node1 src]# tar xf mysql-5.5.35.tar.gz   
[root@node1 src]# cd mysql-5.5.35
[root@node1 mysql-5.5.35]# cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DSYSCONFDIR=/data/mysql/etc -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1
[root@node1 mysql-5.5.35]# make && make install

好了，到這裡我們的mysql就安裝完成了，下面我們為mysql提供多例項配置檔案。

 

五、新建支援多例項的配置檔案（我這裡配置的是四個例項）

1.刪除預設的資料目錄

1
2
[root@node1 ~]# cd /data/mysql/ 
[root@node1 mysql]# rm -rf data

2.建立多例項配置需要的目錄

1
2
[root@node1 mysql]# mkdir etc tmp run log binlogs data data2 data3 data4
[root@node1 mysql]# chown -R mysql.mysql tmp run log binlogs data data2 data3 data4

3.提供配置檔案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
[root@node1 ~]# cd src/ 
[root@node1 src]# cd mysql-5.5.35
[root@node1 mysql-5.5.35]# cp support-files/my-small.cnf /data/mysql/etc/my.cnf
[root@node1 ~]# cd /data/mysql/etc/ 
[root@node1 etc]# vim my.cnf  
# This server may run 4+ separate instances. So we use mysqld_multi to manage their services.
[client] 
default-character-set = utf8
[mysqld_multi] 
mysqld = /data/mysql/bin/mysqld_safe  
mysqladmin = /data/mysql/bin/mysqladmin  
log = /data/mysql/log/mysqld_multi.log  
user = root   
#password =
                                                                                                     
# This is the general purpose database. 
# The locations are default.  
# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi.
[mysqld1] 
socket = /data/mysql/run/mysqld.sock  
port = 3306  
pid-file = /data/mysql/run/mysqld.pid  
datadir = /data/mysql/data  
lc-messages-dir = /data/mysql/share/english  
                                                                                                      
# These support master - master replication  
#auto-increment-increment = 4  
#auto-increment-offset = 1  # Since it is master 1  
log-bin = /data/mysql/binlogs/bin-log-mysqld1  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld1.index  
#binlog-do-db = # Leave this blank if you want to control it on slave  
max_binlog_size = 1024M  
                                                                                                      
# This is exlusively for mysqld2  
# It is on 3307 with data directory /data/mysqld/data2
[mysqld2] 
socket = /data/mysql/run/mysqld.sock2  
port = 3307  
pid-file = /data/mysql/run/mysqld.pid2  
datadir = /data/mysql/data2  
lc-messages-dir = /data/mysql/share/english  
                                                                                                      
# Disable DNS lookups  
#skip-name-resolve  
                                                                                                      
# These support master - slave replication  
log-bin = /data/mysql/binlogs/bin-log-mysqld2  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld2.index  
#binlog-do-db =  # Leave this blank if you want to control it on slave  
max_binlog_size = 1024M
 # Relay log settings
#relay-log = /data/mysql/log/relay-log-mysqld2
#relay-log-index = /data/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit = 4G
                                                                                                      
# Slow query log settings
#log-slow-queries = /data/mysql/log/slow-log-mysqld2
#long_query_time = 2
#log-queries-not-using-indexes
                                                                                                      
# This is exlusively for mysqld3 
# It is on 3308 with data directory /data/mysqld/data3
[mysqld3] 
socket = /data/mysql/run/mysqld.sock3  
port = 3308  
pid-file = /data/mysql/run/mysqld.pid3  
datadir = /data/mysql/data3  
lc-messages-dir = /data/mysql/share/english
#Disable DNS lookups 
#skip-name-resolve
# These support master - slave replication 
log-bin = /data/mysql/binlogs/bin-log-mysqld3  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld3.index  
#binlog-do-db =  # Leave this blank if you want to control it on slave  
 max_binlog_size = 1024M
 # This is exlusively for mysqld4
# It is on 3309 with data directory /data/mysqld/data4
[mysqld4] 
socket = /data/mysql/run/mysqld.sock4  
port = 3309  
pid-file = /data/mysql/run/mysqld.pid4  
datadir = /data/mysql/data4  
lc-messages-dir = /data/mysql/share/english
# Disable DNS lookups 
#skip-name-resolve
# These support master - slave replication 
log-bin = /data/mysql/binlogs/bin-log-mysqld4  
log-bin-index = /data/mysql/binlogs/bin-log-mysqld4.index  
#binlog-do-db =  # Leave this blank if you want to control it on slave  
max_binlog_size = 1024M
 # The rest of the my.cnf is shared
# Here follows entries for some specific programs
# The MySQL server
[mysqld] 
basedir = /data/mysql  
tmpdir = /data/mysql/tmp  
socket = /data/mysql/run/mysqld.sock  
port = 3306  
pid-file = /data/mysql/run/mysqld.pid  
datadir = /data/mysql/data  
lc-messages-dir = /data/mysql/share/english  
                                                                                                      
skip-external-locking  
key_buffer_size = 16K  
max_allowed_packet = 1M  
table_open_cache = 4  
sort_buffer_size = 64K  
read_buffer_size = 256K  
read_rnd_buffer_size = 256K  
net_buffer_length = 2K  
thread_stack = 128K  
                                                                                                      
# Increase the max connections  
max_connections = 2  
                                                                                                      
# The expiration time for logs, including binlogs  
expire_logs_days = 14  
                                                                                                      
# Set the character as utf8  
character-set-server = utf8  
collation-server = utf8_unicode_ci  
                                                                                                      
# This is usually only needed when setting up chained replication  
#log-slave-updates  
                                                                                                      
# Enable this to make replication more resilient against server crashes and restarts  
# but can cause higher I/O on the server  
#sync_binlog = 1  
                                                                                                      
# The server id, should be unique in same network  
server-id = 1  
                                                                                                      
# Set this to force MySQL to use a particular engine/table-type for new tables  
# This setting can still be overridden by specifying the engine explicitly  
# in the CREATE TABLE statement  
default-storage-engine = INNODB  
                                                                                                      
# Enable Per Table Data for InnoDB to shrink ibdata1  
innodb_file_per_table = 1  
                                                                                                      
# Uncomment the following if you are using InnoDB tables  
#innodb_data_home_dir = /data/mysql/data  
#innodb_data_file_path = ibdata1:10M:autoextend  
#innodb_log_group_home_dir = /data/mysql/data  
# You can set .._buffer_pool_size up to 50 - 80 % of RAM  
# but beware of setting memory usage too high  
innodb_buffer_pool_size = 16M  
innodb_additional_mem_pool_size = 2M  
# Set .._log_file_size to 25 % of buffer pool size  
innodb_log_file_size = 5M  
innodb_log_buffer_size = 8M  
innodb_flush_log_at_trx_commit = 1  
innodb_lock_wait_timeout = 50  
                                                                                                      
[mysqldump]  
quick  
max_allowed_packet = 16M  
                                                                                                      
[mysql]  
no-auto-rehash  
                                                                                                      
[myisamchk]  
key_buffer_size = 8M  
sort_buffer_size = 8M  
                                                                                                      
[mysqlhotcopy]  
interactive-timeout  
                                                                                                      
[mysql.server]  
user = mysql  
                                                                                                      
[mysqld_safe]  
log-error = /data/mysql/log/mysqld.log  
pid-file = /data/mysql/run/mysqld.pid  
open-files-limit = 8192

注，MySQL自帶了幾個不同的配置檔案，放置在/data/mysql/support-files目錄下，分別是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf，通過名稱我們可以很直觀的瞭解到他們是針對不同的伺服器配置的，本文的配置檔案是來自於my-small.cnf的，因為我是在虛擬機器上進行的設定；在生產環境中，我們可以通過參考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分引數配置，來對伺服器進行優化；

4.修改my.cnf讀寫許可權

1
2
[root@node1 etc]# chown -R root.root /data/mysql/etc
[root@node1 etc]# chmod 600 /data/mysql/etc/my.cnf

好了，到這裡我們的配置檔案就設定完成了，下面我們來初始化一下資料庫。

 

六、初始化多例項資料庫

1.切換到mysql的安裝目錄

1
[root@node1 ~]# cd /data/mysql/

2.初始化例項[mysqld1]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql

3.初始化例項[mysqld2]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data2 --user=mysql

4.初始化例項[mysqld3]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data3 --user=mysql

5.初始化例項[mysqld4]

1
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data4 --user=mysql

好了，到這裡我們初始化工作就完成了，下面我們來提供一下多例項的管理指令碼。

 


本文轉自 張衝andy 部落格園部落格，原文連結：http://www.cnblogs.com/andy6/p/6171859.html   ，如需轉載請自行聯絡原作者



相關文章