mysqlbackup線上配置MySQL主從架構

mysqlbackup線上配置MySQL主從架構

前言：

MySQL資料庫很多情況下都是用於搭建電商平台，電商平台就意味著為公司賺錢的平台，必須24小時線上的；我們也搭建了屬於自己的電商平台，但是最近反應需要增加一台從庫來緩解主庫的讀取壓力。網上百度了很多相關的方法，總結如下：

MySQLdump搭建，該方法很簡單，但是操作的過程中需要鎖表，並停止應用。該方法適合系統未上線時操作，新手可以搭建用於學習；（  ）

Xtrabackup搭建，該方法需要瞭解Xtrabackup工具的備份還原，可以實現線上搭建主從架構。

MySQL管理之使用XtraBackup進行熱備

MySQL開源備份工具Xtrabackup備份部署

MySQL Xtrabackup備份和恢複

用XtraBackup實現MySQL的主從複製快速部署【主不鎖表】

安裝和使用 Percona 推出的 Xtrabackup 備份 MySQL

MySQL一直推薦的企業版的MySQL備份工具：MySQLbackup，抱著學習的心態就在測試環境中通過MySQLbakcup工具來搭建主從架構；

在進行以下操作之前，需要先進行以下兩個設定：主庫和備庫的參數檔案已經修改完成、在主庫上面建立主從串連使用者；

以下是詳細的整理步驟：

資料庫	主機名稱	IP地址	同步處理的使用者	備份位置
主要資料庫	Mysql01	192.168.47.152	server01	/backup
從資料庫	Mysql02	192.168.47.151

2 主庫的操作步驟

2.1 對主庫進行全備，指令碼如下

mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log

備份的目錄為/backup，請確認這個目錄的存在；

2.2 記錄這個時候主庫的binlog狀態 

mysql> show master status; 

+------------------+----------+--------------+------------------+ 

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

+------------------+----------+--------------+------------------+ 

| mysql-bin.000003 | 107 | | | 

+------------------+----------+--------------+------------------+ 

1 row in set (0.00 sec)
 

2.3 為了驗證主從是沒有問題的，這個時候可以往主庫的某個資料庫插入資料，然後再選項組 

mysql> show master status; 

+------------------+----------+--------------+------------------+ 

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

+------------------+----------+--------------+------------------+ 

| mysql-bin.000003 | 500 | | | 

+------------------+----------+--------------+------------------+ 

1 row in set (0.00 sec)

2.4 把備份從主庫拷貝到從庫 

[root@mysql01 backup]# service iptables stop 

iptables：清除防火牆規則： [確定] 

iptables：將鏈設定為政策 ACCEPT：filter [確定] 

iptables：正在卸載模組： [確定] 

root@mysql01 backup]# scp bak.tar root@mysql02:/backup/* 

Warning: Permanently added the RSA host key for IP address '192.168.47.151' to the list of known hosts. 

root@mysql02's password: 

bak.tar 100% 69MB 23.1MB/s 00:03 

需要先關閉防火牆 

3.從庫的操作步驟
3.1 進行從庫的恢複 

[root@mysql01 backup]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back 

MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26] 

Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. 

   

mysqlbackup: INFO: Starting with following command line ... 

mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql 

--backup-dir=/backup/ copy-back 

   

mysqlbackup: INFO: 

IMPORTANT: Please check that mysqlbackup run completes successfully. 

At the end of a successful 'copy-back' run mysqlbackup 

prints "mysqlbackup completed OK!". 

   

141118 16:19:35 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-18.16-19-35_copy_back.log 

   

-------------------------------------------------------------------- 

Server Repository Options: 

-------------------------------------------------------------------- 

datadir = /data/mysql 

innodb_data_home_dir = /data/mysql 

innodb_data_file_path = ibdata1:10M:autoextend 

innodb_log_group_home_dir = /data/mysql/ 

innodb_log_files_in_group = 2 

innodb_log_file_size = 5242880 

innodb_page_size = Null 

innodb_checksum_algorithm = none 

   

-------------------------------------------------------------------- 

Backup Config Options: 

-------------------------------------------------------------------- 

datadir = /backup/datadir 

innodb_data_home_dir = /backup/datadir 

innodb_data_file_path = ibdata1:10M:autoextend 

innodb_log_group_home_dir = /backup/datadir 

innodb_log_files_in_group = 2 

innodb_log_file_size = 5242880 

innodb_page_size = 16384 

innodb_checksum_algorithm = none 

   

mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 

141118 16:19:35 mysqlbackup: INFO: Copy-back operation starts with following threads 

        1 read-threads 1 write-threads 

mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin. 

        Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. 

141118 16:19:35 mysqlbackup: INFO: Copying /backup/datadir/ibdata1. 

141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'john' 

141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'mysql' 

141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'performance_schema' 

141118 16:19:37 mysqlbackup: INFO: Completing the copy of all non-innodb files. 

141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib_logfile0' 

141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib_logfile1' 

141118 16:19:39 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql 

141118 16:19:39 mysqlbackup: INFO: Copy-back operation completed successfully. 

141118 16:19:39 mysqlbackup: INFO: Finished copying backup files to '/data/mysql'
 

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