现在位置: 首页 / 数据库 / mysql / 正文

MySQL-入门篇-MySQL简介与二进制安装

  • FightLinux
  • 数据库
  • 2025-03-27
  • 1184热度
  • 0评论

简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。详见：百度百科

二进制安装

下载地址：https://downloads.mysql.com/archives/community/

SELinux和防火墙配置

建议对SELinux进行关闭以避免在安装过程中出现未知错误，减少排查时间。

方法一:关闭SELinux和防火墙

关闭SELinux

setenforce 0
sed -i 's|^SELINUX=.*|SELINUX=disable|g' /etc/selinux/config
grep -E "^SELINUX" /etc/selinux/config
mysql_install_setenforce0png

关闭防火墙 ，如果不关闭防火墙，可通过后续命令进行进行防火墙策略配置，开放相关访问策略。

systemctl disable firewalld
systemctl stop firewalld
mysql_install_06

方法二:修改SELinux和防火墙策略

防火墙添加策略放通所有IP对MYSQL的访问，注意后续部署端口与下面命令中的端口配置一致。

INSTALL_DIR="/www/server/mysql"
# 设置MYSQL运行端口
MYSQL_PORT=3306

firewall-cmd --add-port ${MYSQL_PORT}/tcp
firewall-cmd --add-port ${MYSQL_PORT}/tcp --permanent

安装包下载

# 安装依赖包
yum -y install bc
cd /root
# 官方下载源
MYSQL_VERSION="mysql-5.7.44-linux-glibc2.12-x86_64"
wget https://cdn.mysql.com/archives/mysql-5.7/${MYSQL_VERSION}.tar.gz
ls
# 阿里云下载源
MYSQL_VERSION="mysql-5.7.38-linux-glibc2.12-x86_64"
wget https://mirrors.aliyun.com/mysql/MySQL-5.7/${MYSQL_VERSION}.tar.gz

解压安装

# 
INSTALL_DIR=/www/server/mysql
# 创建安装目录
mkdir -pv ${INSTALL_DIR}/{data,logs,mysql-files}
# 解压程序
tar xf ${MYSQL_VERSION}.tar.gz
# 复制程序到mysql目录
cp -a ${MYSQL_VERSION}/* ${INSTALL_DIR}
# 检查复制结果
ls ${INSTALL_DIR}
mysql_install_08

创建配置文件

INSTALL_DIR="/www/server/mysql"
MYSQL_PORT=3306

INNODB_BUFFER_POOL_SIZE=$(echo "$(free -m  | grep Mem | awk '{print $2}') * 0.6" | bc | awk -F'.' '{print $1}')
echo "INNODB_BUFFER_POOL_SIZE:${INNODB_BUFFER_POOL_SIZE}"
# 备份原有my.cnf
[ -f /etc/my.cnf ] && mv /etc/my.cnf{,.bak-$(date '+%F_%T')} || echo 
# 生成新的my.cnf
cat << EOF | tee /etc/my.cnf
[client]
port=${MYSQL_PORT}
socket=${INSTALL_DIR}/logs/mysql.sock
default-character-set=utf8mb4

[mysqld]
basedir=${INSTALL_DIR}
datadir=${INSTALL_DIR}/data
log-error=${INSTALL_DIR}/logs/mysqld.log
socket=${INSTALL_DIR}/logs/mysql.sock
pid-file=${INSTALL_DIR}/logs/mysql.pid
log-bin=${INSTALL_DIR}/logs/mysql-bin
slow_query_log_file=${INSTALL_DIR}/logs/slow.log
secure_file_priv=${INSTALL_DIR}/mysql-files
port=${MYSQL_PORT}
innodb_buffer_pool_size=${INNODB_BUFFER_POOL_SIZE}
# 设置表名不区分大小写
lower_case_table_names=1
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
max_connections = 4000
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache_instances = 64
thread_stack = 512k
max_allowed_packet = 1024M
binlog_cache_size = 2048KB
max_heap_table_size = 256M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 8
query_cache_size = 64M
query_cache_limit = 2M
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
gtid_mode=on
enforce_gtid_consistency=on
server-id = 1
binlog_format=row
log_slave_updates=true
slow_query_log
long_query_time = 5
event_scheduler =1
expire_logs_days = 7
log_timestamps=SYSTEM
log-bin-trust-function-creators=1
skip-name-resolve

default-storage-engine = innodb
innodb_data_file_path = ibdata1:1G:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 10
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 3000
innodb_flush_method = O_DIRECT
innodb_io_capacity = 4000
innodb_io_capacity_max = 40000
innodb_max_dirty_pages_pct = 50
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_undo_tablespaces=3
innodb_undo_logs=128
innodb_max_undo_log_size=1G
innodb_undo_log_truncate=1
innodb_purge_rseg_truncate_frequency=128

sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[mysql]
default-character-set=utf8mb4
no-auto-rehash
prompt="\\u@\\h:[\\d] \\r:\\m:\\s>"

[mysqld_safe]
open-files-limit = 65535
EOF

head -n 30 /etc/my.cnf
mysql_install_07

添加mysql用户

# 用户添加
useradd -u 13820 -s /sbin/nologin mysql
# 查看添加结果
grep mysql /etc/passwd /etc/group

初始化数据库

chmod 755 /www/server
# 设置数据库目录权限
chown -R mysql:mysql /www/server/mysql /etc/my.cnf*
# 切换mysql用户
cd /www/server/mysql/
# 初始化数据库系统
su mysql -s /bin/bash -lc "${INSTALL_DIR}/bin/mysqld --initialize \
         --basedir=${INSTALL_DIR} \
         --datadir=${INSTALL_DIR}/data \
         --log-error=${INSTALL_DIR}/logs/mysqld.log"
# 获取mysql初始化时root用户的临时密码
grep "temporary password" logs/mysqld.log | awk '{print $NF}'       

# 创建环境变量存储文件
touch ${INSTALL_DIR}/env
chmod 600 ${INSTALL_DIR}/env
mysql_install_02

链接命令系统系统路径

ln -sfv /www/server/mysql/bin/mysql* /bin/
mysql_install_10

配置mysql日志轮换

# 复制日志滚动配置文件
cp support-files/mysql-log-rotate /etc/logrotate.d/mysql 
# 替换日志滚动配置内容
sed -i \
 -e 's|/usr/local|/www/server|' \
 -e 's|data/mysqld.log|logs/mysqld.log|' \
 /etc/logrotate.d/mysql 

 grep -vE "^$|^#" /etc/logrotate.d/mysql
mysql_install_04

配置mysql服务

rhel6配置

# 设置安装目录
INSTALL_DIR="/www/server/mysql"
# 复制服务配置文件
cp support-files/mysql.server /etc/init.d/mysql
# 修改服务启动脚本，设置安装目录
sed -e "s|^basedir=|basedir=${INSTALL_DIR}|"  \
-e "s|^datadir=|datadir=${INSTALL_DIR}/data|"  \
-i /etc/init.d/mysql

rhel7+配置

# 设置安装目录
INSTALL_DIR="/www/server/mysql"
# 创建启动服务配置文件
cat << EOF | tee /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL 5.7 Database Service
After=network.target
[Service]
User=mysql
Group=mysql
Type=simple
ExecStart=${INSTALL_DIR}/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=65535
Environment="TZ=Asia/Shanghai"
[Install]
WantedBy=multi-user.target
EOF

启动mysql服务

rhel6

# 添加服务为开机启动项
chkconfig --add mysql
# 设置开机启动
chkconfig mysql on
# 启动服务
service mysql start

rhel7+

# 设置开机启动
systemctl enable mysqld
# 启动mysql
systemctl start mysqld
# 查看mysql服务状态
systemctl status mysqld
mysql_install_09

rocky9程序依赖库处理

ln -sfv /usr/lib64/libncurses.so.6 /usr/lib64/libncurses.so.5
ln -sfv /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5

异常处理

libncurses.so.5 未找到

错误出现：在rockylinux9中执行mysql命令提示找不到相关库

错误提示：mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory 处理办法：

 ln -sfv /usr/lib64/libncurses.so.6 /usr/lib64/libncurses.so.5

错误提示：mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory 处理办法：

ln -sfv /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5

修改初始化密码

方法一：

#设置安装目录
INSTALL_DIR="/www/server/mysql"
# 读取日志中的初始化临时密码
MYSQL_TEMP_PASS=$(grep "temporary password" logs/mysqld.log | awk '{print $NF}')
# 生成新密码
MYSQL_NEW_PASS=$(openssl rand -base64 8)
# 修改新密码
mysqladmin password "${MYSQL_NEW_PASS}" -uroot -p${MYSQL_TEMP_PASS}
# 保存新密码为环境变量
echo "MySQL新密码：${MYSQL_NEW_PASS}"
echo "MYSQL_ROOT_PASSWORD=${MYSQL_NEW_PASS}" > ${INSTALL_DIR}/env
# 测试新密码是否有效
source ${INSTALL_DIR}/env
mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "show databases;"
mysql_install_11

方法二：

#设置安装目录
INSTALL_DIR="/www/server/mysql"
# 读取日志中的初始化临时密码
MYSQL_TEMP_PASS=$(grep "temporary password" logs/mysqld.log | awk '{print $NF}')
# 生成新密码
MYSQL_NEW_PASS=$(openssl rand -base64 8)
# 修改新密码
mysql -uroot -p${MYSQL_TEMP_PASS} -e "alter user user() identified by '${MYSQL_NEW_PASS}';"
# 保存新密码为环境变量
echo "MySQL新密码：${MYSQL_NEW_PASS}"
echo "MYSQL_ROOT_PASSWORD=${MYSQL_NEW_PASS}" > ${INSTALL_DIR}/env
# 测试新密码是否有效
source ${INSTALL_DIR}/env
mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "show databases;"

数据备份

mysqldump与innobackupex备份过程你知多少（一）-腾讯云开发者社区-腾讯云

创建备份用户

# 生成随机密码
MYSQL_BKPUSER_PASSWORD=$(openssl rand -base64 8)
source ${INSTALL_DIR}/env
# 创建备份用户：bkpuser
mysql -uroot -p${MYSQL_ROOT_PASSWORD} \
-e "CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '${MYSQL_BKPUSER_PASSWORD}';"
# 备份用户授权
mysql -uroot -p${MYSQL_ROOT_PASSWORD} \
-e "GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';"
# 权限刷新
mysql -uroot -p${MYSQL_ROOT_PASSWORD}  -e "FLUSH PRIVILEGES;"
# 查询用户是否创建成功
mysql -uroot -p${MYSQL_ROOT_PASSWORD}  -e "select user,host from mysql.user where user='bkpuser';"
# 保存新密码为环境变量
echo "MYSQL_BKPUSER_PASSWORD=${MYSQL_BKPUSER_PASSWORD}" | tee \
-a  ${INSTALL_DIR}/env
echo "备份用户: bkpuser 密码为：${MYSQL_BKPUSER_PASSWORD}"
mysql_install_12

逻辑备份

使用mysqldump进行逻辑备份，将数据生成sql形式进行保存。主要用于单库、单表数据备份迁移等场景。在大数据量场景下，此备份方法比较慢。

# 获取当前备份时间
BACKUP_DT=$(date '+%F_%T')
# 引入环境变量
INSTALL_DIR="/www/server/mysql"
BACKUP_DIR="${INSTALL_DIR}/backup"
BACKUPLOG_DIR="${INSTALL_DIR}/logs"
source ${INSTALL_DIR}/env
BACKUP_FILE="backup_${BACKUP_DT}.sql"
# 创建备份目录
mkdir ${BACKUP_DIR}
# 执行备份操作，对数据库bkp_database数据进行备份。
mysqldump -h 127.0.0.1 -ubkpuser -p${MYSQL_BKPUSER_PASSWORD} \
    --single-transaction --triggers \
    --routines --events bkp_database \
     > ${BACKUP_DIR}/${BACKUP_FILE} \
    2> ${BACKUPLOG_DIR}/backup_${BACKUP_DT}.log
# 显示备份相关信息
echo "备份文件目录：${BACKUP_DIR}"
echo "备份文件名称：${BACKUP_FILE}"
# 查看备份列表
ls ${BACKUP_DIR}
mysql_install_backupby_mysqldump

物理备份

使用percona-xtrabackup工具进行物理备份；参考资料：https://www.yuque.com/fdaoge/ops/riosxw

官网下载：https://www.percona.com/downloads

官网安装文档(二进制)：https://docs.percona.com/percona-xtrabackup/2.4/installation/binary-tarball.html

官网安装文档(yum) ：https://docs.percona.com/percona-xtrabackup/2.4/installation/yum_repo.html#installing-percona-xtrabackup-from-percona-yum-repository

yum包下载地址：https://repo.percona.com/yum/release/2/os/x86_64/

percona-xtrabackup安装

cd ~
# 下载安装包
wget  https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.29/\
binary/tarball/percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17-minimal.tar.gz

# 解压安装
tar xf percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17.tar.gz 
mv percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17 /www/server/percona-xtrabackup-2.4.29

# 加入系统环境变量：PATH
cat >> /etc/profile << EOF
export PATH=\$PATH:/www/server/percona-xtrabackup-2.4.29/bin/
EOF

# 引入系统环境变量：PATH
source /etc/profile

备份脚本

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_BKPUSER="bkpuser"
DATA_DIR="/www/server/mysql/data"
BACKUP_DIR="/www/server/mysql/backup"
BACKUP_FILE="${BACKUP_DIR}/backup_${BACKUP_DT}.tgz"
BACKUP_DT=$(date '+%F_%T')

source ${INSTALL_DIR}/env

xtrabackup --host=${MYSQL_HOST} --port=${MYSQL_PORT} --user=${MYSQL_USER} \
    --password=${MYSQL_BKPUSER_PASSWORD} --datadir=${DATA_DIR} --tmpdir=${BACKUP_DIR}/tmpdir \
    --backup --stream=tar | gzip > ${BACKUP_FILE}