mysql5.6同步_mysql5.6安装及主从半同步配置

1.安装mysql5.6

rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

yum install mysql-community-server -y

rpm -qa|grep mysql

mysql-community-libs-5.6.40-2.el7.x86_64

mysql-community-server-5.6.40-2.el7.x86_64

mysql-community-release-el7-5.noarch

mysql-community-client-5.6.40-2.el7.x86_64

mysql-community-common-5.6.40-2.el7.x86_64

#到此安装成功

systemctl start mysqld

systemctl enable mysqld

mysql_secure_installation #进行一些安全配置，根据选项选择

2. 主从同步配置

添加my.cnf配置参数

#在主库my.cnf [mysqld]中加入

log-bin = mysql-bin

server-id = 1

#从库

log-bin = mysql-bin

server-id = 2

在主库添加复制账号

grant replication slave on *.* to rep@'192.168.0.%' identified by '123456';

同步主库到从库

(1) 锁表法(锁表的时候不能退出，退出就失效了)

flush table with read lock; #锁表

mysql> show master status; #锁表记录的位置点

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 253 | | |

+------------------+----------+--------------+------------------+

mysqldump -uroot -p123456 --events -A -B |gzip >/opt/bak_$(date +%F).sql.gz #比较土的办法加-F 切割binlog可能不对

接着show master status; 如果发现位置一样，则锁表生效，如果不一样说明锁表失败，从库也会文件不同步

unlock tables; 解锁表

(2) 不锁表方法

mysqldump -uroot -p123456 -A -B -F --master-data=2 -x --events|gzip >/opt/all.sql.gz

show master status; 查看记录的位置点

还原从库

gzip -d bak_xxx.sql.gz #解压备份的文件

mysql -uroot -p123456

配置从库

mysql -uroot -p123456 -S /data/3307/mysql.sock<< EOF

CHANGE MASTER TO

MASTER_HOST='10.0.0.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=253;

EOF

start slave; #开始同步在从库使用

show slave status\G 出现如下信息成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0 #这个是从库落后主库的秒数 不是很准

3. 半同步复制配置

3.1 安装插件

#这是半同步所需的插件

ll -h /usr/lib64/mysql/plugin/semisync_*

-rwxr-xr-x. 1 root root 509K Feb 26 21:27 /usr/lib64/mysql/plugin/semisync_master.so

-rwxr-xr-x. 1 root root 273K Feb 26 21:27 /usr/lib64/mysql/plugin/semisync_slave.so

#主库加载插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> set global rpl_semi_sync_master_enabled=on;

#从库加载插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> set global rpl_semi_sync_slave_enabled=on;

3.2 加入my.cnf参数

#主库[mysqld]加入下面参数

rpl_semi_sync_master_enabled = 1

#从库[mysqld]加入下面参数

rpl_semi_sync_slave_enabled = 1

查看参数

show variables like '%Rpl%';

3.3 验证

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> show global status like 'rpl_semi%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON |

+----------------------------+-------+

4 测试