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