MySQL installation, initialization, and use of MySQL-related commands

Install MySQL:
1.yum
#wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm download mysql repo from MySQL official website
#yum-y Install Mysql-server

2. Binary Source Package
#tar-XF mysql-xxx.tar.bz2
#cd mysql-xxx
#make && make Install

3. Source code compilation and installation
See installation of MySQL in lamp.


MySQL Initialization configuration process:
The configuration must be initialized after the installation is complete.
Run the scripts/mysql_install_db script to complete the initialization.
or execute the mysql_install_db command to complete the initialization.

1. Find the configuration file my.cnf
MY.CNF exists in more than one directory, MySQL boot will look for the configuration file in the following order, if the options in multiple profiles are the same, the last configuration file containing the option is the content.

On Unix-like systems:
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL _home/my.cnf MySQL instance directory, MySQL can run multiple instances, MySQL in multiple folders as long as the listener on different ports.
--defaults-extra-file=file MySQL start specifying the option--default-extra-file, manually specify the configuration file.
~/.my.cnf hidden files in the user home directory

On win system:
%windir%\my.ini,%windir%\my.cnf
C:\my.ini,c:\my.cnf
%installdir%\my.ini,%installdir%\my,cnf
--defaults-extra-file=dir MySQL start specifying the option--default-extra-file, manually specify

2. Automatically generate the following databases:
Mysql
Store database account, permissions, and other related information.

Information_schema
MySQL runs the information that is generated in the run-time data in memory.

Performance_schema
Store the collected server performance parameters. Added Performance_schema engine after 5.5.


Configuration after initialization:
1. Change the default account and password:
A user who generates 5 empty passwords after MySQL initialization:
3 Root accounts:
[Email protected]
[Email protected]
[Email protected]
2 Anonymous accounts:
"@localhost
"@HOSTNAME
Delete the anonymous user and set the password for the root user.


MySQL communication:
MySQL is a client program, MYSQLD is a service-side program
If MYSLQL and mysqld are on the same Unix-like host, the communication between the two is done using Mysql.sock to complete interprocess communication.
If on the same win host, the communication is done through shared memory or a pipeline.

If it is not on the same host, it communicates through TCP/IP. Note that, even on the same host, once the host is designated as an IP address that is not local, MySQL will also use TCP/IP to communicate.

Client Tools:
are set by the [client] in the configuration file, and the options are broadly the same.

Mysql
Mysqlimport
Mysqldump Backup Tool
Mysqladmin Management Tools
Mysqlcheck Check Database integrity.

Non-client components
MYISAMCHK Check MyISAM table
Myisampack Compressed MyISAM table

Server-side tools:
mysqld MySQL server-side program
Mysqld_safe Thread-MYSQLD server program
Mysqld_mulit support for multi-instance mysqld server programs



MySQL Client tool:
#mysql [OPTION]
[OPTION]
-A--noauto-rehash--disable-auto-rehash disable name completion
-U--user USERNAME does not specify default to root
-P--password ' password ' does not specify NULL by default
-H HOSTNAME does not specify default to localhost
--protocol PROTO Specify protocol, only TCP is the protocol, the remaining sockets, memory, pipe is actually a communication mechanism. Using the socket is not specified by default.
--port Port specified. Use with the--protocol option. Do not specify the port by default socket mode, but also to see whether the client and the server is a unified host.
-D--database DBNAME Setting the default database
--compress compress all transfer data between the client and the server
--ssl-ca=file specifying the CA certificate file
--ssl-capath=dir If you have more than one trusted CA, place all the certificates in one directory.
--ssl-cert=file to specify your own certificate
--ssl-cipher=cipher_list specifying the list of cryptographic algorithms
--ssl-key=file specify your own secret key file
--ssl-verify-erver-cert whether to validate the server-side certificate, which is off by default.
-H--html output table in HTML format
-X--xml output table in XML format
-E "statment" executes the corresponding MySQL statement


