培森的Blog Mysql mysql主从复制传统模式搭建

mysql主从复制传统模式搭建

作者: 培森 发布: 2023年3月16日 1147阅读 评论关闭

mysql主从复制传统模式搭建 1.软件环境准备 #1)查看系统环境 ##<==操作系统版本。 [roo…

mysql主从复制传统模式搭建

1.软件环境准备

#1)查看系统环境
##<==操作系统版本。
[root@localhost.localdomain /usr/local/mysql]$ cat /etc/redhat-release 
CentOS Linux release 7.8.2003 (Core)   ##<==操作系统版本。

#2)关闭selinux:
setenforce 0 #临时关闭selinux
getenforce #查看关闭selinux
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
#永久关闭：输入命令vi /etc/selinux/config，将SELINUX=enforcing改为SELINUX=disabled，然后保存退出
#3)卸载mariadb依赖包
yum remove mariadb-libs -y #<==卸载系统已经安装的mariadb依赖包。

#4)安装相关依赖包
yum install ncurses ncurses-devel libaio-devel openssl openssl-devel -y

#5)临时及永久关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

#6)安装依赖
rpm -ivh *.rpm --force --nodeps
  • 相关依赖包
glibc-2.17-326.el7_9.i686.rpm               libstdc++-4.8.5-44.el7.i686.rpm
glibc-2.17-326.el7_9.x86_64.rpm             libverto-0.2.5-4.el7.i686.rpm
glibc-common-2.17-326.el7_9.x86_64.rpm      libverto-devel-0.2.5-4.el7.x86_64.rpm
glibc-devel-2.17-326.el7_9.x86_64.rpm       ncurses-5.9-14.20130511.el7_4.x86_64.rpm
glibc-headers-2.17-326.el7_9.x86_64.rpm     ncurses-devel-5.9-14.20130511.el7_4.i686.rpm
keyutils-libs-1.5.8-3.el7.i686.rpm          ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
keyutils-libs-devel-1.5.8-3.el7.x86_64.rpm  ncurses-libs-5.9-14.20130511.el7_4.i686.rpm
krb5-devel-1.15.1-55.el7_9.i686.rpm         nspr-4.34.0-3.1.el7_9.x86_64.rpm
krb5-devel-1.15.1-55.el7_9.x86_64.rpm       nss-softokn-freebl-3.79.0-4.el7_9.i686.rpm
krb5-libs-1.15.1-55.el7_9.i686.rpm          nss-softokn-freebl-3.79.0-4.el7_9.x86_64.rpm
krb5-libs-1.15.1-55.el7_9.x86_64.rpm        nss-util-3.79.0-1.el7_9.x86_64.rpm
libaio-0.3.109-13.el7.i686.rpm              openssl-1.0.2k-25.el7_9.x86_64.rpm
libaio-devel-0.3.109-13.el7.i686.rpm        openssl-devel-1.0.2k-25.el7_9.i686.rpm
libaio-devel-0.3.109-13.el7.x86_64.rpm      openssl-devel-1.0.2k-25.el7_9.x86_64.rpm
libcom_err-1.42.9-19.el7.i686.rpm           openssl-libs-1.0.2k-25.el7_9.i686.rpm
libcom_err-devel-1.42.9-19.el7.x86_64.rpm   openssl-libs-1.0.2k-25.el7_9.x86_64.rpm
libgcc-4.8.5-44.el7.i686.rpm                pcre-8.32-17.el7.i686.rpm
libkadm5-1.15.1-55.el7_9.i686.rpm           pcre-devel-8.32-17.el7.x86_64.rpm
libkadm5-1.15.1-55.el7_9.x86_64.rpm         zlib-1.2.7-20.el7_9.i686.rpm
libselinux-2.5-15.el7.i686.rpm              zlib-1.2.7-20.el7_9.x86_64.rpm
libselinux-devel-2.5-15.el7.x86_64.rpm      zlib-devel-1.2.7-20.el7_9.i686.rpm
libsepol-2.5-10.el7.i686.rpm                zlib-devel-1.2.7-20.el7_9.x86_64.rpm
libsepol-devel-2.5-10.el7.x86_64.rpm

2.开始安装mysql

此处采用二进制安装

1) 建立MySQL用户账号

首先以root身份登录到Linux系统中，然后执行如下命令创建mysql用户账号：

useradd mysql -s /sbin/nologin -M #<==创建mysql用户
id mysql

mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

2) 下载、解压mysql软件包，并做好软链接mysql到/usr/local。

