MySQL 学习（1）—— 安装使用 MySQL | 8月更文挑战

开坦克的贝吉塔
351 阅读5分钟

数据库排名

数据库排名

常见数据库

RDBMS：Oracle、MySQL、PG、MSSQL NoSQL：MongoDB、Redis、ES NEWSQL（分布式）：TiDBit、Spanner、AliSql（RDS+DRDS）、OB、PolarDB

MySQL 企业版本 GA 选择

我司版本 5.7.16，发布时间：2016-9-28

下载方式：

Mysql -> DOWNLOADS -> MySQL Community (GPL) Downloads -> MySQL Community Server -> Archives

选择想要下载的版本即可。

MySQL 安装

登录机器

ssh root@IP

创建目录，用于存放安装包

我喜欢将项目放在 /home/work 目录下，服务放在 /home/service/ 目录下，比如 Nginx，MySQL，软件放在 /home/software 目录下。

  1. 创建 work 用户，并切换到 work 用户下，创建 /home/service/ 目录和 /home/software 目录，不使用 root 用户创建，避免一些权限问题。
# useradd -m work
# su - work
$ mkdir /home/software
$ mkdir /home/service

下载安装 MySQL

我选择的 MySQL 版本是 mysql5.7.26

  1. 下载
$ cd /home/software
$ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
$ mv mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mysql-5.7.26
  1. 解压
$ tar -zxvf mysql-5.7.26 -C /home/service/
$ mv /home/service/mysql-5.7.26-linux-glibc2.12-x86_64/ /home/service/mysql

创建 mysql 用户

我们需要创建一个用于运行 mysqld 的用户和组，MySQL 需要独立的用户来进行数据管理，我们创建用户为 mysql，这是 MySQL 内置自己使用的用户，我们创建出来即可。

$ exit
# useradd -s /sbin/nologin mysql
# id mysql
uid=1001(mysql) gid=1001(mysql) 组=1001(mysql)

修改环境变量

修改环境变量的目的就是为了将来使用命令方便一些。

# echo 'export PATH=/home/service/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
# source /etc/profile
# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

初始化数据（创建系统数据）

安装 MySQL，必须初始化数据目录，包括 mysql 系统数据库中的 table：

  • 对于某些 MySQL 安装方法，数据目录初始化是自动的，比如 Mac 的 brew install mysql。
  • 对于其它安装方法，必须手动初始化数据目录，其中包括在 Unix 和类似 Unix 的系统上从通用二进制发行版和源代码发行版进行安装，以及在 Windows 上从 XIP Archive 软件包进行安装。
  1. 创建系统数据
# mkdir /data/mysql/data -p
# mysqld --initialize --user=mysql --basedir=/home/service/mysql --datadir=/data/mysql/data
mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory
# yum -y install numactl
# mysqld --initialize --user=mysql --basedir=/home/service/mysql --datadir=/data/mysql/data
2021-01-13T14:17:51.963896Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-13T14:17:52.304834Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-01-13T14:17:52.399099Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-01-13T14:17:52.473765Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1f69721e-55aa-11eb-a47c-525400ea4205.
2021-01-13T14:17:52.477812Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-13T14:17:52.479083Z 1 [Note] A temporary password is generated for root@localhost: wZDqM,ATd3Fb

说明：

--initialize 参数作用：

  • 对于密码复杂度进行定制：12 位 4种
  • 密码过期时间：180 天
  • 给 root@localhost 用户设置临时密码

当然我们可以选择不设置密码，使用如下命令：

# rm -rf /data/mysql/data/*
# mysqld --initialize-insecure --user=mysql --basedir=/home/service/mysql --datadir=/data/mysql/data
2021-01-13T14:25:01.404438Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-13T14:25:01.820712Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-01-13T14:25:01.941025Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-01-13T14:25:02.021329Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1f713392-55ab-11eb-aca4-525400ea4205.
2021-01-13T14:25:02.028582Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-13T14:25:02.029771Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

说明：

--initialize-insecure 参数作用：

  • 无限制，不设置密码

注：mysql5.6 初始化数据方法：

/home/service/mysql/scripts/mysql_install_db

目录授权

将 mysql 运行目录和数据目录授权

# chown -R mysql.mysql /home/service/mysql/*
# chown -R mysql.mysql /data

配置文件

mysql 的配置文件是 /etc/my.cnf，当我们想要自定义配置一些文件时，可在该文件中配置。

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/home/service/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
EOF

启动 MySQL

# cp /home/service/mysql/support-files/mysql.server /etc/init.d/mysqld
# service mysqld start
Starting MySQL.Logging to '/data/mysql/data/VM-0-3-centos.err'.
 SUCCESS!
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
......

测试 MySQL 是否启动成功

# netstat -lnp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      13215/mysqld

# ps -ef | grep mysql
root     13049     1  0 11:22 pts/0    00:00:00 /bin/sh /home/service/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/VM-0-3-centos.pid
mysql    13215 13049  0 11:22 pts/0    00:00:03 /home/service/mysql/bin/mysqld --basedir=/home/service/mysql --datadir=/data/mysql/data --plugin-dir=/home/service/mysql/lib/plugin --user=mysql --log-error=VM-0-3-centos.err --pid-file=/data/mysql/data/VM-0-3-centos.pid --socket=/tmp/mysql.sock --port=3306

