实验环境:
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