mysql主从复制,半同步复制(Semisynchronous Replication)

1.搭建一个master对应1个slave (只复制某个数据库)
#######################################
//环境规划
mysql版本：mysql5.5.30
master：10.10.54.154
slave: 10.10.54.155
master上数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d1                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+
slave上数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+


//master上修改配置文件，创建用户
1.修改配置文件
shell> vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
server_id=1 #master上的server_id一定要比slave上的小
log_bin=master_bin
shell> /et/init.d/mysqld restart #重启mysqld

2.创建用户，授予复制权限
mysql> grant replication slave on *.* to 'repl'@'10.10.54.%' identified by 'slave';
mysql> flush privileges; #刷新权限


//slave上修改配置文件
shell> vim /etc/my.vnf
server_id=8
log_bin=slave-bin
replicate_do_db=d1 #只复制与d1数据库相关的操作


//数据导入
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 |              |                  |
+-------------------+----------+--------------+------------------+
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='slave',master_log_file='master-bin.000017',master_log_pos=107;
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


//测试是否只复制d1数据库
A.
测试master上创建新数据库d2
mysql> create database d2;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d1                 |
| d2                 |
| mysql              |
| performance_schema |
| test               |
| test1              |
+--------------------+
查看slave数据库信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d1                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+

B.
在master的d1数据库中新建表
1.
mysql> use d1;
mysql> create table t1(id int NOT NULL,name varchar(20) NOT NULL);
mysql> insert into t1 values(1,'hello');

2.查看slave的d1数据库内到表
mysql> use d1;
mysql> show tables;
+--------------+
| Tables_in_d1 |
+--------------+
| departments  |
| dept_emp     |
| dept_manager |
| employees    |
| salaries     |
| t1           |
| t1_e         |
| titles       |
+--------------+
测试成功





2.搭建一个master对应2个slave
##########################################
//环境规划
linux版本：ceontos6.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]
default-storage-engine=myisam
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 'slave';
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 |              |                  |
+-------------------+----------+--------------+------------------+
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='slave',master_log_file='master-bin.000017',master_log_pos=107;
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 |
+----+-------+



3.搭建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    |
+------------------------------------+-------+


//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> 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     |
+--------------------------------------------+-------+
#测试成功