mysql数据库多实例部署

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql数据库多实例部署

科技小能手 2017-11-15 01:49:00 浏览769
展开阅读全文

本文系统:rhel5.8 

ip : 192.168.100.150

数据库版本:mysql-5.6.15

1、创建部署mysql服务账号:

1
2
3
4
[root@daf ~]# useradd -d /opt/mysql mysql
[root@daf ~]# echo "mysql" |passwd --stdin mysql
Changing password for user mysql.
passwd: all authentication tokens updated successfully.

2、解压无需免装包:配置环境变量

1
2
3
4
5
6
7
8
9
10
[mysql@daf ~]$ tar zxvf mysql-5.6.15-linux-glibc2.5-x86_64.tar.gz
[mysql@daf ~]$ mv mysql-5.6.15-linux-glibc2.5-x86_64 mysql-5.6.15
 
[mysql@daf mysql-5.6.15]$ vim ~/.bash_profile
 
MYSQL_HOME=/opt/mysql/mysql-5.6.15
export MYSQL_HOME
 
PATH=$MYSQL_HOME/bin:$PATH
export PATH

3、配置多实例数据库配置文件

1
2
3
4
5
6
7
[root@daf ~]# mkdir /Data_for_Mysql
[root@daf ~]# chown mysql.mysql -R /Data_for_Mysql/
[root@daf ~]# su - mysql
[mysql@daf ~]$ mkdir -p /Data_for_Mysql/3301/data /Data_for_Mysql/3301/logs
[mysql@daf ~]$ touch /Data_for_Mysql/3301/my.cnf
[mysql@daf ~]$ mkdir -p /Data_for_Mysql/3302/data /Data_for_Mysql/3302/logs
[mysql@daf ~]$ touch /Data_for_Mysql/3302/my.cnf

目录结构: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysql@daf Data_for_Mysql]$ pwd
/Data_for_Mysql
[mysql@daf Data_for_Mysql]$ tree
.
|-- 3301
|   |-- data
|   |-- logs
|   `-- my.cnf
`-- 3302
    |-- data
    |-- logs
    `-- my.cnf
 
6 directories, 2 files
[mysql@daf Data_for_Mysql]$

配置my.cnf ######本配置文件只供参考

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
[mysql@daf 3301]$ vim /Data_for_Mysql/3301/my.cnf
[client]
port = 3301
socket = /Data_for_Mysql/3301/data/mysql.sock
[mysqld]
user=msql
port=3301
bind-address=192.168.100.150
socket = /Data_for_Mysql/3301/data/mysql.sock.3301
pid-file /Data_for_Mysql/3301/data/mysql.pid
basedir = /opt/mysql/mysql-5.6.15
datadir = /Data_for_Mysql/3301/data
server-id=1
log-bin=mysql-bin
log-bin-index= mysql-bin.index
 
# LOGGING
log_error=/Data_for_Mysql/3301/logs/mysql-error.log
slow_query_log_file= /Data_for_Mysql/3301/logs/mysql-slow.log
slow_query_log=1
 
character-sets-dir /opt/mysql/mysql-5.6.15/share/charsets
back_log = 2000
max_connections = 1000
connect-timeout = 60
wait-timeout = 28800
net_buffer_length = 16384
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 20
thread_concurrency = 128
query_cache_size = 256M
query_cache_limit = 2M
query_cache_min_res_unit = 2
 
default-time-zone = system
character-set-server = utf8
default-storage-engine = InnoDB
 
tmp_table_size = 512M
max_heap_table_size = 512M
 
max_binlog_size = 1G
max_relay_log_size = 1G
 
[mysql]
disable-auto-rehash
default-character-set = utf8

4、初始化数据库

1
2
3
[mysql@daf scripts]$ pwd
/opt/mysql/mysql-5.6.15/scripts
[mysql@daf scripts]$ ./mysql_install_db --defaults-file=/Data_for_Mysql/3301/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3301/data/

5、启动3301实例数据库  ###启动数据库必须在mysql目录;

1
2
3
4
5
6
[mysql@daf mysql-5.6.15]$ pwd
/opt/mysql/mysql-5.6.15
[mysql@daf mysql-5.6.15]$ mysqld_safe --defaults-file=/Data_for_Mysql/3301/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3301/data/ &
 
[mysql@daf mysql-5.6.15]$ netstat -nat |grep 3301
tcp        0      0 192.168.100.150:3301        0.0.0.0:*                   LISTEN

Ps:3302 实例操作和上面一样,只需改动端口及相应目录即可;

3302配置文件:

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
[mysql@daf 3302]$ cat my.cnf
[client]
port = 3302
socket = /Data_for_Mysql/3302/data/mysql.sock
[mysqld]
user=mysql
port=3302
bind-address=192.168.100.150
socket = /Data_for_Mysql/3302/data/mysql.sock.3302
pid-file /Data_for_Mysql/3302/data/mysql.pid
basedir = /opt/mysql/mysql-5.6.15
datadir = /Data_for_Mysql/3302/data
server-id=1
log-bin=mysql-bin
log-bin-index= mysql-bin.index
 
# LOGGING
log_error=/Data_for_Mysql/3302/logs/mysql-error.log
slow_query_log_file= /Data_for_Mysql/3302/logs/mysql-slow.log
slow_query_log=1
 
character-sets-dir /opt/mysql/mysql-5.6.15/share/charsets
back_log = 2000
max_connections = 1000
connect-timeout = 60
wait-timeout = 28800
net_buffer_length = 16384
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 20
thread_concurrency = 128
query_cache_size = 256M
query_cache_limit = 2M
query_cache_min_res_unit = 2
 
default-time-zone = system
character-set-server = utf8
default-storage-engine = InnoDB
 
tmp_table_size = 512M
max_heap_table_size = 512M
 
max_binlog_size = 1G
max_relay_log_size = 1G
 
[mysql]
disable-auto-rehash
default-character-set = utf8

3302 实例初始化;

1
2
3
[mysql@daf scripts]$ pwd
/opt/mysql/mysql-5.6.15/scripts
[mysql@daf scripts]$ ./mysql_install_db --defaults-file=/Data_for_Mysql/3302/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3302/data/

3302 启动该实例:

1
2
3
4
5
[mysql@daf mysql-5.6.15]$ mysqld_safe --defaults-file=/Data_for_Mysql/3302/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3302/data/ &
 
[mysql@daf mysql-5.6.15]$ netstat -nat |grep 330
tcp        0      0 192.168.100.150:3301        0.0.0.0:*                   LISTEN
tcp        0      0 192.168.100.150:3302        0.0.0.0:*                   LISTEN

6、创建root密码,要区别实例

1
[mysql@daf mysql-5.6.15]$ mysqladmin -uroot password 'root123' -S /Data_for_Mysql/3301/data/mysql.sock.3301

6.1、关闭mysql,区别实例

1
2
3
4
[mysql@daf mysql-5.6.15]$ mysqladmin -uroot -proot123 -S /Data_for_Mysql/3301/data/mysql.sock.3301 shutdown
Warning: Using a password on the command line interface can be insecure.
150402 15:44:52 mysqld_safe mysqld from pid file /Data_for_Mysql/3301/data/mysql.pid ended
[1]-  Done                    mysqld_safe --defaults-file=/Data_for_Mysql/3301/my.cnf --user=mysql --basedir=/opt/mysql/mysql-5.6.15 --datadir=/Data_for_Mysql/3301/data/



本文转自 西索oO 51CTO博客,原文链接:http://blog.51cto.com/lansgg/1627717

网友评论

登录后评论
0/500
评论