Mysql 一主一从

2023年6月14日下午2:29 • 数据库 • 阅读 77

1. 主从原理

1.1 主从介绍

所谓 mysql 主从就是建立两个完全一样的数据库，其中一个为主要使用的数据库，另一个为次要的数据库，一般在企业中，存放比较重要的数据的数据库服务器需要配置主从，这样可以防止因数据库服务器宕机导致数据丢失，还能保证业务量太多、数据太多和访问人数太多时服务的质量（服务器响应速度），还能提供故障切换、读写分离、和备份等等功能

1.2 主从作用

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

1.3 主从形式

一主一从

主主复制：当作备份使用，当主服务器出现故障时，另一个主服务器会自动顶上。

一主多从：用来实现读写分离，当写操作较少时，读操作较多时使用，主服务器用来实现写操作，从服务器用来实现读操作。

多主一从：用来实现读写分离，当写操作较多时，读操作较少时使用，主服务器用来实现写操作，从服务器用来实现读操作。5.7 开始支持

联级复制：是指从主场地复制过来的又从该场地再次复制到其他场地，即 A 场地把数据复制到 B 场地，B 场地又把这些数据或其中部分数据再复制到其他场地。

1.4 主从复制原理

主从复制步骤：

主库将所有的写操作记录到 binlog 日志中并生成一个 log dump 线程，将 binlog 日志传给从库的 I/O 线程
从库生成两个线程，一个 I/O 线程，一个 SQL 线程
I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log（中继日志）文件中
SQL 线程，会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

2. 主从复制配置

主从复制配置步骤：

确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）

需求：
搭建两台 MySQL 服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

&#x6570;&#x636E;&#x5E93;&#x89D2;&#x8272;    IP  &#x5E94;&#x7528;&#x4E0E;&#x7CFB;&#x7EDF;&#x7248;&#x672C;    &#x6709;&#x65E0;&#x6570;&#x636E;
&#x4E3B;&#x6570;&#x636E;&#x5E93;    192.168.23.4    Centos7.8 mysql-5.7 &#x6709;&#x6570;&#x636E;
&#x4ECE;&#x6570;&#x636E;&#x5E93;    192.168.23.5    Centos7.8 mysql-5.7 &#x65E0;&#x6570;&#x636E;

2.1 Mysql安装

注意：需把安装包分别上传到两台服务器上分别执行下列命令进行安装

[root@localhost soft]# yum remove mysql-libs
[root@localhost soft]# rpm -qa | grep mariadb

进行安装

将下载好的mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar上传

&#x5B89;&#x88C5;&#x76F8;&#x5E94;&#x8F6F;&#x4EF6;
[root@localhost soft]# yum install -y openssl-devel.x86_64 openssl.x86_64 yum install -y libaio.x86_64 libaio-devel.x86_64 yum install -y perl.x86_64 perl-devel.x86_64 yum install -y perl-JSON.noarch yum install -y autoconf yum install -y wget yum install -y net-tools

&#x5173;&#x95ED;&#x9632;&#x706B;&#x5899;
[root@localhost soft]# systemctl stop firewalld
[root@localhost soft]# systemctl disable firewalld

&#x89E3;&#x538B;&#x8F6F;&#x4EF6;&#x5305;&#xFF1A;
[root@localhost soft]# tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar

[root@localhost soft]# rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm
[root@localhostsoft]#rpm -ivh mysql-community-embedded-compat-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-devel-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-test-5.7.22-1.el7.x86_64.rpm
&#x67E5;&#x770B;&#x5B89;&#x88C5;&#x7248;&#x672C;&#xFF1A;
[root@localhost soft]# mysql -V

编辑配置文件

vim /etc/my.cnf

跳过登录验证无需密码即可登录mysql

skip-grant-tables

设置默认字符集UTF-8

character_set_server=utf8

collation-server=utf8_general_ci

设置默认字符集UTF-8

init_connect=’SET NAMES utf8′

设置数据库日志过期天数为14天

server_id=1

expire_logs_days=14

[client]

default-character-set=utf8

