|
现状及环境 之前一直是使用脚本进行数据库的定期备份，但是发现效果并不好，数据库量大且不能保证数据的实时同步。所以就转向使用数据库的主从复制进行数据的备份。 一台云服务器，一台本地服务器 本地服务器数据库实时备份数据 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=’xxxxx′,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 | ——————– 验证成功！ |
|
|
