复制解决的问题是保持多个服务器之间的数据的一致性,就如同通过复制保持两个文件的一致性一样,只不过MySQL的复制要相对要复杂一些,其基本过程如下:
1)在主库上将数据更改记录到二进制日志(Binary Log)中(这些记录被成为二进制日志事件,即binlog)
2)本分将主库上的日志复制到自己的中继日志(Relay Log)中
3)备库读取中继日志中的事件,将其重放到备库数据之上。
从上面可以看出,复制需要四个进程或线程做事情:主库保存日志、主库根据备库的请求转储日志并发送给备库,备库接受日志保存在中继日志中,备库从中继日志中读取信息重放到备库中。当然复制本身可以说只需要3个线程,不算主库保存日志。
从逻辑上来看,复制对于主库的开销主要在I/O上,记录日志、转储日志和网络传输,如果同时支持多个备库,这些消耗是叠加的,对主库的压力比较大。
MySQL支持语句复制和行复制,语句复制在MySQL 3.23版就已经存在,行复制到5.1版才加进来。行复制是其他数据库较为常用的方式,语句复制相对更加简单。在MariaDB 10.0.12中用的是混合模式,即默认使用语句模式,特殊情况下采用行复制。复制模式的启用可以在my.ini或my.cnf中定义:binlog_format=statement/row/mixed,分别代表语句、行、混合模式,可以参考
http://www.linuxidc.com/Linux/2013-06/86632.htm,写的非常详细。
MySQL复制的特点:
1)配置较为简单
2)很方便进行主从切换,即更换主或者增加从都非常简单
3)基于语句的复制方式,导致在从库完全重放主库的SQL,如果从库配置较差,可能会出现很大的延迟,甚至导致从库无法完成其他工作。
4)主库执行SQL是并发执行的,从库重放是串行的,会带来更严重的延迟。
5)一般来说,从库版本要高于主库版本,因为复制是向下兼容的,即高版本的MySQL实例可以读取低版本的二进制日志。
配置复制实例,为方便起见,采用相同的数据库版本,如下
1)本机启动两个MySQL实例
环境为:Windows 7 x64,IP地址为192.168.1.11,两个MariaDB 10.0.12实例
将MySQL实例1启动端口设为3308,实例2启动端口设为3307,实例1作为主服务器,实例2作为从服务器,端口设置在my.ini中,本人将my-medium.ini复制改为my.ini,作为配置文件,修改其端口,启动MySQL命令为mysqld,连接命令mysql -uroot -P 3308和mysql -uroot -P 3307,因为root帐号默认密码为空,所以没有-p参数。
a)在主实例上配置
MariaDB [(none)]>grant replication slave on *.* to rep1@‘192.168.1.%‘ identified by ‘123456‘;
修改my.ini增加或修改以下内容
log_bin=mysql-bin
server_id=10
这里的server_id要保证在所有MySQL实例中唯一
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 500 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
可以看出复制文件为mysql-bin.000003
b)在从服务器上做设置
修改my.ini文件,增加或修改内容
log_bin=mysql-bin
server-id = 11
relay_log = D:\mysql\mariadb-10.0.12-winx64-1\data\mysql-relay-bin
log_slave_updates = 1
read_only = 1
这里要注意,log_bin在主从命名一致,这不是必须的,但是这样做有利于未来切换方便,server_id也要保证唯一,relay_log是中继日志的位置,log_slave_updates=1表明中继日志的修改也要记录从库的更新日志中,这有利于保证数据的一致性,还有利于从从服务器复制数据,read_only=1会阻止没有权限的账户更新数据。
配置复制,
MariaDB [(none)]> change master to master_host=‘192.168.1.11‘,master_port=3308,master_user=‘rep1‘,master_password=‘123456‘,master_log_file=‘mysql-biin.000003‘,master_log_pos=0;
这里的参数都很简单明了,需要注意的是两个master_log_file是主服务器的log日志,日志文件名称和主服务器的show master status;显示的一致,master_log_pos=0表明从头开始读取log文件,其实这两个参数不是很实用,因为MySQL实例重启一次或者调用flush logs;就会生成新的日志文件,如果这里指定了文件名,就需要手工重新设置change master to .....,否则会产生1236错误,如果不指定,系统会自动识别,master_log_pos也是一样。
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.11
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-biin.000003
Read_Master_Log_Pos: 4
Relay_Log_File: ShiYongqiang-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-biin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
ERROR: No query specified
这里显示Slave_IO_Running,Slave_SQL_Running都为NO,前者代表请求数据的线程没有运行,后者代表重放SQL的线程没有执行,还可以看到其他的参数,如可以针对某个数据库复制、针对某个表复制等等,现在是针对全服务器复制的。
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: rep1
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 506
Relay_Log_File: mysql-relay-bin.000028
Relay_Log_Pos: 535
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 506
Relay_Log_Space: 1244
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
ERROR: No query specified
可以看出这里的从服务器的复制开始运行了,需要注意log文件的名称和上面未必一致,因为在实验时重启了几次服务器,重启服务器的命令为:
启动 mysqld
关闭 mysqladmin -uroot -P 3308 shutdown
还可以查看到底有哪些线程在执行,
MariaDB [none]> show processlist\G;
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost:3994
db: shiyq
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
Id: 28
User: system user
Host:
db: NULL
Command: Connect
Time: 6714
State: Waiting for master to send event
Info: NULL
Progress: 0.000
*************************** 3. row ***************************
Id: 29
User: system user
Host:
db: NULL
Command: Connect
Time: 39
State: Slave has read all relay log; waiting for the slave I/O thread to upda
te it
Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
ERROR: No query specified
可以看出id为28的是和主服务器通信的线程,29为重写SQL的线程。
在主服务器上运行同样的命令,可以看到以下效果
MariaDB [(none)]> show processlist\G;
*************************** 1. row ***************************
Id: 9
User: rep1
Host: ShiYongqiang:4807
db: NULL
Command: Binlog Dump
Time: 6875
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 11
User: root
Host: localhost:9309
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
2 rows in set (0.00 sec)
ERROR: No query specified
可以看出id为9的线程,是二进制日志转储线程,已经将所有的二进制文件都发送给了从服务器。
下面可以看看实际效果,
主从服务器是一样的配置,所以其默认数据库是一样的,如下
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
主服务器:
MariaDB [(none)]> use shiyq
Database changed
MariaDB [shiyq]> create table t(a int auto_increment primary key,b varchar(10));
Query OK, 0 rows affected (0.02 sec)
MariaDB [shiyq]> insert into t(b) values(‘shiyq‘);
Query OK, 1 row affected (0.00 sec)
MariaDB [shiyq]> insert into t(b) select b from t;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [shiyq]> insert into t(b) select b from t;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [shiyq]> insert into t(b) select b from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [shiyq]> select * from t;
+---+-------+
| a | b |
+---+-------+
| 1 | shiyq |
| 2 | shiyq |
| 3 | shiyq |
| 4 | shiyq |
| 6 | shiyq |
| 7 | shiyq |
| 8 | shiyq |
| 9 | shiyq |
+---+-------+
8 rows in set (0.00 sec)
从服务器:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use shiyq;
Database changed
MariaDB [shiyq]> show tables;
+-----------------+
| Tables_in_shiyq |
+-----------------+
| t |
+-----------------+
1 row in set (0.00 sec)
MariaDB [shiyq]> select * from t;
+---+-------+
| a | b |
+---+-------+
| 1 | shiyq |
| 2 | shiyq |
| 3 | shiyq |
| 4 | shiyq |
| 6 | shiyq |
| 7 | shiyq |
| 8 | shiyq |
| 9 | shiyq |
+---+-------+
8 rows in set (0.00 sec)
可以看出复制完全正常。
下面增加一个从服务器,端口为3309,server-id为12,在my.ini的修改如下
log-bin=mysql-bin
server-id = 12
relay_log = D:\mysql\mariadb-10.0.12-winx64-3\data\mysql-relay-bin
log_slave_updates = 1
read_only = 1
启动这个MySQL实例,叫做实例3,默认情况下,没有shiyq数据库,运行下列命令:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> change master to master_host=‘192.168.1.11‘,master_port=3308, master_user=‘rep1‘,master_password=‘123456‘;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: rep1
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 3002
Relay_Log_File: mysql-relay-bin.000025
Relay_Log_Pos: 3289
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3002
Relay_Log_Space: 3629
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> show processlist\G;
*************************** 1. row ***************************
Id: 3
User: root
Host: localhost:9761
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 25
State: Waiting for master to send event
Info: NULL
Progress: 0.000
*************************** 3. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 669
State: Slave has read all relay log; waiting for the slave I/O thread to upda
te it
Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| test |
+--------------------+
5 rows in set (0.00 sec)
在主服务器上运行如下命令:
MariaDB [shiyq]> show processlist\G;
*************************** 1. row ***************************
Id: 9
User: rep1
Host: ShiYongqiang:4807
db: NULL
Command: Binlog Dump
Time: 8171
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 11
User: root
Host: localhost:9309
db: shiyq
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
*************************** 3. row ***************************
Id: 15
User: rep1
Host: ShiYongqiang:9895
db: NULL
Command: Binlog Dump
Time: 72
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
ERROR: No query specified
可以看出,有了两个二进制日志转储线程,为两个从服务器服务。
再增加一个服务器,将实例二作为主服务器,进行复制,修改my.ini如下:
port=3310
log-bin=mysql-bin
server-id = 13
relay_log = D:\mysql\mariadb-10.0.12-winx64-4\data\mysql-relay-bin
log_slave_updates = 1
read_only = 1
在实例1上运行命令:
MariaDB [shiyq]> create database shiyq1;
Query OK, 1 row affected (0.00 sec)
MariaDB [shiyq]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| shiyq1 |
| test |
+--------------------+
6 rows in set (0.00 sec)
所有的服务器都更新了。
在实例二运行如下命令:
MariaDB [shiyq]> create database shiyq2;
Query OK, 1 row affected (0.00 sec)
MariaDB [shiyq]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| shiyq1 |
| shiyq2 |
| test |
+--------------------+
7 rows in set (0.00 sec)
可以看到实例4上也增加了数据库,在这些服务器上用show master status;show slave status\G;show processlist\G,可以看到相应的线程。
2)两台完全相同机器的MySQL实例
创建两个VirtualBox的虚拟机,操作系统为ubuntu 14.04服务器版,在部署的时候遇到一些小问题,安装了一台虚拟器,使用apt-get安装了系统自带的mysql后,为了方便和保证一致性,直接复制了使用,发现不行,在加载的时候提示uuid已存在,后来才知道需要用VirutalBox的命令C:\Program Files\Oracle\VirtualBox>VBoxManage.exe clonevdi d:\vm\ubuntu.vdi d:\vm\ubuntu1.vdi,才可以,然后部署了两个虚拟机,启动之后发现,还是不行,复制的虚拟机ip没有获取网络的DHCP,后来修改了一下虚拟机的网络配置,将网络连接方式改成桥接就好了。
ubuntu自带的mysql为Oracle的主流版本,版本为很有意思,Oracle子公司Redhat自带的MySQL是MariaDB。
两个虚拟机的IP地址分为192.168.1.152和192.168.1.154,前者为实例1,后者为实例2,实例1作为主服务器,实例2作为从服务器。
修改实例1的/etc/mysql/my.conf,增加或修改如下
server-id = 10
log_bin = /var/log/mysql/mysql-bin.log
设置复制权限,
mysql> grant replication slave on *.* to rep1@‘192.168.1.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.01 sec)
修改实例2的/etc/mysql/my.conf,增加或修改如下
server-id = 11
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1
read_only = 1
运行命令
mysql>change master to master_host=‘192.168.1.152‘,master_port=3306, master_user=‘rep1‘,master_password=‘123456‘;
mysql>start slave;
然后在主服务器上运行sql,可以看到实际效果
配置双向复制
修改实例1的/etc/mysql/my.cn,增加
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1
read_only = 1
运行命令
mysql> grant replication slave on *.* to rep1@‘192.168.1.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.01 sec)
mysql>change master to master_host=‘192.168.1.154‘,master_port=3306, master_user=‘rep1‘,master_password=‘123456‘;
mysql>start slave;
双向复制理论上是可行的,比如在终端窗口输入,因为传输速度很快,等你切换终端的时候,就已经复制过来了,但是在高并发的情况下,传输可能会滞后,就会出现问题,可以配置双向复制,但是只启动一个主从复制,如果有问题,可以随时切换。
MySQL学习(二)复制,布布扣,bubuko.com
MySQL学习(二)复制
原文:http://www.cnblogs.com/stone-fly/p/3857739.html