当前位置:首页 » Mysql技术教程

关于数据库之MySQL实战

2017-12-31 08:00 本站整理 浏览(30)

[root@marklin ~]# cd /usr/local/mysql

[root@marklin mysql]# ll

total 579372

-rw-r--r--. 1 root root 593274880 Dec 30 16:35 mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar

[root@marklin mysql]# tar xvf mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar 

mysql-community-minimal-debuginfo-5.7.20-1.el7.x86_64.rpm

mysql-community-embedded-5.7.20-1.el7.x86_64.rpm

mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm

mysql-community-libs-5.7.20-1.el7.x86_64.rpm

mysql-community-embedded-compat-5.7.20-1.el7.x86_64.rpm

mysql-community-devel-5.7.20-1.el7.x86_64.rpm

mysql-community-server-5.7.20-1.el7.x86_64.rpm

mysql-community-server-minimal-5.7.20-1.el7.x86_64.rpm

mysql-community-client-5.7.20-1.el7.x86_64.rpm

mysql-community-test-5.7.20-1.el7.x86_64.rpm

mysql-community-common-5.7.20-1.el7.x86_64.rpm

mysql-community-embedded-devel-5.7.20-1.el7.x86_64.rpm

[root@marklin mysql]# rpm -qa|grep mariadb

mariadb-server-5.5.56-2.el7.x86_64

mariadb-libs-5.5.56-2.el7.x86_64

mariadb-5.5.56-2.el7.x86_64

[root@marklin mysql]# rpm -e --nodeps mariadb-server-5.5.56-2.el7.x86_64

[root@marklin mysql]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

[root@marklin mysql]# rpm -e --nodeps mariadb-5.5.56-2.el7.x86_64

[root@marklin mysql]# ll

total 1158752

-rw-r--r--. 1 root root  593274880 Dec 30 16:35 mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar

-rw-r--r--. 1 7155 31415  25090196 Sep 14 11:44 mysql-community-client-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415    278300 Sep 14 11:44 mysql-community-common-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415   3778120 Sep 14 11:44 mysql-community-devel-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415  46238924 Sep 14 11:44 mysql-community-embedded-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415  24077232 Sep 14 11:44 mysql-community-embedded-compat-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 128440800 Sep 14 11:44 mysql-community-embedded-devel-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415   2238604 Sep 14 11:44 mysql-community-libs-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415   2115892 Sep 14 11:44 mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415  55538708 Sep 14 11:44 mysql-community-minimal-debuginfo-5.7.20-1.el7.x86_64.r

-rw-r--r--. 1 7155 31415 171597916 Sep 14 11:45 mysql-community-server-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415  15256460 Sep 14 11:45 mysql-community-server-minimal-5.7.20-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 118609776 Sep 14 11:45 mysql-community-test-5.7.20-1.el7.x86_64.rpm

[root@marklin mysql]# rpm -qa|grep mariadb

[root@marklin mysql]# rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-common-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5:

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-common-5.7.20-1.e################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-libs-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: N

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-libs-5.7.20-1.el7################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-libs-compat-5.7.2################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-libs-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: N

Preparing...                          ################################# [100%]

package mysql-community-libs-5.7.20-1.el7.x86_64 is already installed

[root@marklin mysql]# rpm -ivh mysql-community-embedded-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-embedded-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-embedded-5.7.20-1################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-embedded-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-embedded-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f

Preparing...                          ################################# [100%]

package mysql-community-embedded-5.7.20-1.el7.x86_64 is already installed

[root@marklin mysql]# rpm -ivh mysql-community-embedded-compat-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-embedded-compat-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-embedded-compat-5################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-devel-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-devel-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: 

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-devel-5.7.20-1.el################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-embedded-devel-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-embedded-devel-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-embedded-devel-5.################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-client-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5:

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-client-5.7.20-1.e################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-server-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5:

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-server-5.7.20-1.e################################# [100%]

[root@marklin mysql]# rpm -ivh mysql-community-test-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-test-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: N

error: Failed dependencies:

