mysql主从复制搭建

1、主机准备

(1)

主机名称	ip
mysql_master	192.168.168.136
mysql_slave	192.168.186.137

 

 

(2)

关闭防火墙，关闭selinux。

systemctl stop firewalld

systemctl disable firewalld

setenforce 0

sed -i '/^SELINUX=/cSELINUX=disabled' /etc/selinux/config

（3）

修改/etc/hosts文件

 

 （4）

时间同步

yum -y install ntpdate

 ntpdate cn.ntp.org.cn

说明：保证两台服务器系统时间一致即可

2、主从复制的原理：

（1）slave端的io线程，向master端的binlog dump线程发送请求。

（2）master端binlogdump线程获取二进制文件信息（文件名和位置信息），发送给slave端的io线程。

（3）slave端的io线程获取到的内容依次写到slave端的relay log里，并把master端的binlog文件名和位置记录到master.info里。

（4）slave端的sql线程监测到relay log中内容更新，就会解析relay log 里更新的内容，并执行这些操作，从而达到和master端数据一致。

3、主从搭建步骤。

（1）master端搭建过程。

1、解压： 

tar xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

2、移动到安装目录，并改名为mysql

mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql

3、创建mysql用户

useradd -r -s /sbin/nologin mysql

4、更改安装目录权限

chown -R  mysql.mysql /usr/local/mysql

5、卸载mariadb-libs软件包，删除/etc/my.cnf

 yum remove mariadb-libs -y

6、数据库初始化

cd /user/local/mysql

./scripts/mysql_install_db --user=mysql

7、systemd管理mysql

 （1）cp support-files/mysql.server /etc/init.d/mysql

 （2）[root@mysql_master mysql]# cat /etc/systemd/system/mysql.service
[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service

[Service]
User=mysql
Group=mysql

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables etc.
#ExecStartPre=/usr/bin/mysql-systemd-start pre

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld_safe

# Don't signal startup success before a ping works
#ExecStartPost=/usr/bin/mysql-systemd-start post

# Give up if ping don't get an answer
TimeoutSec=600

Restart=always
PrivateTmp=false

8、添加环境变量

vim /etc/profile

export PATH=$PATH:/usr/local/mysql/bin

source /etc/profile

启动mysql

systemctl start mysql

 

 修改master端配置文件

[root@mysql_master mysql]# egrep -v '^#|^$' my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/mysql.err
character_set_server=utf8mb4
log-bin=/usr/local/mysql/data/binlog
server-id = 10
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

开启binlog日志。

在相应的目录创建mysql.err日志文件

touch mysql.err

重启mysql

（2）slave端部署

1、解压

tar xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

2、移动到安装目录，并改名为mysql

 mv  mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql

cp support-files/mysql.server /etc/init.d/mysql

3、创建mysql用户

useradd -r -s /sbin/nologin mysql

修改安装目录属组和属主

chown -R mysql.mysql /usr/local/mysql

cd /usr/local/mysql

cp support-files/mysql.server /etc/init.d/mysql

[root@mysql_master mysql]# cat /etc/systemd/system/mysql.service

[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service

[Service]
User=mysql
Group=mysql

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables etc.
#ExecStartPre=/usr/bin/mysql-systemd-start pre

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld_safe

# Don't signal startup success before a ping works
#ExecStartPost=/usr/bin/mysql-systemd-start post

# Give up if ping don't get an answer
TimeoutSec=600

Restart=always
PrivateTmp=false

（3）设置环境变量步骤通master端一样

4、slave端不需要初始化

（1）master端数据同步到slave端

   rsync -av /usr/local/mysql/data 192.168.186.137:/usr/local/mysql/

（2）建立配置文件

[root@mysql_slave mysql]# cat my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/mysql.err
character_set_server=utf8mb4
server-id=20
relay-log=/usr/local/mysql/data/relaylog

slave端创建日志文件

rm /etc/my.cnf

touch mysql.err

5、master端创建授权用户

（1）mysql> grant replication slave on *.* to 'slave'@'192.168.186.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

（2）master端进行锁表防止两端数据不一致

flush tables with read lock;

（3）查看二进制文件写到什么位置

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 120| | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 （4）slave端设置

change master to master_host='192.168.186.136',master_user='slave',master_password='123',master_port=3306,master_log_file='binlog.000003',master_log_pos=120;

start slave;

 

（5）master端解表

    unlock tables;

（6）验证

master端创建数据库：

create database db_01;

slave端检查是都存在db_01库。

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_01 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.02 sec)