主庫ip:192.168.31.106

從庫ip:192.168.31.162

設置允許遠程連接：/etc/mysql/mysql.conf.d

注釋bind-address ? = 127.0.0.1

修改配置文件：/etc/mysql/my.cnf

主庫：

[mysqld]
server-id=106
log-bin=mysql-bin
binlog-ignore-db=mysql,information_schema

從庫：

[mysqld]
server-id=162
log-bin=mysql-bin
binlog-ignore-db=mysql,information_schema

運行命令：service mysql restart

主庫：

GRANT?REPLICATION?SLAVE?ON?*.*?TO?'replication'@'192.168.31.162'?IDENTIFIED?BY?'replication';

FLUSH PRIVILEGES;

主庫：SHOW MASTER STATUS;

mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000005 | 775 | | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec)

從庫：

?CHANGE MASTER TO MASTER_HOST='192.168.31.106', ?MASTER_USER='replication', MASTER_PASSWORD='replication', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=775;

mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.31.106Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 775Relay_Log_File: slave-relay-bin.000006Relay_Log_Pos: 941Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 775Relay_Log_Space: 1361Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 106Master_UUID: 95895513-4f41-11e7-9020-24b6fdf8107cMaster_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specifiedmysql>

?

總結

以上是生活随笔為你收集整理的Mysql5.7读写分离的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。