主从复制搭建:一个master对应2个slave
##########################################
//环境规划
linux版本:centos6.4 mysql5.5.30
master:10.10.54.154
slave: 10.10.54.155 10.10.54.156
#master服务器不是新搭建的,所以需要把master数据库中数据备份到从服务器
//master修改配置文件,创建用户
1.修改配置文件
shell> vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=master_bin
shell> /et/init.d/mysqld restart
2.创建用户,授予复制权限
mysql> grant replication slave on *.* to 'repl'@'10.10.54.%' identified by 'mysql';
mysql> flush privileges;
//slave修改配置文件
两个从服务器上操作一样
shell> vim /etc/my.vnf
server_id=8
log_bin=slave-bin
//mysqldump导入数据到从服务器
1.master操作
mysql> flush tables with read lock;
mysql> mysqldump -uroot -p --all-databases --flush-logs --master-data=2 > alldatabases.sql
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000017 | 107 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> unlock tables;
2.slave操作
两台从服务器上操作一样
shell> mysql -uroot -pmysql < alldatabases.sql
//slave上执行change master操作,查看主从架构
mysql> change master to master_host='10.10.54.154',master_user='repl',master_password='mysql',master_log_file='master-bin.000006',master_log_pos=307;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.54.154
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000017
Read_Master_Log_Pos: 107
Relay_Log_File: mycentos5-relay-bin.000002
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
//测试主从架构是否正常运行
1.master 上创建表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| d1 |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> use d1;
mysql> create table t1(id int NOT NULL,name varchar(20) NOT NULL);
mysql> insert into t1 values(1,'hello');
2.从服务器上查看表是否同步
mysql> use d1;
msyql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | hello |
+----+-------+
上面主从复制环境已经搭建成功,下面搭建mysql半复制(Semisynchronous Replication)
//环境规划
mysql版本:5.5.30
master:10.10.54.154
slave:10.10.54.155
#主从环境已经搭建好
//master上安装半同步复制插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
#要想下次重启mysql自动生效,可以加入配置文件
sehll> vim /etc/my.cnf
[msyqld]
set global rpl_semi_sync_master_enabled=1
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;
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
#同理,要想下次重启mysql自动生效,可以加入配置文件
sehll> vim /etc/my.cnf
[msyqld]
set global rpl_semi_sync_slave_enabled = 1
//测试半同步是否生效
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| 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 | 0 |
+--------------------------------------------+-------+
#测试成功
搭建mysql replication支持ssl,一主一从
//环境说明:
master:10.10.54.156
slave:10.10.54.157
1.主服务器上生成私钥
shell> cd /etc/pki/CA/
shell> umask 077;openssl genrsa -out private/cakye.pem 2048
Generating RSA private key, 2048 bit long modulus
..........................................................................+++
..............................+++
2.主服务器上生成自签证书
shell> openssl req -new -x509 -key private/cakye.pem -out cacert.pem -days 365
3.主服务器上申请证书
shell> cd /usr/local/mysql/
shell> mkdir ssl
shell> chown mysql.mysql ssl
shell> umask 077; openssl genrsa -out /usr/local/mysql/ssl/master.key 2048
shell> openssl req -new -key /usr/local/mysql/ssl/master.key -out /usr/local/mysql/ssl/master.csr
shell> openssl ca -in /usr/local/mysql/ssl/master.csr -out /usr/local/mysql/ssl/master.crt -days 365
4.配置文件中添加ssl支持
shell> vim /etc/my.cnf
[msyqld]
ssl
ssl-ca=/etc/pki/CA/cacert.pem
ssl-cert=/usr/local/mysql/ssl/master.crt
ssl-key=/usr/local/mysql/ssl/master.key
5.从服务器上
shell> cd /usr/local/mysql/
shell> mkdir ssl
shell> chown mysql.mysql ssl
shell> umask 077; openssl genrsa -out /usr/local/mysql/ssl/mysql.key 2048
shell> openssl req -new -key /usr/local/mysql/ssl/master.key -out /usr/local/mysql/ssl/mysql.csr
6.把证书传给主服务器,在主服务器上签署证书后在传给从服务器
shell> cd /usr/local/mysql/
shell> scp mysql.csr root@10.10.54.156:/etc/pki/CA
7.主服务器上签署证书
shell> openssl ca -in mysql.csr -out mysql.crt -days 365
8.传回给从服务器
shell> scp cacert.pem mysql.crt root@10.10.54.157:/usr/local/mysql/ssl
shell> chown -R mysql.mysql
9.配置my.cnf文件
在my.cnf中添加ssl支持
[mysqld]
ssl #添加这一行
10.主服务器上创建ssl链接用户
mysql> grant replication client,replication slave on *.* to ssl@10.10.54.157 identified by 'ssl';
11.从服务器上配置
mysql > change master to master_host='10.10.54.156', master_user='ssl', master_password='ssl', master_log_file='mysql-bin.000001',master_port=3306,master_log_pos=112, 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';
12.查看从上slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.54.156
Master_User: ssl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 503
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 263
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /usr/local/mysql/ssl/mysql.crt
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/ssl/mysql.key
Seconds_Behind_Master: 0