Installation, Configuration, Login and Basic Awareness of MySQL under linux

MySQL Uninstallation

matrix

Before installing, it is recommended that you uninstall the system to minimize the impact of related factors.

The entire installation process requires root privileges; the

Installation environment centos 7.

Installing version MySQL 5.7.

Architecture x86-64.

Check if MySQL is installed

Check for running processes, and kill them if they exist.

ps axj|grep mysql

Check to see if there are any running SQL services

systemctl list-units|grep mysql

Suspend mysql service

systemctl stop mysqld

Uninstalling the mysql service

A yum installation of mysql is usually a .rpm version, so you can view the mysql installer through the rpm manager

rpm -qa | grep mysql # qa means 'query all'

All uninstallation, you can choose to manually delete one by one, you can also choose a combination of commands to automate the deletion of

rpm -qa | grep mysql | xargs yum -y remove # Feed rpm output to yum via xargs as a command argument.

Check if the uninstallation is clean

After uninstalling mysql, the /etc/ file will also be deleted.

ls /etc/

Historical mysql data is retained by default. You can make a backup if you need to.

sudo ls /var/lib/mysql/

MySQL Installation

View linux version

Check your linux distribution before installing

cat /etc/centos-release

maybe

cat /etc/redhat-release

maybe

uname -a # el for centos

Select MySQL Version

Open the page./

You can find a lot of versions, but the version is too long, display incomplete, right-click and select the view page source code to expand the view!

My server is centos7, so choose the el version; i.e.(centos7 generic version, if there is a corresponding version can also choose)

