欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

mysql数据库双机热备(主从同步)的实现

程序员文章站 2022-04-09 10:48:47
...

mysql双机热备的实现

简介

Mysql数据库没有增量备份的机制，当数据量太大的时候备份是一个很大的问题。还好mysql数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份的数据库中，实现mysql数据库的热备份。实现原理就是利用mysql的 binlog，主数据库的所有操作全部写入binlog，包括了增删改查及创建、修改、删除表等操作，从数据库读取主数据的binlog，将主数据库的所有操作在从数据库重新执行一遍。

实现双机热备，首先要了解主从数据库的版本。要实现热备mysql的版本都需要高于3.2。还有一个基本的原则就是作为从数据库的数据版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。

环境准备

1. 服务器准备

由于Mysql不同版本之间的（二进制日志）binlog格式可能会不太一样，因此最好的搭配组合是主（Master）服务器的Mysql版本和从（Slave）服务器版本相同或者更低，主服务器的版本肯定不能高于从服务器版本。

本次我用于测试的两台服务器mysql版本都是Mysql-5.7.17。

本次环境在我自己的笔记本搭建，主服务器使用系统环境为win10，从服务器为win10虚拟机。

ip设置如下

A服务器（主服务器Master）：192.168.2.222

B服务器（从服务器Slave）： 192.168.136.131

可以使用如下sql语句查询mysql版本

select version();

+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.01 sec)

2. 用户账户准备

在主服务器上为从服务器建立一个mysql的连接帐户，该帐户必须授予REPLICATION SLAVE权限。

在这里我使用root账户来演示，线上环境请合理设置用户名与权限。

创建好同步连接帐户后，我们需要在从服务器（Slave）上用上一步建立的帐户对主服务器（Master）数据库进行访问下，看下是否能连接成功。

3.修改mysql配置文件

修改两个mysql的配置文件my.ini

修改主服务器的mysql配置文件

我的配置文件在C:\ProgramData\MySQL\MySQL57\my.ini,先将原文件备份一份，然后修改配置文件，添加或修改[mysqld]下的几个配置项即可。

# 唯一id,mysql的server-id是4字节整数，范围是1~2^23-1内的唯一值且不能与其它服务中的配置相同
server-id=1
#设置binlog日志存储路径
log_bin =D:\dev_tools\MySQL57\log\mysql-bin
#记录bin-log日志的数据库，该值可以有多个，下面的配置代表只对 test 和 hidata_pims两个数据库记录bin-log
binlog-do-db =hidata_pims
binlog-do-db=test
#bin-log日志格式
binlog-format=Row

修改完毕，重启主服务器的mysql服务。

下面是一份我使用的完整的my.ini配置文件，可以参考一下


[client]

port=3306

[mysql]
no-beep

default-character-set=utf8


[mysqld]


port=3306

datadir=D:\dev_tools\MySQL57\Data

character-set-server=utf8

default-storage-engine=MyISAM

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE
general-log=0
general_log_file="DESKTOP-674SI3I.log"
# 打开慢查询日志
slow-query-log=1
# 慢查询日志存储路径
 slow_query_log_file="DESKTOP-674SI3I-slow.log"
slow_query_log_file="log-slow-queries.log"
# SQL执行时间大于3秒，则记录日志
long_query_time=3

log-error="DESKTOP-674SI3I.err"



secure-file-priv="D:\dev_tools\MySQL57/Uploads"


max_connections=151


query_cache_size=0


table_open_cache=2000


tmp_table_size=100M
thread_cache_size=10


myisam_max_sort_file_size=100G

myisam_sort_buffer_size=4096M

key_buffer_size=8M


read_buffer_size=64K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1


innodb_log_buffer_size=1M


innodb_buffer_pool_size=8M


innodb_log_file_size=48M


innodb_thread_concurrency=9

innodb_autoextend_increment=64


innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1


innodb_checksum_algorithm=0


back_log=80


flush_time=0

join_buffer_size=256K


max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161


