Grant added MYSQL user 2009-04-03 14:40
I installed the version:
Mysql> select version ();
+------------+
| Version () |
+------------+
| 5.0.22-log |
+------------+
1 row in Set (0.05 sec)
Add Users:
Mysql> Grant Select,update,insert,delete on * * to [E- Mail protected] identified by "Jimmy";
Query OK, 0 rows Affected (0.00 sec)
Mysql> select Host,user,password from user;
+-----------+--------+-------------------------------------------+
| Host | user | password |
+-----------+--------+-------------------------------------------+
| localhost | Root | |
| AS3 | Root | |
| AS3 | | |
| localhost | | |
| AS3 | YUANJL | 550185cd02026208 |
| localhost | YUANJL | 550185cd02026208 |
| AS3 | Jimmy | *1e7f320b8f580aadc02e8a70285e46a8cfda3359 |
+-----------+--------+-------------------------------------------+
7 Rows in Set (0.00 sec)
Mysql> quit
Bye
[Email protected] mysql-5.0.22]# mysql-pjimmy-u jimmy-h AS3 MySQL
ERROR 1251:client does not the support authentication protocol requested by server; Consider upgrading MySQL client
[R[email protected] mysql-5.0.22]# mysql-u root-h AS3 MySQL
Welcome to the MySQL Monitor. Commands End With; Or/g.
Your MySQL Connection ID is the Version:5.0.22-log to server
Type ' help; ' or '/h ' for help. Type '/C ' to clear the buffer.
mysql> Update user Set Password=old_password (' Jimmy ') where user= ' Jimmy ' and host= ' AS3 ';
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
Mysql> quit
Bye
[Email protected] mysql-5.0.22]# mysql-pjimmy-u jimmy-h AS3 MySQL
Welcome to the MySQL Monitor. Commands End With; Or/g.
Your MySQL Connection ID is PNS to server Version:5.0.22-log
Type ' help; ' or '/h ' for help. Type '/C ' to clear the buffer.
Mysql>
2. mysql Add user
Personal lessons, when adding MySQL account, must be the user name and host (local and%) are to be quoted, otherwise the command is wrong
Command mode. Notice that each line is followed by A; represents the end of a command statement.
Format: Grant Select on database. * to "username" @ "Login Host" identified by "password";
Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,delete on * * to ' test1 ' @ "%" identified by "ABC";
Add all permission statements:
By example 1: The Execute permission (Select,insert,....) Change to all privileges, which means that you have all permissions, including creating database permissions and deleting the database, and are not limited to working within a single database
GRANT all privileges on * * to ' root ' @ '% ' identified by ' 123456 ';
Example 1 (non-supplemental) added in the user is very dangerous, because your host is%,% that is the host in any location, and local is the word meaning "native", if, someone want to know test1 password, Then he can connect to your MySQL database on any computer on the Internet and can do whatever you like with your data, see Example 2 for a workaround.
According to the example 1 supplemental statement, this statement try not to use on the MySQL server, if your server is in a managed or remote area, if you need to remotely manage the MySQL server, and do not want to log on to the server through HyperTerminal, then you have to use this statement, but to understand one thing, That is, you can use this method to connect to the server, others can, so keep your MySQL password.
Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to "Test2" @localhost identified by "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to "Test2" @localhost identified by "";
Grant Select,insert,update,delete on dez.* to "test2" @ "%" identified by "123456"; ---------------------------Database version type: MYSQL 5.0.x Community version
Database important commands: mysqldump; MySQL
System environment: Windows Xp SP2
Sample database: Demo (internal table can be built arbitrarily)
Database user name: Root (can also be a user name with administrative operation privileges)
Database login Password: dbuserkey
MySQL in the test environment has been successfully set in the environment variable to start MySQL database engine on the command line
A, fully export demo all data and structure (full backup)
In any directory (such as D:/mydb), create a batch file Backup.bat with the following content:
Mysqldump-uroot-pdbuserkey--add-locks--opt demo>d:/mydb/demo.sql;
@pause
B, fully import demo all data and structure (full recovery)
To create a batch file in the same directory as Backup.bat Restore.bat the contents are as follows:
Mysql-uroot-pdbuserkey demo<d:/mydb/demo.sql;
@pause
C, test
Open MySQL Database engine net start MySQL
Running a in batch will see the D:/mydb build Demo.sql
Then execute the DROP database demo command to delete the demo databases
Create a demo database demo;
Recover all data and structure by running B in the process
D, common commands
Log in or connect to the MySQL database service
Mysql-h host Address-u user name-p user password;
Launch MySQL database connection
Exit
Stop the local MySQL database service
Net stop MySQL
Change Password
Mysqladmin-u username-p Old password password new password;
Show Database list
Show Database;
Working with databases
Use DatabaseName;
Show current selection Database
Select Database ();
Open Table
Show TableName;
Show the structure of a data table
Describe TableName;
The current database contains table information:
Show Tables; (Note: There is a last s)
Set up a database
Create DataBase DatabaseName;
Create a table
Use DatabaseName;
Create table tableName (Field settings list);
Deletion of libraries and deletion of tables
Drop Database DatabaseName;
Drop Table TableName;
Empty records in a table
Delete from TableName;
Table name change
Rename Table TableA to TableB;
SQL Structured query statement commands see MySQL doc documentation
MySQL Create delete user
Grant select,insert,update,delete on *[(database)].* to user name @ login host identified by "password"
Command mode. Notice that each line is followed by A; Represents the end of a command statement.
Format: Grant Select on database. * To User name @ login host identified by "password"
= = The following from the network = =
Example 1, add a user test1 password for ABC, so that he can log on any host, and have query, insert, modify, delete permissions on all databases. First, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";
Example 1 added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, the workaround is shown in Example 2.
Example 2, add a user test2 password for ABC, so that he can only login on localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";
This creates a user named: Phplamp with a password of: 1234.
Then log in.
mysql>exit;
Mysql-u phplamp-p
Enter password
Mysql> Login Successful
2. Authorize the user.
Log in to MySQL (with root privileges). I am logged in as root.
Mysql-u root-p
Password
First create a database for the user (phplampdb)
Mysql>create database phplampdb;
Authorizes the Phplamp user to have all the permissions of the Phplamp database.
>grant all privileges in phplampdb.* to [e-mail protected] identified by ' 1234 ';
Refresh System Permissions Table
Mysql>flush privileges;
Mysql> Other operations
/*
If you want to specify partial permissions to a user, you can write:
Mysql>grant select,update on phplampdb.* to [e-mail protected] identified by ' 1234 ';
Refreshes the System permissions table.
Mysql>flush privileges;
*/
3. Delete the user.
Mysql-u root-p
Password
Mysql>delete from user Where user= "phplamp" and host= "localhost";
Mysql>flush privileges;
Delete a user's database
Mysql>drop database phplampdb;
4. Modify the specified user password.
Mysql-u root-p
Password
Mysql>update Mysql.user Set Password=password (' New password ') where user= "Phplamp" and host= "localhost";
Mysql>flush privileges;
5. List all databases
Mysql>show database;
6. Switching the database
Mysql>use ' database name ';
7. List all Tables
Mysql>show tables;
8. Display the data table structure
Mysql>describe table name;
9. Deleting databases and data tables
Mysql>drop database name;
Mysql>drop table Data Sheet name;
10. Display the structure of the table
Describe "Tabalename";
CMD under connect MySQL:
Mysql-uroot-p Enter note The password do not hit after-p
Linux Grant add MySql user
