Linux网络服务-4-1-Mysql-1-备份概述

Linux网络服务-4-1-Mysql-1-备份概述

Scroll Down

Mysql锁:

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

( 多用户同时修改某个数据的话,而多用户修改之后的数据可能不一致,这样就可能会破坏数据库的一致性)

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作之前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

Mysql的存储引擎:

存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式。存储引擎是基于表而不是基于库的。所以存储引擎也可被称为表类型。Oracle。SqlServer等数据库只有一种存储引擎。Mysql提供了插件式的存储引擎架构。所以Mysql存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。

① MYISAN:默认引擎、插入和查询速度较快,支持全文索引,不支持事务、行级锁和外键约束等功能

② INNODB:支持事务、行级锁和外键约束等功能

③ MEMORY:工作在内存中,通过散列字段保存数据,速度快、不能永久保存

事务(Transaction)是并发控制的基本单位:

可以把一系列要执行的操作称为事务,而事务管理就是管理这些操作要么完全执行,要么完全不执行。

经典案例:银行转账,从一个账户扣款并使另一个账户增款,这两个操作要,都执行,要么都不执行。所以,应该把他们看成一个事务。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。

注:mysql中并不是所有的数据库引擎都支持事务管理,只有innodb支持事务管理。

备份和还原

1、mysqldump备份:

image.png

① 在一台mysql主机上使用mysqldump备份数据库

image.png

② 另一台主机使用mysqldump还原备份文件

使用mysqldump还原备份文件的话,需要指定一个数据库,比如这里就新创建了一个新的数据库atguigu,并将备份文件atguigu.sql还原到该库。
image.png

image.png

2、mysqlhotcopy备份

image.png

原理
mysqlhotcopy备份命令其实就相当于把/var/lib/mysql/下的数据库文件备份到用户指定的目录下,就是mysqlhotcopy有一些命令选项例如 --flushlog 可以刷新日志,可以让备份达到一个更好的效果!

备份
可以直接把mysql数据库/var/lib/mysql/下的数据库文件 拷贝 出来

还原:
可以将数据库文件拷贝到( 默认目录 )/var/lib/mysql/下即可。

3、mysql-binlog日志备份:

二进制日志(log-bin日志)功能:
所有对数据库的增、删、改操作(create、drop、update)都会被记录到该log-bin文件中。

bin-log的三种模式:

Row Level 行模式

日志中会记录每一行数据被修改的形式,然后在slave端再对相同的数据进行修改

优点: 在row level模式下,bin-log中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录哪一条被修改。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function,以及trigger的调用和触发无法被正确复制的问题

缺点:row level,所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,会产生大量的日志内容。

Statement Level(默认)

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行

优点:statement level下的优点首先就是解决了row level下的缺点,不需要记录每一行数据的变化,减少bin-log日志量,节约IO,提高性能,因为它只需要在Master上锁执行的语句的细节,以及执行语句的上下文的信息。

缺点:由于只记录语句,所以,在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题,主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。

Mixed 自动模式

在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式,也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更

企业场景如何选择binlog模式?

1、互联网公司,使用MySQL的功能相对少(存储过程、触发器、函数),选择默认的语句模式,Statement Level(默认)

2、公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数),则选择Mixed模式

3、公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数)又希望数据最大化一直,此时最好选择Row level模式

3.1 mysql 和 mariadb 开启 bin-log 的 配置文件不同:

① mysql 是修改/etc/my.conf配置文件开启binlog日志记录功能

# vim /etc/my.cnf
log-bin=mysql-bin    #启动二进制日志
# systemctl restart mysqld

image.png

image.png

② mariadb 是修改/etc/my.cof.d/server.cnf配置文件来开启binlog日志记录功能

# vim /etc/my.cnf.d/server.cnf

image.png

可以在数据库中通过下面这个命令来查看是否开启了bin-log日志功能,ON 就是已经开启的意思,OFF 是关闭的意思。

MariaDB [(none)]> show variables like 'log_bin';

image.png

bin-log日志文件的默认位置是/var/lib/mysql/,默认日志名称为mysql-bin.00000X

image.png

提示:

关于bin-log日志文件的一些配置选项:

log_bin = /var/lib/mysql/bin-log
# 开启 Binlog 并写明存放日志的位置;默认使用的设置是“log-bin=mysql-bin”,这样日志是存放在默认的/var/lib/mysql/位置上

log_bin_index = /var/lib/mysql/mysql-bin.index
# 指定索引文件的位置。

expire_logs_days = 7
# 超出这个变量保留期之前的全部日志将被删除

