使用MySQL主从复制技术实现数据库热备

左手的ㄟ右手 2022-01-14 02:41 230阅读 0赞

使用MySQL主从复制技术实现数据库热备  
1 MySQL Replication  
2 部署MySQL主从同步  
3 部署MySQL主主双向主从复制

MySQL Replication  
什么是MySQL Replication  
Replication可以实现将数据从一台数据库服务器（master）复制到一台或多台数据库服务器（slave）  
默认情况下属于异步复制，无需维持长连接  
通过配置，可以复制所有的库或者几个库，甚至库中的一些表  
是MySQL内建的，本身自带的  
Replication的原理  
简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作  
![使用MySQL主从复制技术实现数据库热备][MySQL]  
DML:SQL操作语句，update, ×××ert，delete  
Relay log :中继日志  
Replication的作用  
Fail Over 故障切换  
Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份  
High Performance 高性能，可以多台slave,实现读写分离  
Replication工作原理  
整体上来说，复制有3个步骤：  
master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）  
slave将master的binary log events拷贝到它的中继日志(relay log)  
slave重做中继日志中的事件，修改salve上的数据。  
![使用MySQL主从复制技术实现数据库热备][MySQL 1]

MySQL主从复制中：  
第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。  
第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程—I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。  
第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

Replication常见方案  
One master and Muti salve 一主多备  
![使用MySQL主从复制技术实现数据库热备][MySQL 2]

一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中  
在Master上<多台同步影响IO>

2.M-S-S

使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates

![使用MySQL主从复制技术实现数据库热备][MySQL 3]

Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志

M-M 双主互备 (互为主从)  
很多人误以为这样可以做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。

M-M-M  
监控三台机器互相作对方的master

![使用MySQL主从复制技术实现数据库热备][MySQL 4]

天生的缺陷：复制延迟，slave上同步要慢于master，如果大并发的情况那延迟更严重  
mysql在5.6已经自身可以实现fail over故障切换  
One slave Muti master 一从对多主  
好处：节省成本，将多个master数据自动化整合  
缺陷：对库和表数据的修改较多

部署MySQL主从同步 <M-S>  
环境准备：  
![使用MySQL主从复制技术实现数据库热备][MySQL 5]

模式：C/S  
端口：3306  
配置主数据库服务器node-1  
安装MySQL 5.7版本  
上传mysql-5.7.tar.gz到Linux主机上，并解压：  
注：mysql-5.7.tar.gz 中包括了安装mysql5.7主要的软件包。 这样部署起来更方便  
下载mysql 5.7 解压并安装  
\[root@node-1 ~\]\# wget [https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86\_64.rpm-bundle.tar][https_cdn.mysql.com_Downloads_MySQL-5.7_mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar]

\[root@node-1 ~\]\# tar xvf mysql-5.7.26-1.el7.x86\_64.rpm-bundle.tar  
mysql-community-embedded-devel-5.7.26-1.el7.x86\_64.rpm  
mysql-community-libs-5.7.26-1.el7.x86\_64.rpm  
mysql-community-embedded-5.7.26-1.el7.x86\_64.rpm  
mysql-community-test-5.7.26-1.el7.x86\_64.rpm  
mysql-community-embedded-compat-5.7.26-1.el7.x86\_64.rpm  
mysql-community-common-5.7.26-1.el7.x86\_64.rpm  
mysql-community-devel-5.7.26-1.el7.x86\_64.rpm  
mysql-community-client-5.7.26-1.el7.x86\_64.rpm  
mysql-community-server-5.7.26-1.el7.x86\_64.rpm  
mysql-community-libs-compat-5.7.26-1.el7.x86\_64.rpm

\[root@node-1 ~\]\# yum ×××tall ./mysql\*.rpm  
启动mysql 并生成临时密码  
\[root@node-1 ~\]\# systemctl start mysqld

在MySQL的配置文件/etc/my.cnf 中关闭密码强度审计插件，并重启MySQl服务。  
\[root@node-1 ~\]\# vim /etc/my.cnf  
\#修改MySQL的配置文件，在\[myqld\]标签处末行添加以下项  
validate-password=OFF \#不使用密码强度审计插件  
重启mysqld  
\[root@node-1 ~\]\# systemctl restart mysqld  
获取临时密码  
\[root@node-1 ~\]\# grep 'password' /var/log/mysqld.log  
2019-06-11T03:30:58.381293Z 1 \[Note\] A temporary password is generated for root@localhost: Ulw=h.3N1r/T  
使用临时密码登陆mysql，注意临时密码需要引号  
\[root@node-1 ~\]\# mysql -u root -p"Ulw=h.3N1r/T"  
修改root密码  
mysql> set password for root@localhost = password('123456');  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.01 sec)