perl(JSON) is needed by mysql-community-test-5.7.20-1.el7.x86_64

[root@marklin mysql]# yum install perl-JSON.noarch

Loaded plugins: fastestmirror, langpacks

base                                                                                                   

extras                                                                                                 

updates                                                                                                

Loading mirror speeds from cached hostfile

 * base: mirrors.tuna.tsinghua.edu.cn

 * extras: mirrors.tuna.tsinghua.edu.cn

 * updates: mirrors.tuna.tsinghua.edu.cn

Resolving Dependencies

--> Running transaction check

---> Package perl-JSON.noarch 0:2.59-2.el7 will be installed

--> Finished Dependency Resolution

 

Dependencies Resolved

 

=======================================================================================================

 Package                                                                        Arch                   

=======================================================================================================

Installing:

 perl-JSON                                                                      noarch                 

 

Transaction Summary

=======================================================================================================

Install  1 Package

 

Total download size: 96 k

Installed size: 264 k

Is this ok [y/d/N]: y

Downloading packages:

warning: /var/cache/yum/x86_64/7/base/packages/perl-JSON-2.59-2.el7.noarch.rpm: Header V3 RSA/SHA256 Si

Public key for perl-JSON-2.59-2.el7.noarch.rpm is not installed

perl-JSON-2.59-2.el7.noarch.rpm                                                                        

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

Importing GPG key 0xF4A80EB5:

 Userid     : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"

 Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5

 Package    : centos-release-7-4.1708.el7.centos.x86_64 (@anaconda)

 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

Is this ok [y/N]: y

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Warning: RPMDB altered outside of yum.

** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows:

akonadi-mysql-1.9.2-4.el7.x86_64 has missing requires of mariadb-server

icedtea-web-1.6.2-4.el7.x86_64 has missing requires of java-1.8.0-openjdk

  Installing : perl-JSON-2.59-2.el7.noarch                                                             

  Verifying  : perl-JSON-2.59-2.el7.noarch                                                             

 

Installed:

  perl-JSON.noarch 0:2.59-2.el7                                                                        

 

Complete!

[root@marklin mysql]# rpm -ivh mysql-community-test-5.7.20-1.el7.x86_64.rpm

warning: mysql-community-test-5.7.20-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: N

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-test-5.7.20-1.el7################################# [100%]

[root@marklin mysql]# service mysqld start

Redirecting to /bin/systemctl start mysqld.service

[root@marklin mysql]# 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 2017-12-30 16:59:06 EST; 22s ago

     Docs: man:mysqld(8)

           http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 34484 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_O

  Process: 34404 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

 Main PID: 34487 (mysqld)

   CGroup: /system.slice/mysqld.service

           └─34487 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

 

Dec 30 16:58:56 marklin.com systemd[1]: Starting MySQL Server...

Dec 30 16:59:06 marklin.com systemd[1]: Started MySQL Server.

[root@marklin mysql]# cat /var/log/mysqld.log | grep password

2017-12-30T21:59:00.228444Z 1 [Note] A temporary password is generated for root@localhost: bd;6o,HufyoP

[root@marklin mysql]# mysql -uroot -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.20

 

Copyright (c) 2000, 2017, 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> alter use 'root'@'localhost' identified by '123456';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'use 'root'@'localhost' identified by '123456'' at line 1

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456abc';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> 

mysql> Your password does not satisfy the current policy requirements

    -> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456Abc';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Your password does not satisfy the current policy requirements

ALTER USER 'root'' at line 1

mysql> set global validate_password_policy=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set global validate_password_mixed_case_count=0; 

Query OK, 0 rows affected (0.00 sec)

 

mysql> set global validate_password_number_count=3; 

Query OK, 0 rows affected (0.00 sec)

 

mysql> set global validate_password_special_char_count=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set global validate_password_length=3;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW VARIABLES LIKE 'validate_password%';

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

 

mysql> exit

Bye

[root@marklin mysql]# mysql -uroot -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.20 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2017, 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 '123456' WITH GRANT OPTION;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> exit

Bye

[root@marklin mysql]#