最後更新: 2022-11-22

目錄

• Get Shell Default Settings
• Per user config
• MySQL 8 user login
• Shell Command

 

---

Get Shell Default Settings

 

# Get default settings

mysql --print-defaults

# Set character-set

mysql would have been started with the following arguments:

--default-character-set=utf8

A MySQL program started with the --no-defaults option reads no option files other than .mylogin.cnf

 => Client programs read the login path file even when the --no-defaults option is used.

 => This permits passwords to be specified in a safer way than on the command line even if --no-defaults is present.

 

---

Per user config

 

~/.mylogin.cnf v.s. ~/.my.cnf

• ~/.my.cnf             # User-specific options
• ~/.mylogin.cnf     # 只保存 login用
                            # 先 load [mysql] 之後 load [client], 最後 [mypath]

.mylogin.cnf

Each option group in .mylogin.cnf is called a “login path”

which is a group that permits only certain options: host, user, password, port and socket.

# 設定 login-path

mysql --login-path=mypath

mysql_config_editor

The mysql_config_editor utility enables you to store authentication credentials in an obfuscated login path file named .mylogin.cnf.

The unobfuscated format of the .mylogin.cnf login path file consists of option groups, similar to other option files.

Each option group in .mylogin.cnf is called a “login path,”

which is a group that permits only certain options: host, user, password, port and socket.

Print settings

mysql_config_editor print --all

Opts

• help
• print [--all]     #
• set [--help]      #
• remove            # mysql_config_editor remove --login-path=mypath
• reset             # Empty the contents of the login path file.

設定

mysql_config_editor set --login-path=root --user=root --host=mysql80 -p

mysql_config_editor print --login-path=root

# unobfuscated output

[root]
user = "root"
password = *****
host = "mysql80"

 

---

Shell Command

 

mysql> \! ls -l

mysql> \! bash

("exit" or Ctrl+D)

 

 

?                   

# help

clear(\c)       

# clear an unexecuted MySQL query

edit(\e)          

# Edit command with $EDITOR.

go(\g)            

# Send command to mysql server.

--table or -t    

# Table view

ego (\G)          

# Send command to mysql server, display result Vertical Format.

pager (\P)       

# Set PAGER [to_pager]. Print the query results via PAGER.

You can use it to write to a file and the results go only to the file:

mysql> pager cat > /tmp/log.txt

mysql> pager less -n -i -S

 

status (\s)

Get status information from the server.

tee (\T)

Set outfile [to_outfile]. Append everything into given outfile.

 

charset (\C)

Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

system (\!)

Execute a system shell command.

source (\.)

Execute an SQL script file. Takes a file name as an argument.

i.e.

source /root/test_db.sql