MySQL5.5.35單機(jī)多實(shí)例配置詳解
一、前言
創(chuàng)新互聯(lián)是一家專注于網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)與策劃設(shè)計(jì),云南網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:云南等地區(qū)。云南做網(wǎng)站價(jià)格咨詢:18980820575
二、概述
三、環(huán)境準(zhǔn)備
四、安裝MySQL 5.5.35
五、新建支持多實(shí)例的配置文件（我這里配置的是四個(gè)實(shí)例）
六、初始化多實(shí)例數(shù)據(jù)庫
七、提供管理腳本 mysqld_multi.server
八、整體備份方便后續(xù)遷移
九、管理MySQL多實(shí)例
十、登錄MySQL多實(shí)例
十一、其它管理配置
十二、總結(jié)
注，測試環(huán)境 CentOS 6.4 x86_64，軟件版本 MySQL 5.5.35，軟件下載地址：http://dev.mysql.com/downloads/mysql/5.1.html#downloads。
1.應(yīng)用場景
· 采用了數(shù)據(jù)偽分布式架構(gòu)的原因，而項(xiàng)目啟動(dòng)初期又不一定有那多的用戶量，為此先一組物理數(shù)據(jù)庫服務(wù)器，但部署多個(gè)實(shí)例，方便后續(xù)遷移；
· 為規(guī)避mysql對SMP架構(gòu)不支持的缺陷，使用多實(shí)例綁定處理器的辦法，把不同的數(shù)據(jù)庫分配到不同的實(shí)例上提供數(shù)據(jù)服務(wù)；
· 一臺(tái)物理數(shù)據(jù)庫服務(wù)器支撐多個(gè)數(shù)據(jù)庫的數(shù)據(jù)服務(wù)，為提高mysql復(fù)制的從機(jī)的恢復(fù)效率，采用多實(shí)例部署；
· 已經(jīng)為雙主復(fù)制的mysql數(shù)據(jù)庫服務(wù)器架構(gòu)，想部分重要業(yè)務(wù)的數(shù)據(jù)多一份異地機(jī)房的熱備份，而mysql復(fù)制暫不支持多主的復(fù)制模式，且不給用戶提供服務(wù)，為有效控制成本，會(huì)考慮異地機(jī)房部署一臺(tái)性能超好的物理服務(wù)器，甚至外加磁盤柜的方式，為此也會(huì)部署多實(shí)例；
· 傳統(tǒng)游戲行業(yè)的MMO/MMORPG，以及Web Game，每一個(gè)服都對應(yīng)一個(gè)數(shù)據(jù)庫，而可能要做很多數(shù)據(jù)查詢和數(shù)據(jù)訂正的工作，為減少維護(hù)而出錯(cuò)的概率，也可能采用多實(shí)例部署的方式，按區(qū)的概念分配數(shù)據(jù)庫；
上面的應(yīng)用場景介紹主要參考這篇文章：http://www.zhdba.com/mysqlops/2011/07/30/multi-mysqld/，我們這里應(yīng)用主要是基于前面三種場景。下面我們來說一下要注意的問題……
2.背景/需求、注意事項(xiàng)
(1).背景與需求
· 將所有的安裝文件、配置文件、數(shù)據(jù)目錄全部放存/data/mysql目錄中，便于今后實(shí)現(xiàn)快速遷移、整體備份和快速復(fù)制；
· 在一臺(tái)服務(wù)器上運(yùn)行四個(gè)MySQL實(shí)例，分別綁定在3306、3307、3308、3309端口上；
· 四個(gè)實(shí)例都開啟binlog日志，數(shù)據(jù)目錄分別存放在/data/mysql/data、/data/mysql/data2、/data/mysql/data3、/data/mysql/data4
· 四個(gè)實(shí)例均采用InnoDB作為默認(rèn)的存儲(chǔ)引擎，字符編碼采用UTF-8；
· 四個(gè)實(shí)例均采用相同的性能優(yōu)化配置參數(shù)；
(2).注意事項(xiàng)
· 在編譯安裝時(shí)，將數(shù)據(jù)庫的配置文件my.cnf以及data目錄等均指向到/data/mysql目錄中；
· 通過mysqld_multi的方式來管理四個(gè)不同的實(shí)例，采用相同的配置文件共享性能優(yōu)化配置參數(shù)；
· 在同一個(gè)配置文件中，利用[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]標(biāo)簽實(shí)現(xiàn)不同實(shí)例的差異化配置；
三、環(huán)境準(zhǔn)備
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.同步時(shí)間
|
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.創(chuàng)建安裝目錄與數(shù)據(jù)存放目錄
|
1 2 |
[root@node1 ~]# mkdir /data/mysql [root@node1 ~]# mkdir /data/mysql/data |
2.創(chuàng)建mysql用戶與組
|
1 2 3 |
[root@node1 ~]# useradd mysql [root@node1 ~]# id mysql uid=500(mysql) gid=500(mysql)組=500(mysql) |
3.授權(quán)安裝目錄與數(shù)據(jù)目錄
|
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就安裝完成了，下面我們?yōu)閙ysql提供多實(shí)例配置文件。
五、新建支持多實(shí)例的配置文件（我這里配置的是四個(gè)實(shí)例）
1.刪除默認(rèn)的數(shù)據(jù)目錄
|
1 2 |
[root@node1 ~]# cd /data/mysql/ [root@node1 mysql]# rm -rf data |
2.創(chuàng)建多實(shí)例配置需要的目錄
|
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自帶了幾個(gè)不同的配置文件，放置在/data/mysql/support-files目錄下，分別是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf，通過名稱我們可以很直觀的了解到他們是針對不同的服務(wù)器配置的，本文的配置文件是來自于my-small.cnf的，因?yàn)槲沂窃谔摂M機(jī)上進(jìn)行的設(shè)置；在生產(chǎn)環(huán)境中，我們可以通過參考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分參數(shù)配置，來對服務(wù)器進(jìn)行優(yōu)化；
4.修改my.cnf讀寫權(quán)限
|
1 2 |
[root@node1 etc]# chown -R root.root /data/mysql/etc [root@node1 etc]# chmod 600 /data/mysql/etc/my.cnf |
好了，到這里我們的配置文件就設(shè)置完成了，下面我們來初始化一下數(shù)據(jù)庫。
六、初始化多實(shí)例數(shù)據(jù)庫
1.切換到mysql的安裝目錄
|
1 |
[root@node1 ~]# cd /data/mysql/ |
2.初始化實(shí)例[mysqld1]
|
1 |
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql |
3.初始化實(shí)例[mysqld2]
|
1 |
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data2 --user=mysql |
4.初始化實(shí)例[mysqld3]
|
1 |
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data3 --user=mysql |
5.初始化實(shí)例[mysqld4]
|
1 |
[root@node1 mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data4 --user=mysql |
好了，到這里我們初始化工作就完成了，下面我們來提供一下多實(shí)例的管理腳本。
七、提供管理腳本 mysqld_multi.server
1.創(chuàng)建管理腳本目錄
|
1 |
[root@node1 mysql]# mkdir /data/mysql/init.d |
2.提供管理腳本
|
1 |
[root@node1 mysql]# cp support-files/mysqld_multi.server init.d/ |
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 |
[root@node1 mysql]# cd init.d/ [root@node1 init.d]# vim mysqld_multi.server #!/bin/sh # # A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen. # This script assumes that my.cnf file exists either in /etc/my.cnf or # /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the # mysqld_multi documentation for detailed instructions. # # This script can be used as /etc/init.d/mysql.server # # Comments to support chkconfig on RedHat Linux # chkconfig: 2345 64 36 # description: A very fast and reliable SQL database engine. # # Version 1.0 # basedir=/data/mysql bindir=/data/mysql/bin conf=/data/mysql/etc/my.cnf export PATH=$PATH:$bindir if test -x $bindir/mysqld_multi then mysqld_multi="$bindir/mysqld_multi"; else echo "Can't execute $bindir/mysqld_multi from dir $basedir"; exit; fi case "$1" in 'start' ) "$mysqld_multi" --defaults-extra-file=$conf start $2 ;; 'stop' ) "$mysqld_multi" --defaults-extra-file=$conf stop $2 ;; 'report' ) "$mysqld_multi" --defaults-extra-file=$conf report $2 ;; 'restart' ) "$mysqld_multi" --defaults-extra-file=$conf stop $2 "$mysqld_multi" --defaults-extra-file=$conf start $2 ;; *) echo "Usage: $0 {start|stop|report|restart}" >&2 ;; esac |
好了，到這里我們所有的配置就全部完成了，下面我們打包備份一下。
八、整體備份方便后續(xù)遷移
|
1 2 3 4 5 6 7 |
[root@node1 ~]# cd /data/ [root@node1 data]# tar czvf mysql-5.5.350-full.tar.gz /data/mysql/ [root@node1 data]# ll -h 總用量 128M drwx------. 2 root root 16K 8月 17 18:42 lost+found drwxr-xr-x 22 mysql mysql 4.0K 1月 6 22:08 mysql -rw-r--r-- 1 root root 128M 1月 7 00:25 mysql-5.5.350-full.tar.gz |
注，備份完成后，直接將mysql-5.5.350-full.tar.gz拿到其他服務(wù)器上，解壓后便可以直接啟用。嘿嘿，方便吧……
九、管理MySQL多實(shí)例
1.同時(shí)啟動(dòng)四個(gè)mysql實(shí)例
(1).方法一：
|
1 |
[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server start 1,2,3,4 |
或方法二：
|
1 |
[root@node1 ~]# /data/mysql/init.d/mysqld_multi.server start 3306,3307,3308,3309 |
(2).查看一下啟動(dòng)的實(shí)例
