Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to set up mysql and change root password. I hope I can add and update some knowledge to you. If you have any other questions you need to know, you can continue to follow my updated article in industry information.

Set and change root user password

Using mysql for the first time will prompt 'the command is not there' 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, add it to the system environment variable first.

[root@localhost ~] # export PATH=$PATH:/usr/local/mysql/bin/mysql

This variable will become invalid after restarting the system. To take effect permanently, you need to add it to the environment variable configuration file:

[root@localhost ~] # vim / etc/profile.export PATH=$PATH:/usr/local/mysql/bin/ refresh configuration: [root@localhost ~] # source / etc/profile

Set password

First login mysql,root users do not have a password, login directly

[root@localhost ~] # mysql-uroot

/ /-u specify user login

Welcome to the MySQL monitor. Commands end with; or\ g.

.

Mysql > quit

Bye

The / / quit command can exit mysql.

Set the password:

[root@localhost] # mysqladmin-uroot password '123456'

Warning: Using a password on the command line interface can be insecure.

/ / there is no error here, just a hint that the password is not displayed on the command line.

Log in without a password:

[root@localhost ~] # mysql-uroot

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

/ / prompt that login is rejected and password is required.

Log in with a password:

[root@localhost] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

.

/ /-p parameter, log in with a password. You can connect the password to-p. You can also enter the password after-p and enter it according to the following prompts. This method will not expose the user's password and is more secure.

Note: if you do not set the root password, you will be prompted to enter the password when you log in to mysql with the-p parameter. Enter directly.

Change password

When you know the user's password, change the password:

[root@localhost] # mysqladmin-uroot-pendant 123456' password '654321'

Warning: Using a password on the command line interface can be insecure.

/ / the warning password is entered on the command line, which is not safe. But the password has been changed successfully!

Log in with the old password:

[root@localhost] # mysql-uroot-p123456

Warning: Using a password on the command line interface can be insecure.

/ / the warning password is entered on the command line, which is not safe.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

/ / prompt login information verification failed, password error!

Log in with a new password

[root@localhost] # mysql-uroot-p654321

Warning: Using a password on the command line interface can be insecure.

/ / the warning password is entered on the command line, which is not safe.

Welcome to the MySQL monitor. Commands end with; or\ g.

.

Mysql >

/ / Login successfully with the new password!

Password reset

Use when you don't remember the root password, reset the password.

Edit the configuration file:

[root@localhost ~] # vim / etc/my.cnf

[mysqld]

Skip-grant / / ignore authorization!

.

/ / add code under mysqld module: skip-grant

Restart the mysql service:

[root@localhost ~] # / etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

Note: after completing the above operations, you do not need a password to log in to mysql.

Log in to mysql: [root@localhost ~] # mysql-urootWelcome to the MySQL monitor. Commands end with; or\ g.mysql > / / log in directly without using the-p parameter. Switch to the mysql library: mysql > use mysql; / / switch to the mysql library Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from user\ Gram / view the user's table information, which contains user-related information (password, authorization … ) / / the function of the G option is to make the output information displayed in an orderly manner. Without this option, the display will be very messy mysql > select password from user;// to view the user's 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 Warnings / change password to' 112233' recovery configuration file: [root@localhost ~] # vim / etc/my.cnf// comment out the line that was added to skip-grant and restart the mysql service: [root@localhost ~] # / etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! Login: [root@localhost ~] # 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 steps: 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 mysql

Remote connection

Connect using IP and port number

[root@localhost] # mysql-uroot-pendant 112233'-h227.0.0.1-P3306

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

.

Mysql >

/ /-h=host, specify IP,-P=port, specify port number

Local connection

You can connect directly or using socket.

Use the socket link:

[root@localhost] # mysql-uroot-pendant 112233'-S/tmp/mysql.sock

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

.

Mysql >

/ /-S=socket, specify socket. This method applies only to local connections. Same as a direct mysql connection.

Show all databases after connecting to the data

[root@localhost] # mysql-uroot-pendant 112233'-e "show databases"

Warning: Using a password on the command line interface can be insecure.

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

The-e argument can be followed by a mysql statement.

/ / this method is often used in shell scripts.

Mysql commonly used commands to check which databases are available: mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.00 sec) switch to mysql library: mysql > use mysqlReading table information for completion of table And column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > View the tables in the library: 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) to see how the table is created mysql > show create table user\ G * * 1. Row * * Table: userCreate 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 'Nails, `Delete_ priv` enum (' Numbai precepts Y') CHARACTER SET utf8 NOT NULL DEFAULT 'Nongs, `Update_ priv` enum (' Update_ Privacy Y') CHARACTER SET utf8 NOT NULL DEFAULT 'Nails, `Delete_ priv` enum (' Numbai precepts Y') CHARACTER SET utf8 NOT NULL DEFAULT 'Names. / /\ G is to display the results vertically. View the currently logged in user: mysql > select user (); +-+ | user () | +-+ | root@localhost | +-+ 1 row in set (0.00 sec) to view the current library: mysql > select database () +-+ | database () | +-+ | mysql | +-+ 1 row in set (0.00 sec) creation 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 / / cut Switch to db1 library Database changed to create table: mysql > use db1 / / first switch to Database changedmysql > create table T1 (`id` int (4), `name` char (40)) under the specified library; / / define fields and field formats in parentheses, use backquotes to enclose Query OK, 0 rows affected (1.51 sec) / / drop table T1, you can delete the table. Check the current database version: mysql > select version (); +-+ | version () | +-+ | 5.6.35 | +-+ 1 row in set (0.00 sec) / / Database version: 5.6.35 View 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 the specified parameter mysql > show variables like 'max_connect%'\ G match / like. % is the wildcard change parameter: 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 it permanently, you need to edit the configuration file / etc/my.cnf View 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.01sec) 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)

In mysql, drop is followed by the name of the library or table, which can be deleted.

You can clear the screen using ctrl+l

The history command for mysql is in the. MySQL _ history file.

Read the above about setting up mysql to change root password and so on, hoping to bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.