MySQL實戰系列4: 主從複制、讀寫分離、備份恢複全解析

作者介紹

索甯擅長python開發、mysql、前端等衆多技術領域曾負責衆多企業安全架構解決方案涉獵行業有媒體、出版社、航空運輸、醫療、軍隊、政府、教育等。

一、mysql主從複制

1、簡介

我們為什麼要用主從複制

主從複制目的

可以做資料庫的實時備份保證資料的完整性

可做讀寫分離主伺服器隻管寫從伺服器隻管讀這樣可以提升整體性能。

原理圖

從上圖可以看出同步是靠log檔案同步讀寫完成的。

2、更改配置檔案

兩天機器都操作確定 server-id 要不同通常主id要小于從id。一定注意。

# 3306和3307分别代表2台機器

# 打開log-bin,并使server-id不一樣

#vim /data/3306/my.cnf

log-bin = /data/3306/mysql-bin

server-id = 1

#vim /data/3307/my.cnf

log-bin = /data/3307/mysql-bin

server-id = 3

#檢查

1、

[root@bogon ~]# egrep "log-bin|server-id" /data/3306/my.cnf

[root@bogon ~]# egrep "log-bin|server-id" /data/3307/my.cnf

2、

[root@localhost ~]# mysql -uroot -p -s /data/3306/mysql.sock -e "show variables like 'log_bin';"

enter password:

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

| variable_name | value |

| log_bin | on | # on 為開始開啟成功

3、建立用于從庫複制的賬号rep

通常會建立一個用于主從複制的專用賬戶不要忘記授權。

# 主庫授權允許從庫來連接配接我取日志

[root@localhost ~]# mysql -uroot -p -s /data/3306/mysql.sock

# 允許從庫192.168.200網段連接配接賬号rep密碼nick。

mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by 'nick';

query ok, 0 rows affected (0.00 sec)

mysql> flush privileges;

# 檢查建立的rep賬号

mysql> select user,host from mysql.user;

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

| user | host |

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

| root | 127.0.0.1 |

| rep | 192.168.200.% |

| root | localhost |

| root | localhost.localdomain |

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

7 rows in set (0.00 sec)

4、備份主庫及恢複到從庫

把主庫現有資料備份下來再恢複到從庫此時兩個主機的資料一緻。

如果事先有資料的話這不不能忘。

1)在主庫上加鎖使隻有隻讀權限。

mysql> flush table with read lock;

#5.1、5.5鎖表指令略有不同。

# 5.1鎖表flush tables with read lock;

# 5.5鎖表flush table with read lock;

2)記住就是這個點備份的。

mysql> show master status;

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

| mysql-bin.000013 | 410 | | |

1 row in set (0.00 sec)

3)克隆視窗備份資料。

[root@bogon ~]# mysqldump -uroot -p -s /data/3306/mysql.sock -a -b --events --master-data=2|gzip >/opt/rep.sql.gz

參數 -a備份所有的

#看rep.sql.gz參數

vim /opt/rep.sql.gz

-- change master to master_log_file='mysql-bin.000013', master_log_pos=410;

4)檢視master status數值是否正常。

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

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

| mysql-bin.000013 | 410 | | |

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

5)解鎖庫

mysql> unlock tables;

6)恢複到從庫

[root@bogon ~]# gunzip < /opt/rep.sql.gz | mysql -uroot -p -s /data/3307/mysql.sock

5、配置從庫及生效

更改從庫和主庫的連接配接參數配置生效。檢查就成功了

1)進入從庫。

[root@bogon ~]# mysql -uroot -p -s /data/3307/mysql.sock

2)更改從屬伺服器用于與主伺服器進行連接配接和通訊的參數。

mysql> change master to

master_host='192.168.200.98',

master_port=3306,

master_user='rep',

master_password='nick',

master_log_file='mysql-bin.000013',

master_log_pos=410;

query ok, 0 rows affected (0.01 sec)

3)檢視更改的參數。

[root@localhost ~]# cd /data/3307/data/

[root@localhost data]# cat master.info

mysql-bin.000013

410

192.168.200.98

rep

nick

3306

1800.000

4)生效

mysql> start slave;

5)檢查下列參數符合則正常

mysql> show slave status\g

relay_master_log_file: mysql-bin.000013

slave_io_running: yes #取logo。

slave_sql_running: yes #讀relay-bin、logo,寫資料。

seconds_behind_master: 0 #落後主庫的秒數。

6)檢視relay-bin.logo。

[root@localhost 3307]# cd /data/3307

[root@localhost 3307]# ll

總用量 48

drwxr-xr-x. 9 mysql mysql 4096 10月 29 18:52 data

-rw-r--r--. 1 mysql mysql 1900 10月 29 11:45 my.cnf

-rwx------. 1 root root 1307 10月 20 17:06 mysql

-rw-rw----. 1 mysql mysql 6 10月 29 11:00 mysqld.pid

-rw-r-----. 1 mysql mysql 15090 10月 29 18:49 mysql_nick3307.err

srwxrwxrwx. 1 mysql mysql 0 10月 29 11:00 mysql.sock

