Linux server-use mysql
Use mysql
1. Log in, you can log in with or without a password. Command format "mysql-u username-p password"
[root@localhost src] # mysql-u root-p / / Login with password
Enter password:
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 3
[root@localhost src] # mysql-u root / / Login without password
2. Exit and command "quit"
[root@localhost bin] # quit
3. Create a database and use the command "create database database name;". Note that this command is followed by a semicolon.
Mysql > create database test1
Query OK, 1 row affected (0.00 sec)
4. View the database and command "show databases;"
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | mysql |
| | test |
| | test1 |
+-+
4 rows in set (0.00 sec)
5. Delete the database and command "drop database database name".
Mysql > drop database test1
Query OK, 0 rows affected (0.01 sec)
6. Set permissions
Mysql allows specific rights to be given to a particular user and can be specified for use on a machine. The permissions of Mysql are as follows
Authority
Database
Table
Column
Description
All privileges
√
All rights
Alter
√
√
Add or subtract, delete, modify columns
Create
√
√
Create databases, tables
Delete
√
√
Delete Row
Drop
√
√
Delete tables, databases
File
√
Operation file
Index
√
√
Indexes
Insert
√
√
√
insert
Process
√
View threads, connections
Reference
√
Create a foreign key
Reload
√
Reload, with this permission to refresh the table
Select
√
√
√
Choice
Shutdown
√
Close
Update
√
√
√
Update
Usage
√
No permission, can only connect
1) authorized user rights, command format "grant permissions on database file to user name @ ip identified by 'password';" When using grant, it takes so long to create a user if the user does not exist.
/ / authorize david to insert the function locally. Password 123456. It can only be operated on test01.
Mysql > grant insert on test01.* to david@localhost identified by '123456'
Query OK, 0 rows affected (0.00 sec)
Mysql >
/ / give david all permissions, can operate on all hosts, and can operate any database
Mysql > grant all privileges on *. * to david@'%' identified by '123456'
Query OK, 0 rows affected (0.00 sec)
Mysql >
2) View all authorizations in the current database, and command "select host,user from mysql.user"
Mysql > select host,user from mysql.user
+-+ +
| | host | user |
+-+ +
| |% | david |
| | 127.0.0.1 | root |
| | localhost |
| | localhost | david |
| | localhost | root |
| | localhost.localdomain |
| | localhost.localdomain | root |
+-+ +
7 rows in set (0.00 sec)
Mysql >
3) View the rights of the currently logged-in user, and command "show grants"
Mysql > show grants
+- -+
| | Grants for root@localhost |
+- -+
| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' IDENTIFIED BY PASSWORD' * 1256939B1977AFF6C3D114C5594EE354EF363A8B' WITH GRANT OPTION |
+- -+
1 row in set (0.00 sec)
Mysql >
4) check the permissions of a user on a certain machine and command "show grants for user@ip"
Mysql > show grants for david@localhost
+-+
| | Grants for david@localhost |
+-+
| | GRANT USAGE ON *. * TO 'david'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| | GRANT INSERT ON `test01`. * TO 'david'@'localhost' |
+-+
2 rows in set (0.00 sec)
Mysql >
5) remove the user's permissions, and command "revoke permissions on database file from user@ip"
Mysql > revoke all privileges on *. * from david@'%'
Query OK, 0 rows affected (0.00 sec)
Check mysql > show grants for david@localhost; / / after deletion
+-+
| | Grants for david@localhost |
+-+
| | GRANT USAGE ON *. * TO 'david'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-+
2 rows in set (0.00 sec)
Mysql >
6) delete the user and command "delete from user where user='username'"
Mysql > use mysql; / / call this command first
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
Mysql > delete from user where user='david'; / / Delete user
Query OK, 2 rows affected (0.00 sec)
Mysql > select host,user from mysql.user; / / View users
+-+ +
| | host | user |
+-+ +
| | 127.0.0.1 | root |
| | localhost |
| | localhost | root |
| | localhost.localdomain |
| | localhost.localdomain | root |
+-+ +
5 rows in set (0.00 sec)
Mysql >
A Linux learning platform has been made, and a prototype has come out at present, which you can refer to and use.
Link: https://pan.baidu.com/s/1GOLVU2CbpBNGtunztVpaCQ password: n7bk
