一、MySQL多实例介绍
mysql多实例，共用一套mysql安装程序，使用不同的配置文件(my.cnf)、启动程序、和数据文件，即在一台服务器上同时开启多个不同的服务器端口(3306,3307),同时运行多个mysql服务进程，这些服务进程通过不同的socket监听不同的服务器端口来提供服务。
二、安装mysql多实例
1．下载mysql/cmake安装包
|
1
2
3
|
[root@slave ~]# mkdir /home/tools
[root@slave ~]# wget –P /home/tools https://cmake.org/files/v2.8/cmake-2.8.12.tar.gz
[root@slave tools]# wget -P /home/tools/ http://cdn.mysql.com//Downloads/MySQL-5.5/mysql-5.5.50.tar.gz
|
2．建立账号
|
1
2
3
4
5
6
|
[root@slave ~]# groupadd mysql
[root@slave ~]# useradd -s /sbin/nologin -g mysql -M mysql
useradd参数说明：
-s /sbin/nologin # 表示禁止该用户登录，只需角色存在即可，加强安全
-g mysql # 指定属组
-M # 表示不创建用户家目录
|
3．配置安装环境
创建目录并授权
|
1
2
3
4
|
[root@slave ~]# mkdir -p /usr/local/mysql
[root@slave ~]# mkdir -p /db/{3306,3307}/data # 多实例文件目录结构
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql
[root@slave ~]# chown -R mysql.mysql /db
|
安装依赖
|
1
|
[root@slave ~]# yum install gcc gcc-c++ make cmake ncurses-devel bison perl -y
|
配置解析
|
1
2
3
4
|
[root@slave ~]# uname -n
slave[root@slave ~]# vim /etc/hosts
192.168.10.65 slave |
4．安装mysql(在这一步和之前，安装配置都和单实例配置相同)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@slave tools]# tar -zxf mysql-5.5.50.tar.gz
[root@slave tools]# cd mysql-5.5.50
[root@slave mysql-5.5.50]# cmake \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ \
> -DMYSQL_DATADIR=/db/3306/data \ # 这里数据目录先指定一个，后面初始化时手工配置
> -DMYSQL_TCP_PORT=3306 \> -DDEFAULT_CHARSET=utf8 \> -DDEFAULT_COLLATION=utf8_general_ci \> -DEXTRA_CHARSETS=all \> -DENABLED_LOCAL_INFILE=ON \> -DWITH_INNOBASE_STORAGE_ENGINE=1 \> -DWITH_FEDERATED_STORAGE_ENGINE=1 \> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \> -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \> -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \> -DWITH_FAST_MUTEXES=1 \> -DWITH_ZLIB=bundled \> -DENABLED_LOCAL_INFILE=1 \> -DWITH_READLINE=1 \> -DWITH_EMBEDDED_SERVER=1 \> -DWITH_DEBUG=0 \> -DMYSQL_UNIX_ADDR=/tmp/mysql.sock
|
|
1
2
|
[root@slave mysql-5.5.50]# make
[root@slave mysql-5.5.50]# make install
|
5．创建mysql多实例配置文件
|
1
2
|
[root@slave ~]# vim /db/3306/my.cnf
[root@slave ~]# vim /db/3307/my.cnf
|
|
MySQL 3306实例 |
MySQL 3307实例 |
|
[client] port = 3306 socket = /db/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /db/3306/mysql.sock basedir = /usr/local/mysql/ datadir = /db/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 1024 external-locking = FALSE max_allowed_packet = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 100 thread_concurrency = 8 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 pid-file=/db/3306/mysql.pid relay-log = /db/3306/relay-bin relay-log-info-file = /db/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 1M key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db = mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:28M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/db/3306/mysql_3306.err pid-file=/db/3306/mysql.pid |
[client] port = 3307 socket = /db/3307/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3307 socket = /db/3307/mysql.sock basedir = /usr/local/mysql/ datadir = /db/3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 1024 external-locking = FALSE max_allowed_packet = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 100 thread_concurrency = 8 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 pid-file=/db/3307/mysql.pid relay-log = /db/3307/relay-bin relay-log-info-file = /db/3307/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 1M key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db = mysql server-id = 3 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:28M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/db/3307/mysql_3307.err pid-file=/db/3307/mysql.pid |
完成后，/db目录结构:
|
1
2
3
4
5
6
7
8
|
[root@slave ~]# tree /db
/db├── 3306│ ├── data│ └── my.cnf└── 3307 ├── data
└── my.cnf
|
6．创建mysql多实例启动文件(3307实例只需更改端口即可)
|
1
2
|
[root@slave ~]# vim /db/3306/mysqld.py
[root@slave ~]# vim /db/3307/mysqld.py
|
编写MySQL启动程序
|
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
|
#!/bin/env python# -*- coding:utf-8 -*-'''MySQL多实例启动程序'''import os,sys,time
import subprocess
class Mysql_init:
def __init__(self):
self.user = 'root'
self.port = '3306'
self.passwd = 'q.123456'
self.Cmd_path = '/usr/local/mysql/bin'
self.sock = '/db/%s/mysql.sock'%self.port
def jindu(self):
for i in range(2):
sys.stdout.write('.')
time.sleep(1)
sys.stdout.flush()
time.sleep(1)
return '.'
def start(self):
if os.path.exists(self.sock):
print 'MySQL already running.'
else:
val = subprocess.call('/bin/sh %s/mysqld_safe --defaults-file=/db/%s/my.cnf 2>&1 >/dev/null &'%(self.Cmd_path,self.port),shell=True)
if val == 0:
print 'Starting MySQL',
print '%s'% self.jindu(),
print 'SUCCESS!'.strip()
return True
else:
print 'MySQL start failed.'
def stop(self):
if os.path.exists(self.sock):
val = subprocess.call('%s/mysqladmin -u%s -p%s -S %s shutdown'%(self.Cmd_path, self.user,self.passwd,self.sock),shell=True)
if val == 0:
print 'Shutting down MySQL',
print '%s'%self.jindu(),
print 'SUCCESS!'.strip()
return True
else:
print 'MySQL stop failed.'
else:
print 'MySQL already stopped.'
def restart(self):
self.stop()
self.start()
def main(self):
if sys.argv[1] == 'start':
self.start()
elif sys.argv[1] == 'stop':
self.stop()
elif sys.argv[1] == 'restart':
self.restart()
else:
print 'Usage: mysqld {start|stop|restart} [ MySQL server options ]'
if __name__ == '__main__':
ret = Mysql_init()
ret.main()
|
给程序加权限
|
1
2
3
|
[root@slave 3306]# chmod +x mysqld.py
[root@slave 3307]# chmod +x mysqld.py
[root@slave ~]# chown mysql.mysql /db –R # 授权
|
最终/db目录结构：
|
1
2
3
4
5
6
7
8
9
10
|
[root@slave ~]# tree /db
/db├── 3306│ ├── data│ ├── my.cnf│ └── mysqld.py└── 3307 ├── data
├── my.cnf
└── mysqld.py
|
7．配置mysql环境变量
|
1
2
|
[root@slave ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile
[root@slave ~]# source /etc/profile
|
8．初始化mysql多实例数据库文件
|
1
2
3
|
[root@slave ~]# cd /usr/local/mysql/scripts/
[root@slave scripts]# ./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/db/3306/data/ --user=mysql # 3306
[root@slave scripts]# ./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/db/3307/data/ --user=mysql # 3307
|
9．启动多实例mysql
|
1
2
3
4
|
[root@slave ~]# /db/3306/mysqld.py start
Starting MySQL... SUCCESS![root@slave ~]# /db/3307/mysqld.py start
Starting MySQL... SUCCESS! |
10．为mysql多实例设置密码(安装好之后，默认没有密码)
|
1
2
|
[root@slave ~]# mysqladmin -uroot -S /db/3306/mysql.sockpassword 'q.123456'
[root@slave ~]# mysqladmin -uroot -S /db/3307/mysql.sockpassword 'q.123456'
|
11．登录mysql
|
1
2
|
[root@slave ~]# mysql -uroot -pq.123456 -S /db/3306/mysql.sock
[root@slave ~]# mysql -uroot -pq.123456 -S /db/3307/mysql.sock
|
12．权限设定(因为启动程序中带有mysql密码)
|
1
2
|
[root@slave ~]# chmod 700 /db/{3306,3307}/mysqld.py
[root@slave ~]# chown root.root /db/{3306,3307}/mysqld.py
|