常见错误处理

这里列举一些比较常见的错误处理方式。

MySQL 无法启动

我们来试验一下，假设当我们把一些数据文件权限修改的时候，数据库应该就启动不了了。

# service mysqld stop
# cd /data/mysq/data
# chown root.root ibdata1
# service mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/data/VM-0-3-centos.pid).

但其实我们无法通过该条日志来判断是什么原因导致的 MySQL 无法启动的，因为 MySQL 启动失败的日志都是这个。那么我们应该如何处理呢，在数据目录下，也就是 /data/mysql/data 下，有一个错误文件，文件名.err，比如我的是 VM-0-3-centos.err。我们来查看一下日志：

# vim VM-0-3-centos.err
......
2021-01-14T06:32:55.240343Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-01-14T06:32:55.240358Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-01-14T06:32:55.240363Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2021-01-14T06:32:55.840908Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-01-14T06:32:55.840940Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-01-14T06:32:55.840947Z 0 [ERROR] Failed to initialize builtin plugins.
2021-01-14T06:32:55.840957Z 0 [ERROR] Aborting
......

其中的一些错误日志如下，'ibdata1' must be writable 这条错误日志可以看到，可能是 ibdata1 文件有问题，正好对应了我们修改了文件的权限。

更极端的一种情况，日志也无法写入，比如：

# cd /data/mysq/data
# chown -R root.root *

那么我们可以采用如下方式，将日志输出到屏幕上：

# service mysqld --defaults-file=/etc/my.cnf
Usage: mysqld  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]
[root@VM-0-3-centos data]# /home/service/mysql/bin/mysqld --defaults-file=/etc/my.cnf
2021-01-14T06:48:51.325923Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-14T06:48:51.326061Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2021-01-14T06:48:51.326094Z 0 [Note] /home/service/mysql/bin/mysqld (mysqld 5.7.26) starting as process 19593 ...
2021-01-14T06:48:51.333910Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-01-14T06:48:51.333941Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-14T06:48:51.333947Z 0 [Note] InnoDB: Uses event mutexes
2021-01-14T06:48:51.333953Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2021-01-14T06:48:51.333957Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-14T06:48:51.333962Z 0 [Note] InnoDB: Using Linux native AIO
2021-01-14T06:48:51.334272Z 0 [Note] InnoDB: Number of pools: 1
2021-01-14T06:48:51.334378Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-01-14T06:48:51.336333Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-01-14T06:48:51.346967Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-14T06:48:51.349513Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-14T06:48:51.359267Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-01-14T06:48:51.359287Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2021-01-14T06:48:51.359292Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2021-01-14T06:48:51.959778Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2021-01-14T06:48:51.959804Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-01-14T06:48:51.959811Z 0 [ERROR] Failed to initialize builtin plugins.
2021-01-14T06:48:51.959831Z 0 [ERROR] Aborting

2021-01-14T06:48:51.959848Z 0 [Note] Binlog end
2021-01-14T06:48:51.959901Z 0 [Note] Shutting down plugin 'MyISAM'
2021-01-14T06:48:51.959917Z 0 [Note] Shutting down plugin 'CSV'
2021-01-14T06:48:51.960368Z 0 [Note] /home/service/mysql/bin/mysqld: Shutdown complete

MySQL 修改管理员密码

# mysqladmin -uroot -p旧密码 password 新密码

修改了新密码之后，我无法直接通过不输入密码登陆了：

mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

这个时候怎么办呢，我把密码忘记了，登不进去了啊，没关系，我们可以采用安全模式登陆。

首先，我们先关闭 MySQL

# service mysqld stop

然后执行如下命令：

# mysqld_safe --skip-grant-tables --skip-networking &
  • --skip-grant-tables 参数含义是跳过授权表；
  • --skip-networking 参数含义是跳过远程登录；

执行成功后，我们登陆一下 MySQL，发现可以正常登录了。

# mysql

登录进去后，我们查看一下 root 用户的账号密码：

mysql> use mysql;
select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

我们修改一下密码，为了以后方便登录，我们还是不设置密码了

grant all on *.* to root@'localhost' identified by '';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

执行失败了，是因为我们跳过权限表了，这个时候我们就把权限表加载进来即可，然后再执行一下上述命令。

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'localhost' identified by '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost |                                           |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

然后我们退出重新登录一下。

mysql> exit
Bye

# service mysqld stop
Shutting down MySQL..2021-01-14T09:21:50.663434Z mysqld_safe mysqld from pid file /data/mysql/data/VM-0-3-centos.pid ended
 SUCCESS!
[1]+  完成                  mysqld_safe --skip-grant-tables --skip-networking
# service mysqld start
Starting MySQL. SUCCESS!
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
......

到这里，我们的 MySQL 安装使用基本就结束了。

总结

今天呢，我们学习了 MySQL 的安装使用，我们采用了通用二进制文件的方式来进行 MySQL 安装。

按照上面的步骤走一遍呢，我们基本上就能自己手动安装一个 MySQL 了。

上面的一些重要命令大家还是要记住滴！

avatar
文章
阅读
粉丝