MySQL基于SSL的主从复制

hawl

Mysql的复制有效的实现了数据的备份，它将两个MySQL服务器分成一主一从，主的用于读写操作，从的用于备份数据，且从的不能被写入；Master将自身的二进制文件中的事件通过网络传递到slave上，slave在自身启动一个IO进程用来接收事件，并保存到中继日志中，然后在从中继日志中读取并通过SQL进程执行保存至本地磁盘上！

说明：这里的用两个主机，实现了两个MySQL，其中一个为master主机名为node1，IP=172.16.15.1，别一个为slave主机名为node2，IP=172.16.15.2；这里使用的为编译好的二进制MySQL-5.5的软件包！

安装MySQL-5.5，实现MySQL的主从复制

Master:# tar -xf mysql-5.5.28-linux2.6-i686.tar.gz -C /usr/local/# mkdir -pv /mydata/data# groupadd mysql# useradd -g mysql -r -s /sbin/nologin -M mysql# chown -R mysql.mysql /mydata/data# cd /usr/local/# ln -sv mysql-5.5.28-linux2.6-i686.tar.gz mysql# cd mysql# chown -R root.mysql ./*# scripts/mysql_install_db --user=mysql --datadir=/mydata/data# cp support-files/mysql.server /etc/rc.d/init.d/mysqld# chkconfig --add mysqld# cp support-files/my-large.cnf /etc/my.cnf# vim /etc/protfile.d/mysql.shexport PATH=$PATH:/usr/local/mysql/bin# . /etc/profile.d/mysql.sh# vim /etc/my.cnf[mysqld]log-bin=mysql-binbinlog_format=mixedserver-id = 1datadir = /mydata/datalog-bin-index=master-bin.indexinnodb_file_per_table = 1# service mysqld start# mysql创建用户账户，用于实现master与slave之间的通信mysql> grant replication slave on *.* to 'repluser'@'172.16.15.2' identified by 'redhat';mysql> flush privileges;mysql> show master status;查看日志的起始位置+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      338 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql>slave：
# tar -xf mysql-5.5.28-linux2.6-i686.tar.gz -C /usr/local/# mkdir -pv /mydata/data# groupadd mysql# useradd -g mysql -r -s /sbin/nologin -M mysql# chown -R mysql.mysql /mydata/data# cd /usr/local/# ln -sv mysql-5.5.28-linux2.6-i686.tar.gz mysql# cd mysql# chown -R root.mysql ./*# scripts/mysql_install_db --user=mysql --datadir=/mydata/data# cp support-files/mysql.server /etc/rc.d/init.d/mysqld# chkconfig --add mysqld# cp support-files/my-large.cnf /etc/my.cnf# vim /etc/protfile.d/mysql.shexport PATH=$PATH:/usr/local/mysql/bin# . /etc/profile.d/mysql.sh# vim /etc/my.cnf[mysqld]Lrelay-log = relay-logbinlog_format=mixedserver-id = 11datadir = /mydata/dataread-only = ONlog-bin-index=slave-bin.indexinnodb_file_per_table = 1# service mysqld start# mysql连接Mastermysql> change master to master_host='172.16.15.1',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000001',master_log_pos=338;mysql> start slave;mysql> show global variables like 'read%';查看写权限是否关闭这样就配置成功了mysql服务器之间的主从关系，当在master创建一个数据库，在slave也能显示！基于SSL会话实现，实现主从服务器安全复制：在Master上创建CA# cd /etc/pki/CA# (umask 077; openssl genrsa -out private/cakey.pem 2048)# openssl req -new -x509 -key private/cakey.pem -out cacert.pem# mkdir certs newcerts crl# touch index.txt# echo 01 > serialCA为Master签署证书：# cd /usr/local/mysql/mysql_ssl# (umask 077; openssl genrsa -out mysql.key 2048)# openssl req -new -key mysql.key -out mysql.csr# openssl ca -in mysql.csr -out mysql.crt -days 365Slave创建证书:# cd /usr/local/mysql/mysql_ssl# (umask 077; openssl genrsa -out mysql.key 2048)# openssl req -new -key mysql.key -out slave_mysql.csrSlave证书请求发送给CA，请求签署:# scp mysql.csr 172.16.15.1:/rootCA为Slave签署证书：# openssl ca -in slave_mysql.csr -out slave_mysql.crt -days 365# scp slave_msyql.crt 172.16.15.2:/usr/local/mysql/mysql_ssl# scp /etc/pki/CA/cacert.pem 172.16.15.2:/etc/pki/CA配置MySQL，启动SSL功能：Master:
# vim /etc/my.cnf[mysqld]log-bin=mysql-binbinlog_format=mixedserver-id = 1datadir = /mydata/datalog-bin-index=master-bin.indexinnodb_file_per_table = 1sslssl_ca = /etc/pki/CA/cacert.pemssl_cert = /usr/local/mysql/mysql_ssl/mysql.crtssl_key = /usr/local/mysql/mysql_ssl/mysql.key# service mysqld start# msyqlmysql> grant replication slave on *.* to 'repluser'@'172.16.15.2' identified by'redhat'require ssl;创建用户，并实现必须建立ssl才能登录！mysql> show master status;查看日志的起始位置+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      338 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql>Slave:# vim /etc/my.cnf[mysqld]Lrelay-log = relay-logbinlog_format=mixedserver-id = 11datadir = /mydata/dataread-only = ONlog-bin-index=slave-bin.indexinnodb_file_per_table = 1sslssl_ca = /etc/pki/CA/cacert.pemssl_cert = /usr/local/mysql/mysql_ssl/slave_mysql.crtssl_key = /usr/local/mysql/mysql_ssl/mysql.key# service mysqld start# mysqlmysql> change master to master_host='172.16.15.1',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000001',master_log_pos=338,master_ssl=1,master_ssl_ca='/data/mydata/ssl/cacert.pem',master_ssl_cert='/data/mydata/ssl/mysql.crt',master_ssl_key='/data/mydata/ssl/mysql.key';基于SSL的会话配置成功！为了避免主服务器出现事故导致服务器崩溃，而内存中的事件还未来的急记录到二进制中，使得从服务器无法复制完整数据，为了解决种情况，还需启动同步二进制文件功能，在MySQL配置文件中添加如下内容： sync-binlog= NOMySQL实现半同步MySQL的半同步复制用到两个模块！/usr/local/mysql/lib/pluginsemisync_master.so semisync_slave.somaster：装载semisync_master.so模块
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';mysql> set global rpl_semi_master_enabled = 1;mysql> set global rpl_semi_master_timeout = 1000;slave：装载semisync_slave.so模块mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';mysql> set global rpl_semi_slave_enabled=1;mysql> stop slave IO_THREAD;取消加载插件：mysql> uninstall plugin rpl_semi_sync_master;

samdung

走，MM，咱们化蝶去……

q3256

内练一口气，外练一口屁。

懂ni

解释就系掩饰，掩饰等于无出色，无出色不如回家休息!!!

sdoghds88888

我本非随便的人，但如果你想随便，那我就随你的便好啦！

opo

生我之前谁是我，生我之后我是谁？

julley

谢谢楼主，共同发展