SSM开发笔记5-1MySql主从分离 | 数据库层

一、什么是Mysql主从分离

       将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阻塞，提高并发； 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；

二、为什么要实现Mysql主从分离

       大型网站为了缓解大量的并发访问，除了在网站实现分布式负载均衡，远远不够。到了数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是 不堪设想。这时候，我们会考虑如何减少数据库的联接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：memcached,如果资金丰厚的话，必然会想到假设服务器群，来分担主数据库的压力。
       主从分离的优点：
　　       1. 将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；
　　       2. 主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阻塞，提高并发；
　　       3. 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；

三、主从分离原理

       1、第一步：Master（主服务器）将操作记录到binary log（二进制日志文件当中）【即每个事务更新数据完成之前先把操作记录在日志文件中，Mysql将事务串行的写入二进制日志文件中】，写入日志文件完成之后，Master通知存储引擎提交事务（注：对数据的操作成为一次二进制的日志事件【binary log event】）；
       2、第二步：slave（从服务器）把binary log拷贝到relay log（中介日志）【相当于缓存作用，存储在从服务器的缓存中】，首先slave会开始一个工作线程（I/O线程），I/O线程会在Master上打开一个普通的连接，然后读取binary log事件，如果已经跟上master，就会睡眠，并等待Master产生新的事件，I/O线程将读取的这些事件写入到relay log；
       3、第三步：slave从做中介日志事件（relay log），sql线程读取relay log事件并执行更新从服务器上的数据，使其与Master上的数据一致。
       总结：主服务器把操作记录到binary log——>从服务器将binary log中的数据同步到relay log（中介日志中）——>从服务器读取中介日志执行同步数据

四、主从环境

       如果仅有一台电脑，可在自己电脑上安装虚拟机，搭建一台服务器mysql
       可参考 https://blog.csdn.net/Kasey_L/article/details/105103522

五、数据库主从配置 - 数据库层面

       【注】：可以是一主一从，一主多从，主从从等。以下为一主一从的配置
       1、主服务器配置：
              ①配置主服务器（Master）：打开binary log，配置mysql配置文件：
                     （1）vim /etc/my.cnf（编辑服务器上的mysql配置文件，一般都存储在这儿，如果没有可以根据自己的配置文件）:

                     （2）配置打开binary log：

[mysqld]
#红色的为新配置的打开binary log的配置
#配置binary log
#配置server-id
server-id=1
#打开二进制日志文件
log-bin=master-bin
#打开二进制日志文件索引
log-bin-index=master-bin.index
#以上为配置内容

character_set_server=utf8
init_connect='SET NAMES utf8'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

　　　　　　　　
                     ②重启mysql加载配置文件：service mysqld restart或者（etc/init.d/mysql stop 然后etc/init.d/mysql start）

                     【注①】：如果用的是MariaDB，会发生如下错误（Failed to start mysql.server.service: Unit not found.）则需要用systemctl restart mariadb.service启动（参照：https://www.cnblogs.com/yuanchaoyong/p/9749060.html）
                     【注②】：systemctl和service是Linux服务管理的两种方式，service命令其实是去/etc/init.d目录下，去执行相关程序，systemd是Linux系统最新的初始化系统(init),作用是提高系统的启动速度，尽可能启动较少的进程，尽可能更多进程并发启动。systemd对应的进程管理命令是systemctl，systemctl命令兼容了service(即systemctl也会去/etc/init.d目录下，查看，执行相关程序),并且systemctl命令管理systemd的资源Unit（systemd的Unit放在目录/usr/lib/systemd/system(Centos)或/etc/systemd/system(Ubuntu)）
                     ③重启并验证，查看日志文件
                            a、配置验证，能正常显示即为正常

                            b、查看日志文件 SHOW MASTERT STATUS;会查看到第一个二进制日志文件：

　　　　　　
       2、从服务器配置：
              ①同理进入mysql配置文件：vim /etc/my.cnf:
              ②配置relay log：

 [mysqld]
  #配置relay log
  #配置server id
  server-id=2
  #打开从服务器中介日志文件
  relay-log=slave-relay-bin
  #打开从服务器中介日志文件索引
  relay-log-index=slave-relay-bin.index
#以上为配置内容

  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
 
 # Disabling symbolic-links is recommended to prevent assorted security risks
  symbolic-links=0
 
  # Recommended in standard MySQL setup
  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid

              ③ 同理，保存配置重启mysql加载配置文件生效
                     新的重启方式

       3、相关连接配置：
              ①在主服务器上（Master）创建一个用于从服务器连接并赋予其所有数据库所有表的权限：
                     创建用户：create user 用户名;

                      赋予连接权限：GRANT REPLICATION SLAVE ON . TO ‘repl’@‘从服务器IP’ IDENTIFIED BY ‘密码’

                     刷新：flush privileges;

              ②从服务器建立连接（使用主服务器创建的repl用户及密码）：　
                     a、建立连接：change master to master_host=‘主服务器IP’,master_port=主服务器MYSQL端口,master_user=‘用户名’,master_password=‘密码’,master_log_file=‘master-bin.000001’,master_log_pos=0;
                     【注】：此时master_log_pos的值和主库的position的值是一致的，读取master-bin.000001文件，即主服务器的第一个日志文件，master_log_pos的作用是如果从服务器挂掉后，只要记得这个master_log_pos的大小，然后赋值就能恢复同步在某个时刻。

              ③开启主从跟踪：start slave;

                     相应得关闭主从跟踪：stop slave;每次重启都要重新开启
              ④查看从服务器show slave status \G:

这里注意状态是否正确，有可能连接不正确（如主服务器配置文件种有blind-address只能指定ip访问数据库，权限未赋予正确，防火墙等等），我最后遇到的原因是阿里云服务器端口没有打开3306端口，打开即可。
图中为主从server-id相同了

       4、测试：
              主服务器创建一个数据库，从服务器也创建成功，以o2o为例


        5、注意事项：
              ①不可在从库中写数据，从库写了数据，master-log不会发生变化，当主库写入同样数据就会产生冲突
              ②主从版本可以不同，但是从库版本必须要比主库高，因为mysql是向后兼容的
       6、引入数据库
              ①从windows环境下导出数据库脚本（我的数据库在Windows下，Linux同样可以）

C:\Users\释然先生> mysqldump -uroot -p 数据库名 > 导出的位置
C:\Users\释然先生> mysqldump -uroot -p o2o >F:\o2o.sql

              ②传输到远程服务器上

scp 导出的位置 root@服务器IP地址:/服务器用户名
scp f:\o2o.sql root@192.168.2.195:/root

              ③再输入服务的密码
                     sql脚本传输到的服务器的用户名对应的密码
              ④服务器上将传输过来的数据库引入（主库）
                     a、连接上数据库
                     b、创建对应的数据库
                            create database o2o;
                     c、引入sql脚本 o2o.sql文件放在root目录下，所以用~
                            source ~/o2o.sql;
       7、创建可外部访问的用户名
              创建一个可用的用户名并赋予权限

8.0之前版本：mysql> grant select,insert,update,delete on *.* to 'work'@'%' identified by 'Admin@Work66' with grant option;
8.0之后版本：mysql> create user work identified by 'Admin@Work66';
			 mysql> grant select,insert,update,delete on *.* to 'work'@'%';