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

Mysql锁：

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

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

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

Mysql的存储引擎：

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

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

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

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

事务（Transaction）是并发控制的基本单位：

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

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

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

备份和还原

1、mysqldump备份：

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

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

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

2、mysqlhotcopy备份

原理：
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

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

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

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

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

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

提示：

关于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日志文件名

按照时间还原的时候要注意时间的格式，注意时间的起止

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

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

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

# vim /etc/my.cnf

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

# systemctl start mysqld
# systemctl enable mysqld

d、在主服务器上授权

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

f、查看接收的授权信息

g、开启从服务器

h、测试

2、数据库主主备份（ 使用mariadb示例 ）

原理：

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

作用：

均摊了写压力

操作：

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

# mysql_secure_installation

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

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

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

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

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

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

f、测试单一主从模式

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

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

测试：

到此为止就成功实现了主主双向备份！

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

③ 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

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

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

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

⑦ 启动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

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

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

⑨ 分别登陆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;

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

5、Mysql中间件-Amoeba

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

数据库的中间件：

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

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

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

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

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

5.1 amoeba中间件实现主从读写分离

环境一览：

① Master主机：

master # yum -y install mariadb mariadb-server
master # sed -ri '/\[mysqld\]/a\bin-log=mysql-bin\nserver-id=20' /etc/my.cnf.d/server.conf 
master # systemctl enable mariadb
master # systemctl start mariadb
# 设置登录密码
master # mysql_secure_installation
master # mysql -uroot -p密码
MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.52.30' identified by '123456789' ;
# 查看bin-log日志名称与position大小
MariaDB [(none)]>  show master status;

② Slave主机：

Slave # yum -y install mariadb mariadb-server
Slave # sed -ri '/\[mysqld\]/a\bin-log=mysql-bin\nserver-id=30' /etc/my.cnf.d/server.conf
Slave # systemctl enable mariadb
Slave # systemctl start mariadb
# 设置登录密码
Slave # mysql_secure_installation
Slave # mysql -uroot -p密码
MariaDB [(none)]>  change master to  
> master_user='slave', 
> master_password='123456789',
> master_host='1 92.168.52.20',
> master_log_file='mysql-bin.000005',
> master_log_pos=1642;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G; 

mysql replication 中 slave 机器上有两个关键的进程，一个是slave_sql_running，负责自己的slave mysql进程。另一个是Slave_IO_Running，一个负责与主机的io通信。

③ Master创建表，Slave测试是否同步

MariaDB [(none)]> create database ishells;
MariaDB [(none)]> use ishells;
MariaDB [(ishells)]> create table root(id int,name char(20),age int);
MariaDB [(ishells)]> insert into root values (0,'root',22);
MariaDB [(ishells)]> select * from root;

# slave主机检测数据是否同步
MariaDB [(none)]> select * from ishells.root;

④ 配置amoeba

# 我是在SourceForge下载的amoeba
# wget https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz
# amoeba需要jdk支持，jdk需要在Oracle注册登陆再下载
# https://www.oracle.com/java/technologies/javase/javase-jdk8-downloads.html
# ls /root
amoeba-mysql-binary-2.2.0.tar.gz jdk-8u211-linux-x64.tar.gz
# mkdir /amoeba
# tar -xf jdk-8u152-linux-x64.tar.gz -C /amoeba/
# cd /amoeba
# ls
# ln -s jdk1.8.0_211/ jdk
# 声明环境变量
# cat << EOF >> /etc/profile
> JAVA_HOME=/amoeba/jdk
> export JAVA_HOME
> PATH=$JAVA_HOME/bin:$PATH
> export PATH
> CLASSPATH=.:$JAVA_HOME/bin/tools.jar:$JAVA_HOME/lib/dt.jar:$CLASSPATH
> export CLASSPATH
> EOF
# source /etc/profile
# 测试一下
# java -version
# 安装amoeba
# mkdir /usr/local/amoeba
# tar -xf /root/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
# cd /usr/local/amoeba
# ls
# chmod -R +x /usr/local/amoeba/bin/

修改amoeba的amoeba.xml配置文件

# vim /usr/local/amoeba/conf/amoeba.xml

修改amoeba的dbServer.xml文件

# vim /usr/local/amoeba/conf/dbServer.xml

# vim /usr/local/amoeba/conf/amoeba.xml

启动amoebe前更改一下启动脚本：（注意：将-Xss128k 修改为–Xss256k）

# vim /usr/local/amoeba/bin/amoeba

因为前面在amoeba中配置的为‘amoeba’用户去连接后端的数据库，所以现在要在后端数据库上分别为其授权

master # mysql -uroot -p密码
MariaDB [(none)]> grant all on ishells.* to amoeba@'192.168.52.10' identified by '123465789';flush privileges;

slave # mysql -uroot -p密码
MariaDB [(none)]> grant all on ishells.* to amoeba@'192.168.52.10' identified by '123465789';flush privileges;

⑥ 启动amoeba

Amoeba # nohup bash -x /usr/local/amoeba/bin/amoeba start&
# 查看下进程是否启动成功
Amoeba # ps -ef | grep -i amoeba
Amoeba # netstat -antp | grep 8066

⑦ amoeba读写分离测试

首先为了能够看到master、slave两台数据库服务器的数据差异，我们首先在slave机器上关闭slave模式，并对其中的数据表增加一些数据

slave # 

验证测试暂未处理：