MYSQL主从复制/SSL加密

原创

不凡一夏 2012-08-07 21:17:15 博主文章分类：Linux集群 ©著作权

©著作权归作者所有：来自51CTO博客作者不凡一夏的原创作品，请联系作者获取转载授权，否则将追究法律责任

MYSQL主从复制原理及拓扑结构：

准备工作：
1)配置好主机名和IP地址
node1.forman.com 172.16.220.11
node2.forman.com 172.16.220.12
2)时间同步和双机互连

Master:
#hwclock -s
#vim /etc/hosts
添加如下：
172.16.220.11 node1.forman.com node1
172.16.220.12 node2.forman.com node2
# ssh-keygen -t rsa
# ssh-copy-id -i .ssh/id_rsa.pub root@node2
# ssh node2 'ifconfig'
Slave #hwclock -s # vim /etc/hosts
添加如下：
172.16.220.11 node1.forman.com node1
172.16.220.12 node2.forman.com node2
# ssh-keygen -t rsa
# ssh-copy-id -i .ssh/id_rsa.pub root@node1
# ssh node1 'ifconfig'

一、准备两台mysql服务器

Master Slave: 
#fdisk /dev/sda  
....  
1个大小为20G 类型8e的分区，为sda5  
....  
#partprobe /dev/sda  
#pvcreate /dev/sda5  
#vgcreate myvg /dev/sda5  
#lvcreate -L 10G -n mydata myvg  
#mke2fs -j /dev/myvg/mydata  
#mkdir /data/mydata -pv  
#vim /etc/fstab  
/dev/myvg/mydata        /data/mydata            ext3    defaults        0 0  
#mount -a  
#useradd -r mysql  
#chown -R mysql:mysql /data/mydata  
安装mysql  
# tar xf mysql-5.5.22-linux2.6-i686.tar.gz -C /usr/local  
#cd /usr/local  
# ln -sv mysql-5.5.22-linux2.6-i686 mysql  
# cd mysql  
# chown -R mysql:mysql .  
# scripts/mysql_install_db --user=mysql --datadir=/data/mydata  
 
#chown -R root .  
# cp support-files/my-large.cnf /etc/my.cnf  
# vim /etc/my.cnf  
[mysqld]  
thread_concurrency = 2 
datadir = /data/mydata  
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld  
# chmod +x /etc/rc.d/init.d/mysqld  
# chkconfig --add mysqld  
#service mysqld start

二、配置主、从服务器

1 、配置Master

编辑mysql的主配置文件（二进制文件默认已启用；server-id =1 ）
创建具有复制权限的用户  
#/usr/local/mysql/bin/mysql  
mysql>grant replication slave,replication client on *.* to repluser@'172.16.220.%' identified by 'redhat';  //这里授权的主机越少越好  
mysql> flush privileges;  
mysql> show master status;  
+------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| mysql-bin.000001 |      356 |              |                  |  
+------------------+----------+--------------+------------------+

2、配置Slave

2.1 启用中继日志（关闭二进制日志）设置server-id（与主服务器一定不能样）

#vim /etc/my.cnf  
修改server-id  = 111           //server-id 中的连接线可以是"-"也可以是"_"  
添加relay-log=mysql-relay  
注释掉log-bin=mysql-bin  
 
# service mysqld restart  
#/usr/local/mysql/bin/mysql  
 
验证刚才的设置（关闭了log_bin 启用了relay_log)  
mysql> show global variables like 'relay_log';  
+---------------+-------------+  
| Variable_name | Value       |  
+---------------+-------------+  
| relay_log     | mysql-relay |  
+---------------+-------------+  
mysql> show global variables like 'log_bin';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| log_bin       | OFF   |  
+---------------+-------+   
mysql> show global variables like 'server_id';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| server_id     | 111   |  
+---------------+-------+

2.2 启动从服务，并指定主服务器参数

mysql>help change master to;  
以下的这些参数，在mysql 5.5版本以前是可以直接写进配置文件my.cnf中的；但是msyql 5.5版本之后就不支持了，只有再命令行配置才生效；  
使用的命令是 change master to  
 