mysql> exit  
Bye

创建需要同步的数据库：  
\[root@node-1 ~\]\# mysql -u root -p123456  
mysql> create database HA;  
Query OK, 1 row affected (0.01 sec)

mysql> use HA;  
Database changed  
mysql> create table T1(id int,name varchar(20));  
Query OK, 0 rows affected (0.01 sec)  
mysql> exit  
Bye

\[root@node-1 ~\]\# vim /etc/my.cnf  
log-bin=mysql-bin-master \#启用二进制日志  
server-id=1 \#本机数据库ID 标示  
binlog-do-db=HA \#可以被从服务器复制的库, 二进制需要同步的数据库名  
binlog-ignore-db=mysql \#不可以被从服务器复制的库  
授权  
\[root@node-1 ~\]\# mysql -uroot -p123456

mysql> grant replication slave on *.* to slave@192.168.150.139 identified by "123456";  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)  
查看状态信息

mysql> show binlog events\\G

![使用MySQL主从复制技术实现数据库热备][MySQL 7]

复制前要保证同步的数据库一致  
导出数据库文件

\[root@node-1 ~\]\# mysqldump -uroot -p123456 HA >HA.sql

将数据导出传给从服务器  
\[root@node-1 ~\]\# scp HA.sql root@192.168.150.139:/root

配置从服务器node-2  
下载数据库  
\[root@node2 ~\]\# wget [https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86\_64.rpm-bundle.tar][https_cdn.mysql.com_Downloads_MySQL-5.7_mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar]

解压mysql  
\[root@node2 ~\]\# tar xf mysql-5.7.26-1.el7.x86\_64.rpm-bundle.tar  
\[root@node2 ~\]\# yum ×××tall -y ./mysql\*.rpm

mysql 启动会生成临时密码  
\[root@node2 ~\]\# systemctl start mysqld

在MySQL的配置文件/etc/my.cnf 中关闭密码强度审计插件，并重启MySQl服务

\[root@node2 ~\]\# systemctl restart mysqld  
获取临时密码  
\[root@node2 ~\]\# grep 'password' /var/log/mysqld.log  
2019-06-13T02:38:35.490766Z 1 \[Note\] A temporary password is generated for root@localhost: ,%d&>aAaQ2!7  
使用临时密码登陆 记住mysql 是单引号双引号不识别

\[root@node2 ~\]\# mysql -u root -p',%d&>aAaQ2!7'

修改root密码  
mysql> set password for root@localhost = password('123456');  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)  
mysql> exit  
Bye

现在登陆node-1 服务器测试远程能否登陆上数据库是否一致  
\[root@node-1 ~\]\# mysql -u root -p123456

mysql> show variables like '%version%';

![使用MySQL主从复制技术实现数据库热备][MySQL 8]

测试链接主服务器是否成功  
如果远程链接不上请开启服务器端口  
开放端口  
\[root@node-1 ~\]\# firewall-cmd --zone=public --add-port=80/tcp --permanent  
success  
\[root@node-1 ~\]\# firewall-cmd --zone=public --add-port=1000-2000/tcp --permanent  
success  
重载服务  
\[root@node-1 ~\]\# firewall-cmd --reload  
success  
查看端口  
\[root@node-1 ~\]\# firewall-cmd --zone=public --query-port=80/tcp  
yes  
开放相应服务器端口  
\[root@node-1 ~\]\# firewall-cmd --zone=public --add-port=3306/tcp --permanent  
success  
重载服务  
\[root@node-1 ~\]\# firewall-cmd --reload  
success  
测试远程数据库链接是不是正常

出现以下服务表示服务器正常链接  
\[root@node2 ~\]\# mysql -u slave -p123456 -h 192.168.150.138  
mysql: \[Warning\] Using a password on the command line interface can be ×××ecure.  
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 6  
Server version: 5.7.26-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

mysql> show databases;  
\+--------------------+  
| Database |  
\+--------------------+  
| information\_schema |  
\+--------------------+  
1 row in set (0.00 sec)

只有复制的权限, 是看不到其他库的。正常导入数据库，和主数据库服务器保持一致

mysql> exit  
Bye  
在node-2上创建相应数据库  
\[root@node2 ~\]\# mysql -uroot -p123456

mysql> create database HA;  
Query OK, 1 row affected (0.00 sec)

mysql> exit  
Bye

导入数据库

\[root@node2 ~\]\# mysql -uroot -p123456 HA < HA.sql  
以下提示是 不能再命令行下使用 提示不安全 其实已经导入数据库文件进去了  
mysql: \[Warning\] Using a password on the command line interface can be ×××ecure.

