MySQL
MySQL原本是一個開放原始碼的關聯式資料庫管理系統,原開發者為瑞典的MySQL AB公司,該公司於2008年被昇陽微系統收購。2009年,甲骨文公司收購昇陽微系統公司,MySQL成為Oracle旗下產品。
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';
既有帳號建立的 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;
SQL
Online Tutorials
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
- How to Backup/Restore MySQL/MariaDB and PostgreSQL Using ‘Automysqlbackup’ and ‘Autopostgresqlbackup’ Tools
- archlinux | MariaDB
Partition Table
- MySQL 千萬級資料表 partition 實戰應用
- 簡單使用 Mysql Partition 優化查詢
- MariaDB Partition 學習
- PARTITION Maintenance in MySQL
Time-Series in MariaDB
DB Optimization
DB Administration
- 20 mysqladmin Commands for MYSQL/MariaDB Database Administration
- Upgrading Uber’s MySQL Fleet to version 8.0 | Uber Blog
Synchronize DBs
- Synchronize databases more easily with open source tools | Opensource.com
- Apache SeaTunnel | Apache SeaTunnel
Installation
Install on CentOS 7
/etc/yum.repos.d/MariaDB.repo
# MariaDB 10.5 CentOS repository list - created 2021-05-08 02:51 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
安裝開始
yum install MariaDB-server MariaDB-client
Starting MariaDB
systemctl start mariadb.service
systemctl enable mariadb.service
systemctl status mariadb.service
Securing MariaDB
mysql_secure_installation
OS Optimization
Linux Kernel Settings – IO Scheduler
# A temporary change can be done by issuing the following command
echo noop > /sys/block/sda/queue/scheduler
# To make it persistent, you’ll need to configure it in GRUB’s configuration
# file as shown below in /etc/default/grub , rebuild GRUB, and reboot the system.
GRUB_CMDLINE_LINUX_DEFAULT="quiet splash elevator=noop"
Resource Limits – Open Files Limit, Core File Size
# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft core unlimited
mysql hard core unlimited
Configure Swappiness
# /etc/sysctl.conf
vm.swappiness = 1
Filesystem Optimizations
# /etc/fstab
/dev/sdb /var/lib/mysql ext4 defaults,noatime 0 0
DB Optimization
Backup & Restore
Backup
Script#1:
#!/bin/bash
# 以下配置資訊請自己修改
mysql_user="USER" #MySQL 備份使用者
mysql_password="PASSWORD" #MySQL 備份使用者的密碼
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8" #MySQL 編碼
backup_db_arr=("db1" "db2") #要備份的資料庫名稱,多個用空格分開隔開 如("db1" "db2" "db3")
backup_location=/var/www/mysql #備份資料存放位置,末尾請不要帶"/",此項可以保持預設,程式會自動建立資料夾
expire_backup_delete="ON" #是否開啟過期備份刪除 ON 為開啟 OFF 為關閉
expire_days=3 #過期時間天數 預設為三天,此項只有在 expire_backup_delete 開啟時有效
# 本行開始以下不需要修改
backup_time=`date +%Y%m%d%H%M` #定義備份詳細時間
backup_Ymd=`date +%Y-%m-%d` #定義備份目錄中的年月日時間
backup_3ago=`date -d '3 days ago' +%Y-%m-%d` #3 天之前的日期
backup_dir=$backup_location/$backup_Ymd #備份資料夾全路徑
welcome_msg="Welcome to use MySQL backup tools!" #歡迎語
# 判斷 MYSQL 是否啟動,mysql 沒有啟動則備份退出
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR:MySQL is not running! backup stop!"
exit
else
echo $welcome_msg
fi
# 連線到 mysql 資料庫,無法連線則備份退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end
use mysql;
select host,user from user where user='root' and host='localhost';
exit
end
flag=`echo $?`
if [ $flag != "0" ]; then
echo "ERROR:Can't connect mysql server! backup stop!"
exit
else
echo "MySQL connect ok! Please wait......"
# 判斷有沒有定義備份的資料庫,如果定義則開始備份,否則退出備份
if [ "$backup_db_arr" != "" ];then
#dbnames=$(cut -d ',' -f1-5 $backup_database)
#echo "arr is (${backup_db_arr[@]})"
for dbname in ${backup_db_arr[@]}
do
echo "database $dbname backup start..."
`mkdir -p $backup_dir`
`mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset | gzip > $backup_dir/$dbname-$backup_time.sql.gz`
flag=`echo $?`
if [ $flag == "0" ];then
echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz"
else
echo "database $dbname backup fail!"
fi
done
else
echo "ERROR:No database to backup! backup stop"
exit
fi
# 如果開啟了刪除過期備份,則進行刪除操作
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
#`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \;`
`find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf`
echo "Expired backup data delete complete!"
fi
echo "All database backup success! Thank you!"
exit
fi
Restore
How to Restore Large MySQL Database
When we restore a large database, usually we come across two errors as shown below: “MySQL server has gone away” & “Lost connection to MySQL server during query”
Here, we consider restoring a 16GB database:
When restoring huge database, we need to set the values as specified below:
- connect_timeout = 3600
- max_allowed_packet = 1024M
- net_buffer_length = 1000000
- wait_timeout = 86400 (which means 24 hours)
- interactive_timeout = 86400 (which means 24 hours)
- net_write_timeout = 3600
- net_read_timeout = 3600
mysql -u root -p -max_allowed_packet=1024M \
-connect_timeout=3600 \
-net_buffer_length=1000000 \
-wait_timeout=86400 \
-interactive_timeout=86400 \
-net_write_timeout=3600 \
-net_read_timeout=3600 \
database-name < database.sql
Partition Table
Check if partition is supported
> show plugins;
Create partition table
create table mm_tx_new (
start_time timestamp(6) not null default '0000-00-00 00:00:00.000000',
end_time timestamp(6),
hostname varchar(20),
servername varchar(20) not null default 'noservername',
tx_id varchar(15),
user_opi varchar(20),
tx_resp integer,
primary key(start_time,servername)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE ( FLOOR(UNIX_TIMESTAMP(start_time)) )
(PARTITION p202105 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-06-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202106 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-07-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202107 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-08-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202108 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-09-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202109 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-10-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202110 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-11-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202111 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-12-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202112 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2022-01-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION p202201 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2022-02-01 00:00:00.000000')) ) ENGINE = InnoDB)
;
Check partition table
> show create table mm_tx_new;
Add new partition for specified table
ALTER TABLE `mm_tx_new` REORGANIZE PARTITION pMax INTO (
PARTITION p202501 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2025-02-01 00:00:00.000000')) ) ENGINE = InnoDB,
PARTITION pMax VALUES LESS THAN MAXVALUE
);
選用: pMax 是用來儲存最後一個 partition 以後的資料,通常 pMax 不會有資料。目的用途可用來捕捉不預期的未來資料。
Delete the partition
Purge the data in the specified partition
ALTER TABLE <schema.table> TRUNCATE PARTITION <partition-name>