记录 MySQL的学习过程（1） 安装及初始化篇

1.数据库的分类

RDBMS 特点:二维表，有严格的行列结构及约束条件，支持更负责的查询
产品: oracle MySQL PG MSSQL

NoSQL 特点: 没有严格的行列结构，可伸缩性，扩展性，高性能
产品: 键值对（KV）: Redis Memcache
Document :MongoDB
搜索:ES
Wide:Cassandra

NEWSQL 特点: RDBMS+NoSQL+分布式
产品: Tidb Spanner ALISQL(RDS+DRDS) OB PloarDB

2.版本的选择

一般选择 GA 稳定版

5.6.38（2017-09-13）发布
5.7.20（2017-09-13）发布
8.0 一般用于测试环境

3.数据库的下载 一般选择二进制版本(绿色版） 即 Linux - Generic
[root@localhost ~]# rpm -qa | grep mariadb #先移除本机安装过的mariadb
mariadb-libs-5.5.64-1.el7.x86_64
[root@localhost ~]# yum -y remove mariadb-libs-5.5.64-1.el7.x86_64
[root@localhost ~]# mkdir /application
[root@localhost ~]# mkdir /server/tools -p
[root@localhost tools]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@localhost tools]# tar xvzf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@localhost tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql/ #此操作将移动文件夹并重命名为mysql
[root@localhost ~]# useradd -s /sbin/nologin mysql #创建mysql用户

下面修改环境变量

[root@localhost ~]# vim /etc/profile
在最后一行后添加
export PATH=/application/mysql/bin:$PATH
[root@localhost ~]# source /etc/profile

[root@localhost ~]# mysql -V #验证
mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper

 

4.数据库的组成

一般来说数据库的组成分为

