95. Change the root user password for MySQL, common commands for MySQL basic operation

Source: Internet
Author: User
Tags reserved

Change the root user password for MySQL 1, first access the database
[[email protected] ~]# /usr/local/mysql/bin/mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.36 MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> quitBye首次进入数据库使用了绝对路径,直接使用mysql命令是不行的,因为/usr/local/mysql/bin/不再PATH这个环境变量里。还有在首次进入数据库时,密码为空。退出时,输入quit或者exit即可。
2. Add the MySQL command absolute path to the environment variable
[[email protected] ~]# export PATH=$PATH:/usr/local/mysql/bin    //临时加入环境变量,重启就会失效[[email protected] ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile      //追加到profile文件中,使环境变量永久生效[[email protected] ~]# source /etc/profile     //重新加载配置[[email protected] ~]# mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.36 MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>
3. Setting and changing the root password
[[email protected] ~]# mysqladmin-uroot password ' 123456 '//in the actual production environment do not set such a simple password warning:using a pas Sword on the command line interface can is insecure.  [[email protected] ~]# mysqladmin-uroot password ' 123456 ' warning:using a password on the command line interface can Be insecure. [[email protected] ~]# mysql-uroot//Set the password, again using the previous login Chitian error Error 1045 (28000): Access denied for user ' root ' @ ' L Ocalhost ' (using Password:no) [[email protected] ~]# mysql-uroot-p123456//Enter the password before logging in, the-p option followed by a password, no spaces warning:u Sing a password on the command line interface can is insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 6Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c' To clear the current input statement.mysql> quitbye[[email protected] ~]# mysql-uroot-p//-p no password, in the form of an interactive output  Enter the password entered password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 7Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> exitbye[[email protected] ~]# mysqladmin-uroot-p123456 pas Sword ' sdwaqw '//Change password warning:using a password on the command line interface can be insecure.  [[email protected] ~]# mysql-uroot-psdwaqw//Login with new password warning:using a password on the command line interface can Be insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 9Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>4, forgot root password operation [[email protected] ~]# vim/etc/my.cnf# for ADV Ice on Settings see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html# * * * Do not EDIT the this FILE.  It's a template which'll be copied to the# * * * default location during install, and would be replaced if you# * * * Upgrade To a newer version of MySQL. [Mysqld]skip-grant//Add the field under [Mysqld] [[email protected] ~]#/etc/init.d/mysqld Res Tart//restart mysqlshutting down MySQL. success! Starting MySQL. success!  [[email protected] ~]# mysql-uroot//Now no password required Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.6.36 MySQL Community Server (GPL) Copyright (c) $, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help.                        Type ' \c ' to clear the current input statement.mysql> use MySQL; Switch database reading table information for completion of table and column namesyou can turn off this feature to get a quicker st            Artup with-adatabase changedmysql> Update user set Password=password (' sdwaqw123456 ') where user= ' root ';  Update password query OK, 4 rows Affected (0.00 sec) rows Matched:4 changed:4 warnings:0mysql> exitbye[[email protected]  ~]# vim/etc/my.cnf//delete skip-grant field [[email protected] ~]#/etc/init.d/mysqld restart//restart shutting down MySQL. success! Starting MySQL. success! [[email protected] ~]# mysql-uroot//Cannot login error 1045 (28000): Access denied for useR ' root ' @ ' localhost ' (using password:no) [[email protected] ~]# mysql-uroot-psdwaqw123456//Login with new password Warning:usin G A password on the command line interface can is insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 2Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> Exitbye
Second, connect the database
[[email protected] ~]# mysql-uroot-p-h192.168.242.128-p3306//-p Specify port,-H specify IP to sign in Enter Password:welcome  To the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 3Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> exitbye[[email protected] ~]# mysql-uroot-p-s/tmp/mysql.s  Ock//Use sock login, only for local connection, equivalent to "mysql-uroot-p123456" Enter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 4Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Names May trademarkS of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> Exitbye
Iii. common commands for basic MySQL operations
Querying all current libraries: show databases; switch libraries: use MySQL; View the table in the library: show tables; View all fields in the table: Desc tb_name; Tb_name represents the field name to view the build statement: Show CREATE TABLE tb_name\g; \g indicates that the current user is viewed by a vertical display (shown more organized): Select User (); View the database currently in use: Select Databsase (); Create library: Create database db1; Creating 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; Show variables like ' max_connect% '; modify parameters: Set global max_connect_errors=1000; View queue: Show Processlist; Show full processlist; simple demo: [[email protected] ~]# mysql-uroot-p-s/tmp/mysql.sockenter password:welcome to the MyS  QL Monitor. Commands End With; or \g.your MySQL connection ID is 5Server version:5.6.36 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> showdatabases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+4 rows in Set (0.01 sec) mysql> use MySQL; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> 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) mysql> desc func;+-------+------------ ------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------------------+------+-----+---------+-------+| name | CHAR (64) | NO |         PRI |       | || RET | tinyint (1) |     NO | |       0 | || DL | CHAR (128) |     NO |         |       | || Type | Enum (' function ', ' aggregate ') |     NO | |       NULL | |+-------+------------------------------+------+-----+---------+-------+4 rows in Set (0.00 sec) mysql> Select User ();+----------------+| User () |+----------------+| [email protected] |+----------------+1 row in Set (0.00 sec) mysql> Select Database (); +------------+| Database () |+------------+| MySQL |+------------+1 row in Set (0.00 sec)

Mysql>

95. Change the root user password for MySQL, common commands for MySQL basic operation

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.