Mysql数据库常用操作

2019独角兽企业重金招聘Python工程师标准>>>

数据库常用操作整理

1.授权 [ grant ] / 回收 [ revoke ] -----正对用户

增加用户并授权：
grant 权限 on 库名.* to 用户名@登录主机 identified by "密码"
注释：权限select,insert,upgate,delete 不同权限用逗号隔开即可。主机名可以写成ip匹配数字可以用%代替。如192.168.1.%
回收权限：revoke 权限 库名.* 用户名@登录主机；
grant all on *.* to 'wyl'@'%' identified by '123456' with grant option;flush privileges;创建用户并授与root相同权限。
mysql5.7版本
只创建一个用户：CREATE USER 'test'@'%'  IDENTIFIED BY '1234'; 
添加用户并授权：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '12' WITH GRANT OPTION;
修改数据库密码：
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
update mysql.user set authentication_string=password(“123”) where User=”test” and Host=”localhost”;
#%表示非本本地用户指的是：（127.0.0.1，localhost）
创建数据库
create database testDB; 
create database testDB default charset utf8 collate utf8_general_ci;
删除用户
drop user 用户名@’%’; 

 

2.创[ create ]-正对表，库

创建数据库：create database [库名]
显示数据库：show databases;
查看是否存在是数据库：show database like 'xxx%'
打开数据库：use [库名]
查看当前数据库状态：select database();
创建数据表：create table [表名]（字段名 字段类型(字段要求) 字段参数...）;
显示表字段：desc [表名]； desc（describe描述）
查看库中数据表：show tables;
复制表：create table table2 select * from table1;

3.插[ insert  ]-针对数据。

插入数据：insert into [表名] values(1.'wang',28,'133523'),(2,'yun',34,1322344);
插入数据：insert into 表名（字段1，字段2）values(值1，值2)
数字不需要加引号，字符串需要加引号。

4.查[ select  ]-针对数据。

查看数据：select *(列) from [库名].[表名] where 条件 ;
查看数据：select 表2.字段 from 表1 inner join 表2 on 表1.字段3 = 表2.字段3；
如：select id  from wangyunlong.wyl where id=2;
select user,password from mysql.user where user='root';
注：条件模糊匹配包含： 字段名 like '%王%' ；
    条件或 字段名 in（值1，值2，值3,.）;取反用not in即可。
    条件和 用 and 相连即可。
    条件符号可用 : >  <  = ,in ,in not, like '%xx',
    条件前n行在其后加 limit n;  
查看当前用户：select user();
查看授权用户：select user,host from mysql.user;
查看所有用户授权详情：select * from mysql.user;
查看密码：select user,password from mysql.user;
查看某一个用户的授权信息：show grants for 'wyl'@'localhost'\G
查看当前数据版本：select version();
查看当前使用的数据库: select database();

5.改：[ update  ]-针对数据

更新表记录：update [库名].[表名]  set 字段1=字段值1，字段2=字段值2.... where 条件；
更改密码：update mysql.user set password=password('123456') where user='root' and host='localhost';
创建类似root用户还可以：权限部分设置all 权限，最后加入with grant option;
update wangyunlong.wyl set name='wangyunlong' where id=2;
更改密码：
set password for root@localhost = password('mintmath');
mysqladmin -uroot -p123456 password 654321

6.删[delete]--针对数据，[drop]--针对表结构，库 ，用户

删除数据库:drop database [库名]；
删除数据表：drop table [表名]；
删除用户：drop user 用户名@客户端；
删除表记录：delete from [表名] where [条件]；
删除数据：delete from [表名]；

7.改 ：[  alter  ]------针对表结构

添加主键：alter table 表名 and primary key(字段名)；
添加普通索引：alter table 表名 and index 索引名（字段名）；
添加普通索引：alter table 表名 and index 索引名（字段名（前几个字符写数字））；
添加联合索引如：alter table 表名 and index 索引名（id(7),name(8)）；
删除索引：alter table 表名 drop index 索引名；
添加字段（列）：alter table 表名 and 字段名 字段类型；默认在最后面插入。
添加字段（列）如：alter table test add age int(4) after id; 在id后插入
删除字段（列）：alter table 表名 drop 字段名；
修改字段名：alter table 表名 change 字段名1 字段名2 数据类型；

8.授权 [ grant ] / 回收 [ revoke ] -----正对用户

增加用户并授权：
grant 权限 on 库名.* to 用户名@登录主机 identified by "密码"
注释：权限select,insert,upgate,delete 不同权限用逗号隔开即可。主机名可以写成ip匹配数字可以用%代替。如192.168.1.%
回收权限：revoke 权限 库名.* 用户名@登录主机；

9.复制库;复制表

复制表: create table xintable select * from jiutable;
复制库:mysqldump mtmath -u root -pxxx --add-drop-table | mysql sx_mtmath -u root -pxxx

10.查看缓存是否开启：

show variables like "%cache%";查看：
query_cache_size 0
query_cache_type OFF
设置开启这两个值：set global query_cache_size =1024000000;set session query_cache_type=on;
查看命中率：show global status like 'QCache%';查看hit是否起作用。执行selsect测试。

11.慢查询处理；

（1）漫查询日志是否开启
show variables like '%slow%';查看Log_slow_query是否开启,slow慢query查询.
临时生效：set global log_slow_queries=on;
永久生效：log-slow-queries=/var/lib/mysql/slowquery.log
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
（2）设置慢查询时间
show variables like '%long%';查看慢查询日志时间；默认为10s，应该改成0.5-1秒；set long_query_time=0.5;
永久生效：long_query_time=2 (记录超过的时间，默认为10s)
（3）show full processlist 发现慢语句，利用desc或explain加语句查看是否应用了索引。
ows行-扫描的行数。type类型，key（索引）排查；

12.其他慢查询处理方式:

show variables like '%profiling%'; 查看新能指标是否开启；profiling(分析):会记录语句执行时间；
开启:set profiling=on;接着执行慢select....;执行完成后，
执行:show profiles;查看执行时间；
执行:show profile for query ID;id为show profiles显示的id，可以显示执行语句执行到那一阶段比较慢。复制执行时间比较长的状态，百度解决

13.添加索引

普通索引：alter table test add index(t_name);
主键索引：创建表的时候就已经创建。主键索引唯一且不能为空ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
添加唯一索引：唯一索引，不允许有重复的。ALTER TABLE `table_name` ADD UNIQUE ( `column` )
添加多列索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

14.数据备份与合并

备份:mysqldump -uroot -pxxx -hx.x.x.x yyxx >./yyxx.sql
导入:mysql -uroot -pmintamth --force <./xxxsql
注意备份加：--skip-extended-insert 表示没查一个语句生成一个inster语句，这样有助于导入时冲突排错。但生产的备份文件超集大,建议特殊情况下利用该参数。
            -t 表示只导出数据，不导出表结构，如数据合并时,如果导出的数据包含表结构，那么会造成本来合并的数据会备份覆盖,而不是合并

15.binglog日志恢复

利用mysqlbinlog命令进行数据解析:
如:mysqlbinlog mysql-bin.000005 > test0005.sql
a.使用时间作为约束条件
mysqlbinlog --start-datetime="20170331 00:56:19" --stop-datetime="20170331 00:57:11" mysql-bin.000005 | mysql -u[username] -p[password] 
b.使用行号作为约束条件
mysqlbinlog --start-position=1556 --stop-position=1648 mysql-bin.000005 |mysql -u[username] -p[password]

16.mysql主从同步状态查看

show slave status\G;
关注一下两个参数是否为YES。
Slave_IO_Running：YES
Slave_SQL_Running:YES

17.其他操作

启动关闭：/etc/init.d/mysqld start/stop
关闭：mysqladmin -uroot -p123456 shutdown 千万不要用忙碌kill -9杀数据库。企业慎用 不然数据库起不来，实在没办法了可以使用killall mysqld
查看端口：ss -lnt|grep 3306
查看进程：ps -ef|grep mysql|grep -v grep
登录:mysql:mysql -uroot -p  多实例：mysql -uroot -p -S /data/3307/mysql.sock
远程登录多实例无需指定mysql.sock 只需要-P 指定端口即可。
更改密码：mysqladmin -uroot -p 旧密码 password 新密码
更改密码：use mysql;update user set password=password("shapolang") where user="root";更改当前用户密码：set password=password('新密码')；
刷新权限：flush privileges;
本地登录：mysql -uroot -S /usr/lib/mysql/mysql.sock -p 回车
远程登录：mysql -h ip -uroot -S /usr/lib/mysql/mysql.sock -p 回车
查看创建完整语句：show 创建语句\G
查看命令格式：help 命令语句
查询一个语句的执行计划：explain select ......   查询语句前面加expain即可
查看binlog是否启用：show variables like '%log_bin%';
临时开启或关闭session：set session sql_log_bin = On/OFF;
查看整个数据库运行状态信息:show global status; 很重要，要分析并做好监控。
查看binlog 名称：show binary logs;
查看某表的索引：show index from 表名\G
查看数据库状态：show status;
进行了多少次select查询show status like "%select%";
删除binlog日志：reset master；
删除005之前的binlog文件：purge master/binary logs to 'mysql-bin.000005';

18.性能指标说明

mysqladmin -S /data/app/mysql-3306/mysql.sock extended-status
重要指标说明
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。
###########################
uptime               ：mysql启动后运行的时间
com_update       ：执行update的数目
com_select         ：执行select的数目
com_delete        ：执行delete的数目
com_insert         ：执行insert插入数目
slow_queries：查看时间超过3秒的数目
com_rollback      ：事物回滚次数
com_commit      ：事物提交次数
Com_begin      ：
Questions         ：发往服务器的查询的数量
bytes_sent         ：示发送给所有客户端的字节数
bytes_received   ：从所有客户端接收到的字节数

 

转载于:https://my.oschina.net/wangyunlong/blog/3000396