MySQL学习（二）复制

    复制解决的问题是保持多个服务器之间的数据的一致性，就如同通过复制保持两个文件的一致性一样，只不过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-binserver-id = 11relay_log = D:\mysql\mariadb-10.0.12-winx64-1\data\mysql-relay-binlog_slave_updates = 1read_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: NULLMaster_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: 0Master_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 processlistProgress: 0.000*************************** 2. row ***************************      Id: 28    User: system user    Host:      db: NULL Command: Connect    Time: 6714   State: Waiting for master to send event    Info: NULLProgress: 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 update it    Info: NULLProgress: 0.0003 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: NULLProgress: 0.000*************************** 2. row ***************************      Id: 11    User: root    Host: localhost:9309      db: NULL Command: Query    Time: 0   State: init    Info: show processlistProgress: 0.0002 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 shiyqDatabase changedMariaDB [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 changedMariaDB [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-binserver-id= 12relay_log = D:\mysql\mariadb-10.0.12-winx64-3\data\mysql-relay-binlog_slave_updates = 1read_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: 0Master_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 processlistProgress: 0.000*************************** 2. row ***************************      Id: 4    User: system user    Host:      db: NULL Command: Connect    Time: 25   State: Waiting for master to send event    Info: NULLProgress: 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 update it    Info: NULLProgress: 0.0003 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: NULLProgress: 0.000*************************** 2. row ***************************      Id: 11    User: root    Host: localhost:9309      db: shiyq Command: Query    Time: 0   State: init    Info: show processlistProgress: 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: NULLProgress: 0.0003 rows in set (0.00 sec) ERROR: No query specified可以看出，有了两个二进制日志转储线程，为两个从服务器服务。再增加一个服务器，将实例二作为主服务器，进行复制，修改my.ini如下：port=3310log-bin=mysql-binserver-id= 13relay_log = D:\mysql\mariadb-10.0.12-winx64-4\data\mysql-relay-binlog_slave_updates = 1read_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 = 10log_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 = 11log_bin = /var/log/mysql/mysql-bin.logrelay_log = /var/log/mysql/mysql-relay-bin.loglog_slave_updates = 1read_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.loglog_slave_updates = 1read_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;双向复制理论上是可行的，比如在终端窗口输入，因为传输速度很快，等你切换终端的时候，就已经复制过来了，但是在高并发的情况下，传输可能会滞后，就会出现问题，可以配置双向复制，但是只启动一个主从复制，如果有问题，可以随时切换。