mysqldump -u root -p 데이터베이스명 테이블명 --where="조건문" --no-create-info > test.sql                                 



--no-create-info 를 생략하면 백업시 테이블을 삭제하고 복구하니 주의하기 바람


'MySQL, MariaDB' 카테고리의 다른 글

MAMP mysql upgrade to 5.7.16 osx  (0) 2016.11.17
MySQL CAST  (0) 2016.05.12
order by field ()  (0) 2016.03.14
MySQL 테이블 사이즈 조회  (0) 2016.01.13
MySQL unauthenticated user login state 계속 발생시  (0) 2016.01.13
블로그 이미지

엘로드넷

,

order by field ()

MySQL, MariaDB 2016. 3. 14. 22:10

특정 필드의 값 순으로 정렬하고자 할 때;


필드1에서 값1, 값2, 값3 인 데이터를 추출하고, 정렬은 값2, 값3, 값1 순으로 정렬하는 방법


ex)

select * from test where 필드1 IN (값1, 값2, 값3) ORDER BY FIELD(필드1, 값2, 값3, 값1);



블로그 이미지

엘로드넷

,

1. 개별 테이블 조회


SELECT 

    table_name AS `Table`, 

    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

WHERE table_schema = "DB명"

    AND table_name = "테이블명";



2. 테이블 전체 조회


해당 디비 선택 후,


SELECT 

     table_schema as `Database`, 

     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

ORDER BY (data_length + index_length) DESC;




블로그 이미지

엘로드넷

,

/etc/resolv.conf 에 설정된 dns서버 원인


dns서버를 아래와 같이 변경


nameserver 168.126.63.1

nameserver 168.126.63.2





'MySQL, MariaDB' 카테고리의 다른 글

order by field ()  (0) 2016.03.14
MySQL 테이블 사이즈 조회  (0) 2016.01.13
too many connections, max_connections, wait_timeout  (0) 2016.01.13
MySQL 5.7.9 root 비밀번호 재설정  (1) 2015.12.02
MSQL Locked 확인 및 죽이기  (0) 2015.07.23
블로그 이미지

엘로드넷

,

MySQL too many connections 에러시



1. max_connectons 값 조절


mysql> show global variables like 'max%';

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

| Variable_name              | Value      |

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

| max_allowed_packet         | 1048576    |

| max_binlog_cache_size      | 4294963200 |

| max_binlog_size            | 104857600  |

| max_connect_errors         | 10         |

| max_connections            | 512        |

| max_delayed_threads        | 20         |

| max_error_count            | 64         |

| max_heap_table_size        | 16777216   |

| max_insert_delayed_threads | 20         |

| max_join_size              | 4294967295 |

| max_length_for_sort_data   | 1024       |

| max_long_data_size         | 1048576    |

| max_prepared_stmt_count    | 16382      |

| max_relay_log_size         | 0          |

| max_seeks_for_key          | 4294967295 |

| max_sort_length            | 1024       |

| max_sp_recursion_depth     | 0          |

| max_tmp_tables             | 32         |

| max_user_connections       | 0          |

| max_write_lock_count       | 4294967295 |

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

20 rows in set (0.00 sec)



512 로 되어 있는 것을 1024로 늘리자.



mysql> set @@global.max_connections = 1024;



적용되었는지 화인



mysql> show global variables like 'max%';

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

| Variable_name              | Value      |

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

| max_allowed_packet         | 1048576    |

| max_binlog_cache_size      | 4294963200 |

| max_binlog_size            | 104857600  |

| max_connect_errors         | 10         |

| max_connections            | 1024       |

| max_delayed_threads        | 20         |

| max_error_count            | 64         |

| max_heap_table_size        | 16777216   |

| max_insert_delayed_threads | 20         |

| max_join_size              | 4294967295 |

| max_length_for_sort_data   | 1024       |

| max_long_data_size         | 1048576    |

| max_prepared_stmt_count    | 16382      |

| max_relay_log_size         | 0          |

| max_seeks_for_key          | 4294967295 |

| max_sort_length            | 1024       |

| max_sp_recursion_depth     | 0          |

| max_tmp_tables             | 32         |

| max_user_connections       | 0          |

| max_write_lock_count       | 4294967295 |

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

