mysql主备重新授权_mysql 主备配置

1、准备：

主机：

master    192.168.40.129

slave        192.168.40.130

mysql版本    mysql5.7

mysql启动    service mysqld start

防火墙关闭    service iptables stop

2、修改配置     /etc/my.cnf

分别修改两个主机的配置

vi /etc/my.cnf

#master主机

bind-address=192.168.40.129

log-bin=mysql-bin

server_id=1

#slave主机

bind-address=192.168.40.130

log-bin=mysql-bin

server_id=2

3、创建slave的授权登录账户git

mysql> create user ‘git’@’192.168.40.130’ identified by ‘123456’;

Query OK, 0 rows affected (0.00 sec)

mysql>  grant replication slave on *.* to ‘test’@’192.168.40.130′;

Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user,host from user;

+—————+—————–+

| user          | host            |

+—————+—————–+

| git           | 192.168.*.*     |

| git           | 192.168.100.107 |

| git           | 192.168.40.130  |

| test          | 192.168.40.130  |

| git           | localhost       |

| mysql.session | localhost       |

| mysql.sys     | localhost       |

| root          | localhost       |

+—————+—————–+

8 rows in set (0.00 sec)

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000004 |      620 |              |                  |                   |

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

4、退出mysql，将mysql中数据导出

mysql> exit

Bye

[root@minion-one ~]# cd /home/

[root@minion-one home]# mysqldump -u root -p –all-databases –master-data > git.sql

Enter password:

[root@minion-one home]# ls

account  git.sql        init.sql  res.txt  test.tar

cdr      git.sql123456  log       s        val.txt

5、将导出的.sql脚本传到slave主机上

[root@minion-one home]# scp git.sql root@192.168.40.130:/home/

root@192.168.40.130’s password:

git.sql                                       100%  785KB 784.9KB/s   00:00

6、进入slave主机，使用该脚本初始化mysql

[root@minion-two home]# mysql -uroot -p < git.sql

7、启动mysql，建立master和slave的联系

输入master ip、用户名，密码、file值、Position值，(后两项在前面使用show master status查到，我有标记颜色)

配置

mysql> change master to

-> master_host=’192.168.40.130′,

-> master_user=’git’,

->  master_password=’123456′,

-> master_log_file=’mysql-bin.000004′,

-> master_log_pos=620;

启动

mysql> start slave;

查看(两个Running都是yes，说明配置成功，否则失败，需要查找失败原因并改正)

mysql> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.40.129

Master_User: test

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 620

Relay_Log_File: minion-two-relay-bin.000005

Relay_Log_Pos: 833

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果配置失败，再次配置 change master to……这一步时，会提示

ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ” first.

这时需要先关闭主备联系：    mysql> stop slave;

之后在配置就可以了