修改从服务器配置文件

从服务器没必要开启bin-log日志  
停止mysql 服务

\[root@node2 ~\]\# systemctl stop mysqld  
\[root@node2 ~\]\# vim /etc/my.cnf

server-id = 2 \#从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。  
启动mysql 服务  
\[root@node2 ~\]\# systemctl restart mysqld  
\[root@node2 ~\]\# mysql -u root -p123456  
停止slave  
mysql> stop slave;  
Query OK, 0 rows affected, 1 warning (0.00 sec)  
写入主服务器  
mysql> change master to master\_host='192.168.150.138',master\_user='slave',master\_password='123456';  
Query OK, 0 rows affected, 2 warnings (0.01 sec)  
启动slave  
mysql> start slave;  
Query OK, 0 rows affected (0.00 sec)

查看状态  
mysql> show slave status\\G

![使用MySQL主从复制技术实现数据库热备][MySQL 9]

Slave\_IO\_Running ：一个负责与主机的IO通信  
Slave\_SQL\_Running：负责自己的slave mysql进程  
两个为YES 就成功了！

再到主服务器查看状态

\[root@node-1 ~\]\# mysql -uroot -p123456

mysql> show processlist \\G

![使用MySQL主从复制技术实现数据库热备][MySQL 10]

mysql> use HA;  
这句话的是意思是  
读取表信息以完成表和列名称  
您可以关闭此功能以使用-A更快地启动  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A

从数据库查看

mysql> ×××ert into T1 values (1,'man');  
![使用MySQL主从复制技术实现数据库热备][MySQL 11]

排错  
如果遇到主从不同步，看一下主从bin-log的位置，然后再同步。  
mysql> show master status;

![使用MySQL主从复制技术实现数据库热备][MySQL 12]  
在主服务器上看二进制日志事件列表  
![使用MySQL主从复制技术实现数据库热备][MySQL 13]

先停止从服务器  
mysql> stop slave;  
Query OK, 0 rows affected (0.00 sec)  
change master to master\_log\_file='mysql-bin-master.000001',master\_log\_pos=1164;  
Query OK, 0 rows affected (0.01 sec)  
\#根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，达到同步的效果  
重新启动slave同步服务  
mysql> start slave;  
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\\G; \#用show slave status\\G;看一下从服务器的同步情况  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
如果都是yes，那代表已经在同步

重启从服务器，再查看状态：  
stop slave; \#停止从服务器  
start slave; \#开启从服务器

排错思路：  
二进制日志没有开启  
IPTABLES 没有放开端口  
对应的主机 IP地址写错了

SQL线程出错  
主从服务器数据库结构不统一，出错后，数据少，可以手动解决创建插入，再更新slave状态。  
注：如果主上误删除了。那么从上也就误删除了。 \#因此主上要定期做mysqldump备份

转载于:https://blog.51cto.com/xia1314520ting/2408320

[MySQL]: https://s1.51cto.com/images/blog/201906/11/5687d1aca7149ca86d49f6bb828a8af9.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 1]: https://s1.51cto.com/images/blog/201906/11/cea2c2d322841efbb69b93a45a2e1924.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 2]: https://s1.51cto.com/images/blog/201906/11/df838d671586b4136f4cf014b47f5a02.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 3]: https://s1.51cto.com/images/blog/201906/11/5570325a9e44501b90097da5f5aa6899.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 4]: https://s1.51cto.com/images/blog/201906/11/3404d2d87685132ab2eaa1a360caa540.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 5]: https://s1.51cto.com/images/blog/201906/11/f1d202f3b41aee6021fc8a0af1cc5387.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[https_cdn.mysql.com_Downloads_MySQL-5.7_mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar]: https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
[MySQL 6]: https://s1.51cto.com/images/blog/201906/13/c37f007f97c809841823f144bf630fa0.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 7]: https://s1.51cto.com/images/blog/201906/13/5122b88298326252d6d11874034bb969.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 8]: https://s1.51cto.com/images/blog/201906/13/bd4034f0b38a803fc149fc36ed97ac74.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 9]: https://s1.51cto.com/images/blog/201906/13/8fa78946a9d3c010a5bfd82183ac05e0.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 10]: https://s1.51cto.com/images/blog/201906/13/e84d54f7d72604854beddc6b202e1e5b.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 11]: https://s1.51cto.com/images/blog/201906/13/42ed5d3375eae20d9ec8da617e13f8b0.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 12]: https://s1.51cto.com/images/blog/201906/13/3fed450ae5214a678c9d3df2dc359244.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 13]: https://s1.51cto.com/images/blog/201906/13/18683dc4c5860a8adf61066039c26770.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=