DB Admin Tips

Initial DB Setup 
 /usr/bin/mysql_secure_installation 
 Change the root's password 
 ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd';
flush privileges;
exit; 
 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'; 
 權限類別 
 
 Data USAGE privileges includes: SELECT, INSERT, UPDATE, DELETE, and FILE 
 Structure privileges includes: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, and TRIGGER 
 Administration privileges includes: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, and CREATE USER 
 SSL privileges includes: REQUIRE NONE, REQUIRE SSL, REQUIRE X509 
 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'; 
 一般程式專用帳號 
 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 
 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;