Getting started with mysql 01
- What is a database?
- Classification of database
- The construction of mysql database
- mysql introduction
- Installation software
- Initialization
- View initial password
- Login with initial password & & modify initial password
- Modify password policy
- Related parameters
- Basic operation of mysql
1, Database terminology
- DB(DataBase)
- Database: a collection of data organized according to a data model and stored in storage
- DBMS(DataBase Management System)
- Database management system: service software used to operate and manage database
- DBS(Database System)
- Database system: computer system with database and database management system
2, Classification of database
ps:mariadb is almost the same as mysql for beginners
3, mysql installation and initialization
-
Download the data package on the official website: the new version of centos/redhat does not come with mysql data package
-
The function of data package (part) complete data package mysql-5.7.17.tar
-
Install mysql
[root@mysql01 ~]# tar -xvf mysql-5.7.17.tar mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm mysql-community-libs-5.7.17-1.el7.x86_64.rpm mysql-community-client-5.7.17-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm mysql-community-common-5.7.17-1.el7.x86_64.rpm mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm mysql-community-devel-5.7.17-1.el7.x86_64.rpm mysql-community-server-5.7.17-1.el7.x86_64.rpm mysql-community-embedded-5.7.17-1.el7.x86_64.rpm mysql-community-test-5.7.17-1.el7.x86_64.rpm [root@mysql01 ~]# yum install -y mysql-community-*.rpm #Install all packages in this directory [root@mysql01 ~]# systemctl start mysqld #Startup service [root@mysql01 ~]# systemctl enable mysqld #Boot from boot [root@mysql01 ~]# ss -ant | grep 3306 LISTEN 0 80 :::3306 :::* #Service turned on
-
-
View the initial password, log in to modify the initial password
[root@mysql01 ~]# grep -i 'password' /var/log/mysqld.log 2020-02-12T02:19:48.981591Z 1 [Note] A temporary password is generated for root@localhost: ;J6_Mlhl7)e+ #';J6_Mlhl7)e+' Is the initial password [root@mysql01 ~]# mysql -uroot -p';J6_Mlhl7)e+' mysql> #Enter database mysql> alter user root@"localhost" identified by "DZ@znb123"; #Comply with password complexity principle Query OK, 0 rows affected (0.00 sec) mysql>
-
Modify password policy
There are three password policies
/*View policy content*/ mysql> show variables like "%password%" ; +---------------------------------------+--------+ | Variable_name | Value | +---------------------------------------+--------+ | default_password_lifetime | 0 | | disconnect_on_expired_password | ON | | log_builtin_as_identified_by_password | OFF | | mysql_native_password_proxy_users | OFF | | old_passwords | 0 | | report_password | | | sha256_password_proxy_users | OFF | | validate_password_check_user_name | OFF | | validate_password_dictionary_file | |/*Dictionary file path used by plug-in to verify password strength*/ | validate_password_length | 8 |/*Password length limit*/ | validate_password_mixed_case_count | 1 |/*Password must contain at least lowercase and uppercase letters*/ | validate_password_number_count | 1 |/*Number of digits a password must contain at least*/ | validate_password_policy | MEDIUM |/*Level of password policy*/ | validate_password_special_char_count | 1 |/*Password must contain at least special characters*/ +---------------------------------------+--------+ 14 rows in set (0.00 sec) /*Modify policy content*/ mysql> set global validate_password_length=6; /*Change password length*/ Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_policy=0; /*Modify policy level*/ Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%password%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | default_password_lifetime | 0 | | disconnect_on_expired_password | ON | | log_builtin_as_identified_by_password | OFF | | mysql_native_password_proxy_users | OFF | | old_passwords | 0 | | report_password | | | sha256_password_proxy_users | OFF | | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 6 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | LOW | | validate_password_special_char_count | 1 | +---------------------------------------+-------+ 14 rows in set (0.00 sec) /*The password can be set to any password greater than 6 digits*/
ps: all the above configurations will fail after restarting
#Permanent configuration(Be careful of wrong parameter name) vim /etc/my.cnf [mysqld] validate_password_policy=0 validate_password_length=6
4, Operation of database (core sql statement)
-
sql function Core command Data query Select (find) Data definition Create, drop, alter Data manipulation Insert, update, delete Data control Grant, revoke -
Database connection
Command format: mysql -h server address: -u user name: -p password: [database name] / / -h can be omitted for this machine [root@mysql02 ~]# mysql -hlocalhost -uroot -p Enter password:
-
Change Password
mysql> alter user user@"Database address" identified by "Password"; /*Change password format*/
-
Find library, switch library, create library, delete Library
/*Show all libraries*/ mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) /*Switch to Library Directory*/ 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 /*Create Library*/ mysql> create database test01; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test01 | +--------------------+ 5 rows in set (0.00 sec) /*Delete Library*/ mysql> drop database test01; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
-
Lookup table name, query table record, create table, view table structure, insert table record, modify table record, delete table
/*Lookup table name (can only operate in the Library)*/ mysql> use sys; mysql> show tables; +-----------------------------------------------+ | Tables_in_sys | +-----------------------------------------------+ | host_summary | | host_summary_by_file_io | | host_summary_by_file_io_type | | host_summary_by_stages | | host_summary_by_statement_latency | | host_summary_by_statement_type | | ... ... ... ... ... .... | | x$waits_by_host_by_latency | | x$waits_by_user_by_latency | | x$waits_global_by_latency | +-----------------------------------------------+ 101 rows in set (0.01 sec) /*Lookup table record (only the simplest)*/ mysql> select * from sys.host_summary; //Query all slave library names.Table name; /*Create table*/ mysql> create database test01; Query OK, 1 row affected (0.00 sec) mysql> use test01; Database changed mysql> create table > player(name varchar(5),sex char(1),QQ varchar(12)) DEFAULT charset=utf8; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +------------------+ | Tables_in_test01 | +------------------+ | player | +------------------+ 1 row in set (0.00 sec) /*View table structure*/ mysql> desc player; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(5) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | QQ | varchar(12) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) /*Insert table record*/ mysql> insert into player values ("Luban seven","male","174451542"); Query OK, 1 row affected (0.00 sec) mysql> select * from player; +--------------+------+-----------+ | name | sex | QQ | +--------------+------+-----------+ | Luban seven | male | 174451542 | +--------------+------+-----------+ 1 row in set (0.00 sec) /*Modification table record*/ mysql> update player set sex="female" where name="Luban seven"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from player; +--------------+------+-----------+ | name | sex | QQ | +--------------+------+-----------+ | Luban seven | female | 174451542 | +--------------+------+-----------+ 1 row in set (0.00 sec) /*Delete table*/ mysql> drop table player ; Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec)
Published 12 original articles, won praise 2, visited 191