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