mysql读写分离

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql读写分离

dandan911217 2016-03-24 14:03:00 浏览3039
展开阅读全文

为什么MySQL要做主从复制(读写分离)?

通俗来讲,如果对数据库的读和写都在同一个数据库服务器中操作,业务系统性能会降低。
为了提升业务系统性能,优化用户体验,可以通过做主从复制(读写分离)来减轻主数据库的负载。
而且如果主数据库宕机,可快速将业务系统切换到从数据库上,可避免数据丢失。

MySQL主从复制(读写分离)和集群的区别:

我对MySQL也是刚开始研究,不是很专业。我的理解是:

主从复制(读写分离):

一般需要两台及以上数据库服务器即可(一台用于写入数据,一台用于同步主的数据并用于数据查询操作)。
局限性:

配置好主从复制之后,同一张表,只能对一个服务器写操作。如果在从上执行了写操作,而之后主也操作了这张表,或导致主从不同步;据说可以配置成主主方式,但我还没有研究到。

主数据库服务器宕机,需要手动将业务系统切换到从数据库服务器。无法做到高可用性(除非再通过部署keepalive做成高可用方案)。

集群是由N台数据库服务器组成,数据的写入和查询是随机到任意一台数据库服务器的,其他数据库服务器会自动同步数据库的操作。

任何一台数据库宕机,不会对整个集群造成大的影响。

局限性

我经过测试才知道目前mysql集群版本(MySQL Cluster)只能对NDB存储引擎的数据进行集群同步,如果是INNODB或其他的MySQL存储引擎是不行的。这个也导致了我放弃了在业务系统中应用这种方案。

回归正题

MySQL5.6开始主从复制有两种方式:基于日志(binlog);基于GTID(全局事务标示符)。
需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑这种方式了,至少目前最新版本MySQL5.6.12的GTID复制还是不支持临时表的。
所以此篇教程主要是告诉大家如何通过日志(binlog)方式做主从复制!
Master :192.168.15.141
Slave:192.168.15.142
如何安装mysql我就不细说了,请看linux下安装mysql

配置文件

master

vi /etc/my.cnf
[mysqld] 
server-id = 1
log-bin innodb_flush_log_at_trx_commit=1 
sync_binlog=1 
datadir=/var/lib/mysql 
character-set-server=utf8 
init_connect='SET NAMES utf8'

什么是sync_binlog?
当每个sync_binlog’th写入该二进制日志后,MySQL服务器将它的二进制日志同步到硬盘上(fdatasync())。请注意如果在autocommit模式,每执行一个语句向二进制日志写入一次,否则每个事务写入一次。 默认值是0,不与硬盘同步。值为1是最安全的选择,因为崩溃时,你最多丢掉二进制日志中的一个语句/事务;但是,这是最慢的选择(除非硬盘有电池备份缓存,从而使同步工作较快)。
slave:

server-id=2 
datadir=/var/lib/mysql 
character-set-server=utf8 
init_connect='SET NAMES utf8'

在master数据库设置用来同步的slave用户权限

启动数据库

[root@localhost ~]# service mysqld start

进入mysql

[root@localhost ~]# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO '<root>'@'<192.168.15.142>' IDENTIFIED BY '<123456>';
Query OK, 0 rows affected (0.10 sec)

主数据库锁表(禁止再插入数据以获取主数据库的的二进制日志坐标)

>FLUSH TABLES WITH READ LOCK;

在主数据库上使用mysqldump命令创建一个数据快照

[root@localhost ~]# mysqldump -u root -pnew-password  --databases drp > /root/test.sql

这里写图片描述

解锁第(2)步主数据的锁表操作

SSH登录到从数据库

通过FTP、SFTP或其他方式,将上一步备份的主数据库快照all.sql上传到从数据库某个路径,例如我放在了/root/目录下;

从导入主的快照

[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -P3306< test.sql
Enter password: 
You have mail in /var/spool/mail/root

给从数据库设置复制的主数据库信息(注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值)

MASTER_LOG_FILE和MASTER_LOG_POS在主数据中查看这里写图片描述

mysql> CHANGE MASTER TO MASTER_HOST='192.168.15.141',MASTER_USER='root',MASTER_PASSWORD='new-password',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=3931
mysql> START slave;
Query OK, 0 rows affected (0.74 sec)

查看重数据库的状态

mysql> SHOW slave STATUS \G

如果下面两个参数都是Yes,则说明主从配置成功!
这里写图片描述
slave_sql_running:负责自己的slave mysql进程
Slave_IO_Running:负责与主机的io通信
查看报错:
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
意思就是从上的server_id和主的一样的,经查看发现从上的/etc/my.cnf中的server_id=1这行我没有注释掉(在下面复制部分我设置了server_id),于是马上把这行注释掉了,然后重启mysql,发现还是报同样的错误。
解决方案:
查看主从数据库中my.conf的server_id是否重复,如果重复,改一下再重启。
这样当主数据库插入数据,新建表或者创建数据库时,就可以自动同步到从数据库了~

网友评论

登录后评论
0/500
评论