分类 MySQL 下的文章

[Warning] Aborted connection 162746 to db: (Got an error reading communication packets)

这几天发现VPS上的mysql数据库告警日志里报了很多如下错误:

2017-04-10 05:37:49 14557 [Warning] Aborted connection 162746 to db: 'bigbiglife' user: 'bigbiglife' host: 'localhost' (Got an error reading communication packets)

虽然不是大问题,但是对于处女座的我,不能忍!

我这个问题可能是PHP连接MySQL后超过wait_timeout设置的时间限制被kill后造成的。
详情可以看https://dev.mysql.com/doc/refman/5.7/en/communication-errors.html

- 阅读剩余部分 -

MySQL之log_timestamps

今天有同学在群里问MySQL的error log里记录的时间怎么和当前系统时间不一致,日志里的时间要比当前晚了好些。我以前也遇到过这类问题,是在crontab这个地方遇到的,一直也没搞明白怎么回事,最后重装系统,重新ntp同步后,发现时间又正常了。最初查询google还以为是rsyslog程序的问题。

后来经群里另一个同学提示,发现MySQL 5.7.2以后的版本新增了一个log_timestamps参数,原来问题就出在这里。在MySQL官方文档里有下面一段话:

Error Log Message Format

As of MySQL 5.7.2, the log_timestamps system variable controls the timestamp time zone of messages written to the error log (as well as to general query log and slow query log files). Permitted values are UTC (the default) and SYSTEM (local system time zone). Before MySQL 5.7.2, messages use the local system time zone.

- 阅读剩余部分 -

MySQLSRV起停脚本

分享一个自己写mysql起停脚本,方便测试环境多实例其他操作

[root@node2 ~]# cat /sbin/mysqlsrv 
#!/bin/bash

mysql_dir=/usr/local/mysql/
mysql_dat=/data
if [ "$2" = "" ] 
then
        port=3306
else
        port="$2"
fi

function mysql_process_cnt()
(ps -ef|grep -v "grep"|grep  mysql$port|wc -l)

function mysql_start()
(/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my$port.cnf &)

function mysql_kill()
(
#ps -ef|grep mysql|grep $port|awk -F' ' '{print $2}'|xargs kill -9
mysqladmin  -uroot -pshunzi -S /data/mysql$port/tmp/mysql$port.sock shutdown
)

function mysql_stat()
(mysqladmin  -uroot -pshunzi -S /data/mysql$port/tmp/mysql$port.sock  -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}')

case $1 in 
        start)
        if [ `mysql_process_cnt` -gt  0 ]
                then
                        echo 'Mysql Server is running!'
        else
                        mysql_start
                        echo 'Begin startup mysql...'
                        sleep 5;
                        if [ `mysql_process_cnt` -gt  0 ]
                        then
                                echo 'Mysql Server is startup!'
                        else
                                echo 'Mysql Server is startup error!'
                        fi
        fi
;;
        stop)
        if [ `mysql_process_cnt` -gt 0 ]
                then 
                        mysql_kill
                        echo 'Begin stop mysql...'
                        sleep 5;
                        if [ `mysql_process_cnt` -gt 0 ]
                        then
                                echo 'Mysql Server is stop error!'
                        else
                                echo 'Mysql Server is stop ok!'
                        fi
        else
                        echo 'Mysql Server is not run!'
        fi
;;
        cnt)
        echo `mysql_process_cnt` "$2" mysql$port
;;
        mysql)
        mysql -uroot -pshunzi -S /data/mysql$port/tmp/mysql$port.sock
;;
        stat)
        mysql_stat
;;
        *)
        echo "###  Usage:  ### default port 3306"
        echo "==================================================="
        echo "mysqlsrv start port #start the port's mysql server!"
        echo "mysqlsrv stop port  #stop  the port's mysql server!"
        echo "mysqlsrv cnt port   #view  the port's process info!"
        echo "mysqlsrv stat port  #view  the port's server status!"
        echo "==================================================="
;;
esac
``

MySQL 5.7之主从复制搭建

MySQL5.7版本发版已经好久了,相对也比较稳定了,刚好今天搭好虚拟机环境,下面就来搭一套基于MySQL5.7版本的主从复制吧。

复制过程

mysql-replication.png

  1. master将变化的binary log events记录到binary log
  2. slavemasterbinary log events拷贝到它的relay log中
  3. slave把relay log events重新应用到数据库。

准备环境

- 阅读剩余部分 -

Title - Artist
0:00