server_id = 0002
# 指定一个集群内的 MySQL 服务器 ID,如果做数据库集群那么必须全局唯一,一般来说不推荐 指定 server_id 等于 1。

binlog_format = ROW
# 设置方面提到过的三种Bin-log日志模式。

max_binlog_size = 50M
# binary log 最大的大小

binlog_cache_size = 1M
# 当前的多少事务cache在内存中
binlog_cache_disk_use
# 当前有多少事务暂存在磁盘上的,如果这个值有数值的话,就应该要注意调优了。

max_binlog_cache_size
# 最大能有多少事务cache在内存中

binlog_do_db和binlog_ingore_db
# 是一对控制对哪些数据库进行收集的选项,即收集或忽略指定数据库的增删改操作,示例:
binlog_do_db=test
binlog_ignore_db=mysql

sync_binlog = 0
# 这个值控制cache的数据commit多少次才刷到磁盘上。默认是0,也就是让数据库自己决定同步的频率。如设置成1的话,则每commit一次就会将cache的数据同步到磁盘上,这样做最安全,但是性能最差。

3.2 按时间还原数据库

可以根据日志文件中的时间选项对数据库进行还原:

# vim /var/lib/mysql/binlog日志文件名

image.png

image.png

按照时间还原的时候要注意时间的格式,注意时间的起止
image.png

3.3 按文件大小position还原数据库

image.png

image.png

MySQL集群:

主从模式、主主模式、一主多从模式的基本目的都是备份同一个数据库的数据,多主一从模式则是备份多个数据库的数据到同一台slave服务器上( 这样就需要slave服务器开启多个线程分别接收来自多台主服务器的多个数据库内容 )

1、数据库主从服务器( 使用mysql示例 )

① 原理:
Mysql主从备份的原理就是使用了bin-log日志。当mysql主机开启了bin-log日志之后,将其中一台机器的bin-log日志传到其他机器上,然后可以在接收该日志的机器上读取bin-log日志后将数据库恢复到本地( 相当于按照bin-log日志的格式又执行了一遍,这样原本空的数据库就变得有内容了 )。而且这个过程是实时的,主节点每执行一个增、删、改操作都会告诉备份节点。

注意:
主从备份可以防止意外情况的发生,但是不能避免人为的失误操作

② 作用

a、从服务器主动把主服务器上的数据同步到本地( 备份 )
b、从服务器分摊主服务器的查询压力( 负载均衡 )

③ 操作方法
a、主节点启用bin-log日志,并声明一个server-id

# vim /etc/my.cnf

image.png

b、从节点启用bin-log日志,并声明一个server-id

# vim /etc/my.cnf

image.png

c、两台主机启动数据库服务并设置开机自启

# systemctl start mysqld
# systemctl enable mysqld

d、在主服务器上授权

image.png

e、从服务器接受保存授权信息

image.png

f、查看接收的授权信息

image.png

g、开启从服务器

image.png

h、测试

image.png

2、数据库主主备份( 使用mariadb示例 )

原理:

主主备份是在主从服务器的基础上,使原来的从服务器同时也成为一个主服务器( 原来的从服务器同时也是主服务器 ),然后授权给原来的主服务器,使原来的主服务器同时也成为从服务器,这样两台服务器就互为主从了。A为主,B为从;B为主,A为从。两台服务器同时拥有两个身份。

作用:

均摊了写压力

操作:

a、mariadb-server、mariadb安装之后需要初始化

# mysql_secure_installation

b、2个节点都开启bin-log日志功能

分别修改/etc/my.cof.d/server.cnf文件

image.png

image.png

c、10主机首先作为master节点向20主机授予权限,并查看bin-log文件名和position大小

image.png

d、20主机作为slave节点接受10主机赋予的权限

bin-log的文件名和position大小要根据master节点查询出来的数据填写:

image.png

e、20主机开启从服务器slave模式

image.png

f、测试单一主从模式

10主机作为master节点,创建等操作都会被记录在bin-log日志中,binlog日志被实时传给slave节点20主机,20主机解析bin-log日志为SQL语句并执行,达到一个实时备份的目的。
image.png

g、基于上面的主从结构,再将20主机设为master节点,将10主机设置为slave节点,达到主主备份的目的

image.png

image.png

image.png

测试:

到此为止就成功实现了主主双向备份!
image.png

3、数据库一主多从模式

一主多从即在主从的基础上再多加几台从服务器slave,在master主服务器上执行授权,多台从服务器slave接受权限并开启slave模式即可!

