mysql> \s #查看mysql数据库信息
授权命令
grant all on *.* to user1@192.168.111.20 identified by "111";
授权说明：
all 授权所有权限
*.* 表示对所有库的所有表授权
user1 授权用户
192.168.111.20 授权地址
"111" 授权密码
查看用户授权表
select user,host,plugin,authentication_string from mysql.user;
authentication_string：为密码，mysql版本不同密码字段会不同
查看当前所在的数据库
select databases();
开启 MySQL bin-log 日志
vi /etc/my.cof
datadir = /usr/local/mysql/var $日志存放位置
[mysqld]
port = 3306
socket = /tmp/mysql.sock #
log-slow-queries=mysql-slow.log #慢查询日志
log-error=mysql.err #错误日志
log=mysql.log #一般的日志
log-bin=mysql-bin #bin-log日志
查看二进制日志是否开启
show variables like "%bin%";
log_bin | ON #表示已开启
优化MySQL 步骤
1：查看慢查询日志是否开启
show variables like "%slow%";
show variables like "%long%"; #查看慢查询时间 默认10秒
2：定位执行效率较低的sql语句
1:explain select * from table where id = 1000\G;
2:desc select * from table where id = 1000\G;
3：进行sql语句索引优化
alter table t1 add index in_name(name);
此时就会多个最新的bin-log日志（新增一个日志）
mysql> flush logs;
查看最后一个bin日志
mysql> show master status;
清空所有的bin-log日志
mysql> reset master;
查看二进制日志文件内容
[root@localhost var]#/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001
用mysqlbinlog mysql-bin.000001时报错,说明是字符集有问题
清空表
truncate 表名
通过 bin-log 恢复数据
[root@localhost var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -p123 lzslnmhj
-------------------------------------------------------------------
-------------------------------------------------------------------
备份数据：
mysqldump -uroot -p123 test -l -F >/tmp/test.sql
test：数据库名
-l：读锁（备份期间禁止写入操作）
-F：即flush logs,可以重新生成新的日志文件，当然包括log-bin日志
恢复数据
mysql -uroot -p123 test -v -f "/tmp/test.sql";
-v：查看导入的详细信息
-f：是当中间遇到错误时，可以skip过去，继续执行下面的语句
恢复binlog二进制日志文件
mysqlbilog --no-defaults binlog-file |mysql -root -p123 database
binlog-file：要恢复的binlog-file二进制日志文件
database：要恢复到哪个据库
mysqlbinlog后跟的主要参数：
--stop-position="100" #结束位置
--start-position="50" #开始恢复的位置
--stop-date="2011-01-22 21:11:12"
--start-date="2011-01-22 11:11:12"
例：
1：
[root@localhost var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001 --start-position="100" --stop-position="300" |mysql -uroot -p123 lzslnmhj
2：
[root@localhost var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001 --stop-position="300" |mysql -uroot -p123 lzslnmhj
备份数据：
mysqldump -uroot -p123 lzslnmhj -l -F >/tmp/lzslnmhj.sql
以库为单位导出：
mysqldump -u用户名 -p密码 -B 库1 库2 库3 > 地址/备份文件名
登录下：
sourec 地址/文件名
不登录情况下恢复数据：
mysql -u用户名 -p密码 < 地址/库级备份文件名
mysql -u用户名 -p密码 库名< 地址/表级备份文件名
通过scp隧道传送到子服务器：
scp /tmp/lzslnmhj.sql 192.168.111.20:/tmp/
主服务器数据配置：
server-id = 1
从服务器数据库配置：
server-id = 2
master-port = 3306
master-host = 192.168.111.19
master-user = user1
master-password = 111
LNMP包配置从库
mysql>stop slave;
change master to
master_host = '192.168.111.19',
master_port = 3306,
master_user = 'user1',
master_password = '111',
master_log_file='mysql-bin.000002',
master_log_pos = 1102;
master_host #主服务器IP地址
master_port # 主服务器端口
master_user #主服务添加的用户名
master_password #主服务添加的密码
master_log_file #主服务器的File字段
master_log_pos #主服务器的Position字段
mysql>start slave;
查看主从是否同步成功
mysql>show slave status\G
--------------------------------------------------------------------
show engines; #查看引擎
show plugins; #查看是否有 partition
MySQL分区
range分区：插入800万数据进行测试
create table part_tab(c1 int default null,c2 varchar(30) default null,c3 date default null)engine=myisam
partition by range(year(c3))(
partition p0 values less than (1995),
partition p1 values less than (1996),
partition p2 values less than (1997),
partition p3 values less than (1998),
partition p4 values less than (1999),
partition p5 values less than (2000),
partition p6 values less than (2001),
partition p7 values less than (2002),
partition p8 values less than (2003),
partition p9 values less than (2004),
partition p10 values less than (2010),
partition p11 values less than maxvalue
);
create table no_part_tab(c1 int default null,c2 varchar(30) default null,c3 date default null)engine=myisam
create procedure load_part_tab()
begin
declare v int default 0;
while v < 8000000 do
insert into part_tab
values(v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v=v+1;
end while;
end$
list分区
hash分区
key分区
创建 hash 分区语法
create table t2(
id int
)engine=myisam
partition by hash(id)
partitions 5;
给 t2 的 id 字段创建索引
例：
create index in_id on t2(id);
倍增数据语法（1000万条数据）
insert into t2 select * from t2;
使用存储过程 存入 10000 数据
\d // 修改定界符（也就是把“;”改成“//”做为结束符）
或者使用 delimiter // 来修改定界符
创建存储过程语句(创建一个名为 p3() 存储过程)
\d //
create procedure p3()
begin
set @i=1;
while @i<=10000; do
insert into t3 values(@i);
set @i=@i+1;
end while;
end//
调用存储过程语法
改回原先定界符
\d ;
调用存储过程 p3()
call p3();
删除存储过程 p3
drop procedure p3;
查看存储过程
show create procedure p3;
用 show PROCEDURE status 命令查看：
-- 查看所有的存储过程
show PROCEDURE status;
-- 查看特定数据库存储过程
show PROCEDURE status where db='test';
-- 用指定的登录名查看该用户创建的存储过程
show PROCEDURE status where definer='root@localhost'; -- @localhost为用户登录位置(本地登录)
-- 查看指定时间段创建存储过程
show PROCEDURE status where created between '2017-02-17 00:00:00'
and '2017-02-17 23:59:59';
用系统表mysql.proc来查看：
-- 查看所有的存储过程信息
select * from mysql.proc;
-- 查看特定数据库里的存储过程
select * from mysql.proc where db='test';
-- 查看某个用户定义的存储过程
select * from mysql.proc where definer='root@localhost';
-- 查看某时间段创建的存储过程
select * from mysql.proc where created between '2017-02-17 00:00:00'
and '2017-02-17 23:59:59';
innodb 使用分区技术 相关配置
是否使用共享心脏独占表空间来
innodb_file_per_table = 1
代表为数据库文件所存放的目录
innodb_data_home_dir = /usr/local/mysql/var
配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1，没给出文件位置，所以默认是在MySQL的数据目录内
innodb_data_file_path = ibdata1:10M:autoextend
为日志存放目录
innodb_log_group_home_dir = /usr/local/mysql/var
使用外键需要注意的问题
创建外键方式：(注：只支持innodb类型)
mysql>create table t1(id int,name char(30),foreign key(id) references outTable(id) on delete cascade on update cascade);
MySQL 数据库性能优化
复制表结构
create table t2 like t1;
复制表数据
insert into t2 select * from t1;
index：普通索引
unique：唯一索引
创建索引
语法一：
create index in_name t1(name);
语法二：
alter table t1 add index in_name(name);
查看索引
show index from t1;
show status like 'Handler_read%'; #查看索引使用情况
删除索引
语法一：
drop index in_name on t1(name);
语法二：
alter table t1 drop index in_name;
MySQL 内容函数
字符串函数：
concat(string1,string2); #连接字符串
lcase(string) #转换成小写
ucase(string) #转换成大写
length(string) #string长度
ltrim(string) #去除前面的空格
rtrim(string) #去除后面的空格
repeat(string,count) #重复count次
replace(str,sea_str,rep_str) #在str中用rep_str替换sea_str
substring(str,position [,length]) #从str的position开始，取length个字符
space(count) #生成count个空格
数学函数：
bin(decimal_number) #十进制转二进制
ceiling(number) #向上取整
floor(number) #向下取整
max(col) #取最大值,聚合时使用
min(col) #取最小值,聚合时使用
sqrt(number) #开平方
rand() #返回0-1内的随机值
日期函数：
curdate() #返回当前日期
curtime() #返回当前时间
now() #返回当前的日期时间
unix_timestamp(date) #返回当前date的unix时间戳
from_unixtime() #返回unix时间戳的日期值
week(date) #返回日期date为一年中的第几周
year(date) #返回日期date的年份
datediff(expr,expr2) #返回起始时间expr和结束时间expr2间天数
MySQL 预处理语句
创建预处理语句：
mysql>prepare stam from 'select * from t1 where id>?';
设置一个变量：
mysql>set @i=1;
执行stam预处理：
mysql>execute stam using @i;
设置@i为5：
mysql>set @i=5;
再去执行stam：
mysql>execute stam using @i;
删除预处理 stam 语句 ：
mysql>drop prepare stam;
分析表的语法(检查一个或多个表是否有错误)
check table sales;
修改表名
rename table t2 to t1; #把表名t2的表修改成t1
优化表
optimize table t1;
注：导出前可以将索引先关掉以提升导出速度(普通索引)
关闭索引：
alter table t1 disable keys;
开启索引(再把索引加上)：
alter table t1 enable keys;
关闭唯一索引：
set unique_checks=0
恢复唯一索引：
set unique_checks=1
在(导入/出)数据前执行行 set autocommit=0 关闭自动提交事件
在(导入/出)数据结束后执行 set autocommit=1 恢复自动提交,可以提高导入效率
快速导出数据(快速备份数据)
1：select name from t1 into outfile "/tmp/t.txt";
2：select * from t1 into outfile "/tmp/t.txt";
快速导入数据
1：load data infile "/tmp/t.txt" into table t2(name);
2：load data infile "/tmp/t.txt" into table t2;
注意如果报错ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决方式
首先在mysql环境下使用show variables like "secure_file_priv";
查看一下这个变量的参数：
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
或者
mysql> SHOW VARIABLES LIKE "%secure%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | NULL |
+--------------------------+-------+
如果secure_file_priv 的 value 是 null 说明导入导出被禁用
找到mysql安装包下的my.ini或者my.cnf文件
使用文本编辑器打开这个文件，然后在[mysqld]这个选项下加入变量声明：
secure_file_priv=''
group by 优化关闭分组自动排序 order by null
select * from t1 group by uid order by null;
myisam 写锁定
lock table t1 write;
解锁：unlock tables;
myisam 读锁定(做数据备份时可使用)
lock table 1 read;
解锁：
unlock tables;
mysql 字符集(配置文件/etc/my.cnf)
[client]
#客户端字符集和连接字符集编码
default-character-set=utf8
[mysqld]
#服务器字符集、数据库字符集、和继承的表字符集
character-set-server=utf8
#校验字符集
collation-server=utf8_general_ci
vi /ect/my.cnf 进行修改配置文件
log-bin=mysql-bin 表示已开启bin-log日志
查看 bin-log 日志：
mysql> show binary logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 2722 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 1073741974 |
| mysql-bin.000005 | 1073741991 |
| mysql-bin.000006 | 423662363 |
| mysql-bin.000007 | 177 |
| mysql-bin.000008 | 177 |
| mysql-bin.000009 | 177 |
| mysql-bin.000010 | 177 |
| mysql-bin.000011 | 177 |
| mysql-bin.000012 | 433856490 |
| mysql-bin.000013 | 177 |
| mysql-bin.000014 | 154 |
+------------------+------------+
查看慢查询是否开启：slow_query_log
show variables like "%slow%";
+---------------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |
+---------------------------+-----------------------------------------+
查看慢查询时间：long_query_time
show variables like "%long%";
+----------------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------------+----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+----------+
开启慢查询日志 log_slow
slow_query_log = ON #开启慢查询
long_query_time = 5 #设置慢查询时间
socket 文件被误删除无法登录问题
临时登录方法：
mysql -uroot -p123 --protocol tcp -hlocalhost
永久方法
重启 MySQL 会自动重新生成一个 mysql.sock 文件
root 密码丢失
1.停止MySQL服务
#跳过授权表mysql.user和mysql.db这些表
2.mysql_safe --skip-grant-tables -user=mysql &
3.mysql-uroot #直接登录
#修改密码
4.mysql>update mysql.user set password=password("123") where user='root' and host='localhost';
#以下语句也可以修改密码
5.mysql>set password for root@localhost=password("123");
一、Windows下开启Mysql慢查询详解
//show variables like '%quer%';查询是否开启了慢查询！！
第一步：修改my.ini(mysql配置文件)
在my.ini中加上下面两句话
log-slow-queries = “D:\wamp\mysql_slow_query.log”
long_query_time=5
第一句使用来定义慢查询日志的路径（因为是windows，所以不牵涉权限问题）
第二句使用来定义查过多少秒的查询算是慢查询，我这里定义的是5秒
第二步：查看关于慢查询的状态
执行如下SQL语句来查看mysql慢查询的状态
show variables like '%slow%';
执行结果会把是否开启慢查询、慢查询的秒数、慢查询日志等信息打印在屏幕上。
第三步：执行一次慢查询操作
其实想要执行一次有实际意义的慢查询比较困难，因为在自己测试的时候，就算查询有20万条数据的海量表，也只需要0.几秒。我们可以通过如下语句代替：
SELECT SLEEP(10);
第四步：查看慢查询的数量
通过如下sql语句，来查看一共执行过几次慢查询：
show global status like '%slow%';
另外：
步骤:
1. 要这样启动mysql
进入到 mysql安装目录
2. 启动 xx>bin\mysqld.exe –slow-query-log 这点注意
测试 ,比如我们把
select * from emp where empno=34678 ；
***********
二、临时打开通用日志
mysql终端执行如下命令
|
1
|
SET GLOBAL general_log='on';
|
然后去mysql目录查找相应.log文件即可查看日志
查看是否打开日志和日志保存位置
|
1
|
show variables like '%general_log%';
|
感谢分享 有用
发表评论