iEnote

外观

Mysql基础知识 ​

记录 MySQL 的基础知识。

yum安装Mysql ​

mysql安装有四种方式:

  • yum仓库安装
  • 二进制安装(推荐)
  • 源码安装
  • 自制mysql的rpm安装包(推荐)

这里介绍下通过yum仓库安装mysql：

1: 去https://dev.mysql.com/downloads/repo/yum/下载mysql yum源

2: 安装yum源: sudo yum -y localinstall -y mysql57-community-release-el7-11.noarch.rpm

3: 验证安装: yum repolist enabled | grep 'mysql.*-community.*'

4: 禁用Mysql5.7并启用mysql8

bash
sudo yum install yum-utils.noarch -y
sudo yum-config-manager --disable mysql57-community
sudo yum-config-manager --enable mysql80-community

通过sudo yum repolist all |grep mysql查看默认支持哪些mysql

5: 安装mysql8

sudo yum -y install mysql-community-server

WARNING

mysql-community-server: 数据库服务器和相关工具

mysql-community-client: Mysql客户端应用程序和工具

mysql-community-common: 服务器和客户端库的公共文件

mysql-community-devel: 开发mysql数据库客户端应用程序的头文件和库.

mysql-community-libs: 某些语言和应用程序需要动态加载和使用mysql的共享库

mysql-community-libs-compat: 旧版本的共享库,如果你安装了与旧版本mysql动态链接的应用程序,但希望不破坏库依赖关系的情况下升级到前版本,需要安装此库.

二进制安装mysql8 ​

  • 初始化mysql
bash
sudo yum install -y libnuma libaio
sudo groupadd mysql
sudo useradd -r -g mysql -s /sbin/nologin mysql
cd /usr/local
sudo wget http://*****/Percona-Server-8.0.15-6-Linux.x86_64.ssl101.tar.gz
sudo tar -zxvf Percona-Server-8.0.15-6-Linux.x86_64.ssl101.tar.gz
sudo ln -s Percona-Server-8.0.15-6-Linux.x86_64.ssl101 mysql    #使用mysql做别名,方便维护
sudo chown -R root.mysql mysql    #将属主设为root比较安全,这样可通过systemctl直接调用,也可以设置为当前用户为属主.
sudo mkdir mysql-files          #无实际作用,可用来存放配置文件
sudo chmod 750 mysql-files
sudo chown -R mysql.mysql .
sudo rm -rf /etc/my.cnf  /etc/my.cnf.d             #初始化之前一定要删除my.cnf文件,否则会初始化失败,建议先将my.cnf备份.
sudo bin/mysqld --initialize --user=mysql          #会在当前目录创建data目录,这样就不用通过--datadir设置了
sudo touch /usr/local/mysql/logs/mysqlerr.log     #日志文件,需在my.cnf中设置[mysqld_safe] #log-error=/usr/local/mysql/logs/mysqlerr.log
sudo chown -R mysql.mysql /usr/local/mysql/logs/mysqlerr.log

WARNING

--user参数必须在具有root权限的账号下使用,否则还得通过chown 设置为mysql属主属组,初始化时会生成一个随机密码请记住.

其实也可以用普通账号初始化,但初始化时不能带--user参数且初始化后需要设置mysql目录以及父级目录的属组为当前账号,sudo chown -R echoxu.mysql .

  • 配置my.cnf

在/usr/local/mysql中创建my.cnf文件.

my.cnf搜索顺序: /etc/my.cnf - - > /etc/mysql/my.cnf - - > $MYSQL_BASE/my.cnf - - > defaults-extra-file = /path/to/some_my.cnf - - > .my.cnf

bash
[mysqld]
datadir=/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/data
socket=/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/run/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#skip_grant_files
[mysqld_safe]
log-error=/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/logs/mysqlerr.log
pid-file=/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/run/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

WARNING

注意修改上面的datadir等值,最好是从别处拷贝my.cnf并备份覆盖/etc/my.cnf文件.

  • 设置开机启动项
bash
sudo cp /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld
sudo sed -i 's#basedir=#basedir=/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64#'  /etc/init.d/mysqld
sudo sed -i 's#datadir=#datadir=/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/data#'  /etc/init.d/mysqld
  • 设置环境变量

sudo vim /etc/profile.d/mysql.sh