query_cache_type=0


sort_buffer_size=256K


table_definition_cache=1400


binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000
#设置binlog日志
log_bin =D:\dev_tools\MySQL57\log\mysql-bin
#记录日志的数据库
binlog-do-db = hidata_pims
binlog-do-db=test
binlog-format=Row
#设置binlog清理时间
expire-logs-days =30
#binlog每个日志文件大小
max-binlog-size = 100M
#binlog缓存大小
binlog_cache_size = 10m
#最大binlog缓存大小
max_binlog_cache_size = 256m
server-id = 1
group_concat_max_len = 10240000

修改从服务器的mysql配置文件

因为这里面是以主－从方式实现mysql双机热备的，所以在从服务器就不用在建立同步帐户了，直接修改从服务器的mysql配置文件my.ini即可。

保险起见，先将原文件备份一份，然后修改配置文件，添加或修改[mysqld]下的几个配置项即可。

# 唯一id,mysql的server-id是4字节整数，范围是1~2^23-1内的唯一值且不能与其它服务中的配置相同
server-id = 2
#复制操作要针对的数据库(可选，默认为全部)，同样如果有多个数据库则可配置多次本属性。
replicate-do-db =test

修改完毕，重启从服务器的mysql服务。

测试数据准备

  1. 在主服务器的mysql数据库新建test库并执行如下脚本
CREATE TABLE `t_user` (
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test`.`t_user`(`name`, `sex`, `age`, `time`) VALUES ('小红', '女', 26, '2020-02-11 11:13:27');
INSERT INTO `test`.`t_user`(`name`, `sex`, `age`, `time`) VALUES ('李四', '男', 34, '2020-02-11 11:13:17');
INSERT INTO `test`.`t_user`(`name`, `sex`, `age`, `time`) VALUES ('张三', '男', 12, '2020-02-11 11:13:07');

  1. 在从服务器建立 跟主服务器同名、同编码的的test数据库，执行如下脚本：

    CREATE TABLE `t_user` (
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

启动同步服务

1. 查询主服务器状态

在主服务器mysql数据库的test库执行如下sql语句：

show master status;

执行结果如下：

+------------------+----------+------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+------------------+------------------+-------------------+
| mysql-bin.000001 |     1571 | hidata_pims,test |                  |                   |
+------------------+----------+------------------+------------------+-------------------+
1 row in set (0.01 sec)

注意看里面的参数，特别前面两个File和Position，在从服务器（Slave）配置主从关系会有用到的。

2. 启动从服务器同步服务

在从服务器test数据库执行如下sql语句：

stop slave;
change master to
master_host='192.168.2.222',master_user='root',master_password='root',
master_log_file='mysql-bin.000001',master_log_pos=1571;
reset slave;

master_host ： 主服务器的ip

master_user：主服务器配置的同步用的用户名

master_password：主服务器配置的同步用的用户密码

master_log_file：上一步，查询主服务器状态时查询到的值File，即当前正在记录bin—log的文件

master_log_pos：上一步，查询主服务器状态时查询到的值Position

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
reset slave;

执行完毕，如果没有报错，查看slave状态，执行如下sql

show slave status;

结果如下：
mysql数据库双机热备(主从同步)的实现

需要特别注意，查看下面两项值均为Yes，即表示设置从服务器成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3. 查看效果

在从服务器查询t_user表，可以看到有三条数据，证明主服务器中的数据一同步过来了。

此时继续在主服务器往t_user表插入的数据，均会同步到从服务t_user表。

主服务器mysql 新建表或修改表结构，也会同步到从服务器。

参考内容：

https://www.cnblogs.com/fnlingnzb-learner/p/7000898.html
https://www.cnblogs.com/l-hh/p/9922548.html
https://www.iteye.com/blog/yunnick-1845301

相关标签： 数据库 mysql

上一篇： Highcharts 导出到 Word  

下一篇： 2020东北大学理学院大三暑期实训（第一天）：Python基础回顾

推荐阅读