Mysql common basic command summary and default account permissions and modifications _ MySQL

Mysql's common basic command summary and default account permissions and modifications have been simply using the database to store data, and windows is also used bad. many commands cannot be remembered when used. so, change to LINUX! No longer use the GUI to manage databases! I also want to learn more about Mysql and start with permission management! This learning note was born.

Change default password

When Mysql is newly installed, the default root password is blank, which is extremely insecure! We recommend that you modify the settings immediately after installation. Attached modification method:

mysqladmin -u root -p password

Next, enter the new password as prompted!

Command details:

mysqladmin -u root -p[oldpass] password newpass

Note that oldpass is optional. if the default root password is blank, you do not need to enter it. if you need to change the previous password, note that there is no space between the previous password and-p; otherwise, an error is reported, in addition, password and newpass are separated by spaces. This is of course nonsense. it is for beginners!

Log on to the database using basic commands

This is simple:

mysql -u root -p

You will see the following command prompt, indicating that

mysql>

You can enter help to view help information. Remember to add ";" to all commands, which is a syntax.

Command explanation:

mysql [-u username] [-h host] [-p[password]] [dbname]

If you do not Enter the password, the Enter password prompt will appear after you press Enter. If the root password is blank, you do not need to Enter the password. if the Enter password prompt appears, press Enter.

[Host and database names can be specified during logon. This host is the address of the recipient when you remotely log on to another database !]

Add a link to those who forgot the password: How to forget the root password for mysql in windows

View database

First, check the default database. run the following command:

mysql> show databases;

Result:

+--------------------+| Database |+--------------------+| information_schema || mysql|| performance_schema |+--------------------+

Use Database

To use a database such as mysql, run the "use" command:

mysql> use mysql;

The result shows that the database is running correctly if it is changed.

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed

Basic operations on database tables view tables in the database

After entering the database, check the table or the show command:

mysql> show tables;

+---------------------------+| Tables_in_mysql |+---------------------------+| columns_priv|| db|| event || func|| general_log || help_category || help_keyword|| help_relation || help_topic|| host|| ndb_binlog_index|| plugin|| proc|| procs_priv|| proxies_priv|| servers || slow_log|| tables_priv || time_zone || time_zone_leap_second || time_zone_name|| time_zone_transition|| time_zone_transition_type || user|+---------------------------+24 rows in set (0.00 sec)

Now, we have learned some simple database operations. next we will start to create our own database and data tables!

Create a database

Simple command:

create database mydata;

Result:

Query OK, 1 row affected (0.00 sec)

The prompt is successful. Next, run the show table command:

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydata || mysql|| performance_schema |+--------------------+5 rows in set (0.00 sec)

Okay. your database is successfully created. let's see what's in the database first? The answer must be nothing! Let's take a look:

mysql> use mydata;Database changedmysql> show tables;Empty set (0.00 sec)

So, create a simple table (note that we have used the mydada database above, and the table created next is in this database, we can only create tables in the database ):

mysql> create table table_name(name VARCHAR(20), age int);Query OK, 0 rows affected (0.02 sec)mysql> show tables;+------------------+| Tables_in_mydata |+------------------+| table_name |+------------------+1 row in set (0.00 sec)

View the table structure

After successful execution, we can view the table structure (using describe ):

mysql> describe table_name;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name| varchar(20) | YES| | NULL| || age | int(11) | YES| | NULL| |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

Insert data to table

At this time, there is no data in the table, insert into: [Note: this is just a simple demonstration. For more detailed commands, please refer to the official website documentation (3000 + pages )]

mysql> insert into table_name values("The_Third_Wave", 100);Query OK, 1 row affected (0.02 sec)mysql> select * from table_name;+----------------+------+| name | age|+----------------+------+| The_Third_Wave |100 |+----------------+------+1 row in set (0.00 sec)

Update table data

mysql> update table_name set age=188 where name="The_Third_Wave";Query OK, 1 row affected (0.00 sec)Rows matched: 1Changed: 1Warnings: 0mysql> select * from table_name;+----------------+------+| name | age|+----------------+------+| The_Third_Wave |188 |+----------------+------+1 row in set (0.00 sec)mysql>

Import data to a table

[To be supplemented]

Delete)

mysql> delete from table_name;Query OK, 1 row affected (0.00 sec)mysql> select * from table_name;Empty set (0.00 sec)

Drop)

mysql> drop table table_name;Query OK, 0 rows affected (0.00 sec)mysql> show tables;Empty set (0.00 sec)mysql> drop database mydata;Query OK, 0 rows affected (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql|| performance_schema |+--------------------+4 rows in set (0.00 sec)

Permission management

The following is the default user group after installation:

mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select Host,User,Password from user;+-----------------------------+------------------+-------------------------------------------+| Host| User | Password|+-----------------------------+------------------+-------------------------------------------+| localhost | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx || xxxxxxxxxxxxxxxxxxxxxxxxxxx | root | || 127.0.0.1 | root | || ::1 | root | || localhost | debian-sys-maint | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |+-----------------------------+------------------+-------------------------------------------+5 rows in set (0.00 sec)

The password for the root account corresponding to the three hosts is null. [Note: for Mysql of different versions, some passwords are set at the same time, not the same as above .]

It seems that we only set the password of the root account corresponding to Host: localhost. We use mysql> select current_user (); to view the username currently logged on:

mysql> select current_user();+----------------+| current_user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)

Note that user_name @ host_name is the user name, and host_name is the address of the client you choose to connect to the server. The host_name you are using mysql-h host_name refers to the server address (note the difference above ).

That is to say: if you have an msyql database in db.csdn.com, then you have mysql-u root-h db.csdn.com, which has nothing to do with your own machine, your logon account is still root @ yourPCname

This may not be easy to understand. let's take an example and practice creating a user.

Create a new user and specify an IP address to establish a connection

My actual operation is to create a remote access user account in the data on my testing machine (the reason is: Security first on the machine, I don't want to set up a firewall separately, my testing machine does not have a firewall !), The purpose is to provide remote host (my computer) access to the database of the local machine (my testing machine! [This statement is a bit difficult], because the default account cannot be accessed without local login, the following error will be reported when I connect to my testing machine with the default root account:

Mysql-u root-h IP address of my testing machine-pEnter password: ERROR 1045 (28000): Access denied for user 'root' @ 'Here is my local IP address '(using password: YES)

So that we can understand the above words?

Next, let's take a look at user permission settings. for details, refer to permission management for MYSQL database management-Feihong

The command details are as follows:

GRANT command instructions

First let's look at an example, create a super user feihong that only allows local login, and allow permission to be granted to other users, the password is test@feihong.111

Grant all privileges on *. * TO feihong @ 'localhost' identified by 'test @ feihong.111 'with grant option;

GRANT command description:

All privileges indicates ALL permissions. you can also use the permissions mentioned by select, update, and other permissions.

ON is used to specify which databases and tables the permission applies.

*. * Indicates the database name, and * indicates the table name.

TO: Grant permissions TO a user.

Feihong @ 'localhost' indicates the feihong user. @ is followed by a restricted host, which can be an IP address, IP segment, domain name, and %. % indicates any location. Note: Here % some versions do not include the local version. I have previously set % to allow a user to log on anywhere, but cannot log on locally. this is related to the version, if this problem occurs, add a localhost user.

Identified by specifies the user's logon password.

With grant option indicates that the user can GRANT permissions to others. Note: Some users often do not specify the with grant option when creating operation users. as a result, this user cannot use the GRANT command to create users or GRANT permissions to other users.

Note: You can use GRANT to repeatedly add permissions to users. for example, you add a select permission to the user first and then add an insert permission to the user, then the user has both select and insert permissions.

The following operations are performed on my testing machine!

Mysql> grant usage, select on *. * TO public @ 'My local IP address' identified by 'pbpass' with grant option; Query OK, 0 rows affected (0.00 sec)

Run the following command on my testing machine to view the user table:

select Host,User,Password from mysql.user;

After confirming the correct addition, I can log on to the testing machine remotely on the local machine!

The following operations are performed on the local machine!

Again, we tried to remotely connect to my testing machine database and connect to the database using the access account we added to the testing machine!

Mysql-u public-h my testing machine IP address-pEnter password: Welcome to the MySQL monitor. commands end with; or/g. your MySQL connection id is 9 Server version: 5.6.15-enterprise-defined cial-advanced MySQL Enterprise Server-Advanced Edition (commercial) Copyright (c) 2000,201 4, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or'/h' for help. type '/C' to clear the current input statement. mysql>

After successfully logging on to my lab database, the next operation depends on your permissions. for example, if the public account I created only has access permissions, this account cannot

Created and deleted!

Let's check which users of my testing machine are:

Mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | mysql | performance_schema | sakila | test | world | + -------------------- + 7 rows in set (0.00 sec) mysql> select Host, User, Password from mysql. user; + ----------------- + -------- + hosts + | Host | User | Password | + ----------------- + -------- + hosts + | localhost | root | * drivers | 127.0.0.1 | root | * drivers | :: 1 | root | * xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | local IP address | public | * hosts | + --------------- + -------- + rows + 5 rows in set (0.00 sec) mysql>

If you use the pbpass password, you will find something in common. So, security is very important, and database user permissions are very important. This reminds us that special tables such as mysql should not be seen by anyone else!

Security-oriented, So, delete unnecessary users. it will not be too late to add users later!

Delete a user

Note: Do not use DELETE to DELETE a user directly, because the user's permissions are not deleted after the DELETE operation. after a user with the same name is created, the user will inherit the previous permissions. The correct method is to use the drop user command to delete the USER!

Drop user 'public' @ my local IP address; Query OK, 0 rows affected (0.00 sec)

Indicates that the operation is successful. you can view it again.

Update user password

Other users do not need to set a password or delete it. How to set a password?

Method 1: mysqladmin

The following command is used:

mysqladmin -u root -p[oldpass] password newpass

Method 2: use the table update command

Mysql> UPDATE mysql. user SET Password = PASSWORD ('New password') WHERE User = 'root' and Host = '2017. 0.0.1 '; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> FLUSH PRIVILEGES;

Be sure to use the PASSWORD () function! Think about the consequences.

Method 3: set password

Mysql> set password for 'root' @ ': 1' = PASSWORD ('New password'); Query OK, 0 rows affected (0.00 sec) mysql>

Note: flush privileges is used after update;

Command to refresh the permission; otherwise, the original password is used!

This article by @ The_Third_Wave (Blog address: http://blog.csdn.net/zhanh1218) original. Otherwise, it will be updated from time to time. please correct me if any error occurs.

If you find that this Blog post is incomplete, it is the reason for me to prevent crawlers from releasing half of the posts first. please refer to the original author's Blog.

If this blog post is helpful to you, you are not recommended to repost it for a good network environment. we recommend that you add it to your favorites! If you must reprint the content, please include the suffix and the address of this article.