MySQL

MySQL原本是一個開放原始碼的關聯式資料庫管理系統,原開發者為瑞典的MySQL AB公司,該公司於2008年被昇陽微系統收購。2009年,甲骨文公司收購昇陽微系統公司,MySQL成為Oracle旗下產品。

DB Admin Tips

Initial DB Setup
/usr/bin/mysql_secure_installation
Tablespace Shrink

資料表刪除大量的歷史資料列後,所屬的 Tablespace 檔案大小不會自動變小,必須執行維護指令

mysql> OPTIMIZE table foo;

一次優化所有的資料表

#!/bin/sh
echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ "$datafree" -gt 0 ] ; then
   fragmentation=$(($datafree * 100 / $datalength))
   echo "$database.$name is $fragmentation% fragmented."
   mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done
Mariadb log 管理維護

手動清除

$> cat /dev/null > /var/log/mysql.log
$> mysqladmin -uroot -p flush-logs

使用 logrotate 服務自動管理
1. 新增 /etc/logrotate.d/mariadb:

/var/log/mariadb/*.log {
    create 660 mysql mysql
    notifempty
    daily
    rotate 5
    missingok
    minsize 1M
    maxsize 100M
    compress
    delaycompress
    sharedscripts
    olddir archive/
    createolddir 770 mysql mysql
    postrotate
        # just if mysqld is really running
        if test -x /usr/bin/mysqladmin && \
            /usr/bin/mysqladmin ping &>/dev/null
        then
            /usr/bin/mysqladmin flush-logs
        fi
    endscript
}

2. 新增 mysql 管理帳號密碼檔

$> vi ~/.my.cnf

[mysqladmin]
user            = root
password        = YourPass

$> chmod 0600 ~/.my.cnf 

 

SQL

WHERE
SELECT name, wins
FROM golfers
WHERE wins = 1;

-- Comparison
SELECT name
FROM golfers
WHERE name = 'George';
-- <>: tests whether two values are not equal
SELECT name, wins
FROM golfers
WHERE wins <> 1;
--
SELECT name, wins
FROM golfers
WHERE wins < 1;
-- <=: tests whether the first value is less than or equal to the second
SELECT name, wins
FROM golfers
WHERE wins <= 1;
-- >=: tests whether the first value is greater than or equal to the second
SELECT name, wins
FROM golfers
WHERE wins >= 1;

-- Null
SELECT name, rounds_played
FROM golfers
WHERE rounds_played IS NULL;

-- Range
SELECT name, best
FROM golfers
WHERE best BETWEEN 67 AND 73;

-- Membership
SELECT name, best
FROM golfers
WHERE best IN (65, 67, 69, 71);

-- Pattern Match
-- _: an underscore represents a single unknown character
SELECT name, rounds_played
FROM golfers
WHERE rounds_played LIKE '2_';
-- %: a percentage sign represents zero or more unknown characters
SELECT name, rounds_played
FROM golfers
WHERE name LIKE 'G%';

-- Combining Multiple Predicates with AND and OR
SELECT name, best, worst, average
FROM golfers
WHERE best < 70 AND worst < 96;
--
SELECT name, best, worst, average
FROM golfers
WHERE best < 70 OR worst < 96;
--
SELECT name, average, worst, rounds_played
FROM golfers
WHERE average < 85 OR worst < 95 AND rounds_played BETWEEN 19 AND 23;
--
SELECT name, average, worst, rounds_played
FROM golfers
WHERE (average < 85 OR worst < 95) AND rounds_played BETWEEN 19 AND 23;

-- Excluding Results with NOT
SELECT name
FROM golfers
WHERE name NOT LIKE 'R%';
--
SELECT name, rounds_played
FROM golfers
WHERE rounds_played IS NOT NULL;
--
SELECT name, average, best, wins
FROM golfers
WHERE NOT (average < 80 AND best < 70) OR wins = 9;
--
SELECT name, average, best, wins
FROM golfers
WHERE NOT ((average < 80 AND best < 70) OR wins = 9);
--
SELECT name
FROM golfers
WHERE name NOT = 'Grady';

Learning MySQL

MySQL Backup