linux服务-mysql主从

1.主从简介

1.1诞生原因

数据库隐患：访问人多，服务器宕机
服务器时间长质量问题数据丢失

1.2主从作用

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

1.3形式

image

  • 1主1从
  • 主主复制（高可用）
  • 1主多从
  • 多主1从（5.7版本支持）
  • 联级复制

2.主从原理

image

  • 用户在主机写入（增删改），数据库改变，二进制日志记录生成日志备份（logdump）线程。从机的I/O线程请求读取，主机收到请求后传输给从机。从机写入中继日志（relaylog），从机的SQL线程读取中继日志，解析后重现主机的操作

3.主从复制配置

环境

数据库角色 ip 系统与数据库版本 有无数据
主 192.168.94.141 rhel8.2
mysql5.7		 有
从 192.168.94.140 rhel8.2
mysql5.7		 无

3.1创建同步用户

  • 看主表有的库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
  • 看从表有的库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
  • 主从关掉防火墙selinux
[root@matster ~]# systemctl stop firewalld.service 
[root@matster ~]# setenforce 0
  • 全备主库
备份前上读锁
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.001 sec)
//全备
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases>all-20210104.sql
[root@localhost ~]# ll
total 476
-rw-r--r--  1 root root 482035 Jan  4 10:38 all-20210104.sql
-rw-------. 1 root root   1179 Jan  4 00:36 anaconda-ks.cfg
//将数据传给从
[root@localhost ~]# scp /root/all-20210104.sql 'root'@'192.168.94.140':/root/
The authenticity of host '192.168.94.140 (192.168.94.140)' can't be established.
ECDSA key fingerprint is SHA256:3N9nRI4b+PknMMJ8yhtDWv/UjyjRNYizaOHP28glw0E.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.94.140' (ECDSA) to the list of known hosts.
root@192.168.94.140's password: 
all-20210104.sql                                                                   100%  471KB  68.1MB/s   00:00
  • 同步主从数据
//从端恢复数据
[root@localhost ~]# mysql -uroot -p <all-20210104.sql 
Enter password: 
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| company            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
  • 创建主从同步用户并授权（主上操作）
//创建用户(主)
mysql> create user 'repl'@'192.168.94.140' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)

//授权并刷新
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.94.140';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • 连接测试(从连主)
[root@localhost ~]# mysql -urepl -prepl123 -h192.168.94.141
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 9
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

3.2配置数据库

  • 配置主
[root@localhost ~]# vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin   
server-id=10
[root@localhost ~]# systemctl restart mariadb.service 
//查看
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
  • 配置从
[root@localhost ~]# vim /etc/my.cnf

[mysqld]
server-id=20
relay-log=mysql-relay-bin
//设置从
MariaDB [(none)]> change master to \
    -> master_host='192.168.94.141',\
    -> master_user='repl',\
    -> master_password='123456',\
    -> master_log_file='mysql-bin.000001',\
    -> master_log_pos=328;
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
//查看状态
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.94.141
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: mysql-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
  • 测试
//主增加记录mia
MariaDB [school]> select * from school.student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   24 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | zhangshan   |   26 |
|  5 | zhangshan   |   20 |
|  6 | lisi        |   27 |
|  7 | cchenshuo   |   10 |
|  8 | wangwu      |    3 |
|  9 | qiuyi       |   15 |
| 10 | qiuxiaotian |   20 |
| 11 | eason       |   28 |
+----+-------------+------+
11 rows in set (0.000 sec)
MariaDB [school]> insert student(name,age) value('mia',24);
Query OK, 1 row affected (0.001 sec)

//从查看
MariaDB [school]> select * from school.student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   24 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | zhangshan   |   26 |
|  5 | zhangshan   |   20 |
|  6 | lisi        |   27 |
|  7 | cchenshuo   |   10 |
|  8 | wangwu      |    3 |
|  9 | qiuyi       |   15 |
| 10 | qiuxiaotian |   20 |
| 11 | eason       |   28 |
| 12 | mia         |   24 |
+----+-------------+------+
12 rows in set (0.000 sec)

posted on 2021-01-04 10:56  fxx013  阅读(60)  评论(0)  编辑  收藏  举报

导航