Shulou(Shulou.com)06/01 Report--

Basic operation

Show databases

Use library name

Show tables

Create table table name (list of field settings)

Describe table name

Create database library name

Drop database library name

Drop table table name

Delete from table name

Select * from table name

Change the new password

Method one (which I often use)

Enter: mysql-u username-p password at the terminal

Use mysql

Update user set password=PASSWORD ('new password') where user=' username'

Flush privileges; # Update permissions

Quit; # exit

Method 2:

Use the SET PASSWORD command

Mysql-u root

Mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' newpass')

Method 3:

Use mysqladmin

Mysqladmin-u root password "newpass"

If root has already set a password, use the following methods

Mysqladmin-u root password oldpass "newpass"

Method 4:

You can do this when you lose your root password

Mysqld_safe-skip-grant-tables&

Mysql-u root mysql

Mysql > UPDATE user SET password=PASSWORD ("new password") WHERE user='root'

Mysql > FLUSH PRIVILEGES

Authority

Create a user and authorize it

Format: GRANT permission ON library. Table TO 'username' @ 'specify IP' identified by' password'

GRANT ALL PRIVILEGES ON *. * TO 'daxiong1'@'%' identified by' daxiong1'

Flush privileges; [give effect to the operation authorized above]

GRANT ALL PRIVILEGES ON *. * TO 'daxiong2'@'192.168.8.100' identified by' daxiong2'

Flush privileges; [give effect to the operation authorized above]

Authentication: use the Navicat Lite for MySQL tool in windows and use the above 2 users to log in to our Mysql server!

GRANT select ON *. * TO 'daxiong3'@'%' identified by' daxiong3'

Flush privileges; [give effect to the operation authorized above]

Show grants for user; [view the permissions of the specified user]

Revoke all privileges on *. * from 'user' @'%'; [take back all permissions of a user]

When the user permission is USAGE, this permission is minimum, he can only log in!

[almighty change password]

Update mysql.user set password=password ('new password') where user=' username'

[let the permission take effect]

Flush privileges

Show full processlist; [see which users are logged in]

Kill specifies the user's id [forcibly exits the specified user]

Second, add new users (learn from articles on the Internet)

Format: grant permission on database. * to user name @ login host identified by "password"

View the user's permissions show grants for root

Revoke permissions on database. * from username

The following information must be given when setting permissions

1, permissions to be granted

2, the database or table to which access is granted

3, user name

Grant and revoke can control access at several levels

1, the entire server, using grant ALL and revoke ALL

2, the whole database, using on database.*

3, feature table, using on database.table

4, specific column

5, specific stored procedures

The meaning of the value of host column in user Table

% match all hosts

Localhost localhost will not be resolved to an IP address, but will be connected directly through UNIXsocket

127.0.0.1 will connect through the TCP/IP protocol and can only be accessed locally

:: 1:: 1 is compatible with ipv6, representing 127.0.0.1 of the same as ipv4

MySQL grant permissions, which can be used at multiple levels.

1. Grant acts on the entire MySQL server:

Grant select on *. * to dba@localhost;-- dba can query tables in all databases in MySQL.

Grant all on *. * to dba@localhost;-- dba can manage all databases in MySQL

2. Grant acts on a single database:

Grant select on testdb.* to dba@localhost;-- dba can query tables in testdb.

3. Grant acts on a single data table:

Grant select, insert, update, delete on testdb.orders to dba@localhost

4. Grant acts on the columns in the table:

Grant select (id, se, rank) on testdb.apache_log to dba@localhost

5. Grant acts on stored procedures and functions:

Grant execute on procedure testdb.pr_add to 'dba'@'localhost'

Grant execute on function testdb.fn_add to 'dba'@'localhost'

Grant the permissions of a normal DBA to manage a MySQL database.

Grant all privileges on testdb to 'dba'@'localhost'

The keyword "privileges" can be omitted.

Grant Advanced DBA manages permissions for all databases in MySQL.

Grant all on *. * to 'dba'@'localhost'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.