1. Modify the prompt of mysql database
1.1 Temporarily modify the mysql login prompt, which expires after the session ends
mysql> prompt \u@\h [\d] \r:\m:\s-> PROMPT set to '\u@\h [\d] \r:\m:\s->' root@localhost [(none)] 09:18:10->\q
1.2 Permanent modification of mysql login prompt
In my.cnf configuration file, [mysql] module add the following content (note, not [mysqld]) save, no need to restart MySQL, exit the current session, re-login; if you add in my.cnf, you can use\ to avoid escaping problems.
[root@192168066012_MySQL_5_7_27 ~]# vim /etc/my.cnf [mysql] prompt=\\u@\\h [\d] \\r:\\m:\\s->
2 Use help in mysql
By default, commands in MySQL are case-insensitive;
Help < command > # to see how specific commands are used
root@localhost [(none)] 09:27:28->help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
...ellipsis3 Setting and Modifying MySQL USER Password
3.1 Command Line Setting USER Password Method
# MySQL Single Instance Setting Password
[root@192168066012_MySQL_5_7_27 ~]# mysqladmin -uroot password "boyu123"
# MySQL Multi-instance Setting Password
[root@192168066012_MySQL_5_7_27 ~]# mysqladmin -uroot password 'boyu123' -S /application/mysql/data/3306/mysql.sock
3.2 Command Line Modification of root Password Method <Commonly Used in this Method>
# MySQL Single Instance Modification Password
[root@192168066012_MySQL_5_7_27 ~]# mysqladmin -uroot -pboyu123 password 'boyu1234'
# MySQL multi-instance password modification
[root@192168066012_MySQL_5_7_27 ~]# mysqladmin -uroot -pboyu123 password 'boyu1234' -S /application/mysql/data/3306/mysql.sock
3.3 sql statement modification method < this method is dangerous >
# Modify the root password to boyu123
root@localhost [(none)] 09:43:22->update mysql.user set authentication_string=password('boyu123') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
#Reload to make the configuration take effect immediately
root@localhost [(none)] 09:44:33->flush privileges;
Query OK, 0 rows affected (0.00 sec)Tips:
a. where condition must be specified, otherwise the password will be changed completely, dangerous!!!
b. You must use the password() function to encrypt changes
4 Single Instance Retrieves Lost MySQL USER Password
4.1 First stop MySQL database
Single instance stops MySQL database
[root@192168066012_MySQL_5_7_27 ~]# /etc/init.d/mysqld stop Shutting down MySQL. SUCCESS!
4.2 Start MySQL database with skip-grant-tables, ignoring authorized login validation
The command line enters the following commands
mysqld_safe --skip-grant-tables --user=mysql &
Entering mysql can directly skip validation and enter the database
mysql
update root password
mysqladmin gracefully closes the database
Mysql-uroot-pboyu123 is ready to log in to the database
Tip: At startup, add the -- skip-grant-tables parameter to indicate ignorance of authorization table validation
4.3 Operating Method of Skp-grant-tables Parameters
#Skip Authorization Table Verification
[root@192168066012_MySQL_5_7_27 ~]# mysqld_safe --skip-grant-tables --user=mysql &
[1] 130775
[root@192168066012_MySQL_5_7_27 ~]# 2019-07-31T13:57:05.294289Z mysqld_safe Logging to '/application/mysql/logs/mysql_5_7_27.err'.
2019-07-31T13:57:05.327331Z mysqld_safe Starting mysqld daemon with databases from /application/mysql/data
#Check the database process
[root@192168066012_MySQL_5_7_27 ~]# ps -ef|grep 3306
root 359 99061 0 21:57 pts/1 00:00:00 grep --color=auto 3306
mysql 131036 130775 2 21:57 pts/1 00:00:00 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/application/mysql/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --skip-grant-tables --log-error=/application/mysql/logs/mysql_5_7_27.err --pid-file=/application/mysql/mysqld.pid --socket=/application/mysql/tmp/mysql.sock --port=3306
#Log in to the database without a password
[root@192168066012_MySQL_5_7_27 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
...ellipsis
#Update root password
root@localhost [(none)] 10:03:30->update mysql.user set authentication_string=password('boyu123') where user='root';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 1
#Refresh Authorization Form
root@localhost [(none)] 10:04:28->flush privileges;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)] 10:04:32->\q
Bye
#Graceful Stop Database
[root@192168066012_MySQL_5_7_27 ~]# mysqladmin -uroot -pboyu123 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2019-07-31T14:05:16.946425Z mysqld_safe mysqld from pid file /application/mysql/mysqld.pid ended
[1]+ Done mysqld_safe --skip-grant-tables --user=mysql
#Start the database
[root@192168066012_MySQL_5_7_27 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
#At this point, you can log in to the database with the new password
[root@192168066012_MySQL_5_7_27 ~]# mysql -uroot -pboyu123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
...ellipsis
root@localhost [(none)] 10:05:42->5 Multiple Instances to Retrieve Lost MySQL USER Password
5.1 First stop MySQL database
[root@192168066012_MySQL_5_7_27 ~]# mysqladmin -uroot -p"boyu123" -S /application/mysql/data/3306/mysql.sock shutdown
5.2 Start MySQL database with skip-grant-table, ignoring authorized login validation
[root@192168066012_MySQL_5_7_27 ~]# /application/mysql/bin/mysqld_safe --skip-grant-tables --port=3306 --user=mysql --character_set_server=utf8 --socket=/application/mysql/data/3306/mysql.sock --datadir=/application/mysql/data/3306/data --pid-file=/application/mysql/data/3306/mysql.pid --log-bin=/application/mysql/data/3306/mysql-bin --server-id=1 --log-error=/application/mysql/data/3306/mysql_boyu3306.err &
5.3 Log on to database without password, modify root password and refresh authorization form
[root@192168066012_MySQL_5_7_27 ~]# mysql -S /application/mysql/data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
...ellipsis
mysql> update mysql.user set authentication_string=password('boyu123') where user='root';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye5.4 Stop 3306 database and login database with new password
[root@192168066012_MySQL_5_7_27 ~]# mysqladmin -uroot -p"boyu123" -S /application/mysql/data/3306/mysql.sock shutdown mysqladmin: [Warning] Using a password on the command line interface can be insecure. 2019-08-08T03:37:09.248027Z mysqld_safe mysqld from pid file /application/mysql/data/3306/mysql.pid ended [1]+ Done /application/mysql/bin/mysqld_safe --skip-grant-tables --port=3306 --user=mysql --character_set_server=utf8 --socket=/application/mysql/data/3306/mysql.sock --datadir=/application/mysql/data/3306/data --pid-file=/application/mysql/data/3306/mysql.pid --log-bin=/application/mysql/data/3306/mysql-bin --server-id=1 --log-error=/application/mysql/data/3306/mysql_boyu3306.err #Start 3306 database [root@192168066012_MySQL_5_7_27 ~]# /application/mysql/bin/mysqld --port=3306 --user=mysql --character_set_server=utf8 --socket=/application/mysql/data/3306/mysql.sock --datadir=/application/mysql/data/3306/data --pid-file=/application/mysql/data/3306/mysql.pid --log-bin=/application/mysql/data/3306/mysql-bin --server-id=1 --log-error=/application/mysql/data/3306/mysql_boyu3306.err & #New password login database [root@192168066012_MySQL_5_7_27 ~]# mysql -uroot -pboyu123 -S /application/mysql/data/3306/mysql.sock ...ellipsis mysql>
6 SQL Structured Query Language
6.1 What is SQL?
SQL (Structured Query Language) is a structured query language (database query and programming language), which is a language method to define and operate data in relational database.
6.2 Common Classification of SQL Statements
DDL - Data Definition Language (CREATE,ALTER,DROP)
Data Definition Language. In a database, creating new tables or deleting tables, indexing tables, etc. are also part of the action query.
b. DML - Data Operating Language (SELECT,INSERT,DELETE,UPDATE)
Data Manipulation Language, also known as Action Query Language, modifies and deletes rows (data) in tables in databases.
c. DCL - Data Control Language (GRANT,REVOKE,COMMIT,ROLLBACK)
Data Control Language, in a database, is licensed to determine the access of a single user and group of users to database objects.
7 Create a database
Note that library names do not start with numbers
Command grammar: create database < database name >
- Default database configuration, equivalent to creating a Latin Character Set database
root@localhost [(none)] 10:20:09-> create database boyu;
- Creating gbk Character Set Database
root@localhost [(none)] 10:20:09-> create database boyu_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
- Creating utf8 Character Set Database
root@localhost [(none)] 10:20:09-> create database boyu_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- View the character set of the created database
root@localhost [(none)] 10:20:09-> show create database boyu_utf8\G +-----------+--------------------------------------------------------------------+ | Database | Create Database | +-----------+--------------------------------------------------------------------+ | boyu_utf8 | CREATE DATABASE "boyu_utf8" /*!40100 DEFAULT CHARACTER SET utf8 */ | +-----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec)
Tips:
a. Character set inconsistency is the main culprit of Chinese content disorder in database
b. If a specific character set is specified when compiling and installing, then it is not necessary to specify a character set when creating a database for the corresponding character set in the future.
c. How to create a database in an enterprise?
Determine the character set according to the developed program (usually UTF8)
Character sets are specified at compilation time and then created by default; for example:
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
create database boyu;
When compiling, it does not specify a character set, or specify a different character set from the program. How to solve this problem?
When creating a database, specify the character set.
8 Display database
Command grammar: show databases;
- Display all current databases
root@localhost [(none)] 10:31:00->show databases; +--------------------+ | Database | +--------------------+ | information_schema | | boyu | | boyu_gbk | | boyu_utf8 | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec)
- Display boyu database
root@localhost [(none)] 10:31:05->show databases like 'boyu'; +-----------------+ | Database (boyu) | +-----------------+ | boyu | +-----------------+ 1 row in set (0.00 sec)
- Displays multiple databases starting with boyu, with% as wildcard
root@localhost [(none)] 10:31:36->show databases like 'boyu%'; +------------------+ | Database (boyu%) | +------------------+ | boyu | | boyu_gbk | | boyu_utf8 | +------------------+ 3 rows in set (0.00 sec)
9 Delete database
Command grammar: drop database < database name >
- Delete the database named boyu_utf8
root@localhost [(none)] 10:31:43->drop database boyu_utf8; Query OK, 0 rows affected (0.00 sec)
9.1 Learning Subconscious View Help
root@localhost [(none)] 10:39:10->help drop database;
Name: 'DROP DATABASE'
Description:
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
DROP DATABASE drops all tables in the database and deletes the
database. Be very careful with this statement! To use DROP DATABASE,
you need the DROP privilege on the database. DROP SCHEMA is a synonym
for DROP DATABASE.10 Connect to the database
Command grammar: use < database name >
- Connect/enter boyu database
root@localhost [(none)] 10:45:04->use boyu; Database changed
- View the current database
root@localhost [boyu] 10:45:07->select database(); +------------+ | database() | +------------+ | boyu | +------------+ 1 row in set (0.00 sec)
- View the current database version
root@localhost [boyu] 10:45:24->select version(); +------------+ | version() | +------------+ | 5.7.27-log | +------------+ 1 row in set (0.00 sec)
- View System/Database Current Time
root@localhost [boyu] 10:45:33->select now(); +---------------------+ | now() | +---------------------+ | 2019-07-31 22:45:53 | +---------------------+ 1 row in set (0.00 sec)
11 Create MySQL Users and Give User Privileges
11.1 Use help grant to view command help
By looking at the help of the grant command, you can easily find examples of creating users and authorizing them.
root@localhost [boyu] 10:45:53->help grant; ...ellipsis CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'; ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
11.2 Operations and maintenance personnel often use grant to create users and authorize them at the same time. For example:
GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
11.3 The grant command help provides a way to create users with the create command first, and then grant authorization, i.e. create users and authorize privileges separately, for example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; The above two commands correspond to the following one: GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
11.4 Create users and authorize them by grant command
grant Grammar: grant all privileges on dbname.* to 'username'@'localhost' identified by 'mypass';
| grant | all privileges | on dbname.* | to username@localhost | identified by 'mypass' |
|---|---|---|---|---|
| Authorization Order | Corresponding permissions | Goals: libraries and tables | User name and client host | User password |
Description: Authorize the user username to manage all the rights of dbname database on the localhost host host, password is passwd. Username, dbname, passwd can be modified according to the business situation.
- Create a Boyu user with a password of boyu123 and authorize access to the Boyu database
root@localhost [boyu] 11:02:41->grant all privileges on boyu.* to 'boyu'@'localhost' identified by 'boyu123'; Query OK, 0 rows affected, 1 warning (0.00 sec)
- Refresh permission table
root@localhost [boyu] 11:03:13->flush privileges; Query OK, 0 rows affected (0.00 sec)
- Query creation status
root@localhost [boyu] 11:06:03->select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | boyu | localhost | | root | localhost | +------+-----------+ 2 rows in set (0.00 sec)
- View permissions that boyu has
root@localhost [boyu] 11:10:18->show grants for 'boyu'@'localhost'; +--------------------------------------------------------+ | Grants for boyu@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'boyu'@'localhost' | | GRANT ALL PRIVILEGES ON "boyu".* TO 'boyu'@'localhost' | +--------------------------------------------------------+ 2 rows in set (0.00 sec)
11.5 Authorized LAN Host Remote Connection Database
According to the grant command grammar, we know that the oldboy@localhost location is the host authorized to access the database. Localhost can be replaced by domain name, IP segment or IP address.
a. Percentage matching method
root@localhost [boyu] 11:10:41->grant all on boyu.* to boyu@'192.168.66.%' identified by 'boyu123'; Query OK, 0 rows affected, 1 warning (0.01 sec) root@localhost [boyu] 11:18:50->flush privileges; Query OK, 0 rows affected (0.00 sec)
b. Subnet Mask Configuration Method
root@localhost [boyu] 11:18:55->grant all on boyu.* to boyu1@'192.168.66.0/255.255.255.0' identified by 'boyu123'; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [boyu] 11:19:34->flush privileges; Query OK, 0 rows affected (0.00 sec)
c. Authorized Single IP Remote Connection
root@localhost [boyu] 11:19:37->grant all on boyu.* to boyu2@'192.168.66.11' identified by 'boyu123'; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [boyu] 11:19:44->flush privileges; Query OK, 0 rows affected (0.00 sec) root@localhost [boyu] 11:19:46->select user,host from mysql.user; +-------+----------------------------+ | user | host | +-------+----------------------------+ | boyu | 192.168.66.% | | boyu1 | 192.168.66.0/255.255.255.0 | | boyu2 | 192.168.66.11 | | boyu | localhost | | root | localhost | +-------+----------------------------+ 5 rows in set (0.00 sec)
12 Delete MySQL System Redundant Accounts
- drop user "user"@ "host domain", note that quotation marks can be single or double quotation marks, but not without quotation marks
root@localhost [boyu] 11:20:02->drop user 'boyu1'@'192.168.66.0/255.255.255.0'; Query OK, 0 rows affected (0.01 sec) root@localhost [boyu] 12:47:53->delete from mysql.user where user='boyu2' and host='192.168.66.11'; Query OK, 1 row affected (0.00 sec) root@localhost [boyu] 01:09:50->select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | boyu | localhost | | root | localhost | +------+-----------+ 2 rows in set (0.00 sec)
- Note: If drop cannot be deleted (usually special characters or capitals), it can be deleted in the following way (BO Y&U0@07 user, BOYU host for example)
root@localhost [boyu] 01:27:43->select user,host from mysql.user; +------------+-----------+ | user | host | +------------+-----------+ | BO#Y&U0@07 | boyu | | boyu | localhost | | root | localhost | +------------+-----------+ 3 rows in set (0.00 sec) root@localhost [boyu] 01:30:05->delete from mysql.user where user='BO#Y&U0@07' and host='boyu'; Query OK, 1 row affected (0.00 sec) root@localhost [boyu] 01:30:55->select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | boyu | localhost | | root | localhost | +------+-----------+ 2 rows in set (0.00 sec) root@localhost [boyu] 01:31:01->flush privileges; Query OK, 0 rows affected (0.00 sec)
13 What permissions are included in ALL PRIVILEGES?
| SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | INDEX | ALTER | CREATE TEMPORARY TABLES |
|---|---|---|---|---|---|---|---|---|
| query | insert | To update | delete | Create libraries and tables | Delete libraries and tables | Indexes | modify | Create temporary tables |
| LOCK TABLES | EXECUTE | CREATE VIEW | SHOW VIEW | CREATE ROUTINE | ALTER ROUTINE | EVENT | TRIGGER | REFERENCES |
|---|---|---|---|---|---|---|---|---|
| Lock table | implement | Create Views | Display View | Create stored procedures | Modifying stored procedures | Event | trigger | foreign key |
Tip: When authorizing, users can be authorized to have the smallest privileges to meet business needs, rather than simply authorizing "ALL PRIVILEGES"
- Authorize boyu1 users to have "select,insert,update,delete,create,drop" permissions to execute Boyu Libraries
root@localhost [boyu] 01:59:22->CREATE USER 'boyu1'@'localhost' IDENTIFIED BY 'boyu123'; Query OK, 0 rows affected (0.00 sec) root@localhost [boyu] 02:00:20->grant select,insert,update,delete,create,drop ON `boyu`.* TO 'boyu1'@'localhost'; Query OK, 0 rows affected (0.00 sec) root@localhost [boyu] 02:00:28->show grants for boyu1@localhost; +---------------------------------------------------------------------------------------+ | Grants for boyu1@localhost | +---------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'boyu1'@'localhost' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON "boyu".* TO 'boyu1'@'localhost' | +---------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
- Create a boyu2 user and grant "select,insert,update,delete,create,drop" permissions to execute the Boyu Library
root@localhost [boyu] 01:58:31->grant select,insert,update,delete,create,drop on boyu.* to 'boyu2'@'localhost' identified by 'boyu123'; Query OK, 0 rows affected, 1 warning (0.01 sec) root@localhost [boyu] 01:59:07->show grants for boyu2@localhost; +---------------------------------------------------------------------------------------+ | Grants for boyu2@localhost | +---------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'boyu2'@'localhost' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON "boyu".* TO 'boyu2'@'localhost' | +---------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
How to Authorize User Rights in Enterprise Production Environment
14.1 Blog, CMS and other products authorization
To minimize the authorization of web connection users, many open source software are installed on the web interface. Therefore, it is normal to grant select,insert,update,delete privileges, such as discuz,bbs and other open source software, and also to grant create,drop more dangerous privileges.
root@localhost [boyu] 02:00:39-> grant select,insert,update,delete,create,drop ON blog.* to 'blog'@'192.168.66.%' identified by 'boyu123';
14.2 After generating database tables, you need to reclaim create and drop permissions
root@localhost [boyu] 02:00:39-> REVOKE create ON blog.* FROM 'blog'@'192.168.66.%'; root@localhost [boyu] 02:00:39-> REVOKE drop ON blog.* FROM 'blog'@'192.168.66.%';