基于半同步，ssl的mysql级联复制
今天闲来无事 做了一个mysql级联复制的实验拓扑如下：
操作系统：centos6.4（64bit）
数据库：mysql-5.5.35
主服务器：node1.example.com (192.168.1.166)
ssl主服务器：node2.example.com (192.168.1.167 172.16.0.22)
ssl从服务器：client1.example.com(172.16.0.10 )
注意三台服务器 的时间一定要同步。
1.mysql安装
[root@node1 ~]# fdisk /dev/sdb#新建个分区/dev/sdb1[root@node1 ~]#partx -a /dev/sdb[root@node1 ~]#pvcreate /dev/sdb1[root@node1 ~]#vgcreate vg_data /dev/sdb1[root@node1 ~]#lvcreate -L 2G -n lv_data vg_data #存放数据[root@node1 ~]#lvcreate -L 1G -n lv_log vg_data #存放二进制日志#具体大小自己定[root@node1 ~]#mkfs.ext4 /dev/vg_data/lv_data[root@node1 ~]#mkfs.ext4 /dev/vg_data/lv_log[root@node1 ~]#mkdir -pv /mydata/data[root@node1 ~]#mkdir -pv /mydata/log[root@node1 ~]#useradd -r -u 306 mysql[root@node1 ~]#mount /dev/vg_data/lv_data /mydata/data[root@node1 ~]#mount /dev/vg_data/lv_log /mydata/log[root@node1 ~]# chown mysql.mysql -R /mydata/data[root@node1 ~]#chown mysql.mysql -R /mydata/log[root@node1 ~]#yum install bison gcc gcc-c++ autoconf automake ncurses-devel cmake -y[root@node1 ~]#cd /tools[root@node1 tools]# tar -zxvf mysql-5.5.35.tar.gz[root@node1 tools]#cd mysql-5.5.35[root@node1 mysql-5.5.35]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_DATADIR=/data/mydata \-DSYSCONFDIR=/etc \-DWITH_INNOBASE_STORAGE=1 \-DWITH_ARCHIVE_STORAGE=1 \-DWITH_BLACKHOLE_STORAGE=1 \-DWITH_READLINE=1 \-DWITH_SSL=system \-DWITH_ZLIB=system \-DWITH_LIBWRAP=0 \-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci[root@node1 tools]# make && make install注意：如果编译失败 需要make clean 并且rm -r CMakeCACHE.txt[root@node1 mysql-5.5.35]#cd /usr/local/mysql[root@node1 mysql]#cp supports-file/my-large.cnf /etc/my.cnf[root@node1 mysql]#cp supports-file/mysql.server /etc/rc.d/init.d/mysqld[root@node1 mysql]# scripts/msyql_install_db --user=mysql \--datadir=/mydata/data[root@node1 mysql]#vim /etc/my.cnf#在mysqld 下添加datadir=/mydata/datainnodb_file_per_table=1log-bin=/mydata/log/mysqlbin[root@node1 mysql]# chkconfig --add mysqld[root@node1 mysql]#/etc/init.d/mysqld start
剩下的2台主机，一样的配置
2.主从配置
主服务器配置node1
replication client：可以使用show master status；show slave status命令，也就是说这个权限用于授予账户监视和管理replication状况的权力。
replication slave：这个是基本权利，授予salve服务器连接到master执行replicate操作的权利。
修改server-id：[root@node1 ~]# vim /etc/my.cnf[mysqld]server-id=10[root@node1 ~]# /etc/init.d/mysqld restart登录mysqlmysql>grant replication client,replication slave on *.* to replicationuser@'192.168.1.167' identified by 'mypass';mysql>flush privileges;mysql>show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 182 | | |+------------------+----------+--------------+------------------+
从服务器配置node2
修改server-id[root@node1 ~]# vim /etc/my.cnf[mysqld]server-id=20skip_slave_start=1log_slave_update=1read_only=1relay_log=/mydata/log/log-bin=/mydata/log/mysql-bin[root@node1 ~]# /etc/init.d/mysqld restart[root@node1 ~]# mysql -ureplicationuser -pmypass -h192.168.1.166#验证成功[root@node1 ~]# mysql -uroot -pmysql>change master to master_host='192.168.1.166', master_user='replicationuser', master_password='mypass', master_log_file='mysql-bin.000002', master_log_pos=182;mysql>start slave;mysql> show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
基本的mysql主从配置完成。
skip_slave_start：重启不自动运行主从同步， 阻止崩溃后系统自启动,它可以给你机会修复服务器。
log_slave_update：让slave 也记录binlog，因为我要做级联，他还得做ssl的master，所以必须的开启。
read_only=1：只读模式。
3.SSL 主从配置
mysql 主从复制是明文传送的，如果跨网的话我们可以使用ssl的加密方法来进行。
1）将node1作为CA服务器
[root@node1 log]# cd /etc/pki/CA/[root@node1 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)[root@node1 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or a DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:beijingLocality Name (eg, city) [Default City]:beijingOrganization Name (eg, company) [Default Company Ltd]:topsageOrganizational Unit Name (eg, section) []:techCommon Name (eg, your name or your server's hostname) []:node1.example.comEmail Address []:root@node1.example.com[root@node1 CA]#touch index.txt[root@node1 CA]#echo 01 > serial
2）为node2创建证书申请，并由CA签发证书
[root@node2 ~]# mkir /usr/local/mysql/ssl[root@node2 ~]# cd /usr/local/mysql/ssl[root@node2 ssl]# (umask 077;openssl genrsa -out master.key 2048)[root@node2 ssl]# openssl req -new -key master.key -out master.csr -days 365You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or a DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:beijingLocality Name (eg, city) [Default City]:beijingOrganization Name (eg, company) [Default Company Ltd]:topsageOrganizational Unit Name (eg, section) []:techCommon Name (eg, your name or your server's hostname) []:node2.example.comEmail Address []:root@node2.example.comPlease enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []:[root@node2 ssl]#scp master.csr 192.168.1.166:/root在node1上为node2的master.csr 签发证书[root@node1 ~]# openssl ca -in master.csr -out master.crt -days 365Using configuration from /etc/pki/tls/openssl.cnfCheck that the request matches the signatureSignature okCertificate Details: Serial Number: 1 (0x1) Validity Not Before: Mar 18 06:26:52 2014 GMT Not After : Mar 18 06:26:52 2015 GMT Subject: countryName = CN stateOrProvinceName = beijing organizationName = topsage organizationalUnitName = tech commonName = node2.example.com emailAddress = root@node2.example.com X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Comment: OpenSSL Generated Certificate X509v3 Subject Key Identifier: C4:D8:F2:82:A2:52:CC:16:54:B8:79:74:3A:9A:E9:15:96:89:59:2E X509v3 Authority Key Identifier: keyid:8A:88:0D:B9:67:72:47:29:51:5C:A9:CA:E6:B3:F2:B5:50:4C:A6:4ACertificate is to be certified until Mar 18 06:26:52 2015 GMT (365 days)Sign the certificate? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]yWrite out database with 1 new entriesData Base Updated把证书和CA证书传到node2上去[root@node1 ~]# scp master.crt node2:/usr/local/mysql/ssl/[root@node1 ~]# scp /etc/pki/CA/cacert.pem node2:/usr/local/mysql/ssl
3）为client1创建证书申请,并由CA签发证书
[root@client1 ~]# mkdir /usr/local/mysql/ssl[root@client1 ~]# cd /usr/local/mysql/ssl[root@client1 ssl]# (umask 077;openssl genrsa -out slave.key 2048)[root@client1 ssl]# openssl req -new -key slave.key -out slave.csr -days 365You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or a DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [GB]:CNState or Province Name (full name) [Berkshire]:beijingLocality Name (eg, city) [Newbury]:beijingOrganization Name (eg, company) [My Company Ltd]:topsageOrganizational Unit Name (eg, section) []:techCommon Name (eg, your name or your server's hostname) []:client1.example.comEmail Address []:root@client1.example.comPlease enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []:[root@client1 ssl]# scp slave.csr 192.168.1.166:/root/#在node1上为client1的slave.csr 签发证书[root@node1 ~]# openssl ca -in slave.csr -out slave.crt -days 365Using configuration from /etc/pki/tls/openssl.cnfCheck that the request matches the signatureSignature okCertificate Details: Serial Number: 2 (0x2) Validity Not Before: Mar 18 06:39:32 2014 GMT Not After : Mar 18 06:39:32 2015 GMT Subject: countryName = CN stateOrProvinceName = beijing organizationName = topsage organizationalUnitName = tech commonName = client1.example.com emailAddress = root@client1.example.com X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Comment: OpenSSL Generated Certificate X509v3 Subject Key Identifier: 10:6E:00:1E:3E:91:A7:DB:9B:C5:27:AA:07:4B:A5:D1:9E:7A:A9:8C X509v3 Authority Key Identifier: keyid:8A:88:0D:B9:67:72:47:29:51:5C:A9:CA:E6:B3:F2:B5:50:4C:A6:4ACertificate is to be certified until Mar 18 06:39:32 2015 GMT (365 days)Sign the certificate? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]yWrite out database with 1 new entriesData Base Updated[root@node1 ~]# scp /etc/pki/CA/cacert.pem 172.16.0.10:/usr/local/mysql/ssl[root@node1 ~]# scp slave.crt 172.16.0.10:/usr/local/mysql/ssl
4）主服务器配置node2
[root@node2 ~]#cd /usr/local/mysql[root@node2 mysql]# chown -R mysql.mysql ssl/[root@node2 mysql]#vim /etc/my.cnfsslssl_ca=/usr/local/mysql/ssl/cacert.pemssl_cert=/usr/local/mysql/ssl/master.crtssl_key=/usr/local/mysql/ssl/master.keymysql> show 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_key | /usr/local/mysql/ssl/master.key |+---------------+---------------------------------+mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000004 | 107 | | |+------------------+----------+--------------+------------------+:mysql> grant replication client,replication slave on *.* to slave@'172.16.0.10' identified by 'mypass' require ssl;mysql> flush privileges;
5）从服务器配置client1
[root@client1 ~]# chown mysql.mysql -R /usr/local/mysql/ssl[root@client1 ~]# vim /etc/my.cnfskip_slave_start=1read_only=1sslssl_ca=/usr/local/mysql/ssl/cacert.pemssl_cert=/usr/local/mysql/ssl/slave.crtssl_key=/usr/local/mysql/ssl/slave.key[root@client1 ~]# /etc/init.d/mysqld restartmysql> show variables like '%ssl%';mysql> show 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/slave.crt || ssl_cipher | || ssl_key | /usr/local/mysql/ssl/slave.key |+---------------+---------------------------------+测试ssl用户[root@client1 ~]#mysql -uslave -pmypass -h172.16.0.22 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.keymysql> change master to master_host='172.16.0.22', -> master_user='slave', -> master_password='mypass', -> master_log_file='mysql-bin.000004', -> master_log_pos=365, -> 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';mysql> start slave；mysql> show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
进行测试：
在node1 上创建一个数据库ssl_test;
mysql>create database ssl_test;
在node2 和client1 上查看，如果能查看到ssl_test 说明配置成功。
4.半同步复制
半同步：在数据库更改操作执行前，确保更改操作至少被写入一台slave磁盘中，意味着着对于每一个连接，最多只有一个事务会由于master崩溃丢失。主要是保证数据完整性，防止事务的丢失。
检查半同步的插件[root@node1 ~]# ls /usr/local/mysql/lib/pluginsemisync_master.so #master 同步插件semisync_slave.so #slave 同步插件在master（node1） 上安装插件mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';mysql>set global rpl_semi_sync_master_enabled=1;mysql> set global rpl_semi_sync_master_timeout=3000;#mysql> show variables like '%semi%';+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 3000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_no_slave | ON
rpl_semi_sync_master_timeout ：毫秒 主库等待半同步复制信息返回的超时间隔
rpl_semi_sync_master_wait_no_slave：是否允许master 每个事物提交后都要等待slave 的receipt信号。默认为on ，每一个事务都会等待，如果slave当掉后，当slave追赶上master的日志时，可以自动的切换为半同步方式，如果为off,则 slave追赶上后，也不会采用半同步的。
在slave（node2）上安装插件mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';mysql> set global rpl_semi_sync_slave_enabled=1;mysql> show variables like '%semi%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled | ON || rpl_semi_sync_slave_trace_level | 32 |+---------------------------------+-------+mysql>stop slave；mysql>start slave;
测试：在node1上删除ssl_test,node2和node3都进行了删除。
然后把参数加到配置文件中去。node1：/etc/my.cnf[mysqld]rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout=3000node2:/etc/my.cnf[mysqld]rpl_semi_sync_slave_enabled=1
以上一个mysql的级联配置就完成了，涉及到了mysql的ssl，以及半同步相关知识。欢迎指正