MySQL主从复制

最新推荐文章于 2024-10-15 11:52:10 发布

zuo_yq

最新推荐文章于 2024-10-15 11:52:10 发布

阅读量147

点赞数

分类专栏： MySQL 文章标签： mysql linux mariadb

本文链接：https://blog.csdn.net/zuo_yq/article/details/125591741

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL主从复制

数据库角色	IP地址
主数据库	192.168.10.101/24
从数据库	192.168.10.102/24

安装主数据库

[root@master ~]# systemctl disable --now firewalld
[root@master ~]# setenforce 0
[root@master ~]# dnf -y install mariadb mariadb-server
[root@master ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

# 数据库初始化
[root@master ~]# mysql_secure_installation

# 配置主从复制
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

log_bin=mysql-bin
server_id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# 重启服务
[root@master ~]# systemctl restart mariadb

# 授权用户
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log 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)]> grant replication slave on *.* to 'zyq'@'%' identified by '1';
Query OK, 0 rows affected (0.002 sec)

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

安装从数据库

[root@slave ~]# systemctl disable --now firewalld
[root@slave ~]# setenforce 0
[root@slave ~]# dnf -y install mariadb mariadb-server
[root@slave ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

# 初始化数据库
[root@slave ~]# mysql_secure_installation

# 配置主从复制，如果有数据需要先导入
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

server_id=2

# 重启服务
[root@slave ~]# systemctl restart mariadb

# 配置主服务器信息
[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-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)]> change master to
    -> master_host='192.168.10.101',
    -> master_port=3306,
    -> master_user='zyq',
    -> master_password='1';
Query OK, 0 rows affected (0.011 sec)

# 查看主从是否开启
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.10.101
                   Master_User: zyq
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 631
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 930
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
.......

验证

# 在主服务器创建数据库
MariaDB [(none)]> create database zyq;
Query OK, 1 row affected (0.002 sec)

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

# 在从服务器查看是否同步
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| zyq                |
+--------------------+
4 rows in set (0.001 sec)