160924 20:24:17 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:24:17 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:24:57 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:24:57 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:24:58 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:24:58 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:09 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:09 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:23 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:23 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:24 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:24 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:29 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:29 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:40 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:40 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

160924 20:25:42 [ERROR] /usr/libexec/mysqld: Table './Zblog/zbp_post' is marked as crashed and should be repaired

[root@VM_132_47_centos ~]# myisamchk -c -r /var/lib/mysql/Zblog/zbp_post.MYI

- recovering (with sort) MyISAM-table '/var/lib/mysql/Zblog/zbp_post.MYI'

Data records: 77

- Fixing index 1

- Fixing index 2

一、MySQL 服务宕了​

$ mysql -uroot -p -e "show global status like 'uptime';"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Uptime        | 68928 |

+---------------+-------+

1 row in set (0.04 sec)

$ tail /var/log/mysql/error.log

130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M

130101 22:22:30 InnoDB: Completed initialization of buffer pool

130101 22:22:30 InnoDB: highest supported file format is Barracuda.

130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509

130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306

130101 22:22:30 [Note]   - '127.0.0.1' resolves to '127.0.0.1';

130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'.

130101 22:22:30 [Note] Event Scheduler: Loaded 0 events

130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.5.28-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

二、连接超时

$ mysql -uroot -p -e "show global variables like '%timeout';"

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| connect_timeout            | 30       |

| delayed_insert_timeout     | 300      |

| innodb_lock_wait_timeout   | 50       |

| innodb_rollback_on_timeout | OFF      |

| interactive_timeout        | 28800    |

| lock_wait_timeout          | 31536000 |

| net_read_timeout           | 30       |

| net_write_timeout          | 60       |

| slave_net_timeout          | 3600     |

| wait_timeout               | 28800    |

+----------------------------+----------+

mysql> SET SESSION wait_timeout=5;





# Wait 10 seconds



mysql> SELECT NOW();

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    132361

Current database: *** NONE ***



+---------------------+

| NOW()               |

+---------------------+

| 2013-01-02 11:31:15 |

+---------------------+

1 row in set (0.00 sec)

三、进程在server端被主动kill

$ mysql -uroot -p -e "show global status like 'com_kill'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_kill      | 0     |

+---------------+-------+

四、Your SQL statement was too large.

mysql> show global variables like 'max_allowed_packet';

+--------------------+---------+

| Variable_name      | Value   |

+--------------------+---------+

| max_allowed_packet | 1048576 |

+--------------------+---------+

1 row in set (0.00 sec)



# 修改参数：



mysql> set global max_allowed_packet=1024*1024*16;

mysql> show global variables like 'max_allowed_packet';

+--------------------+----------+

| Variable_name      | Value    |

+--------------------+----------+

| max_allowed_packet | 16777216 |

+--------------------+----------+

1 row in set (0.00 sec)

一、什么是SlowLog

二、SlowLog设置

slowlog-log-slower-than 10000

slowlog-max-len 128

- 查看当前slowlog-log-slower-than设置

    127.0.0.1:6379> CONFIG GET slowlog-log-slower-than

    1) "slowlog-log-slower-than"

    2) "10000"

- 设置slowlog-log-slower-than为100ms

    127.0.0.1:6379> CONFIG SET slowlog-log-slower-than 100000

    OK

- 设置slowlog-max-len为1000

    127.0.0.1:6379> CONFIG SET slowlog-max-len 1000

    OK

三、SlowLog 查看

127.0.0.1:6379> SLOWLOG LEN 

(integer) 4

127.0.0.1:6379> SLOWLOG GET

1) 1) (integer) 25

   2) (integer) 1440057769

   3) (integer) 6

   4) 1) "SLOWLOG"

      2) "LEN"

2) 1) (integer) 24

   2) (integer) 1440057756

   3) (integer) 36

   4) 1) "CONFIG"

      2) "GET"

      3) "slowlog-log-slower-than"

