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

MySQL basic operation command

1. View the MySQL process

Ps-ef | grep mysql | grep-v grep

2. View the MySQL port

Ss-lnt | grep 3306

3. The startup of MySQL

Mysql-uroot-p

/ etc/init.d/mysql

Startup method of MySQL during initialization

Mysqld_safe-user=mysql &

4. Close the database

(1) smoothly shut down the database

Mysqladmin-uroot-pcentos shutdown

/ etc/init.d/mysqld stop

Kill-USR2 cat path/pid

(2) forcibly close the database

Killall mysqld

Pkill mysqld

Killall-9 mysqld

Note: try not to savagely kill the database, the production of high concurrency environment may cause data loss.

5. Start and shut down multiple instances

When multiple instances are started, we write scripts. When starting, we use mysqld_safe to specify the configuration file to start, and when we shut down, we use mysqadmin.

(1) / data/3306/mysql start

(2) / data/3307/mysql stop

6. Log in to mysql

(1) single instance login

Mysq # database is not configured with secret login method

Mysql-uroot # database is not configured with secret login method

Mysql-uroot-p # standard dba command login command

Mysql-uroot-pcentos # is not generally used in non-scripts. The password will be disclosed in plain text, which can be solved by masking the history function.

Force linux not to record sensitive history commands

# HISTCONTROL=ignorespace

(2) multiple instance login

Mysql-uroot-p-S / data/3306/mysql.sock

Multiple instances use mysql-S command to make different sock files and log in to different services.

(3) No sock path is required for remote connection

Mysql-uroot-p 127.0.0.1-P3307

7. Change the MySQL database login prompt

(1) modify the login prompt on the command line, effective temporarily (enter into the database)

Mysql > prompt\ u@solindb\ r:\ m:\ s->

(2) configuration file modifies the login prompt

Add the following under the [mysql] template in the my.cnf configuration file

[mysql]

Prompt=\\ u@solindb\\ r:\ m:\\ s->

8. MySQL help command help

Help in MySQL is similar to man in linux

Mysql > help history

Mysql > help grant

Mysql > help show grants

9. Set the password for the administrator root user

Mysqladmin-u root password 'centos123' # users who do not have a password set a password

Mysqladmin-u root-p 'centos123' password' centos456'-S / data/3306/mysql.sock # is suitable for multi-instance mode

Note: the above is the linux command line

10. Modify the administrator root password

Method 1: command line modification

Mysqladmin-u root-p 'centos123' password' centos456'

Mysqladmin-u root-p 'centos123' password' centos456'-S / data/3306/mysql.sock

Method 2: SQL statement modification

Mysq > UPDATE mysql.user SET password=PASSWORD ("centos123") WHERE user='root'

Mysql > flush privileges

Method 3: root password is empty, modify root password

Mysql > set passwprd=passwpef ('centos123')

Mysql > flush privileges

Note: this method is not suitable for skip--grant--tables to change the password.

10. Retrieve the lost mysql user password

(1) stop mysql first

/ etc/init.d/mysqld stop

(2) use-- skip-grant-tables to start mysql, ignoring authorization to log in

Mysqld_safe-skip-grant-tables-user=mysql &

Mysql-uroot-p or mysql # login space-time password

Note: add-- user=mysql parameter at startup to ignore authorization verification

(3) change the root password to a new password

Mysql > update mysql.user set password=PASSWORD ("cebtos123") where user= "root" and host= "locahost"

Mysql > flush privileges

(4) restart the service and log in again

Mysqladmin-uroot-pcentoos123 shtudown

/ tec/init.d/mysqld start

Mysql-uroot-p "centos123"

11. Multi-instance MySQL starts and modifies lost root password

(1) close mysql

Killall mysqld

(2) add-- skip-grant-table parameter when starting

Mysqld_safe-defaults-file=/data/3306/my.cof-skip-grant-table &

Mysql-u root-p-S / data/3306/mysql.sock # login space-time password

(3) the method of changing the password

Mysql > UPDATE msql.user SET password=PASSWORD ("centos123") WHERE user='root'

FLUSH PRIVILEGES

12. SQL structured query language

SQL structured query language consists of six parts.

(1) data query language (DQL)

The full name of DQL is Data Query Language, and its statement, also known as "data retrieval statement", is used to obtain data from a table and determine how the data is given in the application. The reserved word SELECT is the most frequently used verb in DQL. Other commonly used reserved words in DQL are WHERE,ORDAER BY,GROUP BY and HAVING.

Mysql > select user,host from mysql.user order by user asc; # query the user,host of mysql.user and sort it in ascending order

+-+ +

| | user | host |

+-+ +

| | root | localhost |

| | root | 127.0.0.1 | |

| | root |:: 1 |

+-+ +

3 rows in set (0.00 sec)

Mysql > select user,host from mysql.user order by user desc; # query the user,host of mysql.user and sort it in reverse order

+-+ +

| | user | host |

+-+ +

| | root | localhost |

| | root | 127.0.0.1 | |

| | root |:: 1 |

+-+ +

3 rows in set (0.00 sec)

(2) data manipulation language (DML)

The full name of DML is Data Manipulation Language and its sentences include INSERT,UPDATE and DELETE. They are used to add, modify, and delete rows (data) in a table, also known as action query statements.

Mysql > select user,host from mysql.user order by user desc

+-+ +

| | user | host |

+-+ +

| | root | localhost |

| | root | 127.0.0.1 | |

| | root |:: 1 |

+-+ +

3 rows in set (0.00 sec)

Mysql > delete from mysql.user where host='::1'; # Delete the (row) data of host=;;1 in the table

Query OK, 1 row affected (0.00 sec)

Mysql > select user,host from mysql.user order by user desc

+-+ +

| | user | host |

+-+ +

| | root | localhost |

| | root | 127.0.0.1 | |

+-+ +

2 rows in set (0.00 sec)

(3) transaction processing language (TPL)

Its statement ensures that all rows of the table affected by the DML statement are updated in a timely manner. TPL statements include BEGIN

TRANSACTION,COMMIT and ROLLBACK

4) data Control language (DCL)

The full name of DCL (Date Control Language), whose statements are licensed through GRANT or REVOKE to determine access to multiple data objects for single users and user groups. Some RDBMS can use GRANT or REVOKE to control access to individual columns of the form.

5) data definition language (DDL)

The full name of DDL (Date Definition Language), whose sentences include verbs CREATE and DROP, create a new table in the database or delete a table (CREAT TABLE or DROP TABLE), add an index to the table, and so on. DDL includes a number of reserved words related to getting access to the human database directory. Words are also part of the action query.

(6) pointer control language CCL)

The full name is CURSOR Control Language, and its statements, such as DECLARE CURSOR,FETCH INTO and UPDATE WHERE CURRENT, are used for separate operations on one or more forms.

Summary: the most common categories of common SQL statements are generally three categories.

DDL-- data definition language (CREATE,ALTER,DROP)

DML-- data manipulation language (SELECT,INSERT,DELETE,UPDATE)

DCL-- data Control language (GRANT,REVOKE,COMMIT,ROLLBACK)

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.