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;

之后在配置就可以了