Category Archives: mysql
Home » mysql
아래와 같은 에러를 확인할때
[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 설치 디렉토리\my.inf 파일을 수정하면 되고
리눅스 같은거에서는 /etc/my.cnf 파일을 수정하면 된다.
아무튼 my.xxx 파일을 열어보면
[mysqld] 요부분이 있을건데
[mysqld] 아래쪽에
lower_case_table_names=1
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> set global log_warnings = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like “log_warnings”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_warnings | 0 |
+—————+——-+
1 row in set (0.00 sec)
이렇케하믄 안생기긴한데..음..확인해봐야될듯
임시적인 방법인듯하긴한데..
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
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 | […]
select table_schema,table_name,engine from information_schema.tables where table_schema=’$DBname’;
// “ << 이거아님 ‘ ‘ 이거임 변경해서할것
>> table 하나만
check table $테이블명
repair table $테이블명
>> 한번에 데이터베이스 전체 리페어
/usr/local/mysql/bin/mysqlcheck -uroot -p’$PASSWORD’ –auto-repair DB명
>> safe 모드로 mysql 실행
설치경로/mysqld_safe –skip-grant &
update user set password=password(‘패스워드’) where user=’root’;
flush privileges;
quit