現(xiàn)狀及環(huán)境

之前一直是使用腳本進行數(shù)據(jù)庫的定期備份，但是發(fā)現(xiàn)效果并不好，數(shù)據(jù)庫量大且不能保證數(shù)據(jù)的實時同步。所以就轉(zhuǎn)向使用數(shù)據(jù)庫的主從復制進行數(shù)據(jù)的備份。

一臺云服務器，一臺本地服務器

本地服務器數(shù)據(jù)庫實時備份數(shù)據(jù)

mysqldump命令

mysqldump

常用選項：

–databases db1 db2….：指定要備份的一個或者多個數(shù)據(jù)庫

–all-databases：備份全部的數(shù)據(jù)庫

–lock-all-tables：在備份數(shù)據(jù)期間，鎖定全部的表（讀鎖）

–lock-tables：在備份期間僅僅鎖定正在備份的表（讀鎖）

–flush-logs：備份前，并且施加了鎖之后，刷新日志

–single-transaction：基于事務的方式做備份，是熱備，僅僅支持事務的存儲引擎為innodb才能用熱備 <<不能與lock-tanle一起用

–master-data=0|1|2

0：不記錄二進制日志信息

1：將二進制日志文件信息記錄到備份文件中

2：將二進制日志文件信息記錄到備份文件中，但是所記錄到的信息是被注釋掉的即下面的內(nèi)容

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

云服務器數(shù)據(jù)庫備份

第一步：對線上服務器保存網(wǎng)頁信息的數(shù)據(jù)庫ken進行備份

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

第二步：線下服務器拉取備份數(shù)據(jù)庫

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

第三步：導入到數(shù)據(jù)庫

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=’xxxxx′,MASTER_LOG_FILE=’on.000005′,MASTER_LOG_POS=245;

第六步：查看狀態(tài)

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)

補充：如果想要停掉主從執(zhí)行如下的兩個命令

stop slave

reset slave all

驗證主從復制

第一步：線上服務器創(chuàng)建數(shù)據(jù)庫

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 |

——————–

驗證成功！