一.背景
有个比较小的mysql数据库，需要迁移到新的服务器上，环境和要求如下：
1.环境
windows server 2012 r2
mysql 5.6.21 社区版 单节点
PS C:\Users\Administrator> mysql -V C:\MySQL\MySQL 5.6\bin\mysql.exe Ver 14.14 Distrib 5.6.21, for Win64 (x86_64)
| 主库ip | 备库ip |
|---|---|
| 192.168.59.12 | 192.168.59.13 |
2.要求
应用不修改ip地址
停机时间尽量短。
二.方案
准备利用最简单的mysql 主从复制 异步复制来进行。
1.新服务器安装操作系统和数据库
2.先搭建主从复制。
3.停止应用服务。
6.新旧服务器更换ip。
7.重新配置主从复制信息并验证数据同步。
8.启动业务。
三.安装操作系统和数据库
3.1.安装操作系统
省略
3.2.安装数据库
1.下载
https://downloads.mysql.com/archives/community/
我选择的是免安装压缩包。
2.安装
安装mysql数据库，可以参考如下文档： refman-5.6-en.pdf
mysql 5.6文档
第一步：解压缩
第二步：配置参数文件
[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
# Path to the database root
datadir=C:\MySQL\MySQL data
character-set-server=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="WIN-B8GASUCFD4M.log"
slow-query-log=1
slow_query_log_file="WIN-B8GASUCFD4M-slow.log"
long_query_time=10
# Binary Logging.
# log-bin
log-bin=C:\\MySQL\\MySQL data\\mysql_logbin.log
binlog-format=row
expire_logs_days=14
max_binlog_size=1024M
# Error Logging.
log-error="WIN-B8GASUCFD4M.err"
# Server Id.
server-id=1
第三步：安装服务
首先拷贝把解压缩之后的data目录内容拷贝到自定米的数据目录。
配置环境变量：C:\MySQL\MySQL 5.6\bin
第四步：启动服务
第五步：登录数据库
初始密码为空
PS C:\Users\Administrator> mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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>
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
mysql>
mysql> create user 'root'@'%' identified by 'clear123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> set password for 'root'@'localhost' =password('clear123');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)
mysql>
备库按照同样的方式进行安装，my.ini中server-id修改为2就可以了。
四.搭建主从复制
非gtid模式，基于binlog的异步复制，前面参数文件也能看出来，没有开启gtid。
4.1.主库创建测试数据库（正式环境肯定就不用了）
-- =============================================
-- MySQL测试数据生成脚本
-- 包含Oracle Scott用户表结构及中文表
-- =============================================
-- 创建数据库test
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test;
-- 创建EMP表(员工表)及其中文表EMP_ZH
CREATE TABLE IF NOT EXISTS emp (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';
CREATE TABLE IF NOT EXISTS emp_zh (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工中文表';
-- 创建DEPT表(部门表)及其中文表DEPT_ZH
CREATE TABLE IF NOT EXISTS dept (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
CREATE TABLE IF NOT EXISTS dept_zh (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门中文表';
-- 创建SALGRADE表(薪资等级表)及其中文表SALGRADE_ZH
CREATE TABLE IF NOT EXISTS salgrade (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级表';
CREATE TABLE IF NOT EXISTS salgrade_zh (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级中文表';
-- 创建BONUS表(奖金表)及其中文表BONUS_ZH
CREATE TABLE IF NOT EXISTS bonus (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金表';
CREATE TABLE IF NOT EXISTS bonus_zh (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金中文表';
-- 插入DEPT表数据
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
-- 插入DEPT_ZH表数据(中文)
INSERT INTO dept_zh (deptno, dname, loc) VALUES
(10, '会计部', '纽约'),
(20, '研发部', '达拉斯'),
(30, '销售部', '芝加哥'),
(40, '运营部', '波士顿');
-- 插入EMP表数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入EMP_ZH表数据(中文)
INSERT INTO emp_zh (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, '史密斯', '职员', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, '艾伦', '销售员', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, '沃德', '销售员', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, '琼斯', '经理', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, '马丁', '销售员', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, '布莱克', '经理', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, '克拉克', '经理', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, '斯科特', '分析师', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, '金', '总裁', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, '特纳', '销售员', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, '亚当斯', '职员', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, '詹姆斯', '职员', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, '福特', '分析师', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, '米勒', '职员', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入SALGRADE表数据
INSERT INTO salgrade (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入SALGRADE_ZH表数据(中文)
INSERT INTO salgrade_zh (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入BONUS表数据
INSERT INTO bonus (ename, job, sal, comm) VALUES
('SMITH', 'CLERK', 800, 200),
('ALLEN', 'SALESMAN', 1600, 300),
('WARD', 'SALESMAN', 1250, 500),
('JONES', 'MANAGER', 2975, 200),
('MARTIN', 'SALESMAN', 1250, 1400),
('BLAKE', 'MANAGER', 2850, 200),
('CLARK', 'MANAGER', 2450, 200),
('SCOTT', 'ANALYST', 3000, 200),
('KING', 'PRESIDENT', 5000, 500),
('TURNER', 'SALESMAN', 1500, 0),
('ADAMS', 'CLERK', 1100, 200),
('JAMES', 'CLERK', 950, 200),
('FORD', 'ANALYST', 3000, 200),
('MILLER', 'CLERK', 1300, 200);
-- 插入BONUS_ZH表数据(中文)
INSERT INTO bonus_zh (ename, job, sal, comm) VALUES
('史密斯', '职员', 800, 200),
('艾伦', '销售员', 1600, 300),
('沃德', '销售员', 1250, 500),
('琼斯', '经理', 2975, 200),
('马丁', '销售员', 1250, 1400),
('布莱克', '经理', 2850, 200),
('克拉克', '经理', 2450, 200),
('斯科特', '分析师', 3000, 200),
('金', '总裁', 5000, 500),
('特纳', '销售员', 1500, 0),
('亚当斯', '职员', 1100, 200),
('詹姆斯', '职员', 950, 200),
('福特', '分析师', 3000, 200),
('米勒', '职员', 1300, 200);
-- =============================================
-- 创建数据库test1并重复相同结构
-- =============================================
CREATE DATABASE IF NOT EXISTS test1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test1;
-- 创建EMP表(员工表)及其中文表EMP_ZH
CREATE TABLE IF NOT EXISTS emp (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';
CREATE TABLE IF NOT EXISTS emp_zh (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工中文表';
-- 创建DEPT表(部门表)及其中文表DEPT_ZH
CREATE TABLE IF NOT EXISTS dept (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
CREATE TABLE IF NOT EXISTS dept_zh (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门中文表';
-- 创建SALGRADE表(薪资等级表)及其中文表SALGRADE_ZH
CREATE TABLE IF NOT EXISTS salgrade (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级表';
CREATE TABLE IF NOT EXISTS salgrade_zh (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级中文表';
-- 创建BONUS表(奖金表)及其中文表BONUS_ZH
CREATE TABLE IF NOT EXISTS bonus (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金表';
CREATE TABLE IF NOT EXISTS bonus_zh (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金中文表';
-- 插入DEPT表数据
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
-- 插入DEPT_ZH表数据(中文)
INSERT INTO dept_zh (deptno, dname, loc) VALUES
(10, '会计部', '纽约'),
(20, '研发部', '达拉斯'),
(30, '销售部', '芝加哥'),
(40, '运营部', '波士顿');
-- 插入EMP表数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入EMP_ZH表数据(中文)
INSERT INTO emp_zh (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, '史密斯', '职员', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, '艾伦', '销售员', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, '沃德', '销售员', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, '琼斯', '经理', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, '马丁', '销售员', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, '布莱克', '经理', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, '克拉克', '经理', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, '斯科特', '分析师', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, '金', '总裁', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, '特纳', '销售员', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, '亚当斯', '职员', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, '詹姆斯', '职员', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, '福特', '分析师', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, '米勒', '职员', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入SALGRADE表数据
INSERT INTO salgrade (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入SALGRADE_ZH表数据(中文)
INSERT INTO salgrade_zh (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入BONUS表数据
INSERT INTO bonus (ename, job, sal, comm) VALUES
('SMITH', 'CLERK', 800, 200),
('ALLEN', 'SALESMAN', 1600, 300),
('WARD', 'SALESMAN', 1250, 500),
('JONES', 'MANAGER', 2975, 200),
('MARTIN', 'SALESMAN', 1250, 1400),
('BLAKE', 'MANAGER', 2850, 200),
('CLARK', 'MANAGER', 2450, 200),
('SCOTT', 'ANALYST', 3000, 200),
('KING', 'PRESIDENT', 5000, 500),
('TURNER', 'SALESMAN', 1500, 0),
('ADAMS', 'CLERK', 1100, 200),
('JAMES', 'CLERK', 950, 200),
('FORD', 'ANALYST', 3000, 200),
('MILLER', 'CLERK', 1300, 200);
-- 插入BONUS_ZH表数据(中文)
INSERT INTO bonus_zh (ename, job, sal, comm) VALUES
('史密斯', '职员', 800, 200),
('艾伦', '销售员', 1600, 300),
('沃德', '销售员', 1250, 500),
('琼斯', '经理', 2975, 200),
('马丁', '销售员', 1250, 1400),
('布莱克', '经理', 2850, 200),
('克拉克', '经理', 2450, 200),
('斯科特', '分析师', 3000, 200),
('金', '总裁', 5000, 500),
('特纳', '销售员', 1500, 0),
('亚当斯', '职员', 1100, 200),
('詹姆斯', '职员', 950, 200),
('福特', '分析师', 3000, 200),
('米勒', '职员', 1300, 200);
-- =============================================
-- 创建数据库test2并重复相同结构
-- =============================================
CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test2;
-- 创建EMP表(员工表)及其中文表EMP_ZH
CREATE TABLE IF NOT EXISTS emp (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';
CREATE TABLE IF NOT EXISTS emp_zh (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工中文表';
-- 创建DEPT表(部门表)及其中文表DEPT_ZH
CREATE TABLE IF NOT EXISTS dept (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
CREATE TABLE IF NOT EXISTS dept_zh (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门中文表';
-- 创建SALGRADE表(薪资等级表)及其中文表SALGRADE_ZH
CREATE TABLE IF NOT EXISTS salgrade (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级表';
CREATE TABLE IF NOT EXISTS salgrade_zh (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级中文表';
-- 创建BONUS表(奖金表)及其中文表BONUS_ZH
CREATE TABLE IF NOT EXISTS bonus (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金表';
CREATE TABLE IF NOT EXISTS bonus_zh (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金中文表';
-- 插入DEPT表数据
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
-- 插入DEPT_ZH表数据(中文)
INSERT INTO dept_zh (deptno, dname, loc) VALUES
(10, '会计部', '纽约'),
(20, '研发部', '达拉斯'),
(30, '销售部', '芝加哥'),
(40, '运营部', '波士顿');
-- 插入EMP表数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入EMP_ZH表数据(中文)
INSERT INTO emp_zh (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, '史密斯', '职员', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, '艾伦', '销售员', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, '沃德', '销售员', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, '琼斯', '经理', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, '马丁', '销售员', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, '布莱克', '经理', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, '克拉克', '经理', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, '斯科特', '分析师', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, '金', '总裁', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, '特纳', '销售员', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, '亚当斯', '职员', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, '詹姆斯', '职员', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, '福特', '分析师', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, '米勒', '职员', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入SALGRADE表数据
INSERT INTO salgrade (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入SALGRADE_ZH表数据(中文)
INSERT INTO salgrade_zh (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入BONUS表数据
INSERT INTO bonus (ename, job, sal, comm) VALUES
('SMITH', 'CLERK', 800, 200),
('ALLEN', 'SALESMAN', 1600, 300),
('WARD', 'SALESMAN', 1250, 500),
('JONES', 'MANAGER', 2975, 200),
('MARTIN', 'SALESMAN', 1250, 1400),
('BLAKE', 'MANAGER', 2850, 200),
('CLARK', 'MANAGER', 2450, 200),
('SCOTT', 'ANALYST', 3000, 200),
('KING', 'PRESIDENT', 5000, 500),
('TURNER', 'SALESMAN', 1500, 0),
('ADAMS', 'CLERK', 1100, 200),
('JAMES', 'CLERK', 950, 200),
('FORD', 'ANALYST', 3000, 200),
('MILLER', 'CLERK', 1300, 200);
-- 插入BONUS_ZH表数据(中文)
INSERT INTO bonus_zh (ename, job, sal, comm) VALUES
('史密斯', '职员', 800, 200),
('艾伦', '销售员', 1600, 300),
('沃德', '销售员', 1250, 500),
('琼斯', '经理', 2975, 200),
('马丁', '销售员', 1250, 1400),
('布莱克', '经理', 2850, 200),
('克拉克', '经理', 2450, 200),
('斯科特', '分析师', 3000, 200),
('金', '总裁', 5000, 500),
('特纳', '销售员', 1500, 0),
('亚当斯', '职员', 1100, 200),
('詹姆斯', '职员', 950, 200),
('福特', '分析师', 3000, 200),
('米勒', '职员', 1300, 200);
-- =============================================
-- 创建数据库test3并重复相同结构
-- =============================================
CREATE DATABASE IF NOT EXISTS test3 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test3;
-- 创建EMP表(员工表)及其中文表EMP_ZH
CREATE TABLE IF NOT EXISTS emp (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';
CREATE TABLE IF NOT EXISTS emp_zh (
empno INT(4) NOT NULL COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
mgr INT(4) COMMENT '上级编号',
hiredate DATE COMMENT '雇佣日期',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金',
deptno INT(2) COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工中文表';
-- 创建DEPT表(部门表)及其中文表DEPT_ZH
CREATE TABLE IF NOT EXISTS dept (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
CREATE TABLE IF NOT EXISTS dept_zh (
deptno INT(2) NOT NULL COMMENT '部门编号',
dname VARCHAR(14) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门中文表';
-- 创建SALGRADE表(薪资等级表)及其中文表SALGRADE_ZH
CREATE TABLE IF NOT EXISTS salgrade (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级表';
CREATE TABLE IF NOT EXISTS salgrade_zh (
grade INT COMMENT '等级',
losal DECIMAL(10,2) COMMENT '最低薪资',
hisal DECIMAL(10,2) COMMENT '最高薪资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='薪资等级中文表';
-- 创建BONUS表(奖金表)及其中文表BONUS_ZH
CREATE TABLE IF NOT EXISTS bonus (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金表';
CREATE TABLE IF NOT EXISTS bonus_zh (
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(9) COMMENT '工作',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '佣金'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='奖金中文表';
-- 插入DEPT表数据
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
-- 插入DEPT_ZH表数据(中文)
INSERT INTO dept_zh (deptno, dname, loc) VALUES
(10, '会计部', '纽约'),
(20, '研发部', '达拉斯'),
(30, '销售部', '芝加哥'),
(40, '运营部', '波士顿');
-- 插入EMP表数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入EMP_ZH表数据(中文)
INSERT INTO emp_zh (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, '史密斯', '职员', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, '艾伦', '销售员', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, '沃德', '销售员', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, '琼斯', '经理', 7839, '1981-04-02', 2975.00, NULL, 20),
(7654, '马丁', '销售员', 7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, '布莱克', '经理', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, '克拉克', '经理', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, '斯科特', '分析师', 7566, '1987-04-19', 3000.00, NULL, 20),
(7839, '金', '总裁', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, '特纳', '销售员', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7876, '亚当斯', '职员', 7788, '1987-05-23', 1100.00, NULL, 20),
(7900, '詹姆斯', '职员', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, '福特', '分析师', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, '米勒', '职员', 7782, '1982-01-23', 1300.00, NULL, 10);
-- 插入SALGRADE表数据
INSERT INTO salgrade (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入SALGRADE_ZH表数据(中文)
INSERT INTO salgrade_zh (grade, losal, hisal) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
-- 插入BONUS表数据
INSERT INTO bonus (ename, job, sal, comm) VALUES
('SMITH', 'CLERK', 800, 200),
('ALLEN', 'SALESMAN', 1600, 300),
('WARD', 'SALESMAN', 1250, 500),
('JONES', 'MANAGER', 2975, 200),
('MARTIN', 'SALESMAN', 1250, 1400),
('BLAKE', 'MANAGER', 2850, 200),
('CLARK', 'MANAGER', 2450, 200),
('SCOTT', 'ANALYST', 3000, 200),
('KING', 'PRESIDENT', 5000, 500),
('TURNER', 'SALESMAN', 1500, 0),
('ADAMS', 'CLERK', 1100, 200),
('JAMES', 'CLERK', 950, 200),
('FORD', 'ANALYST', 3000, 200),
('MILLER', 'CLERK', 1300, 200);
-- 插入BONUS_ZH表数据(中文)
INSERT INTO bonus_zh (ename, job, sal, comm) VALUES
('史密斯', '职员', 800, 200),
('艾伦', '销售员', 1600, 300),
('沃德', '销售员', 1250, 500),
('琼斯', '经理', 2975, 200),
('马丁', '销售员', 1250, 1400),
('布莱克', '经理', 2850, 200),
('克拉克', '经理', 2450, 200),
('斯科特', '分析师', 3000, 200),
('金', '总裁', 5000, 500),
('特纳', '销售员', 1500, 0),
('亚当斯', '职员', 1100, 200),
('詹姆斯', '职员', 950, 200),
('福特', '分析师', 3000, 200),
('米勒', '职员', 1300, 200);
-- 提交所有事务
COMMIT;
-- 显示创建完成信息
SELECT '测试数据创建完成，共4个数据库(test, test1, test2, test3)，每个库包含Oracle Scott用户表及中文表' AS '执行结果';
4.2.主库创建复制用户
Create user 'repl'@'%' identified by ‘clear123’;
Grant replication slave on *.* to 'repl'@'%';
Flush privileges;
4.3.主库导出备份
mysqldump -uroot -p -B test test1 test2 test3 --master-data=2 --single-transaction --default-character-set=utf8mb4 --set-charset=true --skip-opt --result-file=C:\MySQL\databasebackup\alldatabase.sql
PS C:\Users\Administrator> mysqldump -uroot -p -B test test1 test2 test3 --master-data=2 --single-transaction --default-
character-set=utf8mb4 --set-charset=true --skip-opt --result-file=C:\MySQL\databasebackup\alldatabase.sql
Enter password: ********
PS C:\Users\Administrator>
4.4.主库拷贝备份到备库
windows可以通过共享文件夹的方式进行拷贝。
4.5.备库进行导入
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
4 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql -uroot -p --default-character-set=utf8mb4 < C:\MySQL\databasebackup\alldatabase.sql
4.6.备库开启同步
开启同步需要查看备份文件中log_file的信息，如果文件比较大的话可以上传到linux系统用more命令来看。
我测试环境备份很小，直接用记事本打开可以看到如下信息：
CHANGE MASTER TO MASTER_LOG_FILE='mysql_logbin.000002', MASTER_LOG_POS=120;
备库执行：
mysql> show slave status \G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql> change master to
-> master_host='192.168.59.12',
-> master_user='repl',
-> master_password='clear123',
-> master_port=3306,
-> master_log_file='mysql_logbin.000002',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.59.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_logbin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: WIN-STDEV303KMH-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql_logbin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
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: 120
Relay_Log_Space: 120
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: NULL
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: 0
Master_UUID:
Master_Info_File: C:\MySQL\MySQL data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)
ERROR:
No query specified
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.59.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_logbin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: WIN-STDEV303KMH-relay-bin.000002
Relay_Log_Pos: 286
Relay_Master_Log_File: mysql_logbin.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: 120
Relay_Log_Space: 469
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: 6f2f1ca8-45d5-11f0-8f90-000c291d239e
Master_Info_File: C:\MySQL\MySQL data\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)
ERROR:
No query specified
mysql>
测试数据是否同步：
主库：
mysql> use test1;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| bonus |
| bonus_zh |
| dept |
| dept_zh |
| emp |
| emp_zh |
| salgrade |
| salgrade_zh |
+-----------------+
8 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | r | r |
+--------+------------+----------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> delete from dept where deptno=50;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql>
备库：
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | r | r |
+--------+------------+----------+
5 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql>
五.互换ip地址并重新配置主从复制
5.1.停止业务
5.2.主库a执行：主库设置为只读
主库参数修改添加
read_only = 1
myql> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
记录主库a和备库b的状态
--a
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql_logbin.000002
Position: 322
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
--b
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql_logbin.000001
Position: 96649
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.59.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_logbin.000002
Read_Master_Log_Pos: 322
Relay_Log_File: WIN-STDEV303KMH-relay-bin.000005
Relay_Log_Pos: 286
Relay_Master_Log_File: mysql_logbin.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: 322
Relay_Log_Space: 635
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: 6f2f1ca8-45d5-11f0-8f90-000c291d239e
Master_Info_File: C:\MySQL\MySQL data\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)
ERROR:
No query specified
mysql>
重启主库
mysql> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
| test3 |
+--------------------+
7 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bonus |
| bonus_zh |
| dept |
| dept_zh |
| emp |
| emp_zh |
| salgrade |
| salgrade_zh |
+----------------+
8 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | m | u |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
9 rows in set (0.00 sec)
mysql> insert into dept values(50,'new','new');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | m | u |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | new | new |
+--------+------------+----------+
10 rows in set (0.00 sec)
可以看到，虽然设置为readonly了，但是root用户不受影响
5.3.从库执行：从库停止复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Que ry OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql>
5.4.互换ip
5.5.主库执行：重新建立复制并启动
这里重新建立复制是应该用a的信息还是b的信息呢，分别试一下
–a
mysql> show slave status \G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql> change master to
-> master_host='192.168.59.12',
-> master_user='repl',
-> master_password='clear123',
-> master_port=3306,
-> master_log_file='mysql_logbin.000002',
->
master_log_pos=322;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.59.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_logbin.000002
Read_Master_Log_Pos: 322
Relay_Log_File: WIN-7IBJ145PULH-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql_logbin.000002
Slave_IO_Running: No
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: 322
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first
log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 5bc1bccc-475a-11f0-9978-000c29f60ca6
Master_Info_File: C:\MySQL\MySQL data\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: 250617 11:57:11
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)
ERROR:
No query specified
mysql>
–b
mysql> change master to
-> master_host='192.168.59.12',
-> master_user='repl',
-> master_password='clear123',
-> master_port=3306,
-> master_log_file='mysql_logbin.000001',
-> master_log_pos=96649;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.59.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_logbin.000001
Read_Master_Log_Pos: 96649
Relay_Log_File: WIN-7IBJ145PULH-relay-bin.000002
Relay_Log_Pos: 286
Relay_Master_Log_File: mysql_logbin.000001
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: 96649
Relay_Log_Space: 469
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: 2
Master_UUID: 5bc1bccc-475a-11f0-9978-000c29f60ca6
Master_Info_File: C:\MySQL\MySQL data\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)
ERROR:
No query specified
mysql>
结论：重新搭建a-b的主从复制，在a库用b的show master的信息。
验证数据同步：
--b
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
| test3 |
+--------------------+
7 rows in set (0.00 sec)
mysql> use test3;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| bonus |
| bonus_zh |
| dept |
| dept_zh |
| emp |
| emp_zh |
| salgrade |
| salgrade_zh |
+-----------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> insert into dept values(50,'new','new');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | new | new |
+--------+------------+----------+
5 rows in set (0.00 sec)
mysql>
--a
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
| test3 |
+--------------------+
7 rows in set (0.01 sec)
mysql> use test3;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| bonus |
| bonus_zh |
| dept |
| dept_zh |
| emp |
| emp_zh |
| salgrade |
| salgrade_zh |
+-----------------+
8 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.05 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | new | new |
+--------+------------+----------+
5 rows in set (0.00 sec)
mysql>
5.6.启动业务
最后修改时间：2025-06-18 10:19:42
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