[root@localhost.localdomain /db_yilai]$ tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

[root@localhost.localdomain /db_yilai]$ mv mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql

[root@localhost.localdomain /usr/local/mysql]$ ls
bin   include  LICENSE  README  support-files
docs  lib      man      share

3)创建MySQL数据目录、配置文件(/etc/my.cnf)并授权

  • 主库配置文件
[root@localhost.localdomain /usr/local/mysql]$ cat /etc/my.cnf [mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/23306/data
port=23306 
socket=/tmp/mysql.sock 
#开启binlog
log_bin = /data/23306/data/binlog
binlog-format=ROW       #选择ROW模式
server-id = 8           #最好选IP最后一个字符
#日志过期时间
expire_logs_days=15     #日志过期时间
[client]
socket=/tmp/mysql.sock
  • 从库配置文件
[root@localhost.localdomain /usr/local/mysql]$ cat /etc/my.cnf [mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/23306/data
port=23306 
socket=/tmp/mysql.sock 
server-id = 9
[client]
socket=/tmp/mysql.sock
  • 创建MySQL数据目录并授权
[root@localhost.localdomain ~]$ mkdir -p /data/23306/data
[root@localhost.localdomain ~]$ chown -R mysql.mysql /data
[root@localhost.localdomain ~]$ ls /data
23306
[root@localhost.localdomain ~]$ ll /data
总用量 0
drwxr-xr-x 3 mysql mysql 18 2月  15 22:28 23306

4)配制PATH环境变量，并初始化MySQL数据库

#1)配置PATH环境变量
[root@localhost.localdomain ~]$ echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile
#2)加载生效并检查
[root@localhost.localdomain ~]$ source /etc/profile
[root@localhost.localdomain ~]$ echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
  • 初始化
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql  --basedir=/usr/local/mysql --datadir=/data/23306/data
  • 初始化结果
[root@localhost.localdomain /usr/local/mysql]$ ll  /data/23306/data/
总用量 122968
-rw-r----- 1 mysql mysql       56 2月  15 22:36 auto.cnf
-rw-r----- 1 mysql mysql      177 2月  15 22:36 binlog.000001
-rw-r----- 1 mysql mysql      154 2月  15 22:37 binlog.000002
-rw-r----- 1 mysql mysql       62 2月  15 22:37 binlog.index
-rw------- 1 mysql mysql     1676 2月  15 22:36 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 2月  15 22:36 ca.pem
-rw-r--r-- 1 mysql mysql     1112 2月  15 22:36 client-cert.pem
-rw------- 1 mysql mysql     1676 2月  15 22:36 client-key.pem
-rw-r----- 1 mysql mysql      436 2月  15 22:36 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 2月  15 22:37 ibdata1
-rw-r----- 1 mysql mysql 50331648 2月  15 22:37 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月  15 22:36 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 2月  15 22:37 ibtmp1
-rw-r----- 1 mysql mysql     3707 2月  15 22:37 localhost.localdomain.err
-rw-r----- 1 mysql mysql        6 2月  15 22:37 localhost.localdomain.pid
drwxr-x--- 2 mysql mysql     4096 2月  15 22:36 mysql
drwxr-x--- 2 mysql mysql     8192 2月  15 22:36 performance_schema
-rw------- 1 mysql mysql     1676 2月  15 22:36 private_key.pem
-rw-r--r-- 1 mysql mysql      452 2月  15 22:36 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 2月  15 22:36 server-cert.pem
-rw------- 1 mysql mysql     1680 2月  15 22:36 server-key.pem
drwxr-x--- 2 mysql mysql     8192 2月  15 22:36 sys
  • 启动
sh  /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &   #后台运行
  • 登录
mysql -uroot -P'23306' -S /tmp/mysql.sock

10022 23306 21521

主从复制集群搭建

3.复制前提

  • 准备两个mysql实例

    • 20.164.2.8 （master）
    • 20.164.2.9 (slave)

  • 确认主库开启binlog日志

    • 
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

    mysql> select @@log_bin_basename;
    +-------------------------+
    | @@log_bin_basename |
    +-------------------------+
    | /data/23306/data/binlog |
    +-------------------------+
    1 row in set (0.00 sec)

    mysql> show variables like 'log_%';
    +----------------------------------------+-------------------------------+
    | Variable_name | Value |
    +----------------------------------------+-------------------------------+
    | log_bin | ON |
    | log_bin_basename | /data/23306/data/binlog |
    | log_bin_index | /data/23306/data/binlog.index |
    | log_bin_trust_function_creators | OFF |
    | log_bin_use_v1_row_events | OFF |
    | log_builtin_as_identified_by_password | OFF |
    | log_error | ./localhost.localdomain.err |
    | log_error_verbosity | 3 |
    | log_output | FILE |
    | log_queries_not_using_indexes | OFF |
    | log_slave_updates | OFF |
    | log_slow_admin_statements | OFF |
    | log_slow_slave_statements | OFF |
    | log_statements_unsafe_for_binlog | ON |
    | log_syslog | OFF |
    | log_syslog_facility | daemon |
    | log_syslog_include_pid | ON |
    | log_syslog_tag | |
    | log_throttle_queries_not_using_indexes | 0 |
    | log_timestamps | UTC |
    | log_warnings | 2 |
    +----------------------------------------+-------------------------------+
    21 rows in set (0.00 sec)

  • 确保主从环境server_id和server_uuid不同

    • 主（20.164.2.8）

    • mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 294b415c-ad3e-11ed-bd4e-047bcbb0faac |
+--------------------------------------+
1 row in set (0.00 sec)

    • 从 (20.164.2.9)

    • mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 26ef1f23-ad3e-11ed-b3ac-047bcbb0fc08 |
+--------------------------------------+
1 row in set (0.00 sec)

  • 确保两边都执行reset master;让环境干净，减少干扰
    特别是防止GTID影响,取消GTID,编辑/etc/my.cnf

    gtid

    gtid_mode=ON

    enforce_gtid_consistency=ON

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

4.搭建过程

1）主库创建复制用户并授权

#创建repl用户，密码123
mysql> create user repl@'20.164.2.%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.00 sec)
#授权
mysql> grant replication slave on *.* to repl@'20.164.2.%';
Query OK, 0 rows affected (0.00 sec)
#replication slave 仅用来复制权限。
#主库看一下是否创建用户成功
mysql> select user,host from mysql.user;
+---------------+------------+
| user          | host       |
+---------------+------------+
| repl          | 20.164.2.% |
| mysql.session | localhost  |
| mysql.sys     | localhost  |
| root          | localhost  |
+---------------+------------+
4 rows in set (0.00 sec)

2）主数据库锁表

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

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

image-20230215154730299

3) 备份主库