3) 1) (integer) 23

   2) (integer) 1440057752

   3) (integer) 11

   4) 1) "CONFIG"

      2) "SET"

      3) "slowlog-log-slower-than"

      4) "1"

4) 1) (integer) 22

   2) (integer) 1440057493

   3) (integer) 27

   4) 1) "CONFIG"

      2) "GET"

      3) "slowlog-log-slower-than"

5) 1) (integer) 21

   2) (integer) 1440057133

   3) (integer) 7

   4) 1) "monitor"

127.0.0.1:6379> SLOWLOG GET 1

1) 1) (integer) 26            // slowlog唯一编号id

   2) (integer) 1440057815    // 查询的时间戳

   3) (integer) 47            // 查询的耗时（微秒），如表示本条命令查询耗时47微秒

   4) 1) "SLOWLOG"            // 查询命令，完整命令为 SLOWLOG GET，slowlog最多保存前面的31个key和128字符

      2) "GET"

select * from db_main.cluster_user into outfile '/sql/cluster_user.sql'

ERROR 1 (HY000): Can't create/write to file '/sql/cluster_user.sql' (Errcode: 13)

[]安装Haproxy（你可以安装到独立的服务器或者应用服务器）[/][]配置集群监控检测脚本，用于检测每个backend server服务器健康[/][]配置Haproxy统计页面[/][]将应用程序指向Haproxy[/]

$ git clone https://github.com/olafz/percona-clustercheck

$ cp percona-clustercheck/clustercheck /usr/local/bin

# default: on

# description: mysqlchk

service mysqlchk

{

  disable = no

  flags = REUSE

  socket_type = stream

  port = 9200

  wait = no

  user = nobody

  server = /usr/local/bin/clustercheck

  log_on_failure += USERID

  only_from = 0.0.0.0/0

  per_source = UNLIMITED

}

echo 'mysqlchk      9200/tcp    # MySQL check' >> /etc/services

mysql> GRANT PROCESS ON [i].[/i] TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';

mysql> FLUSH PRIVILEGES;

$ /usr/local/bin/clustercheck > /dev/null

$ echo $?

0

$ yum install haproxy # Redhat/CentOS

$ sudo apt-get install haproxy # Debian/Ubuntu

$ yum install php-curl gcc make # Redhat/CentOS

$ apt-get install php5-curl gcc make # Debian/Ubuntu

$ wget http://www.haproxy.org/download/1.5/src/

$ tar xvzfz

$ cd

$ make TARGET=linux26

$ cp -f haproxy /usr/sbin/haproxy

global

        pidfile /var/run/haproxy.pid

        daemon

        user haproxy

        group haproxy

        stats socket /var/run/haproxy.socket user haproxy group haproxy mode 600 level admin

 

        maxconn 8192

        spread-checks 3

        quiet

defaults

        mode    tcp

        option  dontlognull

        option tcp-smart-accept

        option tcp-smart-connect

        retries 3

        option redispatch

        maxconn 8192

        timeout check   3500ms

        timeout queue   3500ms

        timeout connect 3500ms

        timeout client  10800s

        timeout server  10800s

 

userlist STATSUSERS

        group admin users admin

        user admin insecure-password admin

        user stats insecure-password yourpassword

 

listen admin_page 0.0.0.0:9600

        mode http

        stats enable

        stats refresh 60s

        stats uri /

        acl AuthOkay_ReadOnly http_auth(STATSUSERS)

        acl AuthOkay_Admin http_auth_group(STATSUSERS) admin

        stats http-request auth realm admin_page unless AuthOkay_ReadOnly

 

listen  mysql_3307

        bind *:3307

        mode tcp

        timeout client  10800s

        timeout server  10800s

        balance leastconn

        option httpchk

        option allbackups

        default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100

        server db1 10.0.0.187:3306 check

        server db2 10.0.0.188:3306 check

        server db3 10.0.0.189:3306 check