[noarch_Baidu Encyclopedia ()] (/item/noarch/5351882?fr=aladdin

Getting the official mysql yum source

Use wget to download directly to the server

wget /

Or download directly to the local, and then use other methods such as scp, lrzsz, ftp way to copy over the

rpm install mysql official yum source

After entering the directory where mysql is stored, we need to use the rpm manager to install mysql since we have downloaded the .rpm version of mysql.

yum is like a store for downloading packages, and the actual installation is done by calling rpm. If you have a yum source for downloading mysql, you can install it directly from yum.

You can view all yum sources by ls /etc//.

I don't have mysql-related yum sources on my liunx, so I need to install them.

Installation command.

sudo rpm -ivh 

After the installation, yum also updates the mysql related yum sources.

After that you can install mysql and mysql related tools via yum.

Test that the added yum sources are working properly:

yum list |grep mysql

If mysql-related packages appear, the installation was successful.

Installing the mysql service

sudo yum install -y mysql-community-server

Installation requires about 1G of space, installation time depends on the machine configuration, it will take some time.

Check if the installation was successful

Check if the mysql configuration file exists

ls /etc/

Check if the server-side application exists

which mysqld

sbin:super bin # Services that require hypervisor privileges

View Client Application

which mysql

Check the listening port number, mysql will start up at local Address 3306.

netstat nltp

Configuring MySQL

Simple login mysql commands

First see if it can be used, a simple login test, note that when you first install is not know the password, see below to deal with

Newer versions of mysql may not have a login password for root, just enter to log in.

Other ways such as history view mysql generates temporary passwords, etc. (older versions) :

sudo grep 'temporary password' /var/log/ \

If you can find it, use what you found. If you can't, you'll have to use other methods.

Maybe the temporary passwords are all the same, i.e.waQ,qR%be2(5

Try typing.

mysql -uroot -p ## -u:User, -p for password

exit command

quit

Secure Login Configuration

When you don't know your password, there are many ways to log in online, and this is how you can configure a password-free login.

Just add the following command to the /etc/ file.

skip-grant-tables

Restart mysqld after configuration

systemctl restart mysqld ##Or in two steps, stop and then start.

Then log in to mysql again and enter the password.

After you can log in, you can change your password, and there are many ways to change your password, but currently it is in thekip-grant-tablesUnder the conventional way is not allowed, you can directly modify the user table to change the password; the following statement can be used for the time being

mysql> set global validate_password_policy=0; ## Set to weak password strength.
## Security strength, defaults to Medium, i.e., 1, and requires that it must contain a number, symbol, or small or large name with at least 8 digits.

set global validate_password_length=1; ## Set the minimum password length to 1.

update user set authentication_string=password('123456') where user='root'; ## Directly manipulate the data
or ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

flush privileges; ## flush privileges

You can remove it after changing the passwordskip-grant-tablesConfigured

Other configuration items

## /etc/
## ...

datadir=/var/lib/mysql #Data path for tables, etc.
socket=/var/lib/mysql/ #Internal data, omitted.

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/ #Error logging
pid-file=/var/run/mysqld/ # error logs

# User-defind Add
skip-grant-tables #Shielded settings, restart mysqld to take effect.
port=3306 #mysql service port number
character-set-server=utf8 #Set the character set to utf8.
default-storage-engine=innodb #Set the default storage engine to innodb.


lower_case_table_names=1 #Set mysqld to be case-insensitive (required for linux)
# lower_case_table_names = 0: table names are stored as specified, comparisons are case sensitive.
# lower_case_table_names = 1: table names are stored in lowercase on disk, comparison is not case sensitive.
# lower_case_table_names = 2: table names are stored as specified, but comparisons are not case-sensitive.

Setting boot-up (optional)

systemctl enable mysqld #boot-up self-start
systemctl daemon-reload #reload configuration file

Common commands and noun recognition

Login Command

mysql -h127.0.0.1 -P3306 -uroot ## -h Specify the server ip -P Specify the port number ## Use the default if not specified

Clear Screen Command

mysql> system clear;

Getting to know mysql and mysqld

mysqld is a network service, d means daemon, daemon process; mysqld means mysql database service server-side

mysql is the client of the database service.

mysql is a set of network programs that provide access to data.

A database is essentially data stored on disk or in memory that is organized in a particular structure.

General talk about the database service is expressed mysqld; talk about the database, is the specific data organization file

Forms of database presence in linux

A database was created calledhelloworld

according to/etc/I know, I know, I know.datadiris the data storage directory, default is/var/lib/mysql,Into the directory, you can find that there are a number of directories with the same name as the database, namelyDatabase is a directory in linux

Go to the helloworld directory, and there is only one configuration file.

After creating a table, view the changes

The table is expressed in two files, .frm and .ibd; that is, theA table is a file in the database directory

Create a directory in the database directory, the corresponding mysql will be synchronized with the addition of a database (for testing purposes, the actual can not do so)

SQL Classification

• DDL (data definition language) data definition language, used to maintain the structure of stored data

  Represents commands: create, drop, alter

• DML (data manipulation language) data manipulation language, used to manipulate the data

  Represents commands: insert, delete, update

• DCL(data control language) data control language, mainly responsible for the management of rights and things

  Represents commands: grant, revoke, commit

storage engine

The storage engine is: a database management system how to store data, how to index the stored data and how to update and query the data and other technical implementation methods.
At its core, MySQL is a plug-in storage engine that supports multiple storage engines.

View Storage Engines

show engines;

MySQL commonly used storage engine is basically only two InnoDB or MyISAM; if you need other storage engine, the probability of not choosing MySQL, but to consider other database services.

InnoDB to a powerful index , things function , to facilitate business processes ; MyISAM does not support transactions , but high concurrency is better; .

Storage Engine Comparison

MySQL Architecture

MySQL is a portable database that runs on virtually all current operating systems. The underlying implementation varies from system to system, but MySQL basically guarantees a consistent physical architecture across platforms.

It is mainly used as a web service on servers (linux).

mysqld can be simply divided into 4 layers

help Chinese translation

A list of all MySQL commands:
Note that all text commands must be at the beginning of a line and end with ';'.

? (\?) - Synonym for `help`.
clear (\c) - Clears the currently typed statement.
connect (\r) - Reconnect to the server. Optional parameters include database (db) and host.
delimiter (\d) - Sets the statement delimiter.
edit (\e) - Edit command using $EDITOR.
ego (\G) - Sends the command to the MySQL server and displays the result vertically.
exit (\q) - Quit MySQL. same as quit.
go (\g) - Sends the command to the MySQL server.
help (\h) - Displays this help message.
nopager (\n) - Disables pager, prints to standard output.
notee (\t) - Does not write to the output file.
pager (\P) - Sets the pager [to_pager]. Prints query results through the pager.
print (\p) - Prints the current command.
prompt (\R) - Change your MySQL prompt.
quit (\q) - Quit MySQL.
rehash (\#) - Rebuild the complementary hash table.
source (\...) - Execute a SQL script file. - Executes a SQL script file. Takes a filename as a parameter.
status (\s) - Get status information from the server.
system (\!) - Executes a system shell command.
tee (\T) - Sets the output file [to_outfile]. Appends everything to the specified output file.
use (\u) - Use another database. Requires a database name as a parameter.
charset (\C) - Switch to another character set. May be needed when processing binlogs with multi-byte character sets.
warnings (\W) - Display warnings after each statement.
nowarning (\w) - Do not display warnings after each statement.
resetconnection (\x) - Clears the session context.

immediately put into practice something one has just learned

• ? Command Displays the help file for the corresponding command

  for example? use

• Command \c The current input command is canceled.

• Command \G The results are displayed vertically rather than in a table.

• system shell commands maybe\! shell commands :: Execute system commands

  Commonly used.system clear

• status maybe\s :: Display server information

• source maybe\.: execute sql

• connect [db_name [host]] Reconnect to themy_database database, the connection host ismy_host。

  Example.connect my_database my_host;

• prompt [new_prompt] Change the name of the prompt

  Example.prompt hello> Put the promptmysql> Changed it tohello>