備份

一般分為物理備份(物理文件)和邏輯備份(sql語(yǔ)句)
物理備份 只要備份物理文件 速度快不跨平臺(tái) linux windos
邏輯備份 sql語(yǔ)句的備份 速度慢 跨平臺(tái) linxu sql --- > windos

還可以分為離線備份(物理)和在線備份(邏輯)

物理備份
1.停掉數(shù)據(jù)庫(kù),cp相應(yīng)的數(shù)據(jù)庫(kù)目錄或者表文件
myIsam引擎 備份表拷貝以下文件
tt.frm 表結(jié)構(gòu)
tt.MYD 數(shù)據(jù)
tt.MYI 索引

innodb引擎 innodb hot backup收費(fèi)199$ xtarbackup 免費(fèi)

物理備份
關(guān)閉mysql
cp /usr/local/mysql/data/db/{t4.frm,t4.ibd} /backup/
cp /usr/local/mysql/data/ib* /backup/
drop table t4
恢復(fù)t4
cp /backup/t4.* /usr/local/mysql/data/db
chown mysql.mysql /usr/local/mysql/data/db/t4.*
cp /backup/ib* /usr/local/mysql/data/
/bin/cp /backup/ib* /usr/local/mysql/data/ 覆蓋掉
啟動(dòng)mysql
select * from db.t4;

ibdata1 數(shù)據(jù)
ib_logfile0\

事務(wù)信息
ib_logfile1/

物理在線備份 myisam
1.鎖定表 lock table t1 read;
2.cp 物理文件 備份
3.unlock tables;

工具mysqlhotcopy 適用于myIsam引擎 需要perl-DBD-MySQL包
/usr/local/mysql/bin/mysqlhotcopy -u root -p 123 -S /tmp/mysql.sock
db /tmp/

ls /tmp/hr

vim /etc/my.cnf
[mysqld] 服務(wù)端
innodb_file_per_table=1 獨(dú)立表空間
socket=/backup/mysql.sock

[mysqlhotcopy]
socket=/backup/mysql.sock
user=root
password=123

[mysql] 客戶端
user=root
password=123
socket=/backup/mysql.sock

練習(xí):
創(chuàng)建/mysql目錄 mysqld進(jìn)程產(chǎn)生的套接文件放到/mysql目錄下 把mysql錯(cuò)誤日志放到/mysql下

要求:mysql客戶端可以直接鏈接mysql
mysqlhotcopy 可以直接連接

創(chuàng)建帳號(hào)king 可以修改mysql.user表的用戶和host 查看權(quán)限對(duì)應(yīng)表.刪除king帳號(hào)

備份你的myfarm庫(kù)(mysqlhotcopy)

[mysqld]
log_error=/mysql/uplooking.com.err
socket=/mysql/mysql.sock

[msyql]
socket=/mysql/mysql.sock

過(guò)程：先把/etc/my.cnf改名，再初始化，將文件改回/etc/my.cnf，再啟動(dòng)服務(wù)。

邏輯備份 不區(qū)分引擎
mysqldump -u root -p123 --all-databases > /all.sql 全備
刪掉data目錄
初始化數(shù)據(jù)庫(kù)，添加默認(rèn)庫(kù)
殺死進(jìn)程(因?yàn)橛性瓉?lái)進(jìn)程，初始化時(shí)才需要?dú)⑺肋M(jìn)程)
重啟進(jìn)程
數(shù)據(jù)庫(kù)(修改權(quán)限)
mysql -u root < all.sql
mysqldump -u root -p123 --database hr >> hr.sql 備一個(gè)庫(kù)
mysql -u root < hr.sql
mysqldump -u root -p123 --databases hr db > hr.db.sql備多個(gè)庫(kù)
mysql -u root <hr.db.sql
mysqldump -u root -p123 hr emp > emp.sql 備份表
mysql -u root -p123 hr < emp.sql 恢復(fù)

表的導(dǎo)出和導(dǎo)入
select * from t9;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 1 | aa |
+------+------+
mysql> select * from t9 into outfile '/backup/a.txt';
shell>cat a.txt
1 aa
1 aa
添加
2 bb
3 cc
mysql> load data infile '/backup/a.txt' into table t9 fields terminated by '\t' lines terminated by '\n' ;
mysql> select * from t9;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 1 | aa |
| 1 | aa |
| 1 | aa |
| 2 | bb |
| 3 | cc |
+------+------+