##&#x6DFB;&#x52A0;
skip-grant-tables
character_set_server=utf8
collation-server=utf8_general_ci
init_connect='SET NAMES utf8'
server_id=1
expire_logs_days=14
[client]
default-character-set=utf8

&#x542F;&#x52A8;mysql
[root@localhost soft]# systemctl start mysqld.service
[root@localhost soft]# systemctl status mysqld

&#x83B7;&#x53D6;mysql&#x7684;root&#x7528;&#x6237;&#x7684;&#x521D;&#x59CB;&#x5BC6;&#x7801;
[root@localhost soft]# grep 'temporary password' /var/log/mysqld.log

&#x4EE5;&#x83B7;&#x53D6;mysql&#x7684;root&#x7528;&#x6237;&#x7684;&#x521D;&#x59CB;&#x5BC6;&#x7801;&#x767B;&#x5F55;&#x6570;&#x636E;&#x5E93;
[root@localhost soft]# mysql -u root -pAcqWlI64o:kG

&#x4FEE;&#x6539;root&#x5BC6;&#x7801;
mysql> flush privileges;
mysql> set password for root@localhost=password('123456');
&#x4F7F;&#x5BC6;&#x7801;&#x5373;&#x65F6;&#x751F;&#x6548;
mysql> flush privileges;
&#x5141;&#x8BB8;&#x4EE5;root&#x8EAB;&#x4EFD;&#x8FDC;&#x7A0B;&#x767B;&#x5F55;mysql
mysql> grant all privileges on *.* to root@'%' identified by "123456";
mysql> flush privileges;

2.2 mysql 主从配置

2.2.1 确保从数据库于主数据库的数据一样

为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

//&#x4E3B;&#x5E93;&#x4E3A;master
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# bash
[root@master ~]#

