MySQL主从同步详细搭建步骤（5.7.28）

---

1 简介

关于MySQL 主从同步的介绍百度有很多，这里略过

---

2 环境准备

2.1 IP地址规划

主机名	IP地址	节点名	MySQL路径	数据文件目录
db1	192.168.10.3	master	/usr/local/mysql-5.7.28	/data/mysql/3306
db2	192.168.10.4	slave	同上	同上
db3	192.168.10.5	slave	同上	同上

2.2 MySQL安装及配置（三个节点都要操作）

解压包：

tar -xf mysql-5.7.28-linux-glibc2.12-x86_64.tar -C /usr/local # 解压二进制包
ln -s /usr/local/mysql-5.7.28-linux-glibc2.12-x86_64/ /usr/local/mysql-5.7.28	# 创建软连接

创建用户及数据目录：

useradd mysql -s /sbin/nologin -M
mkdir -p /data/mysql/3306

创建 my.cnf 文件：

cat /etc/my.cnf

[client]
port = 3306
socket = /data/mysql/3306/mysql.sock

[mysqld]
basedir = /usr/local/mysql-5.7.28
datadir = /data/mysql/3306
port = 3306
server_id = 1
socket = /data/mysql/3306/mysql.sock
sql_mode=NO_ENGINE_SUBSTITION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error = /data/mysql/3306/mysql.log
pid-file = /data/mysql/3306/mysql.pid

其中主库和从库中的 server_id 不能重复。
另外主库的配置文件需要加上：
binlog-do-db=test
binlog-ignore-db = mysql, performance_schema, information_schema

binlog-do-db 表示需要同步的库，不在内的不同步。
test 替换成需要同步的库名
binlog-ignore-db 是不记录binlog，来达到从库不同步mysql库，以确保各自权限

初始化MySQL：

cd /usr/local/mysql-5.7.28
./bin/mysqld --initialize --basedir=/usr/local/mysql-5.7.28 --datadir=/data/mysql/3306 --user=mysql

记住框框内的密码，一会登陆 mysql 要用。

启动 MySQL：

cd /usr/local/mysql-5.7.28
cp ./support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chown -R mysql:mysql /data/mysql
/etc/init.d/mysqld start

[root@k8s mysql-5.7.28]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!

配置环境变量：

echo "export PATH=$PATH:/usr/local/mysql-5.7.28/bin" > ~/.bashrc
source ~/.bashrc

登陆数据库并修改密码：

[root@db1 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

其余两个也是这么安装，注意要将 my.cnf 中的 server_id 改掉。
也可以直接克隆虚拟机，但是需要修改主机名以及网卡配置文件中的 UUID 和 IP地址，并重启网卡。

2.3 master配置（db1）

1. 在主库上开启 binlog 功能
   在 my.cnf 配置文件中添加如下:
   log_bin = /data/mysql/3306/mysql_master-bin
2. 重启 MySQL 服务

/etc/init.d/mysqld restart

3. 配置同步用户并授权

mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'192.168.10.%' IDENTIFIED BY '1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

4. 查看主库状态

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql_master-bin.000001
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

2.4 slave配置（db2、db3）

登录从库，执行以下操作：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.10.3',
    -> MASTER_PORT=3306,
    -> MASTER_USER='rep_user',
    -> MASTER_PASSWORD='1234',
    -> MASTER_LOG_FILE='mysql_master-bin.000001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

操作完后，可以查看从库的状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.3
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_master-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: k8s-node1-relay-bin.000002
                Relay_Log_Pos: 327
        Relay_Master_Log_File: mysql_master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

最后两项 Slave_IO_Running 和 Slave_SQL_Running 为Yes 则说明同步正常。
另一台操作一致。

---

3 测试

在主库新增一张表：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table test.tmp1 (id int not null,name varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test.tmp1 values (001,'zhangsan'),(002,'lisi');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test.tmp1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

此时，我们登录到从库查看：

mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from test.tmp1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

主从两库数据一致，表明数据已同步过去。

---

4 重置 MySQL 密码

第一种方法：
在 my.cnf 配置文件中添加如下：
[mysqld]
skip-grant-tables

重启 MySQL：

/etc/init.d/mysqld stop
/etc/init.d/mysqld start

登录数据库并修改密码：

mysql -uroot -p	# 空密码回车
mysql> update user set authentication_string = PASSWORD('1234') where user='root';
mysql> flush privileges;

第二种方法：
停数据库
用下面的方式启动服务：

/usr/local/mysql-5.7.28/bin/mysqld_safe --skip-grant-tables &

然后登录数据库修改密码