基于半同步，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/datainnodb_file_per_table=1log-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登录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
。
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 []:[email protected][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 []:[email protected] 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)ValidityNot Before: Mar 18 06:26:52 2014 GMTNot After : Mar 18 06:26:52 2015 GMTSubject:countryName= CNstateOrProvinceName= beijingorganizationName= topsageorganizationalUnitName = techcommonName= node2.example.comemailAddress= [email protected] extensions:X509v3 Basic Constraints:CA:FALSENetscape Comment:OpenSSL Generated CertificateX509v3 Subject Key Identifier:C4:D8:F2:82:A2:52:CC:16:54:B8:79:74:3A:9A:E9:15:96:89:59:2EX509v3 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/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 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 []:[email protected] 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)ValidityNot Before: Mar 18 06:39:32 2014 GMTNot After : Mar 18 06:39:32 2015 GMTSubject:countryName= CNstateOrProvinceName= beijingorganizationName= topsageorganizationalUnitName = techcommonName= client1.example.comemailAddress= [email protected] extensions:X509v3 Basic Constraints:CA:FALSENetscape Comment:OpenSSL Generated CertificateX509v3 Subject Key Identifier:10:6E:00:1E:3E:91:A7:DB:9B:C5:27:AA:07:4B:A5:D1:9E:7A:A9:8CX509v3 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本文来源gao($daima.com搞@代@#码(网/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.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.半同步复制
转载请注明原文链接：基于半同步，ssl的mysql级联复制
