MySQL介绍

MySQL简介

MySQL是一款数据库管理系统(DBMS-Database Management System)。

数据库管理系统是用来保证数据安全、提高数据管理效率、读写效率、扩展性、灵活性的软件。

DBMS的分类

关系型数据库(RDBMS)

• 它的特点是使用二维表，有严格的行列结构及属性约束，支持复杂的查询。
• 相关产品有：MySQL、MariaDB、OracleDB

非关系型数据库(NoSQL)

• 它的特点是没有严格的行列结构，更具可伸缩性、扩展性。
• 相关产品：Redis、Memcache、MongoDB、ES

NewSQL

• NewSQL是对各种新的可扩展、高性能数据库的简称，这类数据库不仅具有NoSQL对海量数据的存储管理能力，还保持了传统数据库支持ACID和SQL等特性。
• 相关产品：TiDB、Spanner、AliSQL(RDS+DRDS)、PolarDB

MySQL部署

1. 获取MySQL二进制包

• 官方下载地址：https://downloads.mysql.com/archives/

• 在页面选择社区版(MySQL Community Server) 单击进入。

• 选择需要的MySQL版本和对应操作系统。

  • Linux-Generic - 通用二进制版本，解压即用。
  • Source Code - 源代码，需要自行编译。

  注意二进制包要选择正确的glibc版本，可以使用ldd --version命令查看主机的glibc版本。

• 然后下载即可

2. 解压部署

# 解压MySQL软件包并放到指定位置
mkdir -p /data/apps/
tar -xvf mysql-*.tar.xz -C /data/apps/
mv /data/apps/mysql* /data/apps/mysql/

# 创建相关目录
mkdir -p /data/apps/mysql/{data,conf,logs,logs/binlog}

# 创建并授权目录给mysql管理用户
useradd -M -s /sbin/nologin mysql
chown -R mysql.mysql /data/apps/mysql

3. 添加环境变量

# 添加mysql命令到环境变量，方便日后调用
echo 'export PATH=/data/apps/mysql/bin:$PATH' >> /etc/profile
source /etc/profile

# 验证是否生效
mysql -V

4. 初始化数据库

# 安装依赖
yum install -y libaio-devel

# 初始化数据库
# mysqld --initialize-insecure --user=[mysql管理用户] --basedir=[mysql软件所在路径] --datadir=[mysql数据存放路径]
mysqld --initialize-insecure --user=mysql --basedir=/data/apps/mysql/ --datadir=/data/apps/mysql/data/

5. 设置配置文件

cat>/data/apps/mysql/conf/my.cnf<<EOF
[mysqld]
user=mysql
port=3306
server_id=16
basedir=/data/apps/mysql/
datadir=/data/apps/mysql/data/
log_error=/data/apps/mysql/logs/err.log
socket=/tmp/mysql.sock
default-time_zone='+8:00'

# binlog日志配置
log_bin=/data/apps/mysql/logs/binlog/mysql-bin
# 超过14天的binlog日志清除
binlog_expire_logs_seconds=1209600
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=on
# 启用二进制日志时如果没有做主从同步则下面参数可以设为1，这样才能正常创建或使用函数
log_bin_trust_function_creators=1

# 缓冲池大小,一般配置为系统内存的50%至75%
# 另外块的数量不应超过1000，buffer_pool_size除以buffer_chunk_size
innodb_buffer_pool_size=8G

# 最大连接数
max_connections=1000

# 去掉ONLY_FULL_GROUP_BY，按需配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


[mysql]
socket=/tmp/mysql.sock

EOF

• [mysqld] - 服务端配置

  • user - mysql管理用户
  • port - 端口号
  • server_id- 数据库实例ID
  • basedir - mysql软件所在路径
  • datadir - mysql数据存放路径
  • socket - Socket文件存放路径
  • log_error - 错误日志文件存放路径

• [mysql] - 客户端配置

  • socket - Socket文件路径(需要和mysqld配置的socket路径一致)

• 错误日志用于排查MySQL运行过程的故障，一般关注[ERROR]即可。

  • 默认是开启的，默认存放位置是：[datadir]/[主机名].err
  • 配置文件定义方式是：log_error=[日志路径].log

6. 使用systemd管理服务

• MySQL服务端的启动脚本有两个mysqld和mysqld_safe，两者的区别实际不大。使用mysqld_safe方式启动会比mysqld多一个守护进程，该守护进程会在mysqld挂掉时，自动把mysqld进程拉起来。

  cat>/etc/systemd/system/mysqld.service<<EOF
  [Unit]
  Description=MySQL Server
  Documentation=man:mysqld(8)
  Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  After=network.target
  After=syslog.target
  
  [Service]
  User=mysql
  Group=mysql
  # MySQL启动命令
  ExecStart=/data/apps/mysql/bin/mysqld_safe --defaults-file=/data/apps/mysql/conf/my.cnf
  # 文件描述符打开限制
  LimitNOFILE=10000
  # 自动重启策略
  Restart=on-failure
  
  [Install]
  WantedBy=multi-user.target
  EOF
  
  # 重载配置
  systemctl daemon-reload
  
  # 启动MySQL
  systemctl enable mysqld && systemctl start mysqld
  
  # 检查启动状态
  systemctl status mysqld
  

7. 修改Root用户密码

• 使用--initialize-insecure参数初始化的数据库root用户默认无密码，所以需要设置密码。

• 8.0之后版本的更新方式

  # 首先使用命令登录数据库
  mysql -uroot
  
  # 使用ALTER语句进行修改即可
  ALTER USER root@"localhost" identified by "此处输入密码";
  
  # 然后使用exit退出
  exit;
  
  # 重新登录输入密码，测试是否生效即可
  mysql -uroot -p
  

• 8.0之前版本的更新方式

  # 首先使用命令登录数据库
  mysql -uroot
  
  # MySQL存储用户账号密码是用mysql库下的user表，密码是加密存储在authentication_string字段中的
  SELECT User,Host,authentication_string FROM mysql.user WHERE User="root" AND Host="localhost";
  
  # 我们使用Update语句和PASSWORD函数给root@'localhost'设置密码即可
  UPDATE mysql.user SET authentication_string=PASSWORD("此处输入密码") WHERE User="root" AND Host="localhost";
  
  # 然后刷新授权表
  flush privileges;
  
  # 然后使用exit退出
  exit;
  
  # 重新登录输入密码，测试是否生效即可
  mysql -uroot -p
  

8. 连接MySQL客户端

命令连接

• TCP/IP方式：mysql -h 127.0.0.1 -P 3306 -uroot -p
• Socket方式：mysql -S /tmp/mysql.socket -uroot -p
• mysql命令参数：
  • -u - 指定用户名
  • -p - 指定用户密码
  • -S - 指定Socket
  • -h - 指定数据库主机
  • -P - 指定端口
  • -e - 不进入交互模式执行SQL语句
  • < - 恢复导入数据
    • 例如：mysql -uroot -p -S /data/mysql_3306/mysql.sock < world.sql

客户端工具

• navicat - 推荐
• sqlyog
• DBeaver

9. MySQL交互式命令

• help; - 打印MySQL交互式命令帮助
• Ctrl+C - 结束当前SQL
• Ctrl+D、quit; - 退出MySQL交互式
• \G - 格式化输出，以列:值方式显示，数据内容多时建议可以使用
• source - 恢复备份文件，同等于<
  • 例如：source /root/world.sql;