mysql gtid 同步_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%';