MySQL Master-slave replication The circumstances of the case:
| Host |
Operating System |
IP Address |
main software |
| Primary server |
CentOS 7.3 x86_64 |
192.168.217.130 |
Ntp |
| From the server |
CentOS 7.3 x86_64 |
192.168.217.131 |
Ntpdate |
Case implementation: Configure the primary server:
- Set up the time synchronization service on the primary server to install NTP:
yum install ntp -y
- To configure NTP:
vim /etc/ntp.conf #编辑配置文件 添加server 127.127.217.0 #设置本地是时钟源fudge 127.127.217.0 stratum 8 #设置时间层级为8(限制在15内)#127.127 ntp时间服务器的保留ip地址,作用是使用本机作为客户端的时间服务器
- Open service:
systemctl start ntpd.service systemctl stop firewalld.service setenforce 0
- To configure the MySQL configuration file:
vim /etc/my.cnf #在配置文件添加[mysqld] server-id = 11 #修改 ID 主从不一样log-bin=master-bin #开启二进制日志log-slave-updates=true #开启从服务器同步主服务器二进制日志
- Restart the service and enter the database to authorize from the server:
mysql -u root -pmysql>GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.217.%‘ IDENTIFIED BY ‘123456‘;#允许从服务器使用 myslave账户 登录数据库 mysql> FLUSH PRIVILEGES; #刷新权限mysql> show master status; #查看主服务器状态+-------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 | 604 | | | |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
Remember the binary log file name and offset of the primary server.
To configure the slave server:
- Install ntpdate sync Time:
yum install ntpdate -y #从服务器上安装的 [root[email protected] ~]# systemctl start ntpdate.service #开启服务[[email protected] ~]# /usr/sbin/ntpdate 192.168.217.130 #时间同步6 Jul 09:45:44 ntpdate[2941]: adjust time server 192.168.217.130 offset -0.233224 sec #同步成功systemctl stop firewalld.service setenforce 0
- To configure the MySQL configuration file:
vim /etc/my.cnf[mysqld] server-id = 12 #id 不一样relay-log=relay-log-bin #开启中继日志relay-log-index=slvae-relay-bin.index #指定位置和名称
- Restart the service and go to MySQL:
mysql -u root -pmysql> change master to master_host=‘192.168.217.130‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=604; # 主服务器地址、授权用户、二进制日志名称、偏移量 。mysql> start slave; # 开启从服务器mysql> show slave status\G; #查看从服务器状态*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.217.130 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 604 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes # 两个为yes 表示同步中 Slave_SQL_Running: Yes
Synchronization test:
- To create a database on the primary server:
mysql> create database test;
- On the From server view:
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || jdy || mysql || performance_schema || sys || test | # 同步成功+--------------------+6 rows in set (0.12 sec)
PS: Data synchronization synchronizes only the libraries or tables that were created or modified after the database was synchronized. PS: It must not be wrong to refer to the primary server binary log name and offset from the server. PS: Build more than one from the server only need to change the ID is different. PS: The above is the operation after the database is installed.
MySQL Master-slave replication