指定列分隔符和行分隔符
select * from emp into outfile '/backup/c.txt' fields terminated by ',' lines terminated by '\n';

cat c.txt
1,aa
1,aa
1,aa
1,aa
2,bb
3,cc

練習(xí)
建立表結(jié)構(gòu)導(dǎo)入/etc/passwd文件
mysql> create table pass(a char(50),s char(50),g int,w int,h char(50),dfds char(50),fds char(50));
load data infile '/etc/passwd' into table pass fields terminated by ':' lines terminated by '\n';

lvm的快照(mysql備份)
1.將lv掛載mysql數(shù)據(jù)目錄
2.保證數(shù)據(jù)完整(全備 完全恢復(fù))
3.鎖庫(kù)
mysql> FLUSH TABLES WITH READ LOCK;
4.創(chuàng)建快照
[root@robin data]# lvcreate -L 300M -s -n /dev/vgmysql/lvmysql-snampshot /dev/vgmysql/lvmysql
5.解鎖
mysql> unlock tables;
6.備份
[root@robin opt]# tar -cvf /backup/mysql.bak.tar /opt/*
7.刪除快照
[root@robin /]# umount /opt/
[root@robin /]# lvremove /dev/vgmysql/lvmysql-snampshot

恢復(fù)
1.確定mysql進(jìn)程結(jié)束
[root@robin backup]# killall -9 mysqld
2.解壓恢復(fù)
[root@robin backup]# tar -xvf /backup/mysql.bak.tar -C /usr/local/mysql/data/

問(wèn)題:
1.鎖表(時(shí)間不好估算)
2.快照大小

二進(jìn)制日志的使用log-bin
binlog日志是把數(shù)據(jù)庫(kù)的每一個(gè)變化都記載到一個(gè)專(zhuān)用的文件里,這種文件叫日志文件,mysql默認(rèn)只打開(kāi)錯(cuò)誤日志文件,因?yàn)檫^(guò)多的日志會(huì)影響系統(tǒng)的處理性能
啟用方法
1.mysqld_safe --log-bin --user=mysql&
2.vim /etc/my.cnf
[mysqld]
log-bin=master 日志名稱(chēng)
log-bin-index=master
max-binlog-size=防止binlog大小

查看是否啟動(dòng)了binlog日志
show binlog events \G
*************************** 1. row ***************************
Log_name: 131-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.34-log, Binlog ver: 4
1 row in set (0.00 sec)

查詢第二個(gè)binlog日志
show binlog events in '131-bin.000002';

binlog文件存儲(chǔ)位置/usr/local/mysql/var/
131-bin.000001 binlog日志
131-bin.index 記錄binlog的文件個(gè)數(shù)以及名字

查看binlog文件內(nèi)容
mysqlbinlog /usr/local/mysql/var/master.000001

切換日志 (重新啟動(dòng)會(huì)產(chǎn)生新日志 mysqladmin --flush-logs)
mysql>flush logs

使用binlog恢復(fù)
如果遇到災(zāi)難事件,應(yīng)該使用最近一次制作的完整備份恢復(fù)數(shù)據(jù)庫(kù),然后使用備份之后的日志文件將數(shù)據(jù)庫(kù)恢復(fù)到最接近現(xiàn)在的可用狀態(tài).
使用日志恢復(fù)時(shí),應(yīng)依次進(jìn)行,即最早生成的日志文件最先恢復(fù):
mysqlbinlog master-bin.000001 | mysql -u root -p123
mysqlbinlog master-bin.000002 | mysql -u root -p123
mysqlbinlog master-bin.000001 master-bin.000002 |mysql
mysqlbinlog cat master-bin.index | mysql

如果在日志內(nèi)有錯(cuò)誤,我們可以根據(jù)日志里的時(shí)間進(jìn)行部分恢復(fù);
mysqlbinlog --stop-datetime='2012-03-22 23:20:44' master-bin.000004 | mysql
根據(jù)binlog日志里的字節(jié)數(shù)進(jìn)行恢復(fù)
mysqlbinlog --stop-position=274 master-bin.000004 | mysql

使用binlog-do-db可以指定記錄的數(shù)據(jù)庫(kù)；
使用binlog-ignore-db可以指定不記錄的數(shù)據(jù)庫(kù)。
注意的是：binlog-do-db和binlog-ignore-db一次只指定一個(gè)數(shù)據(jù)庫(kù)，指定多個(gè)數(shù)據(jù)庫(kù)需要多個(gè)語(yǔ)句。而且，MySQL會(huì)將所有的數(shù)據(jù)庫(kù)名稱(chēng)改成小寫(xiě)，在指定數(shù)據(jù)庫(kù)時(shí)必須全部使用小寫(xiě)名字，否則不會(huì)起作用。

實(shí)驗(yàn)環(huán)境
數(shù)據(jù)庫(kù)全備
1.開(kāi)啟binlog日志 將binlog和binglog索引文件 放到/backup下
2.創(chuàng)建庫(kù) 創(chuàng)建表
3.刷新日志 flush logs
4.插入數(shù)據(jù)
5.物理刪除庫(kù)

恢復(fù)
1.殺mysql進(jìn)程(關(guān)閉2進(jìn)制binlog)
2.初始化數(shù)據(jù)庫(kù)
3.恢復(fù)全備
4.恢復(fù)binlog日志,刪除已有日志
5.打開(kāi)binlog

實(shí)驗(yàn)環(huán)境
1.完整數(shù)據(jù)
2.開(kāi)啟binlog日志
3.創(chuàng)建庫(kù) 創(chuàng)建表
4.插入數(shù)據(jù) 2條數(shù)據(jù) 刪除所有數(shù)據(jù) 再插入兩條數(shù)據(jù)
5.切換日志
6.刪除所有數(shù)據(jù) 插入3條數(shù)據(jù)
7.刪除表
8.rm -rf /usr/local/mysql/data/*

要求:
恢復(fù)所有刪除數(shù)據(jù)

1.關(guān)閉binlog
2.殺進(jìn)程
3.初始化
4.啟動(dòng)mysql
5.恢復(fù)全備
6.恢復(fù)binlog

mysqlbinlog --start-position=1035 /backup/master.000001 | mysql -u root -p123

mysqlbinlog --start-datetime='2015-08-21 15:50:21' --stop-datetime='2015-08-21 15:50:32' /backup/master.000002 | mysql -u root -p123

MySQL 5.1 AB 復(fù)制

ab復(fù)制(主從復(fù)制)
可以讓mysql實(shí)現(xiàn):
1.數(shù)據(jù)的備份(主服務(wù)器的數(shù)據(jù)全部同步到從服務(wù)器)
2.可以負(fù)載均衡減小主服務(wù)器的壓力
3.真正實(shí)現(xiàn)mysql的讀寫(xiě)分離

配置方法
主服務(wù)器：
1.cat /etc/my.cnf
[mysqld]
log-bin=mysql_bin
server-id=1 不允許重復(fù)
2.grant replication slave on . to ‘slave‘@‘192.168.1.140‘ identified by '123';授權(quán)，指定從服務(wù)器只能復(fù)制binlog，從服務(wù)器的ip
mysql>show master status查看主服務(wù)器的運(yùn)行狀態(tài)

在從上使用slave用戶登錄測(cè)試：
3.mysql -u slave -p123 -h 192.168.1.14
4.mysqldump --all-database > db_backup.sql (主服務(wù)執(zhí)行全備)
5.mysql> show master status;找到當(dāng)前的日志文件和位置號(hào)

從：（可以不用開(kāi)啟binlog功能）
6.mysql -uroot -p123 < db_backup.sql 將主服務(wù)的全備腳本拷貝到主服務(wù)器并且導(dǎo)入主服務(wù)器
7.cat /etc/my.cnf
[mysqld]
server-id=2 和主服務(wù)器不重復(fù)就可以
master-host=192.168.1.131 主服務(wù)器ip
master-user=slave 登錄時(shí)賬號(hào)
master-password=123 mysql讀取配置文件時(shí)，知道是以從服務(wù)器的形式運(yùn)行

8.從服務(wù)器從主服務(wù)器讀取binlog產(chǎn)生問(wèn)題時(shí)
mysql -u root -p
mysql> change master to
mysql> master_host='192.168.1.131',
mysql> master_user='slave',
mysql> master_password='123',
mysql> master_log_file='',
mysql> master_log_pos=399;

9.啟動(dòng)從服務(wù)器
mysql> slave start 或者是start slave，啟動(dòng)從服務(wù)器
mysql>show slave status 查看從服務(wù)器狀態(tài)

練習(xí):
主----從(主)------從
log-slave-updates 中間這臺(tái)作用:中繼日志內(nèi)容寫(xiě)入到binlog日志

互主
[mysqld]
log-bin=mysql_bin
server-id=1
master-host=192.168.100.130
master-user=slave2
master-password=123
auto-increment-increment=2
auto-increment-offset=1

[mysqld]
log-bin=slave
server-id=2
master-host=192.168.100.128
master-user=slave
master-password=123
auto-increment-increment=2
auto-increment-offset=2

主服務(wù)器
A ip 172.16.10.1
1.A全備份
mysqldump -u root -S /var/lib/mysql/mysql.sock --all-databases >> /tmp/all.sql
2.開(kāi)啟binlog日志
vim /etc/my.cnf
[mysqld]
log-bin=master
log-bin-index=master
server-id=1

service mysqld restart

3.授權(quán)B服務(wù)器 可以傳輸binlog日志
grant replication slave on . to slave@'172.16.10.2' identified by '123';

從服務(wù)器
B ip 172.16.10.2
1.測(cè)試slave是否能正常鏈接
mysql -u slave -p123 -h 172.16.10.1

2.拷貝A服務(wù)器的全備,并且恢復(fù)
scp 172.16.10.1:/backup/all.sql ./
mysql -u root < /root/all.sql

3.配置A服務(wù)器連接地址及用戶信息
vim /etc/my.cnf
[mysqld]
server-id=2
master-user=slave
master-password=123
master-host=172.16.10.1

測(cè)試
A服務(wù)器
mysql> show master status\G

B服務(wù)器
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

A服務(wù)器做出數(shù)據(jù)改動(dòng) 查看從服務(wù)器是否有同步的變化

MySQL 5.6 AB復(fù)制

準(zhǔn)備2臺(tái)linux系統(tǒng)
保證兩臺(tái)服務(wù)器都有mysql數(shù)據(jù)庫(kù)(如果沒(méi)有的話可以打包 拷貝)

1.保證主從數(shù)據(jù)庫(kù)數(shù)據(jù)一致(主服務(wù)器全備 從服務(wù)器恢復(fù))
2.主服務(wù)器授權(quán)從服務(wù)器可以復(fù)制binlog日志（互主時(shí)必須也是slave用戶）
grant replication slave on . to 'slave'@'172.16.110.2' identified by '123';
從服務(wù)器測(cè)試連接
mysql -u slave -p123 -h 172.16.110.1
3.主服務(wù)器打開(kāi)binlog日志,設(shè)置服務(wù)器id
vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=master
log-bin-index=master

重啟服務(wù)

4.檢查主服務(wù)器日志和狀態(tài)
mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: master.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 120
Info: Server ver: 5.6.22-log, Binlog ver: 4
1 row in set (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
File: master.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

5.設(shè)置從服務(wù)器id,設(shè)置從服務(wù)器的配置項(xiàng),連接主服務(wù)器
vim /etc/my.cnf
[mysqld]
server-id=2

service mysqldd restart

mysql -u root -p123
mysql> show slave status\G 沒(méi)有信息正常
Empty set (0.06 sec)
mysql> change master to master_host='172.16.110.1', master_user='slave', master_password='123', master_log_file='master.000001', master_log_pos=120;

mysql> start slave;
mysql> show slave status\G

解決錯(cuò)誤:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

vim /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=9f74856a-47d9-11e5-a20c-000c2929c607
保證主從uuid不同(16進(jìn)制)

service mysqldd restart

再次啟動(dòng)slave
mysql> stop slave;
mysql> change master to master_host='172.16.110.1', master_user='slave', master_password='123', master_log_file='master.000001', master_log_pos=120;
mysql> start slave;
mysql> show slave status\G