同时运行多个mysql

最近有个需求是要在一台机器上同步多个不同机房的数据库,这也是为了备份需求。于是不得不在同一台机器上跑多个mysql slave
运行多个mysql无非就是跑在不同的端口,有的是写多个my.cnf然后写多个mysqld来强制使用不同的数据目录。但是我这个人还是比较懒,还是写在单个my.cnf文件更容易一些。

#[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权限

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的方式就是可以使用不同端口来进行登录。

mysql -P 3306 -u rmcore -p
mysql -P 3307 -u rmcore -p

关于 Timo
XNIX SA & MYSQL DBA

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s

%d 博主赞过: