![首頁 Logo Keith ]p !!](/themes/mytheme/logo.png){kind=logo}
最後更新: 2018-12-01
目錄
- Mysql Data File Format
- mysql cli options
- Command Shell Input Password
- DB Status
- 在 Shell 行一個 SQL File
- Output Selected Data To File
- rehash
- Profiling
- mysqldump
- HotKey
- TroubleShoot
- mysqlimport
-
mysqladmin
- kill - mysqlshow
- mysqlcheck
- CHECK 與 REPAIR
- OPTIMIZE TABLE
- mysql_upgrade
- User Config file
- mysqld_safe
- PROCEDURE & PROCEDURE
-
查看情況
Mysql Data File Format
*.frm, *.MYI, *.MYD 分別是 MyISAM Table 的表 "結構, 索引, 數據"
file tpl_mytable.MYI
tpl_mytable.MYI: MySQL MISAM compressed data file Version 1
file tpl_mytable.frm
tpl_mytable.frm: MySQL table definition file Version 7
file tpl_mytable.MYD
tpl_mytable.MYD: DBase 3 data file (1398362898 records) <-- 不代表有幾多 record !!
mysql cli options
--skip-column-names (-N)
Suppress column header on SELECT
ie.
mysql -u mail_admin mail -N -e 'select email from users'
--silent (-s)
This option can be given multiple times to produce less and less output.
用圖: output without table
# 有 table 的情況
+----------+ | Header | +----------+ | account1 | | account2 | +----------+
ie.
mysql -u mail_admin mail -sN -e 'select REPLACE(email ,"@datahunter.org","") from users'--compress (-C)
43277 rows in set (15.54 sec) # 無開 43278 rows in set (2.66 sec) # 有開
--quick
This forces mysql to retrieve results from the server a row at a time
rather than retrieving the entire result set and buffering it in memory before displaying it.
--raw (-r)
For tabular output,
the “boxing” around columns enables one column value to be distinguished from another.
For nontabular output (--batch or --silent option is given)
special characters are escaped in the output so they can be identified easily.
The --raw option disables this character escaping.
(Newline, tab, NUL, and backslash are written as \n, \t, \0, and \\)
run cmd:
mysql -u "username" [dbname] -p"pw" -e [query]
Command Shell Input Password
[1] 建議
export MYSQL_PWD=MyPass
* 如果 MYSQL_PWD 失效, 那可能是 ~/.my.cnf 設定了 "password=???"
[2] 會比其他人看到 pw
myql -pMyPass
DB Status
[1]
mysql> status
-------------- mysql Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3 Connection id: 1003 Current database: Current user: [email protected] SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.31 MySQL Community Server (GPL) Protocol version: 10 Connection: 192.168.0.1 via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 5 min 54 sec Threads: 11 Questions: 2905083 Slow queries: 0 Opens: 464 Flush tables: 1 Open tables: 456 Queries per second avg: 8206.449 --------------
[2]
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]
在 mysql Shell 內執行一個 SQL File
mysql> use your_db_name
mysql> source sql_name.sql
OR
mysql> \. sql_name.sql
OR
mysql> LOAD DATA INFILE '/home/jason/categories.txt' INTO TABLE categories LINES TERMINATED BY '\n';
Run Script
[1] mysql < script.sql
[2] mysql -e 'source <path-to-sql-file>'
Output Selected Data To File
Output data to:
tee(\T)
mysql > tee <outfile> # Append everything into given outfile.
OR
INTO OUTFILE
mysql > SQL QUERY INTO OUTFILE '/path/to/sql/file'
i.e.
SELECT * INTO OUTFILE 'backup.txt' FIELDS TERMINATED BY ',' FROM <tables>
* Default FIELDS TERMINATED BY "Tab"
* 遇到 "NULL" 時, MySQL會使用 "\N" 表到.
* MySQL 預設的 Escape character "\" ( FIELDS ESCAPED BY '\' )
* 當沒有指定 OUTFILE 的 full path 時, 那它會保存到 /var/lib/mysql/<databasename>
CSV Output:
select * into outfile 'c:/resources.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from resources;
rehash
#no-auto-rehash # faster start of mysql but no tab completition
mysql> rehash
auto-rehash
Profiling
mysql > SET PROFILING = 1;
mysql > ...
mysql > SHOW PROFILES;
mysqldump
Usage
mysqldump [options] [db_name [tbl_name ...]]
* To make a complete dump, pass --all-databases --triggers --routines --events
Dump remote DB 到本地
mysqldump -u remote_user -p -h remote_host remote_db > DB.sql
remark:
remote 的 tcp port 3306 要開 !!
不 dump 個別 table
--ignore-table=DB-NAME.TB-NAME
P.S.
# dump table without data
mysqldump -u USERNAME DB-NAME TB-NAME --no-data --skip-triggers -p > DB-NAME.TB-NAME.sql
不同的編碼的 dump
mysqldump -u root -p --default-character-set=big5 YOUR_DB > YOUR_DB.sql
triggers, routines, and events
- stored procedures / FUNCTION ( --routines ) # FALSE by default
- triggers ( --triggers ) # TRUE by default
- events ( --events, -E ) # This option requires the EVENT privileges for those databases.
routines and triggers only
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt "MyDb" > MyDb.sql
opts
---- routines & triggers ----
--routines, -R # Included stored routines
# Requires the SELECT privilege for the mysql.proc table
# (CREATE PROCEDURE and CREATE FUNCTION statements)
--skip-triggers # Default: --triggers
----
--no-create-info, -t # Suppress the "CREATE TABLE" statements
--no-data, -d # do not dump table contents ( Schema Only )
--no-create-db, -n # Suppress the "CREATE DATABASE" statements
----
--skip-opt # 即是 skip 了 --opt
--opt # Enable by default 相當於加入了
# --add-drop-table --add-locks --create-options
# --disable-keys --extended-insert
# --lock-tables --quick --set-charset
--skip-add-drop-table # Do not add a "DROP TABLE" statement before each CREATE TABLE statement
--skip-lock-tables
--create-options # Include all MySQL-specific table options in the CREATE TABLE statements.
--disable-keys, -K # 會用以下結構 Export.
# This makes loading the dump file faster
# because the indexes are created after all rows are inserted.
/*!40000 ALTER TABLE tbl_name DISABLE KEYS */; INSERT statements /*!40000 ALTER TABLE tbl_name ENABLE KEYS */;
--extended-insert
--set-charset
--quick, -q # useful for dumping large tables
# retrieve rows for a table from the server a row at a time (--quick 的作用),
# rather than retrieving the entire row set and buffering it in memory before writing it out.
# --quick is on by default, as it is one of the options included in --opt, which is itself on by default.
# 不用 "--quick" 必須 "--skip-extended-insert", 否則會 load 爆 RAM
要小心用的 opt "--compact"
--compact 相當於
- --skip-add-drop-table
- --skip-add-locks
- --skip-comments
- --skip-disable-keys
- --skip-set-charset
multiple insert
--extended-insert, -e (Default)
Use multiple-row INSERT syntax that include several VALUES lists.
This results in a smaller dump file and speeds up inserts when the file is reloaded.
(that it groups 1024 statements)
Import 唔到就要修改 "max_allowed_packet"
--skip-extended-insert
mysqldump Troubleshoot
Error 1:
mysqldump: Got error: 1016: Can't open file: './fcms/tl_homeworktoex.frm' (errno: 24) when using LOCK TABLES
mysqldump --lock-tables=false DB -p > DB.sql
Or
# "LOCK TABLES" privilege if the --single-transaction option is not used
mysqldump --single-transaction DB -p > DB.sql
Error 2:
#145 - Table './db/table' is marked as crashed and should be repaired
在 mysql shell 上行
REPAIR TABLE DB
P.S.
Dump 到碰不代表 restore 到!!
Error 3:
mysqldump got error 2013 lost connection ...
解決方法: 加大 max_allowed_packet
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
socket = /var/run/mysqld/mysqld.sockError 4:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
原因
Mysql 5.7.31 之後 Access to the INFORMATION_SCHEMA.FILES table 需要有 PROCESS privilege.
解決方法:
A) --no-tablespaces, -y
This option suppresses all "CREATE LOGFILE GROUP" and "CREATE TABLESPACE" statements in the output of mysqldump.
=> 當帳戶沒有 PROCESS privilege 時去 Dump DB 就要用它
B) Add the global PROCESS privilege to the user running the command(mysqldump)
P.S.
InnoDB:
CREATE [UNDO] TABLESPACE tablespace_name
[ADD DATAFILE 'file_name']
[AUTOEXTEND_SIZE [=] value]
[FILE_BLOCK_SIZE = value]
[ENCRYPTION [=] {'Y' | 'N'}]To add tables to the tablespace
- CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name
- ALTER TABLE tbl_name TABLESPACE [=] tablespace_name
* 一般 user a/c 係行唔到 "CREATE TABLESPACE"
#1227 - Access denied; you need (at least one of) the CREATE TABLESPACE privilege(s) for this operation
mysqlimport
mysqlimport [options] db_name textfile1 [textfile2 ...] <-- (LOAD DATA INFILE )
HotKey
# Display results vertically to avoid line-wrapping
select * from users \G
# Cancel the current command and clear the input
slect * fom typo \c
mysqladmin
version
mysqladmin version
mysqladmin Ver 8.41 Distrib 4.1.22, for Win32 on ia32 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.22-community Protocol version 10 Connection localhost via TCP/IP TCP port 3306 Uptime: 3 min 44 sec Threads: 1 Questions: 3 Slow queries: 0 Opens: 11 Flush tables: 1 Open tabl es: 5 Queries per second avg: 0.013
ping
mysqladmin ping
mysqld is alive
variables
mysqladmin variables
+-----------------------------------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +-----------------------------------------+-------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/ | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF |
status
mysqladmin status
Uptime: 1105 Threads: 14 Questions: 8471056 Slow queries: 0 Opens: 699
Flush tables: 1 Open tables: 678 Queries per second avg: 7666.114Opens The number of tables the server has opened.
Flush tables The number of flush-*, refresh, and reload commands the server has executed.
Open tables The number of tables that currently are open.
* If no modifier is present, the default is SESSION.
mysqladmin extended-status
+-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | Aborted_clients | 0 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 461602 | | Bytes_sent | 4568937 | ... | Com_delete | 39510 | | Com_insert | 466 | | Com_select | 120923 | | Com_update | 324 | ... | Threads_connected | 2 | | Threads_created | 151 | | Threads_running | 1 |
Example
mysqladmin extended-status | grep Qcache_hits
| Qcache_hits | 0 |
reload
相當於 flush-privileges
flush-tables
...
flush-logs
...
processlist
mysqladmin processlist
+-----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+----+---------+------+-------+------------------+
| 103 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+Time:
The time in seconds that the thread has been in its current state
--verbose, -v
mysqladmin -v processlist 相當於 SHOW FULL PROCESSLIST
kill id, id ..
mysql> KILL 103;
kill by username
export MYSQL_PWD=MyPass # 查看當前 Login 的 User mysqladmin processlist # process id list mysql -s -e "SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='USERNAME'" > id.txt # loop to kill while read id; do mysqladmin kill $id; done < id.txt
start-slave /stop-slave
Start / Stop slave
mysqlshow
display database, table, and column information
command-line interface to several SQL SHOW statements.
mysqlshow [options] [db_name [tbl_name [col_name]]]
mysqlcheck
- 它可以 checks, repairs, optimizes, or analyzes tables / DB.
- Each table is locked and therefore unavailable to other sessions
- mysqlcheck is similar in function to myisamchk
- mysqlcheck must be used when the mysqld server is running
相對應的 SQL statements
- CHECK TABLE (-c)
- REPAIR TABLE (-r)
- ANALYZE TABLE (-a)
- OPTIMIZE TABLE (-o)
Usage:
mysqlcheck [options] db_name
常用參數:
repair 多少個 DB
- --databases, -B # 只 check 某 DB
- --all-databases, -A # Check all tables in all databases
repair 設定
- --check, -c # Default operation
- --auto-repair # If a checked table is corrupted, automatically fix it
- --repair, -r
- --optimize, -o
- --all-in-1 # 對 DB 一次過修復 (Default 是逐 table repair 的)
Check for upgrade
-
--check-upgrade
Invoke CHECK TABLE with the FOR UPGRADE option to check tables for
incompatibilities with the current version of the server.
repair 的方式:
--use-frm
For repair operations on MyISAM tables, get the table structure from the .frm file
so that the table can be repaired even if the .MYI header is corrupted.
--fast, -f
只 check 不正常 close table
--quick, -q
比 --fast 還會快 !! 只對 index tree 作修複
--extended, -e
If you are using this option to check tables,
it ensures that they are 100% consistent but takes a long time.
ie.
# MySQL OPTIMIZE all tables in a DB
mysqlcheck -u root -p --auto-repair -c -o DBNAME
CHECK 與 REPAIR
CHECK Usage:
CHECK TABLE tbl_name [, tbl_name] ... [option] [option]
CHECK TABLE test_table FAST QUICK;
Opts:
QUICK
Do not scan the rows to check for incorrect links.
FAST
Check only tables that have not been closed properly.
( Applies only to MyISAM tables and views; ignored for InnoDB. )
MEDIUM
Scan rows to verify that deleted links are valid.
This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
(Applies only to MyISAM tables and views; ignored for InnoDB.)
EXTENDED
Do a full key lookup for all keys for each row.
This ensures that the table is 100% consistent, but takes a long time.
# If a table is corrupted, it is most likely that the problem is in the indexes and not in the data part.
REPAIR Usage:
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
* Make a backup of a table before performing a table repair operation;
under some circumstances the operation might cause data loss.
Option:
NO_WRITE_TO_BINLOG ( alias LOCAL )
By default, the server writes REPAIR TABLE statements to the binary log so that they replicate to replication slaves.
QUICK
REPAIR TABLE tries to repair only the index file, and not the data file.
EXTENDED
MySQL creates the index row by row instead of creating one index at a time with sorting.
USE_FRM
available for use if the .MYI index file is missing or if its header is corrupted. ( recreate .myi files from scratch)
OPTIMIZE TABLE
OPTIMIZE [NO_WRITE_TO_BINLOG|LOCAL] TABLE tbl_name [, tbl_name] ...
不同 DB Engine (inodb|myisam|archive) 有不同的 OPTIMIZE
* Reduce storage space (Overhead)
MyISAM:
Reclaim the unused space & defragment
After deleting a large part of a MyISAM or ARCHIVE table,
or making many changes to a MyISAM or ARCHIVE table with variable-length rows
(tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns).
Unused space
Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.
mysql_upgrade
After running "mysql_upgrade", restart mysqld so that any changes made to the system tables take effect.
相當於
mysqlcheck --all-databases --check-upgrade --auto-repair mysql < fix_priv_tables
mysql_upgrade also saves the MySQL version number in a file named mysql_upgrade_info in the data directory.
User Config file (.my.cnf)
touch ~/.my.cnf; chmod 600 ~/.my.cnf
~/.my.cnf
# Apply to all mysql client tools [client] # V 5.X socket = /var/run/mysqld/mysqld.sock # V 8.0 #socket = /var/run/mysqld/mysqlx.sock # Default #port = 3306 [mysqladmin] user=root password=pass [mysql] user=root password=pass [mysqldump] user=root password=pass
* Permissions: 600
Linux Load config 的次序:
- /etc/my.cnf
- /etc/mysql/my.cnf
- $MYSQL_HOME/my.cnf
- [datadir]/my.cnf
- ~/.my.cnf
Window Configure file 優先次划序
# First Win
- --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini"
- C:\WINDOWS\my.ini
- C:\my.cnf
init_connect
A String containing one or more SQL statements
to be executed by the server(setting) for each client that connects.
Usage
- my.cnf: init_connect="string"
- CLI: --init-connect="string"
i.e.
[mysqld]
init-connect='SET NAMES utf8'Checking
show variables like 'init_connect';
mysqld_safe
mysqld_safe adds some safety features such as restarting the server when an error occurs and
logging runtime information to an error log file.
Default:
[mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0
mysqld_safe reads all options from the [mysqld], [server], and [mysqld_safe] sections in option files.
nice=N
Use the nice program to set the server's scheduling priority to the given value
令 mysql 行得象 root 快
nice = -1
open_files_limit=N
# script: /usr/bin/mysqld_safe load 這個 setting 的
# It call ‘ulimit -n’ to set the limit in the shell before starting mysqld as extra instance
# 當這 value 太細時, mysql 就會有以下 Error
SQLSTATE[HY000]: General error: 23 Out of resources when opening file './databasename/tablename#P#p0.MYD' (Errcode: 24)
Check
SHOW VARIABLES LIKE 'open%';
+------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 2048 | +------------------+-------+
cat /proc/$(pgrep mysqld$)/limits
Max open files 1505
P.S.
* The open_files_limit option does one thing. It sets the soft limit for open files (max files) but it can never be set higher than the hard limit
* CANNOT set your open_file_limit to be higher than the hard limit configured in the operation system.
PROCEDURE & PROCEDURE
# List MySQL stored procedures
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
Column:
- Db
- Name
- Type
- Definer
- Modified
- Created
- Security_type
- Comment
- character_set_client
- collation_connection
- Database Collation
# 在某 DB 的 PROCEDURE
SHOW PROCEDURE STATUS WHERE Db = 'mydb'
# Show the SQL code in the stored procedure
SHOW CREATE PROCEDURE mysproc
Usage:
If the DEFINER attribute is omitted from a stored program or view definition,
the default account is the user who creates the object
If the SQL SECURITY characteristic is omitted, the default is definer context.
SECURITY characteristic with a value of DEFINER or INVOKER
to specify whether the object executes in definer or invoker context.
A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges.
The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
UPDATE t1 SET counter = counter + 1;
END;p2 executes in INVOKER security context.
The DEFINER attribute is irrelevant and p2 executes with the privileges of the invoking user.
p2 fails if the invoker lacks the EXECUTE privilege for p2 or the UPDATE privilege for the table t1.
procedure 與 function 的分別
A stored routine is either a procedure or a function.
A procedure is invoked using a CALL statement and can only pass back values using output variables.
Function must return one values.
A function can be called from inside a statement just like any other function and can return a scalar value.
Procedure return multiple values. (max 1024)
* Stored procedure returns always integer
Remark:
ERROR 1227 (42000) at line 31: Access denied; you need the SUPER privilege for this operation
* You can specify a DEFINER value other than your own account only if you have the SUPER privilege.
A trigger always executes in DEFINER context and
It is activated by access to the table with which it is associated,
even ordinary table accesses by users with no special privileges.
查看情況
SHOW VARIABLES LIKE '%max%'
mysql> show status like '%onn%';
+--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | Aborted_connects | 1 | | Connections | 17141345 | | Max_used_connections | 104 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 4 | +--------------------------+----------+ 7 rows in set (0.00 sec)
* Many status variables are reset to 0 by the "FLUSH STATUS" statement.
SHOW [GLOBAL | SESSION] STATUS statement
........ 291 rows
show global status like 'uptime'
+---------------+----------+ | Variable_name | Value | +---------------+----------+ | Uptime | 15597308 | +---------------+----------+
show processlist;
Show processlist show full query
SHOW FULL PROCESSLIST
mysqladmin status
Uptime: 598 Threads: 4 Questions: 241391 Slow queries: 0 Opens: 127 Flush tables: 1 Open tables: 64 Queries per second avg: 403.663
extended-insert
phpMyAdmin
When exporting data in phpMyAdmin, it prepares single insert operations with multiple values.
Depending on the number of records in a table, this query could be very long.
If the physical size of the query exceeds the max_allowed_packet parameter in MySQL, the query would break.
For this purpose, phpMyAdmin gives you the option to limit the size of individual queries, splitting long insert queries into several smaller ones.
mysqldump
mysqldump option for "Maximal length of created query"
This will limit the length of the generated queries.
# mysqldump you typically produce "extended INSERT"(--extended-insert, -e) queries
# --skip-extended-insert = Turn off extended-insert
# test
mysqldump --skip-extended-insert --max_allowed_packet=2M .. > file.sql
TEMPORARY
A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed.
This means that two different sessions can use the same temporary table name without conflicting with each other or
with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)
To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.
Example 1:
CREATE TEMPORARY TABLE t1 ( address VARCHAR(255) NOT NULL , goto text NOT NULL );
Example 2:
CREATE TEMPORARY TABLE t1 (select * from t2);
