centos7下mysql8 主从安装配置 centos7下

centos7下mysql8 主从安装配置

centos7 版本下载

镜像版本要求centos7的小版本；下载地址建议从阿里云镜像站下载地址

目前官方镜像分为如下类型：

• CentOS-7-x86_64-DVD-2009.iso 标准安装版，一bai般下载这个就可以了。
• CentOS-7-x86_64-NetInstall-2009.iso 网络安装镜像du。
• CentOS-7-x86_64-Everything-2009.iso 对完整版安装盘的软件进行补充集成所有软
• CentOS-7-x86_64-Minimal-2009.iso 精简版本，包含核心组件。
  这边我使用CentOS-7-x86_64-DVD-2009.iso 安装centos7操作系统

mysql 8.0.20下载

mysql版本要求 mysql8.0.20;下载地址建议从官方镜像站下载地址：mysql8.0.20。

mysql8.0.20 二进制安装

总体安装步骤概述如下：

1. 操作系统等相关配置设置
2. 安装依赖包
3. 创建用户
4. 修改配置文件、创建相关数据目录、日志目录等并授权
5. 运行安装命令，启动数据库
6. 配置环境变量、服务等（看需要）

操作系统配置和设置

查看CPU、内存、SSL版本、硬盘大小、关闭防火墙和selinux

查看物理CPU个数

sh复制代码[root@localhost ~]# cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
1

查看逻辑CPU的个数

sh复制代码[root@localhost ~]# cat /proc/cpuinfo| grep "processor"| wc -l
1

查看CPU信息（型号）

sh复制代码[root@localhost ~]# cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
      1  Intel(R) Core(TM) i5-8265U CPU @ 1.60GHz

查看内存：

sh复制代码[root@localhost ~]# cat /proc/meminfo
MemTotal:        1863076 kB
MemFree:           81492 kB
MemAvailable:      48092 kB
......

查看ssl版本

sh复制代码[root@localhost ~]# openssl version
OpenSSL 1.0.2k-fips  26 Jan 2017

查看硬盘大小 尽量将/根目录占用所有磁盘目录的80% ；home目录50-100g即可

sh复制代码[root@localhost ~]# df -lh
Filesystem               Size  Used Avail Use% Mounted on
devtmpfs                 898M     0  898M   0% /dev
tmpfs                    910M     0  910M   0% /dev/shm
tmpfs                    910M  9.6M  901M   2% /run
tmpfs                    910M     0  910M   0% /sys/fs/cgroup
/dev/mapper/centos-root   17G  5.4G   12G  32% /
/dev/sda1               1014M  150M  865M  15% /boot
tmpfs                    182M     0  182M   0% /run/user/0

关闭防火墙

sh复制代码systemctl stop firewalld.service
systemctl disable firewalld.service

修改selinux,将SELINUX=enforcing 改为SELINUX=disabled

sh复制代码[root@localhost ~]# vi /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

sh复制代码# 不重启机器生效
setenforce 0

修改文件限制等

sh复制代码[root@localhost ~]# vi /etc/security/limits.conf
hard nofile 65535
soft nofile 65535
ulimit -n 65535

sh复制代码[root@localhost ~]# vi /etc/sysctl.conf

# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

fs.aio-max-nr = 1048576
fs.file-max = 6553600
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.tcp_sack = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.netdev_max_backlog = 262144
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_synack_retries = 1
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_keepalive_time = 30
net.ipv4.ip_local_port_range = 1024 65000


# 生效
sysctl – p

安装mysql组件包

arduino复制代码yum install -y epel-release wget
yum install -y glibc gcc gcc-c++ openssl-devel autoconf automake cmake bison make ncurses-devel numactl numactl-devel
yum install -y libtool-ltdl-devel** zlib* libxml* fiex**
yum install -y libaio libaio-devel libmcrypt libmcrypt-devel mcrypt mhash**

创建用户和用户文件夹

复制代码groupadd mysql
useradd -r -g mysql mysql

将下载好的安装包上传到服务器执行目录，也可以直接用wget下载软件包

