Category Archives: mysql

Home »  mysql

Column count of mysql.db is wrong. Expected 22, found 20. The table is probably

아래와 같은 에러를 확인할때
[ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
[ERROR] mysql.user has no `Event_priv` column at position 29
[ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
 
mysql_upgrade –force -uroot -p [your mysql root password]
끝!
 

mysql 커맨드 대소문자 구별

윈도우에서는 MySQL 설치 디렉토리\my.inf 파일을 수정하면 되고
리눅스 같은거에서는 /etc/my.cnf 파일을 수정하면 된다.
아무튼 my.xxx 파일을 열어보면
[mysqld] 요부분이 있을건데
[mysqld] 아래쪽에
lower_case_table_names=1

Binlog has bad magic number;  It’s not a binary log file that can be used by this version of MySQL

Binlog has bad magic number (mysql error)
by DAVID on NOVEMBER 20, 2012
My MySql server had the following lines in the error log:
121119 22:50:57 mysqld started
121119 22:50:57 InnoDB: Started; log sequence number 0 43655
121119 22:50:57 [ERROR] Binlog has bad magic number; It’s not a binary log file that can be used by this version of MySQL
121119 22:50:57 [ERROR] Can’t init tc log
121119 22:50:57 [ERROR] Aborting
I fixed the problem by renaming the mysql-bin.index file:
mv mysql-bin.index mysql-bin.index-corrupt

mysql 튜닝..

http://dev.kthcorp.com/2011/07/01/mysql-innodb-storage-engine-benchmark/
http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21862&cat1=14&cat2=422&cat3=438&lang=k

mysql replication

1. OS : Centos 5.8 64 bit

2. 구성

> mysql 설치는 마음것 설치하시길~

> my.cnf 설정

>reblication 의 핵심은 중복되지 않은 서버 ID 와 bin로그를 기록하게 해야 됨

master
slave

log-bin=mysql-bin
server-id = 10

server-id = 1
relay-log=slave1-relay-bin

 
skip_slave_start

 
read_only

 
slave-skip-errors = all

> 원격지 접속 권한부여 (master 에만해당)

INSERT INTO mysql.user (host,user,password) VALUES (‘192.168.1.%’,’root’,password(‘password’));
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.1.%’;
flush privileges;

> 확인

| root | 192.168.1.% | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |

> replication 설정 (슬레이브전체)
 

usr/local/mysql/bin/mysql -u root -p -h 192.168.1.101 // 마스터쪽으로 접속되는지먼저 확인
 

 
 

slave stop;
 

 
 

change master to master_host=’192.168.1.101′, master_user=’root’, master_password=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=4;

// 최초 구성이기 때문에 첫번째 로그파일에서 첫번째 POS 지점으로 연결함

slave start;
 

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.101

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 590

Relay_Log_File: slave1-relay-bin.000004

Relay_Log_Pos: 736

Relay_Master_Log_File: mysql-bin.000003

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: 590

Relay_Log_Space: 1036222

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: 0

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: 1

1 row in set (0.00 sec)

ERROR:

No query specified

> master 쪽에서 최종확인

[MMM_1, /usr/local/mysql/data >/usr/local/mysql/bin/mysqladmin -u root -p proc stat -i1

Enter password:

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

| Id | User | Host | db | Command | […]

mysql 엔진 타입확인

select table_schema,table_name,engine from information_schema.tables where table_schema=’$DBname’;
// “ << 이거아님 ‘ ‘  이거임 변경해서할것

mysql repair

>> table 하나만
check table $테이블명
repair table $테이블명
 
>> 한번에 데이터베이스 전체 리페어
/usr/local/mysql/bin/mysqlcheck -uroot -p’$PASSWORD’ –auto-repair DB명

mysql 락걸린 DB 덤프뜰때~

mysqldump -u root -p –lock-tables=0 mysql > mysql.sql

mysql root 패스워드 초기화

>> safe 모드로 mysql 실행
설치경로/mysqld_safe –skip-grant &

update user set password=password(‘패스워드’) where user=’root’;
flush privileges;
quit