• Mysql主从

    Mysql主从

    主从简介

    主从复制也叫主从同步，用于备份数据防止意外，一台数据库存放数据有丢失风险，所以需要有多台数据库来保障安全。

    主从作用

    • 实时灾备，用于故障切换

    • 读写分离，提供查询服务

    • 备份，避免影响业务

    主从形式

    • 一主一从
    • 主主复制
    • 一主多从---（扩展系统读取的性能，因为读是在从库读取的）
    • 多主一从
    • 联级复制

    mysql1

    主从复制原理

    主从复制步骤：

    主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程

    从库生成两个线程，一个I/O线程，一个SQL线程

    I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中

    SQL线程，会读取relay log文件中的日志，并解析成具体操作重放，来实现主从的操作一致，达到最终数据一致的目的

    mysql2

    主从复制配置

    主从复制配置步骤：

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

    环境1说明：

    数据库角色 IP 应用与系统版本 有无数据
    主数据库master 192.168.100.1 redhat8 mariadb10.3.17 无数据
    从数据库slave 192.168.100.2 redhat8 mariadb10.3.17 无数据
    准备工作（安装mysql并且关闭防火墙）
    [root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl enable --now mariadb
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
    在主数据库里创建一个同步账号授权给从数据库使用
    master:
[root@master ~]# mysql
MariaDB [(none)]> grant replication slave on *.* to 'yqh'@'192.168.100.2' identified by '123456';
Query OK, 0 rows affected (0.003 sec)

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

slave:
//测试连接
[root@slave ~]# mysql -uyqh -p123456 -h192.168.100.1
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 10
Server version: 10.3.17-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)]>
    配置主数据库
    [root@master ~]# vim /etc/my.cnf
[mysqld]
log-bin = mysql_bin		//启用binlog日志
server-id = 10			//数据库服务器唯一标识符，从库的server-id值必须比主库的大
[root@master ~]# systemctl restart mariadb
[root@master ~]# mysql

//查看主库的状态
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@slave ~]# vim /etc/my.cnf
[mysqld]
relay-log = myrelay		//启用中继日志relay-log
server-id = 20			//设置从库的唯一标识符，从库的server-id值必须大于主库的该值

[root@slave ~]# systemctl restart mariadb

//配置并启动主从复制
[root@slave ~]# mysql
MariaDB [(none)]> change master to master_host='192.168.100.1',master_user='yqh',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=328;
Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)

//查看从服务器状态
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.100.1
                   Master_User: yqh
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: myrelay.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql_bin.000001
              Slave_IO_Running: Yes				//此处必须为Yes
             Slave_SQL_Running: Yes				//此处必须为Yes
						······
						······
