一，Mysql复制概述
Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后*并等待主服务器通知新的更新。
1.复制能解决的问题
数据分布(多个地区的数据分发)
负载均衡(读写分离)
备份
高可用和故障切换的核心功能
测试mysql升级
2.复制的原理
mysql复制的原理现阶段都是一样的，master将操作记录到bin-log中，slave的一个线程去master读取bin-log，并将他们保存到relay-log中，slave的另外一个线程去重放relay-log中的操作来实现和master数据同步。
3.复制的历史
mysql-3.2 开始支持基于命令的复制，也就是statement-based replication。mysql-5.1 开始支持基于行的复制和混合复制，也就是row-based replication和mixed-based relication，mysql-5.5 开始支持semi-synchronous的复制，也叫半同步复制，目的在于事务环境下保持主从一致，mysql-5.6 开始支持延时复制。
下面是复制的基本原理：
二，配置主从复制
为了演示方便，实验使用通用的二进制包来安装mysql:
mysql版本：mysql-5.5.33-linux2.6-x86_64
OS版本：Centos 6.4 x86:64
首先来安装master服务器
[root@localhost ~]# useradd -r -u 120 mysql [root@localhost ~]# tar zxvf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/ [root@localhost local]# ln -s mysql-5.5.33-linux2.6-x86_64 mysql [root@localhost local]# cd mysql [root@localhost mysql]# mkdir /mydata/data -p [root@localhost mysql]# chown -R root.mysql ./* [root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data [root@localhost mysql]# cp support-files/my-large.cnf /etc/my.cnf [root@localhost mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld [root@localhost mysql]# chmod +x /etc/init.d/mysqld [root@localhost mysql]# chkconfig --add mysqld [root@localhost mysql]# chkconfig mysqld on [root@localhost mysql]# vi /etc/my.cnf # 在mysqld中添加如下俩行 datadir=/mydata/data innodb_file_per_table=1 [root@localhost mysql]# service mysqld start Starting MySQL...... SUCCESS!
在从服务器上执行上面相同的操作
在master服务器上启用二进制日志,设置server-id（必须保证和从服务器不同）
[root@localhost mysql]# vi /etc/my.cnf log-bin = /mydata/data/master-bin log-bin-index = /mydata/data/master-bin.index server-id = 1
创建具体复制权限的用户
[root@localhost ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> grant replication slave,replication client on *.* to ‘repl‘@‘192.168.30.%‘ identified by ‘123456‘; mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) [root@localhost ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL... SUCCESS!
登录从服务器
[root@localhost mysql]# vi /etc/my.cnf # 开启中继日志 relay-log=/mydata/data/relay-log relay-log-index=/mydata/data/relay-log.index server-id=2 # 关闭二进制日志 #log-bin=mysql-bin [root@localhost ~]# service mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL... SUCCESS!
连接至主服务器，并开始复制
# 查看主服务器正在使用的binlog和当前的position
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
# 在从服务器连接主服务器
mysql> change master to master_host=‘192.168.30.115‘
-> master_port=3306
-> master_log_file=‘mysql-bin.000002‘
-> master_user=‘repl‘
-> master_password=‘123456‘
-> master_log_pos=107;
Query OK, 0 rows affected (0.49 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.30.115
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
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: 107
Relay_Log_Space: 403
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: 1
1 row in set (0.00 sec)
以上信息发现
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明主从服务器配置成功了。
三，配置半同步复
登录主服务器
# 加载半同步复制模块 mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so‘; Query OK, 0 rows affected (0.00 sec) # 开启半同步复制功能 mysql> set global rpl_semi_sync_master_enabled=1; Query OK, 0 rows affected (0.00 sec) # 设置主服务器等待从服务器多长时间，开始转为异步复制 mysql> set global rpl_semi_sync_master_timeout=1000; Query OK, 0 rows affected (0.00 sec)
登录从服务器
mysql> install plugin rpl_semi_sync_slave soname‘semisync_slave.so‘; Query OK, 0 rows affected (0.00 sec) mysql> set global rpl_semi_sync_slave_enabled=1; Query OK, 0 rows affected (0.00 sec) mysql> stop slave IO_THREAD; Query OK, 0 rows affected (0.07 sec) mysql> start slave IO_THREAD; Query OK, 0 rows affected (0.00 sec)
在Master和Slave的my.cnf中编辑
# On Master [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 # 1 second # On Slave [mysqld] rpl_semi_sync_slave_enabled=1
# 也可通过设置全局变量的方式来设置,如下： set global rpl_semi_sync_master_enabled=1 # 取消加载插件 mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
查看从服务器上的semi_sync是否开启:
mysql> 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) # 注意clients 变为1 ，证明主从半同步复制连接成功
在主-从架构上，建议使用的配置：
主服务器：
sync_binlog=1 # 主服务器崩溃，任何一个事务提交之后就立即写入到磁盘中的二进制文件
innodb_flush_logs_at_trx_commit=1 #任何一个事物提交之后就立即写入到磁盘中的日志文件
从服务器：
skip_slave_start=1 #重启从服务器时不自动开启slave进程
read_only=1 #设置从服务器为只读模式
四，配置基于SSL主从复制
配置Master为CA服务器
[root@localhost ~]# cd /etc/pki/CA [root@localhost CA]# (umask 077;openssl genrsa 2048 > private/cakey.pem) [root@localhost CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650 [root@localhost CA]# mkdir certs crl newcerts [root@localhost CA]# touch index.txt [root@localhost CA]# echo 01 > serial
在主服务器上为mysql准备私钥和证书
[root@localhost ~]# mkdir /usr/local/mysql/ssl [root@localhost ~]# cd /usr/local/mysql/ssl/ [root@localhost ssl]# (umask 077;openssl genrsa 1024 > mysql.key) # 这里生成的证书请求要去CA的一致 [root@localhost ssl]# openssl req -new -key mysql.key -out mysql.csr [root@localhost ssl]# openssl ca -in mysql.csr -out mysql.crt [root@localhost ssl]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/ [root@localhost ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/
在从服务器上：
[root@localhost ~]# mkdir /usr/local/mysql/ssl [root@localhost ~]# chown -R mysql.mysql /usr/loca/mysql/ssl [root@localhost ~]# cd /usr/local/mysql/ssl/ [root@localhost ssl]# (umask 077;openssl genrsa 1024 > mysql.key) # 这里填写也要与CA一致 root@localhost ssl]# openssl req -new -key mysql.key -out mysql.csr # 把证书申请传到CA服务器上 [root@localhost ssl]# scp mysql.csr root@192.168.30.115:/root/
在master为slave签署证书
[root@localhost ~]# openssl ca -in mysql.csr -out mysql.crt [root@localhost ~]# scp ./mysql.crt root@192.168.30.116:/usr/local/mysql/ssl [root@localhost ~]# scp /etc/pki/CA/cacert.pem root@192.168.30.116:/usr/local/mysql/ssl/ #在从服务器，设置ssl属主属组为mysql [root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/ssl/
在主从服务器上都开启ssl功能：
[root@localhost ~]# vi /etc/my.cnf # 添加如下几行 ssl ssl-ca=/usr/local/mysql/ssl/cacert.pem ssl-cert=/usr/local/mysql/ssl/mysql.crt ssl-key=/usr/local/mysql/ssl/mysql.key [root@localhost ~]# service mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL... SUCCESS! [root@localhost ssl]# mysql mysql> show variables like ‘%ssl%‘; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: *** NONE *** +---------------+---------------------------------+ | 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/mysql.crt | | ssl_cipher | | | ssl_key | /usr/local/mysql/ssl/mysql.key | +---------------+---------------------------------+ 7 rows in set (0.00 sec)
以上信息显示master服务器ssl功能已经开启
在slave服务器执行上面同样的操作，开启ssl功能
在master服务器删除原来的复制账号，添加新的复制账号
mysql> delete from user where User=‘repl‘; mysql> grant replication client,replication slave on *.* to ‘sslrepl‘@‘192.168.30.%‘ identified by ‘123456‘ require ssl; mysql> flush privileges;
在slave服务器连接master
mysql> stop slave;
mysql> change master to master_host=‘192.168.30.115‘
-> master_port=3306
-> master_log_file=‘mysql-bin.000005‘
-> master_user=‘sslrepl‘
-> master_password=‘123456‘
-> master_log_pos=107
-> master_ssl=1
-> master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘
-> master_ssl_cert=‘/usr/local/mysql/ssl/mysql.crt‘
-> master_ssl_key=‘/usr/local/mysql/ssl/mysql.key‘;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.30.115
Master_User: sslrepl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000005
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: 107
Relay_Log_Space: 403
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path: /usr/local/mysql/ssl
Master_SSL_Cert: /usr/local/mysql/ssl/mysql.crt
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/ssl/mysql.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: 1
1 row in set (0.00 sec)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Allowed: Yes 三个都输出为yes 才表明配置成功
五，在slave主机测试
[root@localhost ~]# mysql -u sslrepl -h 192.168.30.115 -p123456 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/mysql.crt --ssl-key=/usr/local/mysql/ssl/mysql.key mysql> \s -------------- mysql Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 6 Current database: Current user: sslrepl@192.168.30.116 SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: ‘‘ Using delimiter: ; Server version: 5.5.33-log MySQL Community Server (GPL) Protocol version: 10 Connection: 192.168.30.115 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 TCP port: 3306 Uptime: 24 min 25 sec Threads: 2 Questions: 23 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.015
信息：SSL: Cipher in use is DHE-RSA-AES256-SHA 表明连接是加密的
Mysql-5.5.33主从复制，半同步复制，以及基于ssl的复制已经配置完成
本文出自 “龍行天下” 博客，请务必保留此出处http://luojianlong.blog.51cto.com/4412415/1387371