-rw-rw----. 1 mysql mysql 150 10月 29 18:49 relay-bin.000001

-rw-rw----. 1 mysql mysql 340 10月 29 18:52 relay-bin.000002

-rw-rw----. 1 mysql mysql 56 10月 29 18:49 relay-bin.index

-rw-rw----. 1 mysql mysql 53 10月 29 18:52 relay-log.info

7)檢視relay-log.info。

[root@localhost 3307]# cat relay-log.info

/data/3307/relay-bin.000002

340

497

8)檢視master.info。

[root@localhost 3307]# cat data/master.info

6、讀寫分離

讀寫分離在生産環境比比皆是也是必備技能。

忽略mysql主從複制授權表同步讀寫分離。

[root@bogon 3306]# vim my.cnf

#添加以下四行

replicate-ignore-db = mysql

binlog-ignore-db = mysql

binlog-ignore-db = performance_schema

binlog-ignore-db = information_schema

1)通過read-only參數防止資料寫入從庫的方法。

#修改配置檔案。

vim /data/3307/my.cnf

[mysqld]

read-only

#對使用者授權事不能指定有super或all privileges權限。不然沒效果。

#建立賬戶suoning并重新整理權限。

mysql> grant select,insert,update,delete on *.* to 'suoning'@'localhost' identified by '123';

#用建立賬戶登入并建立庫

[root@bogon 3307]# mysql -usuoning -p123 -s /data/3307/mysql.sock

mysql> create user kangkangkang@'192.%' identified by 'old123';

error 1290 (hy000): the mysql server is running with the --read-only option so it cannot execute this statement

2)主從同步故障

a. last_sql_errno: 1007

stop slave;

set global sql_slave_skip_counter = 1;

start slave;

b.忽略

skip-name-resolve #忽略名字解析

slave-skip-errors = 1032,1062,1007 #忽略故障編号

3)從庫開啟bin-log

vim my.cnf

log-slave-updates #表示從庫記錄bin-log

expire_logs_days = 7 #保留7天bin-log。

7、主當機

我們來模拟一下如果主機當機了那我們該如何讓從快速替換讓損失降到最小當然了雙機熱備也是一個不錯的選擇那下節會給大家講解。

一主多從的環境下如果主當機了選一台從做主繼續和其它從同步。

a.檢視每個從庫的master.info看誰的更靠前最新更大丢的資料最少。

[root@localhost 3307]# cat /data/3307/data/master.info

mysql-bin.000015

326

b.確定所有relay log全部更新完畢。

在每個從庫上執行stop slave io_thread;show processlist;

知道看到has read all relay log;表示從庫更新都執行完畢。

c.登陸mysql -uroot -p -s /data/3307/mysql.sock

reset master;

quit

d.進入到資料庫目錄删除master.info relay-log.info

cd /data/3307/data/

rm -f master.info relay-log.info

e. 3307提升為主庫

開啟log-bin = /data/3307/mysql-bin

如存在log-slave-updatesread-only等一定要注釋。

/data/3307/mysql restart

f.其它從庫操作

change master to master_host ='192.168.200.98';

show slave status\g

8、雙主

使用主主前提表的主鍵自增。

雙主情況下id會是這樣情況通過程式指定id寫庫m11,3,5m22,4,6。

[root@localhost 3307]# vim my.cnf

auto_increment_increment = 2

auto_increment_offset = 2

[root@localhost 3307]# ./mysql restart

[root@localhost 3306]# vim my.cnf

auto_increment_offset = 1

log-slave-updates

[root@localhost 3306]# ./mysql restart

[root@localhost 3306]# mysqldump -uroot -pnick -s /data/3307/mysql.sock -a -b --master-data=1 -x --events > /opt/3307bak.sql

[root@localhost 3306]# mysql -uroot -pnick -s /data/3306/mysql.sock < /opt/3307bak.sql

master_port=3307,

master_user='rep',

master_password='nick';

mysql> show slave status \g

二、mysql備份及恢複

1、備份單個資料庫

最基礎的備份單個資料庫。

1>文法mysqldump –u 使用者名 –p 資料庫名> 備份的資料庫名

2>備份nick_defailt資料庫檢視内容。

[root@localhost ~]# mysqldump -uroot -p -b nick_defailt >/opt/mysql_nick_defailt.bak

