MySQL集群主从复制（基于position）

最新推荐文章于 2024-02-28 09:31:59 发布

程序姜

最新推荐文章于 2024-02-28 09:31:59 发布

阅读量256

点赞数

本文链接：https://blog.csdn.net/lurenjiang/article/details/106240461

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

环境准备

CentOS7

cat /etc/os-release 
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

MySQL5.7.28

配置Ip,hostname

IP:192.168.123.70/24

IP:192.168.123.71/24

IP:192.168.123.72/24

[user@localhost ~]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eno16777984: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:50:56:91:56:46 brd ff:ff:ff:ff:ff:ff
    inet 192.168.123.70/24 brd 192.168.123.255 scope global eno16777984
       valid_lft forever preferred_lft forever
    inet6 fe80::250:56ff:fe91:5646/64 scope link
       valid_lft forever preferred_lft forever

hostname:db01

hostname:db02

hostname:db03

[root@localhost user]# uname -a
Linux db01 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

修改主机名(root用户才有写的权限):

[root@localhost user]# vi /etc/hostname

清理历史环境

[root@db01 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.41-2.el7_0.x86_64
[root@db01 ~]# yum remove mariadb-libs -y

创建用户和组

[root@db01 ~]# useradd mysql -s /sbin/nologin
[root@db01 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) 组=1001(mysql)

创建相关目录

创建日志目录

mkdir -p /app/database/
mkdir -p /data/3306/
mkdir -p /binlog/3306/

设置权限

chown -R mysql.mysql /app/ /data/ /binlog

上传并解压MySQL软件

tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql

设置环境变量

[root@db01 database]# vi /etc/profile
#添加一行
export PATH=/app/database/mysql/bin:$PATH
[root@db01 database]# source /etc/profile
[root@db01 database]# mysql -V
mysql  Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) u                            sing  EditLine wrapper

初始化系统库表

[root@db01 database]# mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/
2020-05-19T06:58:05.679573Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-19T06:58:06.351750Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-05-19T06:58:06.424756Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-05-19T06:58:06.484401Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1768bc0d-999e-11ea-83d2-005056915646.
2020-05-19T06:58:06.485660Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-05-19T06:58:08.245682Z 0 [Warning] CA certificate ca.pem is self signed.
2020-05-19T06:58:08.411215Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

可能的报错

mysqld:error while loading shared libraries: libaio.so.1:
connot open shared object file:No such file or directory
#解决
yum install -y libaio-devel
#重新初始化系统库表
mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/
#重新初始化可能遇到的错误
[ERROR] --initialize specified but the data directory has files in it.
#解决
data directory has files in it 删了data/3306下的文件

配置文件设置

主节点配置

[root@db01 database]# cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
log-bin=mysql-bin-6
binlog-format=row
[mysql]
socket=/tmp/mysql.sock

从节点配置

[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=7
port=3306
socket=/tmp/mysql.sock
relay-log=mysql-relay-7
[mysql]
socket=/tmp/mysql.sock

[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=8
port=3306
socket=/tmp/mysql.sock
relay-log=mysql-relay-8
[mysql]
socket=/tmp/mysql.sock

重启MySQL服务生效配置文件

sudo service mysql restart

准备MySQL启动脚本

 cd /app/database/mysql/support-files/
 #拷贝mysql的启动脚本至系统软件管理目录中
 cp mysql.server /etc/init.d/mysqld
 service mysqld start
 chkconfig --add mysqld
 service mysqld stop
 sytemctl start mysqld

主从复制

关闭防火墙

[root@db01 etc]# firewall-cmd --state
running
[root@db01 etc]# systemctl stop firewalld.service

启动实例

sytemctl start mysqld

检查信息

server_id

[root@db01 etc]# mysql -S /tmp/mysql.sock -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
[root@db02 ~]# mysql -S /tmp/mysql.sock -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@db03 ~]# mysql -S /tmp/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+

或者在mysql终端执行(以下mysql -S /tmp/mysql.sock -e ""同理)

select @@server_id

主库binlog

[root@db01 ~]# mysql -S /tmp/mysql.sock -e "select @@log_bin";
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

主库建立复制用户

[root@db01 ~]# mysql -S /tmp/mysql.sock -e "grant replication slave,replication client on *.*to'repl'@'%' identified by '123456'";

[root@db01 ~]# mysql -S /tmp/mysql.sock -e "select user,host from mysql.user";   				   +---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

查看主库状态

重点查看二进制文件(File: mysql-bin-6.000004)和起点位置(Position: 154)

[root@db01 ~]# mysql -S /tmp/mysql.sock -e "show master status \G"
*************************** 1. row ***************************
             File: mysql-bin-6.000005
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

告知从节点复制信息

[root@db02 ~]# mysql -S /tmp/mysql.sock

mysql> change master to 
　　> master_host='192.168.123.70',
　　> master_port=3306,
　　> master_user='repl',
　　> master_password='123456',
　　> master_log_file='mysql-bin-6.000005',
　　> master_log_pos=154;
　
　  >start slave;
　  
[root@db03 ~]# mysql -S /tmp/mysql.sock

mysql> change master to 
　　> master_host='192.168.123.70',
　　> master_port=3306,
　　> master_user='repl',
　　> master_password='123456',
　　> master_log_file='mysql-bin-6.000005',
　　> master_log_pos=154;
　　
　　>start slave;

查看主从状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

若不为Yes看下面的错误信息提示。

Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.123.70
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin-6.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-7.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin-6.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 527
              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: 6
                  Master_UUID: 1768bc0d-999e-11ea-83d2-005056915646
             Master_Info_File: /data/3306/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:

程序姜

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL集群主从复制（基于position）

环境准备CentOS7cat /etc/os-release NAME="CentOS Linux"VERSION="7 (Core)"ID="centos"ID_LIKE="rhel fedora"VERSION_ID="7"PRETTY_NAME="CentOS Linux 7 (Core)"ANSI_COLOR="0;31"CPE_NAME="cpe:/o:centos:centos:7"HOME_URL="https://www.centos.org/"BUG_REPORT_
复制链接

扫一扫