今天要登入awacs(網管軟体)時
出現無法連接的訊息
登入主机發現mariadb的daemon不見了
systemctl restart mariadb也起不來
看了一下 /var/log/mariadb/mariadb.log 發現以下的記錄

170824 19:02:01  InnoDB: Page checksum 2583736692 (32bit_calc: 3902863637), prior-to-4.0.14-form checksum 2992650943
InnoDB: stored checksum 218772529, prior-to-4.0.14-form stored checksum 775370784
InnoDB: Page lsn 825440558 909582385, low 4 bytes of lsn at page end 825373998
InnoDB: Page number (if stored to page already) 775303712,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 775041840
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

按照說明
在/etc/my.cnf 加上

[mysqld]
innodb_force_recovery = 1

重啟mariadb

再查一下log

170824 19:02:01  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 1553519:2, should be 1553541:2!
170824 19:02:01  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 1553519:3, should be 1553541:3!

InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
170824 19:02:01  InnoDB: Rolling back trx with id 127203F, 451 rows to undo
170824 19:02:01  InnoDB: Waiting for the background threads to start

InnoDB: Rolling back of trx id 127203F completed
170824 19:02:01  InnoDB: Rollback of non-prepared transactions completed
170824 19:02:02 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 653240202086
170824 19:02:02 InnoDB: !!! innodb_force_recovery is set to 1 !!!
170824 19:02:02 [Note] Plugin 'FEEDBACK' is disabled.
170824 19:02:02 [Note] Server socket created on IP: '0.0.0.0'.
170824 19:02:02 [Note] Event Scheduler: Loaded 0 events
170824 19:02:02 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.41-MariaDB'  socket: '/home/Alopex/mysql/mysql.sock'  port: 3306  MariaDB Server
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

看來是修好了
不過user還是不能用

InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

要再把

[mysqld]
innodb_force_recovery = 1

mark掉再重開

目前看來是正常了
再觀察看看


https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

最近新買了一hp dl380 g8 要做web server的備援机
要考慮的就是資料同步的問題了
不考慮HA即時同步 只在一台出問題時 另一台改完ip後能暫時運作
所以預計每天晚上同步資料一次
檔案資料可以用rsync
但db就要考慮lock的問題了
查了一下資料

mysql可以用mysqldump來備份執行中的db而不用shutdown 但當量大時時間過久而且restore更久 不考慮

另外就是mysqlhotcopy(mysql自帶工具)
這個是直接copy mysql的資料檔案，所以復原時直接copy檔案就可以了 速度快又方便 但只能備份myisam的資料庫 無法在innodb上使用

語法如下
mysqlhotcopy -u root -p password --addtodest dbname /backup/path

若要備份全部db 語法如下
mysqlhotcopy -u root -p password --addtodest --regex . /backup/path

若要備份innodb(也可以備份myisam)
可以使用http://www.percona.com/doc/percona-xtrabackup/
操作方法可參考
http://blog.chinaunix.net/uid-20728886-id-138116.html
http://ezerg.iteye.com/blog/1029826

因為大table 效能的問題想把mysql 的 innodb 開起來

找了半天要如何把mysql innodb enable的方法

除了在/etc/my.cnf把innodb的相關參數設好之外

還要以下的動作

/etc/init.d/mysql stop
mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1_bck
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

/etc/init.d/mysql start

mysql innodb myisam 轉換的方法

http://phorum.study-area.org/index.php?topic=27865.0