mysql主从

最新推荐文章于 2024-04-15 16:13:05 发布

aleihello

最新推荐文章于 2024-04-15 16:13:05 发布

阅读量152

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/aleihello/article/details/106942832

版权

1.主从简介

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

1.1主从作用

做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

1.2主从方式

在这里插入图片描述

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制

2. 主从复制原理

在这里插入图片描述
主从复制步骤：
1.从库的IO线程向主库的主进程发送请求，主库验证从库，交给主库IO线程负责数据传输;
2.主库IO线程对比从库发送过来的master.info里的信息，将binlog文件信息，偏移量和binlog文件名等发送给从库
3.从库接收到信息后，将binlog信息保存到relay-bin中，同时更新master.info的偏移量和binlog文件名
4.从库的SQL线程不断的读取relay-bin的信息，同时将读到的偏移量和文件名写道relay-log.info文件，binlog信息写进自己的数据库，一次同步操作完成。
5.完成上次同步后，从库IO线程不断的向主库IO线程要binlog信息
6.从库如果也要做主库，也要打开log_bin 和log-slave-update参数

3. 主从复制配置

主从复制配置步骤：

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

环境说明：

数据库角色	ip (主机名)	有无数据
主数据库	192.168.30.130（localhost）	有
从数据库	192.168.30.100（client）	无

3.1mysql主从配置

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

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

//先查看主库有哪些库
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| teacher            |
| test               |
+--------------------+
6 rows in set (0.00 sec)

//再查看从库有哪些库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出
//备份主库并将备份文件传送到从库
[root@localhost ~]# mysqldump --all-databases > /opt/all-20200624.sql
[root@localhost ~]# ls /opt/
all-20200624.sql
[root@localhost ~]# scp /opt/all-20200624.sql root@192.168.30.100:/opt
The authenticity of host '192.168.30.100 (192.168.30.100)' can't be established.
ECDSA key fingerprint is SHA256:Ff2YCGeFM2cIJ3VUVrcrDPUT1AZNAS+3vThA8g9lNVA.
ECDSA key fingerprint is MD5:2f:98:7a:fa:23:92:f6:40:dd:d1:0e:b8:80:14:5f:f3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.30.100' (ECDSA) to the list of known hosts.
root@192.168.30.100's password: 
all-20200624.sql                         100%  504KB  43.8MB/s   00:00   
//解除主库的锁表状态，直接退出交互式界面即可
MariaDB [(none)]> exit
Bye
[root@localhost ~]# 

//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@client ~]# mysql -uroot -p123456 < /opt/all-20200624.sql 

[root@client ~]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| teacher            |
| test               |
+--------------------+

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

MariaDB [(none)]> create user 'haha'@192.168.30.100 identified by 'haha123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'haha'@'192.168.30.100.';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.1.3 配置主数据库

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
log-bin=mysql-bin
server-id=10
//重启mysql服务
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      50        *:3306                  *:*                  
LISTEN      0      128       *:22                    *:*                  
LISTEN      0      100    127.0.0.1:25                    *:*                  
LISTEN      0      128      :::22                   :::*                  
LISTEN      0      100     ::1:25                   :::*    
//查看主库的状态
MariaDB [(none)]> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

3.1.4 配置从数据库

[root@client ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
relay-log=mysql-relay-bin
server-id=20
//重启从库的mysql服务
[root@client ~]# systemctl restart mariadb
[root@client ~]# ss -tanl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      50      *:3306                *:*                  
LISTEN      0      128     *:22                  *:*                  
LISTEN      0      128    :::22                 :::*                  
//配置并启动主从复制
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.30.130',
    -> MASTER_USER='haha',
    -> MASTER_PASSWORD='haha123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
//查看从服务器状态
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.30.130
                  Master_User: haha
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.1.5 测试验证

MariaDB [teacher]> insert into hehe values (1,'tee',20),(2,'tom',15);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [teacher]> select * from hehe;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tee  |   20 |
|  2 | tom  |   15 |
+----+------+------+
2 rows in set (0.00 sec)

MariaDB [teacher]>

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

MariaDB [(none)]> use teacher;
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
MariaDB [teacher]> select * from hehe;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tee  |   20 |
|  2 | tom  |   15 |
+----+------+------+
2 rows in set (0.00 sec)

aleihello

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql主从

1.主从简介主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。1.1主从作用做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用s
复制链接

扫一扫