MySQL主从复制

服务器 0

目录

一、MySQL主从复制的概述

二、MySQL主从复制的原理

三、主从复制的步骤

四、主从复制架构图

五、MySQL主从复制的分类

MySQL主从复制按照binlog的三种日志格式进行划分:

1、基于SQL语句的复制(statement-based replication, SBR):

优点:

缺点:

2、基于行的复制(row-based replication, RBR):

优点:

缺点:

3、混合模式复制(mixed-based replication, MBR):是上面两种方式的折中。

六、MySQL主从复制的好处

 MySQL主从复制配置操作

需求:

配置环境说明:

1、修改主机名 主:master  从:slave

2、永久关闭两台服务器的防火墙

3、在master和slave上部署MySQL服务

 4、对两台设备进行同步配置

 5、在master上对用户slave进行授权,使其能够访问数据库

6、在slave上开启同步

7、测试主从效果


一、MySQL主从复制的概述

MySQL作为世界上使用最为广泛的数据库之一,免费是其原因之一;但不可忽略的是它本身的功能的确很强大。随着技术的发展,在实际的生产环境中,由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群,能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。

二、MySQL主从复制的原理

MySQL服务器之间的主从同步是基于二进制日志机制,主服务器使用二进制日志来记录数据库的变动情况,从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

三、主从复制的步骤

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

  • 从库生成两个线程,一个I/O线程,一个SQL线程。

  • I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中。

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

四、主从复制架构图

五、MySQL主从复制的分类

MySQL主从复制按照binlog的三种日志格式进行划分:

1、基于SQL语句的复制(statement-based replication, SBR):

优点:

  • 历史悠久,技术成熟。
  • 产生的binlog文件较小,比较节省空间。
  • binlog中包含了所有数据库更改信息,可以据此来审核数据库的安全等情况;binlog可以用于实时的还原,而不仅仅用于复制。
  • 主从版本可以不一样,从服务器版本可以比主服务器版本高。

缺点:

  • 不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
  • 调用具有不确定因素的 UDF(用户自定义函数)时复制也可能出问题。
  • INSERT … SELECT会产生更多的行级锁
  • 使用以下函数的语句也无法被复制:
    • LOAD_FILE()
    • UUID()
    • USER()
    • FOUND_ROWS()
    • SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

2、基于行的复制(row-based replication, RBR):

优点:

  • 任何情况都可以被复制,这对复制来说是最安全可靠的。
  • 多数情况下,从服务器上的表如果有主键的话,复制就会快了很多。
  • 从服务器上采用多线程来执行复制成为可能。
  • 执行 INSERT,UPDATE,DELETE 语句时锁更少。
  • 复制以下几种语句时的行锁更少:
    • INSERT … SELECT
    • 包含 AUTO_INCREMENT 字段的 INSERT
    • 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句

缺点:

  • binlog 文件太大
  • 复杂的回滚时 binlog 中会包含大量的数据
  • 主服务器上执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,而 SBR 只会写一次,这会导致频繁发生 binlog 的并发写问题
  • UDF 产生的大 BLOB值(较大的二进制对象)会导致复制变慢
  • 无法从 binlog 中看到都复制了写什么语句,无法进行审计。

3、混合模式复制(mixed-based replication, MBR):是上面两种方式的折中。

以上三种模式对应binlog的三种格式:

STATEMENT

ROW

MIXED。

六、MySQL主从复制的好处

  • 实时备份,提高数据安全性,同时用于故障切换,避免影响业务。
  • 读写分离(mysql-proxy),将读请求交给从服务器,实现负载分担。
  • 数据库性能提高,通过增加从数据库组建集群,提高总性能。

 MySQL主从复制配置操作

需求:

搭建两台mysql服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作

配置环境说明:

配置项主服务器从服务器
IP地址192.168.206.11192.168.206.13
server_ID200201
主机名masterslave
系统版本centos8  centos8

1、修改主机名 主:master  从:slave

主服务器:[root@localhost ~]# hostnamectl set-hostname master[root@localhost ~]# bash[root@master ~]# 
从服务器:[root@localhost ~]# hostnamectl set-hostname slave[root@localhost ~]# bash[root@slave ~]# 

2、永久关闭两台服务器的防火墙

主服务器:[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 ~]# vim /etc/selinux/config #进来之后按字母i进行数据修改# This file controls the state of SELinux on the system.# SELINUX= can take one of these three values:#     enforcing - SELinux security policy is enforced.#     permissive - SELinux prints warnings instead of enforcing.#     disabled - No SELinux policy is loaded.SELINUX=enforcing    //将这里的enforcing改为disabled# SELINUXTYPE= can take one of these three values:#     targeted - Targeted processes are protected,#     minimum - Modification of targeted policy. Only selected processes are protected. #     mls - Multi Level Security protection.SELINUXTYPE=targeted#修改完之后按键盘上的ESC键,输入 :wq(保存并退出)[root@master ~]# setenforce 0    //刷新配置文件
从服务器:[root@slave ~]# systemctl stop firewalld.service [root@slave ~]# 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 ~]# vim /etc/selinux/config #进来之后按字母i进行数据修改# This file controls the state of SELinux on the system.# SELINUX= can take one of these three values:#     enforcing - SELinux security policy is enforced.#     permissive - SELinux prints warnings instead of enforcing.#     disabled - No SELinux policy is loaded.SELINUX=enforcing    //将这里的enforcing改为disabled# SELINUXTYPE= can take one of these three values:#     targeted - Targeted processes are protected,#     minimum - Modification of targeted policy. Only selected processes are protected. #     mls - Multi Level Security protection.SELINUXTYPE=targeted#修改完之后按键盘上的ESC键,输入 :wq(保存并退出)[root@slave ~]# setenforce 0    //刷新配置文件

