現(xiàn)狀及環(huán)境
之前一直是使用腳本進(jìn)行數(shù)據(jù)庫(kù)的定期備份，但是發(fā)現(xiàn)效果并不好，數(shù)據(jù)庫(kù)量大且不能保證數(shù)據(jù)的實(shí)時(shí)同步。所以就轉(zhuǎn)向使用數(shù)據(jù)庫(kù)的主從復(fù)制進(jìn)行數(shù)據(jù)的備份。
一臺(tái)云服務(wù)器，一臺(tái)本地服務(wù)器
本地服務(wù)器數(shù)據(jù)庫(kù)實(shí)時(shí)備份數(shù)據(jù)
mysqldump命令
mysqldump
常用選項(xiàng)：
–databases db1 db2….：指定要備份的一個(gè)或者多個(gè)數(shù)據(jù)庫(kù)
–all-databases：備份全部的數(shù)據(jù)庫(kù)
–lock-all-tables：在備份數(shù)據(jù)期間，鎖定全部的表（讀鎖）
–lock-tables：在備份期間僅僅鎖定正在備份的表（讀鎖）
–flush-logs：備份前，并且施加了鎖之后，刷新日志
–single-transaction：基于事務(wù)的方式做備份，是熱備，僅僅支持事務(wù)的存儲(chǔ)引擎為innodb才能用熱備 <<不能與lock-tanle一起用
–master-data=0|1|2
0：不記錄二進(jìn)制日志信息
1：將二進(jìn)制日志文件信息記錄到備份文件中
2：將二進(jìn)制日志文件信息記錄到備份文件中，但是所記錄到的信息是被注釋掉的即下面的內(nèi)容
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000229′, MASTER_LOG_POS=194;
云服務(wù)器數(shù)據(jù)庫(kù)備份
第一步：對(duì)線(xiàn)上服務(wù)器保存網(wǎng)頁(yè)信息的數(shù)據(jù)庫(kù)ken進(jìn)行備份
[root@ken ~]# mysqldump -uroot -p ken –single-transaction –flush-logs –master-data=2 > ken.sql
第二步：線(xiàn)下服務(wù)器拉取備份數(shù)據(jù)庫(kù)
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
第三步：導(dǎo)入到數(shù)據(jù)庫(kù)
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)
….
第四步：查看備份其實(shí)文件及位置
[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; #關(guān)鍵位置，主從時(shí)指定
第五步：主從復(fù)制
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)
補(bǔ)充：如果想要停掉主從執(zhí)行如下的兩個(gè)命令
stop slave
reset slave all
驗(yàn)證主從復(fù)制
第一步：線(xiàn)上服務(wù)器創(chuàng)建數(shù)據(jù)庫(kù)
MariaDB [(none)]> create database kenken;
Query OK, 1 row affected (0.00 sec)
第二步：線(xiàn)下服務(wù)器進(jìn)行查看
MariaDB [(none)]> show databases;
——————–
| Database |
——————–
| information_schema |
| ken |
| kenken |
| mysql |
| performance_schema |
| test |
——————–
驗(yàn)證成功！
聯(lián)系客服
