Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

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

hwff 2019-02-22 15:12:00 阅读数:167 评论数:0 点赞数:0 收藏数:0

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;" 
版权声明
本文为[hwff]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/fllf/p/10417431.html

编程之旅,人生之路,不止于编程,还有诗和远方。
阅代码原理,看框架知识,学企业实践;
赏诗词,读日记,踏人生之路,观世界之行;

支付宝红包,每日可领