MYSQL主从复制/SSL加密

最新推荐文章于 2022-09-11 12:56:27 发布

weixin_34174105

最新推荐文章于 2022-09-11 12:56:27 发布

阅读量102

点赞数

文章标签：数据库运维开发工具

原文链接：http://blog.51cto.com/xinghuiru/957696

版权

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
说明是加密连接了