一、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 mysqluseradd参数说明： -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 -nslave[root@slave ~]# vim /etc/hosts192.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,timeimport subprocessclass 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 startStarting MySQL... SUCCESS![root@slave ~]# /db/3307/mysqld.py startStarting 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 |
原文：http://www.cnblogs.com/opsedu/p/50eaf49082dc20a86878ac4a7c90fda5.html