FAQ-MySQL Admin

求助指令

mysql> \h
mysql> help
mysql> help <command>;

shell> perror <ErrCode#>

Q: [Warning] Using a password on the command line interface can be insecure

寫資料庫備份 scripts 會遇到這個警告訊息。

解決方法：如果 script 用到 mysqldump 指令，在 Home 目錄裡新增

~/.my.cnf

[mysqldump]
user=YourDBuser
password=YourDBpass

TIP:

- 如果 script 用到 mysql 指令，將 mysqldump 換成 mysql

- 如果 script 用到 mysqladmin，將 mysqldump 換成 mysqladmin

Q: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

從 MySQL 5.6 起對於 TIMESTAMP 有比較嚴格的規範。要避免這訊息，可以修改

Ubuntu 16.04)
/etc/mysql/mysql.conf.d/mysqld.cnf

# fixed for the WARNING below in error.log
# [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
explicit_defaults_for_timestamp = TRUE

Q: [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

MySQL 5.7 預設會啟用 SSL 連線方式，如果用不到，可以將它關閉。

Ubuntu 16.04)
/etc/mysql/mysql.conf.d/mysqld.cnf

# fixed for the WARNING below in error.log
# [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
skip-ssl

Q: 資料庫檔案 /var/lib/mysql/ibdata1 一直再成長怎辦

從 mysql 5.5 以後，預設的 Innodb 會將所有的 DB 的資料儲存在同一個檔案 <資料庫儲存目錄>/ibdata1。

這將會到導致以下幾個問題

• 隨著 DB 數的增加，與 DB 的資料成長，檔案 ibdata1 會呈現巨量的大小。
• 資料庫效能會降低
• 既使從 DB 裡刪除舊資料，甚至將某些 DB 完全移除，檔案 ibdata1 也不會減小

Ans：解決方式是修改 DB 參數使每個 DB 有不同的 Tablespace 檔案。

如果 DB 已經有資料必須重新匯入匯出，才能使設定生效。

// 匯出所有 DB
mysqldump -u root -p --events --routines --triggers --all-databases > alldbs.sql

// 關閉 DB 服務
systemctl stop mariadb

// 修改 /etc/my.cnf.d/server.cnf
[mariadb-5.5]
innodb_file_per_table = 1

// 刪除 ibdata1 與 ib_log檔
rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile*

// 啟動 DB 服務
systemctl start mariadb

// 確認參數生效
mysql -uroot -p -e "show variables like 'innodb_file%';"

+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |   <==== 這一行
+--------------------------+----------+

// 重新匯入所有 DB
mysql -u root -p < alldbs.sql

TIP：

如何確認目前資料庫的儲存目錄
mysql -uroot -p -e "show variables like 'datadir';"

資料表 foo 刪除大量資料列後，如何縮小 Tablespace 的檔案
mysql> optimize table foo;

雖然已經將每個 DB 的 Tablespace 檔案分開，一旦有大量資料交易，ibdata1 檔案仍會有明顯的增長。

Q: 錯誤訊息：ERROR 2013 (HY000) at line 190: Lost connection to MySQL server during query

Ans：資料庫在本機做匯入時，總是出現這個錯誤。原因可能是系統記憶體過小，解決方式：

• 增加實體記憶體
• 或者，建立 SWAP 虛擬記憶體檔

Q: 分析 SQL statement

Ans: explain 的使用範例

mysql> create table t(a int, b int, c int);
mysql> create index t_idx on  t (a,b,c);
mysql> insert into t values (1, 1, 1);
... (load 10 more test rows like this) ...

mysql> explain select * from t where a=1 or b=1;
| table | type  | possible_keys | key   | key_len | ref  
| t     | index | t_idx         | t_idx |      15 | NULL 

Q: root 密碼遺失怎辦

Ans: 以系統的 root 做強制重設密碼步驟

// 切換到 root
su -
// 停止 MySQL 服務
service mysqld stop
// 以 --skip-grant-tables 啟動 MySQL
mysqld_safe --skip-grant-tables --user=root &
// 不用密碼進入 mysql，修改密碼為空
mysql -u root
mysql>UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root';
mysql>flush privileges;
mysql>exit;
// kill 目前的 mysql 服務
ps -ef | grep mysql
kill <mysql-pid>
// 啟動正常模式的 MySQL 服務
service mysqld start
// 修改為新密碼
mysqladmin -u root -p password 'newpass'

