#mysql主从
文章目录
###主从简介
###主从作用
实时灾备,用于故障切换
读写分离,提供查询服务
备份,避免影响业务
###主从形式
主从
主主 :两个加起来才算完整的数据,最好是创建一个共享文件,将两台数据都挂载在这个共享文件中
一主多从;主服务器只有一台,有多台从服务器备份,
多主一从:从相当于一个共享挂载点,当一台服务器发生损坏,不影响使用
###主从复制原理
主从复制步骤:
主库将所有的写操作记录到binlog日志中并生成一个log dump线程,将binlog日志传给从库的I/O线程
从库生成两个线程,一个I/O线程,一个SQL线程
I/O线程去请求主库的binlog(二进制日志),并将得到的binlog日志写到relay log(中继日志) 文件中
SQL线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,达到最终数据一致的目的
###主从复制配置
主从复制配置步骤:
确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库(修改配置文件)
配置从数据库(修改配置文件)
需求:
搭建两台MySQL服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作
环境说明:
数据库角色 IP 应用与系统版本 有无数据
主数据库 172.16.12.128 centos8/redhat8 mysql-5.7 有数据
从数据库 172.16.12.129 centos8/redhat8mysql-5.7 无数据
###mysql安装
分别在主从两台服务器上安装mysql-5.7版本,此处略过安装步骤,mysql详解及其安装
###mysql主从配置
192.168.87.128是主数据库
192.168.87.129是从数据库
确保从数据库与主数据库的数据一样
为确保从数据库与主数据库里的数据一样,先全备主数据库并还原到从数据库中
[root@localhost ~]# 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>
将主数据库的数据全被传到从数据库上实现一致
localhost/
[root@localhost opt]# ls
all-2022.sql
[root@localhost 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@localhost 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.200.128' identified by '@123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zbc'@'192.168.200.128';
Query OK, 0 rows affected (0.00 sec)
主从数据库环境配置
在主数据库配置文件/etc/my.cnf中添加如下
log-bin=mysql-bin //启用binlog日志
server-id=1 //数据库服务器唯一标识符,主库的server-id值必须比从库的大
重启查看
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 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@localhost ~]# 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.168.200.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.300.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)