MySQL启动与更改密码
mysql启动基本原理说明:
/etc/init.d/mysqld是一个shell启动脚本,启动后最终会调用,mysqld_safe脚本,最后调用mysqld服务启动mysql,我们编辑/etc/init.d/mysqld,可以看到脚本,启动俩个进程mysqld和mysqld_safe,一般故障的时候我们用mysqld_safe来启动,
关闭mysql
1 mysqladmin - uroot -p密码 shut down
2 /etc/init.d/mysqld stop
3 kill USR2`cat path/pid`
优雅的关闭mysql但是不建议用killall杀掉所有的mysql进程,这样会导致mysql数据库起不来,所以网友遇到这样情况也很多,
我们登陆mysql后想分清出那个是正式环境那个是测试环境,
命令行修改登陆提示符
mysql> prompt\u@king\s->PROMPTset to '\u@king\s->'
配置文件修改登陆提示符
在my.cnf配置文件中[mysql]模块下添加如下内容,保存后,无需重启myysql,退出当前的session,重新登陆
[mysql]
prompt=\\u@king\s->
登陆mysql
[root @king~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection idis 2Server version:5.5.40MySQL Community Server (GPL)
Copyright (c)2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracleis a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or '\h' for help. Type '\c'to clear the current input statement.
mysql>
更改root密码
1 mysql> update user set password=password('123456') where user='root' and host='localhost'; #password('12345')是指定一个函数2 Query OK, 1 row affected (0.00sec)3 Rows matched: 1 Changed: 1 Warnings: 0
4 mysql>flush privileges; #刷新,没刷新前是在内存里面5 Query OK, 0 rows affected (0.17sec)6 mysql>
说明,修改密码都需要刷新一下哦,linux找回mysql root用户密码
单实例mysql修改丢失root方法
1,首先停止mysql
/etc/init.d/mysql stop
2,使用--skip-grant-tables启动mysql,忽略授权登陆验证
1 mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
2 mysql -u root -p #说明-p登陆时密码是空,也可以不加-p,亲测哦(#^.^#)
3 update mysql.user set password=password("newpassword") where user='root'and host='localhost';4 mysql>flush privileges;
5 mysql>quit
6 # /etc/init.d/mysql restart
7 # mysql -uroot -p
8 enter password:
9 mysql>
多实例mysql修改丢失root方法
1关闭mysql
killall mysqld
2启动时加--skip-grant-tables参数
mysql_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables& mysql -u root -p -S /data/3306/mysql.sock
3修改密码
mysql>update mysql.user set password=password("newpassword") where user='root'and host='localhost';
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
SQl的分类
SQl结构化查询语言包含6个部分
数据查询语言(DQL(data query language)),作用从表中获取数据,关键字select,
数据操作语言(DMl(data manipulation language))作用处理表中的数据insert ,update,delete
事务处理语言(TPL)关键字begin,commit和 rollback
数据控制语言(DCl)grant(授权) 和revoke
数据定义语言(DDl)create和drop在数据库中创建新表或删除表alter
指针控制语言(CCl) declare cursor, fetch into 和update where current用于对一个或多个表单独行的操作
查看数据库
show databases;或show database link '';或select database;
1 mysql>show databases;
2 +--------------------+
3 | Database |
4 +--------------------+
5 | information_schema |
6 | mysql |
7 | performance_schema |
8 | student |
9 | test |
10 +--------------------+
11 8 rows in set (0.01 sec)
创建数据库
命令语法:create database 注意库名字不能数字开头
1 mysql>create database king;2 Query OK, 1 row affected (0.01sec)3
4 mysql>show databases;5 +--------------------+
6 | Database |
7 +--------------------+
8 | information_schema |
9 | king |
10 | mysql |
11 | performance_schema |
12 | student |
13 | test |
14 +--------------------+
15 6 rows in set (0.01sec)16
17 mysql>show create database king\G18 *************************** 1. row ***************************
19 Database: king20 Create Database: CREATE DATABASE `king` /*!40100 DEFAULT CHARACTER SET utf8*/
21 1 row in set (0.00sec)22
23 mysql>
创建一个指定字符集的数据库
1 mysql>CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; #红色指定编码,蓝色校验规则。2 Query OK, 1 row affected (0.01 sec)
删除数据库
drop database
1 mysql>drop database test;2 Query OK, 1 row affected (0.07sec)3
4 mysql>show databases;5 +--------------------+
6 | Database |
7 +--------------------+
8 | information_schema |
9 | king |
10 | mysql |
11 | performance_schema |
12 | student |
13 +--------------------+
14 5 rows in set (0.01 sec)
连接数据库
命令:use 相当于linux下的cd切换目录的命令,use是切换数据库
1 mysql>use king;2 Database changed3 mysql> selectdatabase(); #查看当前的数据库,带()相当于函数,4 +------------+
5 | database() |
6 +------------+
7 | king |
8 +------------+
9 1 row in set (0.00sec)
查看数据库
select database ();相当于linux下的pwd
1 mysql> selectversion(); #查看当前的版本2 +-----------+
3 | version() |
4 +-----------+
5 | 5.5.40 |
6 +-----------+
7 1 row in set (0.06sec)8 mysql> selectuser(); #查看当前的用户9 +----------------+
10 | user() |
11 +----------------+
12 | root@localhost |
13 +----------------+
14 1 row in set (0.04sec)15 mysql> selectnow(); #查看当前的时间16 +---------------------+
17 | now() |
18 +---------------------+
19 | 2018-11-02 19:26:39 |
20 +---------------------+
21 1 row in set (0.02sec)22 mysql>
表操作
创建表并查看
create table ();
1 mysql> create table im(id int(3) not null, name varchar(20) not null default'QQ' #创建表id为int类型,name为varchar
2 );3 Query OK, 0 rows affected (0.59sec)4 mysql> show tables fromking; #从king数据库中查看表5 +----------------+
6 | Tables_in_king |
7 +----------------+
8 | im |
9 +----------------+
10 1 row in set (0.04sec)11 mysql>desc im; #查看表结构12 +-------+-------------+------+-----+---------+-------+
13 | Field | Type | Null | Key | Default | Extra |
14 +-------+-------------+------+-----+---------+-------+
15 | id | int(3) | NO | | NULL | |
16 | name | varchar(20) | NO | | QQ | |
17 +-------+-------------+------+-----+---------+-------+
18 2 rows in set (0.22sec)19 mysql>
show colums from im;查看表结构
1 mysql>show create table im \G; #\G Send command to mysql server,display result vertically向MySQL服务器发送命令,垂直显示结果2 *************************** 1. row ***************************
3 Table: im4 Create Table: CREATE TABLE `im` (5 `id` int(3) NOT NULL,6 `name` varchar(20) NOT NULL DEFAULT 'QQ'
7 ) ENGINE=InnoDB DEFAULT CHARSET=utf88 1 row in set (0.64 sec)
mysql表的字段类型
列类型
需要的存储量
tinyint
1字节
smallint
2个字节
mediumint
3个字节
int
4个字节
integer
4个字节
bigint
8个字节
float(X)
4如果x<=24或8如果25<=X=53
float
4个字节
double
8个字节
double precision
8个字节
real
8个字节
decimal(M,D)
M字节(D+2,如果m
numeric(M,D)
M字节(D+2,如果m
为表的字段创建索引
创建主键索引,查询数据库,按主键查询是最快的,每一个表只有一个主键列,但是可以有多个普通的索引列,主键列要求列的内容必须唯一,而索引列不要求内容必须唯一。
create table student(
id int (4) not null auto_increment,
name char(20) not null,
age tinyint(2) not null default'0',
dept varchar(16) default null,
primary key (id),
key index_name(name));
1 mysql> create table student( id int (4) not null auto_increment, name char(20) n2 ot null, age tinyint(2) not null default'0',dept varchar(16) default null,primar3 y key (id), key index_name(name));4 Query OK, 0 rows affected (0.18 sec)
auto_increment自增,primary key (id), 主键,key index_name(name));普通索引,
1 mysql>desc student; #查看创建的student表2 +-------+-------------+------+-----+---------+----------------+
3 | Field | Type | Null | Key | Default | Extra |
4 +-------+-------------+------+-----+---------+----------------+
5 | id | int(4) | NO | PRI | NULL | auto_increment |
6 | name | char(20) | NO | MUL | NULL | |
7 | age | tinyint(2) | NO | | 0 | |
8 | dept | varchar(16) | YES | | NULL | |
9 +-------+-------------+------+-----+---------+----------------+
10 4 rows in set (0.17 sec)
alter table student drop primary key ; 删除主键,测试不行,
1 mysql>alter table student drop primary key ;2 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
不正确的表定义;只能有一个自动COLUM它必须被定义为一个密钥
alter table student change id id int primary key auto_increment; 如果创建表忘记添加主键了,就执行这个。
创建普通索引分为唯一索引和普通索引
alter table student drop index index_name; # index固定索引,index_name是你创建表的时候的索引
alter table student add index index_name(name);
create index index_name on student (name(8)); # index固定索引(固定写法),index_name 随便写但要见名知意, on student 在哪个表上(student)8就是前8个字符创建索引。
create index index_name_dept on student (name,dept)
按条件列查询数据时,联合索引是有前缀生效特性的 index(a,b,c)仅a,ab,abc三个查询条件列可以走索引
查看表的索引
1 mysql> show index fromstudent\G;2 *************************** 1. row ***************************
3 Table: student4 Non_unique: 0
5 Key_name: PRIMARY6 Seq_in_index: 1
7 Column_name: id8 Collation: A9 Cardinality: 0
10 Sub_part: NULL11 Packed: NULL12 Null:13 Index_type: BTREE14 Comment:15 Index_comment:16 *************************** 2. row ***************************
17 Table: student18 Non_unique: 1
19 Key_name: index_name20 Seq_in_index: 1
21 Column_name: name22 Collation: A23 Cardinality: 0
24 Sub_part: NULL25 Packed: NULL26 Null:27 Index_type: BTREE28 Comment:29 Index_comment:30 2 rows in set (0.00 sec)
创建唯一索引
create unique index uni on student(name);
1 mysql>create unique index uni on student (name);2 Query OK, 0 rows affected (0.09sec)3 Records: 0 Duplicates: 0 Warnings: 0
4 mysql>desc student;5 +-------+-------------+------+-----+---------+----------------+
6 | Field | Type | Null | Key | Default | Extra |
7 +-------+-------------+------+-----+---------+----------------+
8 | id | int(4) | NO | PRI | NULL | auto_increment |
9 | name | char(20) | NO | UNI | NULL | |
10 | age | tinyint(2) | NO | | 0 | |
11 | dept | varchar(16) | YES | | NULL | |
12 +-------+-------------+------+-----+---------+----------------+
13 4 rows in set (0.02 sec)
要在表的列上创建索引
索引会加快查询的速度,但是会影响更新的速度
索引不是越多越好,要在频繁查询的where后的条件列上创建索引
小表或唯一值极少的列上不建索引,要在带包以及不同内容多的列上创建索引
往表中插入数据
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key (id)
);
1 mysql> create table test(id int(4) not null auto_increment, name char(20) not n2 ll,primary key (id));3 Query OK, 0 rows affected (0.01sec)4 mysql>show tables;5 +----------------+
6 | Tables_in_king |
7 +----------------+
8 | student |
9 | test |
10 +----------------+
11 2 rows in set (0.00sec)12 mysql> insert into test (id,name) values(1,'boy'); #插入数据,主键自增我们直接插name,insert into test(name) values('new');13 Query OK, 1 row affected (0.05sec)14 mysql> select *fromtest;15 +----+------+
16 | id | name |
17 +----+------+
18 | 1 | boy |
19 +----+------+
20 1 row in set (0.04 sec)
mysql> delete fromtest;
Query OK,1 row affected (0.05sec)
mysql> select *fromtest;
Emptyset (0.00sec)
mysql> insert into test (id,name) values(1,'boy'),(2,'new');
Query OK,2 rows affected (0.00sec)
Records:2 Duplicates: 0 Warnings: 0
查询数据
select *from 表名;
1 mysql> select *fromtest;2 +----+------+
3 | id | name |
4 +----+------+
5 | 1 | boy |
6 | 2 | new |
7 +----+------+
8 2 rows in set (0.47 sec)
指定条件查询
1 mysql> select *from test where id =1; #根据id查询2 +----+------+
3 | id | name |
4 +----+------+
5 | 1 | boy |
6 +----+------+
7 1 row in set (0.09 sec)
1 mysql> select *from test where name='new'; #根据name查询,字符串需要带引号2 +----+------+
3 | id | name |
4 +----+------+
5 | 2 | new |
6 +----+------+
7 1 row in set (0.03 sec)
多个条件查询and区交集,or取并集。排序 order by id desc(正序),sec(倒序)
1 mysql> select *from test where name='new'or id=1;2 +----+------+
3 | id | name |
4 +----+------+
5 | 1 | boy |
6 | 2 | new |
7 +----+------+
8 2 rows in set (0.08 sec)
explain查看索引、
1 mysql> explain select *from test where name='new'\G;2 *************************** 1. row ***************************
3 id: 1
4 select_type: SIMPLE5 table: test6 type: ALL7 possible_keys: NULL8 key: NULL9 key_len: NULL #可以看到索引为空10 ref: NULL11 rows: 2
12 Extra: Using where
13 1 row in set (0.04 sec)
help explain;
修改表中指定条件固定列的数据
命令语法:update 表名 set 字段=新值,where 条件
1 mysql> update test set name='wangxinxia' where id=2;2 Query OK, 1 row affected (0.90sec)3 Rows matched: 1 Changed: 1 Warnings: 0
4 mysql>desc test;5 +-------+----------+------+-----+---------+----------------+
6 | Field | Type | Null | Key | Default | Extra |
7 +-------+----------+------+-----+---------+----------------+
8 | id | int(4) | NO | PRI | NULL | auto_increment |
9 | name | char(20) | NO | | NULL | |
10 +-------+----------+------+-----+---------+----------------+
11 2 rows in set (0.25sec)12 mysql> select* fromtest;13 +----+------------+
14 | id | name |
15 +----+------------+
16 | 1 | boy |
17 | 2 | wangxinxia |
18 +----+------------+
19 2 rows in set (0.00 sec)
truncate table test;和delete from test的区别
truncate 更快,清空物理文件
delete逻辑清楚,按行删
删除数据
1 mysql> delete from student where id=3;2 Query OK, 1 row affected (0.30sec)3 mysql>show tables;4 +----------------+
5 | Tables_in_king |
6 +----------------+
7 | student |
8 | test |
9 +----------------+
10 2 rows in set (0.07sec)11 mysql> select *from student;12 +----+----------+-----+-------+
13 | id | name | age | dept |
14 +----+----------+-----+-------+
15 | 1 | zhangsan | 20 | yanfa |
16 | 2 | zhaoliu | 21 | dba |
17 +----+----------+-----+-------+
增删改表的字段
语法alter table 表名 add sex char(4);
1 mysql> alter table student add sex char (4);2 Query OK, 2 rows affected (0.54sec)3 Records: 2 Duplicates: 0 Warnings: 0
4 mysql>desc student;5 +-------+-------------+------+-----+---------+----------------+
6 | Field | Type | Null | Key | Default | Extra |
7 +-------+-------------+------+-----+---------+----------------+
8 | id | int(4) | NO | PRI | NULL | auto_increment |
9 | name | char(20) | NO | UNI | NULL | |
10 | age | tinyint(2) | NO | | 0 | |
11 | dept | varchar(16) | YES | | NULL | |
12 | sex | char(4) | YES | | NULL | |
13 +-------+-------------+------+-----+---------+----------------+
14 5 rows in set (0.20 sec)
alter table 表名 add qq varchar(10)first;
1 mysql> alter table student add qq varchar(10) first;2 Query OK, 2 rows affected (0.06sec)3 Records: 2 Duplicates: 0 Warnings: 0
4 mysql>desc student;5 +-------+-------------+------+-----+---------+----------------+
6 | Field | Type | Null | Key | Default | Extra |
7 +-------+-------------+------+-----+---------+----------------+
8 | qq | varchar(10) | YES | | NULL | |
9 | id | int(4) | NO | PRI | NULL | auto_increment |
10 | name | char(20) | NO | UNI | NULL | |
11 | age | tinyint(2) | NO | | 0 | |
12 | dept | varchar(16) | YES | | NULL | |
13 | sex | char(4) | YES | | NULL | |
14 +-------+-------------+------+-----+---------+----------------+
15 6 rows in set (0.02 sec)
rename table 原表to新表
1 mysql>show tables;2 +----------------+
3 | Tables_in_king |
4 +----------------+
5 | student |
6 | test |
7 +----------------+
8 2 rows in set (0.00sec)9 mysql>rename table test to test1;10 Query OK, 0 rows affected (0.05sec)11
12 mysql>show tables;13 +----------------+
14 | Tables_in_king |
15 +----------------+
16 | student |
17 | test1 |
18 +----------------+
19 2 rows in set (0.00 sec)
alter table test1 rename to test;
mysql插入数据解决乱码问题
set names 库的字符集,这种方法是临时的每次进来之前都需要执行
source test.sql在这个文件中插入set names 库字符集
对已有数据,需要把数据导出去,重新建库建表,在导进来。
创建/查看/删除mysql系统的用户
语法:
create user '用户名'@'主机名' identified by '用户名'; 创建用户
select host,user from mysql.user; 查看用户
drop user 用户名@'%';注意可以是单或者双引号,但不能不加,删除用户
delete from mysql.user where user='用户名' and host=‘@后面指定的主机名’;
1 mysql> create user 'usrabc'@'%' identified by 'usrabc'; #创建usrabc用户2 Query OK, 0 rows affected (0.49sec)3 mysql> select host,user frommysql.user; #查看所有用户4 +-----------+--------+
5 | host | user |
6 +-----------+--------+
7 | % | usrabc |
8 | localhost | root |
9 +-----------+--------+
10 2 rows in set (0.00sec)11 mysql> drop user usrabc@'%'; #删除usrabc用户12 Query OK, 0 rows affected (0.00 sec)
处理完用户最好刷新一下权限,flush privileges;
创建mysql用户及赋予用户权限
通过help grant 查看帮助,比较常用的创建用户的方法是:CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'
GRANT ALL ON db1.* TO 'jeffrey'@'localhost' #这俩命令是先用create创建用户,然后在授权GRANT SELECT ON db2.invoice TO'jeffrey'@'localhost'GRANT USAGE ON*.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90
通过grant命令创建用户并授权,命令语法:grant all privileges on dbname.* to username@localhost identified by 'passwd';
grant
all privileges
on dbname.*
to username@localhost
identified by 'passwd'
授权命令
对应权限
目标:库和表
用户名和客户端主机
用户密码
1 mysql> grant all privileges on king.* to liang@localhost identified by '123456';2 #创建liang用户,对king库具备所有权限,允许从localhost主机登陆管理数据库,密码是123456.3 Query OK, 0 rows affected (0.01sec)4 mysql>flush privileges;5 Query OK, 0 rows affected (0.96 sec)
1 mysql> show grants forliang@localhost; #查看权限2 +-------------------------------------------------------------------------------
3 -------------------------------+
4 | Grants forliang@localhost5 |
6 +-------------------------------------------------------------------------------
7 -------------------------------+
8 | GRANT USAGE ON *.* TO 'liang'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74
9 329105EE4568DDA7DC67ED2CA2AD9'|
10 | GRANT ALL PRIVILEGES ON `king`.* TO 'liang'@'localhost'
11 |
12 +-------------------------------------------------------------------------------
13 -------------------------------+
14 2 rows in set (0.07 sec)
create和grant配合
1 mysql> create user zh@localhost identified by '123456';2 Query OK, 0 rows affected (0.04sec)3 mysql> grant all on king.*to zh@localhost;4 Query OK, 0 rows affected (0.00sec)5 mysql>flush privileges;6 Query OK, 0 rows affected (0.00 sec)
授权局域网内主机远程连接数据库grant all privileges on king.* to liang@10.0.0.% identified by '123456';把localhost改为ip就可以了。连接,mysql -uliang -p123456 -h10.0.0.%
1 mysql> show grants forzh@localhost; #查看zh用户权限2 +-------------------------------------------------------------------------------
3 ----------------------------+
4 | Grants forzh@localhost5 |
6 +-------------------------------------------------------------------------------
7 ----------------------------+
8 | GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329
9 105EE4568DDA7DC67ED2CA2AD9'|
10 | GRANT ALL PRIVILEGES ON `king`.* TO 'zh'@'localhost'
11 |
12 +-------------------------------------------------------------------------------
13 ----------------------------+
14 2 rows in set (0.16sec)15
16 mysql> revoke insert on king.* fromzh@localhost; #更改权限17 Query OK, 0 rows affected (0.04sec)18
19 mysql> show grants forzh@localhost;20 +-------------------------------------------------------------------------------
21 --------------------------------------------------------------------------------
22 -------------------------------------------------------------+
23 | Grants forzh@localhost24
25 |
26 +-------------------------------------------------------------------------------
27 --------------------------------------------------------------------------------
28 -------------------------------------------------------------+
29 | GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329
30 105EE4568DDA7DC67ED2CA2AD9'31 |
32 |GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE T33 EMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, A34 LTER ROUTINE, EVENT, TRIGGER ON `king`.* TO 'zh'@'localhost' |
35 +-------------------------------------------------------------------------------
36 --------------------------------------------------------------------------------
37 -------------------------------------------------------------+
38 2 rows in set (0.06 sec)
1 mysql -uroot -p123456 -e "show grants for zh@localhost;"|grep -i grant #用linux中grep命令过滤需要的权限2 Grants forzh@localhost3 GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329
4 105EE4568DDA7DC67ED2CA2AD9'5 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE T6 EMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, A7 LTER ROUTINE, EVENT, TRIGGER ON `king`.* TO 'zh'@'localhost'
查看mysql的ALL PRIVILEGES权限mysql -uroot -p123456 -e "show grants for zh@localhost;"|grep -i grant|tail -1|tr ‘,’ ‘\n’ >a.txt
我们在授权用户最小的 满足业务需求的权限,而不是一味的授权ALl PRIVILEGES。