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

mysql主从复制传统模式搭建

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

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
本文来自网络，不代表培森的Blog立场，转载请注明出处：https://blog.xupeisen.com/archives/720
打赏 333赞

作者: 培森

相关文章

联系我们

联系我们

13262951234

在线咨询: QQ交谈

邮箱: admin@xupeisen.com

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

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

微信扫一扫关注我们

关注微博
返回顶部