常见问题排查
1. 数据不一致:
- SHOW SLAVE STATUS \G;
- Last_Errno: 1062
- Last_Error: Error 'Duplicate entry'xxxn-66-77'for key1' on query. Default database: 'guild'. Query: 'insert into pynpcrecord setMapCode ='xxxn', UpdateTime ='2015-08-07 00:00:32''
解决方法: 在从库上将重复的主键记录删除, 再次重启主从
deletefrom xxxx where 主键 = yyyy;
stopslave;start slave;
解决方法: 停掉主从同步, 忽略一次错误, 再开启同步:
- stop slave;
- set global sql_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' identified by '123456';
- mysql> flush privileges;
B 上面:
- mysql> grant replication slave on *.* to 'repl'@'192.168.10.11' identified by '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' identified by 'pwd123';
- // 在 slave1 上登陆成功
- mysql> grant replication slave on *.* to 'affairlog'@'192.168.2.111' identified by '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
- 1 row in set (0.00 sec)
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: Waiting for master to send event
- 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:
- 1 row in set (0.00 sec)
如果两个 slave 中的 Slave_IO_Running,Slave_SQL_Running 状态均为 Yes 则表明设置成功.
来源: http://blog.51cto.com/13517254/2094803