#1)先创建备份目录
mkdir -p /server/mq-bak
[root@localhost.localdomain /usr/local/mysql/bin]$ mysqldump -uroot -A --single-transaction -R -E --triggers --max_allowed_packet=64M|gzip>/server/mq-bak/master_`date +%F`.sql.gz
#冲库也需要创建备份目录
mkdir -p /server/mq-bak

[root@localhost.localdomain /server/mq-bak]$ ls
master_2023-02-15.sql.gz

4）拷贝到数据到从库

[root@localhost.localdomain /server/mq-bak]$ scp master_2023-02-15.sql.gz  root@20.164.2.9:`pwd`
#从库做检查
[root@localhost.localdomain /usr/local/mysql]$ ls /server/mq-bak/
master_2023-02-15.sql.gz

5）从库恢复数据

[root@localhost.localdomain /server/mq-bak]$ gzip -d master_2023-02-15.sql.gz 
[root@localhost.localdomain /server/mq-bak]$ ls
master_2023-02-15.sql

#mysql -uroot </server/mq-bak/master_`date +%F`.sql 
[root@localhost.localdomain /server/mq-bak]$ mysql -uroot </server/mq-bak/master_2023-02-15.sql 

####如果报错,删除/server/mq-bak/master_`date +%F`.sql 第一行,--master-data被弃用提示.

6) 指定连接参数到位置点

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='20.164.2.8',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123',
    ->   MASTER_PORT=23306,
    ->   MASTER_LOG_FILE='binlog.000001',       ##这里要对上show master status;文件
    ->   MASTER_LOG_POS=154,                        ##这里要对上show master status;位置点
    ->   MASTER_CONNECT_RETRY=10;       
Query OK, 0 rows affected, 2 warnings (0.01 sec)

CHANGE MASTER TO
  MASTER_HOST='20.164.2.8',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=23306,
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;

7)主数据库解锁(20.164.2.8)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

8)从库启动专用复制线程 start slave;

