MySQL5.7主从搭建

1.准备好两台服务器,

主 192.168.43.2 CentOS7.7

从 192.168.43.3 CentOS7.7

2.下载安装包

https://downloads.mysql.com/archives/community/

安装包版本为：mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

3.清理安装环境

[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]# rpm -qa | grep mariadb*
mariadb-libs-5.5.64-1.el7.x86_64

[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]# rpm -qa | grep mariadb*
mariadb-libs-5.5.64-1.el7.x86_64
[root@localhost ~]# rpm -e mariadb-libs-5.5.64-1.el7.x86_64
错误：依赖检测失败：
libmysqlclient.so.18()(64bit) 被 (已安裝) postfix-2:2.10.1-7.el7.x86_64 需要
libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-7.el7.x86_64 需要

搭建本地yum重新卸载

[root@localhost yum.repos.d]# vi /etc/yum.repos.d/my.repo

[mysql]
name=mysql
baseurl=file:///mnt
enable=1
gpgcheck=0

重新卸载安装包

[root@localhost ~]# yum remove mariadb-libs-5.5.64-1.el7.x86_64

4.关闭防火墙及SELINUX

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# vi /etc/selinux/config

5.安装依赖包

yum install -y perl-Module*

6.解压安装包

[root@localhost soft]# tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar 
mysql-community-embedded-devel-5.7.26-1.el7.x86_64.rpm
mysql-community-libs-5.7.26-1.el7.x86_64.rpm
mysql-community-embedded-5.7.26-1.el7.x86_64.rpm
mysql-community-test-5.7.26-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.26-1.el7.x86_64.rpm
mysql-community-common-5.7.26-1.el7.x86_64.rpm
mysql-community-devel-5.7.26-1.el7.x86_64.rpm
mysql-community-client-5.7.26-1.el7.x86_64.rpm
mysql-community-server-5.7.26-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm

7.开始安装

[root@localhost soft]# rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
警告：mysql-community-common-5.7.26-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-5.7.26-1.e################################# [100%]
[root@localhost soft]# rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
警告：mysql-community-libs-5.7.26-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-5.7.26-1.el7################################# [100%]
[root@localhost soft]# rpm -ivh mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm
警告：mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-compat-5.7.2################################# [100%]
[root@localhost soft]# rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
警告：mysql-community-client-5.7.26-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-5.7.26-1.e################################# [100%]
[root@localhost soft]# rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.26-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-server-5.7.26-1.e################################# [100%]

8.启动数据库

[root@localhost soft]# systemctl start mysqld

9.查看密码并且修改密码

[root@localhost soft]# cat /var/log/mysqld.log | grep password
2022-06-22T02:57:05.817302Z 1 [Note] A temporary password is generated for root@localhost: 6Vg;JxpfXDXI

[root@localhost soft]# 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.26

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 'Password@123';
Query OK, 0 rows affected (0.00 sec)

10.授权远程登陆

mysql> grant all privileges on *.* to 'root'@'%' identified by 'Password@123' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

11.修改配置文件，重启数据库

［client］
default-character-set = utf8mb4

[mysqld]
socket=/var/lib/mysql/mysql.sock
port=13306
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

［mysql］

default-character-set = utf8mb4

[root@localhost ~]# systemctl restart mysqld

创建数据库

mysql> create database care default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

12.创建同步用户

主节点A服务器：192.168.43.2 从节点B服务器：192.168.43.3

A服务器执行：
[root@localhost etc]# mysql -uroot -p

mysql> grant replication slave on *.* to 'replicate'@'192.168.43.3' identified by 'Password@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

从主机B测试连接主机A：

[root@localhost ~]# mysql -h192.168.43.2 --port=13306 -ureplicate -p

13.修改主几点my.cnf配置

在[mysqld]下添加如下配置
server_id=2
log_bin=mysql-bin
binlog-do-db=care
binlog-ignore-db=mysql,information_schema,performance_schema
log_bin_trust_function_creators=TRUE

重启数据库：

[root@localhost ~]# systemctl restart mysqld

查看节点信息：

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

14.从节点配置my.cnf

server_id=3
log_bin=mysql-bin
replicate-do-db=cadre_gs_bd
replicate-ignore-db=mysql,information_schema,performance_schema
log_bin_trust_function_creators=TRUE

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.43.2',master_port=13306,master_user='replicate',master_password='Password@123',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

master_log_file 和master_log_post 是查询主节点得出的；

启动备节点：

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.43.2
Master_User: replicate
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: care
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: ffc69bf3-f1d6-11ec-974e-000c2948fad7
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

15.测试主从

主节点插入数据：
mysql> use care;
Database changed
mysql> create table te(id int);
Query OK, 0 rows affected (0.03 sec)

从节点查看：

mysql> use care;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_care |
+----------------+
| te |
+----------------+
1 row in set (0.00 sec)

16.在主节点查看从节点的状态

mysql> show slave hosts;
+-----------+------+-------+-----------+--------------------------------------+
| Server_id | Host | Port  | Master_id | Slave_UUID                           |
+-----------+------+-------+-----------+--------------------------------------+
|         3 |      | 13306 |         2 | 08d543d7-f1d7-11ec-9807-000c29ff6bca |
+-----------+------+-------+-----------+--------------------------------------+
1 row in set (0.00 sec)

17.启停Slave进程

执行维护任务，或者出现意外情况，DBA可以手动控制Slave节点服务的启动和停止，主要是通过STOP SLAVE和START SLAVE 两个命令执行

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.43.2
                  Master_User: replicate
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000014
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: care

启动slave

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.43.2
                  Master_User: replicate
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000015
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Slave有两个线程服务组成：

IO_THREAD:负责读取Master端的二进制日志，并写入到本地的中继日志（relay-log）中。

SQL_THREAD：负责从本地的中继日志读取事件并执行。

这两个进程可以单独停止。

start slave sql_thread;

start slave io_thread;

posted @ 2022-06-26 21:17 中仕阅读(49) 评论(0) 收藏举报

刷新页面返回顶部

MySQL5.7主从搭建

公告