CLIENT

endline options

\g # formated list, is default option ;
\G # inline list

command options

# the MariaDB command-line tool
mysql [options] db_name

# output
  -B, --batch # Print results using tab as the column separator, with each row on a new line
  -E, --vertical # Print query output rows vertically (one line per column value)
  -H, --html # Produce HTML output
  -L, --skip-line-numbers # Do not write line numbers for errors
  -N, --skip-column-names # Do not write column names in results
  -r, --raw # For tabular output, the “boxing” around columns enables one column value to be distinguished from another
  -s, --silent # Silent mode. Produce less output
  -t, --table # Display output in table format
  -v, --verbose # Verbose mode
  -X, --xml # Produce XML output
  --show-warnings # Cause warnings to be shown after each statement if there are any
  --line-numbers # Write line numbers for errors
  -# ., --debug[=debug_options] # Write a debugging log. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysql.trace'
  --binary-mode # By default, ASCII '\0' is disallowed and '\r\n' is translated to '\n'

# connection
  -D ., --database=db_name # The database to use
  -h ., --host=host_name # Connect to the MariaDB server on the given host
  -P ., --port=port_num # The TCP/IP port number to use for the connection or 0 for default to
  -u ., --user=user_name # The MariaDB user name to use when connecting to the server

# used
  -c, --comments # Whether to preserve comments in statements sent to the server
  -C, --compress # Compress all information sent between the client and the server if both support compression
  -e ., --execute=statement # execute statement
  -f, --force # Continue even if an SQL error occurs
  -n, --unbuffered # Flush the buffer after each query
  -w, --wait # If the connection cannot be established, wait and retry instead of aborting
  -V, --version # Display version information and exit
  --delimiter=str # Set the statement delimiter. The default is the semicolon character (“;”)
  --tee=file_name # Append a copy of output to the given file
  --max-allowed-packet=num # Set the maximum packet length to send to or receive from the server. (Default value is 16MB, largest 1GB.)

# others

  --abort-source-on-error # Abort 'source filename' operations in case of errors
  --auto-rehash # Enable automatic rehashing
  --auto-vertical-output # Automatically switch to vertical output mode if the result is wider than the terminal width
  --character-sets-dir=path # The directory where character sets are installed
  --column-names # Write column names in results
  --column-type-info, -m # Display result set metadata
  --connect-timeout=seconds # Set the number of seconds before connection timeout
  --debug-check # Print some debugging information when the program exits
  --debug-info, -T # Prints debugging information and memory and CPU usage statistics when the program exits
  --default-auth=name # Default authentication client-side plugin to use
  --default-character-set=charset_name # Use charset_name as the default character set for the client and connection
  --defaults-extra-file=filename # Set filename as the file to read default options from after the global defaults files has been read
  --defaults-file=filename # Set filename as the file to read default options from, override global defaults files
  --defaults-group-suffix=suffix # In addition to the groups named on the command line, read groups that have the given suffix
  --disable-named-commands # Disable named commands
  -i, --ignore-spaces # Ignore spaces after function names
  --init-command=str # SQL Command to execute when connecting to the MariaDB server
  --local-infile[={0|1}] # Enable or disable LOCAL capability for LOAD DATA INFILE
  --max-join-size=num # Set the automatic limit for rows in a join when using --safe-updates. (Default value is 1,000,000.)
  -G, --named-commands # Enable named mysql commands. Long-format commands are allowed, not just short-format commands
  --net-buffer-length=size # Set the buffer size for TCP/IP and socket communication. (Default value is 16KB.)
  -A, --no-auto-rehash # This has the same effect as --skip-auto-rehash
  -b, --no-beep # Do not beep when errors occur
  --no-defaults # Do not read default options from any option file
  -o, --one-database # Ignore statements except those those that occur while the default database is the one named on the command line
  --pager[=command] # Use the given command for paging query output
  --password[=password], -p[password] # The password to use when connecting to the server
  -W, --pipe # On Windows, connect to the server via a named pipe
  --plugin-dir=dir_name # Directory for client-side plugins # 
  --print-defaults # Print the program argument list and exit
  --progress-reports Get progress reports for long running commands (such as ALTER TABLE)
  --prompt=format_str # Set the prompt to the specified format
  --protocol={TCP|SOCKET|PIPE|MEMORY} # The connection protocol to use for connecting to the server
  --quick, -q # Do not cache each query result, print each row as it is received
  --reconnect # If the connection to the server is lost, automatically try to reconnect
  -U, --safe-updates, --i-am-a-dummy # Allow only those UPDATE and DELETE statements that specify which rows to modify by using key values
  --secure-auth # Do not send passwords to the server in old (pre-4.1.1) format
  --select-limit=limit # Set automatic limit for SELECT when using --safe-updates. (Default value is 1,000.)
  --server-arg=name # Send name as a parameter to the embedded server
  --sigint-ignore # Ignore SIGINT signals
  --skip-auto-rehash # Disable automatic rehashing
  --socket=path, -S path # For connections to localhost, the Unix socket file to use

# ssl connection

  --ssl # Enable SSL for connection (automatically enabled with other flags)
  --ssl-ca=name # CA file in PEM format
  --ssl-capath=name # CA directory
  --ssl-cert=name # X509 cert in PEM format
  --ssl-cipher=name # SSL cipher to use
  --ssl-key=name # X509 key in PEM format
  --ssl-crl=name # Certificate revocation list
  --ssl-crlpath=name # Certificate revocation list path
  --ssl-verify-server-cert # Verify server's "Common Name" in its cert against hostname used when connecting

MySQL Commands

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt:

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'

?         (\?) Synonym for `help'
clear     (\c) Clear command
connect   (\r) Reconnect to the server. Optional arguments are db and host
delimiter (\d) Set statement delimiter
edit      (\e) Edit command with $EDITOR
ego       (\G) Send command to mysql server, display result vertically
exit      (\q) Exit mysql. Same as quit
go        (\g) Send command to mysql server
help      (\h) Display this help
nopager   (\n) Disable pager, print to stdout
notee     (\t) Don't write into outfile
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER
print     (\p) Print current command
prompt    (\R) Change your mysql prompt
quit      (\q) Quit mysql
rehash    (\#) Rebuild completion hash
source    (\.) Execute an SQL script file. Takes a file name as an argument
status    (\s) Get status information from the server
system    (\!) Execute a system shell command
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile
use       (\u) Use another database. Takes database name as argument
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets
warnings  (\W) Show warnings after every statement
nowarning (\w) Don't show warnings after every statement

For server side help, type 'help contents'

option examples

# remove title and format
mysql -uroot -p$pwd -sBe "$cmd"

# upgrade databases
mysql_upgrade -uroot -p$pwd

SQL

create database

CREATE DATABASE $db_name CHARACTER SET = 'utf8' COLLATE = 'utf8_unicode_ci';

# show tables by engine
SELECT ENGINE, COUNT(*) AS count FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE;
# show log variables
SHOW VARIABLES LIKE '%log%';

collation

https://mariadb.com/kb/en/supported-character-sets-and-collations/
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

Change collation for database $db to 'utf8mb4_general_ci', utf8-unicode-ci is more exact but longer

ALTER DATABASE $db COLLATE = 'utf8mb4_general_ci';

privileges

SELECT user,host,password FROM mysql.user WHERE Host <> 'localhost'; # show users
SHOW GRANTS; # show grants
SHOW PRIVILEGES\G; # show privileges

GRANT ALL PRIVILEGES ON *.* TO $user@'$host' IDENTIFIED BY '$pwd' WITH GRANT OPTION; # gives all privileges with grant option to user

GRANT USAGE ON *.* TO `$user`@`%` IDENTIFIED BY '$pwd';
GRANT SELECT, INSERT, UPDATE, DELETE ON `db_name`.* TO `$user`@`%`;

GRANT PROXY ON ''@'%' TO 'root'@'$host' with GRANT OPTION; # gives proxy privileges with grant option to user 

REVOKE ALL PRIVILEGES, GRANT OPTION FROM $user; # revoke all privileges for user

DROP USER IF EXISTS $user1, $user2; # drop users

DUMP

dump all databases in separates files

[ -z "${pwd}" ] && echo -n 'pwd: ' && read pwd
path="/var/share/mariadb/default/dump-$(grep $HOSTNAME /etc/hosts|cut -d' ' -f1)-$(date +%s)"
! [ -d "${path}" ] && mkdir -p "${path}"
for db in $(mysql -u$db_user -p$db_pwd -Bse "SHOW SCHEMAS"); do
    [[ $db =~ _schema$ ]] && opt='--skip-lock-tables' || opt=
    echo "$db"; mysqldump -uroot -p$pwd $db --no-data $opt > "${path}/${db}.sql"
    mysqldump -uroot -p${pwd} ${db} --no-create-info ${opt} | gzip -c > "${path}/${db}-data.sql.gz"
done
echo -e "\e[0;33m$path\e[0;0m"
ls -al "${path}"

data & structure

mysqldump -uroot -p$pwd $db_name --no-data > ${file}.sql # dump only structure
mysqldump -uroot -p$pwd $db_name --no-create-info --ignore-table='$tables' > ${file}-data.sql # dump only data

mysqldump -u$user -p$pwd $db_name --dump-slave --no-data > "${path2}/${file}-struct.sql" # dump only structure for slave in replication
mysqldump -u$user -p$pwd $db_name --dump-slave --no-create-info | gzip -c > "${path2}/${file}.sql.gz" # dump only data for slave replication

SSL

https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/