mysql主从复制，基于GTID主从复制

实验环境：

Server1:172.25.2.1

Server2:172.25.2.2

 

一.主从复制

Server1  server2：

yum install -y 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

Server1:

vim /etc/my.cnf

server-id=1

log-bin=mysql-bin 激活二进制文件

/etc/init.d/mysqld start  #启动过程漫长

grep password /var/log/mysqld.log  查看日志会看到密码

mysql_secure_installation 进入初始化安装

Enter password for user root: 输入默认的初始化密码

New password: 输入密码，密码要是字母大小写+特殊字符+数字

Re-enter new password: 再次输入密码

Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password: 

Re-enter new password: 

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success.            #移除匿名用户登录

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success.   #不允许root用户远程登陆 Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y       #移除test数据库

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success.   #重新加载特权表

All done! 

[root@server1 ~]# mysql -p

Enter password:  #输入密码

mysql> grant replication slave on *.* to repl@'172.25.2.%' identified by '123:Westos'; #在主节点上创建有复制权限的用户

Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> show master status;  #查看状态

 

 

 

Server2:从端

vim /etc/my.cnf #编辑配置文件加入填写server-id和主端不同即可

server-id=2 

/etc/init.d/mysqld start

grep password /var/log/mysqld.log  查看日志会看到密码

mysql_secure_installation 进入初始化安装

mysql> change master to master_host='172.25.2.1',master_user='repl',master_password='123:Westos',master_log_file='mysql-bin.000002',master_log_pos=1248;

Query OK, 0 rows affected, 2 warnings (0.19 sec) 

mysql> start slave;

Query OK, 0 rows affected (0.27 sec) 

mysql> show slave status\G;

 

测试：

master:添加数据库

mysql> create database data;

Query OK, 1 row affected (0.09 sec)

mysql> use data; Database changed

mysql> create table info(    

-> username varchar(10) not null,    

-> password varchar(10) not null);

Query OK, 0 rows affected (0.83 sec)

mysql> insert into info values('user1','123');

Query OK, 1 row affected (0.37 sec)

mysql> insert into info values('user2','456');

Query OK, 1 row affected (0.40 sec)

mysql> select * from data.info;

2 rows in set (0.00 sec)

slave：

查看到同步过来

mysql> select * from data.info;

 

二.基于GTID主从复制

server1:

 vim /etc/my.cnf

server-id=1

log-bin=mysql-bin

gtid_mode=ON

enforce-gtid-consistency=true 

/etc/init.d/mysqld restart

主端查看状态

show master status;

 

 

server2:

vim /etc/my.cnf

server-id=2

log-bin=mysql-bin

gtid_mode=ON

enforce-gtid-consistency=true

/etc/init.d/mysqld restart

从端重新建立连接：

[root@server2 ~]# mysql -p

Enter password: 

mysql> stop slave;  #停止slave

Query OK, 0 rows affected (0.04 sec)

mysql>change master to master_host='172.25.2.1',master_user='repl',master_password='123:Westos',MASTER_AUTO_POSITION = 1;  

Query OK, 0 rows affected, 2 warnings (0.16 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

mysql> show slave status\G;

*************************** 1. row ***************************               

Slave_IO_State: Waiting for master to send event                  

Master_Host: 172.25.2.1                  

Master_User: repl                  

Master_Port: 3306                

Connect_Retry: 60              

Master_Log_File: mysql-bin.000003          

Read_Master_Log_Pos: 1800              

  Relay_Log_File: server2-relay-bin.000002                

Relay_Log_Pos: 320        

Relay_Master_Log_File: mysql-bin.000003             

Slave_IO_Running: Yes            

Slave_SQL_Running: Yes

Slave_IO_Running  Slave_SQL_Running 一定是YES

Slave_IO_Running  验证没通过

Slave_SQL_Running  数据不同步

查看gtid