Centos7使用yum安装MySQL8和简单设置
参考：Centos7下MYSQL8安装
注意，文中的卸载内容我没有尝试过，尤其是其中的删除文件部分，请谨慎。
下载mysql的rpm源
地址：MySQL Yum Repository
下载MySQL的RPM源，安装成功后会自动覆盖MariaDB。下载完成后上传到服务器。有网络的话也可直接wget命令下载。
安装repo并更新yum缓存
[root@localhost ~]# rpm -ivh /home/wg/Downloads/mysql84-community-release-el7-1.noarch.rpm
# 要确保路径正确
[root@localhost ~]# ll /etc/yum.repos.d/ | grep mysql
-rw-r--r--. 1 root root 3070 Apr 22 2024 mysql-community-debuginfo.repo
-rw-r--r--. 1 root root 2751 Apr 22 2024 mysql-community.repo
-rw-r--r--. 1 root root 2870 Apr 22 2024 mysql-community-source.repo
# 可以看到在/etc/yum.repos.d/下生成两个repo文件
[root@localhost ~]# yum clean all
[root@localhost ~]# yum makecache
[root@localhost ~]# yum repolist all | grep mysql
# 查看yum中的MySQL版本
安装和设置mysql
yum安装
[root@localhost ~]# yum -y install mysql-community-server
# 安装
[root@localhost ~]# systemctl start mysqld
# 开启服务
[root@localhost ~]# systemctl enable mysqld
# 开机自启
初次登陆和设置密码
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
2021-01-03T09:56:56.709802Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Hj9Vr2?sWqp
# 查看初始密码 Hj9Vr2?sWqp
[root@localhost ~]# mysql -u root -p
# 输入初始密码登录mysql
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.3
Copyright (c) 2000, 2024, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpassword';
Query OK, 0 rows affected (0.07 sec)
# 修改为自己的密码，密码需要包含大小写字母数字和特殊符号
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
# FLUSH PRIVILEGES命令在MySQL中用于刷新用户权限，使得对用户权限的更改立即生效，而无需重启MySQL服务器
mysql> CREATE USER 'wg'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
mysql> SHOW PLUGINS;
+----------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndbinfo | DISABLED | STORAGE ENGINE | NULL | GPL |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| mysql_native_password | DISABLED | AUTHENTICATION | NULL | GPL |
+----------------------------------+----------+--------------------+---------+---------+
48 rows in set (0.00 sec)
# 尝试创建远程登录用户时发生了错误。查看插件安装情况，确认已经安装mysql_native_password插件，但是插件的状态不是active而是disabled即未激活。
创建远程用户并开放权限
怎么解决此问题呢？
参考：Mysql8设置密码规则为mysql_native_password
我们可以修改配置文件my.cnf，在其中加入内容：
[mysqld]
mysql_native_password=ON # 添加此行
不要添加defult_authentication_plugin=mysql_native_password，会导致mysql无法启动。
修改完毕保存退出，重启mysql服务。
[root@localhost ~]# systemctl restart mysqld
# 重启mysql服务
[wg@localhost ~]$ mysql -u root -p
# 登录mysql，可以用之前的show plugins；验证是否成功通过配置文件激活mysql_native_password插件。
mysql> CREATE USER 'wg'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.06 sec)
# 添加了一个用户wg，因为我们想用户wg可以远程登陆
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| wg | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
# 查看用户使用的插件
mysql> ALTER USER 'wg'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.33 sec)
# 修改wg的密码认证方式
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
# FLUSH PRIVILEGES命令在MySQL中用于刷新用户权限，使得对用户权限的更改立即生效，而无需重启MySQL服务器
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| wg | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
# 可以看到用户wg使用的认证方式已经更改过来
mysql> grant all privileges on *.* to 'wg'@'%';
Query OK, 0 rows affected (0.06 sec)
# 为用户wg开放权限
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
导入csv数据
csv数据示例：
"Date/Time","Lat","Lon","Base"
"9/1/2014 0:01:00",40.2201,-74.0021,"B02512"
"9/1/2014 0:01:00",40.75,-74.0027,"B02512"
"9/1/2014 0:03:00",40.7559,-73.9864,"B02512"
"9/1/2014 0:06:00",40.745,-73.9889,"B02512"
"9/1/2014 0:11:00",40.8145,-73.9444,"B02512"
"9/1/2014 0:12:00",40.6735,-73.9918,"B02512"
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.01 sec)
# 找到允许导入导出数据的目录
mysql> use uber;
mysql> CREATE TABLE rawdata_sep ( date DATETIME, lat DECIMAL(9,6), lon DECIMAL(9,6), base_code VARCHAR(10) );
Query OK, 0 rows affected (0.22 sec)
# 创建合适的表
mysql> LOAD DATA INFILE '/var/lib/mysql-files/uber-raw-data-sep14.csv' INTO TABLE rawdata_sep FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS
-> (@var1,@var2,@var3,@var4)
-> SET Date=STR_TO_DATE(@var1,'%m/%d/%Y %H:%i:%s'),
-> lat=@var2,
-> lon=@var3,
-> base_code=@var4;
Query OK, 1028136 rows affected (17.52 sec)
Records: 1028136 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from rawdata_sep limit 10;
+---------------------+-----------+------------+-----------+
| date | lat | lon | base_code |
+---------------------+-----------+------------+-----------+
| 2014-09-01 00:01:00 | 40.220100 | -74.002100 | B02512 |
| 2014-09-01 00:01:00 | 40.750000 | -74.002700 | B02512 |
| 2014-09-01 00:03:00 | 40.755900 | -73.986400 | B02512 |
| 2014-09-01 00:06:00 | 40.745000 | -73.988900 | B02512 |
| 2014-09-01 00:11:00 | 40.814500 | -73.944400 | B02512 |
| 2014-09-01 00:12:00 | 40.673500 | -73.991800 | B02512 |
| 2014-09-01 00:15:00 | 40.747100 | -73.647200 | B02512 |
| 2014-09-01 00:16:00 | 40.661300 | -74.269100 | B02512 |
| 2014-09-01 00:32:00 | 40.374500 | -73.999900 | B02512 |
| 2014-09-01 00:33:00 | 40.763300 | -73.977300 | B02512 |
+---------------------+-----------+------------+-----------+
10 rows in set (0.00 sec)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· SQL Server 2025 AI相关能力初探
· 单线程的Redis速度为什么快？
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码