基于半同步，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/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 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/ssl
4）主服务器配置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.半同步复制
