mysql+gtid主从同步

安装mysql

 yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm  mysql-community-libs-5.7.17-1.el6.x86_64.rpm  mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm  mysql-community-server-5.7.17-1.el6.x86_64.rpm  -y
server1:
[root@server1 ~]# /etc/init.d/mysqld start
[root@server1 ~]# grep password /var/log/mysqld.log
[root@server1 ~]# mysql_secure_installation
[root@server1 ~]# mysql -p
mysql> show databases;
mysql> grant replication slave on *.* to bss@’172.25.8.%’ identified by ‘westos’;
mysql> quit

server2:
[root@server2 ~]# mysql -u bss -p -h 172.25.8.1
Enter password:

 

[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# grep password /var/log/mysqld.log
[root@server2 ~]# mysql_secure_installation
[root@server2 ~]# mysql -p
Enter password:
mysql> change master to master_host='172.25.8.1', master_user='bss', master_password='Westos-456',master_log_file='mysql-bin.000003', master_log_pos=1001;
mysql> start slave;
Query OK, 0 rows affected (0.14 sec)
mysql> show slave status\G

 

gtid
server1
vim /etc/my.cnf
/etc/init.d/mysqld restart
server2
mysql> stop slave;
mysql> change master to master_host='172.25.8.2',master_user='bss',master_password='Westos-123',MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show  slave status\G
mysql> use westos
mysql> select * from usertb;
测试：
server1
mysql> create database test;
mysql> use test;
mysql> create table userlist(
    -> username varchar(10) not null,
    -> password varchar(20) not null);
mysql> desc userlist;
mysql> insert into userlist values ('user1','111');
mysql> insert into userlist values ('user2','222');

 

server2
mysql> show databases;
mysql> use test
mysql> select * from userlist;

 

server1
mysql> use test;
mysql> show tables;
mysql> insert into userlist values ('user3','333');

 

server2
mysql> use test;
mysql> select * from userlist;

 

主从半同步
server1:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> show variables like '%semi_sync%';
mysql> set global rpl_semi_sync_master_enabled=1;

 

server2：
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;
mysql> start slave io_thread;
mysql> show global variables like '%semi%';

 

检测：
server1：
mysql> use test;
mysql> insert into userlist values ('user4','444');
mysql> show status like '%semi_sync%';

 

将slave的半同步关掉,进行插入测试:
server2：
mysql> set global rpl_semi_sync_slave_enabled=OFF;
mysql> stop slave io_thread;
mysql> start slave io_thread;
mysql> show global variables like '%semi%';

 

server1：
mysql> insert into userlist values ('user5','555');
Query OK, 1 row affected (10.20 sec)
mysql> show global status like '%semi%';