MySQL additions and deletions, login close, password modification hack application

##################################################################################

Start off and Login

##################################################################################

1 Single Instance

Mysqld_safe--user=mysql & #启动服务

MYSQLADMIN-UROOT-PWSYHT shutdown #关闭服务

Mysql-uroot-p #本地登陆

Mysql-uroot-pwsyht-h 192.168.2.1 #远程登陆

2 Dual Instance

Mysqld_safe--DEFAULTS-FILE=/DATA/3307/MY.CNF & #启动服务

Mysqladmin-uroot-pwsyht-s/data/3307/mysql.sock shutdown #关闭服务

Mysql-uroot-pwsyht-s/data/3307/mysql.sock #本地登陆

Mysql-uwsyht-pwsyht-h 192.168.2.1-p3307 #远程登陆

##################################################################################

MySQL Password modification and hack

##################################################################################

"Set password for the first time"

1 Single Instance

# mysqladmin-uroot password ' mysql '

2 Multi-instance

#mysqladmin-uroot password ' mysql '-s/data/3306/mysql.sock

Three ways to modify a user's password you must specify the where condition must use the password function to encrypt the change password

1 First method of cryptography

# mysqladmin-uroot-pmysql password ' wsyht123 '

2 The second method of changing the password

mysql> Update Mysql.user set Password=password (123) where user= ' root ' and host= ' localhost ';

mysql> flush Privileges;

Mysql> quit

3 The second method of changing the password

Mysql> set Password=password ("wsyht123"); #字符串需要用单引号或双引号括住此项是修改root用户的密码

mysql> flush Privileges;

Mysql> quit

"Forgot password method There are two ways to reset the password"

1 first method of re-setting password

Single Instance

# Service Mysqld Stop

# Mysqld_safe--skip-grant-tables--user=mysql & ignore Authorization table way to start

# MySQL #登陆时空密码

mysql> Update Mysql.user Set Password=password ("Newpass") where user= ' root ' and host= ' localhost '; #更改密码为netpass

mysql> flush Privileges; #更新权限

Mysql> quit

# Mysqladmin-uroot-pnewpass Shutdown

# service MySQL Start

# mysql-uroot-p #以新密码登陆

Dual instance

# Pkill Mysqld

# Mysqld_safe--DEFAULTS-FILE=/DATA/3306/MY.CNF--skip-grant-tables &

# mysql-s/data/3306/mysql.sock #登陆空密码

mysql> Update Mysql.user Set Password=password ("Newpass") where user= ' root ' and host= ' localhost '; #更改密码为netpass

mysql> flush Privileges; #更新权限

Mysql> quit

# mysqladmin-uroot-pnewpass-s/data/3306/mysql.sock shutdown

#/data/3306/mysql Start

# mysql-uroot-p #以新密码登陆

2 The second method of re-setting the password

1. Edit/ETC/MY.,CNF

Add a row in the [mysqld] configuration section

Skip-grant-tables

2. Restart after saving

# Service Mysqld Restart

3. Log in to the database to reset the root password

# mysql-uroot-p #直接回车不用输入密码

mysql> use MySQL

mydql> Update Mysql.user set Password=password ("MySQL") where user= "root";

mydql> flush Privileges;

Mydql> quit

Delete the "skip-grant-tables line" added in the/etc/my.cnf/file to restart MySQL

You can log in with your new password.

#####################################################################################

Modify the MySQL prompt

#####################################################################################

Temporary entry into force

mysql> prompt \[email protected] \r:\m:\s->

Permanent effect

Add the following in the/ETC/MY.CNF configuration file

[MySQL]

Prompt=\\[email protected] \\r:\\m:\\s->

Restart Service

########################################################################################

MySQL Database common management application

########################################################################################

1 Creating a Database

Create Database Wsyht; #创建wsyht数据库

Create DATABASE WSYHT_GBK default Character set GBK collate gbk_chinese_ci; #指定gbk字符集

Create DATABASE Wsyht_utf8 default character set UTF8 collate utf8_general_ci; #指定utf8字符集

2) Display Database

show databases; #显示所有数据库

Select Database (); #显示当前所在的数据库

Show databases like '%wsyht% '; #显示所有含有wsyht名字的库

Show CREATE Database Wsyht_utf8; #显示创建的数据库的字符集

Show CREATE DATABASE wsyht_gbk\g #加 \g-line display statements that do not use semicolons to view a built library

3 Deleting databases and users

Drop database wsyht; #删除wsyht数据库

Drop user ' root ' @ ' localhost ' #删除系统多余账号删除user表root用户主机名为localhost

Delete from Mysql.user where user= ' root ' and host= ' localhost '; #删除root用户

Flush privileges; #删除用户需要刷新权限

4 Connecting the database

Use MySQL #使用Mysql库可以不用加分号

5 Show the current table

Show tables; #显示当前所在的表

6 Viewing the Select usage

Select User (); #查看当前用户

Select Now (); #查看当前时间

Select User,host,password from Mysql.user order by user ASC; #user列升序排序

Select User,host,password from Mysql.user order by user desc; #user列倒序排序

7 User authorized user authorization requires Flush privileges Refresh permission

Grant all on db1.* to [email protected] identified by ' WSYHT '; #授权用户, the host refers to the client landing host here to write localhost permission to log in on this machine

Grant all privileges on * * to [email protected] identified by ' Peter '; #或这样授权用户创建用户和授权一起操作

Create user [email protected] identified by ' WSYHT '; #先创建用户语句详情意思可看MySQL简介文档

Grant all on * * to [email protected]; #再授权用户

