linux搭建MySQL主从复制-蒲公英云

linux搭建MySQL主从复制

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

rpm -qa | grep mysql

2.下载mysql的repo源

cd /usr/local

wget 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