Set change root password, connect MySQL, MySQL common commands

Set Change root password

1. Add MySQL to the environment variable

[[email protected] ~]# grep mysql/etc/profile

Export path=/usr/local/mysql/bin/: $PATH

2, direct login, no password

[Email protected] ~]# Mysql-uroot

3. Set the password

[Email protected] ~]# mysqladmin-uroot password ' 123456 '

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

You have new mail in/var/spool/mail/root

4. Login

[Email protected] ~]# mysql-uroot-p ' 123456 '-h127.0.0.1-p3306

-H: Specify host

-P: Specify port

5. Change the password to 1234

[[email protected] ~]# mysqladmin-uroot-p ' 123456 ' password ' 1234 '

How do I change my password after I forget it?

1, [[email protected] ~]# VI/ETC/MY.CNF

[Mysqld]

skip-grant# Skip Authorization Login

2. Restart MySQL Service

[Email protected] ~]#/etc/init.d/mysqld restart

3, [[email protected] ~]# Mysql-uroot

mysql> use MySQL;

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

mysql> Update user Set Password=password (' 123456 ') where user= ' root '; #更改密码

4. Restart the MySQL service after skip-grant configuration is removed

/etc/init.d/mysqld restart

5. Changed password

Connect to MySQL

1, [[email protected] ~]# mysql-uroot-p123456-h127.0.0.1-p3306

2. Connect via socket

[Email protected] ~]# Mysql-uroot-p123456-s/tmp/mysql.sock

MySQL Common commands

1. Show all databases

show databases;

2. Which database to enter

User db

3. Display all tables in the database

Mysql> Show tables;

4. Display table structure (what fields are composed)

DESC user;

5. Enquiry

Select Host,user from user;

6. Show the process of building the statement

Show CREATE TABLE user\g;

7. Show which user is logged in

Select User ();

8. Show how many rows the table has

Select COUNT (*) from user;

9. Display variables

Show variables;

10. Find

Show variables like '%error% ';

11. Set Temporary variables

mysql> Set Global max_connect_errors = 1000;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like ' max_connect_errors ';

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

| variable_name | Value |

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

| max_connect_errors | 1000 |

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

11. Show MySQL Process queue

Mysql> show Processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 3 | Root | localhost | MySQL |    Query | 0 | init | Show Processlist |

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

1 row in Set (0.01 sec)

Mysql> Show full processlist; #完整情况

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 3 | Root | localhost | MySQL |    Query | 0 | init | Show Full Processlist |

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

12. New Table, Field

Mysql> CREATE TABLE TB (' id ' int, ' num ' varchar (10));

Query OK, 0 rows affected (0.02 sec)

Mysql> Show Tables

;

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

| Tables_in_test |

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

| TB |

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

1 row in Set (0.00 sec)

Mysql> Show CREATE TABLE TB;

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

| Table | Create Table |

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

| TB | CREATE TABLE ' TB ' (

' id ' int (one) DEFAULT NULL,

' num ' varchar (DEFAULT NULL)

) Engine=innodb DEFAULT charset=latin1 |

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

mysql> desc TB;

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

| Field | Type | Null | Key | Default | Extra |

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

| ID | Int (11) |     YES | |       NULL | |

| num | varchar (10) |     YES | |       NULL | |

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

13. Delete Libraries and tables

drop database test;

drop table TB;

14. Inserting data

Insert TB value (1,4);

This article is from the "Discover new things" blog, make sure to keep this source http://jacksoner.blog.51cto.com/5802843/1982740

Set change root password, connect MySQL, MySQL common commands