# DB Admin Tips

##### Initial DB Setup

```
/usr/bin/mysql_secure_installation
```

Change the root's password

```sql
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd';
flush privileges;
exit;
```

##### Tablespace Shrink

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

```SQL
mysql> OPTIMIZE table foo;
```

一次優化所有的資料表

```shell
#!/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

```shell
# 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

檢查帳號權限

```SQL
-- 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 帳號

```SQL
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';
```

一般程式專用帳號

```sql
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'thisissecurity';

-- For all tables
GRANT ALL PRIVILEGES ON dbname.* TO 'appuser'@'localhost';

-- For Specified table
GRANT ALL PRIVILEGES ON dbname.tablename TO 'appuser'@'localhost';
```

既有帳號建立的 DDL

```SQL
SHOW CREATE USER admin;
SHOW CREATE USER vivek;
```

移除權限

```SQL
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%';
```

##### Table Management  


Table's DDL

```sql
show create table <table_name>;
```

Copy Table without data

```sql
create table new-table like old-table;
```