主服务器配置 my.cnf
# binary logging is required for replication log-bin=mysql-binbinary logging format - mixed recommended
binlog_format=mixed
required unique id between 1 and 2^32 - 1
defaults to 1 if master-host is not set
but will not function as a master if omitted
server-id = 65
从服务器配置 my.cnf
server-id = 75 relay-log=mysql-relay
log_salve_update = on [可选 从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志,当从服务器作为其他服务器的主服务器]
bin_log = mysql-bin
read_only = 1 [可选 只读]
在主服务器建立授权账号
mysql> grant replication slave on *.* to 'slave75'@'10.16.134.75' identified by '123'; Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000014 | 354 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从服务器设置
mysql> change master to master_host='10.16.134.65', master_user='slave75', master_password='123', master_port=3306, master_log_file='mysql-bin.000014', master_log_pos=354; Query OK, 0 rows affected (0.15 sec)
Slave_SQL_Running: Yes
1.程序可能在slave上进行了写操作 2.也可能是slave机器重起后,事务回滚造成的. 一般是事务回滚造成的: 解决办法: mysql> slave stop; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> slave start; 解决办法二、 首先停掉Slave服务:slave stop 到主服务器上查看主机状态: 记录File和Position对应的值 进入master mysql> show master status; +----------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------------+----------+--------------+------------------+ | localhost-bin.000094 | 33622483 | | | +----------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 然后到slave服务器上执行手动同步: mysql> change master to > master_host='master_ip', > master_user='user', > master_password='pwd', > master_port=3306, > master_log_file=localhost-bin.000094', > master_log_pos=33622483 ; 1 row in set (0.00 sec) mysql> slave start;
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.16.134.65 Master_User: slave75 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 354 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 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: 354 Relay_Log_Space: 107 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 65 1 row in set (0.00 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave statusG
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 10.16.134.65
Master_User: slave75
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 354
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 354
Relay_Log_Space: 405
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 65
1 row in set (0.00 sec)
MySQL5.7之前,一个从库只能有一个主库,MySQL5.7之后支持一从多主架构
主从延迟的因素
主库写入二进制日志的时间 [ 控制主库的事务大小,分割大事务 ]
二进制日志传输时间 [ 使用MIXED日志格式, set binlog_row_image = minimal ]
MySQL57.配置多线程复制
stop slave;
set global slave_parallel_type = 'logical_clock';
set global slave_parallel_workers = 4;
start slave;