mysql主从

最新推荐文章于 2024-07-15 19:44:21 发布
最新推荐文章于 2024-07-15 19:44:21 发布
阅读量44 收藏
点赞数
分类专栏： 奋斗 文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nuanchunhujian/article/details/126117424
版权

mysql主从

文章目录

主从作用

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

主从复制的原理

img

主从复制步骤：

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

主从配置步骤：

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

mysql主从配置

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

先准备实验环境，两个主机打开两个数据库，使用二进制安装的方式安装数据库（前面文章有详细操作）。

准备环境！
#先把主数据库的防火墙和一些配置文件关闭
[root@zhu ~]# systemctl stop --now firewalld
[root@zhu ~]# vi /etc/selinux/config 
SELINUX=disabled
#启动mysql的服务
[root@zhu ~]# service mysqld start
Starting MySQL. SUCCESS! 
#关闭从数据库的防火墙
[root@con ~]# systemctl stop  --now firewalld
[root@con ~]# vi /etc/selinux/config 
SELINUX=disabled
#启动mysql服务
[root@con ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/con.err'.
. SUCCESS!


//先查看主库有哪些库
[root@zhu ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhuconfuzhi        |
+--------------------+
[root@zhu ~]# 


mysql> 
//再查看从库有哪些库
[root@con ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@con ~]# 



#全备主库,全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
#备份主库并将备份文件传送到从库
[root@zhu ~]# mysqldump -uroot -p123 --all-databases > /opt/all-20220802.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zhu ~]# ls /opt/
all-20220802.sql  data  myrepo
[root@zhu ~]# scp /opt/all-20220802.sql root@192.168.171.134:/opt/
The authenticity of host '192.168.171.134 (192.168.171.134)' can't be established.
root@192.168.171.134's password: 
all-20220802.sql                        100%  856KB  81.6MB/s   00:00    
#查看此时从库上有传过来的备份文件
[root@con ~]# ls /opt/
all-20220802.sql  data
[root@con ~]# 
[root@con ~]# mysql -uroot -p123 <  /opt/all-20220802.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
#把复制过来的备份文件备份到从库确保数据一致
[root@con ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhuconfuzhi        |
+--------------------+
[root@con ~]# 


#在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@'192.168.171.134' identified by 'repl123'; 
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.171.134';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

配置主数据库

[root@zhu ~]# cat /etc/my.cnf 
[mysqld]
datadir=/opt/data
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
port=3306
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/opt/data/mysql.pid
log-bin=mysql-bin	//启用binlog日志
server-id=10		//数据库服务器唯一标识，主据库的server-id值必须比从库的小。
symbolic-links=0  	
[root@zhu ~]# 
[root@zhu ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@zhu ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port   Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:22          0.0.0.0:*            
LISTEN 0      80                 *:3306              *:*            
LISTEN 0      128             [::]:22             [::]:*            
[root@zhu ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
[root@zhu ~]#

配置从数据库

[root@con ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=20
relay-log=mysql-relay-bin
symbolic-links=0
[root@con ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@con ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port   Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:22          0.0.0.0:*            
LISTEN 0      128             [::]:22             [::]:*            
LISTEN 0      80                 *:3306              *:*            
#配置并启动主从复制
mysql> change master to
    -> master_host='192.168.171.133',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#启动
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#查看服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.171.133
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

测试验证

在主服务器的student库的bj2表中插入数据：

mysql> use zhuconfuzhi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from zhucon;
+----+-------+
| id | name  |
+----+-------+
|  1 | java  |
|  2 | tom   |
|  3 | hreey |
+----+-------+
3 rows in set (0.00 sec)
mysql> insert  zhucon(name) values ('tumao'),('lishuai');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@zhu ~]# mysql -uroot -p123 -e 'select * from zhuconfuzhi.zhucon;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+
| id | name    |
+----+---------+
|  1 | java    |
|  2 | tom     |
|  3 | hreey   |
|  4 | tumao   |
|  5 | lishuai |
+----+---------+
[root@zhu ~]#

在从数据库中查看数据是否同步：

[root@con ~]# mysql -uroot -p123 -e 'select * from zhuconfuzhi.zhucon;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+
| id | name    |
+----+---------+
|  1 | java    |
|  2 | tom     |
|  3 | hreey   |
|  4 | tumao   |
|  5 | lishuai |
+----+---------+
[root@con ~]#

GTID主从

GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。mysql主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。

GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。

准备主库和从库的环境

[root@zhu ~]# systemctl stop firewalld
[root@zhu ~]# vi /etc/selinux/config
SELINUX=disabled
[root@zhu ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port   Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:22          0.0.0.0:*            
LISTEN 0      80                 *:3306              *:*            
LISTEN 0      128             [::]:22             [::]:*            
[root@zhu ~]# 
[root@con ~]# systemctl stop firewalld
[root@con ~]# vi /etc/selinux/config 
SELINUX=disabled
[root@con ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/con.err'.
. SUCCESS! 
[root@con ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port   Peer Address:Port Process 
LISTEN 0      128          0.0.0.0:22          0.0.0.0:*            
LISTEN 0      80                 *:3306              *:*            
LISTEN 0      128             [::]:22             [::]:*            
[root@con ~]#

把主数据库的数据备份一下啊，让从库和主库的数据同步，记得备份的时候把主数据库锁上。

[root@zhu ~]# mysqldump -uroot -p123 --all-databases > /opt/all-20220802.sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zhu ~]# scp /opt/all-20220802.sql root@192.168.171.134:/opt/
The authenticity of host '192.168.171.134 (192.168.171.134)' can't be established.
root@192.168.171.134's password: 
all-20220802.sql                  100%  855KB  67.9MB/s   00:00    
#从数据库接收，并数据同步
[root@con ~]# ls /opt/
all-20220802.sql  data
[root@con ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@con ~]# mysql -uroot -p123 < /opt/all-20220802.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@con ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhang              |
+--------------------+
[root@con ~]#

配置主库

[root@zhu ~]# cat /etc/my.cnf 
[mysqld]
datadir=/opt/data
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
port=3306
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/opt/data/mysql.pid
server_id=1
gtid_mode=on
enforce_gtid_consistency=on
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@zhu ~]#

配置从库

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
gtid_mode=on
enforce_gtid_consistency=on
server_id=2
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@con ~]#

重启主从库

[root@zhu ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@con ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.Logging to '/opt/data/con.err'.
 SUCCESS!

配置主从复制

#在主库上创建共享用户
mysql> create user 'repl'@'192.168.171.134' identified by 'repl123'; 
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.171.134';
Query OK, 0 rows affected (0.00 sec)

mysql> 
#在从库上配置主从
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.171.133',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl123',
    -> MASTER_PORT=3306,
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.00 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.171.133
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 623
               Relay_Log_File: con-relay-bin.000002
                Relay_Log_Pos: 844
        Relay_Master_Log_File: master-binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#确保以下两项是yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试验证

#在主库上创建一个新数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create databases lishuai,zhang;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lishuai            |
| mysql              |
| performance_schema |
| sys                |
| zhang              |
+--------------------+
6 rows in set (0.00 sec)
#在从库上查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lishuai            |
| mysql              |
| performance_schema |
| sys                |
| zhang              |
+--------------------+
6 rows in set (0.00 sec)

``
暖春忽见
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql主从同步备份策略分享
09-11
MySQL 主从同步备份策略分享 MySQL 主从同步备份策略是指在 master 服务器和 slave 服务器之间实现数据同步的备份策略。该策略可以确保数据的一致性和可用性，提高系统的可靠性和稳定性。 在本文中，我们将介绍 ...
MYSQL主从复制
11-03
MYSQL主从复制技术详解 MYSQL主从复制是MYSQL数据库管理系统中的一种重要功能，它可以将主服务器上的数据自动复制到从服务器上，从而实现实时灾备、读写分离、备份和异步复制等功能。本文将详细介绍MYSQL主从复制的...
MySQL主从
SinG_Lala的博客
09-07 2810
本文主要讲述了MySQL的主从复制概念以及部署配置和GTID
MySql主从
m0_37685866的博客
03-25 803
mysql主从同步原理，同步策略，延迟原因，延迟优化方案等
mysql主从搭建
qq_37940440的博客
01-17 862
mysql 主从配置
MySQL主从复制
junge的博客
03-30 1946
MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一；但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。
MySQL 主从复制原理与问题
Dablelv 的博客专栏。
10-19 707
对应的，主库会启动一个 log dump 线程，根据传过来的（file，pos）在本地的binlog中查找，并把剩下的 binlog 发送给slave。主节点会为每一个从节点创建一个 dump 线程。因为如果主节点有太多的从节点，就会损耗一部分性能用于 replication ，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 log dump 线程，而每个从节点都有自己的 I/O 线程和 SQL 线程。
mysql主从复制
m0_73695023的博客
05-12 2131
数据库的主从复制工作中必须要用到一定要会！！！！
详解MySQL主从复制
Joker_ZJN的博客
05-20 2347
一文聊明白MySQL主从复制以及请求分发问题
MySQL主从复制与切换
weixin_43816711的博客
12-20 1967
由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。为了完成主从复制，从库需要通过 I/O 线程获取主库中 dump 线程读取的 binlog 内容并写入到自己的中继日志 relay log 中，从库的 SQL 线程再读取中继日志，重做中继日志中的日志，相当于再执行一遍 SQL，更新自己的数据库，以达到数据的一致性。
MySQL 主从同步原理
喵酱
09-26 3708
MySQL 主从同步
Mysql主从模式
李永志的博客
04-09 4373
随着科技的进步和互联网行业的发展，上网的人越来越多，所以我们开发的项目访问量越来越多，数据也越来越多，为了针对这个问题很多项目都搭建了集群，同样对于数据库也搭建了集群，本文就简单的对Mysql搭建主从模式做了简单的说明和配置。
docker-compose关于mysql主从复制一键搭建脚本
09-18
关于博客: https://blog.csdn.net/qq_42413011/article/details/126914080 的搭建脚本
Mysql 主从1062问题修复
07-12
本文将深入探讨MySQL主从复制中的1062错误及其解决方法。 1. **1062错误详解**： MySQL错误代码1062表示“Duplicate entry '...' for key '...''”，意味着在尝试插入一条新记录时，发现该记录的主键值已经存在于...
ubuntu 16.04配置MySQL主从同步的配置方法
09-09
在Ubuntu 16.04上配置MySQL主从同步是一项关键的任务，对于实现高可用性和数据备份至关重要。以下是一个详细的步骤指南： **准备工作** 1. **主从数据库版本一致**：为了确保兼容性和数据一致性，建议主从数据库...
MySQL & NaviCat 安装及配置教程（Windows）【安装】
绯樱殇雪的博客
07-15 362
MySQL & NaviCat 安装及配置教程
centos7中MySQL的卸载
CodeTom的博客
07-11 243
6、删除/var/log/mysqld.log（如果不删除这个文件，会导致新安装的mysql无法生存新密码，会出现无法登陆的情况）卸载完成后不要忘记检查一遍是否卸载干净。4、对安装的所有XXX目录进行删除操作。1、查看mysql安装了哪些东西。5、删除/etc/my.cnf。删除完成后检查是否删除干净。3、找mysql相关目录。
MySQL中的内置函数
qhy850716的博客
07-15 271
一、时间函数 语法 函数 说明 current_date() 日期 current_time() 时间 current_timestamp() 时间戳 now() 当前时间 date(date_time) 取出时间中的日期 date_add(date, interval value_type) 时间相加，单位year, day, minute, second date_sub(date, interval value_type)
记录些MySQL题集（3）
最新发布
安静的软件工程师
07-15 504
记录些MySQL题集（3）
mysql主从复制安装配置进阶.pptx
10-21
mysql主从复制安装配置进阶

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值