Based on semi-synchronous, ssl mysql cascade replication today nothing to do a mysql cascade replication experiment topology is as follows: Operating System: centos6.4 (64bit) Database: mysql-5.5.35 master server: node1.example
Based on semi-synchronous, ssl mysql cascade replication today nothing to do a mysql cascade replication experiment topology is as follows: Operating System: centos6.4 (64bit) Database: mysql-5.5.35 master server: node1.example
Semi-synchronous, ssl-based mysql cascade Replication
Today, I did nothing to do with a mysql cascade replication experiment topology as follows:
Operating System: centos6.4 (64bit)
Database: mysql-5.5.35
Master server: node1.example.com (192.168.1.166)
Ssl master server: node2.example.com (192.168.1.167 172.16.0.22)
Ssl slave server: client1.example.com (172.16.0.10)
Note that the time of the three servers must be synchronized.
1. Install [root @ node1 ~] in mysql # Fdisk/dev/sdb # create a new partition/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 # store data [root @ node1 ~] # Lvcreate-L 1G-n lv_log vg_data # store binary logs # set the specific size by yourself [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-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 \-keys = 1 \-keys = 1 \-keys \- 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 note: if compilation fails, make clean and 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 # add 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
Modify server-id: [root @ node1 ~] # Vim/etc/my. cnf [mysqld] server-id = 10 [root @ node1 ~] #/Etc/init. d/mysqld restart log on to mysqlmysql> grant replication client, replication slave on *. * to replicationuser @ '2017. 168.1.167 'identified by 'mypass'; mysql> flush privileges; mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000002 | 182 | + usage + -------- + -------------- + ------------------ +
Configure node2 on the slave server
Modify server-id [root @ node1 ~] # Vim/etc/my. cnf [mysqld] server-id = 20skip_slave_start = 1log_slave_update = 1read_only = 1relay_log =/mydata/log-bin =/mydata/log/mysql-bin [root @ node1 ~] #/Etc/init. d/mysqld restart [root @ node1 ~] # Mysql-ureplicationuser-pmypass-h192.168.1.166 # Verification Successful [root @ node1 ~] # Mysql-uroot-pmysql> change master to master_host = '2017. 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) Use node1 as the CA Server
[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 inemediatedinto 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) create a Certificate Application for node2 and issue the certificate by the 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 inemediatedinto 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 blankF Or 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.examp Le. 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 is the master of node2 on node1. csr issuing certificate [root @ node1 ~] # Openssl ca-in master. csr-out master. crt-days 365 Using 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 = node 2. example. comemailAddress = root@node2.example.comX509v3 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: 4 ACertificate is to be certified until Mar 18 06:26:52 2015 GMT (365 d Ays) Sign the certificate? [Y/n]: y1 out of 1 certificate requests certified, commit? [Y/n] yWrite out database with 1 new entriesData Base Updated transfers the certificate and CA certificate to node2 [root @ node1 ~] # Scp master. crt node2:/usr/local/mysql/ssl/[root @ node1 ~] # Scp/etc/pki/CA/cacert. pem node2:/usr/local/mysql/ssl3) create a Certificate Application for client1, And the CA issues the certificate [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 inemediatedinto 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]: 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.exa Mple. 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/# slave of client1 on node1. csr issuing certificate [root @ node1 ~] # Openssl ca-in slave. csr-out slave. crt-days 365 Using 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 = client 1. example. comemailAddress = root@client1.example.comX509v3 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: 4 ACertificate 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/ssl4) Configure node2 on the master server [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 %'; + --------------- + variant + | 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 | + --------------- + response + mysql> show master status; + -------------------- + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | bytes | + ------------------ + -------- -- + -------------- + ------------------ + | Mysql-bin.000004 | 107 | + -------------------- + ---------- + ---------------- +: mysql> grant replication client, replication slave on *. * to slave @ '2017. 16.0.10 'identified by 'mypass' require ssl; mysql> flush privileges; 5) Configure client1 from the server [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 % '; + --------------- + certificate + | Variable_name | Value | + --------------- + certificate + | 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/m Ysql/ssl/slave. key | + --------------- + --------------------------------- + test ssl users [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 = '2017. 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
Test:
Create a database ssl_test on node1;
Mysql> create database ssl_test;
View on node2 and client1. If you can view ssl_test, the configuration is successful.
4. semi-synchronous Replication
