0
0

mysql-搭建主从复制

文章目录

**

  1. 主库更新，从库会同步更新。

**
**

  1. 从库更新，主库一般是不会同步更新的，如果发生主库也同步更新，可能出现短暂bug，或者主从配置有问题。

**
mysql集群：

单台设备的负载压力：主从复制

集群：分摊访问压力和存储压力

需求：使用
**

  1. 3306

** mysql当作主，
**

  1. 3316

** mysql 当作从，在3306中对
**

  1. mydb2/mydb3

** 数据库所有的操作，希望能够主从复制同步到3316，其他的数据库操作不同步。

1、准备主服务器

  1. docker run -d\--name spzx-mysql \-p3306:3306 \-v mysql_data:/var/lib/mysql \-v mysql_conf:/etc/mysql \--restart=always \--privileged=true \-eMYSQL_ROOT_PASSWORD=123456\
  2. mysql:8
  1. [root@localhost ~]# docker ps
  2. CONTAINER ID   IMAGE                       COMMAND                   CREATED        STATUS      PORTS                                                                                  
  3. ab66508d9441   mysql:8                     "docker-entrypoint.s…"8 months ago   Up 9 days   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp                                   spzx-mysql

此时我已经有一个主服务器 spzx-mysql

2、准备从服务器

  1. docker run -d\-p3316:3306 \-v mysql-slave1-conf:/etc/mysql/conf.d \-v mysql-slave1-data:/var/lib/mysql \-eMYSQL_ROOT_PASSWORD=123456\--name atguigu-mysql-slave1 \
  2. mysql:8
  1. [root@localhost ~]# docker ps
  2. CONTAINER ID   IMAGE                       COMMAND                   CREATED          STATUS         PORTS                                                                                  NAMES
  3. c236f876ae40   mysql:8"docker-entrypoint.s…"10 seconds ago   Up 3 seconds   33060/tcp,0.0.0.0:3316->3306/tcp,:::3316->3306/tcp                                   atguigu-mysql-slave1
  4. ab66508d9441   mysql:8"docker-entrypoint.s…"8 months ago     Up 9 days      0.0.0.0:3306->3306/tcp,:::3306->3306/tcp,33060/tcp                                   spzx-mysql

3、主库配置

  1. 先在主mysql中配置 记录mydb2/mydb3库的操作日志到binlog日志文件中 – 主库写操作会按照配置记录到二进制文件中（binlog） – 主库需要创建一个从账户并分配可以读取binlog日志的权限
  2. 在从mysql中配置中继日志文件，用来保存读取到的mysql主的 binlog 日志 – 从库可以开启主从复制，从指定的主库的binlog文件中加载日志缓存到自己的relaylog文件中，最后通过一个sql线程将relaylog文件中的日志replay到自己的库表中 – 从库需要使用主库提供的账号和主库的binlog文件建立连接

3.1、创建MySQL主服务器配置文件：

  1. [root@localhost ~]# docker inspect spzx-mysql 
  1. "Mounts":[{"Type":"volume","Name":"mysql_conf","Source":"/var/lib/docker/volumes/mysql_conf/_data","Destination":"/etc/mysql","Driver":"local","Mode":"z","RW":true,"Propagation":""},{"Type":"volume","Name":"mysql_data","Source":"/var/lib/docker/volumes/mysql_data/_data","Destination":"/var/lib/mysql","Driver":"local","Mode":"z","RW":true,"Propagation":""}],
  1. [root@localhost _data]# cd /var/lib/docker/volumes/mysql_conf/_data[root@localhost _data]# ll
  2. 总用量 8
  3. drwxrwxr-x. 2 root root   4112月 262023 conf.d
  4. -rw-rw-r--. 1 root root 108012月 212021 my.cnf
  5. -rw-r--r--. 1 root root 14489月  282021 my.cnf.fallback
  1. [root@localhost _data]# vim my.cnf

配置如下内容：

  1. [mysqld]# 服务器唯一id，默认值1
  2. server-id=1# 设置日志格式，默认值ROW。row（记录行数据）  statement（记录sql）  mixed（混合模式）binlog_format=STATEMENT
  3. # 二进制日志名，默认binlog# log-bin=binlog
  4. log-bin=spzxbinlog
  5. # 设置需要复制的数据库，默认复制全部数据库
  6. binlog-do-db=mydb2
  7. binlog-do-db=mydb3
  8. # 设置不需要复制的数据库
  9. binlog-ignore-db=mydb4
  10. #binlog-ignore-db=infomation_schema

在这里插入图片描述

  1. [root@localhost _data]# docker restart spzx-mysql
  2. spzx-mysql
  1. [root@localhost _data]# ll ../../mysql_data/_data/

在这里插入图片描述

4、从库配置

  1. [root@localhost _data]# docker inspect atguigu-mysql-slave1

在这里插入图片描述

  1. vim /var/lib/docker/volumes/mysql-slave1-conf/_data/my.cnf

配置如下内容：

  1. [mysqld]
  2. # 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
  3. server-id=2
  4. # 中继日志名，默认xxxxxxxxxxxx-relay-bin
  5. #relay-log=relay-bin

在这里插入图片描述

  1. [root@localhost _data]# docker restart atguigu-mysql-slave1 
  2. atguigu-mysql-slave1

