windows部署mysql5.7主从

原创
2016/12/20 23:48
阅读数 300

网上有很多资料,但是mysql版本更新会造成很多不一致下面是我的操作流程;

第一步安装主mysql

配置my.ini

 log_bin=D:\Mysql\mysql-5.7.17-winx64-master\log-bin //二进制日志,主从配置必须要在主服务器上配置
 log_error=D:\Mysql\mysql-5.7.17-winx64-master\log-error //查看错误,需要用到
 general_log=ON //5.7需要这样配置
 general_log_file=D:\Mysql\mysql-5.7.17-winx64-master\log
 slow_query_log=ON
 slow_query_log_file=D:\Mysql\mysql-5.7.17-winx64-master\log-slow
 binlog_ignore_db=mysql  //不同步的库
 basedir =D:\Mysql\mysql-5.7.17-winx64-master
 datadir =D:\Mysql\mysql-5.7.17-winx64-master\data
 port =3306
 server_id =1 //主从服务器这个值一定不能相同
 

进入D:\Mysql\mysql-5.7.17-winx64-master\bin

 mysqld--initialize   //5.7需要先执行会初始化data目录

 mysqld install mysql-master

第二步安装从mysql

配置my.ini

 log_error=D:\Mysql\mysql-5.7.17-winx64-master\log-error
 general_log=ON
 general_log_file=D:\Mysql\mysql-5.7.17-winx64-master\log
 slow_query_log=ON
 slow_query_log_file=D:\Mysql\mysql-5.7.17-winx64-master\log-slow
 basedir =D:\Mysql\mysql-5.7.17-winx64-slave
 datadir =D:\Mysql\mysql-5.7.17-winx64-slave\data
 port =3307
 server_id =2

直接copy主服务的data目录到从服务器下,这里会造成一个问题

需要修改data\auto.cnf 的service-uuid的值不然会造成主从失败

进入D:\Mysql\mysql-5.7.17-winx64-slave\bin

 mysqld install mysql-slave

 

第三步配置主从

依次启动服务器

在主mysql中创建用户slave

create user 'slave'@'127.0.0.1' identified by 'slave';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'127.0.0.1';//分配权限

show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 107 | test | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

在从服务器中执行

change master to master_host='127.0.0.1',master_port=3306,master_user='salve',master_password='salve',master_log_file='mysql-bin.000001',master_log_pos=107;

Query OK, 0 rows affected (0.19 sec)

start slave;

查看slave状态

show slave status;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                Master_Host: 172.17.2.40
                Master_User: photorepl
                Master_Port: 4331
               Connect_Retry: 60
               Master_Log_File: mysql-bin.005502
                Read_Master_Log_Pos: 64401238
               Relay_Log_File: mysqld-relay-bin.015418
               Relay_Log_Pos: 13456757
               Relay_Master_Log_File: mysql-bin.005152
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
               Replicate_Do_DB: 
               Replicate_Ignore_DB: mysql
               Replicate_Do_Table: 
               Replicate_Ignore_Table: 
               Replicate_Wild_Do_Table: photo.%
               Replicate_Wild_Ignore_Table: mysql.%
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
               Exec_Master_Log_Pos: 13456620
              Relay_Log_Space: 36764898503
               Until_Condition: None
              Until_Log_File: 
              Until_Log_Pos: 0
              Master_SSL_Allowed: No
             Master_SSL_CA_File: 
             Master_SSL_CA_Path: 
              Master_SSL_Cert: 
              Master_SSL_Cipher: 
             Master_SSL_Key: 
            Seconds_Behind_Master: 249904
××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

只要这两个是yes就对咯;

 

展开阅读全文
打赏
0
1 收藏
分享
加载中
更多评论
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部