1 row in set (0.001 sec)
    测试验证

    在主服务器创建school数据库在创建student表并插入数据：

    [root@master ~]# mysql
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use school
Database changed
MariaDB [school]> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint,score float);
Query OK, 0 rows affected (0.008 sec)
MariaDB [school]> insert student(name,age,score) values('tom',20,100),('jerry',18,90),('zhangshan',23,97.5),('lisi',21,60.5);
Query OK, 4 rows affected (0.004 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [school]> select * from student;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  1 | tom       |   20 |   100 |
|  2 | jerry     |   18 |    90 |
|  3 | zhangshan |   23 |  97.5 |
|  4 | lisi      |   21 |  60.5 |
+----+-----------+------+-------+
4 rows in set (0.001 sec)

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

    [root@slave ~]# mysql

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> use school;
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 [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.001 sec)

MariaDB [school]> select * from student;
+----+-----------+------+-------+
| id | name      | age  | score |
+----+-----------+------+-------+
|  1 | tom       |   20 |   100 |
|  2 | jerry     |   18 |    90 |
|  3 | zhangshan |   23 |  97.5 |
|  4 | lisi      |   21 |  60.5 |
+----+-----------+------+-------+
4 rows in set (0.001 sec)

    环境2说明：

    数据库角色 IP 应用与系统版本 有无数据
    主数据库master 192.168.100.1 redhat8 mariadb10.3.17 有数据
    从数据库slave 192.168.100.2 redhat8 mariadb10.3.17 无数据
    准备工作（安装mysql并且关闭防火墙）
    [root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl enable --now mariadb
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

    模拟有数据的数据库：

    [root@master ~]# mysql
MariaDB [(none)]> create database info;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use info;
Database changed
MariaDB [info]> create table basic(id int not null primary key auto_increment,name varchar(50),job varchar(50));
Query OK, 0 rows affected (0.007 sec)

MariaDB [info]> insert basic (name,job) values ('tom','engineer'),('jerry','office');
Query OK, 2 row affected (0.002 sec)

MariaDB [info]> create table salary(name varchar(50),salary float);
Query OK, 0 rows affected (0.006 sec)

MariaDB [info]> insert salary values('tom',8000),('jerry',8500),('zhangshan',9000),('lisi',7500);
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [info]> show tables;
+----------------+
| Tables_in_info |
+----------------+
| basic          |
| salary         |
+----------------+
2 rows in set (0.001 sec)

MariaDB [info]> select * from basic;
+----+-------+----------+
| id | name  | job      |
+----+-------+----------+
|  1 | tom   | engineer |
|  2 | jerry | office   |
+----+-------+----------+
2 rows in set (0.001 sec)

MariaDB [info]> select * from salary;
+-----------+--------+
| name      | salary |
+-----------+--------+
| tom       |   8000 |
| jerry     |   8500 |
| zhangshan |   9000 |
| lisi      |   7500 |
+-----------+--------+
4 rows in set (0.001 sec)
    确保从数据库与主数据库里的数据一样

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

    //先查看主库有哪些库
[root@master ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| info               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

//再查看从库有哪些库
[root@slave ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.001 sec)
//此锁表的终端必须在备份完成以后才能退出
MariaDB [(none)]> use info;
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 [info]> insert basic(name,job) values('zhangshan','teacher');
//无法写入数据
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

//另开一个终端：
[root@master ~]# mysqldump -uroot --all-databases > all.sql
[root@master ~]# ls
all.sql  anaconda-ks.cfg
[root@master ~]# scp all.sql 192.168.100.2:/
root/
The authenticity of host '192.168.100.2 (192.168.100.2)' can't be established.
ECDSA key fingerprint is SHA256:yvB25r1iyD1DrH7KQlo2thy8nREkrS7Qddd1KQ5ucsQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.100.2' (ECDSA) to the list of known hosts.
root@192.168.100.2's password: 
all.sql   100%  469KB  27.3MB/s   00:00 

//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye

//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@slave ~]# mysql < all.sql
[root@slave ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| info               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
    在主数据库里创建一个同步账号授权给从数据库使用
    master:
[root@master ~]# mysql
MariaDB [(none)]> grant replication slave on *.* to 'yqh'@'192.168.100.2' identified by '123456';
Query OK, 0 rows affected (0.001 sec)

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

slave:
//测试连接
[root@slave ~]# mysql -uyqh -p123456 -h192.168.100.1
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 10
Server version: 10.3.17-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)]>
    配置主数据库
    [root@master ~]# vim /etc/my.cnf
[mysqld]
log-bin = mysql_bin		//启用binlog日志
server-id = 10			//数据库服务器唯一标识符，从库的server-id值必须比主库的大
[root@master ~]# systemctl restart mariadb
[root@master ~]# mysql

//查看主库的状态
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@slave ~]# vim /etc/my.cnf
[mysqld]
relay-log = myrelay		//启用中继日志relay-log
server-id = 20			//设置从库的唯一标识符，从库的server-id值必须大于主库的该值

[root@slave ~]# systemctl restart mariadb

//配置并启动主从复制
[root@slave ~]# mysql
MariaDB [(none)]> change master to master_host='192.168.100.1',master_user='yqh',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=328;
Query OK, 0 rows affected (0.014 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

//查看从服务器状态
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.100.1
                   Master_User: yqh
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: myrelay.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql_bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
            			······
           				······
1 row in set (0.001 sec)
    测试验证

    在从服务器上查看原有数据：

    [root@slave ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| info               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> use info;
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 [info]> show tables;
+----------------+
| Tables_in_info |
+----------------+
| basic          |
| salary         |
+----------------+
2 rows in set (0.001 sec)

MariaDB [info]> select * from basic;
+----+-------+----------+
| id | name  | job      |
+----+-------+----------+
|  1 | tom   | engineer |
|  2 | jerry | office   |
+----+-------+----------+
2 rows in set (0.001 sec)

    在主服务器上插入数据：

    [root@master ~]# mysql
MariaDB [(none)]> use info;
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 [info]> insert basic(name,job) values('zhangshan','teacher');
Query OK, 1 row affected (0.002 sec)

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

    [root@slave ~]# mysql
MariaDB [(none)]> use info;
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 [info]> select * from basic;
+----+-----------+----------+
| id | name      | job      |
+----+-----------+----------+
|  1 | tom       | engineer |
|  2 | jerry     | office   |
|  3 | zhangshan | teacher  |
+----+-----------+----------+
3 rows in set (0.001 sec)
  • 相关阅读:
    链接程序和库
    setjmp.h 文件解析与使用
    程序中的.dll .lib .def 和 .exp文件
    GDB开发测试相关
    gdb调试方法
    gdb代码分析
    ln对目录下所有文件做软链接
    python pexecpt模块 实现自动交互
    vscode中less自动输出为wxss或者css
    cmake
  • 原文地址：https://www.cnblogs.com/yuqinghao/p/14213596.html
Copyright © 2020-2023  润新知