MySQL笔记

发布时间:2019-04-22 06:00:03发布者:Mr.Zhang阅读(91)


一、常用sql

1.1帮助命令

mysql  help命令相当于linux的man命令;
help  contents;    #相当于linux man命令
update  mysql.user  set password= PASSWORD('123123')  where user="root" and  host="localhost";     #修改root用户密码

1.2SQL语言分类

1.数据查询语言DQL
2.数据操作语言DML
3.事务处理语言TPL
4.数据控制语言DCL
5.数据定义语言DDL
6.指针控制需要CCL

1.3普通常用命令

show  create  database;    #打印创建数据库的语法
select  database();            #打印use的数据库
CREATE  USER  '用户'@'主机'  IDENTIFIED BY  '密码';    #授权用户
rename  table  原表名  to  新表名;                                #重命名表
select  id, name from  test  where  id = 2 and name='mashy';    #select 查询语句
mysql -e "select * from 库名.表名"                                #使用-e参数,可在命令行执行MySQL命令

1.4TRUNCATE与DELTE区别

TRUNCATE:通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放,因此使用的系统和事务日志资源更少。
DELETE语句每删除一行,并且会在事务日志中为所删除的每一行记录一项。
TRUNCATE与不带WHERE子句的DELETE语句功能相同:两者均删除表中的全部行,到TRUNCATE比DELETE速度更快。

二、备份与恢复

2.1mysqldump进行数据库备份

mysqldump -u user -p password 参数 数据库名 > 备份文件名.sql 
mysqldump -u user -p password -B 数据库名 > 备份文件名.sql                # -B参数的作用是增加创建数据库和连接数据库的语句,并且后面可直接接多个库名,即同时备份多个库。
mysqldump -u user -p password -B 数据库名 | gzip > 备份文件名.sql        #指定压缩命令gzip压缩备份数据库
mysqldump -u user -p password -A > 备份文件名.sql                             #备份所有的数据库

2.2备份表

mysqldump -u user -p password 数据库名 表名> 备份文件名.sql 
#### 备份单个表
mysqldump -u user -p password 参数 数据库名 表名1 表名2> 备份文件名.sql 
#### 备份多个表

2.3备份数据库表结构(不包含数据)

mysqldump -u user -p password -d 数据库名 > 备份文件名.sql         #-d参数表示备份数据库表结构,不包含数据

2.4备份数据库表数据(不包含表结构)

mysqldump -u user -p password -t 数据库名 > 备份文件名.sql 

2.5同时将数据和表结构分离导出

#需调整配置文件参数并重启MySQL
#在[mysqld]模块下增加secure_file_priv=''
mysqldump -u user -p password 数据库名 表名 -T  备份目录 

2.6mysqldump工作原理

    利用mysqldump命令备份数据的过程,实际上就是把数据(包括库表)从MySQL库里以SQL语句的形式直接输出或者生成备份文件的过程,这种备份成SQL语句的方式称为逻辑备份。

2.7取出mysql库名

./bin/mysql -uroot -p123 -e "show databases" | egrep -v "_schema| atabase"

2.8binlog是什么

    Binlog是一个二进制格式的文件,用于记录用户对数据库更新的SQL语句信息,例如更改数据库库表和更改内容的SQL语句都会记录到binlog里,但是对表库等内容的查询则不会记录到日志中。因为binlog文件里的数据就是写入数据库的数据,使用binlog文件恢复数据,称之为二进制增量数据恢复
#开启binlog文件
log_bin=mysql-bin
server_id=1

2.9记录binlog位置的特殊参数(—master-data)

    使用--master-data参数备份后,在备份的文件对应的SQL语句里会添加CHANGE  
MASTER 语句及binlog文件及位置点信息。
    当"--master-data=1"时,备份结果为可执行的"CHANGE MASTER..." 语句;当"--master-data=2"时,备份结果为注释的"CHANGE MASTER"语句。

2.9.1锁表备份

mysqldump 加上-x参数

innodb表特有的备份参数(—single-transaction)

mysqldump -u user -p password -B  --master-data=2 --single-transaction 数据库名  > 备份文件名.sql
#当使用mysqldump的“--single-transaction”参数对innodb表进行备份时,会开启一个事务,并将整个备份过程放到一个事务里,以确保执行本次dump会话时,不会看到其他连接会话已经提交了的数据,即备份开始的时候表是什么样,备份出来就是什么样子。

2.9.2将表数据导出为纯文本

select * from test into outfile "/data/test_1.txt" ;        #导出时设置字符集
select * from test into outfile "/data/test_2.txt" character set utf8 ;        #以指定的分隔符导出,这里指定“-”为不同域之间的分隔符
select * from test into outfile "/data/test_3.txt" character set utf8 fields terminated by '-';        #导出时设置对字段内容进行引用,此处是'"'双引号

2.9.3导入表

mysql> load data infile '/data/test_1.txt' into table test ;    #从/data/test_1.txt读数据写入test表中

三、恢复数据库

1.进入mysql数据库source 备份.sql
2.mysql 需要恢复的库名  < 备份.sql 

3.1mysql -e 参数查看mysql数据

mysql -u user -p password -e "select * from 库名.表名;"        #查看表数据
mysql -u user -p password -e "show processlist;"                 #查看SQL线程执行状态
mysql -u user -p password -e "show full processlist;"            #查看完整SQL线程状态
show variables like "%_timeout%";                            #产看超时参数配置
在my.cnf文件mysqld模块设置timeout值
interactive_timeout=120
wait_timeout=120
#修改完成后重启mysql数据库使配置生效

3.2查看数据库状态命令

show global status; #查看整个数据库运行的状态信息
show processlist ;  #查看正在执行的SQL语句,可能无法看全完整的SQL语句
show full processlist ;     #查看正在执行的完整SQL语句。
show variables;     #查看数据库的配置参数信息
show variables like "%_timeout%"; #过滤查看
kill ID                 #杀掉SQL线程的命令,ID为线程号
show session status;    #查看当前整个数据库的运行状态
show engine innodb status ; #显示innodb引擎的性能状态

3.3mysqladmin常用命令参数

mysqladmin password newpasswd   #设置密码
mysqladmin -uroot -ppasswd password newpasswd   #修改密码
mysqladmin -uroot -ppasswd status #查看状态
mysqladmin -uroot -ppasswd -i 1 status  #每秒查看一次状态
mysqladmin -uroot -ppasswd extended-status #查看当前整个数据库的运行状态
mysqladmin -uroot -ppasswd processlist #查看执行的SQL语句信息
mysqladmin -uroot -ppasswd processlist -i 1  #每秒查看一次执行的SQL语句信息
mysqladmin -uroot -ppasswd shutdown         #关闭mysql服务
mysqladmin -uroot -ppasswd variables    #查看数据库的配置参数信息

3.4解析指定库的binlog日志

mysqlbinlog -d oldboy mysql-bin.000001 -r bin.sql        
#mysqlbinlog可以指定-d实现分库导出binlog,如果使用-d参数,那么在更新数据时,必须要有use库名,才能分出指定库的binlog。必须采用如下写法:
use 库名;
insert into  test values(1,'huge');
下面这种写法就不行
insert into 库名.test values(2,'oldgirl');

3.5binlog过期清理

在my.cnf中配置
expire_logs_days=7
#只保存7天内的binlog日志

四、数据库备份概念

4.1全量备份

    全量数据就是数据库中所有的数据(或某一个库的全部数据);全量备份就是把数据库中所有数据进行备份

4.2增量备份

    增量数据就是指上一次全量备份数据之后到下一次全量备份数据之前数据库所更新的数据。




本文转自博客园,原文地址:https://www.cnblogs.com/iloveyoukm/p/10747614.html