A tip on managing MySql in MYSQL command line mode

  • 2020-05-06 11:45:48
  • OfStack

MySql database is the first choice for small and medium website backstage database, because it is free of charge for non-commercial applications. Web developers can build a "Linux + + + MySql PHP Apache" platform, this is one of the most economical and efficient platform. In development, with MySql MySql own documentation is a good reference for beginners. This is my little experience in using MySql.
The current average user development environment is Windows or Linux more, users can to http: / / www codepub. com/software/index html download version to install, in windows MySql exists in the form of service, before use should ensure that this service has been started, don't start available net   start   mysql start command. And startup in Linux available "/ etc rc. d/init. d/mysqld   start" command, pay attention to the starter should have administrator privileges.
The newly installed MySql contains an root account with an empty password and an anonymous account, which is a big security risk. For some important applications, we should improve the security as much as possible. Here, we should delete the anonymous account and set the password for   root account use   mysql;
delete   from   User   where   User="";
update   User   set   Password=PASSWORD('newpassword')   where   User='root';
If you want to restrict the login terminal used by the user, you can update the Host field of the corresponding user in the User table. The database service should be restarted after the above changes, and the following similar command can be used for login:
mysql   -uroot   -p;
mysql   -uroot   -pnewpassword;
mysql   mydb   -uroot   -p;
mysql   mydb   -uroot   -pnewpassword;
The above command parameters are part of the common parameters, details can refer to the documentation. mydb here is the name of the database to be logged into.
In the development and practical application, users should not only use root users to connect to the database. Although it is convenient to use root users for testing, it will bring significant security risks to the system and is not conducive to the improvement of management technology. We give the most appropriate database permissions to the user used in an application. For example, a user who only inserts data should not be given permission to delete data. The user management of MySql is realized through User table. There are two common methods to add new users. One is to insert corresponding data rows in User table and set corresponding permissions. The second is to create a user with certain permissions through the GRANT command. The common usage of GRANT is as follows:
grant   all   on   mydb.*   to   NewUserName@HostName   identified   by   "password"   ;
grant   usage   on   *.*   to   NewUserName@HostName   identified   by   "password";
grant   select,insert,update   on   mydb.*   to   NewUserName@HostName   identified   by   "password";
grant   update,delete   on   mydb.TestTable   to   NewUserName@HostName   identified   by   "password";
To give this user the ability to manage permissions on the corresponding object, add the WITH   GRANT   OPTION option after GRANT. For users added with the User table, the Password field is encrypted with the PASSWORD   function to prevent miscreers from reading the password. For those users who are no longer in use, they should be cleared, and users whose permissions have crossed the boundary should timely recover their permissions, which can be done by updating the corresponding fields in the User table, or by using the REVOKE operation.
Below is an explanation of the commonly used permissions I obtained from other sources (www.cn-java.com) :
Global management permissions:  
FILE:   reads and writes files on the MySQL server.  
PROCESS:   displays or kills service threads belonging to other users.  
RELOAD:   overloads access control tables, flushes logs, etc.  
SHUTDOWN:   shuts down MySQL service.
Database/data table/data column permissions:  
Alter:   modifies existing data tables (e.g., add/drop columns) and indexes.  
Create:   creates a new database or data table.  
Delete:   deletes records from tables.  
Drop:   deletes a table or database.  
INDEX:   creates or deletes indexes.  
Insert:   adds records to the table.  
Select:   displays/searches the record of the table.  
Update:   modifies existing records in the table.
Special permission:  
ALL:   is allowed to do anything (like root).  
USAGE:   is only allowed to log in -- nothing else.  
Finally, I give a demonstration of MySql operation under RedHat 9.0:
Select database root user login
[weiwen@weiwenlinux]$mysql   -uroot   -p
Enter   password:MyPassword
mysql > create   database   mydb;
Query   OK,   1   row   affected   (0.02   sec)
mysql > use   mydb;
Database   changed
mysql > create   table   TestTable(Id   int   aut_increment   primary   key,
UserName   varchar(16)   not   null,
Address   varchar(255));
Query   OK,   0   rows   affected   (0.02   sec)
mysql > grant   all   on   mydb.*   to   test@localhost   identified   by   "test";
Query   OK,   0   rows   affected   (0.01   sec)
mysql > quit
[weiwen@weiwenlinux]$mysql   mydb   -utest   -ptest

Es203en.sql is an SQL script edited with vi, the content of which is
Insert   into   TestTable(UserName,Address)values('Tom','shanghai');
Insert   into   TestTable(UserName,Address)values('John','beijing');
select   *   from   TestTable;
Running the edited SQL script can be done using source   filename   or  .\   filename.
The above is just a simple exercise for the novice, to become a good database player, when the tireless pursuit of knowledge, constantly thinking, trying, and thinking.

MySql common command summary    
These two days to set up a website, and use MySql, but the command is not one of them, so, take this opportunity to organize these, right as notes, so that you can consult later!  
1: use SHOW statement to find out what database currently exists on the server:    
    mysql >   SHOW   DATABASES;    
Create a database MYSQLDATA  
    mysql >   Create   DATABASE   MYSQLDATA;    
3: select the database you created    
    mysql >   USE   MYSQLDATA;   (press enter to see Database   changed  )  
4: see what table    
exists in the current database     mysql >   SHOW   TABLES;  
5: create a database table  
    mysql >   Create   TABLE   MYTABLE   (name   VARCHAR(20),   sex   CHAR(1));  
6: display table structure:    
    mysql >   DESCRIBE   MYTABLE;    
7: add records    
to the table     mysql >   insert   into   MYTABLE   values   ("hyq","M");    
8: load data into database tables with text (e.g. D:/ mysql.txt)  
    mysql >   LOAD   DATA   LOCAL   INFILE   "D:/mysql.txt"   INTO   TABLE   MYTABLE;  
9: import.sql file commands (e.g. D:/ mysql.sql)  
    mysql > use   database;  
    mysql > source   d:/mysql.sql;    
10: delete table  
    mysql > drop   TABLE   MYTABLE;    
11: clear  
    mysql > delete   from   MYTABLE;  
12: update the data  
in the table     mysql > update   MYTABLE   set   sex="f"   where   name='hyq';  

Related articles: