[大数据]Linux MySQL—备份与恢复

备份类型

冷备 热备 离线 在线? 物理 逻辑

一般分为物理备份(物理文件)和逻辑备份(sql语句)

物理备份 只要备份物理文件 速度快不跨平台 linux windos

逻辑备份 sql语句的备份 速度慢 跨平台 linxu sql --- > windos

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

逻辑备份 不区分引擎

mysqldump -u root -p123 --all-databases > /all.sql    #全备

删掉data目录

rm -rf /usr/local/mysql/data

初始化数据库(修改权限)

mysql -u root < all.sql

mysqldump -u root -p123 --databases mydb >> mydb.sql 备一个库

mysql -u root -p123 < mydb.sql 恢复

mysqldump -u root -p123 --databases mydb db > mydb.db.sql备多个库

mysql -u root <mydb.db.sql

mysqldump -u root -p123 dpt emp > emp.sql 备份表

mysql -u root -p123 dpt < emp.sql 恢复

mysqldump -u root -p123 mydb emp dpt> /backup/emp.sql 多表备份

实验过程:

先做完全备份

模拟损坏:

rm -rf /usr/local/mysql/data

修复:

1.杀掉进程

pkill mysqld

2.创建目录data 修改拥有者和所属组 并且初始化

mkdir /usr/local/mysql/data

chown -R mysql.mysql /usr/local/mysql/

/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data

3.启动mysql

systemctl restart mysqldd

4.修改密码

mysql -u root -p'密码'

set password='123';

5.恢复数据

mysql -uroot -p123 </backup/all.sql

导入导出

mysql> show global variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.00 sec)

? 当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出

? 当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下

? 当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

vim /etc/my.cnf

secure_file_priv =

表的导出和导入

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
|    2 | dog   |
+------+-------+
4 rows in set (0.00 sec)

[root@localhost ~]# chmod mysql.mysql /backup/
mysql> select * from t11 into outfile '/backup/a.txt';
Query OK, 4 rows affected (0.00 sec)


[root@localhost ~]# cat /backup/a.txt
1       tong
1       tom
3       jerry
2       dog


mysql> delete from t11;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from t11;
Empty set (0.00 sec)



mysql> load data infile '/backup/a.txt' into table t11 fields terminated by '\t' lines terminated by '\n' ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t11;                                                              +------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
|    2 | dog   |
+------+-------+
4 rows in set (0.00 sec)



指定列分隔符和行分隔符

mysql> select * from emp into outfile '/backup/c.txt' fields terminated by ',' lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)



[root@localhost ~]# cat /backup/c.txt
1,robin,30,200,10000
2,zorro,25,200,20000
3,tom,22,200,30000
4,jerry,31,100,40000
5,jack,32,100,50000
6,rose,33,100,60000
7,king,40,300,70000
8,jean,41,300,80000
9,natasha,42,300,90000
10,xinghh,43,300,100000

lvm的快照(mysql备份)

1.将lv挂载mysql数据目录data( 注意: 自动挂载 /etc/fstab)

a. 先停止数据库;mv /usr/local/mysql/data /tmp

b. 创建lv 并且挂载到 /usr/local/mysql/data

