（二）mysql一主一从

时间：2022-11-20 11:57:59

1、环境说明

操作系统：centos7.9
mysql版本：mysql5.7
安装方式：yum源安装

2、环境准备

  1. 准备两台全新的机器
  2. 完成系初始化操作
    1. 关闭防火墙
    2. 关闭selinux
    3. 配置yum源
  3. 安装mysql5.7

3、主从原理

（二）mysql一主一从

4、master配置

  1. 创建用于同步数据的用户
mysql> grant replication slave replication client on *.* to 'rep'@'192.168.100.%' idenfitied by 'Admin@123';

replication slave replication client -- 用于主从复制的权限
'rep'	-- 用户名
'192.168.100.%' -- 远程登录的主机
  1. 开启二进制日志
[root@master01 ~]# vim /etc/my.cnf  
[mysqld]
···
log_bin			# 开启二进制日志文件，默认是注释的
server-id=1		# mysql服务的id号，搭建集群需要使用且每台的id号不同

重启mysql服务
systemctl restart mysqld
  1. 备份原有的数据库(如果是新的数据库则略过此步骤)
[root@master01 ~]# mysqldump -uroot -p'Admin@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

--single-transaction 参数来获得一致性备份，减少锁表
--master-data = 2 参数记录主库 binlog 信息
--all-databases 备份所有的数据

[root@master01 ~]# ll
-rw-r--r--. 1 root root 881287 Nov 20 18:35 2022-11-20-mysql-all.sql

将备份的数据拷贝到从数据库中
[root@master01 ~]# scp 2022-11-20-mysql-all.sql master02:/root/

5、slave配置

  1. 测试rep账号
[root@master02 ~]# mysql -u rep -p'Admin@123' -h 192.168.100.41
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 15
Server version: 5.7.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>
  1. 启动服务器的序列号
[root@master02 ~]# vim /etc/my.cnf  
[mysqld]
···
log_bin		# 从节点可以不用开启二进制日志，没有节点再向从节点同步
server-id=1	# mysql服务的id号，搭建集群需要使用且每台的id号不同

重启mysql服务
systemctl restart mysqld
  1. 同步备份的数据
mysql> set sql_log_bin=0;	#临时关闭二进制日志，只在当前会话生效
mysql> source 2022-11-20-mysql-all.sql
查看测试数据是否同步
mysql> select * from  blog.user;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
|    2 | lily |
+------+------+
3 rows in set (0.00 sec)
  1. 设置住服务器
mysql> change master to mstart_host='master01', master_user='rep' master_password='Admin@123',master_log_file='master01-bin.000002',master_log_pos=154;

master_log_file		-- 指定二进制日志位置
master_log_pos		-- 指定从二进制日志的哪一行开始同步
ps：这两条信息在备份的sql文件中的22行，如果是新数据库不需要添加
-- CHANGE MASTER TO MASTER_LOG_FILE='master01-bin.000002', MASTER_LOG_POS=154;
  1. 启动从节点
mysql> start slave;
查看从节点的状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master01
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master01-bin.000002
          Read_Master_Log_Pos: 413
               Relay_Log_File: master02-relay-bin.000002
                Relay_Log_Pos: 582
        Relay_Master_Log_File: master01-bin.000002
             Slave_IO_Running: Yes	#读取二级制日志的IO线程
            Slave_SQL_Running: Yes	#执行中继日志的SQL线程
              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: 413
              Relay_Log_Space: 792
              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: 95d807d0-68b9-11ed-ac83-000c29fa43dc
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified


再次查看你数据库中的测试数据，新生成的数据已经同步成功
mysql> select * from  blog.user;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
|    2 | lily |
|    3 | jack |
+------+------+
3 rows in set (0.00 sec)

https://www.bilibili.com/video/BV1oA411i73S?p=31&vd_source=b5bfcdfba8f2fb2a2247899ae27dd751

标签：

相关文章