[root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_nick_defailt.bak

drop table if exists `oldsuo`;

create table `oldsuo` (

`id` int(4) not null,

`name` char(20) not null,

`age` tinyint(2) not null default '0',

`dept` varchar(16) default null

) engine=myisam default charset=latin1;

lock tables `oldsuo` write;

insert into `oldsuo` values (2,'ç´¢å®',0,null),(3,'ç´¢å°¼',0,null),(4,'åº•åº•',0,null);

unlock tables;

drop table if exists `student`;

create table `student` (

`qq` varchar(15) default null,

`id` int(4) not null auto_increment,

`suo` int(4) default null,

`dept` varchar(16) default null,

`sex` char(4) default null,

primary key (`id`),

key `index_name` (`name`)

) engine=myisam auto_increment=8 default charset=latin1;

lock tables `student` write;

insert into `student` values (null,2,'oldsuo',null,0,null,null),(null,3,'kangknag',null,0,null,null),(null,4,'kangkang',null,0,null,null),(null,5,'oldsuo',null,0,null,null),(null,6,'kangknag',null,0,null,null),(null,7,'kangkang',null,0,null,null);

3>啟用壓縮備份資料庫

[root@localhost~]#mysqldump -uroot -p -b nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz

[root@localhost ~]# ll /opt/

總用量 28

-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sq2

-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sql

-rw-r--r--. 1 root root 1002 10月 27 11:55 mysql_nick_defailt.bak

-rw-r--r--. 1 root root 1002 10月 27 11:56 mysql_nick_defailt.bak.gz

-rw-r--r--. 1 root root 3201 10月 27 11:46 mysql_nick_defailt_b.bak

drwxr-xr-x. 2 root root 4096 11月 22 2013 rh

-rw-r--r--. 1 root root 1396 10月 24 16:11 student_bak.sql

4>恢複nick_defailt資料庫

[root@localhost ~]# mysql -uroot -p nick_defailt </opt/mysql_nick_defailt.bak

#加-b恢複方法

[root@localhost ~]# mysql -uroot -p </opt/mysql_nick_defailt_b.bak

5>總結

1、備份用-b參數。增加use db,和create database的資訊。

2、用gzip對備份的資料壓縮。

2、備份多個資料庫

備份多個資料庫的情況呢

#多個資料庫名中間加空格

[root@localhost ~]# mysqldump -uroot -p -b nick_defailt oldsuo oldsuo_1|gzip>/opt/mul.sql.gz

3、備份單個及多個表

那如果備份單個和多個表怎麼辦

1>文法mysqldump -u 使用者名 -p 資料庫名表名 > 備份的檔案名

[root@localhost ~]# mysqldump -uroot -p nick_defailt student >/opt/mysql_nick_defailt_student.bak

2>文法mysqldump -u 使用者名 -p 資料庫名表名1 表名2 > 備份的檔案名

[root@localhost ~]# mysqldump -uroot -p nick_defailt student oldsuo >/opt/mysql_nick_defailt.bak

4、mysqldump 的參數

mysqldump 的關鍵參數

-b指定多個庫增加建庫語句和use語句。

--compact去掉注釋适合調試輸出生産不用。

-a 備份所有庫。

-f重新整理binlog日志。

--master-data 增加binlog日志檔案名及對應的位置點。

-x--lock-all-tables

-l,--locktables

-d 隻備份表結構

-t 隻備份資料

--single-transaction 适合innodb事務資料庫備份。

5、增量恢複

重要的來了生産環境一般是增量備份與恢複所謂增量就是在原資料的基礎上繼續添加資料不必每次都重新添加省時省力。

a增量恢複必備條件

1.開啟mysql資料庫log-bin參數記錄binlog日志。

[root@localhost 3306]# grep log-bin /data/3306/my.cnf

2.存在資料庫全備。

b生産環境 mysqldump備份指令

# 進行資料庫全備生産環境還通過定時任務每日淩晨執行

mysqldump -uroot -pnick -s /data/3306/mysql.sock --default-character-set=gbk --single-transaction -f -b nick |gzip >/server/backup/mysql_$(date +%f).sql.gz

# innodb引擎備份

mysqldump -u$myuser -p$mypass -s $mysock -f --single-transaction -a -b |gzip >$data_file

# myisam引擎備份

mysqldump -u$myuser -p$mypass -s $mysock -f -a -b --lock-all-tables |gzip >$data_file

c恢複

# 通過防火牆禁止web等應用向主庫寫資料或者鎖表。讓主庫暫時停止更新然後再進行恢複。

# 誤操作删除nick庫

1.檢查全備及binlog日志

[root@localhost 3306]# cd /server/backup/

[root@localhost backup]# gzip -d mysql_2015-10-31.sql.gz

[root@localhost backup]# vim mysql_2015-10-31.sql

[root@localhost backup]# grep -i "change" mysql_2015-10-31.sql

2.立即重新整理并備份出binlog

[root@localhost 3306]# mysqladmin -uroot -pnick -s /data/3306/mysql.sock flush-logs

[root@localhost 3306]# cp /data/3306/mysql-bin.000030 /server/backup/

#誤操作log-bin,倒數第二

[root@localhost backup]# mysqlbinlog -d nick mysql-bin.000030 >bin.sql #導出為.sql格式。

[root@localhost backup]# vim bin.sql

找到語句drop database nick删除誤操作語句

3.恢複

[root@localhost backup]# mysql -uroot -pnick -s /data/3306/mysql.sock <mysql_2015-10-31.sql #恢複之前的資料庫全備

[root@localhost backup]# mysql -uroot -pnick -s /data/3306/mysql.sock nick < bin.sql

#恢複删除誤操作語言的bin-log。

# 搞定

原文釋出時間為2016-12-05

本文來自雲栖社群合作夥伴dbaplus

MySQL實戰系列4: 主從複制、讀寫分離、備份恢複全解析

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述