【无标题】

最新推荐文章于 2024-07-31 12:20:16 发布

m0_67953532

最新推荐文章于 2024-07-31 12:20:16 发布

阅读量95

点赞数

文章标签： linux

本文链接：https://blog.csdn.net/m0_67953532/article/details/132738784

版权

文章目录

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

mysql主从

主从作用：

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

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

3 备份，避免影响业务

主从结构：

一主一从

主主复制

一主多从

多主一从

联级复制

主从复制原理：

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

主从配置

` 提示：这里使用的是Ubuntu22.04 安装mariadb数据库，本次实验采用一主一从。

主	192.168.1.10
从	192.168.1.100
…

root@master:/home# apt-get  update 
Hit:1 http://archive.ubuntu.com/ubuntu jammy InRelease
Get:2 http://archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB]
..........................................
root@master:/# apt-get install mariadb*
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Note, selecting 'mariadb-tokudb-engine-5.5' for glob 'mariadb*'
Note, selecting 'mariadb-server-core-5.5' for glob 'mariadb*'
Note, selecting 'mariadb-plugin-spider' for glob 'mariadb*'
Note, selecting 'mariadb-server' for glob 'mariadb*'
..............................
mariadb-server-10.6 is already the newest version (1:10.6.12-0ubuntu0.22.04.1).
mariadb-server-core-10.6 is already the newest version (1:10.6.12-0ubuntu0.22.04.1).
mariadb-test is already the newest version (1:10.6.12-0ubuntu0.22.04.1).
mariadb-test-data is already the newest version (1:10.6.12-0ubuntu0.22.04.1).
0 upgraded, 0 newly installed, 0 to remove and 106 not upgraded.
root@master:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

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)]> show databases;
###  设置数据库密码 
MariaDB [(none)]> set password= password('whd5555');
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> quit
###  账户登录数据库
root@master:/# mysql -uroot -pwhd5555
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

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:/# mysql -uroot -pwhd5555
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

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)]> grant replication slave on *.* to 'whd'@'192.168.1.100' identified by 'whd123';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
###  配置主数据库文件 
root@master:/# vim /etc/mysql/mariadb.conf.d/50-server.cnf

server-id = 10
log-bin = mysql_bin

###  重启数据库服务 
root@master:/# systemctl restart mariadb.service 
###  检查日志
root@master:/# ls /var/lib/mysql/
aria_log.00000001   debian-10.6.flag    ibdata1             multi-master.info   mysql_bin.index     #rocksdb/
aria_log_control    groonga.log         ib_logfile0         mysql/              mysql_upgrade_info  sys/
ddl_recovery.log    ib_buffer_pool      ibtmp1              mysql_bin.000001    performance_schema/ ### 查看主库状态：
root@master:/# mysql -uroot -pwhd5555
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 61
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1-log Ubuntu 22.04

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)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
### 从数据库操作：
###  配置从数据库 文件：
root@dns:~# vim /etc/mysql/mariadb.conf.d/50-server.cnf

server-id = 20
relay-log = whd_bin
root@dns:~# systemctl restart mariadb.service 
MariaDB [(none)]> change master to
    -> master_host='192.168.1.10',  master_user='whd', master_password='whd123', master__log_file='mysql_bin.000001', master_log_pos=328;
Query OK, 0 rows affected (0.007 sec)

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

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: 192.168.1.10
                   Master_User: root
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000001
           Read_Master_Log_Pos: 1965
                Relay_Log_File: mysqld-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql_bin.000001
              Slave_IO_Running: yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
..........................................
``

###  在主数据库中创建数据：
 MariaDB [(none)]> create database dasdsadas;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> create database dasdsadasdwd;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> create database dadsadasd;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| dadsadasd          |
| dasdsadas          |
| dasdsadasdwd       |
| dsefrfvgt          |
| ewrds              |
| ewrfrefs           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| whd                |
| whdd               |
| whde               |
+--------------------+
13 rows in set (0.004 sec)
###  查看从数据库： 
root@dns:/# mysql -uroot -pwhd5555
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 77
Server version: 10.6.7-MariaDB-2ubuntu1.1 Ubuntu 22.04

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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| dadsadasd          |
| dasdsadas          |
| dasdsadasdwd       |
| dsefrfvgt          |
| ewrds              |
| ewrfrefs           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| whd                |
| whdd               |
| whde               |
+--------------------+