mysql互为主从复制的配置

准备:

我们准备两台linux系统的服务器
mysql1:192.168.100.1
mysql2:192.168.100.2
我们这里用的是mysql5.7版本的数据库

注意:

安装mysql请查看以前的博客
配置两台服务器有相同的数据 可以查看备份和恢复

两台机子相同的准备:

1.修改配置文件

]# vim  /etc/my.cny
[mysqld]
server_id=1   //指定server的id  两台机子不能相同
log_bin=bin.log   //指定日志文件  两台机子不能重复
port=3306           //指定端口号

2.重启mysql服务

[root@mysql1 ~]# systemctl restart mysqld

mysql1的主服务器配置

1.用户授权

[root@mysql1 ~]# mysql -uroot -p123456
mysql> grant replication  slave on *.* to repluser@"%" identified by "123qqq...A";

2.查看bin日志信息

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000001 |      441 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql2的从服务器配置

1.指定主库信息

[root@mysql2 ~]# mysql -uroot -p123456
mysql> change master to
    -> master_host="192.168.100.1",master_port=3306,    //指定主服务器地址
    -> master_user="repluser",                 
    -> master_password="123qqq...A",        //这里的密码一定要写授权的密码
    -> master_log_file="bin.000001",            //这两列是bin日志信息
    -> master_log_pos=441;

2.启动slave主从同步信息功能

mysql> start slave;

3.查看状态信息
确保"Slave_IO_Running: Slave_SQL_Running: "都为yes 其中任何一个为no都是错误
查看指定主数据库是否正确

mysql> show slave status\G;
                  Master_Host: 192.168.100.1    
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

一主一从配置完毕

配置互为主从(继续上面的环境)

mysql2为主

1.用户授权

mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

2.查看bin日志信息

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000001 |      441 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql1为从

1.指定主库信息

mysql> change master to
    -> master_host="192.168.100.2",
    -> master_port=3306,
    -> master_user="repluser",
    -> master_password="123qqq...A",
    -> master_log_file="bin.000001",
    -> master_log_pos=441;

2.启动slave主从同步程序

mysql> start slave;

3.查看slave状态信息
确保IO SQL 线程都为yes

ysql> show slave status\G;
                  Master_Host: 192.168.100.2
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

互为主从服务器配置完毕