MySQL basic commands < a >

MysqlCommon Base Operations Command (5.5)First, MySQL startup and shutdown1. The essence of MySQL start and stop

The essence of the launch: Mysqld_salf--default-file=/data/3306/my.cnf

The essence of stopping: mysqladmin uroot-p "password"-s/date/3306/mysql.sock shutdown

Two, MySQL password-related issues

1, single-instance MySQL startup

[[email protected] mysql-5.1.72]# CP Support-files/mysql.server/etc/init.d/mysqld[[email protected] mysql-5.1.72]# chmod 700/etc/init.d/mysqld[[email protected] mysql-5.1.72]#/etc/init.d/mysqld restart

2. Multi-instance MySQL startup

The default sock file can specify the path during compilation, if a multi-instance installation of MySQL starts when you want to specify the sock file, because he does not know your sock file path when you initialize the database.

General Startup:

[[email protected] ~]#/data/3306/mysql start #mysql的登录使用的是一个脚本, in the article upload attachment. Starting MySQL ... [Email protected] ~]# netstat-lnt |grep 3306tcp 0 0 0.0.0.0:3306.0.0.0:* LISTEN

3. Add login password for MySQL

Single instance and many are examples of adding passwords in the same way, just multiple instance modifications and logins to specify the MySQL sock file path

3.1 Multi-instance MySQL add password

[[email protected] bin]# mysqladmin -uroot -s /data/3306/mysql.sock  password  ' Fenghui '  [[email protected] bin]# mysql -s /data/3306/mysql.sock   ERROR 1045  (28000): access denied for user  ' root ' @ ' localhost '   (using password: no) #这时你不能不使用密码登录, use the login below, but this is not recommended because the password is exposed #mysql -s /data/3306/ Mysql.sock -uroot -p return and enter the password [[email protected] bin]# mysql -s /data/3306/ Mysql.sock -uroot -p ' Fenghui ' welcome to the mysql monitor.  commands  end with ; or \g.Your MySQL connection id is 8Server  version: 5.5.32-log source distributioncopyright  (c)  2000, 2013, 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.

4. Change password for MySQL user

4.1 Using SQL statements to modify MySQL password

Mysql> Show databases;+--------------------+| Database |+--------------------+| information_schema| | MySQL | | performance_schema| | Test |+--------------------+4 rows in Set (0.05 sec) mysql> use mysqldatabase changedmysql> select * from MySQL . user\g; #这里的列表太长, it's not listed here. #修改本地root的登录密码. mysql> Update Mysql.user set Password=password (12345) where user= ' root ' and host= ' localhost '; Query OK, 0 rows Affected (0.00 sec) rows Matched:1 changed:0 warnings:0# Here Note that the password is modified in the SQL statement to restart the MySQL service

4.2 Using Mysqladmin to modify passwords in Linux systems

[[email protected] ~]# mysqladmin-uroot-pfenghui password ' 1234 '-s/data/3306/mysql.sock# there is no need to restart MySQL service

Note: This is a multi-instance modification method, if it is a single instance only need to remove-s/data/3306/mysql.sock

5. Retrieve MySQL login password

5.1 Single Instance retrieve password

[[email protected] ~]#/data/3306/mysql stopstoping mysql ... #停止完成之后需要指定--skip-grant-tables parameter startup, he is ignoring authorization form login [[Email Protected] ~]#/application/mysql/bin/mysqld_safe--skip-grant-tables--user=mysql &[[email protected] ~]# mysql- Uroot-p #回车, he will prompt you to enter the password, you just need to return to enter the database # Log in to MySQL using the previous update to change the root local login password, restart the MySQL service is complete.

The MARIADB database uses Mysql_safe--skip-grant-tables & start the database, and also uses Updata to modify the password.

5.2 Multi-instance retrieve password

only start-up and single-instance MySQL are different, others are the same, multi-instance MySQL startup:

[Email protected] ~]#/application/mysql/bin/mysqld_safe--defaults-file=/data/3307/my.cnf--skip-grant-tables &[1] 7787[[email protected] ~]# 170808 13:34:40 mysqld_safe Logging to '/data/3307/mysql_oldboy3307.err '. 170808 13:34:40 Mysqld_safe starting mysqld daemon with databases From/data/3307/data[[email protected] ~]# mysql-uroot-p-S/ Data/3307/mysql.sock #登录之后, use Updata to modify the local root login password and restart.

Iii. definition and classification of SQL statements

1. What is SQL?

SQL full name Structured query Language, Chinese meaning Structured Query language, which is a language method for defining and manipulating data in a relational database. SQL is a database query and programming language.

2. Classification of SQL

DQC (Date Query Language)	Data Query Language	Data retrieval statements, keywords are common with SELECT
DML (Data manipulation Language)	Data Manipulation language	Keywords, INSERT, updata, delete, etc.
TPL (not commonly used)	The language of dealing with things	His statement ensures that the tables affected by the DML statements are updated in a timely manner
DCL (Data Control Language)	Data Control Language	Keyword GRANT,REVOKE
DDL (Data definition Language)	Data Definition language	Keyword Create, DROP
CCL (Cursor Control Language)	Pointer Control Language	Updata WHERE Current is used for one or more tables

Our common SQL classification

SQL classification: ddl-data Definition language (create,alter,drop,declare) dml-Data Manipulation Language (Select,delete,update,insert) dcl-Data Control Language (Grant,revoke, Commit,rollback)

