MySQL Settings change root password, connect MySQL, common commands

setting, changing the root user password

The first use of MySQL will prompt ' This command is not ' because the command has not been added to the environment variable, if you want to use the command, you need to use its absolute path:/usr/local/mysql/bin/mysql, for convenience, first add it to the system environment variable.

[[email protected] ~]# export PATH=$PATH:/usr/local/mysql/bin/mysql

This variable is invalidated after rebooting the system and will need to be added to the environment variable configuration file for permanent entry:

[[email protected] ~] # vim / etc / profile
...
export PATH = $ PATH: / usr / local / mysql / bin /

Refresh the configuration:
[[email protected] ~] # source / etc / profile 

• Set Password

  First login mysql,root user no password, direct login

  [Email protected] ~]# Mysql-uroot
  -U Specify user logon
  Welcome to the MySQL Monitor. Commands End With; or \g.
  ......
  Mysql>quit
  Bye
  The QUIT command can quit MySQL.

  Set Password:
  [Email protected] ~]# mysqladmin-uroot password ' 123456 '
  Warning:using a password on the command line interface can is insecure.
  There is no error, just hint that the password is displayed in the command line is not xxx full.

  To log in without using a password:
  [Email protected] ~]# Mysql-uroot
  ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)
  Prompt login is denied, requires password.

  Log in with a password:
  [Email protected] ~]# mysql-uroot-p
  Enter Password:
  Welcome to the MySQL Monitor. Commands End With; or \g.
  ......
  -p parameter, log on with a password. The password can be connected after-P. You can also not enter the password after-p, according to the following prompt information input, this method will not expose the user password, more secure.

Note: When the root password is not set to log in to MySQL with the-p parameter, you will be prompted to enter the password, this is the direct return to the line.

• Change Password

  When you know the user password, make a password change:
  [[email protected] ~]# mysqladmin-uroot-p ' 123456 ' password ' 654321 '
  Warning:using a password on the command line interface can is insecure. The
  //warning password is entered at the command line and is not secure. But the password has been modified successfully!

  Login with old password:
  [[email protected] ~]# mysql-uroot-p123456
  warning:using a password on the command line inte Rface can be insecure. The
  //warning password is entered at the command line and is not secure.
  Error 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using password:yes)
  //Prompt login information validation failed with password Error!

  Sign in with the new password
  [[email protected] ~]# mysql-uroot-p654321
  warning:using a password on the command line Inter Face can be insecure. The
  //warning password is entered at the command line and is not secure.
  Welcome to the MySQL Monitor. Commands End With; or \g.
  ...
  mysql>
  //login successfully with new password!

• Password reset

  Use to reset the password when you do not remember the root password.

  To edit a configuration file:
  [Email protected] ~]# VIM/ETC/MY.CNF
  [Mysqld]
  Skip-grant//Ignore authorization!
  ......
  Add code under the Mysqld module: skip-grant

  To restart the MySQL service:
  [Email protected] ~]#/etc/init.d/mysqld restart
  Shutting down MySQL. success!
  Starting MySQL. success!

Note: you will not need a password to log in to MySQL after completing the above operation.

Log in to mysql:
[[email protected] ~] # mysql -uroot
Welcome to the MySQL monitor. Commands end with; or \ g.
...
mysql>
// Log in directly without the -p parameter.

Switch to the mysql library:
mysql> use mysql; // Switch to mysql library
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from user \ G;
// View the user's table information, which stores user-related information (password, authorization ...)
// The role of the G option is to display the output information in an orderly manner. Without this option, the display content will be very messy
mysql> select password from user;
// View the user password and display the result Wie encrypted string!

reset Password:
mysql> update user set password = password (‘112233’) where user = ‘root’;
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4 Changed: 4 Warnings: 0
// Change the password to ‘112233’

Restore the configuration file:
[[email protected] ~] # vim /etc/my.cnf
// Comment out the line before adding skip-grant

Restart the mysql service:
[[email protected] ~] # /etc/init.d/mysqld restart
Shutting down MySQL .. SUCCESS!
Starting MySQL. SUCCESS!

log in:
[[email protected] ~] # mysql -uroot -p‘112233 ’
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with; or \ g.
...
mysql>

Reset Password Step: vim/etc/my.cnf--> add skip-grant-->mysql restart--> login-->use mysql-->update User set password=...-->vim/etc/my.cnf--> Delete skip-grant-->mysql restart.

Connect to MySQL

• Remote connection

  Connect using IP and port number

  [Email protected] ~]# mysql-uroot-p ' 112233 '-h127.0.0.1-p3306
  Warning:using a password on the command line interface can is insecure.
  Welcome to the MySQL Monitor. Commands End With; or \g.
  ......
  Mysql>

  -h=host, specifying Ip,-p=port, specifying port number

• Local connection

  You can connect directly or use a socket connection directly.

  Use the socket Link:
  [Email protected] ~]# mysql-uroot-p ' 112233 '-s/tmp/mysql.sock
  Warning:using a password on the command line interface can is insecure.
  Welcome to the MySQL Monitor. Commands End With; or \g.
  ......
  Mysql>

  -s=socket, specify the socket. This method applies only to local connections. Same as the direct MySQL connection.