5、搭建主从&测试

5.1、使用命令行登录MySQL主服务器

  1. [root@localhost _data]# docker exec -it spzx-mysql /bin/bash
  2. root@ab66508d9441:/# mysql -uroot -p123456
  3. mysql: [Warning] Using a password on the command line interface can be insecure.
  4. Welcome to the MySQL monitor.  Commands end with ; or \g.
  5. Your MySQL connection id is 8
  6. Server version: 8.0.27 MySQL Community Server - GPL
  8. Copyright (c)2000, 2021, Oracle and/or its affiliates.
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  14. Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.

5.2、主机中查询master状态：

  1. mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| spzxbinlog.000001|156| mydb2,mydb3  | mydb4            ||+-------------------+----------+--------------+------------------+-------------------+1 row in set(0.01 sec)

5.3、从机中查询slave状态：

  1. [root@localhost ~]# docker exec -it atguigu-mysql-slave1 /bin/bash
  2. ERROR 1045(28000): Access denied for user 'root'@'localhost'(using password: NO)
  3. root@c236f876ae40:/# mysql -uroot -p123456
  4. mysql: [Warning] Using a password on the command line interface can be insecure.
  5. Welcome to the MySQL monitor.  Commands end with ; or \g.
  6. Your MySQL connection id is 9
  7. Server version: 8.0.27 MySQL Community Server - GPL
  9. Copyright (c)2000, 2021, Oracle and/or its affiliates.
  11. Oracle is a registered trademark of Oracle Corporation and/or its
  12. affiliates. Other names may be trademarks of their respective
  13. owners.
  15. Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
  17. mysql>
  1. mysql> show slave status;
  2. Empty set,1warning(0.02 sec)

**

  1. 从库必须和主库主动建立连接 开启自己的sql和io线程

**

5.4、主机中创建slave用户：

  1. -- 创建slave用户CREATEUSER'atguigu_slave'@'%';-- 设置密码ALTERUSER'atguigu_slave'@'%' IDENTIFIED WITH mysql_native_password BY'123456';-- 授予复制权限GRANTREPLICATION SLAVE ON*.*TO'atguigu_slave'@'%';-- 刷新权限
  2. FLUSH PRIVILEGES;
  1. mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| spzxbinlog.000001|1074| mydb2,mydb3  | mydb4            ||+-------------------+----------+--------------+------------------+-------------------+1 row in set(0.00 sec)

5.5、在从机上配置主从关系：

  1. CHANGE MASTER TO MASTER_HOST='192.168.74.148', 
  2. MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
  3. MASTER_LOG_FILE='spzxbinlog.000001',MASTER_LOG_POS=1074;
  1. mysql> CHANGE MASTER TO MASTER_HOST='192.168.74.148',-> MASTER_USER='atguigu_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,-> MASTER_LOG_FILE='spzxbinlog.000001',MASTER_LOG_POS=1074; 
  2. Query OK,0rows affected,9warnings(0.05 sec)
  1. mysql> show slave status;+----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+| Slave_IO_State | Master_Host    | Master_User   | Master_Port | Connect_Retry | Master_Log_File   | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |+----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+||192.168.74.148| atguigu_slave |3306|60| spzxbinlog.000001|1074| c236f876ae40-relay-bin.000001|4| spzxbinlog.000001| No               | No                |||||||0||0|1074|156| None            ||0| No                 ||||||NULL| No                            |0||0|||0|| mysql.slave_master_info |0|NULL||86400||||||||0|||||0||+----------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+1 row in set,1warning(0.00 sec)

在这里插入图片描述

5.6、启动从库的io和sql线程：都启动成功主从才搭建成功

  1. mysql>start slave;
  2. Query OK,0rows affected,1 warning (0.03 sec)
  1. mysql> show slave status;+----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+| Slave_IO_State                   | Master_Host    | Master_User   | Master_Port | Connect_Retry | Master_Log_File   | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                  | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |+----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+| Waiting for source to send event |192.168.74.148| atguigu_slave |3306|60| spzxbinlog.000001|1074| c236f876ae40-relay-bin.000002|325| spzxbinlog.000001| Yes              | Yes               |||||||0||0|1074|541| None            ||0| No                 ||||||0| No                            |0||0|||1| af98f4d4-a3ca-11ee-b194-0242ac110002 | mysql.slave_master_info |0|NULL| Replica has read all relay log; waiting for more updates |86400||||||||0|||||0||+----------------------------------+----------------+---------------+-------------+---------------+-------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+1 row in set,1warning(0.01 sec)

在这里插入图片描述

6、在3306主机上创建mydb1

在这里插入图片描述
在这里插入图片描述
此时刷新3316从数据库，发现没有mydb1

7、在3306主机上创建mydb2

在这里插入图片描述
此时刷新3316从数据库，发现从机复制了主机中的mydb2数据库到从机中

8、在3306主机上创建mydb3

在这里插入图片描述

9、在3306主机上创建mydb4

在这里插入图片描述

标签： mysql adb 数据库
本文转载自: https://blog.csdn.net/m0_65152767/article/details/142214434
版权归原作者 小丁学Java 所有， 如有侵权，请联系我们删除。
登录后发布评论

“mysql-搭建主从复制”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？