一.什么是Mysql主从分离
将读操作和写操作分离到不同的数据库上,避免主服务器出现性能瓶颈;主服务器进行写操作时,不影响查询应用服务器的查询性能,降低阻塞,提高并发; 数据拥有多个容灾副本,提高数据安全性,同时当主服务器故障时,可立即切换到其他服务器,提高系统可用性;
二.为什么要实现Mysql主从分离
大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器扛,如此多的数据库连接操作,数据库必然会崩溃,数据丢失的话,后果更是 不堪设想。这时候,我们会考虑如何减少数据库的联接,一方面采用优秀的代码框架,进行代码的优化,采用优秀的数据缓存技术如:memcached,如果资金丰厚的话,必然会想到假设服务器群,来分担主数据库的压力。
三.主从分离原理
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(中介日志中)——>从服务器读取中介日志执行同步数据
四.数据库主从配置
注:可以是一主一从,一主多从,主从从等。
我这里配置一主一从,自己有一台服务器,又借了室友一台服务器准备试试。
1.两个服务器:
1.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
(3)重启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启动
再注: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))
(4)SHOW MASTERT STATUS;
会查看到第一个二进制日志文件:
1.2从服务器配置:
(1)同理进入mysql配置文件:vim /etc/my.cnf:
(2)配置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
(3)同理,保存配置重启mysql加载配置文件生效
1.3相关连接配置:
(1)在主服务器上(Master)创建一个用于用于从服务器连接并赋予其所有数据库所有表的权限:
创建用户:create user 用户名;
赋予连接权限:GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从服务器IP' IDENTIFIED BY '密码'
刷新:flush privileges;
(2)从服务器建立连接(使用主服务器创建的repl用户及密码):
建立连接:change master to master_host='主服务器IP',master_port=主服务器MYSQL端口,master_user='用户名',master_password='密码',master_log_file='master-bin.000001',master_log_pos=0;
读取master-bin.000001文件,即主服务器的第一个日志文件,master_log_pos的作用是如果从服务器挂掉后,只要记得这个master_log_pos的大小,然后赋值就能恢复同步在某个时刻。
开启主从跟踪:start slave;
相应得关闭主从跟踪:stop slave;
查看从服务器show slave status \G:
这里注意状态是否正确,有可能连接不正确(如主服务器配置文件种有blind-address只能指定ip访问数据库,权限未赋予正确,防火墙等等),我最后遇到的原因是阿里云服务器端口没有打开3306端口,打开即可。
2.测试:
主服务器创建一个数据库:
从服务器也创建成功: