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;
之后在配置就可以了
