Apply MySQL note-mysql management tools

A variety of MySQL administration Tools
<>mysql provides command-line tools
mysql_install_db : MySQL Build library tool, in the source installation MySQL link we used.
Mysql_safe : MySQL boot tool
mysqld : MySQL master process, Mysql_safe is also called mysqld process. Starting to shut down a database, query or modify data, perform various maintenance operations, and so on, is actually based on MYSQLD process operations.
system parameters : Parameters for MySQL service startup
System Variables : MySQL service runtime parameters. For example, check the system variables associated with log using show global variables like ' log% '; some of the system variables can be dynamically adjusted without restarting MySQL, and some are not.
For example, modify the MySQL database to recognize that the binary log format is in row format. Then directly modify the system variable set global Binlog_format=row;
Use show global variables like ' binlog_format '; see if system variables are modified. System variables are divided into global and session (session). No matter the global or session level is set to expire after the next MySQL service restart, only add in the initialization file can be permanently enforced.
State variables: state variables record the system state of the MySQL service. State variables are also categorized as global and session, which records the state of the entire MySQL service, and the latter only represents the state of the current session. View MySQL's status variables: Show global status;
The system and system variables are not completely 1:1 relationships, there are parameters but no variables, or there are no corresponding parameters of variables.

<>mysqld_multi: MySQL Multi-instance management tool needs to add different mysqld chunks in my.cnf. The Mysqld_multi--example can be used for reference. This command is dedicated to scenarios where multiple MySQL instances are running on a single server
Start [Mysqld34] this block: Mysqld_multi start 34;
Stop [MYSQLD6] [MYSQLD7] [Mysqld8] three blocks: Mysqld_multi stop 6-8;

<>mysql Command：
--auto-rehash: Press the TAB key to complete the command
--default-character-set: The character set used to specify a connection session
The-e,--execute:mysql command supports two modes of operation, interactive and non-interactive. Interactivity is the introduction of actions to be performed at the MySQL command prompt, which specifies the statement to execute when the MySQL command is executed in a non-interactive way. Like what:
[email protected] scripts]$ mysql-usystem-poralinux-s/mysql/conf/mysql.sock-e "show slave Status\g"
Warning:using a password on the command line interface can is insecure.
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.1.6
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000024
...
...
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0

-F,--Force: When executing SQL statements in a non-interactive mode (or executing a file that contains SQL statements), if there is an error in an SQL statement to execute, by default all statements after the change statement are no longer executed. For example:
[[email protected] scripts]$ mysql-usystem-poralinux-s/mysql/conf/mysql.sock-e "drop table aa.bbccdd;show slave S Tatus\g "
Warning:using a password on the command line interface can is insecure.
ERROR 1051 (42S02) at line 1:unknown table ' AA.BBCCDD '
It is reported that there is an unknown table object, and the subsequent statement that looks at the slave state is not executed. Sometimes we want the wrong information to be automatically ignored to continue executing the subsequent statement, when the-f parameter comes in handy.
[[email protected] scripts]$ mysql-usystem-poralinux-s/mysql/conf/mysql.sock-e "drop table aa.bbccdd;show slave S Tatus\g "-F
Warning:using a password on the command line interface can is insecure.
ERROR 1051 (42S02) at line 1:unknown table ' AA.BBCCDD '
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.1.6
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000024
read_master_log_pos:120
relay_log_file:mysql-relay-bin.000011
...
...
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0

--show-warings: Displays a warning message immediately after executing the statement, which is equivalent to automatically executing the show warings after executing the SQL statement;

[Email protected] (none) >help

For information on MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must is first on line and end with '; '
? (\?) Synonym for ' help '.
Clear (\c) Clear the current input statement.
Connect (\ r) reconnect to the server. Optional arguments is DB and host.
Delimiter (\d) Set statement delimiter.
Edit command with $EDITOR. \e.
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.
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 is 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 '

Pager is a pipe character in MySQL command-line mode.
Pager more can be split screen display. Pager is followed by an operating system command, which can be replaced by any other command as required. Nopager Cancel

Prompt modifying the current command line prompt
Status pays special attention to the last two lines
Tee similar to spool in Oracle

<>mysqladmin Management Tools
Common parameters:
-I,--sleep=#: Call this mysqladmin command again after a specified time has been built
-R,--Relative: When used in conjunction with the-I parameter and the Extended-status command is specified, displays the difference between the status values for this and the last time.
Common commands:
1, create dbname:mysqladmin-usystem-p ' oralinux '-s/mysql/conf/mysql.sock create TestDB
2. Drop dbname:mysqladmin-usystem-p ' oralinux '-s/mysql/conf/mysql.sock drop TestDB
3. Extended-status: View the status information of the server, as in the MySQL command-line mode to perform the function of show global status:
Mysqladmin-usystem-p ' Oralinux '-s/mysql/conf/mysql.sock extended-status
4. Flush-hosts: Refresh cache information
5. Flush-logs: Refresh Log
6. Flush-status: RESET state variable
7. Flush-tables: Refresh All tables
8, Flush-privilege: Reload the authorization table, the function and the reload command exactly the same
9, Reload
10. Refresh: Refreshes all tables and toggles log files
11, Password [New-password]: Modify the password of the specified user, the function is identical to the SET password statement.
12, Old-password [New-password]: Modify the specified user password, just follow the old format modified
12, Ping: Check the current MySQL service is still able to provide services.
Mysqladmin-usystem-p ' Oralinux '-s/mysql/conf/mysql.sock ping
13, Debug: Output the current MySQL service debugging information to the Error.log file, in some cases, performance analysis or troubleshooting is very practical.
14. Kill ID, ID 、... : Kills the thread connected to the MySQL service with exactly the same function as the Kill ID statement
15. Processlist: View the connection thread information used by the current MySQL service, the function is exactly the same as show processlist; statement
16. Shutdown: Close database service
17, Status: View current MySQL status
[Email protected] ~]$ mysqladmin-usystem-p ' oralinux '-s/mysql/conf/mysql.sock status
Warning:using a password on the command line interface can is insecure.
Mysqladmin:unknown OS Character set ' GB18030 '.
Mysqladmin:switching to the default character set ' UTF8 '.
uptime:3684 threads:3 questions:60 Slow queries:0 opens:82 Flush tables:1 Open tables:75 queries per second avg:0. 016
The following are some of the monitoring metrics:
Uptime:mysql Service start-up time
Thread: Number of sessions currently connected
Questions: Number of query statements executed since MySQL service started
Slow queries: Number of slow query statements
Opens: The number of Table objects that are currently open
Flush tables: Number of flush-*, refresh, reload commands executed
Open tables: Number of Table objects opened by the current session
Queries per second avg: Frequency of query execution
Start-salve
Stop-slave
Variables: Display system variable function with show global variables; statement exactly the same
version, viewing the release information also includes information about the status command

Outputs status information for the current MySQL service every second
Mysqladmin-usystem-p ' Oralinux '-s/mysql/conf/mysql.sock-i 1 status
Number of queries executed per second
Mysqladmin-usystem-p ' Oralinux '-s/mysql/conf/mysql.sock-i 1-r extended-status |grep-e "Com_select"

<>phpmyadmin
XAMPP: A variety of related packages for MySQL, Perl, PHP encapsulation, and phpmyadmin,:http://www.apachefriends.org/zh_cn/xampp.html
1, Decompression: TAR-XVFZ xampp-linux-c/opt
2, vi/opt/lampp/etc/extra/httpd-xampp.conf
Add one line to the/opt/lampp/phpmyadmin label (between lines 16-19): Require all granted.
Change the 62nd line in the Locationmatch tag: Deny from all to allow by all to enable other servers to access phpMyAdmin
3. Start Service:/OPT/LAMPP/LAMPP start
4. Access via browser

<>mysql Workbench
<> third-party management tools: SQLyog and Navicat

Apply MySQL note-mysql management tools