Skip to main content

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
效能監視
mysql> show processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     | 
| Connections              | 8     | 
| Max_used_connections     | 4     | 
| Ssl_client_connects      | 0     | 
| Ssl_connect_renegotiates | 0     | 
| Ssl_finished_connects    | 0     | 
| Threads_connected        | 4     |  << active connections
+--------------------------+-------+
7 rows in set (0.00 sec)
Connect with unix socket
# Locate the unix socket file
mysql -uroot -p -e "show variables like 'socket'"
Enter password:
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| socket        | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+


# Connect to DB without password via unix socket 
mysql -S /var/lib/mysql/mysql.sock -u root
User Management

檢查帳號權限

-- List All users
select host,user from mysql.user;

-- Check the privileges for specified user
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR 'admin'@'%';
SHOW GRANTS FOR 'vivek'@'10.147.164.0/255.255.255.0';

權限類別

  1. Data USAGE privileges includes: SELECT, INSERT, UPDATE, DELETE, and FILE
  2. Structure privileges includes: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, and TRIGGER
  3. Administration privileges includes: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, and CREATE USER
  4. SSL privileges includes: REQUIRE NONE, REQUIRE SSL, REQUIRE X509
  5. ALL PRIVILEGES: Shortcut to grants all privileges to a mysql user account.

建立一個 superuser 帳號

CREATE USER 'admin'@'%' IDENTIFIED BY 'the_secure_password';
-- Or
CREATE USER 'admin'@'10.147.164.0/255.255.255.0' IDENTIFIED BY 'the_secure_password';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';
-- Or
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.147.164.0/255.255.255.0';

既有帳號建立的 DDL

SHOW CREATE USER admin;
SHOW CREATE USER vivek;

移除權限

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%';
Table Management

Table's DDL

show create table <table_name>;

Copy Table without data

create table new-table like old-table;
Back to top