测试 MySQL 主从配置: 实现一主一从读写分离

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

a, 创建一个用户 repl, 密码

MySQL> create user 'repl'@'192.168.1.201' identified by '123456';
Query OK, 0 rows affected (5.50 sec)

b, 授予从服务器 192.168.1.201 复制权限

MySQL> grant replication slave on . to 'repl'@'192.168.1.200';
Query OK, 0 rows affected (0.04 sec)

c, 授予刷新权限

MySQL> flush privileges;
Query OK, 0 rows affected (0.09 sec)

(5) 配置主数据库编辑配置文件

[[email protected] ~]# VIM /etc/my.cnf
[[email protected] ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/MySQL ### 基本路径
datadir = /opt/data ### 数据目录
socket = /tmp/MySQL.sock ### 接口路径
port = 3306 ### 端口
pid-file = /opt/data/MySQL.pid ##pid 文件路径
user = MySQL
skip-name-resolve
// 添加以下内容
log-bin=MySQL-bin // 启用 binlog 日志
server-id=1 // 主数据库服务器唯一标识符 主的必须必比从大
log-error=/opt/data/MySQL.log // 错误日志路径

(6) 重启 MySQL 服务

[[email protected] ~]# service mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL.Logging to '/opt/data/mysql.log'.
............................... SUCCESS!

(7) 列出所有打开的网络连接端口

[[email protected] ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 :22:LISTEN 0 100 127.0.0.1:25:LISTEN 0 128 :::22 :::
LISTEN 0 100 ::1:25 :::LISTEN 0 80 :::3306 :::

(8) 查看主库的状态

MySQL> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| MySQL-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

(9) 配置从数据库

编辑配置文件

[[email protected] ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/MySQL
datadir = /opt/data
socket = /tmp/MySQL.sock
port = 3306
pid-file = /opt/data/MySQL.pid
user = MySQL
skip-name-resolve
// 添加以下内容:
server-id=2 // 设置从库的唯一标识符 从的必须比主小
relay-log=MySQL-relay-bin // 启用中继日志 relay log
error-log=/opt/data/MySQL.log

(10) 重启从库的 MySQL 服务

[[email protected] ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[[email protected] ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 :22:LISTEN 0 100 127.0.0.1:25:LISTEN 0 128 :::22 :::
LISTEN 0 100 ::1:25 :::LISTEN 0 80 :::3306 :::

(11) 配置并启动主从复制

MySQL> change master to
-> master_host='192.168.1.200',
-> master_user='repl',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.28 sec)

(13) 查看从服务器状态

MySQL> show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.55.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MySQL-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: MySQL-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: MySQL-bin.000001
Slave_IO_Running: Yes // 此处必须是 yes
Slave_SQL_Running: Yes // 此处必须是 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: 154
Relay_Log_Space: 527
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: 1
Master_UUID: 5abf1791-b2af-11e8-b6ad-000c2980fbb4
Master_Info_File: /opt/data/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)

来源: http://www.bubuko.com/infodetail-3198435.html