Mysql内建的复制功能是构建大型、高性能应用程序的基础。复制功能不仅有利于构建高性能的应用,同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。
一、复制概述
1、复制的功用
数据分布、负载均衡、备份、高可用性、MySQL升级测试
2、复制的工作原理
①主库把数据更改记录到二进制日志(binary log)中
②备库将主库的二进制日志复制到本地的中继日志(relay log)中。首先,备库会启动一个I/O线程,跟主库建立一个普通的客户端连接,然后在主库上启动一个特殊的二进制转储(binlog dump)线程,此转储线程会读取主库上二进制日志中的事件。如果该线程追赶上了主库,则进入睡眠状态,直到主库发送信号通知其有新的事件产生时才会被唤醒,备库I/O线程会将接收到的事件记录到中继日志中。
③备库的SQL线程从中继日志中读取事件本执行,从而实现备库数据更新。
3、复制方式
①基于语句的复制:实际上是把主库上执行的SQL语句在从库上重放一遍,因此效率高,占用带宽小,但不如基于行的复制精确,对于不确定性的语句(例如包含时间函数的语句)会有问题。另外这种复制是串行的,为了保证串行执行,需要加更多的锁。
②基于行的复制:此时二进制日志记录的是数据本身,这无疑会增加网络带宽消耗和I/O线程负载,优点是从库无需sql语句重放,但无法判断执行了哪些SQL语句
③混合模式,即上面两种方式的组合
mysql默认基于语句复制,建议采用基于行的复制;
4、mysql复制常用架构
一主一从
一主多从:常用于写操作不频繁,查询量较大的环境中
主主互备:即两台mysql server互相将对方作为自己的master,避免了单点故障,主要用于对mysql写操作比较频繁的环境中
多源复制:即slave服务器可指向多个master服务器;MySQL 5.7和MariaDB 10支持,主要用于对mysql读写量都比较大的环境中
★5、特别说明
mysql复制集群中,每个server的Server ID必须唯一;
集群中如果有两个或更多的master,只允许对不同的数据库进行写操作,否则在复制时会产生混乱;
slave服务器只许读,不许写;
建议使用InnoDB作为默认存储引擎,基于语句复制
二、主从复制
1、版本:从节点版本不能低于主节点
2、从哪里开始复制
⑴从0开始:适用于主从均为新建立的服务器;
⑵如果主服务器已经运行一段时间且存在不小的数据量:
①完全备份主服务器数据,并将数据恢复至从服务器;
mysqldump --single-transaction --all-databases --master-data=2 --host=SERVER1 | mysql --host=SERVER2
--master-data:该选项将binlog的位置和文件名追加到输出文件中。如果为1,将会输出CHANGE MASTER TO命令;如果为2,则在输出的CHANGE MASTER TO命令前添加注释。该选项会打开--lock-all-tables选项,除非另外指定--single-transaction。
②从服务器从备份时主服务器二进制日志所在位置开始复制;
3、配置过程
⑴master:
①启用二进制日志
②定义server-id
③创建有复制权限的账号:
GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.30.%' IDENTIFIED BY 'replp@ss';
FLUSH PRIVILEGES;
⑵slave:
①启用中继日志
②定义server-id
③使用有复制权限的账号连接master
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name' # 指明要连接的主节点
| MASTER_USER = 'user_name' # 具有复制权限的账号
| MASTER_PASSWORD = 'password' # 上述用户的密码
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name' # 复制起点,主节点上的二进制日志
| MASTER_LOG_POS = master_log_pos # 复制起点,主节点上二进制日志中的事件位置
......
例如:CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=495;
④启动io thread以及sql thread:START SLAVE;
也可单独启动IO_THREAD或者SQL_THREAD线程,如START SLAVE IO_THREAD;
⑤查看从节点的状态:SHOW SLAVE STATUS\G
⑥查看线程状态:SHOW PROCESSLIST;
⑦相关文件:
master.info:记录了主库帐号信息和I/O线程当前读取到主库的二进制文件的位置
relay-log.info:记录从库的SQL线程当前读取到中继日志的位置
⑧从库如果不做级连复制或者备份,就不要开启二进制日志(在配置文件中添加log_bin=off)
4、复制中要注意的问题
⑴如何限制从服务器只读?
①更改slave的全局服务器变量read-only为yes:
动态:SET GLOBAL read_only = on;
静态:编辑配置文件
[mysqld]
read_only = on
注意:此限制对于拥有super权限的用户无效
②阻止所有用户执行写操作:FLUSH TABLES WITH READ LOCK;
⑵如何保证复制的“安全”?
master:
设置参数:sync_binlog = 1
mysql处理commit语句,它将整个事务写入二进制日志并将事务提交给存储引擎中,如果开启该选项,mysql每次在提交事务给存储引擎之前会将二进制日志同步到磁盘上,保证在服务器崩溃时不会丢失事件。
如果使用InnoDB存储引擎:
innodb_flush_log_at_trx_commit = 1 (默认启用)
innodb_support_xa = on (默认启用)
slave:
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
说明:
slave从master复制binary log写到relay log,指的是先写到 “OS cache”的relay-log,而不是马上刷新到磁盘上,什么时候刷新到磁盘还依赖于cache刷新时间。sync_relay_log #就表示每写入几次,触发一次文件同步,其它同理;这三个选项是从MySQL 5.5才开始引进的
以下示例中,主库node1: 192.68.30.10,从库node2: 192.68.30.20;使用MySQL 5.6.30
主库:
[root@node1 ~]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /mydata/data port = 3306 server_id = 1 socket = /tmp/mysql.sock skip-name-resolve log-bin = /mydata/binlogs/master-bin binlog_format = row sync_binlog = 1 default_storage_engine = innodb #从MySQL 5.5开始默认的存储引擎就是InnoDB,这项可不设置 [root@node1 ~]# service mysqld start Starting MySQL SUCCESS! [root@node1 ~]# mysql < hellodb.sql #生成一些数据 [root@node1 ~]# mysql ... mysql> prompt master PROMPT set to 'master> ' master> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) #创建具有复制权限的账号: master> grant replication slave on *.* to repluser@'192.168.30.%' identified by 'replp@ss'; Query OK, 0 rows affected (0.01 sec) master> flush privileges; Query OK, 0 rows affected (0.00 sec) master> exit Bye [root@node1 ~]# mysqldump --single-transaction --all-databases --master-data=2 > msback.sql #完全备份 [root@node1 ~]# scp msback.sql root@node2:/root/ msback.sql 100% 640KB 640.1KB/s 00:00
从库:
[root@node2 ~]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /mydata/data port = 3306 server_id = 2 #要与主库不同 socket = /tmp/mysql.sock skip-name-resolve log-bin = /mydata/binlogs/slave-bin #如果不是基于GTID复制或不打算让slave充当其它slave的master,可不用开启 binlog_format = row sync_binlog = 1 relay-log = /mydata/relaylogs/slave-relay #启用中继日志 sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1 read_only = on #只读 [root@node2 ~]# service mysqld start Starting MySQL SUCCESS! [root@node2 ~]# mysql < msback.sql #先将主库的备份数据恢复至从库 [root@node2 ~]# grep "^-- CHANGE" msback.sql #查找主库备份那一刻的二进制日志文件位置 -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000004', MASTER_LOG_POS=120; [root@node2 ~]# mysql mysql> prompt slave> PROMPT set to 'slave> ' slave> change master to master_host='192.168.30.10',master_user='repluser',master_password='replp@ss',\ -> master_log_file='master-bin.000004',master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.26 sec) slave> start slave; Query OK, 0 rows affected (0.01 sec) slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.30.10 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 120 #io thread当前读取到主库的二进制文件的位置 Relay_Log_File: slave-relay.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes #io thread线程的状态 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: 120 Relay_Log_Space: 453 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服务器的时长 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: 1 #主库的server_id Master_UUID: 5b1eb4fa-270c-11e6-917c-000c2940359d Master_Info_File: /mydata/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) [root@node2 ~]# cat /mydata/data/master.info #master.info文件的初始数据是由change master命令生成的 23 master-bin.000004 120 #一旦复制了新的日志,master_log_pos的值就会更新 repluser replp@ss 3306 60 0
测试:
master> create database testdb; Query OK, 1 row affected (0.00 sec) master> use testdb Database changed master> create table students (Name char(30),Gender enum('m','f')); Query OK, 0 rows affected (0.03 sec)
slave> show databases; +--------------------+ | Database | +--------------------+ ... | testdb | +--------------------+ 6 rows in set (0.00 sec) slave> show tables from testdb; +------------------+ | Tables_in_testdb | +------------------+ | students | +------------------+ 1 row in set (0.00 sec) slave> show processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 2 | system user | | NULL | Connect | 5759 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 3 | root | localhost | NULL | Query | 0 | init | show processlist | | 4 | system user | | NULL | Connect | 5759 | Waiting for master to send event | NULL | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) [root@node2 ~]# cat /mydata/data/master.info 23 master-bin.000004 356 192.168.30.10 repluser replp@ss 3306 60 0
5、半同步复制
MySQL 5.5之前的复制都是异步的,主服务器在将更新操作写入二进制日志文件中后,不用管从服务器是否已经完成复制,就可以自由处理其它事务处理请求。异步复制能提供较高的性能,但无疑易造成主/从服务器数据的不一致。
MySQL 5.5开始引入半同步复制功能,此功能是由google开发的一个插件实现的。半同步复制要求主库提交的每一个事务,至少有一个备库成功接收后,才能继续提交下一个。
半同步复制的概念详解:
①当slave主机连接到master时,能够查看其是否已开启半同步复制功能。
②当master上开启半同步复制的功能时,至少应该有一个slave开启此功能。此时,一个线程在master上提交事务将受到阻塞,直到得知一个已开启半同步复制功能的slave已收到此事务的所有事件,或等待超时。
③当一个事务的事件都已写入relay-log中且已刷新到磁盘,slave才会告知已收到。在 master实例上,有一个专门的线程(ack_receiver)接收备库的响应消息。
④如果等待超时,也就是master没被告知已收到,此时master会自动转换为异步复制模式。当至少一个半同步的slave赶上了,master与其slave自动转换为半同步复制。
⑤半同步复制的功能要在master,slave都开启,若只开启一边,它依然为异步复制。
master:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SHOW GLOBAL VARIABLES LIKE '%semi%';
SET GLOBAL rpl_semi_sync_master_enabled = on;
SET GLOBAL rpl_semi_sync_master_timeout = 5000; #等待从服务器确认的超时时长,单位为毫秒,超时则转为异步模式
[mysqld]
rpl_semi_sync_master_enabled = on
rpl_semi_sync_master_timeout = 5000
SHOW GLOBAL STATUS LIKE 'rpl_semi%';
slave:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = on;
然后只需重启io thread:
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
[mysqld]
rpl_semi_sync_slave_enabled = on
主库: master> install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.20 sec) master> show global variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) master> set global rpl_semi_sync_master_enabled = on; Query OK, 0 rows affected (0.01 sec) master> set global rpl_semi_sync_master_timeout = 5000; Query OK, 0 rows affected (0.00 sec) master> show global variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 5000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) [root@node1 ~]# vim /etc/my.cnf [mysqld] ... rpl_semi_sync_master_enabled = on rpl_semi_sync_master_timeout = 5000
从库: slave> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.11 sec) slave> show global variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec) slave> set global rpl_semi_sync_slave_enabled = on; Query OK, 0 rows affected (0.00 sec) slave> stop slave io_thread; Query OK, 0 rows affected (0.01 sec) slave> start slave io_thread; Query OK, 0 rows affected (0.00 sec) slave> show global status like 'rpl_semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.00 sec) [root@node2 mydata]# vim /etc/my.cnf [mysqld] ... rpl_semi_sync_slave_enabled = on
主库: master> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | #已连接的启用了半同步复制的从服务器数量 | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | #服务器当前是否工作于半同步复制模式 | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | #表示通过半同步复制到从库的事务数 +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
6、发送复制事件到其它从库
MySQL支持多级复制,即一个slave可以充当其它服务器的master。这需要开启log_slave_updates选项,在开启该选项后,MySQL会将其执行过的事件记录到自己的二进制日志中。这样其它从库就可以复制其二进制日志。
复制级别不宜过多,以免数据落后太多。
可以让一个从库专门做分发主库,而不用负责查询工作,这时候可将它的存储引擎修改为blackhole(不生成任何实际数据)。
7、复制过滤器
让slave仅复制有限的几个数据库,甚至于仅复制某数据库内有限的几张表的机制;
有两种方案:
⑴在主节点上过滤:在向二进制日志记录事件时,仅记录指定数据库的相关操作;
binlog_do_db = # 数据库白名单
binlog_ignore_db = # 数据库黑名单
⑵在从节点上过滤:仅从中继日志中读取指定的数据库或表的相关事件并应用于本地;
replicate_do_db =
replicate_ignore_db =
replicate_db_table = DB_NAME.TB_NAME
replicate_ignore_table =
replicate_wild_do_table = #可使用通配符
replicate_wild_ignore_table =
复制过滤容易出问题,不到万不得已,不建议使用;如果实在要使用,建议在从节点上过滤
8、基于ssl的复制
⑴配置主库为CA服务器
[root@node1 ~]# cd /etc/pki/CA [root@node1 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048) #生成密钥对 Generating RSA private key, 2048 bit long modulus ....................+++ ............................+++ e is 65537 (0x10001) [root@node1 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650 #CA自签证书 ... Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:Zhejiang Locality Name (eg, city) [Default City]:Hangzhou Organization Name (eg, company) [Default Company Ltd]:Dongpu Organizational Unit Name (eg, section) []:tech Common Name (eg, your name or your server's hostname) []:ca.dongpu.com Email Address []:ca@dongpu.com [root@node1 CA]# touch index.txt serial [root@node1 CA]# echo 01 > serial [root@node1 CA]# mkdir csr
⑵为主服务器创建证书
[root@node1 CA]# mkdir /usr/local/mysql/ssl [root@node1 CA]# cd /usr/local/mysql/ssl [root@node1 ssl]# (umask 077;openssl genrsa -out master.key 2048) Generating RSA private key, 2048 bit long modulus .......................+++ ...................+++ e is 65537 (0x10001) [root@node1 ssl]# openssl req -new -key master.key -out master.csr #生成证书签署请求 ... Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:Zhejiang Locality Name (eg, city) [Default City]:Hangzhou Organization Name (eg, company) [Default Company Ltd]:Dongpu Organizational Unit Name (eg, section) []:tech Common Name (eg, your name or your server's hostname) []:mysqlmaster@dongpu.com Email Address []:mysqlmaster@dongpu.com ... [root@node1 ssl]# openssl ca -in master.csr -out master.crt -days 1000 #CA签署证书 ... 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Data Base Updated [root@node1 ssl]# cp /etc/pki/CA/cacert.pem ./ [root@node1 ssl]# ls cacert.pem master.crt master.csr master.key [root@node1 ssl]# chown -R mysql.mysql . [root@node1 ssl]# ll #注意,要确保密码文件、证书文件等对于mysql用户可读 total 20 -rw-r--r-- 1 mysql mysql 1464 Jun 1 01:30 cacert.pem -rw-r--r-- 1 mysql mysql 4686 Jun 1 02:31 master.crt -rw-r--r-- 1 mysql mysql 1074 Jun 1 02:31 master.csr -rw------- 1 mysql mysql 1675 Jun 1 03:27 master.key
⑶为从服务器创建证书,参考上述步骤,略
[root@node2 ssl]# ls cacert.pem slave.crt slave.csr slave.key
⑷在主库上启用ssl认证,并指定有复制权限的用户要以ssl的方式连接
mysql> show global variables like '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | +---------------+----------+ 9 rows in set (0.04 sec) [root@node1 ~]# vim /etc/my.cnf [mysqld] ... ssl_ca = /usr/local/mysql/ssl/cacert.pem ssl_cert = /usr/local/mysql/ssl/master.crt ssl_key = /usr/local/mysql/ssl/master.key [root@node1 ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! mysql> show global variables like '%ssl%'; +---------------+---------------------------------+ | Variable_name | Value | +---------------+---------------------------------+ | have_openssl | YES | | have_ssl | YES #显示已启用 | | ssl_ca | /usr/local/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /usr/local/mysql/ssl/master.crt | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /usr/local/mysql/ssl/master.key | +---------------+---------------------------------+ 9 rows in set (0.00 sec) mysql> revoke all privileges, grant option from repluser@'192.168.30.%'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave,replication client on *.* to repluser@'192.168.30.%' identified by 'replp@ss' require ssl; Query OK, 0 rows affected (0.01 sec) # 在grant命令中指定require ssl选项 mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)
⑸在从库上也启用ssl认证
[root@node2 ~]# vim /etc/my.cnf [mysqld] ... ssl_ca = /usr/local/mysql/ssl/cacert.pem ssl_cert = /usr/local/mysql/ssl/slave.crt ssl_key = /usr/local/mysql/ssl/slave.key [root@node2 ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@node2 ~]# less /mydata/data/master.info 23 master-bin.000014 721 192.168.30.10 repluser replp@ss 3306 60 0 [root@node2 ~]# mysql #启用ssl后需要在change master to命令中指定ssl相关选项重新指向主库 mysql> change master to master_host='192.168.30.10',master_user='repluser',master_password='replp@ss',\ -> master_log_file='master-bin.000014',master_log_pos=721,\ -> master_ssl=1,\ -> master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',\ -> master_ssl_cert='/usr/local/mysql/ssl/slave.crt',\ -> master_ssl_key='/usr/local/mysql/ssl/slave.key'; Query OK, 0 rows affected, 2 warnings (0.19 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** ... Master_SSL_Allowed: Yes Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /usr/local/mysql/ssl/slave.key ... Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it ... [root@node2 data]# vim master.info 23 master-bin.000024 120 192.168.30.10 repluser replp@ss 3306 60 1 #是否启用ssl的标志位 /usr/local/mysql/ssl/cacert.pem /usr/local/mysql/ssl/slave.crt /usr/local/mysql/ssl/slave.key 0 1800.000
9、为从库设定新的主库
在从库停止复制线程,而后重新设定CHANGE MASTER TO命令即可;
⑴计划内提升一个从库为主库:
①停止向老的主库写入数据;
②让计划提升为主库的从库赶上主库;
③提升从库为主库
④修改其它从库的指向
⑵计划外提升一个从库为主库:
①确定哪个从库的数据为最新最全;
②等待所有的从库执行从主库那复制而来的生成的中继日志;
③在提升为主库的从库上STOP SLAVE;而后,让各从库指向新的主库;
④再次比较主库和各从库上的两个参数:
Master_Log_File,Read_Master_Log_Pos
三、双主互备
(1)双节点都得创建具有复制权限的用户;
(2)双节点都得启用中继日志和二进制日志;
(3)为保证具有自动增长功能的字段能正确生成ID,需要配置两个节点分别使用偶数或奇数ID号;
(4)互相把对方配置为自己的主节点;
A节点:
[mysqld]
server-id=1
log-bin=mysql-bin
relay-log=mysql-relay
auto_increment_increment=2
auto_increment_offset=1
B节点:
[mysqld]
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay
auto_increment_increment=2
auto_increment_offset=2
四、MySQL 5.6中关于复制的几个新特性
1、master.info和relay-log.info支持存储在表中:
master_info_repository = table
relay_log_info_repository = table
对应的表的名称为slave_master_info、slave_relay_log_info,位于mysql库中
2、multi-threads slave
5.6以前的从服务器,一个io线程负责复制binary log,还有一个sql线程负责执行relay log中的sql语句。如果主服务器的数据更新相当频繁,而从服务器由于某些原因跟不上,会导致从服务器落后比较长的时间。5.6之后可采用多个sql线程,每个sql线程处理不同的database,提高了并发性能。
slave_parallel_workers = # (0为禁用,最大1024)
3、延时slave
在每个slave sql线程执行的时候都要等延迟时间到后进行下一个操作;
MASTER_DELAY=#,单位为秒,在CHANGE MASTER TO命令中指定,例如:
CHANGE MASTER TO MASTER_DELAY=120
优点:在一定程度上防止了误操作,比如说删表等等;
可以一定程度上作为有效的数据库备份,无需再另行备份;
案例:误删了test库中的test1表,没有备份,只有延时slave
①先在主库上查找删除表的位置:
SHOW BINLOG EVENTS\G
假设删除表的位置为Log_name:master-bin.000001 Pos:884
②然后在从库上:
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 0
START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='mater-bin.000001',MASTER_LOG_POS=884;
4、GTID
⑴概述
GTID(global transaction identifier,全局事务标识)是对于一个已提交事务的编号,并且是一个全局唯一的编号,由UUID+TID组成的,其中UUID是一个MySQL实例的唯一标识,TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。
例如:7800a22c-95ae-11e4-983d-080027de205a:10
GTID用来代替传统的复制方法。不再使用binlog+pos开启复制,而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。
在传统的slave端,binlog是不用开启的,但是在GTID中,slave端的binlog必须开启,目的是记录执行过的GTID(强制)
⑵GTID的作用
假设有如上一个MySQL复制架构,Server A宕机,需要将业务切换到Server B上。同时,又需要将Server C的复制源改成Server B。复制源修改的命令语法很简单即CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=#。而难点在于,由于同一个事务在每台机器上所在的binlog名字和位置都不一样,那么找到Server C当前同步停止点所对应的Server B上的二进制日志位置就成了难题。这种问题在MySQL 5.6的GTID出现后,就显得非常的简单。由于同一事务的GTID在所有节点上的值一致,那么根据Server C当前停止点的GTID就能唯一定位到Server B上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现,我们都不需要知道GTID的具体值,直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可完成failover的工作。
⑶GITD工作原理
①master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
②slave端的i/o线程将变更的binlog,写入到本地的relay log中。
③sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。
④如果有记录,说明该GTID的事务已经执行,slave会忽略。
⑤如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
⑷开启GTID必须启用如下几项:
gtid_mode = on
enforce_gtid_consistency = on
log_slave_updates = on
这三个选项在主从服务器上都要启用
其它可选项:
binlog_checksum = NONE or CRC32(默认为CRC32)
每个session都会产生checksum值,并且写入到binlog
master_verify_checksum = on
Master 当从binlog dump事件的时候会校验checksum值
slave_sql_verify_checksum = on(默认已启用)
SQL线程当从relay log读取事件应用到slave之前会校验checksum值
report_host = XXX
从库向主库报告的主机名或IP地址,可在主库使用SHOW SLAVE HOSTS命令查看
report_port = #
从库向主库报告的连接端口号,默认为3306
⑸启用GTID后,从库可这样指向主库:
CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='repluser', MASTER_PASSWORD='replp@ss', MASTER_AUTO_POSITION=1;(不用指定具体的二进制日志位置了)
⑹MariaDB中使用GTID需要做的修改:
①不支持的参数:
gtid-mode = on #MariaDB 10默认已启用gtid
enforce-gtid-consistency = on
②修改的参数:
slave_parallel_workers参数修改为slave_parallel_threads
③连接至主库使用的命令:
一个新的参数:MASTER_USE_GTID={current_pos/slave_pos/no}
change master to master_host="127.0.0.1",master_port=3310,master_user="root",master_use_gtid=current_pos;
5、启用GTID做主从复制示例
主库:
[root@node1 ~]# vim /etc/my.cnf [mysqld] ... gtid_mode = on enforce_gtid_consistency = on log_slave_updates = on master_verify_checksum = on [root@node1 ~]# service mysqld restart Shutting down MySQL.. SUCCESS!
从库:
[root@node2 ~]# vim /etc/my.cnf [mysqld] ... gtid_mode = on enforce_gtid_consistency = on log_slave_updates = on slave_parallel_workers = 3 #启动三个SQL线程 report_host = 192.168.30.20 report_port = 3306 [root@node2 ~]# rm -f /mydata/data/master.info #删除现有的master.info只是为了测试后面change master命令中的master_auto_position选项是否能实现复制位置的自动确定 [root@node2 ~]# service mysqld restart Shutting down MySQL.. SUCCESS! [root@node2 ~]# mysql mysql> show global variables like '%gtid%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-------+ 7 rows in set (0.00 sec) mysql> change master to master_host='192.168.30.10',master_user='repluser',master_password='replp@ss',\ -> master_ssl=1,\ -> master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',\ -> master_ssl_cert='/usr/local/mysql/ssl/slave.crt',\ -> master_ssl_key='/usr/local/mysql/ssl/slave.key',\ -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.30.10 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000026 Read_Master_Log_Pos: 151 ...... Auto_Position: 1 #表示已开启自动位置确定 1 row in set (0.00 sec) mysql> show processlist; #确实已启动三个sql线程 +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 6 | root | localhost | NULL | Query | 0 | init | show processlist | | 7 | system user | | NULL | Connect | 68 | Waiting for master to send event | NULL | | 8 | system user | | NULL | Connect | 68 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 9 | system user | | NULL | Connect | 68 | Waiting for an event from Coordinator | NULL | | 10 | system user | | NULL | Connect | 68 | Waiting for an event from Coordinator | NULL | | 11 | system user | | NULL | Connect | 68 | Waiting for an event from Coordinator | NULL | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 6 rows in set (0.00 sec)
测试:
主库: mysql> show slave hosts; #此命令可查看有哪些已连接的从库 +-----------+---------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------------+------+-----------+--------------------------------------+ | 2 | 192.168.30.20 | 3306 | 1 | f61472ed-2714-11e6-91b4-000c29bd6823 | +-----------+---------------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) mysql> create database wuxia; Query OK, 1 row affected (0.02 sec)
从库: mysql> show databases; +--------------------+ | Database | +--------------------+ ...... | wuxia | +--------------------+
基于GTID的复制的其它相关问题可参考博客http://www.tuicool.com/articles/rua2emE
五、多源复制
MySQL 5.7.2和MariaDB 10开始支持多源复制,即一个slave可指向多个master;
以下是MariaDB 10中从库指向多个主库的语法:
CHANGE MASTER ['connection_name'] TO ....
FLUSH RELAY LOGS ['connection_name']
MASTER_POS_WAIT(....,['connection_name'])
RESET SLAVE ['connection_name'] [ALL]
SHOW RELAYLOG ['connection_name'] EVENTS
SHOW SLAVE ['connection_name'] STATUS
SHOW ALL SLAVES STATUS
START SLAVE ['connection_name'...]]
START ALL SLAVES ...
STOP SLAVE ['connection_name'] ...
STOP ALL SLAVES ...
示例:
CHANGE MASTER 'master1' TO MASTER_HOST= '192.168.30.10',MASTER_USER='repluser',MASTER_PASSWORD='replp@ss',MASTER_LOG_FILE='master-bin.000028',master_log_pos=1485;
CHANGE MASTER 'master2' TO MASTER_HOST= '192.168.30.20',MASTER_USER='repluser',MASTER_PASSWORD='replp@ss',MASTER_LOG_FILE='master-bin.000023',master_log_pos=639;
START ALL SLAVES;
SHOW ALL SLAVES STATUS\G
也可单独操作某个connection:
START SLAVE 'master1';
SHOW SLAVE 'master1' STATUS\G
转载于:https://blog.51cto.com/9124573/1785454