MySQL Basic operations Summary

One, MySQL service operation

Single instance:

(1) Start and close MySQL

[[email protected] ~]$/etc/init.d/mysql start

[Email protected] ~]$ NETSTAT-LNT | grep 3306

TCP 0 0::: 3306:::* LISTEN

[[email protected] ~]$/etc/init.d/mysql stop

Note:/etc/init.d/mysql by mysql-5.6.20.tar.gz extract directory/mysql-5.6.20/support-files/mysql.server.sh copy

(2) Safe mode start (production environment is not recommended)

[Email protected] home]#/usr/local/mysql/bin/mysqld_safe--user=mysql &

[Email protected] home]# 151207 12:16:22 mysqld_safe Logging to '/usr/local/data/mysql.err '.

151207 12:16:22 Mysqld_safe starting mysqld daemon with databases From/usr/local/data

[Email protected] home]# NETSTAT-TLNP | grep 3306

TCP 0 0::: 3306:::* LISTEN 2317/mysqld

[Email protected] home]# killall mysqld or kill-9 mysqld

Run multiple times until the display: Mysqld:no process killed

Multi-instance:

(1) Start and close MySQL

/data/3306/mysql start

/data/3307/mysql start

/data/3306/mysql stop

/data/3307/mysql stop

Need to write the startup script yourself

Second, login MySQL method

(1) Single Instance

[[email protected] home]# MySQL just installed complete no password situation login mode

[[email protected] home]# Mysql-uroot just installed to complete no password situation login mode

[[email protected] home]# MYSQL-UROOT-P Standard DBA login mode to avoid password being seen

[[email protected] home]# mysql-uroot-p ' 111111 ' non-scripting generally not used to avoid password being seen

mysql> default prompt symbol after landing

(2) Multi-instance:

[Email protected] run]# mysql-uroot-p-s/usr/local/data/mysql.sock

[Email protected] run]# mysql-uroot-p-s/usr/local/data1/mysql.sock

Specify different sock files by mysql-s command to log in to different services

(3) Display information after login

[[email protected] home]# mysql-uroot-p login mode

Enter Password: password input

Welcome to the MySQL Monitor. Commands End With;  or \g. mysql command with; or \g End

Your MySQL Connection ID is 4

Server Version:5.6.20-log Source Distribution current server version

Copyright (c), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help.  Type ' \c ' to clear the current input statement. View Help information

Mysql>

Note: The above information is useful to have a visual understanding of MySQL.

(4) Use MySQL's help skillfully

If authorized:mysql> Help Grant

GRANT all on db1.* to ' Jeffrey ' @ ' localhost ' identified by ' mypass ';

Mysql> GRANT All on * * to ' tangbo ' @ ' localhost ' identified by ' 111111 ';

Query OK, 0 rows affected (0.12 sec)

(5) Exit MySQL

Mysql> quit or exit

Third, modify the root password of MySQL

(1) After installing MySQL, the default root password is empty, very insecure, so to set the password

Single instance: [[email protected] ~]# mysqladmin-uroot password ' 111111 '

Warning:using a password on the command line interface can is insecure.

Warning: Password using the command line interface is not secure

Multi-instance: [[email protected] ~]# mysqladmin-uroot-p ' 111111 ' password ' 111111 '-s/usr/local/data/mysql.sock

(2) Modify the root password method one

[Email protected] ~]# mysqladmin-uroot-p ' 111111 ' password ' Tangbo '

[Email protected] ~]# mysqladmin-uroot-p ' 111111 ' password ' 111111 '-s/usr/local/data/mysql.sock

(3) Change root password method two

mysql> Update Mysql.user Set Password=password ("Tangbo") where user= ' root '

mysql> flush Privileges;

(4) Change root password method three

After MySQL installation is complete, the root default password is empty, you need to change the root password immediately

Mysql> set Password=password (' Tangbo ');

Query OK, 0 rows affected (0.02 sec)

Production environment, passwords must be complex ********************

Iv. retrieving the missing MySQL root password

Single Instance Startup method:

Killall mysqld

[Email protected] ~]# Mysqld_safe--skip-grant-table &

Mysql-u root-p

or write it to the config file my.cnf:skip-grant-tables

Multi-Instance Startup method:

Killall mysqld

[Email protected] ~]# mysqld_safe--defaults-file=/data/3306/my.cnf--skip-grant-table &

Mysql-uroot-p-s/data/3306/mysql.sock Login Time Password

This article from "Bobcat" blog, declined reprint!

MySQL Basic operations Summary