MySQL5.5.35單機多實例配置詳解
一、前言
創(chuàng)新互聯(lián)是一家專注于網(wǎng)站設計、成都網(wǎng)站建設與策劃設計,云南網(wǎng)站建設哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設10余年,網(wǎng)設計領域的專業(yè)建站公司;建站業(yè)務涵蓋:云南等地區(qū)。云南做網(wǎng)站價格咨詢:18980820575
二、概述
三、環(huán)境準備
四、安裝MySQL 5.5.35
五、新建支持多實例的配置文件（我這里配置的是四個實例）
六、初始化多實例數(shù)據(jù)庫
七、提供管理腳本 mysqld_multi.server
八、整體備份方便后續(xù)遷移
九、管理MySQL多實例
十、登錄MySQL多實例
十一、其它管理配置
十二、總結
注，測試環(huán)境 CentOS 6.4 x86_64，軟件版本 MySQL 5.5.35，軟件下載地址：http://dev.mysql.com/downloads/mysql/5.1.html#downloads。
1.應用場景
· 采用了數(shù)據(jù)偽分布式架構的原因，而項目啟動初期又不一定有那多的用戶量，為此先一組物理數(shù)據(jù)庫服務器，但部署多個實例，方便后續(xù)遷移；
· 為規(guī)避mysql對SMP架構不支持的缺陷，使用多實例綁定處理器的辦法，把不同的數(shù)據(jù)庫分配到不同的實例上提供數(shù)據(jù)服務；
· 一臺物理數(shù)據(jù)庫服務器支撐多個數(shù)據(jù)庫的數(shù)據(jù)服務，為提高mysql復制的從機的恢復效率，采用多實例部署；
· 已經(jīng)為雙主復制的mysql數(shù)據(jù)庫服務器架構，想部分重要業(yè)務的數(shù)據(jù)多一份異地機房的熱備份，而mysql復制暫不支持多主的復制模式，且不給用戶提供服務，為有效控制成本，會考慮異地機房部署一臺性能超好的物理服務器，甚至外加磁盤柜的方式，為此也會部署多實例；
· 傳統(tǒng)游戲行業(yè)的MMO/MMORPG，以及Web Game，每一個服都對應一個數(shù)據(jù)庫，而可能要做很多數(shù)據(jù)查詢和數(shù)據(jù)訂正的工作，為減少維護而出錯的概率，也可能采用多實例部署的方式，按區(qū)的概念分配數(shù)據(jù)庫；
上面的應用場景介紹主要參考這篇文章：http://www.zhdba.com/mysqlops/2011/07/30/multi-mysqld/，我們這里應用主要是基于前面三種場景。下面我們來說一下要注意的問題……
2.背景/需求、注意事項
(1).背景與需求
· 將所有的安裝文件、配置文件、數(shù)據(jù)目錄全部放存/data/mysql目錄中，便于今后實現(xiàn)快速遷移、整體備份和快速復制；
· 在一臺服務器上運行四個MySQL實例，分別綁定在3306、3307、3308、3309端口上；
· 四個實例都開啟binlog日志，數(shù)據(jù)目錄分別存放在/data/mysql/data、/data/mysql/data2、/data/mysql/data3、/data/mysql/data4
· 四個實例均采用InnoDB作為默認的存儲引擎，字符編碼采用UTF-8；
· 四個實例均采用相同的性能優(yōu)化配置參數(shù)；
(2).注意事項
· 在編譯安裝時，將數(shù)據(jù)庫的配置文件my.cnf以及data目錄等均指向到/data/mysql目錄中；
· 通過mysqld_multi的方式來管理四個不同的實例，采用相同的配置文件共享性能優(yōu)化配置參數(shù)；
· 在同一個配置文件中，利用[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]標簽實現(xiàn)不同實例的差異化配置；
三、環(huá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.同步時間
|
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.授權安裝目錄與數(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提供多實例配置文件。
五、新建支持多實例的配置文件（我這里配置的是四個實例）
1.刪除默認的數(shù)據(jù)目錄
|
1 2 |
[root@node1 ~]# cd /data/mysql/ [root@node1 mysql]# rm -rf data |
2.創(chuàng)建多實例配置需要的目錄
|
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的，因為我是在虛擬機上進行的設置；在生產(chǎn)環(huán)境中，我們可以通過參考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分參數(shù)配置，來對服務器進行優(yōu)化；
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 |
好了，到這里我們的配置文件就設置完成了，下面我們來初始化一下數(shù)據(jù)庫。
六、初始化多實例數(shù)據(jù)庫
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 |
好了，到這里我們初始化工作就完成了，下面我們來提供一下多實例的管理腳本。
七、提供管理腳本 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拿到其他服務器上，解壓后便可以直接啟用。嘿嘿，方便吧……
九、管理MySQL多實例
1.同時啟動四個mysql實例
(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).查看一下啟動的實例
