一、什么是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'@'%';