//&#x4ECE;&#x5E93;&#x4E3A;slave
[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# bash
[root@slave ~]#

//&#x5148;&#x67E5;&#x770B;&#x4E3B;&#x5E93;&#x6709;&#x54EA;&#x4E9B;&#x5E93;
[root@master soft]# mysql -uroot -p123456 -e&#x2018;show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
//&#x5728;&#x67E5;&#x770B;&#x4ECE;&#x5E93;
[root@slave soft]# mysql -uroot -p123456 -e&#x2018;show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

//全备主库 //全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
//&#x6B64;&#x9501;&#x8868;&#x7684;&#x7EC8;&#x7AEF;&#x5FC5;&#x987B;&#x5728;&#x5907;&#x4EFD;&#x5B8C;&#x6210;&#x4EE5;&#x540E;&#x624D;&#x80FD;&#x9000;&#x51FA;

//&#x5907;&#x4EFD;&#x4E3B;&#x5E93;&#x5E76;&#x5C06;&#x5907;&#x4EFD;&#x6587;&#x4EF6;&#x4F20;&#x9001;&#x5230;&#x4ECE;&#x5E93;
[root@master soft]# mysqldump -uroot -p123456 --all-databases > /opt/acc-2022.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@master ~]# ls /opt/

[root@master soft]# scp /opt/acc-2022.sql root@192.168.23.5:/opt/

//&#x89E3;&#x9664;&#x4E3B;&#x5E93;&#x7684;&#x9501;&#x8868;&#x72B6;&#x6001;&#xFF0C;&#x76F4;&#x63A5;&#x9000;&#x51FA;&#x4EA4;&#x4E92;&#x5F0F;&#x754C;&#x9762;&#x5373;&#x53EF;
mysql> quit
Bye
[root@master ~]#

//&#x5728;&#x4ECE;&#x5E93;&#x4E0A;&#x6062;&#x590D;&#x4E3B;&#x5E93;&#x7684;&#x5907;&#x4EFD;&#x5E76;&#x67E5;&#x770B;&#x4ECE;&#x5E93;&#x6709;&#x54EA;&#x4E9B;&#x5E93;&#xFF0C;&#x786E;&#x4FDD;&#x4E0E;&#x4E3B;&#x5E93;&#x4E00;&#x81F4;
[root@slave ~]# mysql -uroot -p123456 < /opt/acc-2022.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@slave ~]# mysql -uroot -p123456 -e'show databases';
mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
[root@slave ~]#

2.2.3 配置主数据库

[root@master soft]# vim /etc/my.cnf
//&#x5728;[mysql]&#x8FD9;&#x6BB5;&#x540E;&#x9762;&#x6DFB;&#x52A0;
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin //&#x6DFB;&#x52A0; &#x542F;&#x7528;binlog&#x65E5;&#x5FD7;
server-id=1  //&#x6DFB;&#x52A0; &#x6570;&#x636E;&#x5E93;&#x670D;&#x52A1;&#x5668;&#x552F;&#x4E00;&#x6807;&#x8BC6;&#x7B26;&#xFF0C;&#x4E3B;&#x5E93;&#x7684;server-id&#x503C;&#x5FC5;&#x987B;&#x6BD4;&#x4ECE;&#x5E93;&#x7684;&#x5C0F;
Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

//&#x91CD;&#x542F;&#x6570;&#x4E3B;&#x5E93;&#x7684;mysql&#x670D;&#x52A1;
[root@master soft]# systemctl restart mysqld
[root@master soft]# ss -anlt
State      Recv-Q Send-Q                              Local Address:Port                                             Peer Address:Port
LISTEN     0      128                                             *:22                                                          *:*
LISTEN     0      128                                          [::]:22                                                       [::]:*
LISTEN     0      80                                           [::]:3306                                                     [::]:*
[root@master soft]#

//&#x67E5;&#x770B;&#x4E3B;&#x5E93;&#x72B6;&#x6001;
[root@master soft]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

2.2.4 配置从数据库

[root@slave ~]# vim /etc/my.cnf
//&#x5728;[mysql]&#x8FD9;&#x6BB5;&#x540E;&#x9762;&#x6DFB;&#x52A0;
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2  //&#x6DFB;&#x52A0; &#x4ECE;&#x5E93;&#x7684;server-id&#x6BD4;&#x4E3B;&#x5E93;&#x7684;&#x5927;
relay-log=mysql-relay-bin  //&#x6DFB;&#x52A0;
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

//&#x91CD;&#x542F;mysql&#x670D;&#x52A1;
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# ss -anlt
State      Recv-Q Send-Q                              Local Address:Port                                             Peer Address:Port
LISTEN     0      128                                             *:22                                                          *:*
LISTEN     0      128                                          [::]:22                                                       [::]:*
LISTEN     0      80                                           [::]:3306                                                     [::]:*
[root@slave ~]#

//&#x914D;&#x7F6E;&#x5E76;&#x542F;&#x52A8;&#x4E3B;&#x4ECE;&#x590D;&#x5236;
[root@slave ~]# mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> change master to
    -> master_host='192.168.23.4',
    -> master_user='hqd',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>
//&#x67E5;&#x770B;&#x670D;&#x52A1;&#x5668;&#x72B6;&#x6001;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.23.4
                  Master_User: hqd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:

3 测试验证

&#x5728;&#x4E3B;&#x670D;&#x52A1;&#x5668;&#x521B;&#x5EFA;&#x2018;hqd&#x2019;&#x540D;&#x79F0;&#x7684;&#x5E93; &#x67E5;&#x770B;&#x4ECE;&#x670D;&#x52A1;&#x5668;&#x662F;&#x5426;&#x540C;&#x6B65;
mysql> create database hqd;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hqd                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql>

&#x5728;&#x4ECE;&#x6570;&#x636E;&#x5E93;&#x4E2D;&#x67E5;&#x770B;&#x6570;&#x636E;&#x662F;&#x5426;&#x540C;&#x6B65;
[root@slave ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hqd                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql>

Original: https://www.cnblogs.com/Jqazc/p/16618696.html
Author: 我真的兔了
Title: Mysql 一主一从

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/611866/

转载文章受原作者版权保护。转载请注明原作者出处！

数据库

【自取】最近整理的，有需要可以领取学习：

Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言刷题答案下载！

FastDFS安装和简介详细总结

1、fastDFS简介 1 FastDFS是用c语言编写的一款开源的分布式文件系统。 2 FastDFS为互联网量身定制，充分考虑了冗余备份、负载均衡、线性扩容等机制，并注重高可用…

数据库 2023年6月14日
00111
MySQL InnoDB缓存

1. 背景对于各种用户数据、索引数据等各种数据都是需要持久化存储到磁盘，然后以”页”为单位进行读写。相对于直接读写缓存，磁盘IO的成本相当高昂。对于读…

数据库 2023年6月14日
00148
MySQL的插入性能优化

MySQL的插入性能优化修改系统变量的方法一、通过编辑ini配置文件进行修改；二、通过输入sql命令进行修改；查询和修改系统变量；如果要修改全局变量, 必须要显示指定&#…

数据库 2023年6月14日
0089
MySQL快速创建800w条测试数据表&深度分页

MySQL快速创建800w条测试数据表&深度分页如果在普通表格中插入条，效率太低，但内存表的插入速度很快，可以先创建内存表，插入数据，然后再导入到普通表格中。 [En] …

数据库 2023年5月24日
00116
Java学习-第一部分-第一阶段-第六节：面向对象编程（基础）

面向对象编程（基础）笔记目录：(https://www.cnblogs.com/wenjie2000/p/16378441.html) 类与对象 ●使用现有技术解决张老太养了两…

数据库 2023年6月11日
00121
ES6 Map映射

ES6 Map映射 Map对象保存的是键值对。任何类型值(对象或原始值)都可以作为一个键或一个值。基础用法 //创建 let map = new Map(); //增|改,返回…

数据库 2023年6月11日
0093
5000+字硬核干货！Redis 分布式集群部署实战

原理： Redis集群采用一致性哈希槽的方式将集群中每个主节点都分配一定的哈希槽，对写入的数据进行哈希后分配到某个主节点进行存储。集群使用公式（CRC16 key）& 1…

数据库 2023年6月9日
0093
openpyxl使用总结

设置表头单元格的颜色 fill = PatternFill("solid", fgColor=’FF000000′) font = Font(color=’00…

数据库 2023年6月9日
0081
Spring常见问题

Spring常见问题问渠那得清如许？为有源头活水来。 Spring 是个 java 企业级应用的开源开发框架。Spring 主要用来开发 Java 应用，但是有些扩展是针对构建 …

数据库 2023年6月14日
0077
java 考试系统在线学习视频直播人脸识别 springboot框架前后分离 PC和手机端

新增功能：培训学习模块， PDF电子课程、视频课程、直播课程（自己搭建直播流服务器）人脸识别(考试时验证，有开关)、补考开关组建试卷：创建试卷，题目、类型、总分、及格分数、时长…

数据库 2023年6月6日
0089
常用MySQL语句(持续更新)

1. 客户端登录在终端输入 mysql -u[用户名] -p[密码]…

数据库 2023年6月14日
0078
2022-8-30 servlet

HttpServletRequest — request(请求) 所有的 和&a…

数据库 2023年6月14日
0081
leetcode 110. Balanced Binary Tree 平衡二叉树(简单)

一、题目大意给定一个二叉树，判断它是否是高度平衡的二叉树。本题中，一棵高度平衡二叉树定义为：一个二叉树每个节点的左右两个子树的高度差的绝对值不超过 1 。示例 1：输入…

数据库 2023年6月16日
0082
Java中CMD命令来备份mysql数据库备份文件出来为0字节问题

Java中CMD命令来备份mysql&#…

数据库 2023年5月24日
00109
【黄啊码】MySQL入门—3、我用select *，老板直接赶我坐火车回家去，买的还是站票

大家好！我是黄啊码，学会了DDL语句了吗？那我们今天就来学习一下基本的查询语法，我见过很多外包机构的程序员都是万物皆可select *，然后项目跑了一段时间就基本跑不动了，问就回答…

数据库 2023年6月16日
0061
ReentrantLock 公平锁源码第1篇

ReentrantLock 1 这篇还是接着ReentrantLock的公平锁,没看过第0篇的可以先去看上一篇https://www.cnblogs.com/sunankang/p…

数据库 2023年6月16日
00110

2024 年 5 月
一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31