1、简介
数据库运⾏时，⼀些因素可能会导致服务运⾏不正常，⽤户访问数据受阻。对于互联⽹公 司，尤其是购物⽹站⽽⾔，这种情况造成的损失是⽆法估量的。因此，对数据库进⾏“备份” 也是必不可少的操作。当主要的数据库死机时，系统能够快速地切换到备⽤的数据库上。本 章将详细介绍数据库集群中的主从复制原理和操作流程。
2、主从复制集群的⼯作流程如下
（1）主服务器上⾯的任何修改都会通过⾃⼰的I/O线程保存在⼆进制⽇志⾥。
（2）从服务器上⾯也会启动⼀个I/O线程，通过配置好的⽤户名和密码连接到主服务器上⾯ 请求读取⼆进制⽇志，然后把读取到的⼆进制⽇志写到本地的⼀个中继⽇志的末端，并将读 取到的主服务器端的⼆进制⽇志的⽂件名和位置记录到master-info⽂件中，以便在下⼀次读 取的时候能够清楚地告诉主服务器：我需要某个⼆进制⽇志的某个位置之后的⽇志内容，请 发给我。
（3）从服务器的SQL线程检测到中继⽇志中新增加了内容后，会⻢上解析⽇志中的内容，并 在⾃身执⾏。需要注意，每个从服务器都会收到主服务器⼆进制⽇志中的全部内容的副本， 除⾮另⾏指定，否则，从服务器将执⾏来⾃主服务器⼆进制⽇志⽂件的所有的操作语句。另 外，从服务器每次进⾏同步时，都会记录⼆进制⽇志坐标（坐标包含⽂件名和从主服务器上 读取的位置，即master-info)，以便下次连接使⽤。由于每个从服务器分别记录了当前⼆进制 ⽇志的位置，因此可以断开从服务器的连接，重新连接，然后恢复处理。
（4）在从服务器上删除的数据，在主服务器上重新创建，从服务器并不会同步过来，所以不要轻易的在从服务器上做删除操作。
3、基本架构
在 MySQL 的主从复制集群中，主数据库既要负责写操作⼜要负责为从数据库提供⼆进制⽇ 志，这⽆疑增加了主数据库的压⼒。此时可以将⼆进制⽇志只给某⼀个从服务器使⽤，并在 该从服务器上开启⼆进制⽇志，将该从服务器⼆进制⽇志分发给其他的从服务器；或者，这 个从服务器不进⾏数据的复制，只负责将⼆进制⽇志转发给其他的从服务器。这样，不仅可 以减少主服务的压⼒，还可以提⾼整体架构的性能。
⼀主多从原理如图所示
4、 多源复制架构
MySQL 5.7开始⽀持多源复制架构，即多个主服务器连接同⼀个从服务器（多主⼀从）。
多源复制中加⼊了⼀个叫作Channel的概念，每⼀个Channel都是⼀个独⽴的Slave，都有 ⼀个IO线程和⼀个SQL线程， 基本原理和普通的复制⼀样。 在对 Slave执⾏ CHANGEMASTER 语句时，只需要在每个语句最后使⽤for channel 关键字来进⾏区分即 可。
需要注意，在使⽤这种架构时，需要在从数据库的my.cnf配置⽂件中将master-inforepository、relay-log-info-repository参数设置为TABLE， 否则系统会报错。 相⽐于传统的⼀主⼀从、多主多从，在多源复制架构中，管理者可以直接在从数据库中进⾏ 数据备份， 不会影响线上业务的正常运⾏。
多源复制架构将多台数据库连接在⼀起，可以实 现合并表碎⽚，管理者不需要为每个数据库都制作⼀个实例，减少了维护成本，使⽤这种⽅ 式在后期进⾏数据统计时也会⾮常⾼效。
5、多主多从复制
在⼀主的情况下，主节点发⽣故障会影响全局的写⼊，设置双主或者多主集群可以避免单点 故障的发⽣。
前⾯介绍过的⼀主⼀从架构为基础，只需要集群中再加⼊⼀个主服务器master2和⼀个从 服务器slave2即可实现双主双从和多源复制架构。
6、复制模式
MySQL主从复制的⽅式可以分为异步复制、同步复制和半同步复制。
1．异步复制
异步复制为MySQL默认的复制⽅式， 主数据库执⾏完客户端提交的事务后会⽴即将结果返 给客户端，并不关⼼从数据库是否已经接收并进⾏了处理。 从⽇志的⻆度讲，在主数据库将 事务写⼊⼆进制⽇志⽂件后，主数据库只会通知dump线程发送这些新的⼆进制⽇志⽂件， 然后主数据库就会继续处理提交操作，并不考虑这些⼆进制⽇志已经传到每个从数据库节点 上。在使⽤异步复制模式时，如果主数据库崩溃，可能会出现主数据库上已经提交的事务并 没有传到从数据库上的情况，如果此时将从数据库提升为主数据库，很有可能导致新主数据 库上的数据不完整。
2．同步复制
同步复制是指主数据库向从数据库发送⼀个事务,并且所有的从数据库都执⾏了该事务后才会 将结果提交给客户端。因为需要等待所有的从数据库执⾏完该事务，所以在使⽤同步复制 时，主数据库完成⼀个事务的时间会被拉⻓，系统性能受到严重影响。
3．半同步复制
半同步复制介于同步复制与异步复制之间，主数据库只需要等待⾄少⼀个从数据库节点收到 并且更新⼆进制⽇志到中继⽇志⽂件即可，不需要等待所有从数据库给主数据库反馈。如此 ⼀来，不仅节省了很多时间，⽽且提⾼了数据的安全性。另外， 由于整个过程产⽣通信，所 以建议在低延时的⽹络中使⽤半同步复制。
环境
系统 ：Redhat 9
mariadb版本：10.5.16
主机一：master 192.168.200.133
主机二：salve 192.168.200.128
步骤
先给两台设备进⾏命名为master和savle
[root@admin ~]# hostnamectl set-hostname master
[root@admin ~]# bash
[root@master ~]#
[root@localhost ~]# hostnamectl set-hostname salve
[root@localhost ~]# bash
[root@salve ~]# 对两台设备进⾏关闭防⽕墙和selinux操作
[root@master ~]# systemctl stop firewalld.service
[root@master ~]# systemctl disable firewalld.service
Removed "/etc/systemd/system/multi-user.target.wants/firewalld.service".
Removed "/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service".
[root@master ~]# setenforce 0
[root@salve ~]# systemctl stop firewalld.service
[root@salve ~]# systemctl disable firewalld.service
Removed "/etc/systemd/system/multi-user.target.wants/firewalld.service".
Removed "/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service".
[root@salve ~]# setenforce 0配置本地yum仓库、并安装yum工具 配置阿里云网络源
#master
[root@master ~]# mount /dev/sr0 /media/
mount: /media: WARNING: source write-protected, mounted read-only.
[root@master ~]# cat /etc/yum.repos.d/local.repo
[AppStream]
name=AppStream
baseurl=file:///media/AppStream
enabled=1
gpgcheck=0
[BaseOS]
name=BaseOS
baseurl=file:///media/BaseOS
enabled=1
gpgcheck=0
[root@master ~]#
[root@master ~]# yum -y install yum-utils.noarch
[root@master ~]# yum-config-manager --add-repo https://mirrors.aliyun.com/redhat/rhel/rhel-9-beta/rhel-9-beta.repo
正在更新 Subscription Management 软件仓库。
无法读取客户身份
本系统尚未在权利服务器中注册。可使用 subscription-manager 进行注册。
添加仓库自：https://mirrors.aliyun.com/redhat/rhel/rhel-9-beta/rhel-9-beta.repo
[root@master ~]#
#salve
[root@savle mnt]# mount /dev/sr1 /mnt/
[root@savle mnt]# cat /etc/yum.repos.d/local.repo
[AppStream]
name=AppStream
baseurl=file:///mnt/AppStream
enabled=1
gpgcheck=0
[BaseOS]
name=BaseOS
baseurl=file:///mnt/BaseOS
enabled=1
gpgcheck=0
[root@savle mnt]#
[root@savle ~]# yum -y install yum-utils.noarch
[root@savle ~]# yum-config-manager --add-repo https://mirrors.aliyun.com/redhat/rhel/rhel-9-beta/rhel-9-beta.repo
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
Adding repo from: https://mirrors.aliyun.com/redhat/rhel/rhel-9-beta/rhel-9-beta.repo
[root@savle ~]# 修改源的enabled项为1 、以及地址项
#master
[root@master ~]# sed -i -e 's/enabled = 0/enabled = 1/g' /etc/yum.repos.d/rhel-9-beta.repo
[root@master ~]# sed -i -e 's/downloads.redhat.com/mirrors.aliyun.com/g' /etc/yum.repos.d/rhel-9-beta.repo
[root@master ~]#
#savle
[root@savle ~]# sed -i -e 's/enabled = 0/enabled = 1/g' /etc/yum.repos.d/rhel-9-beta.repo
[root@savle ~]# sed -i -e 's/downloads.redhat.com/mirrors.aliyun.com/g' /etc/yum.repos.d/rhel-9-beta.repo
[root@savle ~]# 在两台设备上 安装mariadb服务
#master
[root@master ~]# yum -y install mariadb mariadb-server
正在更新 Subscription Management 软件仓库。
无法读取客户身份
本系统尚未在权利服务器中注册。可使用 subscription-manager 进行注册。
上次元数据过期检查：0:02:11 前，执行于 2024年04月16日 星期二 13时27分59秒。
依赖关系解决。
=======================================================================================================================================================
软件包 架构 版本 仓库 大小
=======================================================================================================================================================
安装:
mariadb x86_64 3:10.5.16-2.el9_0 AppStream 1.6 M
mariadb-server x86_64
省略 。。。。。
#savle
[root@savle ~]# yum -y install mariadb mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
Last metadata expiration check: 0:02:37 ago on Tue 16 Apr 2024 01:28:12 PM CST.
Dependencies resolved.
=======================================================================================================================================================
Package Architecture Version Repository Size
=======================================================================================================================================================
Installing:
mariadb x86_64 3:10.5.16-2.el9_0 AppStream 1.6 M
mariadb-server x86_64 3:10.5.16-2.el9_0 AppStream 9.4 M
省略 。。。。。对两台设备进⾏启动mariadb服务操作并设置为开机⾃启动，随后进⾏数据库 安全设置
#master
[root@master ~]# systemctl restart mariadb.service
[root@master ~]# systemctl enable mariadb.service
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 ~]#
[root@master ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n] y //切换到unix_socket身份验证
Enabled successfully!
Reloading privilege tables..
... Success!
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] y //设置密码
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] //移除匿名用户
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] //禁止root远程登录
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] //移除test数据库
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] //刷新权限表
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
#savle
[root@savle ~]# systemctl restart mariadb.service
[root@savle ~]# systemctl enable mariadb.service
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@savle ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n] y
Enabled successfully!
Reloading privilege tables..
... Success!
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@savle ~]#
对两台设备进⾏主从同步设置 并重启服务
#master
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
16 [mysqld]
17 datadir=/var/lib/mysql //存储数据库文件的目录。这通常包括所有的数据库文件、表文件、索引文件等。
18 socket=/var/lib/mysql/mysql.sock //定义了UNIX套接字文件的路径
19 log-error=/var/log/mariadb/mariadb.log //指定了MariaDB的错误日志文件的路径
20 pid-file=/run/mariadb/mariadb.pid //置了进程ID（PID）文件的路径
21 log_bin=mysql_bin //指定二进制日志功能日志文件名为mysql_bin
22 binlog_ignore_db=mysql //这行配置指定了在二进制日志中忽略的数据库
23 server_id=200 //优先级，越小越优先
[root@master ~]# systemctl restart mariadb.service
[root@master ~]#
#savle
[root@savle ~]# vim /etc/my.cnf.d/mariadb-server.cnf
16 [mysqld]
17 datadir=/var/lib/mysql //存储数据库文件的目录。这通常包括所有的数据库文件、表文件、索引文件等。
18 socket=/var/lib/mysql/mysql.sock //定义了UNIX套接字文件的路径
19 log-error=/var/log/mariadb/mariadb.log //指定了MariaDB的错误日志文件的路径
20 pid-file=/run/mariadb/mariadb.pid //置了进程ID（PID）文件的路径
21 log_bin=mysql_bin //指定二进制日志功能日志文件名为mysql_bin
22 binlog_ignore_db=mysql //这行配置指定了在二进制日志中忽略的数据库
23 server_id=201 //优先级，越小越优先
[root@savle ~]# systemctl restart mariadb.service
[root@savle ~]# 在master上对savle⽤户进⾏授权操作，使其能够访问数据库
[root@master ~]# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.5.16-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)]> create user 'savle'@'%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'savle'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
MariaDB [(none)]>
MariaDB [(none)]> create database test; //创建数据库用于测试从服务器同步
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> 在savle上开启同步设置
MariaDB [(none)]> change master to master_host='192.168.200.133',master_user='savle',master_password='123456';
Query OK, 0 rows affected (0.011 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status \G; // \G参数使得输出结果以垂直格式显示
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.133
Master_User: savle
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 798
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 1097
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes //此项为yes即可
Slave_SQL_Running: Yes //此项为yes即可
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 798
Relay_Log_Space: 1408
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 200
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 3
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test | //刚刚主服务器创建的test数据库
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
#回到主服务器再次进行测试
[root@master ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.5.16-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)]> create database abc;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]>
#进入savle
[root@savle ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.5.16-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)]> show databases;
+--------------------+
| Database |
+--------------------+
| abc | //发现已经同步过来了
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [(none)]> mysql的主从备份就到此结束啦！！！
545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
