linux mysql5.7.25 主从复制_生产版本

一、安装配置

1. 部署总览

2. 下载软件

mkdir /app
cd /app
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz

3. 解压重命名

tar -zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.25-linux-glibc2.12-x86_64 mysql-5.7.25
1
2

4. 创建组

等待解压完毕后执行

groupadd mysql
useradd -r -g mysql mysql
1
2

5. 安装数据库

cd /app/mysql-5.7.25
chown -R mysql:mysql ./
bin/mysqld --initialize --user=mysql --basedir=/app/mysql-5.7.25 --datadir=/app/mysql-5.7.25/data

生成了临时密码(ehAhDfgn97)O) 要复制出来，等会用

122节点默认密码，复制到txtx文本中，等会用
%ci5&rob2DAl

123节点默认密码，复制到txtx文本中，等会用
F:zVnky+C7AK

6. 配置my.cnf

• 122节点配置内容如下

vim /etc/my.cnf
1

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/app/mysql-5.7.25
datadir=/app/mysql-5.7.25/data
socket=/tmp/mysql.sock
#忽略大小写
lower_case_table_names = 1
# 不开启严格模式
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-error=/var/log/mysqld.log
pid-file=/app/mysql-5.7.25/data/mysqld.pid

#开启日志 
log-bin = mysql-bin
#设置服务id，主从不能一致  ,一般设置为ip最后一段
server-id = 122
#设置需要同步的数据库 
binlog-do-db=pis_data
#屏蔽系统库同步 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• 123节点配置内容如下

vim /etc/my.cnf
1

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/app/mysql-5.7.25
datadir=/app/mysql-5.7.25/data
socket=/tmp/mysql.sock
#忽略大小写
lower_case_table_names = 1
# 不开启严格模式
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-error=/var/log/mysqld.log
pid-file=/app/mysql-5.7.25/data/mysqld.pid

#开启日志 
log-bin = mysql-bin
#设置服务id，主从不能一致  ,一般设置为ip最后一段
server-id = 123
#设置需要同步的数据库 
binlog-do-db=pis_data
#屏蔽系统库同步 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

说明：主从复制主节点一定要开启 log-bin = mysql-bin，从节点监听log-bin日志完成数据复制的，因此这个配置是主从复制的关键

7. 添加开机启动

cp /app/mysql-5.7.25/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
1
2

8. 配置数据木库

vim /etc/init.d/mysqld  
1

添加路径 在46行

basedir=/app/mysql-5.7.25
datadir=/app/mysql-5.7.25/data/
1
2

9. 启动mysql

service mysqld start 
1

10. 登录修改密码

• 122节点

cd /app/mysql-5.7.25/bin
./mysql -uroot -p
%ci5&rob2DAl

• 123节点

cd /app/mysql-5.7.25/bin
./mysql -uroot -p
F:zVnky+C7AK

• 修改密码

alter user 'root'@'localhost' identified by '123456';   
flush privileges;
1
2

11. 允许远程连接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; 
1

二、master节点主从

122节点（master）

2.1. 主从复制账号创建

在MySql主机node1节点上,创建mysql 主从复制的账号

cd /app/mysql-5.7.25/bin
./mysql -uroot -p
1
2

密码：123456

2.2. 创建主从复制的账号

create user ‘im_repl’@‘192.168.52.%’ identified by ‘123456’;

2.3. 账号授权

给主从复制的账号赋予操作所有对象的replication slave权限

grant replication slave on *.* to 'im_repl'@'192.168.52.%'; 
1

2.4. 查看同步bin-log

show master status;
1

• 操作记录

[root@localhost bin]# ./mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000001 |      154 | pis_data     | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.5. 开放防火墙

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
1
2

三、133从节点

123节点（slave）

3.1. 创建复制链路

登陆数据库

cd /app/mysql-5.7.25/bin
./mysql -uroot -p
1
2

输入密码：123456

3.2. 建立复制的链路

change master to master_host='192.168.52.122',master_user='im_repl',master_password='123456',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
1

说明：MASTER_LOG_FILE=‘mysql-bin.000001’, MASTER_LOG_POS=154 这项信息就是从主节点查看bin-log信息获取的

3.3. 启动链路

start slave;
1

四、测试主从复制

4.1. master操作

创建数据库、初始化表结构

create DATABASE pis_data;
use pis_data;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for gblfy_test
-- ----------------------------
DROP TABLE IF EXISTS `gblfy_test`;
CREATE TABLE `gblfy_test`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of gblfy_test
-- ----------------------------
INSERT INTO `gblfy_test` VALUES (1, 'gblfy');
INSERT INTO `gblfy_test` VALUES (2, 'gblfy');
INSERT INTO `gblfy_test` VALUES (3, 'gblfy');
INSERT INTO `gblfy_test` VALUES (4, 'gblfy');
INSERT INTO `gblfy_test` VALUES (5, 'gblfy');
INSERT INTO `gblfy_test` VALUES (6, 'gblfy');
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4.2. slave验证数据复制