• Show all databases after connecting data

  [[email protected] ~]# mysql -uroot -p‘112233‘ -e "show databases"

  Warning:using a password on the command line interface can is insecure.
  +--------------------+
  | Database |
  +--------------------+
  | Information_schema |
  | MySQL |
  | Performance_schema |
  | Test |
  +--------------------+

  The-e parameter can be followed by a MySQL statement.
  This method is commonly used in shell scripts.

Mysql Common Commands

Check which databases are available:
mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| information_schema |
| mysql |
| performance_schema |
| test |
+ -------------------- +
4 rows in set (0.00 sec)

Switch to the mysql library:
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

View the table in Curry:
mysql> show tables;
+ --------------------------- +
| Tables_in_mysql |
+ --------------------------- +
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+ --------------------------- +
28 rows in set (0.00 sec)

View the fields in the table:
mysql> desc user;
+ ------------------------ + ------------------------ ----------- + ------ + ----- + ----------------------- +- ------ +
| Field | Type | Null | Key | Default | Extra |
+ ------------------------ + ------------------------ ----------- + ------ + ----- + ----------------------- +- ------ +
| Host | char (60) | NO | PRI | | |
| User | char (16) | NO | PRI | | |
| Password | char (41) | NO | | | |
| Select_priv | enum (‘N‘, ‘Y‘) | NO | | N | |
| Insert_priv | enum (‘N‘, ‘Y‘) | NO | | N | |
 ...
+ ------------------------ + ------------------------ ----------- + ------ + ----- + ----------------------- +- ------ +
43 rows in set (0.00 sec)

See how the table is created
mysql> show create table user \ G;
*************************** 1. row ******************** *******
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char (60) COLLATE utf8_bin NOT NULL DEFAULT‘ ‘,
  `User` char (16) COLLATE utf8_bin NOT NULL DEFAULT‘ ‘,
  `Password` char (41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT‘ ‘,
  `Select_priv` enum (‘ N ‘,‘ Y ‘) CHARACTER SET utf8 NOT NULL DEFAULT‘ N ’,
  `Insert_priv` enum (‘ N ‘,‘ Y ‘) CHARACTER SET utf8 NOT NULL DEFAULT‘ N ’,
  `Update_priv` enum (‘ N ’,‘ Y ‘) CHARACTER SET utf8 NOT NULL DEFAULT‘ N ’,
  `Delete_priv` enum (‘ N ‘,‘ Y ‘) CHARACTER SET utf8 NOT NULL DEFAULT‘ N ’,
  ...

  // \ G is to display the results vertically;

View the currently logged in user:
mysql> select user ();
+ ---------------- +
| user () |
+ ---------------- +
| [email protected] |
+ ---------------- +
1 row in set (0.00 sec)

View the current library:
mysql> select database ();
+ ------------ +
| database () |
+ ------------ +
| mysql |
+ ------------ +
1 row in set (0.00 sec)

Create a library:
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+ -------------------- +
5 rows in set (0.00 sec)

mysql> use db1 // Switch to db1 library
Database changed

Create a table:
mysql> use db1;
// Switch to the specified library first
Database changed
mysql> create table t1 (`id` int (4),` name` char (40));
// The fields and field format are defined in parentheses, enclosed in backticks
Query OK, 0 rows affected (1.51 sec)
// drop table t1, you can delete the table.

View the current database version:
mysql> select version ();
+ ----------- +
| version () |
+ ----------- +
| 5.6.35 |
+ ----------- +
1 row in set (0.00 sec)
// Database version: 5.6.35

View the database status:
mysql> show status;
+ ----------------------------------------------- +- ------------ +
| Variable_name | Value |
+ ----------------------------------------------- +- ------------ +
| Aborted_clients | 0 |
| Aborted_connects | 0 |
+ ----------------------------------------------- +- ------------ +

View all parameters:
mysql> show variables \ G; // View all parameters

View specified parameters
mysql> show variables like ‘max_connect%‘ \ G;
// like means match;% is a wildcard

Change parameters:
mysql> set global max_connect_errors = 110;
Query OK, 0 rows affected (0.04 sec)
#This is only a temporary change, if you want to change permanently, you need to edit the configuration file /etc/my.cnf

View the queue:
mysql> show processlist;
+ ---- + ------ + ----------- + ------ + --------- + ------ +- ------ + ------------------ +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------ +----------- + ------ + --------- + ------ + ------- + ------- ----------- +
5 | root | localhost | db1 | Query | 0 | init | show processlist |
+ ---- + ------ + ----------- + ------ + --------- + ------ +- ------ + ------------------ +
1 row in set (0.01 sec)

Full display:
mysql> show full processlist;
+ ---- + ------ + ----------- + ------ + --------- + ------ +- ------ + ----------------------- +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------ + ----------- + ------ + --------- + ------ +- ------ + ----------------------- +
| 6 | root | localhost | db1 | Query | 0 | init | show full processlist |
+ ---- + ------ + ----------- + ------ + --------- + ------ +- ------ + ----------------------- +
1 row in set (0.00 sec)

Drop followed by the library or table name in MySQL, you can delete the library or table.

You can use Ctrl+l to clear the screen

The MySQL history command is in the. mysql_history file.

MySQL Settings change root password, connect MySQL, common commands