Mysql

Summary: Author: 张亚飞 | Read Time: 3 minute read | Published: 2016-05-11
Filed under — Categories: Linux — Tags: Note,

Mysql 时区文件

老生常谈！数据库如何存储时间？你真的知道吗？

一些关于 MySQL 时区设置的一个常用 sql 命令

# 查看当前会话时区
SELECT @@session.time_zone;
# 设置当前会话时区
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
# 数据库全局时区设置
SELECT @@global.time_zone;
# 设置全局时区
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
  • 刚安装完 php5-fpm mysql 使用 phpMyAdmin 登陆提示如下错误
#2002 - Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) — The server is not responding (or the local server's socket is not correctly configured).
  • 因为使用源码编译安装的 mysql 5.7 的默认配置文件 /etc/mysql/my.cnf 没有 [client] 这一段配置.而默认的 mysql sock 位置为 /var/run/mysqld/mysqld.sock
  1. 首先找到所有的 socket 文件:
coam@coam:~$ sudo find / -type s
/tmp/mongodb-27017.sock
/var/run/mysqld/mysqld.sock
/run/fail2ban/fail2ban.sock
...

所以手动 /etc/mysql/my.cnf 添加这一行:

* [client]
socket=/var/run/mysqld/mysqld.sock <= this path should be also same as is[mysqld]
  • 重启 mysql
sudo service mysql restart
  • 最简单的方法是建立软连接 /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock
sudo ln -s /var/run/mysqld/mysqld.sock /var/run/mysqld/mysqld.sock
  • 据说以下也可以解决此问题的一个途径,记录一下
/usr/bin/mysql -u root -p -S /home/mysql/mysql.sock

参考列表

vi /etc/php5/fpm/php.ini

* [Pdo_mysql]
...
pdo_mysql.default_socket = /var/run/mysqld/mysqld.sock
...

* [MySQL]
...
#php5.6#mysql.default_socket = /var/run/mysqld/mysqld.sock
...

* [MySQLi]
...
mysqli.default_socket = /var/run/mysqld/mysqld.sock
...
  • 重启 php5-fpm 生效

注 没有修改 /etc/php5/cli/php.ini

Mysql 误操作恢复

前提是已经开启并使用 mysql-log-bin 来恢复

  • 查找并输出 [2016-07-18 21:55:00 -> 2016-07-18 22:30:00] 的操作日志
mysqlbinlog --start-datetime="2016-07-18 21:55:00" --stop-datetime="2016-07-18 22:30:00" mysql-bin.000021 > temp/002.txt

Mon Jul 18 22:44:39 coam@coam:/data/home/data/mysql$ sudo chmod -R 777 mysql-bin.000021
Mon Jul 18 22:45:11 coam@coam:/data/home/data/mysql$ ls
auto.cnf         coam.err    ib_buffer_pool  master.info       mysql-bin.000021         private_key.pem  Wordpress
ca-key.pem       CoamInfo    ibdata1         mysql             mysql-bin.index          public_key.pem   www_8875555
ca.pem           coam.pid    ib_logfile0     mysql-bin.000017  mysqld-relay-bin.000009  relay-log.info
client-cert.pem  CoamSNS     ib_logfile1     mysql-bin.000018  mysqld-relay-bin.000010  server-cert.pem
client-key.pem   CommonData  ibtmp1          mysql-bin.000019  mysqld-relay-bin.index   server-key.pem
Coam_Data     forum       IM_Ejabberd     mysql-bin.000020  performance_schema       sys

可以看到最新的是 [mysql-bin.000021] 日志文件,一般最后一次误操作是最新的 mysql-bin-log ,也可以直接通过 vi 来查看 [mysql-bin.000021] 记录的内容

BEGIN
/*!*/;
# at 8780303
#160718 21:57:45 server id 2  end_log_pos 8780926 CRC32 0xb6cf7eac  Query   thread_id=4643  exec_time=0 error_code=0
use `CommonData`/*!*/;
SET TIMESTAMP=1468850265/*!*/;
UPDATE `Hospital_MedicalCategoryInfo` SET `mainCategory` = 'womenFKZX', `subCategory` = 'medicalYDJinS', `postTitle` = '阴道紧缩术的注意事项有哪些', `postIntroduce` = '阴道紧缩术虽然是小手术,但是在女性在做阴道紧缩术之前和之后,都需要留>意一些注意和预防的事,这样对阴道紧缩术的最后成功具有很大的帮助', `postIntroduceImage` = 'MedicalIntroduceImage.jpg', `postName` = '202481', `viewTimes` = 1, `upTime` = '2016-07-09 22:11:28' WHERE `id` = '25922'
/*!*/;
# at 8780926
#160718 21:57:45 server id 2  end_log_pos 8780957 CRC32 0x5466ad6a  Xid = 56133
COMMIT/*!*/;
# at 8780957
#160718 21:59:02 server id 2  end_log_pos 8781022 CRC32 0x6d5887a5  Anonymous_GTID  last_committed=9568 sequence_number=9569
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 8781022
#160718 21:59:02 server id 2  end_log_pos 8781126 CRC32 0xe9291b2e  Query   thread_id=4543  exec_time=0 error_code=0
SET TIMESTAMP=1468850342/*!*/;
BEGIN
/*!*/;
# at 8781126
#160718 21:59:02 server id 2  end_log_pos 8781711 CRC32 0x5f750b86  Query   thread_id=4543  exec_time=0 error_code=0
SET TIMESTAMP=1468850342/*!*/;
UPDATE `Hospital_MedicalCategoryInfo` SET `mainCategory` = 'womenWTLC', `subCategory` = 'women', `postTitle` = '无痛人流的检查要注意哪些?', `postIntroduce` = '无痛人流是诸多女性比较关注的问题,术前检查是很多女性非常重视的问题,那么无痛人
流的检查要注意哪些?针对这个问题', `postIntroduceImage` = '55d58d639c92c_yygk_zl_p11.jpg', `postName` = '200005', `viewTimes` = '371', `upTime` = '2015-08-19 12:40:35' WHERE `id` = '1'
/*!*/;
# at 8781711
#160718 21:59:02 server id 2  end_log_pos 8781742 CRC32 0x296424ea  Xid = 56138
COMMIT/*!*/;
# at 8781742
#160718 21:59:02 server id 2  end_log_pos 8781807 CRC32 0x7d80a362  Anonymous_GTID  last_committed=9569 sequence_number=9570
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 8781807
#160718 21:59:02 server id 2  end_log_pos 8781911 CRC32 0xaf0e98e4  Query   thread_id=4543  exec_time=0 error_code=0
SET TIMESTAMP=1468850342/*!*/;
  • 恢复到 stop-position=“8781126” 以前的数据
#mysqlbinlog --stop-position="8781126" mysql-bin.000021 | mysql -uroot -proot
mysqlbinlog --stop-position="8781126" mysql-bin.000021

MySQL死锁等待与超时事务回滚

mysql> select @@innodb_rollback_on_timeout;
+------------------------------+
| @@innodb_rollback_on_timeout |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set


mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
1 row in set

Comments

Cor-Ethan, the beverage → www.iirii.com