# MySQL MySQL原本是一個開放原始碼的關聯式資料庫管理系統，原開發者為瑞典的MySQL AB公司，該公司於2008年被昇陽微系統收購。2009年，甲骨文公司收購昇陽微系統公司，MySQL成為Oracle旗下產品。 # 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 ; ``` Copy Table without data ```sql create table new-table like old-table; ``` # SQL ##### Online Tutorials - [MySQL Cheat Sheet](https://gist.github.com/bradtraversy/c831baaad44343cc945e76c2e30927b3) ##### WHERE ```SQL 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](https://www.tecmint.com/mysql-mariadb-postgresql-database-backup-using-automysqlbackup-autopostgresqlbackup/) - [archlinux | MariaDB](https://wiki.archlinux.org/title/MariaDB) ##### Partition Table - [MySQL 千萬級資料表 partition 實戰應用](https://www.itread01.com/hkclpql.html) - [簡單使用 Mysql Partition 優化查詢](https://cola.workxplay.net/mysql-partitioning-for-performance-optimization/) - [MariaDB Partition 學習](https://abcg5.pixnet.net/blog/post/117375474-mariadb-partition-%E5%AD%B8%E7%BF%92) - [PARTITION Maintenance in MySQL](http://mysql.rjweb.org/doc.php/partitionmaint) ##### Time-Series in MariaDB - [Using a Staging Table for Efficient MySQL Data Warehouse Ingestion](http://mysql.rjweb.org/doc.php/staging_table) - [Summary Tables in MySQL](http://mysql.rjweb.org/doc.php/summarytables) ##### DB Optimization - [13 Tips for Tuning and Optimizing MySQL and MariaDB](https://vitux.com/tune-and-optimize-mysql-mariadb/) ##### DB Administration - [20 mysqladmin Commands for MYSQL/MariaDB Database Administration](https://www.tecmint.com/mysqladmin-commands-for-database-administration-in-linux/) - [Upgrading Uber’s MySQL Fleet to version 8.0 | Uber Blog](https://www.uber.com/en-TW/blog/upgrading-ubers-mysql-fleet/) - [DBX](https://dbxio.com/cn) - 輕量資料庫工具 + MCP Server 讓 AI Agent 直接操作資料；跨平台、開源免費的輕量型資料庫管理工具 ##### Synchronize DBs - [Synchronize databases more easily with open source tools | Opensource.com](https://opensource.com/article/23/3/synchronize-databases-apache-seatunnel) - [Apache SeaTunnel | Apache SeaTunnel](https://seatunnel.apache.org/) # Installation ##### Install on CentOS 7 URL: [https://downloads.mariadb.org/mariadb/repositories/#mirror=mephi&distro=CentOS&distro\_release=centos7-amd64--centos7](https://downloads.mariadb.org/mariadb/repositories/#mirror=mephi&distro=CentOS&distro_release=centos7-amd64--centos7) /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 ``` 安裝開始 ```shell yum install MariaDB-server MariaDB-client ``` ##### Starting MariaDB ```shell systemctl start mariadb.service systemctl enable mariadb.service systemctl status mariadb.service ``` ##### Securing MariaDB ``` mysql_secure_installation ``` ##### OS Optimization Linux Kernel Settings – IO Scheduler ```shell # 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: ```shell #!/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 < $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 = **1024**M - 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** ```shell 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 ```shell > show plugins; ``` ##### Create partition table ```SQL 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 ```SQL 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 TRUNCATE PARTITION ``` # Automation in shell ##### Run SQL ```bash counter=`mysql -u $DB_USER -p $DB_PASS $DB_NAME -e "select count(*) from tablename where id=XX;"` # -B: Suppress table borders counter=`mysql -u $DB_USER -p $DB_PASS $DB_NAME -B -e "select count(*) from tablename where id=XX;"` # Multiple SQLs mysql -u USER -pPASSWORD <