linux搭建MySQL主从复制

超、凢脫俗 2022-04-12 14:10 208阅读 0赞

### 1.先检查系统是否装有mysql ###

> rpm -qa | grep mysql

### 2.下载mysql的repo源 ###

> cd /usr/local
> 
> wget [http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm][http_repo.mysql.com_mysql-community-release-el7-5.noarch.rpm]

报错: -bash: wget: 未找到命令

安装插件

> yum -y install wget

### 3.安装mysql-community-release-el7-5.noarch.rpm包 ###

> sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

### 4.安装MySQL ###

> sudo yum install mysql-server

### 5.重置MySQL密码 ###

> mysql -u root

报错：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

原因：原因是/var/lib/mysql的访问权限问题。

> chown root /var/lib/mysql/

### 6.重启MySQL服务 ###

> service mysqld restart

### 7.接着登陆设置密码 ###

> mysql -u root
> 
> use mysql;
> 
> update user set password=password('root') where user='root';
> 
> exit;

接着继续重启MySQL服务

> service mysqld restart

### 8.设置Root账户远程连接密码 ###

>  mysql -u root -p
> 
> GRANT ALL PRIVILEGES ON \*.\* TO root@"%" IDENTIFIED BY "root";

重启服务器

> service mysqld restart

### 9.安装完毕，使用外网工具连接MySQL ###

关闭防火墙

> systemctl stop firewalld.service
> 
> systemctl disable firewalld

### MySQL主从复制配置 ###

**1.主服务器节点：192.168.128.159**

> vi /etc/my.cnf

1.1.新增以下内容

> \#\#\#服务器id标识  
> server\_id=159  
> \#\#\#开启日志文件  
> log-bin=mysql-bin

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70][]

1.2.验证是否已经配置成功重启mysql服务

> service mysqld restart

**2.通过连接工具（Navicat）查询**

> show variables like '%server\_id%';

2.1.能够查询对应配置文件中的server\_id 说明已经配置成功

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 1][]

> show master status;

2.2.能够看到同步的文件，和行数 说明已经配置成功。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 2][]

## 从服务器节点 ##

**1.克隆服务器：192.168.128.159**

vi /etc/my.cnf

> \#\#\#从服务器server\_id  
> server\_id=160  
> \#\#\#日志文件同步方式  
> log-bin=mysql-bin  
> \#\#\#需要同步数据库，如果有多个逗号分隔  
> binlog\_do\_db=test

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 3][]

1.1重启mysql服务

> service mysqld restart

**2.通过连接工具（Navicat）查询**

2.1.验证是否已经配置成功

> show variables like '%server\_id%';

能够查询对应配置文件中的server\_id 说明已经配置成功

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 4][]

2.2.从服务器同步主服务器配置

master\_host配置主服务器的IP地址，master\_log\_file和master\_log\_file通过主服务器查询show master status;得到

> change master to master\_host='**192.168.128.159**',master\_user='root',master\_password='root',  
> master\_log\_file='**mysql-bin.000002**',master\_log\_pos=**120**;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 5][]

2.3.开始同步

> start slave

2.4.检查从服务器复制功能状态

> SHOW SLAVE STATUS

看到如下错误

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 6][]

解决办法

因为服务器克隆的时候交UUID产生了重复 ，解决办法，在从服务器删掉该文件

> cat  /etc/my.cnf
> 
> cd /var/lib/mysql
> 
> rm -rf auto.cnf

2.5.重启服务器即可

> service mysqld restart

2.6.检查从服务器复制功能状态

> SHOW SLAVE STATUS

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 7][]

[http_repo.mysql.com_mysql-community-release-el7-5.noarch.rpm]: http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70]: /images/20220412/b1ba804c44e6453991330a34531ef43e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 1]: /images/20220412/51fc25c4fe214bc8b14971eaccaa7a92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 2]: /images/20220412/dec83a53befa4aedb71bbc8764c9cbb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 3]: /images/20220412/db8153e4d5744da7b133c31a3d851fd5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 4]: /images/20220412/3a19af707d234beea5801f59efecfe61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 5]: /images/20220412/c54e9ff3025e4a109fc5513c1f4bed3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 6]: /images/20220412/3e0bb88a02274e278629370fcfa69189.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjcwMTA2_size_16_color_FFFFFF_t_70 7]: /images/20220412/3450df793bfa4d00bc9dc4c4fcfe13b7.png