mysql多主一从数据库同步配置

这两天一直在配置mysql数据库的多主一从的配置。其实现的思路是利用一个数据库启动两个实例(通过mysqld_multi),而这两个实例指向同一个数据库文件目录。但是配置过程中需要注意很多地方。
首先,由于启用了innodb,所以在启动数据服务的时候,服务进程会对innodb数据文件ibdata1加锁,导致第二个实例一致报错,在报了N多个 错误之后,放弃读取数据文件。所以在第二个实例启动时无法读取innodb的表数据,导致这种方案无法同步两个都包含innodb表的镜像。尝试采用指定 数据文件的方式(innodb_data_file_path=ibdata2:98M:autoextend,ibdata2是把ibdata1复制获 得的,注意大小应该和现有的数据文件大小一致,为大于等于数据文件大小的最小整数),启动多个实例,仍然报错,只是错误信息变成了innodb的log文 件无法读取,因此继续设置innodb_log_group_home_dir=test-innodb-log,指定日志文件的存储位置(启动时,提示 无法找到日志文件,自动建立新的日志文件ib_logfile1,ib_logfile0)。启动成功(./bin/mysqld_multi –config-file=./var/my.cnf –mysqld=mysqld_safe start 1,2)。

另外不能重复的还有如下选项:

port     = 5507 
socket   = var/mysql.sock2 
pid-file= var/test-db2a.pid 

在配置同步镜像时,还有很多需要注意,由于是存在两个同步,很多配置不能采用默认设置,否则两个同步进程会读取同一个文件,互相干扰。有如下几个配置:
主服务器信息文件:master-info-file = test-master.info
relay日志文件:relay-log = test-relay-bin
relay日志索引文件:relay-log-index = test-relay-bin.index
relay日志信息文件:relay-log-info-file=test-relay-log.info

完整的配置文件如下:

[mysqld_multi] 
mysqld = /home/work/local/test/mysql/bin/mysqld_safe 
mysqladmin = /home/work/local/test/mysql/bin/mysqladmin 
 
 
# Here follows entries for some specific programs 
 
# server 1 
[mysqld1] 
port     = 5506 
socket   = /home/work/local/test/mysql/var/mysql.sock1 
skip-locking 
pid-file= /home/work/local/test/mysql/var/test-db1a.pid 
datadir = /home/work/local/test/mysql/var 
log=/home/work/local/test/mysql/var/test-db1.log 
user = mysql 
log-slow-queries=/home/work/local/test/mysql/var/slowquery1.log 
long_query_time = 2 
key_buffer = 256M 
max_allowed_packet = 1M 
read_buffer_size = 2M 
myisam_sort_buffer_size = 64M 
thread_cache = 32 
query_cache_size = 32M 
thread_concurrency = 2 
max_connections=500 
server-id     = 4 
master-host     = localhost 
master-user     =   replicuser 
master-password =   123456 
master-port   = 7890 
#report-host = test-db3 
master-connect-retry = 30 
#replicate-rewrite-db=from_name->to_name 
replicate-do-table = test.usertest 
master-info-file = test-master.info 
relay-log = relay1-relay-bin 
relay-log-index = relay1-relay-bin.index 
relay-log-info-file=relay1-relay-log.info 
innodb_data_file_path=ibdata1:98M:autoextend 
log-slave-updates 
# 
# binary logging - not required for slaves, but recommended 
log-bin 
 
# The MySQL server 2 
[mysqld2] 
port     = 5507 
socket   = /home/work/local/test/mysql/var/mysql.sock2 
skip-locking 
pid-file= /home/work/local/test/mysql/var/test-db2a.pid 
datadir = /home/work/local/test/mysql/var 
log=/home/work/local/test/mysql/var/test-db2.log 
user = root 
log-slow-queries=/home/work/local/test/mysql/var/slowquery2.log 
long_query_time = 2 
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=500 
server-id     = 5 
master-host     =   localhost 
master-user     =   replicuser 
master-password =   123456 
master-port     = 3336 
master-connect-retry=60 
replicate-do-table = test2.user2 
master-info-file = relay2-master.info 
relay-log = relay2-relay-bin 
relay-log-index = relay2-relay-bin.index 
innodb_data_file_path=ibdata2:98M:autoextend 
innodb_log_group_home_dir=relayt2-innodb-log/ 
relay-log-info-file=relay2-relay-log.info 
log-slave-updates 
# 
# binary logging - not required for slaves, but recommended 
log-bin 
 
[mysqldump] 
quick 
max_allowed_packet = 16M 

发表回复