标签: MySQL

insert into new_table (id,pid,title,create_time) SELECT id,0,name,now() FROM `old_table` WHERE value='test'

标签: PHP MySQL

啥也别想，emlog不支持PHP7，回退吧...

标签: MySQL

修改/etc/my.cnf以下位置

[mysqld] 
character-set-server=utf8
collation-server=utf8_general_ci
[client] 
default-character-set=utf8

标签: MySQL

default-storage-engine=MYISAM

标签: MySQL

今天线上数据库异常挂了两次，查看日志发现致命错误Fatal error: cannot allocate memory for the buffer pool

修改/etc/my.cnf，适当降低innodb_buffer_pool_size参数值，从256M调整为128M

170602 17:51:27 mysqld_safe Number of processes running now: 0
170602 17:51:27 mysqld_safe mysqld restarted
170602 17:51:27 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.48-log) starting as process 3476 ...
170602 17:51:27 InnoDB: The InnoDB memory heap is disabled
170602 17:51:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170602 17:51:27 InnoDB: Compressed tables use zlib 1.2.7
170602 17:51:27 InnoDB: Initializing buffer pool, size = 256.0M
170602 17:51:27 InnoDB: Completed initialization of buffer pool
170602 17:51:27 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170602 17:51:27 [ERROR] Plugin 'InnoDB' init function returned error.
170602 17:51:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170602 17:51:27 [ERROR] Unknown/unsupported storage engine: InnoDB
170602 17:51:27 [ERROR] Aborting
170602 17:51:27 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170602 17:51:27 mysqld_safe mysqld from pid file /data/mysql/yuxinle.pid ended

贴上几个关键指标，供以后出现同类问题时参考

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 574   |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 8191  |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 3000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>  show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 4     |
+----------------------+-------+
1 row in set (0.00 sec)