MySQL主从复制
CentOS7下安装MySQL,参考:https://www.cnblogs.com/silentdoer/articles/7258232.html
环境配置:
Master操作:
1.接入mysql并创建主从复制的用户
create user m2ssync identified by 'Qq123!@#';
2.给新建的用户赋权
GRANT REPLICATION SLAVE ON *.* TO 'm2ssync'@'%' IDENTIFIED BY 'Qq123!@#';
3.指定服务ID, 开启binlog日志记录, 在/etc/my.cnf中加入
server-id=137
#开启binlog日志
log-bin=dbstore_binlog
#binlog日志记录只做db_store这个db
binlog-do-db=db_store
4.通过SHOW MASTER STATUS;查看Master db状态
Slave操作:
1.指定服务器ID, 指定同步的binlog存储位置, 在/etc/my.cnf中加入
server-id=101
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
read_only=1
#slave只处理db_store的事件
replicate_do_db=db_store
2.接入slave的mysql服务,
在mysql下执行命令
change master to master_host='192.168.8.137',
master_port=3306,
master_user='m2ssync',
master_password='Qq123!@#',
master_log_file='dbstore_binlog.000002',
master_log_pos=369;
#master_log_file、master_log_pos分别是master通过SHOW MASTER STATUS;查询出来的File、Position
3.start slave;
4. show slave status\G ;查看slave服务器状态
mysql> show slave status\G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.189.130
Master_User: m2ssync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dbstore_binlog.000002
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: dbstore_binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_store
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: 154
Relay_Log_Space: 532
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: 130
Master_UUID: 99c12a34-d9f2-11e8-97fc-000c29ebee73
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:
1 row in set (0.00 sec)
看到两个yes说明配置成功,此时在master机器db_store库操作数据,slave同步数据成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
参考文档:
https://www.cnblogs.com/silentdoer/articles/7258232.html
https://www.cnblogs.com/jirglt/p/3549047.html
https://blog.csdn.net/edwzhang/article/details/17226975