1.master配置
1.1在MySQL配置文件/etc/my.cnf中的[mysqld]一节中加入以下语句
[mysqld]
log-bin=mysql-bin #开启二进制日志并命名为mysql-bin
server-id=1 #服务器id,主从服务器组内编号唯一
1.2重启MySQL数据库登陆
systemctl restart mysqld
mysql-u root -p
1.3查看主机状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
1.4创建账户授权
#任何主机访问数据的权限注意如果使用的是MySQL8,需要如下的方式建立账户,并授权slave
CREATE USER 'slave'@'%' IDENTIFIED BY 'Slave_123';
说明:
- MySQL创建用户命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
- username:指定创建的用户名
- host:指定用户登录的主机ip,% 表示任意主机都可远程登录,localhost 表示本地才能登录
- password:指定该用户的登录密码
1.5给创建的用户授权同步复制权
#接着给创建的用户授权同步复制权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
#刷新权限
flush privileges;
说明:
- MySQL创建授权命令:GRANT REPLICATION SLAVE ON database.table TO 'username'@'host';
- privilege :指定授权的权限,比如create、drop等权限,具体有哪些权限,可查看官网文档
- database:指定哪些数据库生效,*表示全部数据库生效
- table:指定所在数据库的哪些数据表生效,*表示所在数据库的全部数据表生效
- username:指定被授予权限的用户名
- host:指定用户登录的主机ip,%表示任意主机都可远程登录
2.slave配置
2.1修改配置文件
[mysqld]
log-bin=mysql-bin
server-id=2
2.2配置从机
CHANGE MASTER TO MASTER_HOST='48.4.124.71',MASTER_PORT=3306,MASTER_USER='slave', MASTER_PASSWORD='Slave_123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=330205 , get_master_public_key=1;
说明
- MASTER_HOST='48.4.124.71' #主机ip
- MASTER_PORT=3306 #主机端口
- MASTER_USER='slave' #主机创建的复制账号
- MASTER_PASSWORD='Slave_123' #主机创建的复制账号密码
- MASTER_LOG_FILE='mysql-bin.000001' #主机偏移量
- MASTER_LOG_POS=330205 #主机偏移量
- get_master_public_key=1; #密码策略设置
2.3开启/关闭从机
START SLAVE;
STOP SLAVE;
3.验证
3.1查看从机状态重点观察Slave_IO_Running: Yes Slave_SQL_Running: Yes
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 48.4.124.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 330205
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 326
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: 330205
Relay_Log_Space: 534
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: 3
Master_UUID: 33982a36-c8be-11ee-a195-fa163eef4717
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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_public_key_path:
Get_master_public_key: 1
Network_Namespace:
3.2在master创建test、test1 数据库成功同步到slave
master
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mldong-plus |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)
slave
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+--------------------+
6 rows in set (0.00 sec)