mysql> start slave;           ##开启同步开关
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.164.2.8
                  Master_User: repl
                  Master_Port: 23306
                Connect_Retry: 10
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes       #成功标准两个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: 613
              Relay_Log_Space: 528
              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: 8
                  Master_UUID: 294b415c-ad3e-11ed-bd4e-047bcbb0faac
             Master_Info_File: /data/23306/data/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)

9）进行测试

#主库创建test库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

#从库做验证
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

2.4数据库服务器配置文件

  • 20.164.2.8master
[root@localhost.localdomain /server/mq-bak]$ cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/23306/data
port=23306 
socket=/tmp/mysql.sock 
#开启binlog
log_bin = /data/23306/data/binlog
binlog-format=ROW       #选择ROW模式
server-id = 8           #最好选IP最后一个字符
#禁用DNS解析
innodb_buffer_pool_size=4G
#直接缓存磁盘加速读
innodb_flush_method=O_DIRECT
#吞吐量 sas磁盘最高2000, ssd8000
#这个值很重要，它对读无效，对写很有决定意义。
innodb_io_capacity_max=2000
innodb_io_capacity=1000
innodb_lru_scan_depth = 800
#每30天清理日志
expire_logs_days=30    #日志过期时间
max_connections=200
#设置服务器字符集为UTF-8
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#区分大小写0  不区分1
lower_case_table_names=1
[client]
socket=/tmp/mysql.sock
#设置客户端字符集为UTF-8
  • 20.164.2.9从库配置文件
[root@localhost.localdomain /server/mq-bak]$ cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/23306/data
port=23306 
socket=/tmp/mysql.sock 
server-id = 9
############
#禁用DNS解析
innodb_buffer_pool_size=4G
#直接缓存磁盘加速读
innodb_flush_method=O_DIRECT
#吞吐量 sas磁盘最高2000, ssd8000
#这个值很重要，它对读无效，对写很有决定意义。
innodb_io_capacity_max=2000
innodb_io_capacity=1000
innodb_lru_scan_depth = 800
#每30天清理日志
expire_logs_days=30
max_connections=200
#设置服务器字符集为UTF-8
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#区分大小写0  不区分1
lower_case_table_names=1
[client]
socket=/tmp/mysql.sock
#设置客户端字符集为UTF-8
default-character-set=utf8

给集群添加一台master节点

1.准备

  • 关闭selinux
  • 防火墙加白名单，master1节点也要加白名单 firewall-cmd --reload
  • yum remove mariadb-libs -y 卸载原来数据库依赖
  • 安装数据库前期依赖

2.安装mysql

  • 新建mysql用户

首先以root身份登录到Linux系统中，然后执行如下命令创建mysql用户账号：

useradd mysql -s /sbin/nologin -M #<==创建mysql用户
id mysql
  • 下载、解压mysql软件包，并做好软链接mysql到/usr/local。
tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql

3.创建数据库配置文件my.cnf

  • masterB 节点
[root@keyan-test /usr/lib/systemd/system]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/23306/data
port=23306 
socket=/tmp/mysql.sock 
#开启binlog
log_bin = /data/23306/data/binlog
binlog-format=ROW       #选择ROW模式
server-id = 4           #最好选IP最后一个字符
#禁用DNS解析
innodb_buffer_pool_size=4G
#直接缓存磁盘加速读
innodb_flush_method=O_DIRECT
#吞吐量 sas磁盘最高2000, ssd8000
#这个值很重要，它对读无效，对写很有决定意义。
innodb_io_capacity_max=2000
innodb_io_capacity=1000
innodb_lru_scan_depth = 800

#主主设置
binlog-do-db=mail     #需要从主库-2同步的数据库1
binlog-do-db=meeting   #需要从主库-2同步的数据库2
binlog-do-db=nsso
binlog-do-db=ywdata

auto-increment-increment=2
auto-increment-offset=2
log-slave-updates=1

#每30天清理日志
expire_logs_days=30    #日志过期时间
max_connections=200
#设置服务器字符集为UTF-8
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#区分大小写0  不区分1
lower_case_table_names=1
#SQL模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
socket=/tmp/mysql.sock
#设置客户端字符集为UTF-8
  • masterA节点
[root@keyan-test /usr/lib/systemd/system]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/23306/data
port=23306 
socket=/tmp/mysql.sock 
#开启binlog
log_bin = /data/23306/data/binlog
binlog-format=ROW       #选择ROW模式
server-id = 8           #最好选IP最后一个字符
#禁用DNS解析
innodb_buffer_pool_size=4G
#直接缓存磁盘加速读
innodb_flush_method=O_DIRECT
#吞吐量 sas磁盘最高2000, ssd8000
#这个值很重要，它对读无效，对写很有决定意义。
innodb_io_capacity_max=2000
innodb_io_capacity=1000
innodb_lru_scan_depth = 800

