mysql主从同步
master节点
配置文件:vim /etc/my.cnf
- 主从同步的关键配置选项:
- server-id=77 master的server-id和slave的server-id一定不能相同
- log-bin 二进制日志
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
skip_name_resolve=on
server-id=77
log-bin
master节点创建一个复制用户,并授权:
- 查看master二进制日志的位置:mysql> show master status;
mysql> grant replication slave on *.* to repluser@'192.168.38.%' identified by 'centos';
slave节点
配置文件:vim /etc/my.cnf
- 关键配置:
- read-only
- server-id=87 不能和master节点一样
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
skip_name_resolve=on
read-only
server-id=87
与master建立关联:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.38.57', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='centos7-57-bin.000001', MASTER_LOG_POS=333, MASTER_CONNECT_RETRY=10;
注意:
- MASTER_HOST='192.168.38.57
- MASTER_LOG_FILE=‘centos7-57-bin.000001’ 主节点的二进制日志文件名(尤其是默认的时候)
- MASTER_LOG_POS=333 二进制日志的位置
mysql> START SLAVE;
验证主从同步
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000006
Read_Master_Log_Pos: 120
Relay_Log_File: mysql.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: master-log.000006
Slave_IO_Running: Yes #这两个线程状态必须为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: 120
Relay_Log_Space: 447
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收到的bin-log到当前的同步位置差多少秒,不是严格意义的主从差多少秒,因为Master的bin-log更新以后可能还没有同步到Slave
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: 10
Master_UUID: 003e966d-de3e-11e6-bc0f-000c2944baf2
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
清除主从同步信息
- mysql>stop slave;
- mysql>reset slave all;
- mysql> show slave status\G