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