Create user [email protected] ' 10.0.0.% ' identified by ' wsyht '; #创建远程登陆账户

Grant all on * * to [e-mail protected] ' 10.0.0.% ' identified by ' wsyht '; #创建并授权远程登陆账户法1

Grant all on * * to [e-mail protected] ' 10.0.0.0/255.255.255.0 ' identified by ' test123 '; #创建并授权远程登陆账户法2

Revoke Insert on * * FROM [email protected]; #去除他所有库和所有表的权限要先查看他对哪个库和哪个表有权限然后再对他授权的库和表进行去除权限操作

8 Viewing permissions

Show grants for [email protected];

9 Interactive viewing

MYSQL-UROOT-PWSYHT-E "Show grants for [email protected];" | Grant-i Grant | Tail-1 | Tr ', ' \ n ' > All.txt

10 Build Table Statements

Use Wsyht

CREATE TABLE Student (

ID Int (4) is not NULL,

Name Char (a) is not NULL,

Age tinyint (2) is not NULL default ' 0 ',

Dept varchar (+) Default NULL

);

11 view statements for a built table

Show CREATE TABLE Student\g

12 viewing the structure of the build table

Use Wsyht

DESC student;

13) Delete the table

drop table student;

14 Building an Index

Use Wsyht

CREATE TABLE Student (

ID int (4) NOT NULL auto_increment,

Name Char (a) is not NULL,

Age tinyint (2) is not NULL default ' 0 ',

Dept varchar (+) default NULL,

Primary key (ID),

KEY index_name (name)

);

Auto_increment, #id字段自动递境

Primary key (ID), #主健

KEY index_name (name) #name字段普通索引

14 Adding a primary health

ALTER TABLE student change ID ID int primary key auto_increment;

15) Add Index

ALTER TABLE student Add index inedex_name (name);

16 Deleting an index

ALTER TABLE student DROP INDEX index_name;

Drop index ind_name_dept on student;

17 View All Indexes

Show index from Student\g

18 Creating an Index

Create INDEX index_dept on student (Dept (8)); #在student表的dept列上前八个字符创建索引

Create INDEX ind_name_dept on student (name,dept); #创建联合索引

Create INDEX ind_nam_dept on student (name (8), Dept (10));

19 Creating a unique index non-primary key

Create unique index uni_ind_name on student (name);

20 Inserting rows

INSERT into student (Id,name) VALUES (1, ' wsyht ');

INSERT into student values (4, ' Jack ', +, ' abc '); #按照顺序插值

INSERT into student values (5, ' dock ', +, ' abc '), (6, ' Tom ', +, ' CAD '); #插入两个值

21 Deleting a database

Delete from student;

22 Backing up the database

# mysqldump-uroot-pwsyht123-b Wsyht >/opt/wsyht.sql #备份wsyht库,-a back up all libraries

23 Check Backup SQL data content filter useless information after backup

# egrep-v "\/|^$|--"/opt/wsyht.sql

24 Querying data

Select *from Student; #查询表所有数据的值

Select ID, name from student; #查询id the value of the Name field

Select Id,name from student limit 2; #只查询id, the value of the Name field 2 rows of data

Select Id,name from student where id=1; The value of the #条件查询查询id =5

Select Id,name from student where name= ' Tom '; #字符患查询要加引号

Select Id,name from student where Name= ' Tom ' and id= ' 6 '; #两边都要成立

Select Id,name from student where name= ' Tom ' or id= ' 8 '; #一边成立即可

Select Id,name from student where id>3 and id<6; #范围内查询

Select Id,name from student order by ID; #排序id列不加默认正向排序

Select Id,name from student order by ID ASC; #排序id列加asc正向排序

Select Id,name from student order by id DESC; #排序id列加desc反向排序需要指定列

23explain view execution plan for SQL statements

Explain select *from t1 where name= ' Wsyht ' \g

24 modifying data in a table

Update T1 set name= ' Jack ' where id=1; #更新表t1的name字段为jack条件为id =1

25 Recovering a database library

Mysql-uroot-pwsyht123 Wsyht </opt/wsyht.sql

Mysqlbinlog mysql-bin.000002 #查看binlog日志

mysqladmin-uroot-pwsyht123 Flush-log #刷新日志生成新日志

mysqlbinlog-d wsyht mysql-bin.000002 > Bin.sql #-d Specify Library backup as Bin.sql file

26 preventing errors from modifying tables causing serious failures

Echo ' Alias mysql= ' mysql-u ' >>/etc/profile

Source/etc/profile

Mysql-uroot-pwsyht-u #加-U is a batch modification that prevents you from forgetting the where after update

27 deleting data from a table

Delete from test where id=1; #删除test表id号等于1的行

Delete from Test where id>3; #删除test表id号等于3的行

TRUNCATE TABLE test; #直接清空整个表test是表名

Truncate emptying the physical file delete logic clears by row delete

28 adding or deleting a table field

ALTER TABLE test add sex char (4); #在test表添加字段sex

ALTER TABLE test add age int (3) after name; #在test表name后面添加age字段

ALTER TABLE test add QQ varchar (+) first;

29 Changing table names

Rename table test to T1; #把test表改名为t1;

ALTER TABLE T1 rename to test; Rename the T1 table to test

30 Deleting a table

drop table test;

########################################################################################

MySQL Other

########################################################################################

mysql> system Ls/tmp #MySQL交互下查看系统当前路径

Mysql> Help Grant #查看grant的命令详解

Hope to communicate with the technology can contact me by the following ways

My OPS group 517751492

My QQ1934844044.

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1717090

MySQL additions and deletions, login close, password modification hack application