$ chkconfig haproxy on # RHEL6

$ service haproxy start # RHEL6

$ update-rc.d haproxy defaults

$ sudo service haproxy start

$ systemctl enable haproxy

$ systemctl start haproxy

sudo netstat -tulpn | grep haproxy

tcp        0      0 0.0.0.0:9600            0.0.0.0:*               LISTEN      370/haproxy

tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      370/haproxy

Server1 IP=192.168.1.1
Server2 IP=192.168.1.2
Database name=db1
Replication user=replicator
Replication user password=slavepass

[mysqld]

server-id=100

log-bin=master-bin

service mysqld restart

mysql> GRANT REPLICATION SLAVE on [i].[/i] to 'replicator'@'%' IDENTIFIED BY 'slavepass';

mysqldump -u[root] -p[password] --master-data --single-transaction db1 > db1.sql

[mysqld]

server-id=101

replicate-wild-do-table=db1.%

service mysqld restart

mysql> CREATE DATABASE db1;

mysql> USE db1;

mysql> SOURCE /root/db1.sql;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='slavepass';

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

***********************[i][b][/i] 1. row [/b]*************************

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[]利用mysql复制协议，从主机获得复制数据。[/][]复制进程负责按事物写进共享内存。[/][]管理进程负责根据sql语句，解析出数据库和表名，回写共享内存。并按事物派生一个执行线程。[/][]执行线程在执行sql语句前，搜索当前共享内存中之前的语句是否有同库同表的语句，有就等待，没有就执行。[/]

[]数据的准确性，需要长期的运行观察，通过对帐来检查。[/][]数据的安全性，在发生灾难的时候，如何确保数据操作信息被正确保存并且在灾难消除后可以快速继续复制。[/][]易用性，需要提供和mysql复制功能的同样的操作。[/]

[]轻量级C语言写的[/]

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

This time difference computation works even if the master and slave do not have identical clock times, provided that the difference,

computed when the slave I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock

skews that can make calculation of Seconds_Behind_Master less reliable

[]SQL thread没运行[/][]IO thread没运行[/][]slave没有连接到master。[/]

mysql> SHOW SLAVE STATUS \G

***********************[i][b][/i] 1. row [/b]*************************

               Slave_IO_State: Queueing master event to the relay log

                  Master_Host: masterIP

                  Master_User: replUser

                  Master_Port: masterPort

                Connect_Retry: 60

              Master_Log_File: binlog.000296

          Read_Master_Log_Pos: 364027786

               Relay_Log_File: relaylog.000002

                Relay_Log_Pos: 250

        Relay_Master_Log_File: binlog.000283

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 594374863

              Relay_Log_Space: 13803486573

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 256219

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: masterID

1 row in set (0.00 sec)

mysql> SHOW FULL PROCESSLIST;

+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+

| Id     | User        | Host             | db                 | Command | Time    | State           | Info                  |

+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+

|  51378 | system user |   | NULL | Connect | 1121183 | Waiting for master to send event | NULL   |

|  88917 | system user |   | NULL | Connect |  245327 | Reading event from the relay log | NULL    |

| 106029 | userA     | xxx.xxx.xxx.xxx:14057 | NULL               | Sleep   |   14504 |   | NULL          |

| 106109 | userA     | xxx.xxx.xxx.xxx:15077 | databaseA | Sleep   |      79 |     | NULL                  |

| 106110 | userA     | xxx.xxx.xxx.xxx:15081 | databaseA | Sleep   |   13000 |   | NULL                  |

| 106116 | userB    | xxx.xxx.xxx.xxx:15096 | databaseA | Sleep   |     357 |  | NULL                  |

| 106117 | userB    | xxx.xxx.xxx.xxx:15097 | NULL               | Sleep   |   12964 |     | NULL         |

| 106119 | root        | localhost    | NULL   | Query   |    0 | NULL     | SHOW FULL PROCESSLIST |

