52, set the change root password, connect MySQL, mysql common command

52, set the change root password, connect MySQL, mysql common command

First, set the change root password

The root user is the MySQL Super Admin user, the root and system root is not a user, need to partition, you can also create a normal user to connect to MySQL.

The default MySQL root user password is empty and can be connected directly.

# PS aux |grep mysql first check MySQL there is no open

#/etc/init.d/mysqld Start

# Mysql-uroot

-bash:mysql: Command not found

Because the MySQL command is not in the environment variable, this command cannot be used directly.

# ls/usr/local/mysql/bin/mysql mysql command path

/usr/local/mysql/bin/mysql

# echo $PATH no path to MySQL command in environment variable

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

# Export path= $PATH:/usr/local/mysql/bin/Change environment variable, add a

# Mysql-uroot to use it again.

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

Your MySQL Connection ID is 1

Server version:5.6.36 MySQL Community Server (GPL)

Copyright (c), 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.

Mysql>

To make this order permanent, put the order under the/etc/profile, and put it on the last side of the line.

# After the source/etc/profile, you need to execute this one. Causes the variable to take effect immediately.

# mysql-uroot-p//-p, specify the login password for root, now is empty, directly enter.

In this inside can operate MySQL behavior, first quit quit.

# mysqladmin-uroot password ' 123456 '

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

Warning: Using a password on the command-line interface may not be secure.

Log in again when you need to enter the login password, or will not be logged in.

Change password when you know your password:

# mysqladmin-uroot-p ' 123456 ' password ' 1234 '

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

This tip does not need to be the same as the above setting the password.

# mysql-uroot-p ' 1234 ' login, specify the login password with single quotation marks, to prevent some symbols command line is not recognized.

Change password when you do not know the password:

# VIM/ETC/MY.CNF

[Mysqld] In this part of MYSQLD

skip-grant Add this line, meaning to ignore the authorization, no user name password can be directly logged in.

Datadir=/data/mysql

Socket=/tmp/mysql.sock

#/etc/init.d/mysqld Restart Restart service to take effect

# Mysql-uroot directly to enter, to change a user table inside.

User table: The table in which the user's password is stored.

mysql> use MySQL; Use MySQL, switch to MySQL library.

Mysql> select * from user; Look at this table.

Mysql> select password from user;

+------------------------------------------------------------------------+

| password |

+------------------------------------------------------------------------+

| *A4B6157319038724E3560894F7F932C8886EBFCF |

| Here you can see the encrypted string of the password |

| |

| |

| |

| |

+------------------------------------------------------------------------+

6 rows in Set (0.00 sec)

This password is actually generated using the password function, so you need to use this function to encrypt the password.

Use this command to change the password:

Update user set Password=password (' 12345 ') where user= ' root ';

Query OK, 4 rows affected (0.08 sec)

Rows Matched:4 Changed:4 warnings:0

So the password is changed, then quit quit, but also need to add the skip-grant deleted.

#/etc/init.d/mysqld Restart

# do not add a space after mysql-uroot-p12345-p, add the space is wrong.

Second, connect MySQL

# mysql-uroot-p12345 Connect this machine

# mysql-uroot-p12345-h127.0.0.1-p3306

Connect remote,-H to specify the destination ip,-p specified port, such as from a server to connect B server's MySQL, here to connect this machine for demonstration, if not specify the port default is 3306.

# Mysql-uroot-p12345-s/tmp/msyql.sock

Using the sock connection, this situation is only appropriate for the native.

# MYSQL-UROOT-P12345-E "Show Databases"

after connecting to MySQL, operate some commands, this command is list all the databases . . This situation is used in shell scripts.

Third, MySQL common commands

Query library show databases;

Switch the library use MySQL;

View the table in the library show tables;

View the fields in the table desc tb_name; Example: DESC user;

View Build Table statement Show create TABLE tb_name\g; \g: Vertical display , the Vanguard select * from User\g, can also add \g, the display is very clear.

View the current user Select User ();

View the database you are currently using Select Database ();

Some commands are inside MySQL and cannot be executed outside of MySQL, Linux is not recognized.

# mysql-uroot-p12345-h192.168.93.130 using another IP login to view it is not localhost, the user name of the login will be parsed.

Mysql> Select User ();

+----------------+

| User () |

+----------------+

| [Email protected] |

+----------------+

1 row in Set (0.01 sec)

Mysql> Select Database ();

+------------+

| Database () |

+------------+

|  NULL | The current database is empty and MySQL is displayed when you switch to MySQL.

+------------+

1 row in Set (0.00 sec)

mysql command history files:. mysql_history

Creating a library Create database db1;

CREATE TABLE use db1;create table T1 (' id ' int (4), ' name ' char (40));

View current database version select version ();

View database status Show status;

View each parameter show variables;

view the specified parameters show variables like ' max_connect%  '; Here% is a wildcard.

Modify parameter set global max_connect_errors=1000;

You can modify the parameters by modifying the/ETC/MY.CNF, or you can temporarily modify the memory to take effect.

View Queue show processlist;show full processlist;

This is equivalent to the PS or top command of Linux, plus full is more complete, to see what the current MySQL is doing, which users are connected, when he connected it in the execution of what operation, there is no lock table.

mysql> CREATE DATABASE db1;

Query OK, 1 row affected (0.29 sec)

mysql> use DB1;

Database changed

mysql> CREATE TABLE T1 (' id ' int (4), ' name ' char (40));

Query OK, 0 rows affected (0.40 sec)

The table name is T1, which defines the field ID and name, the format is int and char, and the string is 4 and 40 long.

Mysql> Show CREATE TABLE t1\g; View the T1 statement

1. Row ***************************

Table:t1

Create table:create Table ' T1 ' (

' ID ' int (4) DEFAULT NULL,

' Name ' char (+) DEFAULT NULL

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.28 sec)

ERROR:

No query specified

InnoDB's engine, Latiin1 's character set, this can be customized.

CREATE TABLE T1 (' id ' int (4), ' name ' char (+) ') Engine=innodb DEFAULT Charset=utf8;

The command preceded by a # will not take effect.

mysql> drop table T1; Delete this table first

Then execute this command that defines the character set:

CREATE TABLE T1 (' id ' int (4), ' name ' char (+) ') Engine=innodb DEFAULT Charset=utf8;

Query OK, 0 rows affected (0.34 sec)

Mysql> Show CREATE TABLE t1\g; Looking at it again shows the UTF8.

1. Row ***************************

Table:t1

Create table:create Table ' T1 ' (

' ID ' int (4) DEFAULT NULL,

' Name ' char (+) DEFAULT NULL

) Engine=innodb DEFAULT Charset=utf8

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql> select version (); View Database Version 5.6.36

+-----------+

| Version () |

+-----------+

| 5.6.36 |

+-----------+

1 row in Set (0.00 sec)

52, set the change root password, connect MySQL, mysql common command