(2)mysql一主一从搭建

1.机器说明

节点		外网ip	内网ip
ali-shuidi01	主	47.114.142.232	172.16.94.131
ali-shuidi02	从	47.114.56.214	172.16.94.132

 

2.修改主机 ali-shuidi01 配置文件

vim /etc/my.cnf

 

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#设置需要复制的数据库
binlog-do-db=copydb
#设置logbin格式
binlog_format=STATEMENT

3.修改从机 ali-shuidi02 配置文件

vim /etc/my.cnf

 

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

 

4.主机、从机重启 MySQL 服务

systemctl restart mysqld

 

5.主机从机都关闭防火墙

 

6.在主机上建立帐户并授权 slave

#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Zan_666888';

7.查询master的状态

 

#查询master的状态
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |      430 | copydb       | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

#记录下File和Position的值

#执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化

 

8.在从机上配置需要复制的主机

#复制主机的命令

CHANGE MASTER TO MASTER_HOST='主机的IP地址',

MASTER_USER='slave',

MASTER_PASSWORD='密码',

MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

 

mysql> CHANGE MASTER TO MASTER_HOST='172.16.94.131',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='Zan_666888',
    -> MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=430;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

 

 

#启动从服务器复制功能
start slave;
#查看从服务器状态
show slave status\G;

 

#下面两个参数都是Yes，则说明主从配置成功！
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes