基于半同步，ssl的mysql级联复制

基于半同步，ssl的mysql级联复制今天闲来无事做了一个mysql级联复制的实验拓扑如下：操作系统：centos6.4（64bit）数据库：mysql-5.5.35主服务器：node1.example

基于半同步，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/data innodb_file_per_table=1 log-bin=/mydata/log/mysqlbin [root@node1 mysql]# chkconfig --add mysqld [root@node1 mysql]#/etc/init.d/mysqld start修改server-id： [root@node1 ~]# vim /etc/my.cnf [mysqld] server-id=10 [root@node1 ~]# /etc/init.d/mysqld restart 登录mysql mysql>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=20 skip_slave_start=1 log_slave_update=1 read_only=1 relay_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 -p mysql>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

。

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 365 You are about to be asked to enter information that will be incorporated into 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 blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:topsage Organizational Unit Name (eg, section) []:tech Common Name (eg, your name or your server's hostname) []:node1.example.com Email 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 365 You are about to be asked to enter information that will be incorporated into 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 blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:topsage Organizational Unit Name (eg, section) []:tech Common Name (eg, your name or your server's hostname) []:node2.example.com Email Address []:root@node2.example.com Please enter the following 'extra' attributes to be sent with your certificate request A 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 365 Using configuration from /etc/pki/tls/openssl.cnf Check that the request matches the signature Signature ok Certificate 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:4A Certificate is to be certified until Mar 18 06:26:52 2015 GMT (365 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Data 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/ssl3）为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 365 You are about to be asked to enter information that will be incorporated into 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 blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [GB]:CN State or Province Name (full name) [Berkshire]:beijing Locality Name (eg, city) [Newbury]:beijing Organization Name (eg, company) [My Company Ltd]:topsage Organizational Unit Name (eg, section) []:tech Common Name (eg, your name or your server's hostname) []:client1.example.com Email Address []:root@client1.example.com Please enter the following 'extra' attributes to be sent with your certificate request A 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 365 Using configuration from /etc/pki/tls/openssl.cnf Check that the request matches the signature Signature ok Certificate 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:4A Certificate is to be certified until Mar 18 06:39:32 2015 GMT (365 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Data 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/ssl4）主服务器配置node2[root@node2 ~]#cd /usr/local/mysql [root@node2 mysql]# chown -R mysql.mysql ssl/ [root@node2 mysql]#vim /etc/my.cnf ssl ssl_ca=/usr/local/mysql/ssl/cacert.pem ssl_cert=/usr/local/mysql/ssl/master.crt ssl_key=/usr/local/mysql/ssl/master.key 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/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.cnf skip_slave_start=1 read_only=1 ssl 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@client1 ~]# /etc/init.d/mysqld restart mysql> 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.key mysql> 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.半同步复制