4、数据库多主一从模式( mariadb 示例 )

用途:

例如现在只有三台服务器,10服务器主要是网站A的数据库,20服务器主要是网站B的数据库,这两个网站的数据都需要备份到30服务器,那么数据库多主一从模式就可以用来解决这个问题。

实现原理:

实现原理就是从服务器开启多个线程分别用来接收多master服务器传递来的数据

大致流程:

① 前提都开启bin-log日志功能,首先10数据库服务器、20数据库服务器身份为 master 服务器,向30 slave 服务器进行授权,随后10、20服务器开启mariadb服务;

② 30服务器需要在配置文件中配置两个线程来分别接收2个主服务器传递过来的数据,然后两个线程分别创建接收数据的两个目录,分别启动两个线程并登陆两个线程的数据库进程,分别接受2个master服务器的授权信息,并启动2个线程的slave身份。

③ 测试

① 三节点安装mariadb、mariadb-server

② 10、20作为master节点编辑配置文件/etc/my.cnf.d/server.cnf

image.png

image.png

③ 30作为slave节点编辑配置文件/etc/my.cnf.d/server.cnf,配置两个线程分别接收2个master的数据

# vim /etc/my.cnf.d/server.cnf
# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log

[mysqld10]
port=3306
datadir=/var/lib/mysqla/
pid-file=/var/lib/mysqla/mysqld.pid
socket=/var/lib/mysqla/mysql.sock
user=mysql
server-id=30

[mysqld20]
port=3307
datadir=/var/lib/mysqlb/
pid-file=/var/lib/mysqlb/mysqld.pid
socket=/var/lib/mysqlb/mysql.sock
user=mysql
server-id=30

image.png

④ 10、20 master节点分别开启mariadb服务并向 30 slave服务器授权 ( 因操作相同,本步骤节省篇幅只给出10节点截图 )

image.png

image.png

⑤ 30 slave服务器初始化数据库,并生成两个线程的目录mysqla、mysqlb

image.png

image.png

image.png

⑥ 30 slave服务器设置mysqla、mysqlb两个目录的属主为mysql,防止出现权限的相关问题

image.png

⑦ 启动30 slave服务器的两个mariadb线程

# start 后边的数值就是刚才在配置文件线程标签mysqld后边的数值
# mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf start 10
# mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf start 20

image.png

⑧ 使用netstat、ps命令检测2个线程是否启动成功

# netstat -antp | grep 330*
# ps -ef | grep 330*

image.png

image.png

⑨ 分别登陆2个线程接收2台master节点的授权信息

# mysql -uroot -P 3306 -S /var/lib/mysqla/mysql.sock
MariaDB [(none)]> change master to
    -> master_user='slave',
    -> master_password='123456',
    -> master_host='192.168.52.X',
    -> master_log_file='mysql-bin.000005',
    -> master_log_pos=1306;

image.png

image.png

⑩ 分别在两台master服务器修改数据库,在slave 服务器开启2个ssh窗口,分别登陆2个mariadb线程检测变化

image.png

image.png

image.png

image.png

5、Mysql中间件-Amoeba

中间件:一种提供在不同技术、不同的软件之间共享资源的程序,更大化的利用了数据库的性能,可以无限扩展( 注:真实环境中并非如此 )

数据库的中间件:

mysql proxy( 官方版本 ) 性能低,需要lua脚本
很多大厂对mysql proxy进行了二次开发,形成了符合自己业务需求的版本。
atlas 性能低,响应时间长
amoeba

首先 数据库中间件 是搭建在一个单独的服务器上的,它主要是用来调度已经搭建好的主从服务器、或者是主从从服务器。amoeba中间件调度相当于一个反向代理服务器,任何关于数据库读和写的请求都会流向中间件上,然后由中间件将数据库的读和写请求分配给主服务器和从服务器。我们知道,在主从服务器模式中,因为从服务器的数据都是来自主服务器,因此只有主服务器可以进行数据的更新( 增、删、改 )。

但是我们也不能让从服务器干等着啥也不干,所以为了充分利用从服务器的性能,所以中间件就提供了这么几种方案:

① 当数据库的读写请求到达中间件后,中间件将读写请求分配给主服务器,另外将读请求也分配给从服务器,这样简单实现了读请求的负载均衡。

② 当数据库的读写请求到达中间件后,中间件仅将写请求分配给主服务器,读请求分配给从服务器,这样就实现了读写请求分离的效果。当有多台主服务器、多台从服务器的时候,就实现了读写请求分离且负载的效果。