MySql ocp认证之MySql安装(一)

一、mysql安装：

[root@VM-24-5-centos mysql]# tar -xvf mysql-5.7.38-linux-glibc2.12-x86_64.tar
1

[root@VM-24-5-centos mysql]# mv mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql
[root@VM-24-5-centos mysql]# cd /usr/local/mysql/
[root@VM-24-5-centos mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
# 运行时会创建一个socket，存放在/var/lib/mysql下，存放ip地址、端口号
[root@VM-24-5-centos mysql]# mkdir -p /var/lib/mysql
[root@VM-24-5-centos mysql]# mkdir -p /usr/local/mysql/log
[root@VM-24-5-centos mysql]# mkdir -p /usr/local/mysql/log/error.err
[root@VM-24-5-centos mysql]# mkdir -p /usr/local/mysql/log/query.err
[root@VM-24-5-centos mysql]# mkdir -p /usr/local/mysql/data

[root@VM-24-5-centos ~]# groupadd mysql
[root@VM-24-5-centos ~]# useradd -r -g mysql -s /bin/false mysql
[root@VM-24-5-centos local]# chown -R mysql:mysql /usr/local/mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14

二、配置文件
查看mysql配置文件路径：

/usr/local/mysql/bin/mysqld --verbose --help |grep -A 1 ‘Default options’
具体内容：

[mysql]
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock

[mysqld]
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

character-set-server=utf8mb4
default-storage-engine=INNODB

max_allowed_packet = 16M
innodb_buffer_pool_size=200M
explicit_defaults_for_timestamp=1

log-output=FILE
general_log=0
general_log_file=/usr/local/mysql/log/general.err
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/log/query.err
long_query_time=10
log-error=/usr/local/mysql/log/error.err
default-authentication-plugin=mysql_native_password
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

三、数据库初始化：

[root@VM-24-5-centos ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
1

注意：–user参数指定初始化的用户，如果不指定则按照当前执行命令的用户进行初始化，如果是root用户，则使用root用户进行初始化各种文件夹。

初始化完毕后可以在data目录下看到有很多数据库实例:

[root@VM-24-5-centos mysql]# cd data/
[root@VM-24-5-centos data]# ls
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  performance_schema  public_key.pem   server-key.pem
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  mysql        private_key.pem     server-cert.pem  sys
1
2
3
4

有问题可以查看log/error.err文件查看

2022-08-25T07:15:02.171562Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-08-25T07:15:02.244319Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-08-25T07:15:02.313131Z 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: a2ece67d-2445-11ed-a8dd-5254009245d5.
2022-08-25T07:15:02.324209Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-08-25T07:15:02.613856Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-08-25T07:15:02.613865Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-08-25T07:15:02.614325Z 0 [Warning] CA certificate ca.pem is self signed.
2022-08-25T07:15:02.687151Z 1 [Note] A temporary password is generated for root@localhost: ypjx/B+-X5xk
1
2
3
4
5
6
7
8

最后一行显示产生了一个临时密码：ypjx/B±X5xk

四、mysql的启动和停止
启动mysql，要指定.cnf文件启动

/usr/local/mysql-slave/bin/mysqld --defaults-file=/usr/local/mysql-slave/conf/my.cnf &
也可以通过mysqld_safe启动，当用mysqld_safe启动时，mysqld进程如果挂了，mysqld_safe会自动重启msqld进程。
/usr/local/mysql-slave/bin/mysqld_safe --defaults-file=/usr/local/mysql-slave/conf/my.cnf --user=mysql &

停止MYSQL，需要指定对应的sock文件。

/usr/local/mysql-slave/bin/mysqladmin -uroot -p shutdowm -S /var/lib/mysql-slave/mysql.sock

登陆3307端口的mysql

/usr/local/mysql-slave/bin/mysql -S /var/lib/mysql-slave/mysql.sock -P 3307

下一步：配置Linux启动和停止脚本

[root@VM-24-5-centos log]# vim /usr/lib/systemd/system/mysqld.service

[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=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=65536
LimitNPROC=65536
1
2
3
4
5
6
7
8
9
10
11
12
13
14

重新加载systemctl配置文件：
[root@VM-24-5-centos log]# systemctl daemon-reload
启动mysql服务
[root@VM-24-5-centos mysql]# systemctl start mysqld
查看mysql进程：
[root@VM-24-5-centos mysql]# ps -ef|grep mysql
mysql 265975 1 0 12:22 ? 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root 267275 262410 0 12:28 pts/0 00:00:00 grep --color=auto mysql
查看mysql进程状态：
[root@VM-24-5-centos mysql]# systemctl status mysqld
随机启动：
[root@VM-24-5-centos mysql]# systemctl enable mysqld
五、mysql登录
执行mysql命令，报下面的错：
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
需要安装下面的包：

yum install -y ncurses-compat-libs

执行mysql命令，报下面的错：
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
创建的mysql.sock在/tmp/目录下，需要创建个软连接指向/var/lib/mysql/mysql.sock

ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

使用临时密码登录mysql：

[root@VM-24-5-centos log]# mysql -uroot -pypjx/B±X5xk

修改root密码：

mysql> alter user root@‘localhost’ identified with mysql_native_password by ‘123456’;
mysql> flush privileges;

允许root远程登录：
mysql的登录名实际由两部分组成，一个是用户名，一个是登录的地址。可以看一下mysql库中的user表，主键是host和user：

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
1
2
3
4
5
6

此时，用外网的root用户登录不了mysql，需要手动创建个host为%的root用户

mysql> create user root@‘%’ identified by ‘123456’;
mysql> flush privileges;

外网登录后无法创建数据库，提示没有权限。需要使用mysql进行授权：

mysql> grant all on *.* to root@'%';
mysql> flush privileges;
1
2

all表示所有权限。