0%

同时运行多个mysql

最近有个需求是要在一台机器上同步多个不同机房的数据库,这也是为了备份需求。于是不得不在同一台机器上跑多个mysql slave
运行多个mysql无非就是跑在不同的端口,有的是写多个my.cnf然后写多个mysqld来强制使用不同的数据目录。但是我这个人还是比较懒,还是写在单个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
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
#[client]
#password = your_password
#port = 3306
#socket = /tmp/mysql.sock
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multi
password = multi
[mysqld1]
port = 3306
socket = /tmp/mysql.sock1
skip-locking
pid-file=/data/mysql/data1/net-app1a.pid
datadir = /data/mysql/data1
log=/data/mysql/data1/net-app1.log
log-slow-queries=/data/mysql/data1/slowquery.log
user = mysql
long_query_time = 1
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 32
query_cache_size = 32M
thread_concurrency = 2
max_connections=1000
server-id = 12
master-host = 192.168.0.101
master-user = repl
master-password = 'repl'
master-port = 3306
report-host = hostname
master-connect-retry = 30
replicate-do-db = aaa
log-bin
log-slave-updates



[mysqld2]
port = 3307
socket = /tmp/mysql.sock2
pid-file = /data/mysql/data2/net-app1b.pid
datadir = /data/mysql/data2
log=/data/mysql/data2/net-app1.log
log-slow-queries=/data/mysql/data2/slowquery.log
user = mysql
long_query_time = 1
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 32M
thread_cache = 32
query_cache_size = 16M
thread_concurrency = 2
max_connections=1000
server-id = 13
master-host = 192.168.0.102
master-user = repl
master-password = 'repl'
master-port = 3306
report-host = hostname
master-connect-retry = 30
replicate-do-db = bbb
replicate-do-db = ccc
log-bin
log-slave-updates
```

把mysql这个database复制到data1和data2之中,当然要注意下权限

这样使用/usr/local/mysql/bin/mysqld_multi start 1-2 来启动这里的mysql1和mysql2

但是现在还不能使用/usr/local/mysql/bin/mysqld_multi stop 1-2来关闭mysql1和mysql2,必须添加shutdown权限
```c
mysql -u root -S /tmp/mysqld.sock1 -p
> GRANT SHUTDOWN ON *.* TO 'multi'@'localhost' IDENTIFIED BY 'multi';
mysql -u root -S /tmp/mysqld.sock2 -p
> GRANT SHUTDOWN ON *.* TO 'multi'@'localhost' IDENTIFIED BY 'multi;
```
这样添加了shutdown权限后就可以用上述命令来关闭mysql1和mysql2

对于其它用户必须在添加的时候加上IP地址如 192.168.% 这样的方式,不能使用localhost这个表示方式,如果使用localhost那就必须指定sock来进行访问,就像上面那样的方式。而用IP的方式就是可以使用不同端口来进行登录。
```c
mysql -P 3306 -u rmcore -p
mysql -P 3307 -u rmcore -p
  • 本文作者: Timo
  • 本文链接: 84.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!