MySql高级

MySql高级：

mysql5.7.26:

一、安装：

下载地地址：http://mirrors.sohu.com/mysql/MySQL-5.7/下载mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

下载完成后上传：

列出所有被安装的rpm package

--因为有冲突所以需要先卸载
rpm -qa | grep mariadb
--卸载
yum remove mariadb-libs-5.5.64-1.el7.x86_64

安装：

 yum localinstall mysql-community-common-5.7.26-1.el7.x86_64.rpm 
 
 yum localinstall mysql-community-libs-5.7.26-1.el7.x86_64.rpm
 
 yum localinstall mysql-community-client-5.7.26-1.el7.x86_64.rpm 
 
 yum localinstall mysql-community-server-5.7.26-1.el7.x86_64.rpm 

安装过程

[root@localhost ~]# yum localinstall mysql-community-common-5.7.26-1.el7.x86_64.rpm 
Loaded plugins: security
Setting up Local Package Process
Examining mysql-community-common-5.7.26-1.el7.x86_64.rpm: mysql-community-common-5.7.26-1.el6.i686
Marking mysql-community-common-5.7.26-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-common.i686 0:5.7.26-1.el6 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=======================================================================================================================================================================================================
 Package      Arch    Version     Repository        Size
=======================================================================================================================================================================================================
Installing:
 mysql-community-common    i686    5.7.26-1.el6    /mysql-community-common-5.7.26-1.el6.i686    2.5 M
 
Transaction Summary
=======================================================================================================================================================================================================
Install 1 Package(s)
 
Total size: 2.5 M
Installed size: 2.5 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
 Installing : mysql-community-common-5.7.26-1.el6.i686                  1/1 
 Verifying : mysql-community-common-5.7.26-1.el6.i686                  1/1 
 
Installed:
 mysql-community-common.i686 0:5.7.26-1.el6                    
 
Complete!
[root@localhost ~]# yum localinstall mysql-community-libs-5.7.26-1.el7.x86_64.rpm 
Loaded plugins: security
Setting up Local Package Process
Examining mysql-community-libs-5.7.26-1.el7.x86_64.rpm: mysql-community-libs-5.7.26-1.el6.i686
Marking mysql-community-libs-5.7.26-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-libs.i686 0:5.7.26-1.el6 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=======================================================================================================================================================================================================
 Package      Arch    Version     Repository        Size
=======================================================================================================================================================================================================
Installing:
 mysql-community-libs    i686    5.7.26-1.el6    /mysql-community-libs-5.7.26-1.el6.i686    7.8 M
 
Transaction Summary
=======================================================================================================================================================================================================
Install 1 Package(s)
 
Total size: 7.8 M
Installed size: 7.8 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
 Installing : mysql-community-libs-5.7.26-1.el6.i686                  1/1 
 Verifying : mysql-community-libs-5.7.26-1.el6.i686                  1/1 
 
Installed:
 mysql-community-libs.i686 0:5.7.26-1.el6                    
 
Complete!
[root@localhost ~]# yum localinstall mysql-community-client-5.7.26-1.el7.x86_64.rpm 
Loaded plugins: security
Setting up Local Package Process
Examining mysql-community-client-5.7.26-1.el7.x86_64.rpm: mysql-community-client-5.7.26-1.el6.i686
Marking mysql-community-client-5.7.26-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.i686 0:5.7.26-1.el6 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=======================================================================================================================================================================================================
 Package      Arch    Version     Repository        Size
=======================================================================================================================================================================================================
Installing:
 mysql-community-client    i686    5.7.26-1.el6    /mysql-community-client-5.7.26-1.el6.i686    87 M
 
Transaction Summary
=======================================================================================================================================================================================================
Install 1 Package(s)
 
Total size: 87 M
Installed size: 87 M
Is this ok [y/N]: ^[[A^Hy
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
 Installing : mysql-community-client-5.7.26-1.el6.i686                  1/1 
 Verifying : mysql-community-client-5.7.26-1.el6.i686                  1/1 
 
Installed:
 mysql-community-client.i686 0:5.7.26-1.el6                    
 
Complete!
[root@localhost ~]# yum localinstall mysql-community-server-5.7.26-1.el7.x86_64.rpm 
Loaded plugins: security
Setting up Local Package Process
Examining mysql-community-server-5.7.26-1.el7.x86_64.rpm: mysql-community-server-5.7.26-1.el6.i686
Marking mysql-community-server-5.7.26-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.i686 0:5.7.26-1.el6 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=======================================================================================================================================================================================================
 Package      Arch    Version     Repository        Size
=======================================================================================================================================================================================================
Installing:
 mysql-community-server    i686    5.7.26-1.el6    /mysql-community-server-5.7.26-1.el6.i686    695 M
 
Transaction Summary
=======================================================================================================================================================================================================
Install 1 Package(s)
 
Total size: 695 M
Installed size: 695 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
 Installing : mysql-community-server-5.7.26-1.el6.i686                  1/1 
 Verifying : mysql-community-server-5.7.26-1.el6.i686                  1/1 
 
Installed:
 mysql-community-server.i686 0:5.7.26-1.el6                    
 
Complete!

安装完成查看版本：

[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

启动 MySQL:

--启动mysqld服务
[root@localhost ~]# service mysqld start
--查看服务状态
[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 Fri 2021-03-12 04:37:21 EST; 25s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3423 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3349 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3426 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3426 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Mar 12 04:37:17 localhost.localdomain systemd[1]: Starting MySQL Server...
Mar 12 04:37:21 localhost.localdomain systemd[1]: Started MySQL Server.

MySQL 无法登陆的解决办法:

--第一次启动后登陆出现下面情况,这是因较新版本的 MySQL 因为安全的考虑，会设置默认密码
[root@localhost ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
//通过下面得到密码
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
2021-03-12T09:37:18.121604Z 1 [Note] A temporary password is generated for root@localhost: q09taAMgsd:7

这样就找到了密码，如果上面方法不可行可以看看是否有 /root/.mysql_secret 文件

重置密码:

无论是获取到了默认密码还是强行修改了密码。新版 MySQL 在第一次通过密码登录后会无法执行SQL：

mysql> select host from mysql.user where User='root';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

这是因为 MySQL 为了安全会要求修改初始密码。

mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '111111';

允许远程访问：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '111111' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

修改字符集：
修改mysql配置文件，“/etc/my.cnf”

#在[mysqld]部分添加：
character-set-server=utf8
#在文件末尾新增[client]段，并在[client]段添加：
default-character-set=utf8

二、测试：