\application （程序部分）
\data (数据部分）

在生产环境中，这2个部分应该分开，最好放到不同的磁盘中

关掉虚拟机，向其中新增一块20g的磁盘

[root@localhost ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 60G 0 disk
├─sda1 8:1 0 300M 0 part /boot
├─sda2 8:2 0 3G 0 part [SWAP]
└─sda3 8:3 0 56.7G 0 part /
sdb 8:16 0 20G 0 disk #sbd为新增盘

[root@localhost ~]# mkfs.xfs /dev/sdb #linux下不用分区 直接就可以格式化

[root@localhost ~]# blkid #查看uuid
/dev/sdb: UUID="5016bf0e-1e46-428e-a177-fb2f20a3c5e6" TYPE="xfs"
...

[root@localhost ~]# vim /etc/fstab #编辑fstab 在最后一行增加一下内容
UUID=5016bf0e-1e46-428e-a177-fb2f20a3c5e6 /data xfs defaults 0 0
然后mount一下，就可以看到挂载成功了
[root@localhost ~]# mount -a
[root@localhost ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 980M 0 980M 0% /dev
tmpfs 991M 0 991M 0% /dev/shm
tmpfs 991M 9.5M 981M 1% /run
tmpfs 991M 0 991M 0% /sys/fs/cgroup
/dev/sda3 57G 4.4G 53G 8% /
/dev/sda1 297M 114M 184M 39% /boot
tmpfs 199M 0 199M 0% /run/user/0
/dev/sdb 20G 33M 20G 1% /data

下面给mysql用户授权
[root@localhost ~]# chown -R mysql.mysql /application/*
[root@localhost ~]# chown -R mysql.mysql /data

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

5.6 和 5.7 初始化的方式不一样

5.6的初始化命令为
/application/mysql/scripts/mysql_install_db
5.7的初始化操作如下：

[root@localhost ~]# mkdir /data/mysql/data -p #创建存放系统数据的文件夹
[root@localhost ~]# chown -R mysql.mysql /data #赋予权限

[root@localhost ~]#yum -y install libaio-devel #安装依赖包

[root@localhost ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data #初始化

2020-04-15T06:46:26.554152Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --
explicit_defaults_for_timestamp server option (see documentation for more details).
2020-04-15T06:46:28.486827Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-04-15T06:46:29.106452Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-04-15T06:46:29.316488Z 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: d5d1d9e9-7ee4-11ea-8562-000c292cf377.
2020-04-15T06:46:29.319034Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-15T06:46:29.383438Z 1 [Note] A temporary password is generated for root@localhost: PQ3h1i6D8f!O

使用上面的初始化命令会做一下几件事

a.定义密码强度 12位 4种混合
b.密码过期时间 180天
c.给root用户设置临时密码

也可以是用别的命令来取消上面的3种限制

 

先删除之前的初始化数据（否则无法进行）

[root@localhost ~]# rm -fr /data/mysql/data/*
[root@localhost ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data #无
限制，无密码

刚才的初始化操作，实际上就是在向/data/mysql/data/中写入数据，这些数据我们称之为系统数据

下面先向my.cnf中写一些配置进去

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

6.启动数据库

a.sys-v的方式

[root@localhost ~]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# service mysqld start # stop|restart 都可以使用
也可以使用绝对路径
[root@localhost ~]# /etc/init.d/mysqld stop # start|restart

chkconfig mysql on   设置开机自动启动

b.systemd 的方式

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
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf #根据实际情况替换路径
LimitNOFILE = 5000
EOF

然后就可以使用 systemctl start mysqld 命令了

[root@localhost ~]# netstat -tnlp | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 2514/mysqld

此时发现3306 port已经起来了

 

7.如何分析数据库的错误日志

[root@localhost ~]# cd /data/mysql/data/
[root@localhost ~]# chown root.root ibdata1 #修改此文件的权限为root所有

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# systemctl status mysqld

此时发现数据库已经起不来了

[root@localhost data]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@localhost data]# service mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/data/localhost.localdomain.pid).

[root@localhost data]# /etc/init.d/mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/data/localhost.localdomain.pid).

[root@localhost data]# ll
total 110660
-rw-r-----. 1 mysql mysql 56 Apr 15 15:01 auto.cnf
-rw-r-----. 1 mysql mysql 283 Apr 15 16:33 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Apr 15 16:33 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Apr 15 16:33 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Apr 15 15:01 ib_logfile1
-rw-r-----. 1 mysql mysql 29403 Apr 15 16:39 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Apr 15 15:01 mysql
drwxr-x---. 2 mysql mysql 8192 Apr 15 15:01 performance_schema
drwxr-x---. 2 mysql mysql 8192 Apr 15 15:01 sys

然后在当前目录发现一个localhost.localdomain.err的文件，查看这个文件找到[ERROR]开头的，随后发现
...
2020-04-15T08:39:36.514264Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
...
由此可以发现是因为 ibdata1 这个文件没有写权限导致的

如果连写日志的权限都没有的话，则可以直接使用命令 将日志输出到屏幕上，用这种方法仍旧可以找到问题所在

[root@localhost data]# /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
2020-04-15T08:53:39.695825Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-04-15T08:53:39.696058Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-04-15T08:53:39.696126Z 0 [Note] /application/mysql/bin/mysqld (mysqld 5.7.26) starting as process 3379 ...
2020-04-15T08:53:39.709092Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-04-15T08:53:39.709153Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-04-15T08:53:39.709169Z 0 [Note] InnoDB: Uses event mutexes
2020-04-15T08:53:39.709222Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2020-04-15T08:53:39.709242Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-15T08:53:39.709256Z 0 [Note] InnoDB: Using Linux native AIO
2020-04-15T08:53:39.709936Z 0 [Note] InnoDB: Number of pools: 1
2020-04-15T08:53:39.710223Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-04-15T08:53:39.714469Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-04-15T08:53:39.736559Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-15T08:53:39.743241Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-04-15T08:53:39.753565Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-04-15T08:53:39.753692Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-04-15T08:53:39.753717Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-04-15T08:53:40.359414Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-04-15T08:53:40.359474Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-04-15T08:53:40.359493Z 0 [ERROR] Failed to initialize builtin plugins.
2020-04-15T08:53:40.359518Z 0 [ERROR] Aborting

2020-04-15T08:53:40.359546Z 0 [Note] Binlog end
2020-04-15T08:53:40.359723Z 0 [Note] Shutting down plugin 'MyISAM'
2020-04-15T08:53:40.359779Z 0 [Note] Shutting down plugin 'CSV'
2020-04-15T08:53:40.360389Z 0 [Note] /application/mysql/bin/mysqld: Shutdown complete

 

8.设置mysql的root密码
[root@localhost ~]# mysqladmin -uroot -p password newpassword
Enter password: 此处直接回车
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure

[root@localhost ~]# mysql -uroot -pnewpassword #使用刚刚设置的密码登录

9.重置root密码

先进入到安全模式（跳过授权表，关闭网络功能）
[root@localhost ~]# mysqld_safe --skip-grant-tables --skip-networking &
[root@localhost ~]# mysql
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.01 sec)
可以看到密码以及加密形式存放到了authentication_string中
因为前面关闭了授权表，所以先刷新下授权，然后修改就可以了

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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

也可以使用 alter user root@'localhost' identified by 'newchangedpassword'更改，效果是一样的

然后重启数据库即可

[root@localhost ~]# /etc/init.d/mysqld start