c. mv /tmp/data/* /usr/local/mysql/data

b. chown -R mysql.mysql /usr/local/mysql

e. 启动数据库

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.备份(tar)

mount /dev/vgmysql/lvmysql-snampshot /opt

[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.快照大小

3.使用源lv时快照使用率会上升(服务器最空闲时)

echo " FLUSH TABLES WITH READ LOCK; system lvcreate -L 300M -s -n /dev/vgmysql/lvmysql-snampshot /dev/vgmysql/lvmysql ; unlock tables" | mysql

二进制日志的使用log-bin

binlog日志是把数据库的每一个变化都记载到一个专用的文件里,这种文件叫日志文件,mysql默认只打开错误日志文件,因为过多的日志会影响系统的处理性能

启用方法

1./usr/local/mysql/bin/mysqld_safe --log-bin --user=mysql&

2.vim /etc/my.cnf

[mysqld]

server-id=1

log-bin=master #日志名称

log-bin-index=master

max-binlog-size=防止binlog大小 =100M 达到值可以自动切换

max-binlog-size 的默认值时1GB 不能设置该值超过1GB 或者小于4096字节

1.开启binlog日志

vim /etc/my.cnf

[mysqld]

server-id=1

log-bin=master 日志名称

log-bin-index=master

查询日志状态

mysql中

mysql> show binlog events\G

mysql中显示语句

mysql> set binlog_rows_query_log_events=1;

系统中

[root@localhost data]# mysqlbinlog -v /usr/local/mysql/data/mysql.000001

-v 显示具体语句信息

binlog日志的模式

1、Statement Level模式（早期默认）

简介：每一条会修改数据的sql都会记录到master的bin-log中。Slave在复制的时候sql线程会解析成和原来master端执行过的相同语句来执行。

优点：不需要记录每一行数据的变化，减少bin-log的日志量，节约IO，提高性能。因为他只记录在master上所执行语句的细节，以及执行语句时候的上下文的信息。

缺点：很多新功能的加入在复制的时候容易导致出现问题。

delete from t1;

日志中直接显示语句

mysql> show binlog events\G

[root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql.000001

2、Row Level 模式：(默认)

简介:日志中会记录成每一行数据被修改的模式，然后再slave 端在对相同的数据进行修改.

优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息。仅仅只需要记录那一条记录被修改了。所以row level的日志内容会非常清楚记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特点情况下的存储过程，或function 以及triggeer的调用和触发无法被正确复制的问题.

缺点：所有执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，可能会产生大量的日志呢。

delete from t1;

mysql> show global variables like '%binlog_format%';

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

| Variable_name | Value |

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

| binlog_format | ROW |

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

系统查看日志信息

[root@localhost data]# mysqlbinlog -v /usr/local/mysql/data/mysql.000001

mysql中查看语句

mysql> set binlog_rows_query_log_events=1;

mysql> show binlog events\G

*************************** 10. row ***************************

Log_name: mysql.000001

Pos: 574

Event_type: Rows_query

Server_id: 1

End_log_pos: 653

Info: # insert into t10 values(1,'robin'),(2,'zorro'),(3,'tom')

*************************** 11. row ***************************

3、Mixed （前两种的混合模式）：根据执行的每一条具体的sql语句来区分对待记录日志的形式；

如何设置模式:

[mysqld]

innodb_file_per_table=1

secure_file_priv =

server-id=1

log_bin=mysql

log_bin_index=mysql

binlog_format={statement|row|mixed}

binlog日志的切换

情况1:

mysqld服务重新启动

情况2:

mysql> flush logs;

情况3:

[root@localhost data]# mysqladmin -u root -p123 'flush-logs'

查看其他日志

mysql> show binlog events in "mysql.000003"\G

模拟恢复:

日志和数据分离

[mysqld]

innodb_file_per_table=1

secure_file_priv =

server-id=1

log_bin=/mysql-log/master

log_bin_index=/mysql-log/master

binlog_format=row

log-error=/mysql-log/localhost.err

[root@localhost data]# mkdir /mysql_log

[root@localhost data]# chown -R mysql.mysql /mysql_log

场景1:物理删除

1.完全备份做好

2.开启binglog日志

创建数据库zorro

创建表t1

插入数据3条

flush logs;

插入数据2条

flush logs;

模拟错误:

rm -rf /usr/local/mysql/data/*

恢复:

1.恢复全备

停止mysql进程

关闭binlog日志

[root@localhost data]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data

登录并修改秘密(默认密码来自日志文件)

mysql> set password=password("Robin_123");

恢复all.sql

[root@localhost data]# mysql -u root -pRobin_123 < /backup/all.sql

[root@localhost data]# systemctl restart mysqldd

binlog日志恢复数据

[root@localhost binlog]# mysqlbinlog /binlog/master.000001 | mysql -u root -p123

[root@localhost binlog]# mysqlbinlog /binlog/master.000002 | mysql -u root -p123

[root@localhost binlog]# mysqlbinlog /binlog/master.000003 | mysql -u root -p123

确认恢复成功

打开binlog,正常运行

场景2:逻辑删除

1.完全备份做好

2.开启binglog日志

创建数据库hello

创建表world

插入数据3条

删除2条数据

flush logs;

插入数据1条

删除所有数据

插入数据2条

删除1条数据

flush logs;

插入2条数据

恢复所有插入数据

完全恢复:

停止mysql进程

关闭binlog日志

[root@localhost data]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data

登录并修改秘密(默认密码来自日志文件)

mysql> set password=password("Robin_123");

恢复all.sql

[root@localhost data]# mysql -u root -pRobin_123 < /backup/all.sql

[root@localhost data]# systemctl restart mysqldd

恢复数据

[root@localhost binlog]# mysqlbinlog --stop-position=1486 master.000001 | mysql -u root -p123

[root@localhost binlog]# mysqlbinlog --start-datetime="2018-04-24 14:52:28" master.000002 | mysql -u root -p123

[root@localhost binlog]# mysqlbinlog master.000003 | mysql -u root -p123

echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 50M -s /dev/vg01/lv_mysql;unlock tables;" |mysql -p123

mysql解决启动问题

1. 是否存在mysql进程

netstat -anplt |grep :3306

ps -ef | grep mysql

2.权限问题 拥有者和所属组必须是mysql 包括目录下所有文件

ls -l /usr/local/mysql/data

3.看日志

/usr/local/mysql/data/主机名.err

rpm包配置文件必须写log-error=/var/log/mysqld.log

/var/log/mysqld.log