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
Bye
[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';
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
Bye
[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';