MySQL-入门篇-MySQL简介与安装
- 数据库
- 2025-03-27
- 684热度
- 0评论
简介
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。详见：百度百科
二进制安装
下载地址：https://downloads.mysql.com/archives/community/
安装包下载
# 安装依赖包
yum -y install bc
cd /root
# 官方下载源
#wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
# 阿里云下载源
wget https://mirrors.aliyun.com/mysql/MySQL-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
ls
添加mysql用户
# 用户添加
useradd -u 13820 -s /sbin/nologin mysql
# 查看添加结果
grep mysql /etc/passwd /etc/group
解压安装
# 创建安装目录
mkdir -pv /www/server/mysql/{data,logs,mysql-files}
# 解压程序
tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
# 复制程序到mysql目录
cp -a mysql-5.7.44-linux-glibc2.12-x86_64/* /www/server/mysql
# 检查复制结果
ls /www/server/mysql
创建配置文件
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}
# 设置表名不区分大小写
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_buffer_pool_size=${INNODB_BUFFER_POOL_SIZE}
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
初始化数据库
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}'
配置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
# 复制服务配置文件
cp support-files/mysql.server /etc/init.d/mysql
# 添加服务为开机启动项
chkconfig --add mysql
# 设置开机启动
chkconfig mysql on
# 启动服务
service mysql start
链接命令系统系统路径
ln -sfv /www/server/mysql/bin/mysql* /bin/
修改初始化密码
方法一：
MYSQL_TEMP_PASS=$(grep "temporary password" logs/mysqld.log | awk '{print $NF}')
mysqladmin password 'newpassword' -uroot -p${MYSQL_TEMP_PASS}
方法二：
MYSQL_TEMP_PASS=$(grep "temporary password" logs/mysqld.log | awk '{print $NF}')
mysql -uroot -p${MYSQL_TEMP_PASS}
mysql> alter user user() identified by 'newpassword';
数据备份
mysqldump与innobackupex备份过程你知多少（一）-腾讯云开发者社区-腾讯云
逻辑备份
使用mysqldump进行逻辑备份，将数据生成sql形式进行保存。主要用于单库、单表数据备份迁移等场景。在大数据量场景下，此备份方法比较慢。
# 获取当前备份时间
BACKUP_DT=$(date '+%F_%T')
# 执行备份操作，对数据库bkp_database数据进行备份。
mysqldump -h 127.0.0.1 -ubkpuser -pbkppassword \
--single-transaction --triggers \
--routines --events bkp_database \
> /www/server/mysql/backup/backup_${BACKUP_DT}.sql \
2> /www/server/mysql/backup/backup_${BACKUP_DT}.log
物理备份
使用percona-xtrabackup工具进行物理备份；参考资料：https://www.yuque.com/fdaoge/ops/riosxw
percona-xtrabackup安装
# 下载安装包
cd ~
wget http://repo.ops.daqsoft.com:4180/m/server/mysql/perconadb/percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm
# 执行安装
yum -y localinstall percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm
创建备份用户
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 't@8SBQAMndSj';
GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;
备份脚本
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="bkpuser"
MYSQL_PASS='t@8SBQAMndSj'
DATA_DIR="/www/server/mysql/data"
BACKUP_DIR="/www/server/mysql/backup"
BACKUP_FILE="${BACKUP_DIR}/backup_${BACKUP_DT}.tar.gz"
BACKUP_DT=$(date '+%F_%T')
xtrabackup --host=${MYSQL_HOST} --port=${MYSQL_PORT} --user=${MYSQL_USER} \
--password=${MYSQL_PASS} --datadir=${DATA_DIR} --tmpdir=${BACKUP_DIR}/tmpdir \
--backup --stream=tar | gzip > ${BACKUP_FILE}
Docker安装
docker及docker-compose安装，省略
创建服务目录
mkdir /www/server/mysql/{config,data,logs,relaylog,binlog,mysql-files}
创建配置文件
INNODB_BUFFER_POOL_SIZE=$(echo "$(free -m | grep Mem | awk '{print $2}') * 0.6" | bc | awk -F'.' '{print $1}')
echo ${INNODB_BUFFER_POOL_SIZE}
cat > /www/server/mysql/config/mysqld.conf << EOF
[client]
port=3306
socket=/www/server/mysql/logs/mysql.sock
default-character-set=utf8mb4
[mysqld]
basedir=/www/server/mysql
datadir=/www/server/mysql/data
log-error=/www/server/mysql/logs/mysqld.log
socket=/www/server/mysql/logs/mysql.sock
pid-file=/www/server/mysql/logs/mysql.pid
log-bin=/www/server/mysql/logs/mysql-bin
slow_query_log_file=/www/server/mysql/logs/slow.log
secure_file_priv=/www/server/mysql/mysql-files
port=3306
# 设置表名不区分大小写
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
default-storage-engine = innodb
innodb_buffer_pool_size=${INNODB_BUFFER_POOL_SIZE}
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
启动服务
Docker命令部署
docker run -d --name mysql57 \
-p 3306:3306
-v /www/server/mysql/config:/etc/mysql/mysql.conf.d \
-v /www/server/mysql/data:/www/server/mysql/data \
-v /www/server/mysql/logs:/www/server/mysql/logs \
-v /www/server/mysql/relaylog:/www/server/mysql/relaylog \
-v /www/server/mysql/binlog:/www/server/mysql/binlog \
-v /www/server/mysql/logs:/www/server/mysql/logs \
-v /www/server/mysql/mysql-files:/www/server/mysql/mysql-files \
-e MYSQL_ROOT_PASSWORD="NEWPASSWORD" \
mysql:5.7
docker-compose部署
创建docker-compose文件
cd /www/server/mysql
cat > docker-compose.yml << EOF
version: '2'
services:
mysql:
image: mysql:5.7
restart: always
container_name: mysql
volumes:
- /www/server/mysql/config:/etc/mysql/mysql.conf.d
- /www/server/mysql/data:/www/server/mysql/data
- /www/server/mysql/logs:/www/server/mysql/logs
- /www/server/mysql/relaylog:/www/server/mysql/relaylog
- /www/server/mysql/binlog:/www/server/mysql/binlog
- /www/server/mysql/logs:/www/server/mysql/logs
- /www/server/mysql/mysql-files:/www/server/mysql/mysql-files
- /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime
ports:
- 3306:3306
environment:
MYSQL_ROOT_PASSWORD: 'NEWPASSWORD'
EOF
启动服务
cd /www/server/mysql
docker-compose up -d