数据库的主从同步

数据库的主从同步

 

 

 

[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> grant replication slave on *.* to 'myslave'@'12.0.0.%' identified by '123456';	//赋权给myslave用户可以同步数据库
mysql> flush privileges; //刷新权限
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)

[root@slave1 ~]# yum -y install ntpdate ntp
[root@slave1 ~]# ntpdate 12.0.0.8	//同步主的时间
19 Jul 20:01:05 ntpdate[49763]: adjust time server 12.0.0.8 offset 0.000007 sec
[root@slave1 ~]# crontab -l		//设置定时任务每十分钟同步一次
*/10 * * * * /use/sbin/ntpdate 12.0.0.8

 

server-id = 22		//从服务器id，三台机器不能相同
log-bin=mysql-bin	//开启二进制日志
relay-log=relay-log-bin  	//开启中继日志
relay-log-index=slave-relay-bin.index	//定义中继日志的位置和名称

server-id = 23
log-bin=mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

 

mysql> change master to master_host='12.0.0.8',master_user='myslave',master_passsword='123456'master_log_file='mysql-bin.000001',master_log_pos=154;	//指定master的ip，用于同步数据的用户名和密码，同步的二进制日志和开始节点
mysql> start slave;		//开启从服务器

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 12.0.0.8
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1974
               Relay_Log_File: slave2-relay-bin.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主上插一条记录
mysql> insert into test values(1,'zhangsan');
Query OK, 1 row affected (0.10 sec)

查看两个备
mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)