上海网站制作平台,高端展馆展厅设计方案,易搭应用快速开发平台,外包公司 网站建设 上海1、简介
数据库运⾏时#xff0c;⼀些因素可能会导致服务运⾏不正常#xff0c;⽤户访问数据受阻。对于互联⽹公 司#xff0c;尤其是购物⽹站⽽⾔#xff0c;这种情况造成的损失是⽆法估量的。因此#xff0c;对数据库进⾏“备份” 也是必不可少的操作。当主要的数据库死…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
[rootadmin ~]# hostnamectl set-hostname master
[rootadmin ~]# bash
[rootmaster ~]# [rootlocalhost ~]# hostnamectl set-hostname salve
[rootlocalhost ~]# bash
[rootsalve ~]#
对两台设备进⾏关闭防⽕墙和selinux操作
[rootmaster ~]# systemctl stop firewalld.service
[rootmaster ~]# systemctl disable firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[rootmaster ~]# setenforce 0[rootsalve ~]# systemctl stop firewalld.service
[rootsalve ~]# systemctl disable firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[rootsalve ~]# setenforce 0 配置本地yum仓库、并安装yum工具 配置阿里云网络源
#master
[rootmaster ~]# mount /dev/sr0 /media/
mount: /media: WARNING: source write-protected, mounted read-only.
[rootmaster ~]# cat /etc/yum.repos.d/local.repo
[AppStream]
nameAppStream
baseurlfile:///media/AppStream
enabled1
gpgcheck0
[BaseOS]
nameBaseOS
baseurlfile:///media/BaseOS
enabled1
gpgcheck0
[rootmaster ~]#
[rootmaster ~]# yum -y install yum-utils.noarch
[rootmaster ~]# 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
[rootmaster ~]# #salve
[rootsavle mnt]# mount /dev/sr1 /mnt/
[rootsavle mnt]# cat /etc/yum.repos.d/local.repo
[AppStream]
nameAppStream
baseurlfile:///mnt/AppStream
enabled1
gpgcheck0
[BaseOS]
nameBaseOS
baseurlfile:///mnt/BaseOS
enabled1
gpgcheck0
[rootsavle mnt]#
[rootsavle ~]# yum -y install yum-utils.noarch
[rootsavle ~]# 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 identityThis 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
[rootsavle ~]#
修改源的enabled项为1 、以及地址项
#master
[rootmaster ~]# sed -i -e s/enabled 0/enabled 1/g /etc/yum.repos.d/rhel-9-beta.repo
[rootmaster ~]# sed -i -e s/downloads.redhat.com/mirrors.aliyun.com/g /etc/yum.repos.d/rhel-9-beta.repo
[rootmaster ~]# #savle
[rootsavle ~]# sed -i -e s/enabled 0/enabled 1/g /etc/yum.repos.d/rhel-9-beta.repo
[rootsavle ~]# sed -i -e s/downloads.redhat.com/mirrors.aliyun.com/g /etc/yum.repos.d/rhel-9-beta.repo
[rootsavle ~]#
在两台设备上 安装mariadb服务
#master
[rootmaster ~]# 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 Mmariadb-server x86_64省略 。。。。。#savle[rootsavle ~]# yum -y install mariadb mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identityThis 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 SizeInstalling:mariadb x86_64 3:10.5.16-2.el9_0 AppStream 1.6 Mmariadb-server x86_64 3:10.5.16-2.el9_0 AppStream 9.4 M
省略 。。。。。
对两台设备进⾏启动mariadb服务操作并设置为开机⾃启动随后进⾏数据库 安全设置
#master
[rootmaster ~]# systemctl restart mariadb.service
[rootmaster ~]# 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.
[rootmaster ~]#
[rootmaster ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDBSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, well need the current
password for the root user. If youve just installed MariaDB, and
havent 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 youve completed all of the above steps, your MariaDB
installation should now be secure.Thanks for using MariaDB!#savle
[rootsavle ~]# systemctl restart mariadb.service
[rootsavle ~]# 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.
[rootsavle ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDBSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, well need the current
password for the root user. If youve just installed MariaDB, and
havent 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 youve completed all of the above steps, your MariaDB
installation should now be secure.Thanks for using MariaDB!
[rootsavle ~]# 对两台设备进⾏主从同步设置 并重启服务
#master
[rootmaster ~]# 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 //置了进程IDPID文件的路径21 log_binmysql_bin //指定二进制日志功能日志文件名为mysql_bin22 binlog_ignore_dbmysql //这行配置指定了在二进制日志中忽略的数据库23 server_id200 //优先级越小越优先
[rootmaster ~]# systemctl restart mariadb.service
[rootmaster ~]##savle[rootsavle ~]# 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 //置了进程IDPID文件的路径21 log_binmysql_bin //指定二进制日志功能日志文件名为mysql_bin22 binlog_ignore_dbmysql //这行配置指定了在二进制日志中忽略的数据库23 server_id201 //优先级越小越优先[rootsavle ~]# systemctl restart mariadb.service [rootsavle ~]#
在master上对savle⽤户进⾏授权操作使其能够访问数据库
[rootmaster ~]# 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_host192.168.200.133,master_usersavle,master_password123456;
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 eventMaster_Host: 192.168.200.133Master_User: savleMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql_bin.000001Read_Master_Log_Pos: 798Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos: 1097Relay_Master_Log_File: mysql_bin.000001Slave_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: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 798Relay_Log_Space: 1408Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 200Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimisticSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesSlave_DDL_Groups: 3
Slave_Non_Transactional_Groups: 0Slave_Transactional_Groups: 0
1 row in set (0.000 sec)ERROR: No query specifiedMariaDB [(none)]
MariaDB [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| test | //刚刚主服务器创建的test数据库
--------------------
4 rows in set (0.001 sec)MariaDB [(none)] #回到主服务器再次进行测试
[rootmaster ~]# 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 ServerCopyright (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
[rootsavle ~]# 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 ServerCopyright (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的主从备份就到此结束啦
