一 环境准备
主:192.168.132.121
从:192.168.132.122
主的数据库上面已经有数据,而且还在不断的写入
1mysql> select * from darren.test; 2+------+ 3| id | 4+------+ 5| 1 | 6| 2 | 7| 3 | 8| 4 | 9| 5 | 10| 6 | 11+------+ 12mysql> grant replication slave on *.* to 'replication'@'192.168.132.122' identified by '1234567'; 13mysql> grant all on *.* to 'root'@'192.168.132.122' identified by '1234567'; 14mysql> show variables like '%engine%'; 15+----------------------------------+--------+ 16| Variable_name | Value | 17+----------------------------------+--------+ 18| default_storage_engine | InnoDB | 19| default_tmp_storage_engine | InnoDB | 20| disabled_storage_engines | | 21| internal_tmp_disk_storage_engine | InnoDB | 22+----------------------------------+--------+
使用循环向表里写数据
[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
1-- MySQL dump 10.13 Distrib 5.7.26, for Linux (x86_64) 2-- 3-- Host: 192.168.132.121 Database: darren 4-- ------------------------------------------------------ 5-- Server version 5.7.26-log 6 7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 10/*!40101 SET NAMES utf8 */; 11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 12/*!40103 SET TIME_ZONE='+00:00' */; 13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 17 18-- 19-- Position to start replication or point-in-time recovery from 20-- 21 22-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=9883; #chang master的信息,位置9883,同步需要从这里同步 23 24-- 25-- Current Database: `darren` 26-- 27 28CREATE DATABASE /*!32312 IF NOT EXISTS*/ `darren` /*!40100 DEFAULT CHARACTER SET latin1 */; 29 30USE `darren`; 31 32-- 33-- Table structure for table `test` 34-- 35 36DROP TABLE IF EXISTS `test`; 37/*!40101 SET @saved_cs_client = @@character_set_client */; 38/*!40101 SET character_set_client = utf8 */; 39CREATE TABLE `test` ( 40 `id` int(11) DEFAULT NULL 41) ENGINE=InnoDB DEFAULT CHARSET=latin1; 42/*!40101 SET character_set_client = @saved_cs_client */; 43 44-- 45-- Dumping data for table `test` 46-- 47 48LOCK TABLES `test` WRITE; 49/*!40000 ALTER TABLE `test` DISABLE KEYS */; 50INSERT 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); 51/*!40000 ALTER TABLE `test` ENABLE KEYS */; 52UNLOCK TABLES; 53/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 54 55/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 56/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 57/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 58/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 59/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 60/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 61/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 62 63-- Dump completed on 2019-07-07 6:09:21
由于一直在主写数据
1mysql> show master status; 2+-------------------+----------+--------------+------------------+-------------------+ 3| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 4+-------------------+----------+--------------+------------------+-------------------+ 5| master-bin.000002 | 40445 | | | | 6+-------------------+----------+--------------+------------------+-------------------+
2.2 把sql导入到备库
[root@slave opt]# mysql -uroot -p123456 --default-character-set=utf8 < daren.sql
mysql> show databases;
1+--------------------+ 2| Database | 3+--------------------+ 4| information_schema | 5| darren | 6| mysql | 7| performance_schema | 8| sys | 9+--------------------+ 105 rows in set (0.00 sec)
mysql> select * from darren.test;
2.3 配置从端,同步数据
1mysql> 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; 2Query OK, 0 rows affected, 2 warnings (0.02 sec) 3 4mysql> start slave; 5Query OK, 0 rows affected (0.01 sec) 6 7mysql> show slave status\G; 8*************************** 1. row *************************** 9 Slave_IO_State: Waiting for master to send event 10 Master_Host: 192.168.132.121 11 Master_User: replication 12 Master_Port: 3306 13 Connect_Retry: 60 14 Master_Log_File: master-bin.000002 15 Read_Master_Log_Pos: 40445 16 Relay_Log_File: relay-log.000002 17 Relay_Log_Pos: 30883 18 Relay_Master_Log_File: master-bin.000002 19 Slave_IO_Running: Yes 20 Slave_SQL_Running: Yes 21 Replicate_Do_DB: 22 Replicate_Ignore_DB: 23 Replicate_Do_Table: 24 Replicate_Ignore_Table: 25 Replicate_Wild_Do_Table: 26 Replicate_Wild_Ignore_Table: 27 Last_Errno: 0 28 Last_Error: 29 Skip_Counter: 0 30 Exec_Master_Log_Pos: 40445 31 Relay_Log_Space: 31084 32 Until_Condition: None 33 Until_Log_File: 34 Until_Log_Pos: 0 35 Master_SSL_Allowed: No 36 Master_SSL_CA_File: 37 Master_SSL_CA_Path: 38 Master_SSL_Cert: 39 Master_SSL_Cipher: 40 Master_SSL_Key: 41 Seconds_Behind_Master: 0 42Master_SSL_Verify_Server_Cert: No 43 Last_IO_Errno: 0 44 Last_IO_Error: 45 Last_SQL_Errno: 0 46 Last_SQL_Error: 47 Replicate_Ignore_Server_Ids: 48 Master_Server_Id: 1 49 Master_UUID: 6b00724f-a094-11e9-8f47-000c2991dd19 50 Master_Info_File: /data/mysql/master.info 51 SQL_Delay: 0 52 SQL_Remaining_Delay: NULL 53 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 54 Master_Retry_Count: 86400 55 Master_Bind: 56 Last_IO_Error_Timestamp: 57 Last_SQL_Error_Timestamp: 58 Master_SSL_Crl: 59 Master_SSL_Crlpath: 60 Retrieved_Gtid_Set: 61 Executed_Gtid_Set: 62 Auto_Position: 0 63 Replicate_Rewrite_DB: 64 Channel_Name: 65 Master_TLS_Version: 661 row in set (0.00 sec)
检验数据是否同步
192.168.132.121查看
mysql> select * from darren.test;
mysql> select * from darren.test;
已经同步
2.4 检验数据同步
1mysql> create table darren.test1 (id int); 2mysql> insert into darren.test1 values (5); 3Query OK, 1 row affected (0.00 sec) 4 5mysql> select * from darren.test1; 6+------+ 7| id | 8+------+ 9| 5 | 10+------+ 11192.168.132.122查看 12mysql> select * from darren.test1; 13+------+ 14| id | 15+------+ 16| 5 | 17+------+
在线不停业务配置主从同步数据成功