#mysqladmin [OPTION] COMMAND [ARG] [Command[arg]] ...
[OPTION]
Same as MySQL
COMMAND
PAASOWRD ' PASSWD ' Set the password to PASSWD
Create DBNAME database named DBNAME
Debug sends related information to error log
Drop DBNAME Delete a database named DBNAME
Ping to test whether the MySQL server is online
Processlist displaying a list of processes (threads) executing
Status shows the state of the MySQL server
--sleep sec specified interval of seconds for sec
--count NUM Specifies the total number of times displayed
Extended-status displaying the server's status variables
Variables to display Server service variables
Flush-hosts refreshes host-related information. The server will record the relevant information, the host will be a corresponding limit, for example, too many attempts to prohibit landing, to prevent attacks. After the refresh, you can log in normally.
Flush-logs Refresh log for binary and relay log scrolling
Flush-privileges let the server reread the authorization form
Flush-tables Close all open tables
Flush-threads reset thread pool, clearing idle threads
Flush-status Reset most of the state variables
Reload equivalent to Flush-privileges
Reflush equivalent to flush-hosts and flush-logs simultaneous execution
Shutdown shut down the MySQL server process.
Version displays the build number and related information for the MySQL server.
Start-slave start replication, start a replication thread from the server
SQL thread
IO thread
Stop-slave shutting down replication, stopping the replication thread from the server

Other client Tools:
PhpMyAdmin
Workbench
MySQL Front


MySQL Mode of operation:
Interactive mode:
Mysql>
Client commands
Executes only on the client.
Server-side commands
The command is sent to the server side, and the result is returned to the client after the service period executes. Must use the statement terminator, which defaults to a semicolon (;).

Batch mode (script mode):
MySQL < file.sql loading SQL scripts directly

The script can also be loaded in interactive mode:
Mysql>source|\. File.sql

MySQL Command mode:
Client command:
Clear (\c) prematurely terminates statement execution
Status (\s) display state
Delimiter (\d) string Set statement terminator to string
Connect (\ r) Reconnect the database and display the connection ID number and the current default database
Ego (\g) ignores the statement terminator and is sent directly to the server for execution, and the results are displayed in a vertical fashion.
Go (\g) ignores the statement terminator and sends it directly to the server for execution
Quit (\q) quit
Source (\.) Execute SQL Script
System (\!) Execution shell command
Warnings (\w) statement after execution, if there is a warning message is displayed
Nowaring (\w) statement does not display a warning message after execution finishes
Prompt (\ r) Modify prompt
Rehash (\#) for newly created objects, enable the name completion feature

Server statement:
Use the statement terminator to end with a semicolon. can be customized.
Help KEYWORD get the KEYWORD information.


Prompt in interactive mode:
Mysql> can be entered
Statement is not finished, you can continue typing
' > Missing half of single quotes
"> Missing half of the double quotes
' > Missing half of the back quotes
/*> The latter half of a multiline comment is missing


Start the MySQL service
#service mysqld Start

Or
#mysqld_safe &

MySQL configuration file:
Configuration file Lookup Order:
/ETC/MY.CNF--
/ETC/MYSQL/MY.CNF--
$BASEDIR/MY.CNF--
The $BASEDIR is the running directory for the MySQL instance, typically the installation directory.

$DATEDIR/MY.CNF--> Data Catalog.

~/.MY.CNF User home directory.

The final configuration is based on the last one, and the latter profile overwrites the previous configuration file.

To view the parameters and default values that the configuration file can use:
#mysqld--help--verbose

My.cnf
A centralized configuration file that can be configured for multiple programs.
Format:
[MySQL]
Xxxx
[Mysqld]
Xxxx
[Client]
Xxxx


/etc/my.cnf
[Mysqld]
Port =3306
Socket =/tmp/mysql.sock
thread_concurrency=8 thread concurrency, number of CPUs
DATADIR=/VAR/LIB/MYSQL specifies the data location.


~/.MY.CNF:
Suppose MySQL only allows local accounts to log on, users do not want to enter the account password when landing, you can use the following methods:
#vim ~/.my.cnf
[Client]
user = ' root '
Password = ' PASSWD '
host = ' localhost '

Note: This method has security implications and is used with caution.


MySQL cannot start the workaround:
MySQL generates a Hostname.err error date to the file in the data directory DataDir.

1. Unable to start and cannot find the Hostname.err file
Data Directory DataDir point to Error, modify profile correction directory.
#vim/etc/my.cnf
Datadir=datadir

2. Previous service not closed, port occupied
The process that kills the socket file releases port 3306.
#ps-aux|grep mysqld
#kill PID

3. Initialization of data failed
Re-executing the initialization script
#cd/usr/local/mysql
#scripts/mysql_install_db--user=mysql--datadir=/sqld/mdata

4. Data Directory Permissions issues
#chmod-R Mysql:mysql DataDir

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1680973

MySQL installation, initialization, and use of MySQL-related commands