mysql8.0数据库安装相关

1.mysql数据库相关

1.1检查自带db-删除-确认

检查自带mariadb

[root@iZ8vb4kqbeb43cx7m9xnj4Z ~]# rpm -qa | grep mariadb mariadb-libs-5.5.68-1.el7.x86_64 [root@iZ8vb4kqbeb43cx7m9xnj4Z ~]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64 [root@iZ8vb4kqbeb43cx7m9xnj4Z ~]# rpm -qa | grep mariadb [root@iZ8vb4kqbeb43cx7m9xnj4Z ~]#

检查是否安装mysql

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# rpm -qa|grep mysql [root@iZ8vb4kqbeb43cx7m9xnj4Z local]#

1.2上传安装包usr/local(下载忽略)

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# pwd /usr/local [root@iZ8vb4kqbeb43cx7m9xnj4Z local]# ls aegis bin etc games include lib lib64 libexec mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz sbin share src [root@iZ8vb4kqbeb43cx7m9xnj4Z local]#

1.3解压安装并重命名

文件名后缀为.gz解压方式：tar -zxvf 文件名

文件名后缀为.xz解压方式：tar -Jxvf 文件名

解压，需要等待一会

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# tar -Jvxf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz

重命名，是为了方便配置

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# mv mysql-8.0.35-linux-glibc2.17-x86_64 mysql8

1.4配置环境变量

环境变量的配置，是为了全局可使用mysql命令

没配置前：command not found

配置方式

修改etc/profile文件,在文件中追加配置“export PATH=$PATH:/usr/local/mysql8/bin”，并重新加载配置

[root@iZ8vb4kqbeb43cx7m9xnj4Z mysql8]# vim /etc/profile

1.5创建用户组

在/usr/local/ 目录下创建用户组和用户

创建mysql用户组

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# groupadd mysql

创建mysql用户组的用户jonmax

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# useradd -r -g mysql jonmax

创建数据目录

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# mkdir -p /data/mysql8_data

更改数组和权限

[root@iZ8vb4kqbeb43cx7m9xnj4Z local]# chown -R jonmax:mysql /data/mysql8_data [root@iZ8vb4kqbeb43cx7m9xnj4Z local]# chmod -R 750 /data/mysql8_data [root@iZ8vb4kqbeb43cx7m9xnj4Z local]#

1.6初始化

在/usr/local/etc/ 下创建 my.cnf 用于初始化mysql

配置如下：

[mysql]
# 默认字符集
default-character-set=utf8mb4
[client]
port       = 3306
socket     = /tmp/mysql.sock
[mysqld]
port       = 3306
server-id  = 3306
user       = jonmax
socket     = /tmp/mysql.sock
# 安装目录
basedir    = /usr/local/mysql8
# 数据存放目录
datadir    = /data/mysql8_data/mysql
log-bin    = /data/mysql8_data/mysql/mysql-bin
innodb_data_home_dir      =/data/mysql8_data/mysql
innodb_log_group_home_dir =/data/mysql8_data/mysql
# 日志及进程数据的存放目录
log-error =/data/mysql8_data/mysql/mysql.log
pid-file  =/data/mysql8_data/mysql/mysql.pid
# 服务端字符集
character-set-server=utf8mb4
lower_case_table_names=1
autocommit =1
##### 以上涉及文件夹名，注意修改
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
#query_cache_size = 128M
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
binlog_format=mixed
binlog_expire_logs_seconds =864000
# 创建表时使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

初始化脚本

[root@iZ8vb4kqbeb43cx7m9xnj4Z mysql8_data]# mysqld --defaults-file=/usr/local/etc/my.cnf --basedir=/usr/local/mysql8 --datadir=/data/mysql8_data/mysql --user=jonmax --initialize-insecure
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

注意：这里报错了，因为是新的服务器缺少依赖。dont afraid.....

[root@iZ8vb4kqbeb43cx7m9xnj4Z ~]# yum list installed | grep libaio [root@iZ8vb4kqbeb43cx7m9xnj4Z ~]#

没找到这个依赖，那就在线安装一下，等待安装成功

[root@iZ8vb4kqbeb43cx7m9xnj4Z ~]# yum install -y libaio

再次执行初始化脚本，没有报错，就成功了 --initialize-insecure 参数是初始化时不用创建root用户密码：

