文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Mysql/Mariadb主从复制

2021-03-22 03:33

关注

什么是·Mysql/Mariadb主从复制?    

Mysql/Mariadb主从复制:当Master(主)数据库发生变化的时候,变化实时会同步到slave(从)数据库中;
类似于:Samba共享文件(C/S)、NFS网络文件共享(C/S),当服务端(Server)发生变化时,客户端(client)数据内容会根据服务端进行改变;

在master机器上,主从同步事件会被写到特殊的log文件中(binary-log);

主从同步事件有3种形式:statement、row、mixed。

 statement:会将对数据库操作的sql语句写入到binlog中。
 row:会将每一条数据的变化写入到binlog中。
 mixed:statement与row的混合。Mysql决定什么时候写statement格式的,什么时候写row格式的binlog。

整体上来说,复制有3个步骤:

下面这章图可以详细解释其原理:

主从复制过程

说的简单一些就是:

当对Master数据库不管做了增、删、改还是创建了数据库、表等操作时,Slave就会快速的接受这些数据以及对象的操作,从而实现主从数据复制,保证数据的一致性。  

我记得我学PHP开发的时候,教员经常说的一句话就是:学习半小时,实战一分钟;
好了,接下来到我们实战的时刻了,认真听讲哟!!! 

环境介绍

系统环境:系统基本上都差不多,一般多数都是Linux平台和Windows平台比较多,不管什么样的系统环境对这次实战的操作都影响不大,我在这里使用的是Docker虚拟出来的CentOS操作系统,当然您可以选用Ubuntu、RedHat以及Windows系统,这些都不会影响到大的操作;

我这里使用的系统版本:

[root@master /]# cat /etc/redhat-release
CentOS Linux release 8.0.1905 (Core) 

这里会用到两台服务器:其中一台MasterIP172.18.0.2,另外一个slaveIP172.18.0.3
数据库版本:(我这里使用的Mariadb,你可以使用mysql数据库)

[root@master /]# mysql --version
mysql  Ver 15.1 Distrib 10.3.11-MariaDB, for Linux (x86_64) using readline 5.1

配置Master数据库

  1.更改Master配置文件
    找到下面文件:

mysql数据库:/etc/mysql/mysql,conf.d/mysqld.cnf
mariadb数据库:/etc/my.cnf.d/mariadb.cnf

注意:我这里是使用yum进行安装的所以默认配置文件是在/etc下面,建议在修改上面两个文件时要先将配置文件进行备份 

修改以下配置:  

bind-address=172.18.0.2		\指定Master地址
server-id = 1					\指定唯一的serverid		部分版本没有需手动写入
log_bin = /var/log/mariadb/mariadb-bin.log				\开启binlog			部分版本没有需手动写入

注意:log_bin这个字段需根据实际情况来定,需找到数据库的日志文件,默认实在 /var/log

   2.重新启动数据库

[root@master my.cnf.d]# systemctl restart mariadb		\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mariadb  restart		\centos6及以下版本使用这个重新启动方式

  mysql重新启动: 

[root@master my.cnf.d]# systemctl restart mysqld		\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mysqld  restart		\centos6及以下版本使用这个重新启动方式

  3.初始化数据库

[root@master my.cnf.d]# 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
you 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 MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y						//这里是设置root密码,可不进行设置
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] y
 ... 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] y
 ... 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] y
 - 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] y
 ... 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@master my.cnf.d]# 

  4.创建主从同步的用户 

 [root@master ~]# mysql -u root -p				\登陆数据库
Enter password: 			\输入root密码
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 18
Server version: 10.3.11-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 "slave"@"%" IDENTIFIED BY "redhat";		
\创建用户,并设置相应的权限
	\此处%表示允许从任何地方(除本地外)使用此账号进行登陆使用,在正式环境建议具体到某台主机IP
Query OK, 0 rows affected (0.000 sec)			\表示sql语句执行成功

  5.更新Slave用户权限 

 MariaDB [(none)]> flush privileges;		\每次修改用户权限,都要使用这个sql语句进行更新
Query OK, 0 rows affected (0.000 sec)

  6.导出数据库中所有数据(此步骤取决于slave的权限)

[root@master ~]# mysqldump -u root -p --all-databases --master-data > mariadb.bat.sql
	--all-databases		\此参数表示备份所有数据库
	--master-data		\此参数表示将二进制的信息写入到输出文件中,在这里是写入到备份的sql文件中
Enter password:

  7.查看MASTERr REPLICATION LOG位置

 MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000002 |     1974 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

配置Slave数据库

  1.更改Slave配置文件

   文件位置与Master位置一致

mysql数据库:/etc/mysql/mysql,conf.d/mysqld.cnf
mariadb数据库:/etc/my.cnf.d/mariadb.cnf

   注意:我这里是使用yum进行安装的所以默认配置文件是在/etc下面,建议在修改上面两个文件时要先将配置文件进行备份

  修改以下配置:

bind-address=172.18.0.3		\指定Master地址
server-id = 2					\指定唯一的serverid		部分版本没有需手动写入
log_bin = /var/log/mariadb/mariadb-bin.log				\开启binlog			部分版本没有需手动写入

  注意:log_bin这个字段需根据实际情况来定,需找到数据库的日志文件,默认实在 /var/log

  2.重新启动数据库

[root@master my.cnf.d]# systemctl restart mariadb		\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mariadb  restart		\centos6及以下版本使用这个重新启动方式

  mysql重新启动:

[root@master my.cnf.d]# systemctl restart mysqld		\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mysqld  restart		\centos6及以下版本使用这个重新启动方式

  3.初始化数据库

[root@master my.cnf.d]# 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
you 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 MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y						//这里是设置root密码,可不进行设置
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] y
 ... 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] y
 ... 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] y
 - 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] y
 ... 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@master my.cnf.d]# 

  4.从Master将数据库备份复制到slave服务器

[root@slave my.cnf.d]# scp jia@172.18.0.2:/opt/mariadb.bat.sql /opt/
jia@172.18.0.2"s password: 
mariadb.bat.sql                   

  5.将备份数据恢复到slave数据库

[root@slave my.cnf.d]# mysql -u root -p < /opt/mariadb.bat.sql 
 Enter password: 

  6.使slave与master建立连接

[root@slave my.cnf.d]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user "root"@"localhost" (using password: YES)
[root@slave my.cnf.d]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 22
Server version: 10.3.11-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)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST = "172.18.0.2",		\指定Master数据库地址
    -> MASTER_USER = "slave",			\指定主从复制用户名
    -> MASTER_PASSWORD = "redhat",			\指定主从复制用户密码
    -> MASTER_LOG_FILE = "mariadb-bin.000002",		\指定Master数据库的binlog文件名
    -> MASTER_LOG_POS=1974;			
Query OK, 0 rows affected (0.290 sec)

MariaDB [(none)]> start slave;			\开启复制功能
Query OK, 0 rows affected (0.019 sec)

MariaDB [(none)]>

  注意:lMASTER_LOG_FILE="mariadb-bin.000002与MASTER_LOG_POS=1974的值,是从上面的 SHOW MASTER STATUS 得到的。

  到这里已经可以做到主从复制了下面让我们测试一下吧

验证数据库是否同步

测试方法很简单,只需要在主数据库上面创建数据库或者增加一条记录就可以测试是否主从复制配置成功

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

MariaDB [(none)]> create database a;			\在主数据库创建a数据库
Query OK, 1 row affected (0.063 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| a                  |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

  下面我们来看看从数据库上面有没有a这个数据库吧

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

  我们会发现已经有了a这个数据库

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