mysql主丛之基于binlog的不停业务配置主从

一环境准备

主:192.168.132.121

从:192.168.132.122

主的数据库上面已经有数据,而且还在不断的写入

mysql> select * from darren.test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
mysql> grant replication slave on *.* to 'replication'@'192.168.132.122' identified by '1234567';
mysql> grant all on *.* to 'root'@'192.168.132.122' identified by '1234567';
mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

使用循环向表里写数据

[root@master mysql]# while true ; do mysql -uroot -p123456 -e 'use darren;insert into test values (1);'; sleep 1; done

二配置主从

2.1 导出数据的现有数据

[root@slave mysql]# cd /opt/

root@slave opt]# mysqldump -h192.168.132.121 -uroot -p1234567 --default-character-set=utf8 --databases darren --single-transaction --master-data=2 >daren.sql

[root@slave opt]# ll

-rw-r--r--. 1 root root 786 Jul 7 06:00 daren.sql

[root@slave opt]# cat daren.sql

-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: 192.168.132.121    Database: darren
-- ------------------------------------------------------
-- Server version    5.7.26-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=9883;    #chang master的信息,位置9883,同步需要从这里同步

--
-- Current Database: `darren`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `darren` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `darren`;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1),(2),(3),(4),(5),(6),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-07-07  6:09:21

由于一直在主写数据

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |    40445 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

2.2 把sql导入到备库

[root@slave opt]# mysql -uroot -p123456 --default-character-set=utf8 < daren.sql

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| darren             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from darren.test;

mysql主丛之基于binlog的不停业务配置主从

2.3 配置从端,同步数据

mysql> change master to master_host='192.168.132.121',master_port=3306,master_user='replication',master_password='1234567',master_log_file='master-bin.000002',master_log_pos=9883;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.132.121
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 40445
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 30883
        Relay_Master_Log_File: master-bin.000002
             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: 40445
              Relay_Log_Space: 31084
              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: 6b00724f-a094-11e9-8f47-000c2991dd19
             Master_Info_File: /data/mysql/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)

检验数据是否同步

192.168.132.121查看

mysql> select * from darren.test;

mysql主丛之基于binlog的不停业务配置主从

mysql> select * from darren.test;

mysql主丛之基于binlog的不停业务配置主从

已经同步

2.4 检验数据同步

mysql> create table darren.test1 (id int);
mysql> insert into darren.test1 values (5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from darren.test1;
+------+
| id   |
+------+
|    5 |
+------+
192.168.132.122查看
mysql> select * from darren.test1;
+------+
| id   |
+------+
|    5 |
+------+

在线不停业务配置主从同步数据成功