Mysql主从复制
一、概述
实现整个复制过程主要由三个线程来完成,其中两个线程(Sql线程和IO线程)在 Slave 端,另外一个线程(IO线程)在Master端。
二、主从复制的原理
实现整个复制过程主要由三个线程来完成,其中两个线程(Sql线程和IO线程)在 Slave 端,另外一个线程(IO线程)在Master端。master数据库将数据变化写入日志文件中,从数据库(slave)的io线程,会去请求主数据库的日志文件,然后将得到的日志文件写到自己的中继日志(relay log)文件中.然后在master.info 的文件记录下master主库日志最后一行的位置,方便下次读取的时候,会带过去,然后从数据库的sql线程,会读取中继日志文件,并解析成具体操作,将数据同步。达到数据一致的效果
(注释:主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;)
mysql主从复制存在的问题:
主库宕机后,数据可能丢失 ---半同步复制、配置互为主从
从库只有一个sql Thread,主库写压力大,复制很可能延时---- 并行复制、数据缓存、
解决方法:
半同步复制---解决数据丢失的问题(保证事务在提交后,必须要传到slave数据库,并写到relay log中,事务才能算结束。也就是从数据会给主数据库一个回应,保证收到,不过这样虽然保证数据的准确性,但对性能影响很大,)
并行复制----解决从库复制延迟的问题(mysql5.6以上支持多线程赋值,因为并行是指从库多线程apply binlog,设置set global slave_parallel_workers=10;设置sql线程数为10,这样就不会像以前单线程工作。等它完成之后,才能继续工作其他的,导致数据延迟的问题)
数据缓存----解决从库复制延迟的问题,(先读缓存数据,再读数据库数据)
大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器扛,如此多的数据库连接操作,数据库必然会崩溃,数据丢失的话,后果更是不堪设想。这时候,我们会考虑如何减少数据库的联接。
三、配置主从复制
1、配置Master主服务器
(1)在Master MySQL上创建一个用户‘repl’,并允许其他Slave服务器可以通过远程访问Master,通过该用户读取二进制日志,实现数据同步。
1 mysql>create user repl; //创建新用户
//repl用户必须具有REPLICATION SLAVE权限,除此之外没有必要添加不必要的权限,密码为mysql。说明一下192.168.0.%,这个配置是指明repl用户所在服务器,这里%是通配符,表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。
2 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'mysql';
(2)找到MySQL安装文件夹修改my.Ini文件(也就是mysql的配置文件)。mysql中有好几种日志方式,这不是今天的重点。我们只要启动二进制日志log-bin就ok。
在[mysqld]下面增加下面几行代码
server-id=1 //给数据库服务的唯一标识,一般为大家设置服务器Ip的末尾号
log-bin=master-bin //[必须]启用二进制日志
log-bin-index=master-bin.index
(3)查看日志
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 1285 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
(4)重启MySQL服务
2、配置从服务器
(1)找到MySQL安装文件夹修改my.ini文件(mysql配置文件),在[mysqld]下面增加下面几行代码
[mysqld]
server-id=2 //给数据库服务的唯一标识,一般为大家设置服务器Ip的末尾号
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin//[必须]启用二进制日志
(2)重启MySQL服务
(3)进入mysql,连接Master
change master to master_host='192.168.1.66', //Master 服务器Ip
master_port=3306,
master_user='repl', //创建的账户
master_password='mysql', //密码
master_log_file='master-bin.000001',//Master服务器产生的日志(与上面查看的要一致)
master_log_pos=0;
(4)进入mysql,启动Slave
start slave;
(5)检查从服务器复制功能状态:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.222 //主服务器地址
Master_User: mysync //授权帐户名,尽量避免使用root
Master_Port: 3306 //数据库端口,部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 //#同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
......
注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
以上操作过程,主从服务器配置完成。
3、主从服务器测试
主服务器Mysql,建立数据库,并在这个库中建表插入一条数据:
mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)
mysql> use hi_db;
Database changed
mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into hi_tb values(001,'bobu');
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hi_db |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
从服务器Mysql查询:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hi_db | //I'M here,大家看到了吧
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use hi_db
Database changed
mysql> select * from hi_tb; //查看主服务器上新增的具体数据
+------+------+
| id | name |
+------+------+
| 1 | bobu |
+------+------+
1 row in set (0.00 sec)