MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。这与同步复制可以进行对比，同步复制是MySQL的一个特征。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后并等待主服务器通知新的更新。
------------------------------------------------------------------------------------------------------
实验环境在redhat6.4 ▎ 安装包使用的mysql-5.5.33版本的。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.下载完mysq安装包到主服务器上
mysql-5.5.33-linux2.6-x86_64.tar.gz
2.复制安装包到从服务器上
1 | [root@yulong ~]# scp mysql-5.5.33-linux2.6-x86_64.tar.gz root@172.16.8.11:/root |
3.解压包到指定路径下
1 | tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local |
4.新建一个mysql用户和mysql的数据库目录,修改创建的目录属主和属组为mysql
1 2 3 4 5 | [root@yulong local]# useradd -r -u 306mysql[root@yulong local]# mkdir -pv /mydata/data[root@yulong local]# chown -R mysql:mysql /mydata/data/[root@yulong local]# ls -ld /mydata/data/drwxr-xr-x 2mysql mysql 4096Aug 2611:52/mydata/data/ |
5.创建一个软连接为mysql,并把mysql里面的文件属主改为root属组为mysql
1 2 3 4 | [root@yulong local]# ln -sv mysql-5.5.33-linux2.6-x86_64 mysql`mysql' -> `mysql-5.5.33-linux2.6-x86_64'[root@yulong local]# cd /usr/local/mysql[root@yulong mysql]# chown -R root:mysql * |
6.初始化数据库一些准备
1 2 3 4 5 6 7 8 9 | [root@yulong ]# cd /usr/local/mysql[root@yulong mysql]# cp support-files/my-large.cnf /etc/my.cnf[root@yulong mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld[root@yulong mysql]# echo 'export PATH=/usr/local/mysql/bin:$PATH'> /etc/profile.d/mysql.sh[root@yulong mysql]# . /etc/profile.d/mysql.shcp主配置文件cpmysq.server脚本到启动路径下输出环境变量重读下环境变量配置文件 |
7.初始化mysql数据库
1 2 3 4 5 6 7 8 | [root@yulong mysql]# vim /etc/my.cnfdatadir=/mydata/datainnodb_file_per_table = 1[root@yulong mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/Installing MySQL system tables...OKFilling help tables...OK |
8.启动mysql服务
1 2 3 | [root@yulong mysql]# service mysqld start[root@yulong mysql]# netstat -tnlptcp 000.0.0.0:33060.0.0.0:* LISTEN 3018/mysqld |
9.下面配置主服务的一些参数，使从服务器可以同步数据
1 2 3 4 5 6 7 8 9 10 11 | [root@yulong mysql]# mysqlmysql> grant replication slave,replication client on *.* to tom@'172.16.%.%'identified by'redhat';mysql> flush privileges;Query OK, 0rows affected (0.00sec)mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003| 351| | |+------------------+----------+--------------+------------------+1row inset(0.00sec) |
10.从服务器上配置步骤和1-8的步骤一样这里就不再次配置了
11.编辑从服务器上主配置文件
1 2 3 4 | [root@localhost mysql]# vim /etc/my.cnfserver-id = 20relay-log = /mydata/data/relay-bin添加上面这两项 |
12.启动复制线程
1 | mysql> CHANGE MASTER TO MASTER_HOST='172.16.8.10', MASTER_USER='tom', MASTER_PASSWORD='redhat' |
13.在主服务器上添加一个数据，看看主从是不是一样的
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> create database qq;mysql> create database taobao;mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || qq || taobao || test |+--------------------+6rows inset(0.01sec) |
14.在从服务器上查看
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || qq || taobao || test |+--------------------+6rows inset(0.00sec)从上面的对比看出数据是一样的 |
二.下面来实现增量复制
1.先把从服务器数据库停掉
1 2 | [root@localhost data]# service mysqld stopShutting down MySQL.... [ OK ] |
2.删除数据库目录中的数据和重新初始化
1 2 | [root@localhost ~]# rm -rf /mydata/data/[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ |
3.在主数据库中添加一些数据在备份下数据库
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> create table zhongqiu ( id int,name varchar);Query OK, 0rows affected (0.38sec)mysql> insert into zhongqiu values (1,q),(2,n),(3,j);mysql> select * from zhongqiu;+------+------+| id | name |+------+------+| 1| q || 2| n || 3| j || 1| l |[root@yulong data]# mysqldump -uroot --all-databases --lock-all-tables --events --master-data=2> /tmp/all.sql |
4.把备份的数据导入到从服务器上
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [root@yulong data]# scp /tmp/all.sql root@172.16.8.11:/root/[root@localhost ~]# mysql < all.sqlmysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || qq || taobao || test |+--------------------+scp到从服务器上导入数据 |
5.然后从导入数据之后开始复制主服务上的数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [root@localhost ~]# head -30all.sql找到数据最后记录的位置大概就是下面这段-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1034;mysql> CHANGE MASTER TO MASTER_HOST='172.16.8.10', MASTER_USER='tom', MASTER_PASSWORD='redhat', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1034;Query OK, 0rows affected (0.16sec)mysql> start slave;Query OK, 0rows affected (0.03sec)mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send eventMaster_Host: 172.16.8.10Master_User: tomMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 1034Relay_Log_File: ralay-bin.000002Relay_Log_Pos: 253Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: Yes |
6.在主服务器上添加数据看看从服务器是否同步
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> create database nihao;主的：mysql> show databases; 从的：mysql> show databases;+--------------------+ +--------------------+| Database | | Database |+--------------------+ +--------------------+| information_schema | | information_schema || mysql | | mysql || nihao | | nihao || performance_schema | | performance_schema || qq | | qq || taobao | | taobao || test | | test |+--------------------+ +--------------------+7rows inset(0.03sec) 7rows inset(0.03sec) |
三.实现半同步
1.在主服务器上安装master模块
1 2 3 4 | mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0rows affected (0.06sec)mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000; |
2.在从服务器安装添加Slave模块
1 2 3 | mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; |
3.查看主服务器上的semi_sync是否开启，注意clients 变为1，证明主从半同步复制连接成功:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';mysql> SHOW GLOBAL STATUS LIKE '%rpl_semi%';+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 1|| Rpl_semi_sync_master_net_avg_wait_time | 863|| Rpl_semi_sync_master_net_wait_time | 863|| Rpl_semi_sync_master_net_waits | 1|| Rpl_semi_sync_master_no_times | 0|| Rpl_semi_sync_master_no_tx | 0|| Rpl_semi_sync_master_status | ON || Rpl_semi_sync_master_timefunc_failures | 0|| Rpl_semi_sync_master_tx_avg_wait_time | 0|| Rpl_semi_sync_master_tx_wait_time | 0|| Rpl_semi_sync_master_tx_waits | 0|| Rpl_semi_sync_master_wait_pos_backtraverse | 0|| Rpl_semi_sync_master_wait_sessions | 0|| Rpl_semi_sync_master_yes_tx | 1|+--------------------------------------------+-------+14rows inset(0.02sec)正常滴 |
为mysql建立安全的SSL加密
1.在主服务器上为CA生成一个私钥
1 2 3 4 | [root@yulong CA]# (umask 077;openssl genrsa -out private/cakye.pem 2048)Generating RSA privatekey, 2048bit long modulus..........................................................................+++..............................+++ |
2.在主服务器上生成自签证书
1 2 3 4 5 6 7 | [root@yulong CA]# openssl req -new-x509 -key private/cakye.pem -out cacert.pem -days 365Country Name (2letter code) [XX]:CNState or Province Name (full name) []:beijingLocality Name (eg, city) [Default City]:beijingOrganization Name (eg, company) [Default Company Ltd]:beijingOrganizational Unit Name (eg, section) []:beijingCommon Name (eg, your name or your server's hostname) []:172.16.8.10 |
3.主服务器上申请证书
1 2 3 4 5 | [root@yulong data]# mkdir ssl[root@yulong data]# chown mysql.mysql ssl[root@yulong data]# (umask 077; openssl genrsa -out /usr/local/mysql/ssl/master.key 2048)[root@yulong data]# openssl req -new-key /usr/local/mysql/ssl/master.key -out /usr/local/mysql/ssl/master.csr[root@yulong data]# openssl ca -in/usr/local/mysql/ssl/master.csr -out /usr/local/mysql/ssl/master.crt -days 365 |
4.编辑主服务器上的配置文件支持SSL功能
1 2 3 4 5 | [root@yulong data]# vim /etc/my.cnfsslssl-ca=/etc/pki/CA/cacert.pemssl-cert=/usr/local/mysql/ssl/master.crtssl-key=/usr/local/mysql/ssl/master.key |
5.在从服务器上生成一个签署证书
1 2 3 4 | [root@yulong data]# mkdir ssl[root@yulong data]# chown mysql.mysql ssl[root@yulong data]# (umask 077; openssl genrsa -out /usr/local/mysql/ssl/mysql.key 2048)[root@yulong data]# openssl req -new-key /usr/local/mysql/ssl/master.key -out /usr/local/mysql/ssl/mysql.csr |
6.把签署证书发送到主服务器上,在主服务器上签署证书后在发给从服务器
1 2 3 4 | [root@localhost ssl]# scp mysql.csr root@172.16.8.10:/etc/pki/CA[root@yulong CA]# openssl ca -inmysql.csr -out mysql.crt -days 365[root@yulong CA]# scp cacert.pem mysql.crt root@172.16.8.11:/usr/local/mysql/ssl[root@yulong ssl]## chown -R mysql.mysql * 把放秘钥的文件中的属主和属组调整为mysql |
7.配置my.cnf文件在开启SSL重启服务
1 2 | [root@yulong data]# echo 'ssl'> /etc/cnf[root@yulong data]#service mysqld restart |
8主上创建ssl链接的用户
1 | mysql> grant replication client,replication slave on *.* to openssl@172.16.8.11identified by 'redhat'; |
9.从服务器上通过ssl链接主服务器
1 | mysql > change master to master_host='172.16.8.10', master_user='openssl', master_password='redhat', master_log_file='mysql-bin.000007',master_port=3306,master_log_pos=430, 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'; |
10.查看slave的状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send eventMaster_Host: 172.16.8.10Master_User: opensslMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 430Relay_Log_File: ralay-bin.000002Relay_Log_Pos: 253Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 430Relay_Log_Space: 403Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: YesMaster_SSL_CA_File: /usr/local/mysql/ssl/cacert.pemMaster_SSL_CA_Path:Master_SSL_Cert: /usr/local/mysql/ssl/mysql.crtMaster_SSL_Cipher:Master_SSL_Key: /usr/local/mysql/ssl/mysql.keySeconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 11row inset(0.00sec) |
11.通过命令看下链接状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [root@localhost ssl]# 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 -uopenssl -h172.16.8.10-predhatmysql> \s--------------mysql Ver 14.14Distrib 5.5.33, forlinux2.6(x86_64) using readline 5.1Connection id: 15Current database:Current user: openssl@172.16.8.11SSL: Cipher inuseisDHE-RSA-AES256-SHACurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.5.33-log MySQL Community Server (GPL)Protocol version: 10Connection: 172.16.8.10via TCP/IPServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8TCP port: 3306Uptime: 9min 47sec |
