mysql双主互备实现的配置笔记

机器一mysql配置,配置需注意偏移量,并做好授权。

port = 8808
server-id = 3
datadir = /home/mysql/data
log-error = /home/mysql/error.log
tmpdir = /home/mysql/tmp
log-bin = /home/mysql/data/mysql-bin
log-bin-index=/home/mysql/data/mysql-bin.index
binlog-do-db = test
#多个数据库多行
binlog-do-db = test2
max_binlog_size = 100M

#做为从服务器
log-slave-updates
sync_binlog=1
#为了避免两台服务器同时做更新时自增长字段的值之间发生冲突。一般在主主同步配置时,需要将两台服务器的auto_increment_increment增长量都配置为2,而要把auto_increment_offset分别配置为1和2.
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db = test
#多个数据库多行
replicate-do-db = test2


#config by shw
max_connections = 6000
table_open_cache = 3000
max_allowed_packet = 16M
max_heap_table_size = 128M
sort_buffer_size = 128M
join_buffer_size = 8M
query_cache_type = 1
query_cache_size = 1024M
query_cache_limit = 2M
slow_query_log = 1
slow_query_log_file = /home/mysql/slow.log
long_query_time = 2
innodb_purge_threads = 16
thread_cache_size = 256
innodb_read_io_threads=32
innodb_write_io_threads=32
#MyISAM config
key_buffer_size = 256M
read_buffer_size = 128M
read_rnd_buffer_size = 16M
myisam_max_sort_file_size = 50G
myisam_repair_threads = 4
#innodb config
innodb_buffer_pool_size = 15G
innodb_log_buffer_size = 64M
innodb_log_file_size = 32M
innodb_lock_wait_timeout = 60

机器二的mysql配置

port = 8808
server-id = 4
datadir = /home/mysql/data
log-error = /home/mysql/error.log
tmpdir = /home/mysql/tmp
log-bin = /home/mysql/data/mysql-bin
log-bin-index=/home/mysql/data/mysql-bin.index
replicate-do-db = test
#多个数据库多行
replicate-do-db = test2

max_binlog_size = 100M
#从服务器做主主互备
log-slave-updates
binlog-do-db = test
#多个数据库多行
binlog-do-db = test2

sync_binlog=1
#为了避免两台服务器同时做更新时自增长字段的值之间发生冲突。一般在主主同步配置时,需要将两台服务器的auto_increment_increment增长量都配置为2,而要把auto_increment_offset分别配置为1和2.
auto_increment_offset=2
auto_increment_increment=2


#config by shw
max_connections = 8000
table_open_cache = 3000
max_allowed_packet = 16M
max_heap_table_size = 128M
sort_buffer_size = 128M
join_buffer_size = 8M
query_cache_type = 1
query_cache_size = 1024M
query_cache_limit = 2M
slow_query_log = true
long_query_time = 2
slow_query_log_file = /home/mysql/slow.log
innodb_purge_threads = 16
thread_cache_size = 256
innodb_read_io_threads=32
innodb_write_io_threads=32
#MyISAM config
key_buffer_size = 256M
read_buffer_size = 128M
read_rnd_buffer_size = 16M
myisam_max_sort_file_size = 50G
myisam_repair_threads = 4
#innodb config
innodb_buffer_pool_size = 22G
innodb_log_buffer_size = 64M
innodb_log_file_size = 32M
innodb_lock_wait_timeout = 60

其它配置与测试过程

双主互备的原则,工作情况下,永远只是其中一台提供写入。

S1,S2
use test
create table `news` (id int primary key auto_increment,title varchar(200) not null,content varchar(2000) not null)default charset=utf8

S1创建一个给S2只读的用于同步的帐号

grant replication slave, replication client on *.* to 's2_salve'@'192.168.10.10' identified by 'pwd';
flush privileges;

S2 创建一个给S1只读的用于同步的帐号

grant replication slave, replication client on *.* to 's1_salve'@'192.168.10.9' identified by 'pwd';
flush privileges;

在s1 和 s2
S1========

s2 reset master;     show master status 

在s1上

change master to master_host='192.168.10.10',master_user='s1_salve', master_password='pwd', master_port=8808, master_log_file='mysql-bin.000001', master_log_pos=0, master_connect_retry=30;
start  slave;

show slave status\G;

错误一条: error connecting to master 's1_salve@192.168.10.10:8808' - retry-time: 30 retries: 1
S2检查防火墙

stop slave
start slave

查看状态OK
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

S2========
S1检查防火墙
在s2上

s1 reset master;     show master status 

change master to master_host='192.168.10.9',master_user='s2_salve', master_password='pwd', master_port=8808, master_log_file='mysql-bin.000005', master_log_pos=0, master_connect_retry=30;
start  slave;

查看状态OK

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

数据插入的测试

在s1 tets.news

insert into news(title,content)values('test1','test1 content');

在s2查看

同步 失败

在s2 test2.news插入数据

insert into news(title,content)values('title2','title2 content');

在s1上查看

同步失败

检查
S1 s2 清空表 truncate table news;
S1 s2 重置日志 reset master;

S1 show master status
FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
mysql-bin.000005120test.*
S2 show master status
FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
mysql-bin.000001120

重新配置主设置 偏移120

S1

stop slave
change master to master_host='192.168.10.10',master_user='s1_salve', master_password='pwd', master_port=8808, master_log_file='mysql-bin.000001', master_log_pos=120, master_connect_retry=30;
start  slave;

查看状态 正常

S2

stop slave
change master to master_host='192.168.10.9',master_user='s2_salve', master_password='pwd', master_port=8808, master_log_file='mysql-bin.000005', master_log_pos=120, master_connect_retry=30;
start  slave;

查看状态 正常

在s1 tets.news

insert into news(title,content)values('test1','test1 content');

在s2查看

同步 成功

在s2 test2.news插入数据

insert into news(title,content)values('title2','title2 content');

在s1上查看

同步成功


标签: mysql, 高可用

非特殊说明,本博所有文章均为博主原创。

最新文章

发表评论