| 106126 | userB    | xxx.xxx.xxx.xxx:15173 | NULL               | Sleep   |   12856 |      | NULL      |

| 106127 | userB    | xxx.xxx.xxx.xxx:15180 | databaseA | Sleep   |   12849 |    | NULL                  |

| 106766 | userA     | xxx.xxx.xxx.xxx:17960 | databaseA | Sleep   |      64 |       | NULL                |

+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+

11 rows in set (0.00 sec)

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \

binlog.000283 > /XXX/binlog.sql

/[i]!40019 SET @@session.max_insert_delayed_threads=0[/i]/;

/[i]!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0[/i]/;

DELIMITER /[i]![/i]/;

# at 4

#150814 17:43:15 server id 21  end_log_pos 107  Start: binlog v 4, server v x.x.xx-log created 150814 17:43:15

BINLOG '

M7jNVQ8VAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/[i]![/i]/;

# at 594374863

#150814 18:09:36 server id 21  end_log_pos 594374945    Query   thread_id=210702841 exec_time=43    error_code=0

SET TIMESTAMP=1439546976/[i]![/i]/;

SET @@session.pseudo_thread_id=210702841/[i]![/i]/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/[i]![/i]/;

SET @@session.sql_mode=0/[i]![/i]/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/[i]![/i]/;

/[i]!\C utf8 [/i]//[i]![/i]/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/[i]![/i]/;

SET @@session.lc_time_names=0/[i]![/i]/;

SET @@session.collation_database=DEFAULT/[i]![/i]/;

BEGIN

/[i]![/i]/;

# at 594374945

# at 594375036

# at 594376047

# at 594377085

# at 594378123

# at 594379152

# at 594380187

# at 594381165

# at 594382194

# at 594383223

# at 594384252

# at 594385269

# at 594386307

# at 594387282

# at 594388299

# at 594389265

# at 594390270

# at 594391299

# at 594392310

# at 594393327

# at 594394344

# at 594395340

# at 594396336

# at 594397332

mysql> SHOW VARIABLES LIKE '%expire%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| expire_logs_days | 3     |

+------------------+-------+

1 row in set (0.00 sec)



mysql> SET GLOBAL expire_logs_days=5;

Query OK, 0 rows affected (0.00 sec)



mysql> SHOW VARIABLES LIKE '%expire%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| expire_logs_days | 5     |

+------------------+-------+

1 row in set (0.00 sec)

vim /xxx/xxxx/xxx/my.cnf

replicate-ignore-table=databaseA.tableA

replicate-ignore-table=databaseA.tableB

replicate-ignore-table=databaseA.tableC

replicate-ignore-table=databaseA.tableD

/xxx/xxx/xxx/xxx/mysqld restart

mysql> SHOW SLAVE STATUS \G

***********************[i][b][/i] 1. row [/b]*************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: masterIP

                  Master_User: replUser

                  Master_Port: masterPort

                Connect_Retry: 60

              Master_Log_File: binlog.000319

          Read_Master_Log_Pos: 985656691

               Relay_Log_File: relaylog.000004

                Relay_Log_Pos: 709043542

        Relay_Master_Log_File: binlog.000284

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table: databaseA.tableA,databaseA.tableB,databaseA.tableC,databaseA.tableD

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 709043399

              Relay_Log_Space: 38579192969

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 258490

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: masterID

1 row in set (0.00 sec)

[]执行 LOAD DATA LOCAL INFILE；[/][]拷贝使用的整个文本文件内容到二进制日志；[/][]添加 LOAD DATA LOCAL INFILE 到最新的二进制日志。[/]

[]检查中继日志中的文本文件；[/][]从多个中继日志文件中读取所有的块；[/][]文本文件存放在 /tmp 文件夹中；[/][]从中继日志中读取 LOAD DATA LOCAL INFILE；[/][]在 SQL 线程中执行 LOAD DATA LOCAL INFILE。[/]