sudo chown -R mysqld:mysqld /output/dir
mysqldump -u dbuser -pxxx --tab=/var/lib/mysql-files Urdbname
--secure-file-priv default @at /var/lib/mysql-files
mysqlimport [options] db_name textfile1 [textfile2 ...]
mysqlimport -u dbuser -pxxx Urdbname textfile1 [textfile2 ...]
–ignore 忽略索引鍵重複的匯入資料
–replace 索引鍵重複時，以匯入的資料更新資料庫中的資料
–local 指定匯入的資料檔案來源為用戶端
******************************************
Import folder of .sql files into a single database
find . -name '*.sql' | awk '{ print "source",$0 }' | mysql --batch -u root -p db_name
mysqlimport -u [username] -p [database] `pwd`/*.txt
***********************************************
Take a mysqldump with each database in its own SQL File
mysql -u username -p -N -e 'show databases' | while read dbname; do mysqldump -u username -pPassword --complete-insert --routines --triggers --single-transaction
"$dbname" > "$dbname".sql; done
MySQL dump db 的 schema
參數: --no-data, -d
MySQL dump db 的 data
參數:
-c: SQL 語法前面會把欄位名稱也列出來
-t: Table
如何看 mysql bin log
mysqlbinlog m10-relay-bin.001
如何看 SQL 語法使用到哪個 Index Key
mysql> EXPLAIN SELECT * FROM User
如何設定把 所有 對此 MySQL 所下的 SQL 語法都記錄下來
my.cnf
[mysqld]
log = /var/logs/mysql/mysql-query.log
如何找出 MySQL column欄位中的值不等於 0-9 的
SELECT * FROM TABLENAME WHERE column REGEXP '[^0-9]';
要將資料倒回 DB 的標準做法(若有設 Foreign Key 用此方法才能順利匯入)
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
----------------------------------------------------------------
Do not do this, since it might screw up encoding:
mysql -u username -p database < dump_file # this is bad
Better do:
mysql -uroot -p --default-character-set=utf8 database
mysql> SET names 'utf8'
mysql> SOURCE utf8.dump
------------------------------------------------------
MySQL innoDB 縮減空間大小
http://xyz.cinc.biz/2014/10/mysql-innodb-reclaim-free-space.html
