Posts Tagged ‘Database’

如何自動備份MySQL DB in Linux Server (AWS EC2)

Wednesday, August 20th, 2014
Part 1. 首先,建立一個名叫db_backup.sh的script檔,存放在任何你想要放的路徑下。
vi db_backup.sh

基本寫法 (雙引號要留著,輸出檔名最好寫完整的絕對路徑,例: /home/db_backup/xxxx.sql )

mysqldump -u root --p"{PASSWORD}" {DB_NAME} > {FILE_NAME}.sql

如要備份所有Table

mysqldump -u root --p"{PASSWORD}" --all-databases > {FILE_NAME}.sql

如要省略建立database及table及不需要輸出trigger,可以加入額外的參數

mysqldump -u root -p"{PASSWORD}" -c -n -t --skip-triggers {DB_NAME} > {FILE_NAME}.sql

如要使用GZip壓縮

mysqldump -u root -p"{PASSWORD}" {DB_NAME} | gzip > {FILE_NAME}.sql.gz

如要使用GZip壓縮並且幫輸出的檔名字尾加上日期

mysqldump -u root -p"{PASSWORD}" {DB_NAME} | gzip > {FILE_NAME}_$(date +%Y-%m-%d).sql.gz

script內容寫好儲存了之後,將其修改為可被執行

chmod +x db_backup.sh
Part 2. 改寫crontab

修改crontab (請注意你目前登入的帳號身份,因為每個account都會有自己的crontab,可別改錯了。):

crontab -e

在crontab裡加入下面這行 (這樣寫的意思代表每天的晚上7點01分(GMT時區),執行後面指定路徑的script。前五個數字(*)分別代表: [分] [時] [日] [月] [Weekday])

01 19 * * * /home/myuser/db_backup/db_backup.sh

檢查cron是否有在運作

service crond status

SQLite Result Codes

Thursday, July 15th, 2010
#define SQLITE_OK           0   /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* NOT USED. Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* NOT USED. Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */
/* end-of-error-codes */

Reference:
http://www.sqlite.org/capi3ref.html#SQLITE_ABORT