20 rows in set (0.00 sec)


mysql> 


1024로 변경되어 있다.



2. wait_timeout 확인


mysql> show global variables like 'wait%';

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

| Variable_name | Value |

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

| wait_timeout  | 28800 |

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

1 row in set (0.00 sec)


mysql> 


28800 으로 되어 있는 것을 60으로 바꾸자


mysql> set @@global.wait_timeout = 60;



변경되었는지 확인

mysql> show global variables like 'wait%';

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

| Variable_name | Value |

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

| wait_timeout  |  60   |

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

1 row in set (0.00 sec)


mysql> 



변경되어 있다.




3. unauthenticated user 삭제


processlist 에서 unauthenticated user 사용자 리스트를 삭제하는 텍스트 파일로 저장하고


mysql> select concat('KILL ', ID, ';') from information_schema.processlist where user='unauthenticated user' and command='query'  into outfile '/tmp/kill_list.txt';



한번에 실행시킨다

mysql> \. /tmp/kill_list.txt   









블로그 이미지

엘로드넷

,

#killall mysqld

#mysqld_safe --skip-grant-tables --user=mysql &

#mysql -u root -p mysql

비밀번호 물을 때 그냥 엔터.


접속 후,


>UPDATE mysql.user SET authentication_string = PASSWORD('새로운비밀번호')
WHERE User = 'root' AND Host = 'localhost';
>flush privilegse;

>quit;

#killall mysqld




#mysqld_safe --user=mysql &

#mysql -u root -p mysql


password: 좀 전에 변경한 비밀번호로 입력



>ALTER USER 'root'@'localhost' IDENTIFIED BY '새로운비밀번호';

>FLUSH PRIVILEGES;

>quit;





블로그 이미지

엘로드넷

,

>show open tables;        //MySQL 전체 테이블 잠금여부

>show open tables from 'db명'        //해당 DB의 테이블 잠금 여부

>show open tables from 'db명' like '테이블명'    //해당 DB의 특정 테이블의 잠금 정보



결과에서


Database    Table        in_use        Name_locked


DB명        테이블명    사용자수    네임락 수



>show processlist;


>kill 아이디;                //해당 클라이언트 종료

>kill query 아이디;        //해당 쿼리 종료


'MySQL, MariaDB' 카테고리의 다른 글

too many connections, max_connections, wait_timeout  (0) 2016.01.13
MySQL 5.7.9 root 비밀번호 재설정  (1) 2015.12.02
MySQL 깨진테이블 복구  (0) 2015.07.13
MySQL 계정 만들기  (0) 2015.04.18
MySQL Innodb backup  (0) 2015.04.17
블로그 이미지

엘로드넷

,

## 데이터베이스 복구

- 복구 유틸리티

1. 복구대상 인덱스 테이블이 *.ISM 파일 형식이면 isamchk 사용
2. 복구대상 인덱스 테이블이 *.MYI 파일 형식이면 myisamchk 사용

해당 유틸리티 사용시에는 mysql 종료후 사용할것

#ps -ef | grep mysqld
#kill -9 프로세서 번호


- 복구시 사용되는 데이터베이스 관련 파일 위치 확인

1. /var/lib/mysql/var/db명/테이블명.MYI 

2. 각 테이블 하나에 3개의 파일이 생성되어 해당 테이블의 데이터가 실제로 저장

*.MYD : 실제 데이터가 저장되는 데이터파일
*.MYI : 테이블의 인덱스 정보를 저장하는 인덱스 파일
*.frm : 테이블의 구조가 저장되는 스키마파일


- 이상유무 점검 방법

# myisamchk -s /var/lib/mysql/test.MYI //점검결과를 간략히 출력
# myisamchk -v /var/lib/mysql/test.MYI //점검결과를 상세히 출력
# myisamchk -i /var/lib/mysql/test.MYI //점검결과를 상세히 종합하여 출력

- 깨진 테이블 복구하기

# myisamchk -rv /var/lib/mysql/test.MYI //
# myisamchk -o /var/lib/mysql/test.MYI // r옵션보다는 안전모드로 복구, 속도는 느리다
r옵션으로 복구하지 못하는 내용도 복구 가능

