本文详细介绍了在CentOS7.4系统上安装MySQL8.0的过程，包括系统配置、磁盘分区设置、依赖包安装、参数配置及半同步复制的实现。涵盖了从环境准备到数据库初始化、用户权限设定、主从复制搭建等关键步骤。
一、安装系统配置
到公有云上申请一个ECS，测试使用，规格可以随意，磁盘空间100G。
操作系统镜像：CentOS7.4
内核版本： 3.10.0-327.36.3.el7.x86_64
MySQL8.0各个安装包的用途。
| Package name | 用途 |
| mysql-community-client | MySQL client applications and tools |
| mysql-community-common | common files for server and client libraries |
| mysql-community-devel | development header files and libraries for mysql database client applications |
| mysql-community-embedded-compat | mysql server as an embedded library with compatibility for applications using version 18 of the library |
| mysql-community-libs | shared libraries for mysql database client applications |
| mysql-community-libs-compat | shared compatibility libraries for previous mysql installations |
| mysql-community-server | database server and related tools |
| mysql-community-test | test suite for the mysql server |
需要到官网下载 https://dev.mysql.com/downloads/mysql/
mysql-8.0.14-1.el7.x86_64.rpm-bundle.tar
二、安装MySQL8.0
1. 准备工作
a. 先创建分区
fdisk /dev/vdb
创建出两个分区，一个70G用于存在mysql数据，一个30G，用于放软件等资料。
b. 格式化文件系统：
mkfs.ext4 /dev/vdb1
mkfs.ext4 /dev/vdb2
c. 创建挂载点
mkdir -p /var/lib/mysql
mkdir -p /var/software
echo '/dev/vdb1 /var/lib/mysql ext4 rw,nosuid,nodev,exec,auto,nouser,async 0 0' >> /etc/fstab
echo '/dev/vdb2 /var/software ext4 rw,nosuid,nodev,exec,auto,nouser,async 0 0' >> /etc/fstab
mount -a
2. 上传Mysql安装包
mysql-8.0.14-1.el7.x86_64.rpm-bundle.tar
3. 解压安装包和卸载冲突的安装包
tar -xvf mysql-8.0.14-1.el7.x86_64.rpm-bundle.tar
4. 安装
a. rpm -ivh mysql-community-common-8.0.14-1.el7.x86_64.rpm
b.卸载冲突包
rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
c. rpm -ivh mysql-community-libs-8.0.14-1.el7.x86_64.rpm
d. rpm -ivh mysql-community-client-8.0.14-1.el7.x86_64.rpm
e. rpm -ivh mysql-community-devel-8.0.14-1.el7.x86_64.rpm --nodeps
5. 创建用户和组
groupadd mysql
useradd mysql -g mysql
6. 创建目录
mkdir -p /var/lib/mysql/tmp
chown mysql:mysql /var/lib/mysql/tmp
mkdir -p /var/lib/mysql/data
chown mysql:mysql /var/lib/mysql/data
7. 配置参数文件
vi /etc/my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/data/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/data/mysql.sock
datadir = /var/lib/mysql/data
skip-external-locking
default-storage-engine = INNODB
key_buffer_size = 256M
max_allowed_packet = 1G
table_open_cache = 4096
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size =32M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
#query_cache_size= 16M #unknown variable
#query_cache_type=0 #unknown variable
event_scheduler=1
skip-name-resolve
skip-slave-start
max_connections=2000
character_set_server=utf8
user=mysql
log-error = /var/lib/mysql/data/error.log
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 8
lower_case_table_names = 1
join_buffer_size =2M
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT
#sql_mode=ANSI,TRADITIONAL,STRICT_ALL_TABLES
explicit_defaults_for_timestamp=false
local-infile=1
#skip-networking
#secure_auth = ON # unknown variable
skip-symbolic-links
#sql_mode = NO_AUTO_CREATE_USER
log_bin_trust_function_creators =1
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
log-bin-index=mysql-bin.index
slave_parallel_type=LOGICAL_CLOCK #for MTS
slave_parallel_workers=32
#open log_slow_queries sync_binlog 2011-9-24 xzlox
long_query_time = 10
slow-query-log = 1
log_output = file
sync_binlog=0
# binary logging format - row recommended
binlog_format=mixed
slave-skip-errors=all
relay-log=relay-bin
relay-log-index=relay-bin.index
expire_logs_days=1
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 611
# Uncomment the following if you are using InnoDB tables
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/data/
innodb_log_files_in_group = 5
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1000M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
#replicate-do-db = ym
innodb_file_per_table
# GTID
log_slave_updates=1
gtid-mode=on
enforce-gtid-consistency=true
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
8. 安装server端
安装依赖包：
yum install perl
rpm -ivh mysql-community-server-8.0.14-1.el7.x86_64.rpm
9. 关闭seLinux和防火墙 (有些操作系统需要这一步)
setenforce 0
并且/etc/selinux/config将enforcing设为disabled
iptables -X
iptables -F
10. 初始化数据并启动mysql
mysqld --initialize --basedir=/usr/ --datadir=/var/lib/mysql/data --user=mysql
/bin/systemctl start mysqld.service
11. 找到临时密码
cat /var/lib/mysql/data/error.log
([Note] A temporary password is generated for root@localhost: iZ6BTu+&AXbh)
12. 修改密码
set sql_log_bin=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY '******';
flush privileges;
13. 停止mysql
/bin/systemctl stop mysqld.service
14. 连接数据库
mysql -uroot -p -A
二、配置半同步
1. 查看是否支持动态加载，需要下面的变量设为YES
mysql> show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
1 row in set (0.01 sec)
2. 在主从库分别按照插件, 考虑到后面主从倒换，需要两边都安装
主库：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从库：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
3. 查看是否正常
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
4. 建立主从同步
a. 创建root账号和复制账号repl并赋权，主从库都执行
set sql_log_bin=0;
CREATE USER 'repl'@'%' IDENTIFIED BY '******';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
create user root@'%' identified by '******';
grant all on *.* to root@'%';
flush privileges;
b. 获取主库的binlog
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000008
Position: 611
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
c. 建立主从关系
CHANGE MASTER TO
MASTER_HOST='10.**.**.50',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='******',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=611,
MASTER_AUTO_POSITION=0;
开通了gtid的话，用下面的方法：
CHANGE MASTER TO
MASTER_HOST='10.**.**.50',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Gauss_234',
MASTER_AUTO_POSITION=1;
这个时候不要忘记在主库上将防火墙关闭；如果没有telnet可以用nc -v $IP 3306命令进行端口探测
d. 在主从库上添加相关变量
主库：
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;
从库：
set global rpl_semi_sync_slave_enabled=1;
为了保证这些变量启动实例后生效，需要将他们放到配置文件中：
主库:
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
从库:
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=1000
另外：rpl_semi_sync_master_wait_point这个变量可以取值AFTER_SYNC(默认) 和 AFTER_COMMIT：这个值是采用老式的MySQL5.5/5.6半同步复制工作
一个重点需要监控的状态变量为：Rpl_semi_sync_master_status， Rpl_semi_sync_slave_status
show status like 'Rpl_semi_sync_master_status';
show status like 'Rpl_semi_sync_slave_status';
5. 启动同步
start slave;
show slave status\G
扫一扫
1195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