option:  
  MASTER_HOST = 'host_name' 
  | MASTER_USER = 'user_name' 
  | MASTER_PASSWORD = 'password' 
  | MASTER_PORT = port_num 
  | MASTER_LOG_FILE = 'master_log_name' 
  | MASTER_LOG_POS = master_log_pos 
 
设置如上的参数内容：  
mysql> change master to master_host='172.16.220.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000001',master_log_pos=356;  
可以测试这条命令配置的结果  
#cat /data/mydata/master.info  
18  
mysql-bin.000001  
356  
172.16.220.11  
repluser  
redhat  
3306  
60  
0  
.......等内容（每当复制一次内容时，这个文件的内容都会改变，从服务关机，再启动时，会自动加载这个文件的）  
 
mysql> start slave; (这条语句，要在上条语句执行后就执行，不要退出mysql)  
mysql> show slave status\G  
确保有以下内容：  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes

2.3 阻止从服务器的写操作

mysql> set global read_only=1;  
mysql> show global variables like 'read_only';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| read_only     | ON    |  
+---------------+-------+  
这项限制，只是阻止了普通用户不可写，管理员依然是有权限的；若是使用flush tables with read lock，会阻止所有用户的写操作，但同时也会阻止SQL Thread的写，数据就无法同步了）

OK此时主从服务器配置完成

2.4 验证

Master:  
 
mysql> create database testdb;  
mysql> use testdb;          //创建数据库
mysql> create table t1 (NAME VARCHAR(20));  //并创建表
mysql> show tables;  
+------------------+  
| Tables_in_testdb |  
+------------------+  
| t1               |  
+------------------+  
node2:来验证一下  
mysql>show databases;  
+---------------------+  
| Database            |  
+---------------------+  
| information_schema  |  
| #mysql50#lost+found |  
| mysql               |  
| performance_schema  |  
| test                |  
| testdb              |  
+---------------------+  
mysql> use testdb;  
mysql> show tables;  
+------------------+  
| Tables_in_testdb |  
+------------------+  
| t1               |  
+------------------+

三、对主从服务器的补充扩展

（建议配置）

1、事务的安全性

即在主服务器崩溃，事务已经提交的情况下--写入二进制文件；
可以保证数据的完整性；实现事务的安全性；

Master：  
sync_binlog=1          将提交的二进制事务，写进二进制日志文件中；随时同步二进制缓存内容到二进制日志文件中去  
inonodb_flush_logs_at_trx_commit=1    在事务日志提交时，是不是将事务日志的内容刷写进去  
操作如下：
mysql> set global sync_binlog=1;  
mysql> set global innodb_flush_log_at_trx_commit=1;  
Slave：  
skip_slave_start=1     跳过从服务的自动启动；需要手动start slave才生效。

2 、若从服务挂了，那么从服务器的内容就全部丢失了，如何解决？

