本文详细介绍了如何进行MySQL主从同步，包括源主机新建数据库、目标主机导入数据结构、配置文件修改、用户创建、同步设置、历史数据迁移等步骤，确保数据在两台服务器间有效复制。
Mysql 主从同步
源主机：192.168.3.144
目标主机：192.168.3.145
1：源主机新建数据库
1.1：新建数据库
mysql> create database test;
1.2：新建数据表
mysql> CREATE TABLE IF NOT EXISTS `test`(
`id` INT UNSIGNED AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.3：导出数据结构
mysqldump -u root -p -d test > test.sql
1.4：拷贝文件到目标主机
scp test.sql root@192.168.3.145:/root/sql/
2：目标主机导入数据结构
2.1：新建数据库
mysql> create database test;
2.2：导入数据结构
mysql -u root -p test < test.sql
3：修改mysql配置文件
3.1：修改源主机mysql文件：
[root@centos7-1 ~]# vim /etc/my.cnf
server-id = 1 #这里可以不用修改，修改目标服务器的即可，主要两台主机的server-id不同即可。
#这里是不需要同步的数据库
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
3.2：修改目标主机mysql文件
[root@centos7-2 ~]# vim /etc/my.cnf
server-id = 2 #修改与源主机不同即可。
4：创建用户
4.1：源主机创建同步账户
mysql> CREATE USER 'repl'@'192.168.3.145' IDENTIFIED BY 'repl';
mysql> grant all privileges on *.* to 'repl'@'192.168.3.145' identified by 'repl' with grant option;
4.2：查看源主机master status
这里要记住两个地方
File ：mysql-bin.000008
Position ：1175916
后面要用到，实际情况要根据自己的信息进行更改
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000008 | 1175916 | | mysql,information_schema,performance_schema | |
+------------------+----------+--------------+---------------------------------------------+-------------------+
5：设置同步
5.1：设置slave同步master信息
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.3.144',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=1175916;
5.2：查看slave状态
只需要查看这两个状态是否YES即可
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.144
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 1176221
Relay_Log_File: centos7-2-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000008
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: 1176221
Relay_Log_Space: 460
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: 3e92e1fa-e648-11ec-907d-000c29ea7c36
Master_Info_File: /usr/local/mysql/var/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
5.3：测试同步
在源主机插入数据，看看目标主机是否有数据了。
6：导出旧数据
6.1：导出表历史数据
mysql同步完成后，那源主机的数据库的数据表中还是有之前的历史数据，那么这样的数据如果要迁移到目标主机，只能用导出导入的方式了。
mysqldump -u root -p test test_tables -w "id<192" > 1.sql
这里指的是，我们导出test数据库中test_tables表中的id小于192字段的数据，因为我们是从id=193字段开始同步的，所以我们只需要导出id是193之前的数据即可。
6.2：复制数据到目标主机
scp /root/sql/1.sql root@192.168.3.145:/root/sql
7：导入历史数据
7.1：修改sql文件
现在我们目标主机的test数据库中的test_tables表中是只有id从193开始的数据
mysql> select * from test_tables;
+-----------+--------------+---------------+-----------------+
| id | title | author | submission_date |
+-----------+--------------+---------------+-----------------+
| 193 | 学习Mysql | 猫和大叔 | 2022-06-23 |
| 194 | 学习Mysql | 猫和大叔 | 2022-06-23 |
| 195 | 学习Mysql | 猫和大叔 | 2022-06-23 |
| 196 | 学习Mysql | 猫和大叔 | 2022-06-23 |
+-----------+--------------+---------------+-----------------+
但是如果我们直接导入1.sql文件，那么1.sql的文件内容会覆盖test_tables中的所有数据，也就是相当于新的数据都被清空了，test_tables只有导入的旧数据了。
为了避免导入数据后数据会被覆盖，那么我们要修改下1.sql的内容m，大概意思就是把删除test_tables表的字段注释掉，把创建test_tables表的字段注释掉，其他就无须修改了：
DROP TABLE IF EXISTS test_tables;
CREATE TABLE test_tables (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title varchar(100) NOT NULL,
author varchar(40) NOT NULL,
submission_date date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=195 DEFAULT CHARSET=utf8;
[root@centos7-2 ~]# vim 123.sql
-- MySQL dump 10.13 Distrib 5.6.51, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.51-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 */;
--
-- Table structure for table `t`
--
DROP TABLE IF EXISTS `test_tables`; #这里注释掉：/* */
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_tables` ( #从这里开始注释
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`author` varchar(40) NOT NULL,
`submission_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=195 DEFAULT CHARSET=utf8; #这里注释结束
.............
7.2：导入数据
[root@centos7-2 ~]# mysql -u root -p test < 1.sql
这样新的数据也没有被覆盖，旧的数据也被导入进去了。
617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