[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]# mysqld --defaults-file=/usr/local/etc/my.cnf --basedir=/usr/local/mysql8 --datadir=/data/mysql8_data/mysql --user=jonmax --initialize-insecure
[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]#

安全后台启动 这里已经配置过环境变量，所以在任何路径都可以后台启动

[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]# mysqld_safe --defaults-file=/usr/local/etc/my.cnf &
[1] 20590
[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]# 2024-03-06T06:27:02.597973Z mysqld_safe Logging to '/data/mysql8_data/mysql/mysql.log'.
2024-03-06T06:27:02.628501Z mysqld_safe Starting mysqld daemon with databases from /data/mysql8_data/mysql
​
[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]#

查看进程状态

[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]# ps -ef | grep mysql
root     20590 19143  0 14:27 pts/2    00:00:00 /bin/sh /usr/local/mysql8/bin/mysqld_safe --defaults-file=/usr/local/etc/my.cnf
jonmax   21128 20590  4 14:27 pts/2    00:00:01 /usr/local/mysql8/bin/mysqld --defaults-file=/usr/local/etc/my.cnf --basedir=/usr/local/mysql8 --datadir=/data/mysql8_data/mysql --plugin-dir=/usr/local/mysql8/lib/plugin --user=jonmax --log-error=/data/mysql8_data/mysql/mysql.log --open-files-limit=65535 --pid-file=/data/mysql8_data/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306
root     21206 19143  0 14:27 pts/2    00:00:00 grep --color=auto mysql
[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]#

1.7登录修改密码

• 第一次登录不需要密码，因为初始化时，没有输入密码

• 查看数据库，并使用至mysql库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
​
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
mysql>

• 查看用户表，修改密码，刷新权限：

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
​
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
​
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
​

• 创建一个用户用于远程访问

mysql> create user 'root'@'%';
Query OK, 0 rows affected (0.01 sec)
​
mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
​
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
​
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
​
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
​
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
​

• 需要查看3306的端口的状态，我这里没有开启防火墙所以无所谓

[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]# firewall-cmd --query-port=3306/tcp FirewallD is not running

• 永久开放端口

firewall-cmd --add-port=3306/tcp --permanent

• 重启防火墙

systemctl restart firewalld

---

1.8创建数据库和表试试

---

[root@iZ8vb4kqbeb43cx7m9xnj4Z etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2799
Server version: 8.0.35 MySQL Community Server - GPL
​
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
mysql> CREATE DATABASE IF NOT EXISTS jonmax ;
Query OK, 1 row affected (0.01 sec)
​
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jonmax             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
​
mysql> use jonmax;
Database changed
mysql> CREATE TABLE IF NOT EXISTS jon_user(
    ->   id INT  AUTO_INCREMENT PRIMARY KEY,
    ->   `name`VARCHAR(25),
    -> `password` VARCHAR(25)
    -> );
Query OK, 0 rows affected (0.02 sec)
​
mysql> DESC jon_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(25) | YES  |     | NULL    |                |
| password | varchar(25) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
​
mysql> INSERT INTO jon_user (`name`,`password`)VALUES('tom', '123'),('jerry', '123'),('jon', '123'),('max', '123');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
​
mysql> SELECT * FROM jon_user;
+----+-------+----------+
| id | name  | password |
+----+-------+----------+
|  1 | tom   | 123      |
|  2 | jerry | 123      |
|  3 | jon   | 123      |
|  4 | max   | 123      |
+----+-------+----------+
4 rows in set (0.00 sec)

1.9linux设置开启自启

#将mysql.server文件复制到指定路径/etc/init.d/并命名为mysqld
[root@iZ8vb4kqbeb43cx7m9xnj4Z support-files]# cp mysql.server /etc/init.d/mysqld
#将mysqld 文件添加可执行权限
[root@iZ8vb4kqbeb43cx7m9xnj4Z support-files]# chmod +x /etc/init.d/mysqld
#添加至启动列表
[root@iZ8vb4kqbeb43cx7m9xnj4Z support-files]# chkconfig --add mysqld
[root@iZ8vb4kqbeb43cx7m9xnj4Z support-files]# chkconfig --list
​
Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.
​
      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.
​
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off

重启测试，成功--good

[root@iZ8vb4kqbeb43cx7m9xnj4Z ~]# netstat -an | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN
tcp6       0      0 :::33060                :::*                    LISTEN
[root@iZ8vb4kqbeb43cx7m9xnj4Z ~]#

---