mysql主从同步

步骤以及原理

1主库把更改记录在二进制日志中

2备库创建一个I/Oxian线程用于与主库通信，将主库日志复制到自己的中继日志（Relay_log）中

3备库通过sql线程读取中继日志中的事件，将其重放到备用库数据中

配置：

主库（IP：172.17.0.2）

1创建帐号用于同步(备库ip为172.17.0.3，帐号repl，密码123456)

mysql>grant replication slave,replication client on *.* to repl@'172.17.0.3' identified by '123456'

2修改配置文件，my.cnf，上面新加了log_bin(二进制日志)，server_id（mysql唯一的ID）

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql 
log_bin         = mysql-bin 
server_id       = 10
#log-error      = /var/log/mysql/error.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_type=OFF
sql_mode='ONLY_FULL_GROUP_BY'

2重启主库mysql服务service mysqld restart

3查看主库状态SHOW MASTER STATUS，二进制日志已经创建，默认与数据文件在同一目录

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	213			

 

备库（IP：172.17.0.3）：

1创建用于同步的帐号

mysql>grant replication slave,replication client on *.* to repl@'172.17.0.3' identified by '123456'

2修改配置文件my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-bin         = mysql-bin
server_id       = 11
relay_log       = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only       = 1
#log-error      = /var/log/mysql/error.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_type=OFF
sql_mode='ONLY_FULL_GROUP_BY'

这里分别设置了log-bin，server_id，relay_log（中继日志），log_slave_updates（允许备库将重放事件记录到二进制日志中，如果没有开启，万一配置错误导致备库数据被更改，将无法恢复数据），read_only开启（详情请查看我转发的博客“mysql slave read only的理解”）

3service mysqld restart重启备库

 

启用复制：

备库

mysql>CHANGE MASTER TO MASTER_HOST = '172.17.0.2',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;

在mysql上show  PROCESSLIST查看线程

Id	User	Host	db	Command	Time	State	Info
1	root	192.168.10.43:37296	\N	Query	0	init	show processlist
2	root	192.168.10.43:37297	\N	Sleep	1764		\N
4	system user		\N	Connect	66	Waiting for master to send event	\N
5	system user		\N	Connect	66	Slave has read all relay log; waiting for the slave I/O thread to update it	\N

可以看到的确启动了两个线程，I/O和sql线程用于复制

启动备库同步

mysql>start slave;

查看是否是否开启

mysql>SHOW SLAVE STATUS

Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert	Last_IO_Errno	Last_IO_Error	Last_SQL_Errno	Last_SQL_Error	Replicate_Ignore_Server_Ids	Master_Server_Id	Master_UUID	Master_Info_File	SQL_Delay	SQL_Remaining_Delay	Slave_SQL_Running_State	Master_Retry_Count	Master_Bind	Last_IO_Error_Timestamp	Last_SQL_Error_Timestamp	Master_SSL_Crl	Master_SSL_Crlpath	Retrieved_Gtid_Set	Executed_Gtid_Set	Auto_Position
Waiting for master to send event	172.17.0.2	repl	3306	60	mysql-bin.000001	213	mysql-relay-bin.000002	376	mysql-bin.000001	Yes	Yes							0		0	213	549	None		0	No						0	No	0		0			10	6ecf0b7d-63e4-11e8-a8e7-0242ac110004	/var/lib/mysql/master.info	0	\N	Slave has read all relay log; waiting for the slave I/O thread to update it	86400								0

可以看到

Slave_IO_Running    Slave_SQL_Running两个状态都为yes

在主库上新建数据库（如123）测试备库有没有同步