mysql配置主从复制

最新推荐文章于 2024-08-29 23:09:51 发布

Crowno17

最新推荐文章于 2024-08-29 23:09:51 发布

阅读量143

点赞数 1

分类专栏： Mysql

本文链接：https://blog.csdn.net/qq_24532581/article/details/97512333

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.准备两台linux服务器安装好Mysql的环境，我这里Mysql的服务器的ip分别为

#matser 节点
192.168.88.201 
#slave 节点
192.168.88.202

2 配置主的mysql

第一步

vim /etc/my.cnf

第二步添加binlog 跟server_id

[mysqld]

# 添加如下的配置

#开启binlog
log_bin =mysql-bin
#配置server_id 
server_id = 19


datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

这里的serveri_id是随意指定的不要重复就好，配置好之后重启mysql服务

service mysqld restart

验证配置是否生效执行命令出现下面的则配置成功

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

3.配置从节点

第一步

vim /etc/my.cnf

在my.cnf添加如下的内容

# 开启bin-log
log_bin =mysql-bin
#设置server_id 要与主节点不相同
server_id =199502
# 开启relay_log
relay_log =/var/lib/mysql/mysql-relay-bin
log_slave_updates =1

开始复制

CHANGE MASTER TO MASTER_HOST="192.168.88.201" ,
#主节点用户名
MASTER_USER="****",
#主节点的密码
MASTER_PASSWORD="*****",
#bin_log的日志文件 如果你是第一次配合文件名是这样的 
MASTER_LOG_FILE="mysql-bin.000001",
# 读取文件的位置
MASTER_LOG_POS = 0;

SHOW SLAVE STATUS;

这时候你查看状态会发现你的IO线程是No没有启动的，在slave-mysql客户端通过下面的指令

START SLAVE;

然后 SHOW PROCESSLIST;

你就会看到如下的IO线程

+----+-------------+--------------------+---------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host               | db      | Command | Time  | State                                                  | Info             |
+----+-------------+--------------------+---------+---------+-------+--------------------------------------------------------+------------------+
|  1 | system user |                    | NULL    | Connect | 11659 | Slave has read all relay log; waiting for more updates | NULL             |
|  2 | system user |                    | NULL    | Connect | 11669 | Waiting for master to send event                       | NULL             |
|  7 | root        | 192.168.88.1:53257 | test001 | Sleep   |    72 |                                                        | NULL             |
|  8 | root        | 192.168.88.1:53263 | NULL    | Sleep   |  1262 |                                                        | NULL             |
| 10 | root        | localhost          | NULL    | Query   |     0 | starting                                               | show processlist |
+----+-------------+--------------------+---------+---------+-------+--------------------------------------------------------+------------------+
5 rows in set (0.01 sec)

到此配置也就完成了