Q: 錯誤訊息： [ERROR] /usr/libexec/mysqld: The table 'XXX' is full

Ans: 需要調高 my.cnf 的 max_heap_table_size 的值(預設 16MB)

檢查這個 Table 的狀態

mysql> show table status like 'data_source_stats_hourly_cache' \G
*************************** 1. row ***************************
           Name: data_source_stats_hourly_cache
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 124417
 Avg_row_length: 73
    Data_length: 10244096
Max_data_length: 9278300
   Index_length: 6654260
      Data_free: 191479
 Auto_increment: NULL
    Create_time: 2014-12-24 20:34:07
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

結果顯示 Data_length 已經超過 Max_data_length，解決方法是：透過調整參數 max_heap_table_size，可以增加 Table 的Max_data_length 的長度。

TIPs:

以上的參數適用 MySQL 5.1，不同 MySQL 的版本，參數名稱可能不一樣，請參閱 http://dev.mysql.com/doc/refman/5.1/...on-tables.html。

編輯 /etc/my.cnf

[mysqld]
...
max_heap_table_size=64M
... 

重啟 MySQL 服務

#> service mysqld stop
#> service mysqld start 

再次檢查Table 狀態

#> mysql -uroot -p
mysql> use caati;
mysql> show table status like 'data_source_stats_hourly_cache' \G
*************************** 1. row ***************************
           Name: data_source_stats_hourly_cache
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 5591
 Avg_row_length: 73
    Data_length: 511232
Max_data_length: 37113200
   Index_length: 328268
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-10 14:42:02
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

結果顯示 Max_data_length 有變大，並且 mysql.log 的錯誤訊息不再發生。

Q: 錯誤訊息 [ERROR] Column count of mysql.proc is wrong

[ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50077, now running 50152. Please use mysql_upgrade to fix this error.

Ans: 這是因為作過資料庫升級後，沒有執行 mysql_upgrade，解決方法

# mysql_upgrade -u root -p

Q: 錯誤訊息：Error: 1030 - Got error 28 from storage engine

Ans：執行指令 perror 分析錯誤發生原因

#perror 28
OS error code  28:  No space left on device

原來是資料庫的儲存空間不足所造成。

// 列出目前資料庫所有連線

#mysqladmin -u root -p processlist

Q: 目錄 /var/lib/mysql 有大量的的mysql-bin.00001.....磁碟空間佔滿

Ans: mysql-bin 是 transaction log，用於資料庫同步或資料回復時使用，預設是一個檔案約 1GB，解決方式如下：

service mysqld stop
vi /etc/my.cnf 

/etc/my.cnf：找到 log-bin=mysql-bin 增加這兩行

# added by alang
expire_logs_days        = 10
max_binlog_size         = 100M

啟動 mysql

service mysqld start

另一種方法

5.2 MySQL Server Logs :: 5.2.3 The Binary Log
12.5.1 SQL Statements for Controlling Master Servers :: 12.5.1.1 PURGE BINARY LOGS Syntax

系統目錄 /var/lib/mysql 底下有很多 mysql-bin.xxxxxxx 的檔案，這些是 MySQL 的 binary log，假設系統並沒有作 replication，而且確定不需要保留這些 transaction log，下列指示可以清除它們：

// 檢查 log 功能開啟

mysql> show variables like '%log%';
名稱為 log_bin

// 檢查 log 檔

mysql> show binary logs;
編號愈大的表示 log 愈新

// 刪除編號 mysql-bin.000011 以前但不包含 mysql-bin.000011 的所有 binary log files

mysql> purge binary logs to 'mysql-bin.000011';
指令結束後，記得在執行一次 show binary logs 檢查看看。

Q: mysqldump: Got error: 145: Table './.../wp_useronline' is marked as crashed and should be repaired when using LOCK TABLES

Ans: 這是因為資料庫可能曾經意外中斷服務所造成，執行修復指令如下：

mysqlcheck -u<db_username> -p --auto-repair --databases <db_name>

NOTE:

若有多個 Table 損壞，可能要執行多次，直到每一個 Table 顯示 ok。

如果 ErrCode 是 144，可能必須使用其他修復的指令，例如 myisamchk，參閱教學：http://dev.mysql.com/doc/refman/5.1/...am-repair.html