(使用mysqldump是不能了，用msyqldump需要mysql服务，此时mysql服务不能使用了，这个命令就无从谈起了；若使用这个命令需要重新初始化mysql，会导致数据冲突的；因此使用物理备份即可）  
使用物理备份的：冷备份或快照的方式备份主服务上的内容，而后复制过来即可。  
 
 
模拟Slave不能工作  
node2:  
#service mysqld stop  
#cd /data/mydata  
#rm -rf *  
 
node1:  
mysql>flush tables with read lock;  
mysql>show master status;               //加锁之后要观察此时的二进制文件和时间点  
+------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| mysql-bin.000001 |      924 |              |                  |  
+------------------+----------+--------------+------------------+  
 
不要退出mysql  
再打开一个终端,执行  
#lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata   //创建快照卷，这条命令执行结束后，可以在上个终端释放锁了mysql>unlock tables;  
#mount /dev/myvg/mydata-snap /mnt  
#cd /mnt  
#find . | cpio -o -H newc --quiet | gzip > /root/alldatabase.gz  
#umount /mnt  
#scp alldatabase.gz node2:/root  
 
此时又对数据库做了改变：  
mysql>use testdb;  
mysql>create table tb2(id int);  
mysql>show master status;  
------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| mysql-bin.000001 |     1014 |              |                  |  
+------------------+----------+--------------+------------------+//这些数据，在快照卷里是没有的  
 
node2:  
此时可以恢复数据了  
#gzip -d alldatabase.gz  
#cp alldatabase /data/mydata  
#cd /data/mydata  
#cpio -id < alldatabase 
#rm -rf alldatabase  
# service mysqld start  
Starting MySQL...                                          [  OK  ]  
 
此时，从服务的配置master.info都已经没了，需要重新配置  
mysql>change master to master_host='172.16.220.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000001',master_log_pos=924;//从时间点924开始同步  
mysql>start slave;  
mysql> show slave status\G  
有这么一条信息  
Read_Master_Log_Pos: 1014    和主服务的时间点是一样的  
mysql>use testdb;  
mysql>show tables;  
| Tables_in_testdb |  
+------------------+  
| tb1              |  
| tb2              |  
+------------------+       //看到，数据已经完全同步过来了

3、设置半同步

在Master和Slave的mysql命令行运行如下代码：

#Master    
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  //安装支持主服务半同步的插件  
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;               //启动主服务器的半同步功能  
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;            //超时时间  
 
#Slave    
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';    
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;    
mysql> STOP SLAVE;  
mysql> START SLAVE;  
 
在Master和Slave的my.cnf中编辑，可以长久有效：  
 
# Master    
[mysqld]    
rpl_semi_sync_master_enabled=1    
rpl_semi_sync_master_timeout=1000 # 1 second   
 
# Slave    
[mysqld]    
rpl_semi_sync_slave_enabled=1   
 
 
# 也可通过设置全局变量的方式来设置,如下：  
set global rpl_semi_sync_master_enabled=1   
# 取消加载插件  
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;  
==============================================  
 
查看从服务器上的semi_sync是否开启:  
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';  
 
查看主服务器上的semi_sync是否开启，注意clients 变为1 ，证明主从半同步复制连接成功:  
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';

4、设置mysql的复制过滤（仅同步有限的数据库，或某个数据库的某些表）

在主服务器上实现：  
在主服务器过滤：任何不涉及到数据库相关的写操作都不会被记录到二进制日志当中；  
 
binlog-do-db           //binlog-do-db表示和哪个数据库相关的写入类、修改类指令会被写入到二进制文件中去  
binlog-do-db  
 
binlog-ignore-db        //binlog-ignore-db表示忽略（黑名单），除了这个数据库之外的都复制  
 
例如，只复制testdb这个表  
node1:  
#vim /etc/my.cnf  
[mysqld]  
binlog-do-db=testdb 
#service mysqld restart  
测试即可（在主服务器的非testdb数据库中做的任何操作，在从服务上都不会有所改变）  
 
从服务器：  
replicate_do_db  
rpplicate_ignore_db  
 
replicate_do_table  
replicate_ignore_table  
 
replicate_wild_do_table  
replicate_wild_ignore_table  
 
例如  
在从服务器上只复制testdb这一个数据库：  
#vim /etc/my.cnf  
[mysqld]  
replicate_do_db=testdb 
#service mysqld restart  
建议：在从服务器上复制

四、基于SSL实现数据库复制

要求主从服务器各自都要有证书和私钥；默认情况下主从服务器的SSL功能是没有启用的，需要先启用。

1、配置Master为CA证书服务器

#vim /etc/pki/tls/openssl.cnf  
将dir             = ../../cA修改为  
dir             = /etc/pki/CA     
#cd /etc/pki/CA  
#(umask 077; openssl genrsa 1024 > private/cakey.pem)  
#openssl req -new -x509 -key private/cakey.pem -out cacert.pem  
 
Country Name (2 letter code) [GB]:CN  
State or Province Name (full name) [Berkshire]:HN  
Locality Name (eg, city) [Newbury]:ZZ  
Organization Name (eg, company) [My Company Ltd]:Forman  
Organizational Unit Name (eg, section) []:Tech  
Common Name (eg, your name or your server's hostname) []:node1.forman.com  
Email Address []:  
#mkdir newcerts certs crl  
#touch index.txt  
#echo 01 > serial

4.2 为Master上的mysql准备私钥以及颁发证书

#mkdir /usr/local/mysql/ssl  
#cd /usr/local/mysql/ssl  
#(umask 077; openssl genrsa 1024 > mysql.key)  
#openssl req -new -kdy mysql.key -out mysql.csr  
CN  
HN  
ZZ  
Forman 
Tech  
node1.forman.com  
 
#openssl ca -in mysql.csr -out mysql.crt  
#cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl  
#cd /usr/local/mysql  
#chown -R mysql:mysql ssl/

4.3 为Slave上的mysql准私钥，证书申请、在Master上为Slave签发证书

#mkdir /usr/local/mysql/ssl  
#cd /usr/local/mysql/ssl  
#(umask 077; openssl genrsa 1024 > mysql.key)  
#openssl req -new -key mysql.key -out mysql.csr  
CN  
HN  
ZZ  
Forman  
Tech  
node1.forman.com  
 
#scp ./mysql.csr node1:/root  
 
Master上为Slave签发证书  
#pwd  
/root  
#openssl ca -in mysql.csr -out mysql.crt  
#scp ./mysql.crt node2:/usr/local/mysql/ssl  
#scp /etc/pki/CA/cacert.pem node2:/usr/local/mysql/ssl  
 
保证主从服务器上/usr/lcoal/mysql/ssl目录下的文件的属主，属组为mysql  
#ll /usrl/local/mysql/ssl  
-rw-r--r-- 1 mysql mysql 1119 Aug 20 20:13 cacert.pem  
-rw-r--r-- 1 mysql mysql 3067 Aug 20 20:04 mysql.crt  
-rw-r--r-- 1 mysql mysql  643 Aug 20 20:03 mysql.csr  
-rw------- 1 mysql mysql  887 Aug 20 20:02 mysql.key

4.4 开启主从服务器mysql的SSL功能

Master:  
#vim /etc/my.cnf  
 
在[mysqld]和[mysqldump]之间添加如下内容：  
ssl  
ssl-ca=/usr/local/mysql/ssl/cacert.pem  
ssl-cert=/usr/local/mysql/ssl/mysql.crt  
ssl-key=/usr/local/mysql/ssl/mysql.key  
保存退出  
#service mysqld restart  
 
mysql> show global 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/mysql.crt  |  
| ssl_cipher    |                                 |  
| ssl_key       | /usr/local/mysql/ssl/mysql.key  |  
+---------------+---------------------------------+  
mysql> show master status;  
+------------------+----------+--------------+------------------+  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+------------------+----------+--------------+------------------+  
| mysql-bin.000003 |      107 | testdb       |                  |  
+------------------+----------+--------------+------------------+  
 
Slave:(建议不要在配置文件中指定，而是在change to命令中指定）  
#vim /etc/my.cnf  
在[mysqld]和[mysqldump]之间添加如下内容：  
ssl  
#service mysqld restart  
 
mysql>  change master to master_host='172.16.220.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000003',master_log_pos=107,master_ssl=1,master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',master_ssl_cert='/usr/local/mysql/ssl/mysql.crt',master_ssl_key='/usr/local/mysql/ssl/mysql.key';  
mysql> show global 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/mysql.crt  |  
| ssl_cipher    |                                 |  
| ssl_key       | /usr/local/mysql/ssl/mysql.key  |  
+---------------+---------------------------------+  
msyql> show slave status\G  
输出的信息有：  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes  
Master_SSL_Allowed: Yes  
说明，基于SSL的配置已经成功

4.5 验证是否使用了SSL加密

Master上添加ssl的复制用户  
mysql> grant replication slave,replication client on *.* to ssluser@'172.16.220.12' identified by 'redhat' require ssl;  
mysql> flush privileges;  
 
Slave测试  
 
# /usr/local/mysql/bin/mysql --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/mysql.crt --ssl-key=/usr/local/mysql/ssl/mysql.key -ussluser -h 172.16.220.11 -p  
Enter password  
mysql>\s  
输出信息中有这么一项  
SSL:   Cipher in use is DHE-RSA-AES256-SHA  
 
说明是加密连接了

操作的过程中也遇到了诸多的问题：

问题  
1、在2.2步骤中  
mysql>show slave status\G 时遇到这样的问题  
 Slave_IO_Running: No  
 Slave_SQL_Running: Yes  
原因：退出了mysql，而后登陆再执行的这条命令，就出现了这样的问题；不退出mysql，直接执行，就不会有问题了