主从Mysql数据库之my.cnf 存档

主server:192.168.0.47
从server1:192.168.0.68
从server2:192.168.0.69

两组Mysql主从,其中:

192.168.0.47:3307  => 192.168.0.68:3307
                               => 192.168.0.69:3307

192.168.0.47:3308  => 192.168.0.68:3308
                               => 192.168.0.69:3308

一台服务器运行多个主从,使用mysqld_multi来启动和停止Mysql,此过程不表,三台server的my.cnf如下:

主server:192.168.0.47

[mysqld_multi]

mysqld=/data/app/mysql/bin/mysqld_safe
mysqladmin=/data/app/mysql/bin/mysqladmin
user=root

# The MySQL server
[mysqld1]
port            = 3307
socket          = /tmp/mysql.sock1
pid-file        = /data/app/mysql/var1/db-app1.pid
log             = /data/app/mysql/var1/db-app.log
datadir         = /data/app/mysql/var1
user            = mysql
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
log-bin=/data/app/mysql/var1/mysql1-bin
log-slow-queries
max_connections=250
server-id=1

[mysqld2]
port            = 3308
socket          = /tmp/mysql.sock2
pid-file        = /data/app/mysql/var2/db-app2.pid
log             = /data/app/mysql/var2/db-app.log
datadir         = /data/app/mysql/var2
user            = mysql
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
log-bin=/data/app/mysql/var2/mysql2-bin
log-slow-queries
max_connections=250
server-id=2

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

从server1:192.168.0.68
从server2:192.168.0.69
两个my.cnf基本一样。

[mysqld_multi]

mysqld=/data/app/mysql/bin/mysqld_safe
mysqladmin=/data/app/mysql/bin/mysqladmin
user=root

# The MySQL server
[mysqld1]
port            = 3307
socket          = /tmp/mysql.sock1
pid-file        = /data/app/mysql/var1/db-app1.pid
log             = /data/app/mysql/var1/db-app.log
datadir         = /data/app/mysql/var1
user            = mysql
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 16M
thread_concurrency = 8
log-slow-queries
max_connections=250
server-id = 3
master-host = 192.168.0.47
master-user = 'repl'
master-password = '123456'
master-port = 3307
report-host = 192.168.0.68
master-connect-retry = 30
log-bin
log-slave-updates
read-only

[mysqld2]
port            = 3308
socket          = /tmp/mysql.sock2
pid-file        = /data/app/mysql/var2/db-app2.pid
log             = /data/app/mysql/var2/db-app.log
datadir         = /data/app/mysql/var2
user            = mysql
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 16M
thread_concurrency = 8
log-slow-queries
max_connections=250
server-id = 4
master-host = 192.168.0.47
master-user = 'repl'
master-password = '123456'
master-port = 3308
report-host = 192.168.0.68
master-connect-retry = 30
log-bin
log-slave-updates
read-only

主从复制结构注意几点:

1。注意在主server上建立复制帐号的权限赋予,grant Repl_slave on *.* to replicationUser@%
2。注意从服务器拷贝主服务器的data目录;
3。注意启用log-bin,同时观察mysql日志中的replication部分;

发表评论