# myisamchk -er 또는 -eo /var/lib/mysql/test.MYI // 테이블이 완전히 깨진 최후의 복구방법


- myisamchk 로 복구되지 않을 경우

1. frm 파일로 인해 복구가 되지 않는 경우

- MYI, MYD 파일의 원본 백업
- mysql 접속후 create 문을 이용하여 테이블 생성
- myisamchk 를 통해 복구 시도

2. MYI 파일로 인해 복구가 되지 않는 경우

- MYD, frm 파일의 원본을 백업
- mysql 접속후 delete 구문을 통해 해당 테이블의 모든 레코드를 삭제
- mysql 종료후 백업한 MYD, frm 파일을 원위치로 복사 후 myisamchk 시도


3. 복구 후 mysqld 새로 시작

#/usr/local/mysql/bin/mysqld_safe &

'MySQL, MariaDB' 카테고리의 다른 글

too many connections, max_connections, wait_timeout  (0) 2016.01.13
MySQL 5.7.9 root 비밀번호 재설정  (1) 2015.12.02
MSQL Locked 확인 및 죽이기  (0) 2015.07.23
MySQL 계정 만들기  (0) 2015.04.18
MySQL Innodb backup  (0) 2015.04.17
블로그 이미지

엘로드넷

,

MySQL 계정만들기


#mysql>grant all privileges on 디비명.* to '유저명'@'%' identified by '비밀번호' with grant option;


'MySQL, MariaDB' 카테고리의 다른 글

too many connections, max_connections, wait_timeout  (0) 2016.01.13
MySQL 5.7.9 root 비밀번호 재설정  (1) 2015.12.02
MSQL Locked 확인 및 죽이기  (0) 2015.07.23
MySQL 깨진테이블 복구  (0) 2015.07.13
MySQL Innodb backup  (0) 2015.04.17
블로그 이미지

엘로드넷

,

MySQL Innodb backup

MySQL, MariaDB 2015. 4. 17. 21:47

MySQL Innodb 백업방법(핫백업) : XtraBackup 사용


다운로드주소 : http://www.percona.com/downloads/XtraBackup 



1. 우선 해당 사이트에서 rpm 파일을 받자


percona-xtrabackup-2.2.7-5050.el7.x86_64.rpm



2. 받은 파일을  MySQL이 돌고 있는 서버에 올리고 설치하자


#rpm -Uvh percona-xtrabackup-2.2.7-5050.el7.x86_64.rpm



perl-DBD-MySQL 도 필요하니 설치하자


#yum install perl-DBD-MySQL



3. 백업디렉토리를 만든다


#mkdir /home/backup

#cd /home/backup


4. 백업을 하자


4.1. 모든 디비백업

# innobackupex --user=root --password=디비암호 /home/backup/


4.2. 특정디비 백업

# innobackupex --user=root --password=디비암호 --databases=testdb /home/backup/




5. 백업이 되고 나면 백업 디렉토리에 YYYY-MM-DD HH-MM-SS 폴더가 생성되고 그 안에 백업파일이 들어가 있다.



6. ib_log파일 백업(백업을 저장할 폴더를 좀 전에 디비백업시 생성된 폴더로 지정한다.)


# innobackupex --user=root --password=디비암호 --apply-log /home/backup/YYYY-MM-DD*





복구하기



1. MySQL디비를 stop 한다.


2. 백업한 디비폴더와 ibdata*를 새디비서버의 데이터 저장하는 곳으로 복사한다.


3. 디비를 구동한다.


만약 ib_log파일과 관련한 에러가 난다면 로그파일 시퀀스가 안 맞는 것이므로 


백업폴더에 포함되어 있는 xtrabackup_binlog_pos_innodb 파일을 열어서 숫자를 확인하고 맞춰주면 된다.


#cat xtrabackup_binlog_pos_innodb









'MySQL, MariaDB' 카테고리의 다른 글

too many connections, max_connections, wait_timeout  (0) 2016.01.13
MySQL 5.7.9 root 비밀번호 재설정  (1) 2015.12.02
MSQL Locked 확인 및 죽이기  (0) 2015.07.23
MySQL 깨진테이블 복구  (0) 2015.07.13
MySQL 계정 만들기  (0) 2015.04.18
블로그 이미지

엘로드넷

,