准备两台服务器,一主一从。
IP | 服务ID | 角色 |
---|---|---|
192.168.1.110 | 110 | master |
192.168.1.111 | 111 | slave |
Mysql主从软件版本尽量保持一致
> vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
#要给从库同步的库
binlog-do-db=rumenz
#不给从机同步的库(多个写多行)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ifnore-db=performance_schema
binlog-ignore-db=sys
log-error=/usr/local/mysql/data/error.log
#自动清理 7 天前的log文件,可根据需要修改
expire_logs_days=7
#启动binlog
log-bin=mysql-bin
服务器唯一ID,一般取IP最后一段,必填
server-id=110
> systemctl restart mysql
> grant file,replication slave on *.* to 'slave'@'%' identified by '123456';
> flush privileges;
> show master status;
img
将主库的所有数据拷贝一份,保证主从数据一致
> vim /etc.my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server-id=111
#加上以下参数可以避免更新不及时,SLAVE 重启后导致的主从复制出错。
read_only=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
log-error=/usr/local/mysql/data/error.log
> stop slave;
> change master to
> master_host='192.168.1.110',
> master_port=3306,
> master_user='slave', #备份用户名
> master_password='123456',#密码
> master_log_file='mysql3306-bin.000001', #上面截图,且要与master的参数一致
> master_log_pos=1155; #上面截图,且要与master的参数一致
> start slave;
master_log_file
和master_log_pos
的值要和查看主库的内容要一致,一个指的是二进制文件,一个指的是二进制日志的位置
show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000001
Read_Master_Log_Pos: 604
Relay_Log_File: BOTCHNFOSDAF06-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql3306-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: 604
Relay_Log_Space: 540
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: 110
Master_UUID: b7f6033a-c74e-11eb-a1ab-000c291b6073
Master_Info_File: mysql.slave_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:
1 row in set (0.00 sec)