url复制代码https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz**](https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

sh复制代码# 解压
tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
# 移动到/user/local 目录
mv mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql
# 添加环境变量
cat >>/etc/profile << EOF
export PATH=$PATH:/usr/local/mysql/bin
EOF
# 环境变量生效
source /etc/profile
# 创建mysql安装所需文件夹
mkdir -p /data/mysql/tmp
mkdir -p /data/mysql/data
mkdir -p /data/mysql/etc
mkdir -p /data/mysql/logs
# 授予文件夹mysql权限
chown -R mysql:mysql /data/mysql

修改配置文件

yml复制代码[mysqld]
########basic settings########
server-id = 112             #建议和服务器ip保持一致 例如192.168.1.66  则此处写66
port = 3306
user = mysql
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql/data      #根据实际情况修改,建议和程序分离存放
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 512M
tmp_table_size = 512M
tmpdir = /data/mysql/tmp
pid-file = /data/mysql/tmp/mysqld.pi
socket = /tmp/mysql.sock
max_allowed_packet = 1024M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
lower_case_table_names=1      #大小写不敏感，linux专属 window服务器直接注释掉
default_time_zone='+8:00'
########log settings########
log_error =/data/mysql/logs/mysqld.log            #根据实际情况修改建议和datadir 位于不同物理磁盘
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log   #根据实际情况修改建议和datadir 位于不同物理磁盘
#log_queries_not_using_indexes = 1 #调试数据库的时候打开，此选项将记录所有没走索引的sql语句，不管执行快慢
log_slow_admin_statements = 1
log_slow_slave_statements = 1
#log_throttle_queries_not_using_indexes = 10  #设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
binlog_expire_logs_seconds=10800                 #如果log_bin 所在磁盘空间充足；建议调整至25200
long_query_time = 2
min_examined_row_limit = 100


########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = /data/mysql/logs/mysql_binlog   #根据实际情况修改建议和datadir 位于不同物理磁盘
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log = /data/mysql/logs/relay.log    #根据实际情况修改建议和datadir 位于不同物理磁盘
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors

########innodb settings########
innodb_page_size = 16384  #兼容老系统
innodb_buffer_pool_size = 14G    #根据实际情况修改 一般为服务器可用内存的80%
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /data/mysql/logs/   #根据实际情况修改建议和datadir 位于不同物理磁盘
innodb_flush_neighbors = 1
innodb_log_file_size = 512M               #根据实际情况修改
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_read_io_threads=24                #服务器cpu逻辑核数的60% 取整数 例如你是32核 建议调整到19
innodb_write_io_threads=24               #服务器cpu逻辑核数的60% 取整数 例如你是32核 建议调整到19

########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin      #根据实际情况修改
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
[mysqld-8.0]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
default_authentication_plugin = 'mysql_native_password'
log_bin_trust_function_creators=1

安装和启动数据库

指定本实例的配置文件进行安装

建议写全路径运行命令进行安装

sh复制代码/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

查看日志

bash复制代码vi /data/mysql/logs/mysqld.log

此时可以看到临时密码，并查看是否有错误产生:A temporary password is generated for root@localhost: 密码

ini复制代码[root@localhost ~]# vi /data/mysql/logs/mysqld.log

2021-11-22T11:36:07.179120+08:00 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process 2503
2021-11-22T11:36:07.216846+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-11-22T11:36:11.364494+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-11-22T11:36:11.697981+08:00 0 [Warning] [MY-013501] [Server] Ignoring --plugin-load[_add] list as the server is running with --initialize(-insecure).
2021-11-22T11:36:12.605450+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: VZico+qE8l0M

启动数据库

• 方法1:以服务模式启动数据库 建议用此方式启动。

sh复制代码cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
service mysqld start

• 启动数据库，并在后台运行（执行下面命令时多次回车，专为小白而备注）

bash复制代码/usr/local/mysql/bin/mysqld_safe  --defaults-file=/etc/my.cnf &

查看数据库进程

sh复制代码[root@localhost ~]# ps -ef | grep mysql
root       1901      1  0 15:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/tmp/mysqld.pi
mysql      2800   1901  1 15:59 pts/0    00:00:56 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/logs/mysqld.log --pid-file=/data/mysql/tmp/mysqld.pi --socket=/tmp/mysql.sock --port=3306
root       5569   1816  0 16:54 pts/0    00:00:00 grep --color=auto mysql

修改mysql默认密码

登录数据库修改root密码。安装按成必须修改随机root密码，不然无法操作其他内容

sh复制代码[root@localhost ~]#/usr/local/mysql/bin/mysql -u root -p '初始生成密码' -S /tmp/mysql.sock
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '要修改的密码';

mysql8主从配置

1. 将上面安装好mysql的虚拟复制1台（注意修改ip）
2. 修改master和slave my.cnf文件

sh复制代码#建议和服务器ip保持一致,防止重复
server-id = 112

3. 重命名auto.cnf文件（auto.cnf默认在mysql的data目录下）

sh复制代码mv /data/mysql/data/auto.cnf /data/mysql/data/auto.cnf_bak

4. 重启master服务

sh复制代码service mysqld restart

5. 为从节点创建一个登陆用户 repl

sh复制代码[root@localhost ~]# mysql -u root -p
[这里输入mysql链接密码]
mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'Ron_master_1';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

这里注意‘repl’@’%’中 % 不要写ip

6. 查看master节点状态

sql复制代码[root@localhost ~]# mysql -u root -p
[这里输入mysql链接密码]
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                   |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| mysql_binlog.000010 |     1304 |              |                  | 56b7da5a-4b45-11ec-ae40-000c2964986e:1-14,
fef0443e-4b5d-11ec-bf78-000c2964986e:1-5 |
+---------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这里要注意 File 和 Position 后面要用。

7. 登陆slave服务，配置主节点信息

sh复制代码[root@localhost ~]# mysql -u root -p
[这里输入mysql链接密码]
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.182.110',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='Ron_master_1',
    -> MASTER_LOG_FILE='mysql_binlog',
    -> MASTER_LOG_POS=856;

注意：

makefile复制代码CHANGE MASTER TO
# master 节点ip
MASTER_HOST='192.168.182.110,
# master节点登陆用户
MASTER_USER='repl',
# master节点登陆用户密码
MASTER_PASSWORD='Ron_master_1',
# binlog日志文件 对应上一步中的File
MASTER_LOG_FILE='mysql_binlog',
# 对应上一步Position
MASTER_LOG_POS=856;

8. 检查slave节点状态

sql复制代码mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.182.110
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_binlog.000010
          Read_Master_Log_Pos: 196
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 327
        Relay_Master_Log_File: mysql_binlog.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table:

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
看到这些说明我们配置成功了，接下来可以使用nacicat等连接工具测试一下。

记录

1. mysql查看当前用户列表

sql复制代码SELECT User, Host FROM mysql.user;

常见问题

1. Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
   
   这个错误提示。即主从架构中使用了相同的UUID。首先排查server_id系统变量，是否相同:

sql复制代码mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 110   |
+---------------+-------+
1 row in set (0.00 sec)

sql复制代码mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 111   |
+---------------+-------+
1 row in set (0.00 sec)

发现我们已经改为两个不同的了,查看auto.cnf文件(找不到可以使用find / -name auto.cnf查找)

sh复制代码[root@localhost logs]# vim /data/mysql/data/auto.cnf

[auto]
server-uuid=56b7da5a-4b45-11ec-ae40-000c2964986e

sh复制代码[root@localhost logs]# vim /data/mysql/data/auto.cnf

[auto]
server-uuid=56b7da5a-4b45-11ec-ae40-000c2964986e

可以看出两个计器上的server-uuid出现了重复，原因是克隆了虚拟机，只改server_id不行。

解决：重命名master auto.cnf文件后重启mysql

sh复制代码[root@localhost ~]# mv /data/mysql/data/auto.cnf /data/mysql/data/auto.cnf.bak
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.... SUCCESS! 
[root@localhost ~]# vim /data/mysql/data/auto.cnf 

[auto]
server-uuid=fef0443e-4b5d-11ec-bf78-000c2964986e

重启后可以看出server-uuid已经重新生成了。

2. 主从同步报错 Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log…
   这个是因为binlog位置索引处的问题，解决方法：

ini复制代码1.打开主服务器，进入mysql
2.执行flush logs；# 这时主服务器会重新创建一个binlog文件；
3.在主服务上执行show master slave \G; # 记录 a 和 Position
4.来到从服务器的mysql；
5.stop slave;
6.change master to master_log_file='File',master_log_pos=Position; #这里的file和pos都是上面主服务器master显示的。
7.start slave;
8.show slave status \G; # 查看从表状态

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有