#主主设置
binlog-do-db=mail     #需要从主库-2同步的数据库1
binlog-do-db=meeting   #需要从主库-2同步的数据库2
binlog-do-db=nsso
binlog-do-db=ywdata

auto-increment-increment=2
auto-increment-offset=1
log-slave-updates=1

#每30天清理日志
expire_logs_days=30    #日志过期时间
max_connections=200
#设置服务器字符集为UTF-8
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#区分大小写0  不区分1
lower_case_table_names=1
#SQL模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
socket=/tmp/mysql.sock
#设置客户端字符集为UTF-8
  • 创建MySQL数据目录并授权
mkdir -p /data/23306/data
chown -R mysql.mysql /data
  • 配置环境变量，并初始化数据库
#1)配置PATH环境变量
[root@localhost.localdomain ~]$ echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile
#2)加载生效并检查
[root@localhost.localdomain ~]$ source /etc/profile
[root@localhost.localdomain ~]$ echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
  • 初始化
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql  --basedir=/usr/local/mysql --datadir=/data/23306/data
  • 启动
sh  /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &   #后台运行
  • 登录
mysql -uroot -p -P'23306' -S /tmp/mysql.sock

#修改密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
use mysql;
update user set Host = '%' where Host='localhost' and User = 'root';

flush privileges;

4.数据库启动文件

[root@keyan-test /usr/lib/systemd/system]# cat mysql.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1
#PrivateTmp=false

5.搭建过程

1）masterA 创建repl用户并授权

#由于上次搭建主从masterA节点已创建所已本次不用创建
#masterB节点需要创建此用户
create user repl@'20.164.2.%' identified with mysql_native_password by '123';
#授权
grant replication slave on *.* to repl@'20.164.2.%';
#查看repl用户是否创建成功
select user,host from mysql.user;

flush privileges;

2）masterA节点锁表

flush table with read lock;

show master status;

3）备份主库

#1)先创建备份目录
mkdir -p /server/mq-bak
[root@localhost.localdomain /usr/local/mysql/bin]$ mysqldump -uroot -p'sgeri.2023!' -A --single-transaction -R -E --triggers --max_allowed_packet=64M|gzip>/server/mq-bak/master_`date +%F`.sql.gz
#修改端口后
#mysqldump -uroot -p'sgeri.2023!' -P'23306' -A --single-transaction -R -E --triggers --max_allowed_packet=64M|gzip>/server/mq-bak/master_`date +%F`.sql.gz

#masterB节点库也需要创建备份目录
mkdir -p /server/mq-bak

4）拷贝数据到masterB节点

5) masterB节点库恢复数据

gzip -d master_2023-02-15.sql.gz

mysql -uroot -p'sgeri.2023!' -P'23306' </server/mq-bak/master_2023-03-06.sql

6) 指定连接参数到位置点

  • masterA节点
mysql> flush table with read lock;
Query OK, 0 rows affected (0.01 sec)

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


CHANGE MASTER TO
  MASTER_HOST='20.164.2.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=23306,
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=1360,
  MASTER_CONNECT_RETRY=10;
  • masterB节点开启复制线程
start slave;
show slave status\G     #检查是否出两个yes
  • 如果复制线程没问题(masterA节点解锁)
unlock tables;

6.对masterA对masterB节点进行数据库同步备份服务

  • masterA节点因为修改了配置文件，所以需要重启数据库

因为双主模式要进行相互复制，所以在masterA节点里添加了

#主主设置
binlog-do-db=mail     #需要从主库-2同步的数据库1
binlog-do-db=meeting   #需要从主库-2同步的数据库2
binlog-do-db=nsso
binlog-do-db=ywdata

auto-increment-increment=2
auto-increment-offset=1
log-slave-updates=1

#SQL模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

1.masterB节点创建repl用户并授权（如若创建授权跳过此步）

create user repl@'20.164.2.%' identified with mysql_native_password by '123';
#授权
grant replication slave on *.* to repl@'20.164.2.%';
#查看repl用户是否创建成功
select user,host from mysql.user;
#刷新
flush privileges;

2.masterA节点指定连接参数到位置点

  • masterB
flush table with read lock;

show master status;
  • masterA 指定连接参数到masterB
