技术流ken

运维拯救世界

每天五分钟轻松学运维2019-05-18:数据库主从复制实现数据的实时备份–技术流ken

现状及环境

 

之前一直是使用脚本进行数据库的定期备份,但是发现效果并不好,数据库量大且不能保证数据的实时同步。所以就转向使用数据库的主从复制进行数据的备份。

 

一台云服务器,一台本地服务器

本地服务器数据库实时备份数据

 

mysqldump命令

 

mysqldump

 

常用选项:

–databases db1 db2….:指定要备份的一个或者多个数据库

–all-databases:备份全部的数据库

–lock-all-tables:在备份数据期间,锁定全部的表(读锁)

–lock-tables:在备份期间仅仅锁定正在备份的表(读锁)

–flush-logs:备份前,并且施加了锁之后,刷新日志

–single-transaction:基于事务的方式做备份,是热备,仅仅支持事务的存储引擎为innodb才能用热备  <<不能与lock-tanle一起用

–master-data=0|1|2

0:不记录二进制日志信息

1:将二进制日志文件信息记录到备份文件中

2:将二进制日志文件信息记录到备份文件中,但是所记录到的信息是被注释掉的即下面的内容

— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000229′, MASTER_LOG_POS=194;

 

云服务器数据库备份

 

第一步:对线上服务器保存网页信息的数据库ken进行备份

[root@ken ~]# mysqldump -uroot -p ken –single-transaction –flush-logs –master-data=2 > ken.sql

 

第二步:线下服务器拉取备份数据库

root@kvm13014:37:39~#scp 152.136.127.116:/root/ken.sql .
The authenticity of host ‘152.136.127.116 (152.136.127.116)’ can’t be established.
ECDSA key fingerprint is SHA256:WPTZQzietsJ3IpCGlnFn3k3ZUMmBUBEmppOvD0FK3Xg.
ECDSA key fingerprint is MD5:ef:f7:30:fc:45:d4:bb:bc:e5:17:d0:3d:84:3e:06:71.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘152.136.127.116’ (ECDSA) to the list of known hosts.
root@152.136.127.116’s password:
ken.sql 0% 0 0.0KB/s –:– ETA
ken.sql 100% 9599KB 86.0KB/s 01:51
root@kvm13014:39:51~#
root@kvm13014:39:51~#ls
ken.sql test

 

第三步:导入到数据库

MariaDB [(none)]> create database ken
-> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> use ken;
Database changed
MariaDB [ken]> source /root/ken.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

….

 

第四步:查看备份其实文件及位置

[root@ken ~]# more ken.sql
— MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)

— Host: localhost Database: ken
— ——————————————————
— Server version 5.5.60-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


— Position to start replication or point-in-time recovery from

— CHANGE MASTER TO MASTER_LOG_FILE=’on.000005′, MASTER_LOG_POS=245;    #关键位置,主从时指定

 

 

第五步:主从复制

MariaDB [ken]> change master to MASTER_HOST=’152.136.127.116′,MASTER_USER=’ken’,MASTER_PASSWORD=’123′,MASTER_LOG_FILE=’on.000005′,MASTER_LOG_POS=245;

 

第六步:查看状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 152.136.127.116
Master_User: ken
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: on.000005
Read_Master_Log_Pos: 27421
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 27702
Relay_Master_Log_File: on.000005
Slave_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: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 27421
Relay_Log_Space: 28002
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)

 

补充:如果想要停掉主从执行如下的两个命令

stop slave

reset slave all

 

 

验证主从复制

 

第一步:线上服务器创建数据库

MariaDB [(none)]> create database kenken;
Query OK, 1 row affected (0.00 sec)

 

第二步:线下服务器进行查看

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| ken |
| kenken |
| mysql |
| performance_schema |
| test |
+——————–+

 

 

验证成功!

发表评论

电子邮件地址不会被公开。