目录
MySQL 备份及主从同步
MySQL 备份
1. mysqldump 全量备份
2.log_bin 增量备份
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;"
来源: https://www.cnblogs.com/fllf/p/10417431.html