mysql备份、主从

xingliuhua 收录于笔记

2019-03-02 约 2975 字预计阅读 6 分钟

备份和恢复

mysqldump命令备份

mysqldump命令把数据库备份为一个文本文件，包含了很多create和insert语句，使用这些语句就可以重新插入和备份。我们可以直接对数据库备份，也可以对具体某些表进行导出。

mysqldump -u user -h host -p password dbname [tablename…] > filename.sql

数据恢复

mysql -u user -p [dbname] < filename.sql 如果导出的语句中包含创建数据库的语句就不用指定数据库名了

数据库迁移

我们可能需要安装新的数据库、mysql版本更新等原因而迁移数据库。

迁移其实就是导出和恢复的过程，当然如果主版本号相同我们还可以直接拷贝数据库文件（只适用于MyISAM引擎）

Mysql Binlog格式介绍

Mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW！

Mysql默认是使用Statement日志格式，推荐使用MIXED。由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。

Statement

每一条会修改数据的sql都会记录在binlog中。

**优点：**不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

**缺点：**由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。

Row:

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

**缺点：**所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

Mixedlevel:

是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog

主从复制

主服务器master把数据复制到多台从服务器slaves。主从一般是一个主，多个从。也可以是链式的a->b->c ,这样b既是主又是从。主从复制主要分以下步骤：

主服务器将数据的更新记录到二进制日志（Binary log）中，用于记录二进制日志事件，这一步由主库线程完成；
从库将主库的二进制日志复制到本地的中继日志（Relay log），这一步由从库 I/O 线程完成；
从库读取中继日志中的事件，将其重放到数据中，这一步由从库 SQL 线程完成。

流程

可以看到：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令，并执行。

备库设置成只读了，还怎么跟主库保持同步更新呢？这个问题，你不用担心。因为 readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

操作步骤

master配置

[mysqld]
## 设置server_id，一般设置为IP,注意要唯一
server_id=100  
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql  
## 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin=edu-mysql-bin  
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed  
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

创建复制账户，授予权限

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

这里主要是要授予用户REPLICATION SLAVE权限和REPLICATION CLIENT权限

slave配置

[mysqld]
## 设置server_id，一般设置为IP,注意要唯一
server_id=101  
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=edu-mysql-slave1-bin  
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed  
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062  
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin  
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1  
## 防止改变数据(除了特殊的线程)
read_only=1

查看master status

show master status;

记录下返回结果的File列和Position列的值

Slave中设置Master信息

change master to master_host='192.168.1.100', master_user='slave', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=1389, master_connect_retry=30;

上面执行的命令的解释：

master_host=’192.168.1.100′ ## Master的IP地址

master_user=’slave’ ## 用于同步数据的用户（在Master中授权的用户）

master_password=’123456′ ## 同步数据用户的密码

master_port=3306 ## Master数据库服务的端口

masterlogfile=’edu-mysql-bin.000001′ ##指定Slave从哪个日志文件开始读复制数据（Master上执行命令的结果的File字段）

masterlogpos=429 ## 从哪个POSITION号开始读（Master上执行命令的结果的Position字段）

masterconnectretry=30 ##当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。

开始同步

start slave;

show slave status; 查询查看主从同步状态，会发现SlaveIORunning和SlaveSQLRunning是Yes了，表明开启成功

读写分离

主服务只负责写，从服务器只负责读，可以达到负载均衡的效果。我们可以在程序中指定连接地址，区分读写数据库。这里用第三方mysql proxy来读写分离。它是位于客户端与mysql数据库之间的程序。我们可以指定主、从的地址，它能自动的做负载均衡，对程序是透明的。

目录