Iv. common management and application of MySQL database

1. Create a database view delete and connect

1.1 Creating command syntax

CREATE Database < name >;

mysql> CREATE DATABASE Feitian;                  Query OK, 1 row affected (0.03 sec) mysql> SHOW DATABASES; +--------------------+| Database |+--------------------+| Information_schema | | Feitian | | MySQL | | Performance_schema | | Test |+--------------------+5 rows in Set (0.00 sec)

1.2 Creating a database of GBK character sets

mysql> CREATE DATABASE GBK DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci; Query OK, 1 row Affected (0.00 sec)

1.3 Creating a database of Utf-8 character sets

mysql> CREATE DATABASE UTF DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci; Query OK, 1 row Affected (0.00 sec) #数据库的名字叫UTF

1.4 Displaying the database

mysql> create database utf default character  set utf8   collate utf8_general_ci; query ok, 1 row affected  (0.00 sec) mysql> show databases;+--------- -----------+| database           |+--------------- -----+| information_schema | |  feitian            | |  gbk                | |  mysql              | |  performance_schema | |  test               | |  utf                |+--- -----------------+7 rows in set  (0.00 SEC) mysql> use gbk;database changed# View the current table, which is equivalent to Pwd.mysql> select database () in Linux; +------------+| database ()  |+------------+| gbk         |+------------+1 row in set  (0.00 sec) #查看当前登录的用户mysql > select user (); +-- --------------+| user ()          |+----------------+| [ email protected] |+----------------+1 row in set  (0.00 sec) #查看当前时间mysql >  select now (); +---------------------+| now ()        |+------- --------------+|2017-08-08 14:21:10 |+---------------------+1 row in set  (0.00  SEC) #查看数据库名称中含有e的. mysql> show databases like  '%e% '; +--------------------+| database  (%e%)    |+--------------------+| information_schema| |  feitian      | |  performance_schema| |  test       |+--------------------+4 rows in set  ( 0.00 SEC)

1.5 Deleting a database

mysql> DROP DATABASE GBK; Query OK, 0 rows affected (0.06 sec) #删除名字为GBK的数据库

1.6 Connecting the database

mysql> use mysql;database changedmysql> SELECT Database (); +------------+| DATABASE () |+------------+| MySQL |+------------+1 row in Set (0.00 sec)

2. Creation and deletion and authorization of users in the database

2.1 Deleting a user

Mysql> select User,host from Mysql.user; +------+-----------+| user | Host |+------+-----------+| Root | 127.0.0.1 | | Root |      :: 1 | | | localhost | | Root |      localhost | | | Loveyu | | Root | Loveyu |+------+-----------+6 rows in Set (0.00 sec) mysql> drop user ' root ' @ ' Loveyu '; Query OK, 0 rows Affected (0.00 sec)

Note: If the name with uppercase letters is not deleted with drop, you can use the following command

Delet from Mysql.user where user= ' LALA ' and hoset= ' localhost '

2.2 Create users and permissions for users to authorize and view users

2.2.1 Create a user and authorize one step completion

Mysql> GRANT all on mysql.* to ' Feitian ' @localhost identified by ' 1234 '; Query OK, 0 rows Affected (0.00 sec) #创建一个用户feitian只能在本地登录, password is 1234, all rights to tables in MySQL Library

2.2.2 First create the user in the authorization

mysql> CREATE USER ' lala ' @ ' 172.25.254.231 ' identified by ' 1234 '; Query OK, 0 rows Affected (0.00 sec) #创建用用户, can only be logged in 172.25.254.231 this host, the password is 1234.mysql> GRANT all on *. * to ' lala ' @ ' 172.25.2 54.231 '; Query OK, 0 rows Affected (0.00 sec) gives Lala this user all permissions to operate the entire database.

2.3 Viewing the user's authorization

mysql> show grants for  ' lala ' @ ' 172.25.254.231 '; +--------------------------------------- ------------------------------------------------------------------------------------+| grants for  [email protected]                                                                                                 |+------------------------------------------------------------------ ---------------------------------------------------------+| grant all privileges on * .* to  ' lala ' @ ' 172.25.254.231 '  IDENTIFIED BY PASSWORD  ' *A4B6157319038724E3560894F7F932C8886EBFCF '  |+- --------------------------------------------------------------------------------------------------------------- -----------+1 row in set  (0.00 SEC)

/span> all permissions that the user can authorize:

mysql> revoke delete on *.* from  ' lala ' @ ' 172.25.254.231 '; query ok, 0 rows affected  (0.00 sec) #收回一个删除的权利, in the second view to change the rights of users, will be all rights mysql>  show grants for  ' lala ' @ ' 172.25.254.231 ' \g;*************************** 1. row *** Grants for [email protected]: grant select, insert,  update, create, drop, reload, shutdown, process, file, references,  INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,  lock tables, execute, replication slave, replication client, create  view, show view, create routine, alter routine, create user,  event, trigger, create tablespace on *.* to  ' lala ' @ ' 172.25.254.231 '   identified by password  ' *a4b6157319038724E3560894F7F932C8886EBFCF ' 1 row in set  (0.00 sec) 

This article is from the "13122323" blog, please be sure to keep this source http://13132323.blog.51cto.com/13122323/1955805

MySQL basic commands < a >