[Linux] mysql 복구 script

mysql이 자꾸 죽으면서 워드프레스가 DB연결을 못하고 자꾸 에러를 띄운다.

InnoDB memory pool size가 기본으로 128M로 되어 있는데, 이걸 할당을 못하고 죽는 것 같다.

64M로 줄이자.

# vi /etc/mysql/conf.d/innodb.cnf
[mysqld]
innodb_buffer_pool_size = 64M

 

죽으면 사실 그냥 service mysql restart 해주면 되는데, 요거도 귀찮으니까 돌려서 죽으면 실행하게 스크립트를 만들자

# vi mysqlmon.sh
#!/bin/bash

linecount=`ps -aux | grep /usr/sbin/mysqld | wc -l`

if [ "$linecount" = "2" ]; then
        echo "`date`: Working well" >> /var/log/mysqlmon/mysqlmon.log;
else
        echo "`date`: Trying to recover..." >> /var/log/mysqlmon/mysqlmon.err;
        service mysql restart;
        echo "`date`: Done" >> //var/log/mysqlmon/mysqlmon.err;
fi

 

crontab에 등록하자

# crontab -e
*/5 * * * * /root/mysqlmon.sh

 

log파일을 관리하도록 [Linux] logrotate 설정 글의 예제처럼 logrotate를 설정하자.

[MySQL] Encoding 관련 Parameters

* Client characterset, Server characterset

mysql> status

————–

mysql  Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1

Connection id:          72

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ”

Using delimiter:        ;

Server version:         5.1.41-3ubuntu12.10 (Ubuntu)

Protocol version:       10

Connection:             Localhost via UNIX socket

Client characterset:    latin1

Server characterset:    latin1

UNIX socket:            /var/run/mysqld/mysqld.sock

Uptime:                 39 sec

Threads: 1  Questions: 259  Slow queries: 0  Opens: 231  Flush tables: 1  Open tables: 56  Queries per second avg: 6.641

————–

mysql> 

* /etc/mysql/my.cnf [client]

[mysqld]
default-character-set = utf8

 

mysql> show variables like ‘%char%’;

+————————–+—————————-+

| Variable_name            | Value                      |

+————————–+—————————-+

| character_set_client     | latin1                     |

| character_set_connection | latin1                     |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | latin1                     |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+————————–+—————————-+

8 rows in set (0.00 sec)

mysql>

* Database

mysql> ALTER DATABASE reviewdb DEFAULT charset=utf8;

* Table 및 Column

mysql> ALTER TABLE `reviewdb`.`patch_comments` MODIFY COLUMN `message` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

mysql> ALTER TABLE `reviewdb`.`change_messages` MODIFY COLUMN `message` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

mysql> ALTER TABLE `reviewdb`.`account_groups` MODIFY COLUMN `name` VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

mysql> ALTER TABLE `reviewdb`.`account_group_names` MODIFY COLUMN `name` VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

mysql> ALTER TABLE `reviewdb`.`accounts` MODIFY COLUMN `full_name` VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

mysql> ALTER TABLE `reviewdb`.`accounts` MODIFY COLUMN `preferred_email` VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

mysql> ALTER TABLE `reviewdb`.`account_external_ids` MODIFY COLUMN `email_address` VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

mysql> ALTER TABLE `reviewdb`.`account_groups` MODIFY COLUMN `description` TEXT  CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

mysql> ALTER TABLE `reviewdb`.`projects` MODIFY COLUMN `description` TEXT  CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

mysql> ALTER TABLE `reviewdb`.`projects` MODIFY COLUMN `name` VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;