mysql主从

主从简介

主从作用

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

主从形式

主从

主主 ：两个加起来才算完整的数据，最好是创建一个共享文件，将两台数据都挂载在这个共享文件中

一主多从；主服务器只有一台，有多台从服务器备份，

多主一从：从相当于一个共享挂载点，当一台服务器发生损坏，不影响使用

主从复制原理

主从复制步骤：

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

主从复制配置

主从复制配置步骤：

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

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

环境说明：

mysql安装

分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，mysql详解及其安装

mysql主从配置

192.168.87.128是主数据库
192.168.87.129是从数据库

确保从数据库与主数据库的数据一样

为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中
1.在主中常见数据库和表

[root@zzz ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.39 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> create database zbc;
Query OK, 1 row affected (0.00 sec)

mysql> create database www;
Query OK, 1 row affected (0.00 sec)

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

mysql> 
mysql> use zbc
Database changed
mysql> create table zuoye (id int not null,name varchar(50)not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> create table zuoye (id int not null,name varchar(50)not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)
mysql> insert zuoye (id,name,age)values(1,'tom',22),(2,'wang',21),(3,'zhang',24);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from zuoye;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   22 |
|  2 | wang  |   21 |
|  3 | zhang |   24 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> 

查看从库数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

将主数据库的数据全被传到从数据库上实现一致

[root@zzz ~]# mysqldump -uroot -p@123 --all-databases > all-2022.sql
[root@zzz ~]# mv all-2022.sql /opt
[root@zzz ~]# cd /opt/
[root@zzz opt]# ls
all-2022.sql

[root@zzz opt]# scp /opt/all-2022.sql root@192.168.87.129:/opt/
The authenticity of host '192.168.87.129 (192.168.87.129)' can't be established.
ECDSA key fingerprint is SHA256:GSJLlJgtoagBXmMbWp2t+aTDa0qy8ti+yZX3/Ujycto.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.87.129' (ECDSA) to the list of known hosts.
root@192.168.87.129's password: 
all-2022.sql                                                  100%  861KB  46.4MB/s   00:00    
[root@zzz opt]# 


在从数据库上查看并导入数据
[root@localhost ~]# cd /opt/
[root@localhost opt]# ll
total 864
-rw-r--r--. 1 root root 881324 Jul 31 14:56 all-2022.sql
[root@localhost opt]#

在主数据库里创建一个同步账号授权给从数据库使用

mysql> create user 'zbc'@'192.168.87.129' identified by '@123';
Query OK, 0 rows affected (0.00 sec)

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

主从数据库环境配置

在主数据库配置文件/etc/my.cnf中添加如下

log-bin=mysql-bin   //启用binlog日志
server-id=1     //数据库服务器唯一标识符，主库的server-id值必须比从库的大
重启查看
[root@zzz ~]# systemctl restart mysqld
[root@zzz ~]# ss -antl
State       Recv-Q      Send-Q            Local Address:Port             Peer Address:Port      
LISTEN      0           128                     0.0.0.0:22                    0.0.0.0:*         
LISTEN      0           80                            *:3306                        *:*         
LISTEN      0           128                        [::]:22                       [::]:*         
[root@zzz ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-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> 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>

在主数据库配置文件/etc/my.cnf中添加如下

server-id=20	//设置从库的唯一标识符(数值一定要比主数据库的大)
relay-log=mysql-relay-bin	 //启用中继日志relay-log
重启服务
[root@localhost opt]# systemctl restart mysqld
[root@localhost opt]# ss -antl
State         Recv-Q        Send-Q               Local Address:Port               Peer Address:Port        
LISTEN        0             128                        0.0.0.0:22                      0.0.0.0:*           
LISTEN        0             128                           [::]:22                         [::]:*           
LISTEN        0             80                               *:3306                          *:*           
[root@localhost opt]#

配置主从复制

mysql> change master to
    -> master_host='192.1683.87.128',
    -> master_user='zbc',
    -> master_password='@123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>

mysql> 
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.87.128
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1407
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: yes		
            Slave_SQL_Running: Yes

测试验证

mysql> insert zuoye(id,name,age)value(5,'hello',22);
Query OK, 1 row affected (0.00 sec)

mysql> select * from zuoye;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   22 |
|  2 | wang  |   21 |
|  3 | zhang |   24 |
|  4 | name  |   22 |
|  5 | hello |   22 |
+----+-------+------+
5 rows in set (0.00 sec)

mysql> 


从
mysql> use zbc
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 zuoye;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   22 |
|  2 | wang  |   21 |
|  3 | zhang |   24 |
|  4 | name  |   22 |
|  5 | hello |   22 |
+----+-------+------+
5 rows in set (0.00 sec)