Mysql备份恢复利用主从_Mysql数据备份恢复及主从同步

Mysql备份及主从同步

Mysql备份

1. mysqldump全量备份

1.1 mysqldump备份原理

mysqldump备份数据的过程，实际上就是把数据库从mysql库里以sql语句的形式直接输出并保存成文件的过程,备份的文件/* 到 */都是注释,其余都是sql语句，故备份恢复就相当于把所有sql语句执行一遍

1.2 msyqldump命令详解

语法：mysqldump -u username -p 数据库名 [表名]... > 文件路径

常用选项：

-A 备份所有数据库

-B DBNAME... #能接多个库，并添加创建库和切换库的语句(使用-B备份的sql文件恢复时不需要指定库，也不用手动创建库)

-F 切割binlog

--master-data=1 #1表示执行从库从主库某位置的binlog恢复 ，2表示注释执行语句

-x #锁表

-d DBNAME #只备份表结构

--compact #输出内容减少，用于调试

--no-create-info|-t #只备份数据

--single-transaction # 适合innodb事务数据库备份

1.2 mysqldump备份示例

1.备份所有库并压缩

mysqldump -u root -p -A > xx.sql

2.备份某个库

mysqldump -u root -p -B DBNAME > xx.sql

3.备份多个库

mysqldump -u root -p -B DBNAME1 DBNAME2 > xx.sql

1.3 备份恢复

mysql -uroot -p < xxx.sql

2.log_bin增量备份

2.1 开启log_bin功能

修改my.cnf中的[mysqld]：

#添加该选项

[mysqld]

log_bin=mysqlbin

生成的日志文件存放的是该功能开启后的mysql内部增删改等对数据库有更新内容的sql语句。

该功能开启前的数据并不能通过这个恢复。所以通常先是使用mysqldump全量备份，之后的数据通过log_bin生成的日志文件来恢复

若在mysqldump备份时指定了--master-data=1，会将备份数据最后的位置记录下来，则数据恢复时将大大方便log_bin从合理的位置恢复

2.2 利用mysqlbinlog命令导出sql文件

mysqlbinlog mysql-bin.00001 > 1.sql

#-d DB #只导出指定库sql语句，默认binlog会保存所有库的操作

mysqlbinlog -d test mysql-bin.00001 > 1.sql

基于指定位置导出sql语句：

mysqlbinlog mysqlbin.00001 --start-position=106 >1.sql #导出从位置106开始到文件结束的sql语句

mysqlbinlog mysqlbin.00001 --stop-position=500 >2.sql #导出从文件开始到位置为500处的sql语句

mysqlbinlog mysqlbin.00001 --start-position=106 --stop-position=500 >3.sql #导出从位置106到500的sql语句

基于指定时间导出：

mysqlbinlog --start-datetime="2019-02-22 16:52:06" mysqlbin.000001 > 1.sql

mysqlbinlog --stop-datetime="2019-02-22 16:52:06" mysqlbin.000001 > 2.sql

mysqlbinlog --start-datetime="2019-02-22 16:52:06" --stop-datetime="2019-02-22 17:52:06" mysqlbin.000001 > 3.sql

2.3 备份恢复

mysql -uroot -p < 1.sql #通常先将全量备份的数据恢复，然后用msyqlbinlog导出的文件接着全量的恢复后面的数据

Mysql主从同步配置

1.1主从同步过程

1.从库IO线程连接上主库，并请求从指定日志文件的指定位置之后的日志内容

2.主库收到来自从库的请求后，负责复制的IO线程根据请求信息读取指定日志指定位置之后的日志内容，并返回给从库的IO线程

3.从库接收到信息后，将接收到的内容依次写到从库的Relaylog文件的最末端，并读取其中主库的bin-log文件名和位置记录到master-info文件中，以便下次读取时能清楚的告诉主库"我需要从bin-log文件的哪个位置开始往后的内容"

4.从库的SQL线程检测到Relaylog中新增的内容后，会马上解析该文件中的内容，形成SQL语句并执行，从而完成同步

1.2 配置

主库配置

1.主库开启log_bin 功能，并设置server_id

[mysqld]

server_id=1111

log_bin=mysqlbin

2.主库创建主从同步的mysql账号，并授予replication，slave权限

mysql -e "GRANT replication slave *.* to 'repl'@'192.168.139.0/255.255.255.0' identified by 'repl'" -uroot -p

3.主库整库锁表，然后备份已有数据生成sql文件，并拷贝至从库服务器，完成后解锁主库

mysql -e " flush table with read lock " -uroot -p123456

mysqldump -uroot -p123456 -A --master-data=1 > repl.sql

scp repl.sql root@192.168.139.106:~

mysql -e " unlock tables " -uroot -p123456

从库配置

1.设置server_id, 从库一般不需要开启log_bin功能，除非做级联，即同时还作为其他库的主库

[mysqld]

server_id=2222 #需要跟主库不一致

2.先手动复制主库的数据到从库

mysql -uroot -p123456 < repl.sql

3.如果主库配置第三步中，备份数据时没有指定--master-data=1 ，则需要在从库中指定主库信息

mysql -e "change master to \

master_host='192.168.139.105', \

master_log_file='mysqlbin.000004',\

master_user='repl',master_password='repl',\

master_log_pos=326 "

4.从库开启同步开关

mysql -uroot -p123456 -e " start slave;"