#注意：主从服务器数据库版本须一致：主从服务器时间须同步
环境：
centos6.6_x86_64
mysql5.1
master_ip:192.168.122.20
slave_ip:192.168.122.207
主服务器配置
[root@master mysql]# mkdir /mydata/binlogs
[root@master mysql]# chown -R mysql.mysql /mydata
[root@master mysql]# vim /etc/my.cnf
datadir = /mydata/data
log-bin=/mydata/binlogs/master-bin
binlog_format=mixed #二进制日志格式
server-id = 1 #主服务器与从服务器不可一致
[root@master mysql]#service mysqld start
授权从服务器复制账号(在主上做)
mysql>grant replication slave,replication client on *.* to 'slave'@'192.168.122.%' identified by '123456'；
mysql>flush privileges;
查看二进制日志位置
mysql> show master status \G
从服务器配置
[root@slave mysql]# mkdir /mydata/relaylogs
[root@slave mysql]# chown -R mysql.mysql /mydata
[root@slave mysql]# vim /etc/mysql/my.cnf
datadir = /mydata/data
#log-bin=mysql-bin
#binlog_format=mixed
#log-slave-updates = 1
#如果从服务器为其他从服务器主服务器须启用以上三项
server-id = 2 #不可与主服务器一致
relay-log=/mydata/relaylogs/relay-bin #中继日志位置
read_only = 1 #设置为只读
[root@slave mysql]#service mysqld start
从服务器连接主服务器(从上做)
mysql>change master to master_host='master_ip',master_user='先前在master授权用户',master_password='123456',master_log_file='master_banary_log',master_log_pos=336(这个不带引号);
查看从服务器状态
mysql> show slave status \G
主从复制测试
主服务器添加数据
mysql>create database hello;
mysql>create table test(id int(2));
mysql>insert into test values(1);
mysql>show master status \G
查看从服务器数据及状态
mysql>select * from hell.test;
mysql>show slave status\G
#与主服务器一致，主从复制配置完成
实现SSL安全传输
检查SSL状态
mysql> show variables like '%ssl%';
配置主服务器为CA
[root@master ~]# cd /etc/pki/CA
[root@master CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
[root@master CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
[root@master CA]# touch {index.txt,serial}
[root@master CA]# echo 01 > serial
主服务器生成证书
[root@master CA]# mkdir /etc/mysql/ssl
[root@master CA]# cd /etc/mysql/ssl
[root@master ssl]# (umask 077;openssl genrsa -out master.key 2048)
[root@master ssl]# openssl req -new -key master.key -out master.csr
[root@master ssl]# openssl ca -in master.csr -out master.crt -days 3650
从服务器生成证书请求
[root@slave ssl]# (umask 077;openssl genrsa -out slave.key 2048)
[root@slave ssl]# openssl req -new -key slave.key -out slave.csr
为从服务器签署证书
[root@slave ssl]# scp slave.csr master:/tmp
slave.csr 100% 1013 1.0KB/s 00:00
[root@master ssl]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 3650
[root@master ssl]# scp /tmp/slave.crt slave:/etc/mysql/ssl
slave.crt 100% 4520 4.4KB/s 00:00
将CA证书拷到各服务器
[root@master ssl]# cp /etc/pki/CA/cacert.pem ./
[root@master ssl]# scp /etc/pki/CA/cacert.pem slave:/etc/mysql/ssl
cacert.pem
***权限
[root@master ssl]# chown -R mysql.mysql ./
[root@master ssl]# ll
total 20
-rw-r--r-- 1 mysql mysql 1403 Jun 19 14:50 cacert.pem
-rw-r--r-- 1 mysql mysql 4596 Jun 19 14:27 master.crt
-rw-r--r-- 1 mysql mysql 1045 Jun 19 14:24 master.csr
-rw------- 1 mysql mysql 1679 Jun 19 14:22 master.key
#主从服务器都执行以上操作
主服务器配置SSL
[root@master ~]# vim /etc/my.cnf
[mysqld] #在此段中添加如下配置
ssl #开启SSL功能
ssl-ca = /var/lib/mysql/ssl/cacert.pem #指定CA文件位置
ssl-cert = /var/lib/mysql/ssl/master.crt #指定证书文件位置
ssl-key = /var/lib/mysql/ssl/master.key #指定密钥所在位置
[root@master ~]# service mysqld restart
主服务器配置
再次查看SSL状态
mysql>show variables like '%ssl%';
设授权复制用户只能通过SSL复制
mysql>grant replication slave,replication client on *.* to 'slave'@'192.168.122.%' identified by '123456' require ssl;
从服务器配置
从服务器登陆测试
[root@slave ssl]#mysql -uslave -p123456 -h192.168.122.207 --ssl-ca=/var/lib/mysql/ssl/cacert.pem --ssl-cert=/var/lib/mysql/ssl/slave.crt --ssl-key=/var/lib/mysql/ssl/slave.key
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 7
Current database:
Current user: slave@node2
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.73-log Source distribution
Protocol version: 10
由此可见此次连接是基于SSL加密实现的，
开始连接主服务器并开启复制(从上)
mysql> change master to master_host='192.168.122.207',master_user='slave',master_password='123456',master_log_file='master-bin.000004',master_log_pos=367,master_ssl=1,master_ssl_ca='/var/lib/mysql/ssl/cacert.pem',master_ssl_cert='/var/lib/mysql/ssl/slave.crt',master_ssl_key='/var/lib/mysql/ssl/slave.key';
查看从服务器状态
mysql>show slave status\G
mysql>show tables in db;
测试和前面的一样
