MySQL 主从扩展
常见问题排查
1. 数据不一致:SHOW SLAVE STATUS \G;
Last_Errno:1062
Last_Error:Error'Duplicate entry'xxxn-66-77'for key1'on query.Defaultdatabase:'guild'.Query:'insert into pynpcrecord setMapCode ='xxxn', UpdateTime ='2015-08-0700:00:32''
解决方法: 在从库上将重复的主键记录删除, 再次重启主从
deletefrom xxxx where 主键 = yyyy;
stopslave;start slave;
解决方法: 停掉主从同步, 忽略一次错误, 再开启同步:stop slave;
setglobalsql_slave_skip_counter=1;startslave;
主主架构方案
1. 思路:
两台 mysql 都可读写, 互为主备, 默认只使用一台 (masterA) 负责数据的写入, 另一台 (masterB) 备用;
masterA 是 masterB 的主库, masterB 又是 masterA 的主库, 它们互为主从;
两台主库之间做高可用, 可以采用 keepalived 等方案(使用 VIP 对外提供服务);
所有提供服务的从服务器与 masterB 进行主从同步(双主多从);
建议采用高可用策略的时候, masterA 或 masterB 均不因宕机恢复后而抢占 VIP(非抢占模式);
2. 操作: 修改 my.cnf
修改 A 的[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
basedir=/usr/local/mysql
port=3306
socket=/tmp/mysql.sock
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
log-error=/usr/local/mysql/data/mysql.err
server-id=1
auto_increment_offset=1
auto_increment_increment=2#奇数 ID
log-bin=mysql-bin#打开二进制功能, MASTER 主服务器必须打开此项
binlog-format=ROW
binlog-row-p_w_picpath=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M#binlog 单文件最大值
replicate-ignore-db=mysql#忽略不同步主从的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=test
replicate-ignore-db=zabbix
max_connections=3000
max_connect_errors=30
skip-character-set-client-handshake#忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'#连接时执行的 SQL
character-set-server=utf8#服务端默认字符集
wait_timeout=1800#请求的最大连接时间
interactive_timeout=1800#和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#sql 模式
max_allowed_packet=10M
bulk_insert_buffer_size=8M
query_cache_type=1
query_cache_size=128M
query_cache_limit=4M
key_buffer_size=256M
read_buffer_size=16K
skip-name-resolve
slow_query_log=1
long_query_time=6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
[mysqldump]
quick
max_allowed_packet=16M
[mysqld_safe]
修改 B 的[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
basedir=/usr/local/mysql
port=3306
socket=/tmp/mysql.sock
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
log-error=/usr/local/mysql/data/mysql.err
server-id=2
auto_increment_offset=2
auto_increment_increment=2#偶数 ID
log-bin=mysql-bin#打开二进制功能, MASTER 主服务器必须打开此项
binlog-format=ROW
binlog-row-p_w_picpath=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M#binlog 单文件最大值
replicate-ignore-db=mysql#忽略不同步主从的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=test
replicate-ignore-db=zabbix
max_connections=3000
max_connect_errors=30
skip-character-set-client-handshake#忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'#连接时执行的 SQL
character-set-server=utf8#服务端默认字符集
wait_timeout=1800#请求的最大连接时间
interactive_timeout=1800#和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#sql 模式
max_allowed_packet=10M
bulk_insert_buffer_size=8M
query_cache_type=1
query_cache_size=128M
query_cache_limit=4M
key_buffer_size=256M
read_buffer_size=16K
skip-name-resolve
slow_query_log=1
long_query_time=6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
[mysqldump]
quick
max_allowed_packet=16M
[mysqld_safe]
3. 初始化:cd/usr/local/mysql
scripts/mysql_install_db--user=mysql
4.A 上面:mysql>grant replication slave on*.*to'repl'@'192.168.10.12'identifiedby'123456';
mysql>flush privileges;
B 上面:mysql>grant replication slave on*.*to'repl'@'192.168.10.11'identifiedby'123456';
mysql>flush privileges;
5. 配置同步信息:
A 上面mysql>change master to master_host='192.168.10.12',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=437;
mysql>start slave;
mysql>show slave status\G;
B 上面mysql>change master to master_host='192.168.10.11',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120;
start slave;
mysql>show slave status\G;
完成
一主多从
主要配置:
1. 修改 master 上的配置文件 my.cnf.
在 [mysqld] 下添加如下字段:server-id=1
log-bin=mysql-bin
binlog-do-db=YYY// 需要同步的数据库
binlog-ignore-db=mysql// 被忽略的数据库
binlog-ignore-db=information-schema// 被忽略的数据库
在 master 上为 slave 添加一个同步账号mysql>grant replication slave on*.*to'affairlog'@'192.168.2.182'identifiedby'pwd123';
// 在 slave1 上登陆成功
mysql>grant replication slave on*.*to'affairlog'@'192.168.2.111'identifiedby'pwd123';
// 在 slave2 上登陆成功
保存后, 重启 master 的 mysql 服务:
service mysql restart;
用 show master status 命令查看日志情况mysql>show master status\G;
***************************1.row***************************
File:mysql-bin.000087
Position:106
Binlog_Do_DB:YYY
Binlog_Ignore_DB:mysql,information-schema
1rowinset(0.00sec)
2. 修改 slave1 上的配置文件 my.cnf.
在 [mysqld] 下添加如下字段[root@mysql182~]#vi/etc/my.cnf
server-id=182
master-host=192.168.3.101
master-user=affairlog
master-password=pwd123
master-port=3306
master-connect-retry=60
replicate-do-db=YYY// 同步的数据库
replicate-ignore-db=mysql// 被忽略的数据库
replicate-ignore-db=information-schema// 被忽略的数据库
保存后, 重启 slave 的 mysql 服务:
service mysql restart;
修改 slave2 上的配置文件 my.cnf, 和上面类似, 只是把 server-id 改下, 为了方便, 我都用了相应的 ip 某位,
so,slave2 上我设置的 server-id 是 111.
在进入两个 slave 机中的 mysql.mysql>start slave;
mysql>show slave status\G;
***************************1.row***************************
Slave_IO_State:Waitingformaster to sendevent
Master_Host:192.168.3.101
Master_User:affairlog
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000087
Read_Master_Log_Pos:106
Relay_Log_File:vm111-relay-bin.000002
Relay_Log_Pos:251
Relay_Master_Log_File:mysql-bin.000087
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:YYY
Replicate_Ignore_DB:mysql,information-schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:106
Relay_Log_Space:406
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
1rowinset(0.00sec)
如果两个 slave 中的 Slave_IO_Running,Slave_SQL_Running 状态均为 Yes 则表明设置成功.
来源: http://blog.51cto.com/13517254/2094803