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

目录

• Mysql备份及主从同步
  • Mysql备份
    • 1. mysqldump全量备份
      • 1.1 mysqldump备份原理
      • 1.2 msyqldump命令详解
      • 1.2 mysqldump备份示例
      • 1.3 备份恢复
    • 2.log_bin增量备份
      • 2.1 开启log_bin功能
      • 2.2 利用mysqlbinlog命令导出sql文件
      • 2.3 备份恢复
  • Mysql主从同步配置
    • 1.1主从同步过程
    • 1.2 配置
      • 主库配置
      • 从库配置

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;"