CHANGE MASTER TO
  MASTER_HOST='20.164.2.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=23306,
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=1360,
  MASTER_CONNECT_RETRY=10;
  • masterA开启复制线程
start slave;
show slave status\G     #检查是否出两个yes
  • 对masterB解锁

在masterB节点双库同步备份的ywdata库里创建test表，观察masterA节点是否会同步到ywdata库

同理，在masterA节点ywdata库里创建test1表，观察masterA节点是否会同步到ywdata库

如若测试成功可进行keepalived搭建

mysql 双主模式高可用搭建

1）keepalived 安装启动（masterA、masterB）

masterA+B节点创建keepalived的rpm包存放目录

mkdir keepalived

rpm -ivh *.rpm --force --nodeps

2)keepalived 配置

  • master1配置
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived

bal_defs {
  router_id LVS_DEVEL
}

vrrp_sync_group VG_1 {
  group {
    VI_1
  }
}

vrrp_instance VI_1 {
  state BACKUP  #两个机器都是这个值，不要设置为 MASTER,使用下面的 priority 来控制 
  interface ens3f0  #通过 ifconfig 查看
  virtual_router_id 51
  priority 100 #优先级，另一台机器设置为 90 
  advert_int 1
  nopreempt  # 另一台优先级低的机器不设置此参数！
  authentication {
    auth_type root
    auth_pass root
  }
  virtual_ipaddress {
   20.164.2.99 # 这个是VIP
  }
}

virtual_server 20.164.2.99 23306 {  # 这个是VIP
  delay_loop 2
  lb_algo rr
  lb_kind DR
  persistence_timeout 50
  protocol TCP

  real_server 20.164.2.8 23306 {   # 这是机器的IP地址
    weight 3
    notify_down /data/23306/mysql.sh
    TCP_CHECK {
      connect_timeout 5
      nb_get_retry 3
      delay_before_retry 3
      connect_port 23306
    }
  }
}
  • masterB配置
[root@keyan-test ~/keepalived]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

bal_defs {
  router_id LVS_DEVEL
}

vrrp_sync_group VG_1 {
  group {
    VI_1
  }
}

vrrp_instance VI_1 {
  state BACKUP  #两个机器都是这个值，不要设置为 MASTER,使用下面的 priority 来控制 
  interface ens3f0  #通过 ifconfig 查看
  virtual_router_id 51
  priority 90 #优先级，另一台机器设置为 90 
  advert_int 1
#  nopreempt  # 另一台优先级低的机器不设置此参数！
  authentication {
    auth_type root
    auth_pass root
  }
  virtual_ipaddress {
   20.164.2.99 # 这个是VIP
  }
}

virtual_server 20.164.2.99 23306 {  # 这个是VIP
  delay_loop 2
  lb_algo rr
  lb_kind DR
  persistence_timeout 50
  protocol TCP

  real_server 20.164.2.10 23306 {   # 这是机器的IP地址
    weight 3
    notify_down /data/23306/mysql.sh
    TCP_CHECK {
      connect_timeout 5
      nb_get_retry 3
      delay_before_retry 3
      connect_port 23306
    }
  }
}
  • 创建一个关闭 keepalived 的脚本，用于在 mysql 无法访问时，关闭 keepalived 以释放 VIP
cat /data/23306/mysql.sh
#!/bin/bash
pkill keepalived
  • 将文件设置为可执行文件：
chmod +x /data/23306/mysql.sh
  • keepalived启动并设置为开机自启
systemctl enable --now keepalived
tail -f /var/log/messages
  • 查看vip在那个节点
ip addr

3)测试

  • 通过vip和IP连接两个主库做测试
  • 在主库masterA ywdata添加test2表在masterB做查看
  • 在主库masterB ywdata添加test3表在master做查看
  • stop masterA 查看数据会不会vip飘到masterB
  • 然后重启masterA stop masterB vip飘到masterA
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%';
#在主机MySQL里执行授权命令
#GRANT REPLICATION SLAVE ON *.* TO 'root'@'%'  IDENTIFIED BY '123456';

#flush privileges;

CHANGE MASTER TO
  MASTER_HOST='10.0.0.213',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=23306,
  MASTER_LOG_FILE='binlog.000002',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
本文来自网络，不代表培森的Blog立场，转载请注明出处：https://blog.xupeisen.com/archives/1049
打赏 884赞

作者: 培森

相关文章

联系我们

联系我们

13262951234

在线咨询: QQ交谈

邮箱: admin@xupeisen.com

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部