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]
끝!



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
![[Warning] Statement may not be safe to log in statement format.](https://www.msnote.co.kr/wp-content/themes/analytical-lite/images/default/blog-img.jpg)



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 | […]


