Hello!各位小伙伴大家好,今天给大家分享一下MySQL主从复制配置的具体步骤,有错误的地方还请各位小伙伴评论区指正!
主库配置
- 进入/etc/my.cnf文件中,添加如下内容
log-bin=mysql-bin #启用二进制日志
server-id=101 #id作为服务器唯一标识,不一定要100,只要不重复即可
- 重启mysql服务
systemctl restart mysqld
- 登录进入到mysql当中
- 创建一个用户叫xiaoming,密码是Root@123456,并给该用户授予REPLICATION SLAVE权限
GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123456';
- 查看主库的状态
show master status;
从库配置(另外开一个虚拟机)
- 进入到/etc/my.cnf文件中,添加如下内容
server-id=102
- 重启mysql服务
systemctl restart mysqld;
- 登录进入到mysql当中
- 执行如下内容(注意,引号当中的内容改为自己的)
change master to master_host='192.168.137.101',master_user='xiaoming',master_password='Root@123456',master_log_file='mysql-bin.000001',master_log_pos=433;
- 启动从库
start slave;
- 查看从库状态
show slave status\G
如果出现Slave_IO_Running: Yes,Slave_SQL_Running: Yes,那么配置成功
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.137.101
Master_User: xiaoming
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 685
Relay_Log_File: hadoop102-relay-bin.000002
Relay_Log_Pos: 572
Relay_Master_Log_File: mysql-bin.000001
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: 685
Relay_Log_Space: 783
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: 101
Master_UUID: 9e20e5a4-b804-11ed-8c7f-000c2949bacf
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
原理
- master将改变记录到二进制日志(binary log)
- 从库连接主库
- 从库创建一个I/O线程向主库请求更新的binlog
- 主库会创建一个binlog dump线程来发送binlog,从库中的I/O线程负责接收
- slave的I/O线程将master的binary log拷贝到它的中继日志(relay log)
- slave 重做中继日志中的事件,将改变应用到自己的数据库中,也就是再执行一遍SQL