mysql 中的備份恢復,分割區分表,主從複製,讀寫分離

# 建立管理員
create user 'backup'@'localhost' identified by '123456';

# 給管理員授權
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';

mysql -uroot -p 資料庫 < sql檔案

# 首先進行一次基於最近一次的全量備份的檔案進行一次全量恢復
mysql -uroot -p 資料庫 < sql檔案

# 然後檢視備份的sql檔案的 CHANGE MASTER 值，基於該值進行二進位制紀錄檔的還原
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=154;

# 檢視二進位制紀錄檔,根據時間點找到誤操作前一段時間的二進位制紀錄檔
cd /var/lib/mysql
mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=資料庫名 binlog.000008 | grep -B3 DELETE | more

# 記錄最早刪除記錄的節點值，執行紀錄檔匯出
mysqlbinlog --start-position=開始節點 --stop-position=結束節點 --database=資料庫 二進位制紀錄檔名 > 匯出的sql檔名
mysqlbinlog --start-position=154 --stop-position=26158 --database=laravel binlog.000007 > laravel.sql

# 對匯出的sql檔案進行全量的還原
mysql -uroot -p 資料庫 < sql檔案

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events 資料庫 > 備份檔案.sql

# 下載 libev軟體包
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
 
# 安裝 libev軟體包
yum install percona-xtrabackup-24

# 全量備份
innobackupex --user=管理員賬號 --password=密碼 --parallel=2 備份路徑
innobackupex --user=backup --password=Gzjunyu19970925. --parallel=2 /home/db_backup/

# 全量恢復，建議恢復前停止mysql服務，且清空mysql資料檔案
innobackupex --datadir=mysql資料路徑 --copy-back 備份路徑
innobackupex --datadir=/var/lib/mysql --copy-back /home/db_backup/2018-04-21_10-44-22/

# 修改mysql資料路徑的許可權為777
chmod -R 777 /var/lib/mysql

# 範圍分割區
CREATE TABLE `表名`(
    -- 資料欄位
)engine=INNODB
PARTITION BY RANGE(`欄位名稱`) (
    PARTITION 分表名稱 VALUES LESS THAN(範圍),
    PARTITION 分表名稱 VALUES LESS THAN(範圍)
);

# 雜湊分割區
create table `表名`(
    -- 資料欄位
)engine=INNODB
PARTITION BY HASH(UNIX TIMESTAMP(`欄位名稱`)) PATITIONS 4;

# 時間分割區
create table `表名`(
    -- 資料欄位
)engine=INNODB
PARTITION BY RANGE(YEAR(`欄位名稱`))(
    PARTITION p0 VALUES LESS THAN(2017),
    PARTITION p1 VALUES LESS THAN(2018),
    PARTITION p2 VALUES LESS THAN(2019)
);

# 檢視分割區情況
SELECT
    table_name,partition_name,partition_description,table_rows
FROM
    information_schema.`PARTITIONS`
WHERE table_name = '表名';

# 歸檔分割區資料，mysql資料庫版本需要大於等於5.7
-- 分割區歸檔操作步驟
-- 1.新建和分割區表欄位一致的資料表 歸檔表字首為 arch_
CREATE TABLE `歸檔表表名`(
 
)engine=INNODB
-- 2.進行資料交換 p0為分割區名
ALTER TABLE `原資料表表名` exchange PARTITION p0 WITH TABLE `歸檔表表名`;
-- 3.刪除分割區，避免對資料的再次寫入
ALTER TABLE `原資料表表名` DROP PARTITION p0;
-- 4.將歸檔表引擎設定為 archive  在檢表語句中mysql引擎必須和原資料表引擎一致，否則無法進行資料交換
ALTER TABLE `歸檔表表名` ENGINE=ARCHIVE;

-- 建議新建一個備份和主從複製的資料庫管理員
CREATE USER 'backup'@'localhost' IDENTIFIED BY '密碼';
-- 分配相關許可權
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';
-- 開啟遠端存取許可權
GRANT ALL PRIVILEGES ON *.* TO 授權使用者名稱@被授權伺服器的IP IDENTIFIED BY '授權密碼';
FLUSH PRIVILEGES;

#mysql的bin-log紀錄檔設定選項,假設做讀寫（主從），這個選項在從伺服器必須關閉
log_bin = binlog
#埠資訊，其實可以不寫
port = 3306
#主伺服器的id,這id不一定設為1，只要主從不一樣就行
server-id = 1
#要做同步的資料庫名字，可以是多個資料庫，之間用分號分割。
binlog_do_db = test

show master status;
show variables like 'log_bin';

#從伺服器的id,必須與主伺服器的id是不同
server-id = 2
#主伺服器的ip地址
master-host = 192.168.56.2
#grant授權的可複製使用者賬號
master-user = backup
#grant授權的可複製密碼
master-password = 123456
#主伺服器的mysql埠
master-port = 3306
#這個引數是用來設定在和主伺服器連線丟失的時候，重試的時間間隔，預設是60秒
master-connect-retry = 20
#需要同步的主伺服器資料庫
replicate-do-db = test

show slave statusG
 
-- 如果結果包含如下引數，則證明主從已經設定成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes