mysql主从备份

备份

一般分为物理备份(物理文件)和逻辑备份(sql语句)
物理备份 只要备份物理文件 速度快不跨平台 linux windos
逻辑备份 sql语句的备份 速度慢 跨平台 linxu sql --- > windos

还可以分为离线备份(物理)和在线备份(逻辑)

物理备份
1.停掉数据库,cp相应的数据库目录或者表文件
myIsam引擎 备份表拷贝以下文件
tt.frm 表结构
tt.MYD 数据
tt.MYI 索引

innodb引擎 innodb hot backup收费199$ xtarbackup 免费

物理备份
关闭mysql
cp /usr/local/mysql/data/db/{t4.frm,t4.ibd} /backup/
cp /usr/local/mysql/data/ib* /backup/
drop table t4
恢复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/ 覆盖掉
启动mysql
select * from db.t4;

ibdata1 数据
ib_logfile0\

事务信息
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] 服务端
innodb_file_per_table=1 独立表空间
socket=/backup/mysql.sock

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

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

练习:
创建/mysql目录 mysqld进程产生的套接文件放到/mysql目录下 把mysql错误日志放到/mysql下

要求:mysql客户端可以直接链接mysql
mysqlhotcopy 可以直接连接

创建帐号king 可以修改mysql.user表的用户和host 查看权限对应表.删除king帐号

备份你的myfarm库(mysqlhotcopy)

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

[msyql]
socket=/mysql/mysql.sock

过程：先把/etc/my.cnf改名，再初始化，将文件改回/etc/my.cnf，再启动服务。

逻辑备份 不区分引擎
mysqldump -u root -p123 --all-databases > /all.sql 全备
删掉data目录
初始化数据库，添加默认库
杀死进程(因为有原来进程，初始化时才需要杀死进程)
重启进程
数据库(修改权限)
mysql -u root < all.sql
mysqldump -u root -p123 --database hr >> hr.sql 备一个库
mysql -u root < hr.sql
mysqldump -u root -p123 --databases hr db > hr.db.sql备多个库
mysql -u root <hr.db.sql
mysqldump -u root -p123 hr emp > emp.sql 备份表
mysql -u root -p123 hr < emp.sql 恢复

表的导出和导入
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

练习
建立表结构导入/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数据目录
2.保证数据完整(全备 完全恢复)
3.锁库
mysql> FLUSH TABLES WITH READ LOCK;
4.创建快照
[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

恢复
1.确定mysql进程结束
[root@robin backup]# killall -9 mysqld
2.解压恢复
[root@robin backup]# tar -xvf /backup/mysql.bak.tar -C /usr/local/mysql/data/

问题:
1.锁表(时间不好估算)
2.快照大小

二进制日志的使用log-bin
binlog日志是把数据库的每一个变化都记载到一个专用的文件里,这种文件叫日志文件,mysql默认只打开错误日志文件,因为过多的日志会影响系统的处理性能
启用方法
1.mysqld_safe --log-bin --user=mysql&
2.vim /etc/my.cnf
[mysqld]
log-bin=master 日志名称
log-bin-index=master
max-binlog-size=防止binlog大小

查看是否启动了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)

查询第二个binlog日志
show binlog events in '131-bin.000002';

binlog文件存储位置/usr/local/mysql/var/
131-bin.000001 binlog日志
131-bin.index 记录binlog的文件个数以及名字

查看binlog文件内容
mysqlbinlog /usr/local/mysql/var/master.000001

切换日志 (重新启动会产生新日志 mysqladmin --flush-logs)
mysql>flush logs

使用binlog恢复
如果遇到灾难事件,应该使用最近一次制作的完整备份恢复数据库,然后使用备份之后的日志文件将数据库恢复到最接近现在的可用状态.
使用日志恢复时,应依次进行,即最早生成的日志文件最先恢复:
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

如果在日志内有错误,我们可以根据日志里的时间进行部分恢复;
mysqlbinlog --stop-datetime='2012-03-22 23:20:44' master-bin.000004 | mysql
根据binlog日志里的字节数进行恢复
mysqlbinlog --stop-position=274 master-bin.000004 | mysql

使用binlog-do-db可以指定记录的数据库；
使用binlog-ignore-db可以指定不记录的数据库。
注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。

实验环境
数据库全备
1.开启binlog日志 将binlog和binglog索引文件 放到/backup下
2.创建库 创建表
3.刷新日志 flush logs
4.插入数据
5.物理删除库

恢复
1.杀mysql进程(关闭2进制binlog)
2.初始化数据库
3.恢复全备
4.恢复binlog日志,删除已有日志
5.打开binlog

实验环境
1.完整数据
2.开启binlog日志
3.创建库 创建表
4.插入数据 2条数据 删除所有数据 再插入两条数据
5.切换日志
6.删除所有数据 插入3条数据
7.删除表
8.rm -rf /usr/local/mysql/data/*

要求:
恢复所有删除数据

1.关闭binlog
2.杀进程
3.初始化
4.启动mysql
5.恢复全备
6.恢复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 复制

ab复制(主从复制)
可以让mysql实现:
1.数据的备份(主服务器的数据全部同步到从服务器)
2.可以负载均衡减小主服务器的压力
3.真正实现mysql的读写分离

配置方法
主服务器：
1.cat /etc/my.cnf
[mysqld]
log-bin=mysql_bin
server-id=1 不允许重复
2.grant replication slave on . to ‘slave‘@‘192.168.1.140‘ identified by '123';授权，指定从服务器只能复制binlog，从服务器的ip
mysql>show master status查看主服务器的运行状态

在从上使用slave用户登录测试：
3.mysql -u slave -p123 -h 192.168.1.14
4.mysqldump --all-database > db_backup.sql (主服务执行全备)
5.mysql> show master status;找到当前的日志文件和位置号

从：（可以不用开启binlog功能）
6.mysql -uroot -p123 < db_backup.sql 将主服务的全备脚本拷贝到主服务器并且导入主服务器
7.cat /etc/my.cnf
[mysqld]
server-id=2 和主服务器不重复就可以
master-host=192.168.1.131 主服务器ip
master-user=slave 登录时账号
master-password=123 mysql读取配置文件时，知道是以从服务器的形式运行

8.从服务器从主服务器读取binlog产生问题时
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.启动从服务器
mysql> slave start 或者是start slave，启动从服务器
mysql>show slave status 查看从服务器状态

练习:
主----从(主)------从
log-slave-updates 中间这台作用:中继日志内容写入到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

主服务器
A ip 172.16.10.1
1.A全备份
mysqldump -u root -S /var/lib/mysql/mysql.sock --all-databases >> /tmp/all.sql
2.开启binlog日志
vim /etc/my.cnf
[mysqld]
log-bin=master
log-bin-index=master
server-id=1

service mysqld restart

3.授权B服务器 可以传输binlog日志
grant replication slave on . to slave@'172.16.10.2' identified by '123';

从服务器
B ip 172.16.10.2
1.测试slave是否能正常链接
mysql -u slave -p123 -h 172.16.10.1

2.拷贝A服务器的全备,并且恢复
scp 172.16.10.1:/backup/all.sql ./
mysql -u root < /root/all.sql

3.配置A服务器连接地址及用户信息
vim /etc/my.cnf
[mysqld]
server-id=2
master-user=slave
master-password=123
master-host=172.16.10.1

测试
A服务器
mysql> show master status\G

B服务器
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

A服务器做出数据改动 查看从服务器是否有同步的变化

---

MySQL 5.6 AB复制

准备2台linux系统
保证两台服务器都有mysql数据库(如果没有的话可以打包 拷贝)

1.保证主从数据库数据一致(主服务器全备 从服务器恢复)
2.主服务器授权从服务器可以复制binlog日志（互主时必须也是slave用户）
grant replication slave on . to 'slave'@'172.16.110.2' identified by '123';
从服务器测试连接
mysql -u slave -p123 -h 172.16.110.1
3.主服务器打开binlog日志,设置服务器id
vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=master
log-bin-index=master

重启服务

4.检查主服务器日志和状态
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.设置从服务器id,设置从服务器的配置项,连接主服务器
vim /etc/my.cnf
[mysqld]
server-id=2

service mysqldd restart

mysql -u root -p123
mysql> show slave status\G 没有信息正常
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

解决错误:
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进制)

service mysqldd restart

再次启动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