centos7安装配置mysql的正确姿势_mariadb-5.5.68-1.el7.x86_64

centos7 安装mysql有时候会出现莫名其妙的错误，好多时候可能不成功，这里简单记录一下正确配置的过程。

centos自带的是 mariadb，先查看是否还在，先卸载：

rpm -qa|grep mariadb
um remove  -y  mariadb-libs-5.5.68-1.el7.x86_64

[root@localhost opt]# rpm -qa|grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64

[root@localhost opt]# yum remove  -y  mariadb-libs-5.5.68-1.el7.x86_64
Loaded plugins: fastestmirror, langpacks
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be erased
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.68-1.el7 for package: 1:mariadb-5.5.68-1.el7.x86_64
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.68-1.el7 for package: 1:mariadb-server-5.5.68-1.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.68-1.el7 will be erased
---> Package mariadb-server.x86_64 1:5.5.68-1.el7 will be erased
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be erased
---> Package postfix.x86_64 2:2.10.1-7.el7 will be erased
--> Finished Dependency Resolution
 
Dependencies Resolved
 
==============================================================================================================================================================
 Package                                  Arch                             Version                                  Repository                           Size
==============================================================================================================================================================
Removing:
 mariadb-libs                             x86_64                           1:5.5.68-1.el7                           @base                               4.4 M
Removing for dependencies:
 mariadb                                  x86_64                           1:5.5.68-1.el7                           @base                                49 M
 mariadb-server                           x86_64                           1:5.5.68-1.el7                           @base                                58 M
 perl-DBD-MySQL                           x86_64                           4.023-6.el7                              @base                               323 k
 postfix                                  x86_64                           2:2.10.1-7.el7                           @anaconda                            12 M
 
Transaction Summary
==============================================================================================================================================================
Remove  1 Package (+4 Dependent packages)
 
Installed size: 124 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Erasing    : 1:mariadb-server-5.5.68-1.el7.x86_64                                                                                                       1/5 
warning: /var/log/mariadb/mariadb.log saved as /var/log/mariadb/mariadb.log.rpmsave
  Erasing    : 1:mariadb-5.5.68-1.el7.x86_64                                                                                                              2/5 
  Erasing    : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                          3/5 
  Erasing    : 2:postfix-2.10.1-7.el7.x86_64                                                                                                              4/5 
  Erasing    : 1:mariadb-libs-5.5.68-1.el7.x86_64                                                                                                         5/5 
  Verifying  : 1:mariadb-server-5.5.68-1.el7.x86_64                                                                                                       1/5 
  Verifying  : 2:postfix-2.10.1-7.el7.x86_64                                                                                                              2/5 
  Verifying  : 1:mariadb-libs-5.5.68-1.el7.x86_64                                                                                                         3/5 
  Verifying  : 1:mariadb-5.5.68-1.el7.x86_64                                                                                                              4/5 
  Verifying  : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                          5/5 
 
Removed:
  mariadb-libs.x86_64 1:5.5.68-1.el7                                                                                                                          
 
Dependency Removed:
  mariadb.x86_64 1:5.5.68-1.el7       mariadb-server.x86_64 1:5.5.68-1.el7       perl-DBD-MySQL.x86_64 0:4.023-6.el7       postfix.x86_64 2:2.10.1-7.el7      
 
Complete!
[root@localhost opt]# rpm -qa|grep mariadb
[root@localhost opt]# ls

下载mysql安装包，需要下载社区版。

可以到这里去找：

MySQL :: Download MySQL Community Server (Archived Versions)

 也可以直接下载：

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar

下载后获得这个文件：

mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar

解压缩。

# 按照以下顺序进行安装，因为它们之间存在依赖关系

         common --> libs --> clients --> server

yum -y install mysql-community-common-*
yum -y install mysql-community-libs-5.7.30-1.el7.x86_64.rpm
yum -y install mysql-community-client-5.7.30-1.el7.x86_64.rpm
yum -y install mysql-community-server-5.7.30-1.el7.x86_64.rpm

初始化之前修改mysql的配置文件/etc/my.cnf

启动mysql服务：

systemctl start mysqld.service

 执行mysql，发现已经成功进入：

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
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> exit
Bye
[root@localhost ~]# rpm -qi mysql-server
package mysql-server is not installed
[root@localhost ~]# rpm -e mysql 
error: package mysql is not installed
[root@localhost ~]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2022-01-22 04:28:48 PST; 5min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 44384 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 44362 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 44387 (mysqld)
    Tasks: 28
   Memory: 192.1M
   CGroup: /system.slice/mysqld.service
           └─44387 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 
Jan 22 04:28:48 localhost.localdomain systemd[1]: Starting MySQL Server...
Jan 22 04:28:48 localhost.localdomain systemd[1]: Started MySQL Server.

查看日志，发现报错：

[root@localhost ~]# tail /var/log/mysqld.log 
2022-01-22T04:28:48.820789-08:00 0 [ERROR] Native table 'performance_schema'.'session_status' has the wrong structure
2022-01-22T04:28:48.820801-08:00 0 [ERROR] Native table 'performance_schema'.'variables_by_thread' has the wrong structure
2022-01-22T04:28:48.820813-08:00 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
2022-01-22T04:28:48.820858-08:00 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure
2022-01-22T04:28:48.820968-08:00 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).
2022-01-22T04:28:48.820980-08:00 0 [ERROR] mysql.user has no `Event_priv` column at position 28
2022-01-22T04:28:48.821074-08:00 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
2022-01-22T04:28:48.821081-08:00 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2022-01-22T04:28:48.821359-08:00 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.30-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

进行远程访问授权，发现报错：

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";
ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 42. Created with MySQL 50568, now running 50730. Please use mysql_upgrade to fix this error.
mysql> exit
Bye

进行修复：

[root@localhost mysql-5.7.30-1.el7.x86_64.rpm-bundle]# mysql_upgrade -u root -p --force
Enter password: 
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: (non fatal) [ERROR] 1728: Cannot load from mysql.proc. The table is probably corrupted
mysql_upgrade: (non fatal) [ERROR] 1545: Failed to open mysql.event
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

重启服务后，重新授权，正常了。

[root@localhost mysql-5.7.30-1.el7.x86_64.rpm-bundle]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost mysql-5.7.30-1.el7.x86_64.rpm-bundle]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
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> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
 
mysql> exit
Bye

处理防火墙：

[root@localhost mysql-5.7.30-1.el7.x86_64.rpm-bundle]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@localhost mysql-5.7.30-1.el7.x86_64.rpm-bundle]# firewall-cmd --reload
success

远程访问需要root密码，修改密码，发现报错。

mysql> update user set password=password('jumpserver') where user='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update user set password=password('123456') where user='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

mysql5.7社区版语法有变更。

mysql> update mysql.user set authentication_string=password('root') where user='root' ;
Query OK, 4 rows affected, 1 warning (0.00 sec)
Rows matched: 5  Changed: 4  Warnings: 1
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;
Bye

进行验证

连接成功。 

花了大半天时间，用网上很多教程都没有安装成功，这里却安装成功了，因此进行记录。