3、在master和slave上部署MySQL服务

主服务器:[root@master ~]# yum -y install mariadb*             //下载MySQL数据库[root@master ~]# systemctl start mariadb.service     //启动服务[root@master ~]# systemctl enable mariadb.service    //开机自启[root@master ~]# ss -antl                            //查看是否由3306端口(数据库端口号)[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 currentpassword for the root user.  If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none):   //这里直接回车OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.Set root password? [Y/n] y   //按y确认设置数据库密码New password:                 //设置数据库密码Re-enter new password:         //确认数据库密码Password updated successfully!Reloading privilege tables.. ... Success!By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem.  This is intended only for testing, and to make the installationgo a bit smoother.  You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y ... Success!Normally, root should only be allowed to connect from 'localhost'.  Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] n ... skipping.By default, MariaDB comes with a database named 'test' that anyone canaccess.  This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y ... Success!Cleaning up...All done!  If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB![root@master ~]# 
从服务器:[root@slave ~]# yum -y install mariadb*            //下载mysql数据库[root@slave ~]# systemctl start mariadb.service    //启动服务[root@slave ~]# systemctl enable mariadb.service   //开机自启[root@slave ~]# ss -antl                           //查看是否由3306端口(数据库端口号)[root@slave ~]# 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 currentpassword for the root user.  If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none):   //这里直接回车OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.Set root password? [Y/n] y   //按y确认设置数据库密码New password:                 //设置数据库密码Re-enter new password:         //确认数据库密码Password updated successfully!Reloading privilege tables.. ... Success!By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem.  This is intended only for testing, and to make the installationgo a bit smoother.  You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y ... Success!Normally, root should only be allowed to connect from 'localhost'.  Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] n ... skipping.By default, MariaDB comes with a database named 'test' that anyone canaccess.  This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y ... Success!Cleaning up...All done!  If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB![root@slave ~]# 

 4、对两台设备进行同步配置

主服务器:[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf#进来之后按字母i进行数据修改[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mariadb/mariadb.logpid-file=/run/mariadb/mariadb.pid#在行下添加以下三行内容log_bin=mysql-bin        //启用binlog日志(也叫二进制日志)binlog_ignore_db=mysql   server_id=200            //数据库服务器的唯一标识,主数据库的server_ID必须大于从数据库的server_ID,为0则拒绝从服务器连接#修改完之后按键盘上的ESC键,输入 :wq(保存并退出)[root@master ~]# systemctl restart mariadb.service     //因为修改了配置文件,所以要重启一下配置文件
从服务器:[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf#进来之后按字母i进行数据修改[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mariadb/mariadb.logpid-file=/run/mariadb/mariadb.pid#在行下添加以下三行内容log_bin=mysql-bin        //启用binlog日志(也叫二进制日志)binlog_ignore_db=mysql   server_id=201            //数据库服务器的唯一标识,主数据库的server_ID必须大于从数据库的server_ID,为0则拒绝从服务器连接#修改完之后按键盘上的ESC键,输入 :wq(保存并退出)[root@slave ~]# systemctl restart mariadb.service   //因为修改了配置文件,所以要重启一下配置文件

 5、在master上对用户slave进行授权,使其能够访问数据库

主服务器:mysql -u root -p1    //登录数据库create user 'slave'@'%' identified by '123456';       //在master上创建授权用户grant replication slave on *.* to 'slave'@'%' identified  by '123456';    //为slave用户授权

6、在slave上开启同步

从服务器:mysql -u root -p2     //登录数据库change master to master_host='192.168.206.11',master_user='slave',master_password='123456';   //在从库上设置要同步的主库信息start slave;           //开启同步设置show slave status/G    //查看是否开启同步

7、测试主从效果

测试效果的相关命令可以参照:https://blog.csdn.net/liujun2986230069/article/details/129856213

主服务器(master)的操作:

第一步:登录数据库

第二步:查看数据库

第三步:创建数据库

第四步:进入数据库

第五步:创建表格

第六步:插入数据

第七步:查看数据

从服务器(slave)的操作:

第一步:登录数据库

第二步:查看是否有在master上创建的数据库

第三步:进入数据库

第四步:查看表格

第五步:查看表格数据是否与master上一样

也许您对下面的内容还感兴趣: