三十而立

mysql下的文章

linux下开启mysql慢查询日志

修改mysql的配置文件my.cnf
执行 vi /etc/my.cnf

在[mysqld]里面加上以下内容

#开启慢日志
slow_query_log=TRUE          
#日志存放目录
slow_query_log_file=/home/wwwlogs/slow_query.log
#5秒记录值
long_query_time=5

如下图:
20170522152116543.jpg
然后重启 mysql /etc/init.d/mysql restart

登录mysql验证下 mysql-uroot -p

登录成功后执行 show variables like '%quer%';

看到下图就代表成功了:
2.jpg

显示出慢查询日志中最慢的10条sql

mysqldumpslow -t 10  /home/wwwlogs/slow_query.log  

附LNMP清理二进制mysql数据日志:
/usr/local/mysql/var/下面产生mysql-bin.0000* 类似的文件,而且一般都在几十MB到几个GB,会导致mysql无法启动或报错。

如何关闭MySQL的日志功能:

先删除日志:
先登录mysql:mysql -u root -p
输入密码登录成功后再执行:reset master; #注意别漏了后面的分号;
回车后会显示"Query OK, 234 rows affected (12.3 sec)",说明已经成功,再输入:quit; 退出mysql;

彻底禁用MySQL日志:
打开 vim /etc/my.cnf
找到这两行前面加上#注释掉

log-bin=mysql-bin
binlog_format=mixed

重启mysql /etc/init.d/mysql restart即可。

如果实在想保留日志,可以在/etc/my.cnf里[mysqld]部分中加入expire_logs_days = 10 #10天 然后重启mysql,这样10天就会自动清理日志。

方案参考:
https://blog.csdn.net/qq_27292113/article/details/72626722
https://www.vpser.net/manage/delete-mysql-mysql-bin-0000-logs.html

1、阿里云生成备份快照
2、旧的服务器扩容下数据盘(压缩资料超出存储不够,重要资料顺便本地备份下):https://help.aliyun.com/document_detail/25452.html?spm=a2c4g.11186623.4.3.360b14c5FLMEZt (注意执行第5步)
打包压缩: tar -zcvf wwwroot-bak.tgz /home/wwwroot

解压: `tar -zcvf wwwroot-bak.tgz`

3、新的服务器挂载数据盘

https://help.aliyun.com/document_detail/25426.html?spm=a2c4g.11186623.2.21.62667d56PCiG4i#concept-jl1-qzd-wdb

这次放弃了军哥的lnmp(使用近10年了),改用了宝塔(BT),更原因纯粹是懒,赞扬军哥的lnmp非常稳定,几年来从未出过问题。

4、先安装宝塔

yum install -y wget && wget -O install.sh http://download.bt.cn/install/install_6.0.sh && bash install.sh

中间跳出选择 y,安装完后记复制管理面板账号和密码

5、登录管理面板安装(nginx1.14,php7.2,mysql5.7),可以选择 编绎安装 30分钟到1小时左右

6、导出旧的mysql数据:

mysqldump -uroot -p --all-databases > sqlfile.sql //导出全部数据库,注意:这不是在mysql下
mysqldump -uroot -p dp_db > dumpout.sql   //导出某1个数据库,dp_db是需要导出的数据库名称,dumpout.sql存储导出

的数据

7、导入新的数据库:

mysql> create database abc;      # 创建数据库,abc是数据库名
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc.sql      # 导入备份数据库

mysql时间戳转换

//转换并写入:把时间戳转为可阅读时间(年-月-日 时:分:秒)格式
//wp_post是表名,addtime是时间字段
UPDATE wp_post set addtime=FROM_UNIXTIME(addtime,'%Y-%m-%d %H:%i:%s');

//wp_post是表名,addtime是时间字段
//查询转换入:把时间戳转为可阅读时间(年-月-日 时:分:秒)格式
select FROM_UNIXTIME(addtime,'%Y年%m月%d') from wp_post;

mysql时间加减,通常用于时区转换

//给时间加1小时语法,wp_post是表名,addtime是时间
UPDATE wp_post set addtime=DATE_ADD(addtime,interval 1 hour);
//减去1小时
UPDATE wp_post set addtime=DATE_sub(addtime,interval 1 hour);

select date_add(@dt, interval 1 day);   - 加1天  
select date_add(@dt, interval 1 hour);   -加1小时  
select date_add(@dt, interval 1 minute);    - 加1分钟  
select date_add(@dt, interval 1 second); -加1秒  
select date_add(@dt, interval 1 microsecond);-加1毫秒  
select date_add(@dt, interval 1 week);-加1周  
select date_add(@dt, interval 1 month);-加1月  
select date_add(@dt, interval 1 quarter);-加1季  
select date_add(@dt, interval 1 year);-加1年  

最近在为客户做一个库存升级改造的项目,之前客户的数据管理全部是在Excel中操作,估计以前也是没有意识到数据量变大以后,工作会变得如此困难,基本上处于一个无法操作的程度了。于是我们将旧版本的Excel表格格式化以后,导入到新开发的MySQL数据库中发现一个比较有趣的问题:Excel导入MySQL日期为0000-00-00了。

这个问题客户肯定是不能接受的,其实我们已经事先在Excel表中将日期字段设定为日期类型,而且在MySQL中的数据结构也是DATE类型,原以为是不会出错的。经过思考,我们决定用一个低级但很实用的办法来解决这个问题。

第一步:重新格式化Excel日期这个字段。选中日期列→右键单击→设置单元格格式...→自定义→在日期前面加一个特殊字符,这里以a为例,如下图:
a.jpg
设置单元格格式
b.jpg
自定义单元格格式
c.jpg
设置好单元格格式

第二步:选中已经发生格式变化的日期数据,这个时候双击某一个单元格,可以发现那个a会消失,这是因为那个a只是一个格式,所以我们需要把它处理成真正的文本内容:复制并粘贴到记事本中,然后再从记事本中复制粘贴回来,并替换掉现在的内容。

第三步:在MySQL中把日期字段的字符类型修改为varchar(255),接下来就可以把处理后的Excel导入MySQL了。然后使用SQL语句,对已经成功导入但是含有一个a字母的数据进行修改,所使用的SQL语句为:update 表名 set 字段名=replace(字段名,´a´,´´);

第四步:在MySQL中,把日期字段的字符类型重新修改为DATE,然后,然后就大功告成了!

总的说来,这个方法真的没有什么技术含量,但是确确实实是非常实用的,当你在使用其他高科技方法出现错误或效率十分低下的时候,不妨试试我这个笨方法,不但有效,而且速度还很快!至于Excel要如何导入MySQL这里就不再赘述了,后面有时间再单独整理发文,感谢观看,转载请注明出处!

转载自:http://www.chuangluo.com/news_457.html

有时候经常会用到excel导入与导出数据,但对于mysql命令不熟悉的来说,用excel更为直接方便。

1、把linux系统下的时间改为友好阅读时间:

    
//TEXT是格式化,A2指表格位置
=TEXT((A2+8*3600)/86400+70*365+19,"yyyy-mm-dd HH:mm:ss")
//原A2表格中的时间:1473992001,转换后结果:2016-09-16 10:13:21

=TEXT(D2+TIME(8,0,0),"yyyy-mm-dd HH:mm:ss")
//给时间批量加8小时,D2代表单元格,(8,0,0)代表(小时,分,秒)
//执行结果为:2016-09-16 18:13:21

2、截取单元格内前面100个字
完整公式如下:=left(C2,100)
然后使用下拉——》复制 ——》选择性粘贴 ——》粘贴文本

3、2个单元格内容批量合并
完整公式:=A1&A2 //&输入为键盘的:shift+7
然后使用下拉——》复制 ——》选择性粘贴 ——》粘贴文本

4、2张表筛选合并数据
完整公式:点击空白单元格——>插入fx函数——>选择VLOOKUP
查找值:A2 /共同主键,比如:id
数据表:Sheet2!$A:$B //$A为匹配ID,$B为数据
列序数:2 //从第2栏开始
匹配条件:0 //表示模糊匹配
TIM截图20171224103457.jpg
参考:百度百科

一、同一张表中数据合并mysql中新增查询

SELECT sid, GROUP_CONCAT(filename SEPARATOR ';')
FROM pictures_copy
GROUP BY sid;

第1行:sid代表主字段名,filename代表要合并的字段名
第2行:pictures_copy代表数据表
第3行:sid代表主字段名

mysql中新增查询
简单来说,可以通过:

mysql> SELECT id , GROUP_CONCAT(name) name
    -> FROM a 
    -> GROUP BY id;

来获得这样的结果集:

+------+---------+
| id   | name    |
+------+---------+
| 1    | A,A,B,C |
| 2    | A,B     |
+------+---------+

当然也可以对其的分隔符进行修改:

mysql> SELECT id, GROUP_CONCAT(name SEPARATOR ';')
    -> FROM a
    -> GROUP BY id;

这将使分隔符改为" ; "

同样也可以对name列进行排序:

mysql> SELECT id, GROUP_CONCAT(name ORDER BY name DESC) name
    -> FROM a
    -> GROUP BY id;
+------+---------+
| id   | name    |
+------+---------+
| 1    | C,B,A,A |
| 2    | B,A     |
+------+---------+
2 rows in set (0.00 sec)

当然官方还给出了可以通过DISTINCT来排除冗余
用法如下:

mysql> SELECT id, GROUP_CONCAT(DISTINCT name) name
    -> FROM a
    -> GROUP BY id;
+------+-------+
| id   | name  |
+------+-------+
| 1    | A,B,C |
| 2    | A,B   |
+------+-------+
2 rows in set (0.00 sec)

SELECT sid, GROUP_CONCAT(filename SEPARATOR ';')
FROM pictures_copy
GROUP BY sid;

第1行:sid代表主字段名,filename代表要合并的字段名
第2行:pictures_copy代表数据表
第3行:sid代表主字段名

二、2张表合并

UPDATE subject,img  //subject 和 img分别为2张表
SET subject.img = img.img    /共同的img字段
WHERE  
subject.sid = img.sid;    /2个相加

有时候会忘记wordpress密码,通过mysql修改Wordpress密码,执行SQL命令:
进入到 phpmyadmin 或者 服务器mysql,随后执行如下sql语句:

update wp_users set user_pass=md5("123456") where user_login='admin';
//123456是你的Wordpress新密码,admin是管理员账号(可替换)。执行完了命令后,你就可以用密码123456来登录Wordpress了

mysql数据导入时报错:[Err] [Imp] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

mysql根据配置文件会限制server接受的数据包大小。
有时候大的插入和更新会被 max_allowed_packet 参数限制掉,导致失败。

解决方法:

//先查看目前配置
show VARIABLES like '%max_allowed_packet%';

如果显示的结果为:

max_allowed_packet | 1048576 | 

以上说明目前的配置是:1M

//查my.cnf文件在哪里
mysql --help | grep my.cnf

//一般linux在etc下,打开编辑
vi etc/my.cnf
//修改为20m
max_allowed_packet = 20M

//然后重启下mysql,应该就成功了
/etc/init.d/mysql restart 

如果是军哥的的lnmp用户,直接 lnmp mysql restart 即可

为了安全起见,lnmp 的大部分版本都是禁止远程连接mysql的

开启的话可以按如下步骤:
1、首先需要自己在phpmyadmin里添加一个用户 主机为 % 的任意主机(也可以编辑已有的用户)
2、并且iptables 里删除DROP 3306端口的规则

//查看防火墙的序号
iptables -L -n --line-numbers

比如要删除INPUT里序号为8的规则,执行:

//删除第8条
iptables -D INPUT 8

iptables教程:https://www.vpser.net/security/linux-iptables.html

如果以上设置都不行,如果你用的是(阿里云或者类似云)的服务器,那么要设置:安全组规则

入方向  自定义TCP  端口范围:3306/3306    授权对象:0.0.0.0/0