basedir = /usr/local/mysql
datadir= /usr/local/mysql/data
port= 3306server_id= 2socket= /tmp/mysql.sock
skip_slave_start= 1read_only= 1relay_log=relay_log
relay_log_index= relay_log.index
重启mysqld服务
[root@mysql_slave ~]# service mysqld restart
3.5.查看master服务器的二进制日志及二进制日志事件位置
[root@mysql_master ~]# mysql -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
File：表示从此日志开始复制
Position：表示从这个事件开始复制
3.6在Slave服务器上同步Master服务器上面的数据
mysql> CHANGE MASTER TO MASTER_HOST='mysql_master',MASTER_USER='eivll0m',MASTER_PASSWORD='password',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=120,MASTER_CONNECT_RETRY=60;
3.7启动slave服务器的复制线程并查看状态
mysql>START SLAVE;
mysql>SHOW SLAVE STATUS\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host: mysql_master
Master_User: eivll0m
Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos:120Relay_Log_File: relay_log.000002Relay_Log_Pos:283Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0Last_Error:
Skip_Counter:0Exec_Master_Log_Pos:120Relay_Log_Space:450Until_Condition: None
Until_Log_File:
Until_Log_Pos:0Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:0Last_IO_Error:
Last_SQL_Errno:0Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:1Master_UUID: 988cd54d-c1a7-11e3-b1a5-000c29c976ef
Master_Info_File:/usr/local/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waitingfor the slave I/O thread to update it
Master_Retry_Count:86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:0
1 row in set (0.00 sec)
3.8在slave服务器查看启动的线程
[root@mysql_slave ~]# mysql -e 'SHOW PROCESSLIST;'
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 5 | system user | | NULL | Connect | 102 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 102 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3.9验证：在saster服务器创建数据库，在slave服务器上验证是否复制过去
[root@mysql_master ~]# mysql -e 'CREATE DATABASE eivll0m;'[root@mysql_master~]# mysql -e 'SHOW DATABASES;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| eivll0m |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@mysql_slave ~]# mysql -e 'SHOW DATABASES;' #可以看到eimll0m数据库已经复制过去
+--------------------+
| Database |
+--------------------+
| information_schema |
| eivll0m |
| mysql |
| performance_schema |
| test |
+--------------------+
3.10在主从服务器查看二进制日志事件位置是否更新
[root@mysql_master ~]# mysql -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 223 | | | |
+------------------+----------+--------------+------------------+-------------------+
[root@mysql_slave ~]# mysql -e 'SHOW SLAVE STATUS\G;' | grep "Read_Master_Log_Pos"Read_Master_Log_Pos:223
由此可见，已经更新。
4.配置基于SSL的复制
由于Mysql的主从复制是明文传送的，如果在生产环境中跨网络使用主从还是明文传送，就无法保证数据的传输安全性，为了解决这一问题，我们需要加密进行传送，也就是基于SSL的加密方法进行传输数据。
4.1在master服务器搭建CA服务器
[root@mysql_master ~]# cd /etc/pki/CA/[root@mysql_master CA]# (umask077;openssl genrsa -out private/cakey.pem 2048)[root@mysql_master 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 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 (2letter code) [XX]:CN
State or Province Name (full name) []:BeiJing
Locality Name (eg, city) [Default City]:ChaoYang
Organization Name (eg, company) [Default Company Ltd]:eivll0m
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:mysql_master
Email Address []:master@
[root@mysql_master CA]#touchindex.txt
[root@mysql_master CA]#echo 01 > serial
4.2为master创建证书申请并由CA服务器签发证书
[root@mysql_master ~]# mkdir /usr/local/mysql/ssl
[root@mysql_master ssl]# cd/usr/local/mysql/ssl
[root@mysql_master ssl]# (umask077;openssl genrsa -out master.key 2048)
[root@mysql_master ssl]# openssl req-new -key master.key -out master.csr -days 365You 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 (2letter code) [XX]:CN
State or Province Name (full name) []:BeiJing
Locality Name (eg, city) [Default City]:ChaoYang
Organization Name (eg, company) [Default Company Ltd]:eivll0m
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:mysql_master
Email Address []:master@
Please enter the following'extra'attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@mysql_master ssl]# openssl ca -in master.csr -out master.crt -days 365Using configuration from/etc/pki/tls/f
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number:1 (0x1)
Validity
Not Before: Apr22 15:52:49 GMT
Not After : Apr22 15:52:49 GMT
Subject:
countryName=CN
stateOrProvinceName=BeiJing
organizationName=eivll0m
organizationalUnitName=Tech
commonName=mysql_master
emailAddress=master@
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
A7:4D:33:91:61:CD:92:5E:72:2A:8E:A6:56:15:6A:AB:FA:22:20:98X509v3 Authority Key Identifier:
keyid:0F:79:D1:B8:1C:63:4B:91:A6:17:9F:B4:6D:A3:C7:96:AA:29:5E:48Certificate is to be certifieduntil Apr 22 15:52:49 GMT (365days)
Sign the certificate? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with1new entries
Data Base Updated
4.3为slave服务器创建证书申请
[root@mysql_slave ~]# mkdir /usr/local/mysql/ssl
[root@mysql_slave~]# cd /usr/local/mysql/ssl
[root@mysql_slave~]# (umask 077;openssl genrsa -out slave.key 2048)
[root@mysql_slave ssl]# openssl req-new -key slave.key -out slave.csr -days 365You 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 (2letter code) [XX]:CN
State or Province Name (full name) []:BeiJing
Locality Name (eg, city) [Default City]:ChaoYang
Organization Name (eg, company) [Default Company Ltd]:eivll0m
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:mysql_slave
Email Address []:
Please enter the following'extra'attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
4.4为slave服务器签署证书
[root@mysql_slave ssl]# scp slave.csr mysql_master:/tmp/[root@mysql_master ssl]# openssl ca-in /tmp/slave.csr -out /tmp/slave.crt -days 365Using configuration from/etc/pki/tls/f
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number:2 (0x2)
Validity
Not Before: Apr22 15:57:52 GMT
Not After : Apr22 15:57:52 GMT
Subject:
countryName=CN
stateOrProvinceName=BeiJing
organizationName=eivll0m
organizationalUnitName=Tech
commonName=mysql_slave
emailAddress=slave@
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
B9:4B:EA:28:0E:9E:4B:84:A6:9A:4E:45:3B:DF:B3:B9:E3:E9:ED:55X509v3 Authority Key Identifier:
keyid:0F:79:D1:B8:1C:63:4B:91:A6:17:9F:B4:6D:A3:C7:96:AA:29:5E:48Certificate is to be certifieduntil Apr 22 15:57:52 GMT (365days)
Sign the certificate? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with1new entries
Data Base Updated
在mastet服务器上将签署好证书申请拷贝到Slave服务器
[root@mysql_master ~]# scp /tmp/slave.crt mysql_slave:/usr/local/mysql/ssl/
4.5将CA证书拷贝到slave服务器与saster相应目录
[root@mysql_master ~]# scp /etc/pki/CA/cacert.pem mysql_slave:/usr/local/mysql/ssl/[root@mysql_master~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
4.6修改master与slave服务器证书属主、属组为"mysql"用户
# chown -R mysql.mysql /usr/local/mysql/ssl
# ll/usr/local/mysql/ssl/
-rw-r--r-- 1 mysql mysql 1415 Sep 20 20:57cacert.pem-rw-r--r-- 1 mysql mysql 4600 Sep 20 20:22master.crt-rw-r--r-- 1 mysql mysql 1054 Sep 20 20:20master.csr-rw------- 1 mysql mysql 1675 Sep 20 20:17 master.key
4.7在master与slave服务器编辑f开启SSL加密功能
在master服务器的f文件中[mysqld]下添加如下参数
ssl #开启SSL功能
ssl_ca= /usr/local/mysql/ssl/cacert.pem #指定CA文件位置
ssl_cert= /usr/local/mysql/ssl/master.crt #指定证书文件位置
ssl_key= /usr/local/mysql/ssl/master.key #指定密钥所在位置
在slave服务器的f文件中[mysqld]下添加如下参数
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
4.8在master服务器查看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/master.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
9 rows in set (0.12 sec)
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* to 'slave'@'172.16.%.%' IDENTIFIED BY 'passwd'REQUIRE SSL;
Query OK,0 rows affected (0.00sec)
mysql>FLUSH PRIVILEGES;
Query OK,0 rows affected (0.02 sec)
4.9查看master服务器二进制日志文件和事件位置
mysql>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 919 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.09 sec)
4.10在slave上测试使用加密用户指定密钥连接master服务器(如下测试成功)
root@mysql_slave ssl]# mysql -uslave -ppasswd -h 172.16.10.72 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 2Server version:5.6.17-log Source distribution
Copyright (c)2000, , Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.
mysql>
4.11查看slave服务器SSL是否开启并连接master服务器
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_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
mysql> change master to master_host='172.16.10.72',master_user='slave',master_password='passwd',master_log_file='mysql-bin.000007',master_log_pos=919,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; #启动IO线程
mysql> show slave status\G; ##查看slave状态
4.12查看slave服务器状态
[root@mysql_slave ~]# cd /usr/local/mysql/ssl/[root@mysql_slave ssl]# mysql-e 'show slave status\G;'
*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host:172.16.10.72Master_User: slave
Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 919Relay_Log_File: relay_log.000002Relay_Log_Pos:572Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0Last_Error:
Skip_Counter:0Exec_Master_Log_Pos: 919Relay_Log_Space:739Until_Condition: None
Until_Log_File:
Until_Log_Pos:0Master_SSL_Allowed: Yes
Master_SSL_CA_File:/usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert:/usr/local/mysql/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key:/usr/local/mysql/ssl/slave.key
Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:0Last_IO_Error:
Last_SQL_Errno:0Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:1Master_UUID: 988cd54d-c1a7-11e3-b1a5-000c29c976ef
Master_Info_File:/usr/local/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waitingfor the slave I/O thread to update it
Master_Retry_Count:86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:0
4.13创建数据库进行验证
[root@mysql_master ssl]# mysql -e 'create database mydata'[root@mysql_master ssl]# mysql-e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| eivll0m |
| mydata |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@mysql_slave data]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| eivll0m |
| mydata |
| mysql |
| performance_schema |
| test |
+--------------------+
复制成功！
