MYSQL配置：数据库结构：库.表.记录
一、登录及退出
mysql -u root
exit
加密码：
mysqladmin -u root password 123456
mysql -u root -p
改密码：
mysqladmin -u root -p password 666666
二、查看
查看数据库
show databases;
切换数据库命令
use test;
查看表：
show tables;
查看表结构
desc user;
三、创建与删除
库操作
create database stu;
drop database stu;
表操作
create table user(
name char(8) not null,
age tinyint not null,
xb char(3) not null,
password char(16) not null default '123456',
id char(18) not null,
primary key (id));
drop table user;
四、记录操作命令
查：
select * from user;
select * from user where name='xc';
增：
insert into user values( 'xc','17','w','000000','01' );
insert into user(name) values( 'xh');
改：
update user set xb='m' where name='xh';
update user set password=encrypt('111111') where name='xh';
删：
delete from user where name='xh';
授权：
grant all on stu.user to wcg@localhost identified by '111111';
删除权限：
revoke all on stu.user from wcg@localhost;
查看权限：
show grants for wcg@localhost;
五、备份与还原
备份
mysqldump -u root -p stu>/opt/stu.bak
mysqldump -u root -p --all-databases>/opt/all.bak
mysqldump -u aaas -p yxbao_website>/mydir/aaa.sql 备份
还原
mysql -u root -p stu</opt/stu.bak
mysql -u root -p </opt/all.bak
mysql -u aaas -p qbxb_website<aaa.sql 还原
------------------------------------------------------------
--------《数据操作》------
use mysql； 切换数据库
select user,host,password from mysql.user; 查看数据
drop user ''@'localhost'; 删除空用户
update mysql.user set host='192.168.200.%' where user='root' and host='127.0.0.1'; 刷新
update user set password=password('123456') where user='root'; 刷新改密码
flush privileges; 刷新MySQL的系统权限相关表
在当前用户的家里面： （局部需要手动输入密码）
vim .my.cnf
[client]
user=root
password=123456
host=localhost
show engines; 查看引擎
Transactions: 事物的意思
XA： 分布式事物
Savepoints： 存储点
MyISAM 适用于写少读多的场景 适合做查询，适合数据仓库
InnoDB 适用于读多写多，支持事物
修0改数据库默认引擎：
vim /etc/my.cnf
#default-storage-engine=MYISAM
default-storage-engine=INNODB
MYISAM 写少读多 应用于电子图书馆 （表级别）
.frm :表结构
.myd :表数据
.myi :表索引
Innodb 读多写多 应用于电子商城以及各种应用 （表级别）
.frm ：表结构
.ibd ：表空间（数据和索引）
.opt ：字符集和排序规则
innodb_file_per_table=1 每个表都有一个表空间
show table status like 'user' G 查看表状态 可以查看一个表的存储引擎是什么
变量：
服务器变量：
show global variables; （查看全局变量）
show global variables like '%innodb';
状态变量：
show global status;
select database (); 查看当前正在实用的哪个库
--------------------------
mysql -u root -p123456 -e 'create database c17'
-e 参数，在shell命令中直接操作（调用）mysql 命令
mysql -u root -p123456 -D c17 -e 'create table user(id char(16) not null)'
-D 参数，在shell环境中直接登录到指定的库
-----------------------------------------------------------------
create database c15 default character set utf8;
(创建库的时候加一个默认字符集，就是实现往里面差中文了)
default-character-set=utf8 设置默认字符
通过mysql命令修改：
修改数据库的字符集
mysql>use mydb
mysql>alter database mydb character set `utf8` collate `utf8_general_ci`;
show
创建数据库指定数据库的字符集
mysql>create database mydb character set `utf8` collate `utf8_general_ci`;
mysql>create database mydb character set `gbk` collate `gbk_chinese_ci`;
通过配置文件修改:
修改/var/lib/mysql/mydb/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
为
default-character-set=utf8
default-collation=utf8_general_ci
重起MySQL:
show character set; 字符集查看
show collation; 查看排序规则
cd /usr/local/mysql/data/mydb
vim db.opt (查看修改的字符集)
-------------------------------------------------------
1.通过MySQL命令行修改默认字符集:
mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
mysql> set collation_connection=utf8;
mysql> set collation_database=utf8;
mysql> set collation_server=utf8;
查看:
mysql> show variables like 'character_set_%';
show variables like 'collation_%';
另一台服务器导入已有数据库：
本台服务器yum安装
vim /etc/my.cnf
default-character-set=utf8
default-collation=utf8_general_ci
[client]
default-character-set=utf8
-------------------《表操做》------------------
一、键与索引
1、主键：唯一，非空，只有一个
唯一键：唯一，可以为空，多个
2、索引：
show indexes from user;（查看）
（表名）（字段名）
create index kc_index on kecheng(kc); (创建）
（自定义索引名）
drop index kc_index on kecheng; （删除）
二、创建、删除、修改
1、创建
create table user(id int unsigned not null auto_increment,name varchar(30) not null,age tinyint not null,primary key(id),unique key(name),index(age));
(unsigned 正整数)
(unique 唯一键 )
(index 索引)
auto_increment(自动编号功能)
（整数类型，无符号（正数）、非空、主键或唯一键）
select_insert_id0; (最后一个插入id号)
show table status like 'user' G; (查看表状态)
create table kecheng(kcid tinyint unsigned not null auto_increment primary key,kc varchar(50) not null) engine=innodb;
create table u1 select * from user whwere id<=2;
（导入数据一样，表结构不一样）
create table u2 like user;
（导入表结构一样数据不一样）
2、修改
增加、修改、删除字段
alter table user add kcid tinyint unsigned not null;
alter table user change kcid kid tinyint unsigned not null;
增加、修改、删除索引
alter table user add indx kcid(kcid);
alter table user drop kcid; （删除）
修改表名字
alter u1 rename to nu ll;
修改表引擎
alter table user engine=innodb;
------------------------------------------------------------
mysql环境导入数据库
! mysql -uroot c15</root/c15.sql
--------------------------------------------------------
单表查询（简单查询）
select * from c15.gangwei;
select name,gongzi from c15.gangwei;
select * from c15.gangwei where name='王力邦';
select distinct gangwei from c15.gangwei;
(去除重复的，返回唯一不同值)
select name as '姓名',gongzi as '工资' from gangwei where gongzi>5000;
------布尔表达式：
= Equal to （等于）
<> aka != Not equal （不等于）
<=> NULL,safe equal to （等于空）
< Less than （不到）
<= Less than or equal to（小于或等于）
> Greater than （大于）
>= Greater than or equal to
（大于或等于）
--------逻辑表达式：
Not aka ! Logical NOT
逻辑不
AND aka && Logical AND
逻辑和
OR aka || logical OR
逻辑或
例子：
select * from gangwei where gangwei='运维' or gangwei='售前';
select * from gangwei where gangwei !='运维';
-------扩展表达式：
BETWEEN Exists in specified range
（存在于指定范围）
IN Exists in specified set
（存在于指定的组）
IS NULL Is a NULL value
（是一个空值）
IS not null Is not a NULL value
(不是一个NULL值)
LIKE Wildcard nmath
（通配符nmath）
例子：
select * from gangwei where test3 between 15 and 30;
select * from c15.gangwei where gangwei in ('运维','坐家');
select * form gangwei where gangwei is not null;
------------------------
#order by 子句：
select * from user where gangwei is not null order by gongzi desc;
asc 升序 desc 降序
#limit 子句：
select name as '姓名',gongzi as '工资' from gangwei where gongzi>500 limit 5,5;
(limit 5,5 显示去掉前5个，从第六行显示5行)
select * from info order by id desc limit 1;
(显示最后插入的)
#聚合：
avg(平均值) sum（总和） min（最小） max（最大） count（数）
select avg(test1) as '平均工资' from gamgwei;
select sum(test1) as '总分' from gangwei;
select min(test3) as '总分' from gangwei;
select max(test3) as '总分' from gangwei;
#分组
select avg(test1) as '平均分',gangwei as '岗位' from user group by gangwei;
select avg(test1) as '平均分'，gangwei as '岗位' from user group by gangwei having avg(test1)>5;
select avg(gongzi) as '平均工资', gangwei as '岗位' from user group by gangwei;
select avg(gongzi) as '平均工资', gangwei as '岗位' from user group by gangwei having avg(gongzi)>3000;
having (有的意思) 好比过滤一下的意思
---------------------增、删、查--------------------------
查询：
1、创建单表导入另一个表（简单查询）
create table info select * from gangwei;
create table info2 like gangwei;
2、多表查询
自然连接：
select g.name,g.gangwei,i.class,i.age from gangwei as g,info as i where g.name=i.name;
外连接：
左外连接
select g.id,g.name,g.gangwei,i.class,i.age from gangwei as g left join info as i on g.name=i.name;
右外连接
select g.gangwei,i.id,i.name,i.class,i.age from gangwei as g right join info as i on g.name=i.name;
3、子查询（嵌套查询）
select * from info where age>(select avg(age) from info);
select * from info where name in (select name from gangwei);
4、联合查询
(select name,gongzi from gangwei)union(select name,.age from info)
增删改：
select last_insert_id();
create table infobak like info;
（把另一张表的数据你导入新建的一张表里）
insert into infobak(name,class,age,xulei) select nmae,class,age,xulei from info where age>=20;
truncate table info; (不能加条件，初始化表的意思)
-------------------------------------------------------------------------------------------------------------------------------------
《用户权限管理》
一、用户管理：
1、创建
create user bbsroot@'%' identified by '123456';
select user,host,password from mysql.user; (查看)
flush privileges; （刷新授权表）
2、删除
drop user bbsadmin@localhost
3、修改
rename user bbsroot@'%' to bsadmin@localhost;
select user(); （查看当前登录用户）
update user set password=password('formal') where user='yxjr123456@A'; 刷新改密码
二、权限管理
1、权限级别
user: 用户账号、全局权限
host: 合并到user表
db: 数据库权限
tables_priv: 表权限
columns_priv: 字段权限
procs_priv: 存储过程、存储函数权限
use mysql;
desc user; （查看）
2、权限的种类
是一张表
3、授权
create user aaas@'%' identified by 'yxjr123456@';
grant all on *.* to aaas@'%';
show grants for root@localhost;
grant update(class) on c15.info to wcg@localhost;
grant all on *.* to formal@'%' identified by 'yxjr123456@RAK';
4、撤销权限
revoke all on *.* from wcg@localhost;
5、破解密码
vim /etc/init.d/mysql
$exec 这下面加
--basedir=/usr --user=mysql --skip-grant-table>/dev/null 2>&1 &
--skip-network (跳过远程登录)
--skip-name-resolve
（屏蔽名字解析，加快用户登录速度）
---------------------------------------------------------------PS
PS是LINUX下最常用的也是非常强大的进程查看命令
//以下这条命令是检查java 进程是否存在.
ps -ef |grep java
下面对命令选项进行说明:
-e 显示所有进程。
-f 全格式。
ps e 列出程序时，显示每个程序所使用的环境变量。
ps f 用ASCII字符显示树状结构，表达程序间的相互关系
grep命令是一种强大的文本搜索工具，它能使用正则表达式搜索文本，并把匹 配的行打印出来。grep全称是Global Regular Expression Print，表示全局正则表达式版本，它的使用权限是所有用户。
ps -ef | grep httpd :
检查httpd进程是否存在
------------------------《日志管理》-----------------------------
show global variables like '%log%';
一、错误日志
1、记录mysql启动、停止过程
2、同步过程 /var/log/mysqld.log 目录位置
一台繁忙的mysql服务器，如果开启日志功能会产生大量的I/O，严重影响mysql的性能，把日志和数据分别存放在（不存放在一个硬盘中）
可以更改目录位置
vim /etc/my.cnf
log-error=/var/log/mysqld.log
-----二、一般日志------
默认关闭， 推荐关闭
general_log |OFF
general_log_file |/var/run/mysqld/mysqld.log
-----三、慢查询日志-----
slow-query-log mysql.cnf 里面加一条就开启了
long_query_time |10.000000 十秒以外的就是慢性查询了
slow_query_log |OFF 默认是关闭的
slow_query_log_file |/var/run/mysqld/mysql-slow.log
(/usr/local/mysql/data/localhost-slow.log二进制安装目录)
vim /etc/my.cnf 开启
slow-query-log
log-slow-queries = /var/lib/mysql/mysql-slow.log
long_query_time = 2 超过多少秒
-----四、二进制日志文件------
1、做数据库主从复制
2、即时点还原，可以把数据还原到数据崩溃的那一刻
1、开启
vim /etc/my.cnf （开启二进制日志）
log-bin=mysql-bin
1) log-bin-index=/var/run/mysqld/mysql-bin.index
生成目录有哪些二进制文件
2) log-bin=/var/run/mysqld/mysql-bin
（实际工作中这个这个目录应该是一个挂载点，因为在不同硬盘上比较安全）(更改这个目录的时候必须改 属主数组)
3)sync-binlog=1
同步二进制文件，每隔多长时间、每隔多少事件往硬盘里写一次
2、格式
binlog_format
statement 保存命令，能够引起数据库变化的
row 保存的变化行
mixed 混合格式
vim /etc/my.cnf
binlog_format=mixed
3、查看
show master status; 当前正在使用哪个二进制文件
show binlog events in 'mysql-bin.000002';
当前二进制文件哪些事件
cd /usr/local/mysql/data
mysqlbinlog mysql-bin.000003 查看
mysqlbinlog --start-position=106 --stop-position=187 mysql-bin=000002>/opt/info.bak
导出来
mysqlbinlog -hlocalhost -uroot -p mysql-bin.000001>1.sql 导出来保存sql格式恢复
106 前叫 二进制文件头
at 106 开头 end 187 结束
show binary logs; 查看所有二进制文件
mysqlbinlog mysql-bin.000003 查看
4、滚动
service mysqld restart
flush logs;
5、删除
show binary logs;
purge binary logs to 'mysql-bin.000003'
删除000003 以前的
--------五、《事务》-----------------------------
INNODB特征：
多用户，多程序，多线程，并发
ACID就是：原子性（Atomicity）、一致性（Consistency）、
隔离性（Isolation）、持久性（Durabilily）
show tables status G; (查看表状态)
show global variables like '%auto%';
查看一下自动提交
set autocommeit=0; (关闭自动提交)
start transaction; 启动事务日志（操作数据库之前必须做）
启动日志以后所有操作将会被记录下来
delete from info where id=18; (假如误操作删了表里面的东西)
rollback; 回滚 （可以回复误操作删除的东西）
commit; 提交 （提交以后的才是保存的，做完操作必须做）
savepoint full; 保存当前表完全数据
savepoint no16; 保存前16数据，精确值
savepoint no17;
rollback full; 恢复完全数据的
rollback no16; 恢复精确的
show global variables like '%log%'; 查看一下事务日志的目录
事务日志和数据日志在同一个目录下
cd /avr/lib/mysql
ib_logfile0
ib_logfile1
里面为什么有两个文件呢，因为第一个写满了第二个接替第一个工作，第二个写满了第一个接替第二个工作，循环工作，防止出现工作停顿。
--------------------六、事务日志------------------------------
innodb_flush_log_at_trx_commit
0: 每秒同步，并执行磁盘flush操作；
1：每十五同步，并执行磁盘flush操作；
2：每事务日志，但不执行磁盘flush操作；
工作中用 1
===================================================================== 《备份与恢复》
一、备份类型：
热备：在数据库正在运行情况下进行备份。
温备：服务正在运行，可读不可写时备份。
冷备：在数据库停止的情况下进行备份。
物理备份：直接把数据文件拷贝出去
逻辑备份：mysqldump 逻辑备份。备份文件是语句是逻辑备份。
完全备份：一次完整备份 列如all
增量备份：不包含上一次备份的数据，只备份增加的一部分
优点：备份数据量小备份速度快。占用空间小
缺点：还原速度慢 得一个一个的还原，还原慢。而且很麻烦
差异备份：包含上一次备份的数据
优点：备份数据多，速度比较慢。占用空间大。
缺点：还原效率高。
二、 备份内容：数据文件,配置文件,二进制日志，事务日志。
备份工具：
======mysqldump :====
flush tables with read lock; 把表变成只读表，防止备份出来的数据不一致
unlock tables; 解除读锁
flush logs; 滚动日志（二进制）
man mysqldump:
--lock-all-tables
如果指定库中的表类型均为INNODB，可使用 --single-transaction启动热备，不要和--lock-all-tables 一起使用
--flush-logs
--master-data={0|1|2}
0:不记录二进制日志文件及路位置;
1：以CHNAGE MASTER TO 的方式记录位置，可用与恢复后直接启动从服务器;
2：以CHANGE MASTER TO 的方式记录位置，默认为被注释；
备份
mysqldump -u root -p --lock-all-tables --flush-logs --master-data=2 --events --all-databases>/opt/all-`date +%F-%H-%M-%S`.sql
还原
mysql -uroot -p </opt/all-all-2015-05-20-11-14-03.sql
flush-logs;
show binary logs;
show binlog events in 'mysql-bin.000002'; (查看所需要还原的事件）
二进制日志备份
备份
mysqlbinlog --start-position 120 --stop-position 907 mysql-bin.000009>/opt/9.sql
还原
mysql -uroot -p</opt/binlog-bak-2015-05-20-11--31.sql
还原时关闭二进制日志，完成后开启
缺点：
1、可能会引起浮点数据丢失精度；
2、备份数据容量大于原始数据（压缩后大大节省空间）
3、速度慢，不适合对大数据库做完全备份；
-------《第二种备份方法》
select * into outfile '/opt/back15' from info;
通常适用于备份一张表
create table info1 like info;
load data infile '/opt/back15' into table info1;
---------《LVM 逻辑卷备份》
pvcreate /dev/sdb /dev/sdc
vgcreate vg0 /dev/sdb /dev/sdc
lvcreate -L 500M -n lv0 vg0
mkfs -t ext4 /dev/vg0/lv0
mkdir /mnt/mysqldata
挂载
mount -a
vim /etc/my.cfg
[mysqld]
datadir=/mnt/mysqldata （更改一下位置）
log-bin=mysql-bin
service mysqld restart
chown -R mysql.mysql /mnt/mysqldata
1、数据文件要在逻辑卷上；
2、此逻辑卷所在卷组必须有足够空间使用快照卷；
3、数据文件和事务日志要在同一个逻辑卷上；
1、打开会话，施加读锁，锁定所有表；
flush tables with read lock;
flush logs;
show master status; （查看当前二进制的位置）
2、通过另一个终端，保存二进制日志文件及相关位置信息；
mysql -uroot -p -e 'show master status G'>/path/to/master.info;
例:mysql -uroot -p -e 'show master status G'>/opt/binlog.info
3，创建快照
lvcreate -L# -s -p r -n LV_NAME /path/to/source_lv
例：lvcreate -L 50M -s -p r -n lv0-back /dev/vg0/lv0
（-s snapshop 快照卷的意思）
（-p 权限）（-r读 -w写）
4、释放锁
unlock tables;
5、挂载快照卷 备份
mkdir /mnt/lv0-bak
mount /dev/vg0/lv0-bak /mnt/lv0-bak
备份
cp -ra * /opt/bakc15 ( -a 保留原有文件属性)
6、删除快照卷
unmont /mnt/lv0-bak
lvremove /dev/vg0/lv0-bak
7、增量备份二进制
insert into info(age) values(110); (增加一个备份没有的)
备份二进制
mysqlbinlog mysql-bin.000002>/opt/2.sql
关闭二进制文件
删除然后还原
service mysqld restart
8、还原
cd /opt/bakc15
cp -ra * /mnt/mysqldata
mysql<2.sql （二进制还原）
------------------------------------------------------------------
XTRAbackup 完整备份
innobackupex --user=root /opt
备份到哪里
进入的opt下备份的目录
cat xtrabackup_binlog_info （二进制日志保存的位置）
mysqlbinlog mysql-bin.000004 (保存位置那个日志)
innobackupex --apply-log /opt/2015-05-24_15-28.11
敲完上面的这一条必须敲，完善上面配置的
mysqlbilog --start-position=106 mysql-bin.000004>/opt/bakbinlog
(备份二进制文件)
（恢复）
innobackupex --copy-back /opt/2015-05-24_15-28-11
chown -R mysql.mysql *
（恢复之后属=主数组权限更改为原来的mysql）
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lower_case_table_names=1 (不区分大小写)
<<MySQL查看和修改wait_timeout>>
1、全局查看wait_timeout值
[plain] view plaincopyprint?
mysql> show global variables like 'wait_timeout';
2、修改全局wait_timeout值
[plain] view plaincopyprint?
set global wait_timeout=28800;
或 修改 /etc/mysql/my.cnf
添加 wait_timeout= 432000
默认值是28800秒（即默认8小时），修改为432000
单位为毫秒；
3、查看修改之后的值
-----------------------------------------------------------------------------------------
设置新的MySQL最大连接数为200：
MySQL> set GLOBAL max_connections=200；
或
vim /etc/my.cnf
max_connections=1500；
《如何查看mysql数据库连接数和内存占用》
命令： show processlist;
或 mysqladmin -uadmin -p -h10.140.1.1 processlist
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
show processlist;只列出前100条，如果想全列出请使用show full processlist;
mysql> show
processlist;
命令： show status;
命令：show status like '%下面变量%';
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 服务器工作了多少秒
---------------------------------------------------------------------------------------
log_bin_trust_function_creators=true
解决(mysql)包查询太大
说明：
1、项目刚开始运行时是正常的，可以正常访问，可是运行了一段时间就报错
2、查询下设置最大值
SHOW VARIABLES LIKE '%max_allowed_packet%';
3、修改vim /etc/my.cnf
max_allowed_packe=16384
应用：
truncate table 表名; 恢复id值从1开始