bash
export PATH=/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin:$PATH
source /etc/profile.d/mysql.sh
  • 修改密码

方法一: 修改密码可通过在初始化时提供的随机密码登录,然后执行/usr/local/mysql/bin/mysqladmin -uroot password 'new password'

方法二:

先通过--skip-grant-tables启动mysql，然后不输入密码登录mysql:

sudo -u mysql /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysqld_safe --user=mysql --skip-grant-tables &

/usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/bin/mysql -u root -S /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/run/mysql.sock

登录mysql后执行:

bash
mysql> flush privileges;      #必须先执行此项

mysql> alter user 'root'@'localhost' identified by 'your new passwd';

#上面的命令改为use mysql;update user set password=PASSWORD('yourpasswd') where user='root';flush privileges;也可以

重启Mysql并用新密码登录(使用systemctl启动mysql需要先重启机器使其生效).

方法三:

修改/etc/my.cnf中[client]段中的#password = your_password

或者在当前用户下创建.my.cnf然后添加[mysqld] user = root host = localhost password = yourpasswd

  • 登录时取消指定套接字路径

因为在my.cnf中设置了socket路径,现在每次登录mysql都要通过-S来指定套接字路径,但可通过如下方法让登录mysql时不需要指定套接字路径:

sudo ln -s /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64/run/mysql.sock /tmp/mysql.sock

DANGER

不指定套接字路径会报错ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

  • mysql安全设置

安装完mysql后可通过bin/mysql_secure_installation来移除一些不安全的设置,另可将mysql安装目录的权限可设置为700

sudo chmod -R 700 /usr/local/mysql-8.0.17-linux-glibc2.12-x86_64

但是这样一设置mysql环境变量就不起作用了,每次登录都需要指明mysql文件所在的绝对路径.安全与快捷其实更应注重安全.

另外还需设置sudo chmod 700 /etc/init.d/mysql,这样操作下来至少在管理mysql上更安全些.

创建用户并赋权限 ​

bash
CREATE DATABASE IF NOT EXISTS ISITE DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 
CREATE USER IF NOT EXISTS 'ISITE'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpasswd';
GRANT ALL ON ISITE.* TO 'ISITE'@'%';
FLUSH PRIVILEGES;

字符集和比较规则 ​

1 字符集指的是某个字符范围的编码规则。

2 比较规则是针对某个字符集中的字符比较大小的一种规则。

3 在MySQL中，一个字符集可以有若干种比较规则，其中有一个默认的比较规则，一个比较规则必须对应一个字符集。

4 查看MySQL中查看支持的字符集和比较规则的语句如下：

sql
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
SHOW COLLATION [LIKE 匹配的模式];

5 MySQL有四个级别的字符集和比较规则

  • 服务器级别

character_set_server表示服务器级别的字符集，collation_server表示服务器级别的比较规则。

  • 数据库级别

创建和修改数据库时可以指定字符集和比较规则：

sql
CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

character_set_database表示当前数据库的字符集，collation_database表示当前默认数据库的比较规则，这两个系统变量是只读的，不能修改。如果没有指定当前默认数据库，则变量与相应的服务器级系统变量具有相同的值。

  • 表级别

创建和修改表的时候指定表的字符集和比较规则：

sql
CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]];

ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称];
  • 列级别

创建和修改列定义的时候可以指定该列的字符集和比较规则：

sql
CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

6 从发送请求到接收结果过程中发生的字符集转换：

  • 客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串。

  • 服务器将客户端发送来的字节串采用character_set_client代表的字符集进行解码，将解码后的字符串再按照character_set_connection代表的字符集进行编码。

  • 如果character_set_connection代表的字符集和具体操作的列使用的字符集一致，则直接进行相应操作，否则的话需要将请求中的字符串从character_set_connection代表的字符集转换为具体操作的列使用的字符集之后再进行操作。

  • 将从某个列获取到的字节串从该列使用的字符集转换为character_set_results代表的字符集后发送到客户端。

  • 客户端使用操作系统的字符集解析收到的结果集字节串。

在这个过程中各个系统变量的含义如下：

系统变量描述
character_set_client服务器解码请求时使用的字符集
character_set_connection服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results服务器向客户端返回数据时使用的字符集

一般情况下要使用保持这三个变量的值和客户端使用的字符集相同。

7 比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中.