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.sh export PATH=$PATH:/usr/local/mysql/bin # . /etc/profile.d/mysql.sh # vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format=mixed server-id = 1 datadir = /mydata/data log-bin-index=master-bin.index innodb_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.sh export PATH=$PATH:/usr/local/mysql/bin # . /etc/profile.d/mysql.sh # vim /etc/my.cnf [mysqld] Lrelay-log = relay-log binlog_format=mixed server-id = 11 datadir = /mydata/data read-only = ON log-bin-index=slave-bin.index innodb_file_per_table = 1 # service mysqld start # mysql
连接Master
mysql> 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 > serial
CA为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 365
Slave创建证书:
# cd /usr/local/mysql/mysql_ssl # (umask 077; openssl genrsa -out mysql.key 2048) # openssl req -new -key mysql.key -out slave_mysql.csr
Slave证书请求发送给CA，请求签署:
# scp mysql.csr 172.16.15.1:/root
CA为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-bin binlog_format=mixed server-id = 1 datadir = /mydata/data log-bin-index=master-bin.index innodb_file_per_table = 1 ssl ssl_ca = /etc/pki/CA/cacert.pem ssl_cert = /usr/local/mysql/mysql_ssl/mysql.crt ssl_key = /usr/local/mysql/mysql_ssl/mysql.key # service mysqld start # msyql mysql> 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-log binlog_format=mixed server-id = 11 datadir = /mydata/data read-only = ON log-bin-index=slave-bin.index innodb_file_per_table = 1 ssl ssl_ca = /etc/pki/CA/cacert.pem ssl_cert = /usr/local/mysql/mysql_ssl/slave_mysql.crt ssl_key = /usr/local/mysql/mysql_ssl/mysql.key # service mysqld start # mysql mysql> 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= NO
MySQL实现半同步
MySQL的半同步复制用到两个模块！
/usr/local/mysql/lib/plugin
semisync_master.so semisync_slave.so
master：
装载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;
转载于:https://blog.51cto.com/runnerup/1203931
252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
