mysql 主从配置
分别安装主数据库和从数据库
##下载源安装包
wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
##安装mysql源
yum localinstall mysql57-community-release-el7-11.noarch.rpm
##检查 yum 源是否安装成功
yum repolist enabled|grep "mysql.*-community.*"
##安装服务
yum install mysql-community-server
##启动MySQL
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
修改密码之类步骤掠过
配置主数据库
vi /etc/my.cnf
[mysqld]
# 添加如下配置
# 参数必须唯一, 本例主库设置为 11 ,从库设置为 12
server_id=101
log_bin=/var/log/mysql/mysql-bin
记得检查日志目录是否存在,不存在子主动建立并授权
重启数据库
检查主库配置
mysql> show master;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
配置从库
vi /etc/my.cnf
[mysqld]
# 添加如下配置
# 参数必须唯一, 本例主库设置为 11 ,从库设置为 12
server_id=12
log_bin=/var/log/mysql/mysql-bin
重启数据库
进入从库数据库配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.136',
-> MASTER_USER='admin',
-> MASTER_PASSWORD='admin',
#此选项初始化设置时需要跟主库中的一致。设置好后,如果主
#库发生重启等,不需再次设置,从库会跟着更新
-> MASTER_LOG_FILE='mysql-bin.000001',
# master Position的值
-> MASTER_LOG_POS=154;
检查从库配置
mysql> show slave status \G;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.136
Master_User: admin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 307
Relay_Log_File: relay.000003
Relay_Log_Pos: 473
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: 307
Relay_Log_Space: 670
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: 101
Master_UUID: 22e13cbe-fc77-11e9-bb6f-000c2917f163
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: 3f897ce8-ea3b-11e9-adfc-9c5a4443c08a:1-2575
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
错误提示
The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON.
解决方案: 这个报错是因为主库和从库配置了不同的GTID
策略,修改相同即可。
启动从库
mysql> start slave;
效果演示
主库创建数据库hello2
,从库自动同步创建了hello2
从库数据库示意:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bjkfq |
| docker_mysql |
| hello2 |
| mqtt |
| mqtt2 |
| mqtt3 |
| my |
| mysql |
| performance_schema |
| sys |
+--------------------+
11 rows in set (0.00 sec)
重点难点总结
- 主库和从库都要配置
server_id
- 注意在主库中为从库设置远程访问账户和密码
over
阅读次数: 本文累计被阅读 1000000 次