为什么mysql要做主从复制?
为什么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'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
什么是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'
- 1
- 2
- 3
- 4
在master数据库设置用来同步的slave用户权限
启动数据库
[root@localhost ~]# service mysqld start
- 1
进入mysql
-
[root@localhost ~]
-
mysql> GRANT REPLICATION SLAVE ON *.* TO '<root>'@'<192.168.15.142>' IDENTIFIED BY '<123456>';
-
Query OK, 0 rows affected (0.10 sec)
- 1
- 2
- 3
- 4
主数据库锁表(禁止再插入数据以获取主数据库的的二进制日志坐标)
>FLUSH TABLES WITH READ LOCK;
- 1
在主数据库上使用mysqldump命令创建一个数据快照
[root@localhost ~]# mysqldump -u root -pnew-password --databases drp > /root/test.sql
- 1
解锁第(2)步主数据的锁表操作
SSH登录到从数据库
通过FTP、SFTP或其他方式,将上一步备份的主数据库快照all.sql上传到从数据库某个路径,例如我放在了/root/目录下;
从导入主的快照
-
[
-
Enter password:
-
You have mail in /var/spool/mail/root
- 1
- 2
- 3
- 4
给从数据库设置复制的主数据库信息(注意修改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)
- 1
- 2
- 3
- 4
查看重数据库的状态
mysql> SHOW slave STATUS \G
- 1
如果下面两个参数都是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是否重复,如果重复,改一下再重启。
这样当主数据库插入数据,新建表或者创建数据库时,就可以自动同步到从数据库了~