mysql主库强制生成binlog_mysql主丛之基于binlog的不停业务配置主从-CSDN博客

本文链接：https://blog.csdn.net/weixin_42346935/article/details/113443502

一环境准备

主: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, forLinux (x86_64)--

-- Host: 192.168.132.121Database: 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-timerecovery 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 fortable `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 fortable `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;+-------------------+----------+--------------+------------------+-------------------+

+-------------------+----------+--------------+------------------+-------------------+

| 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;

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.02sec)

mysql>start slave;

Query OK,0 rows affected (0.01sec)

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.132.121Master_User: replication

Master_Port:3306Connect_Retry:60Master_Log_File: master-bin.000002Read_Master_Log_Pos:40445Relay_Log_File: relay-log.000002Relay_Log_Pos:30883Relay_Master_Log_File: master-bin.000002Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:40445Relay_Log_Space:31084Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File: