MySQL change root password, connect MySQL, common operation

Source: Internet
Author: User
Tags time zones create database

MySQL Settings change root password
  • Export path= $PATH:/usr/local/mysql/bin/add MySQL to the environment variable can be used directly to the MySQL command, permanent effect to put this command to/etc/profile, and Source/etc/profile
  • Set Password
    [[email protected] ~]# mysqladmin -uroot password ‘s5381561‘Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
  • Change Password
  • Password changes when you forget your password
    • Change configuration file
      [[email protected] ~]# vim /etc/my.cnf[mysqld]skip-grant  #忽略授权[[email protected] ~]# vim /etc/my.cnf[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [[email protected] ~]# mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.
    • Change Password
      [[email protected] ~]# mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.mysql> use mysql;#切换mysql库Database changedmysql> select * from user\G;#查看用户的表信息,该表中存放的是用户相关信息(密码、授权…)#G选项的作用是使输出信息有序显示,不加该选项,显示内容会很乱  mysql> select password from user;#查看用户密码,显示结果Wie加密字符串!  mysql> update user set password=password(‘1234567‘) where user=‘root‘;Query OK, 4 rows affected (0.11 sec)Rows matched: 4  Changed: 4  Warnings: 0#将密码更改为‘1234567’mysql> quitBye
    • Restore the configuration file and restart
      [[email protected] ~]# vim /etc/my.cnf[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [[email protected] ~]# mysql -uroot -p1234567Warning: Using a password on the command line interface can be insecure.
Connect to MySQL
  • Remote connection
    [[email protected] ~]# mysql -uroot -p1234567 -h127.0.0.1 -P3306Warning: Using a password on the command line interface can be insecure.
  • Local connection
    [[email protected] ~]# mysql -uroot -p1234567 -S/tmp/mysql.sockWarning: Using a password on the command line interface can be insecure.
  • Show all databases, which are used in shell scripts
    [[email protected] ~]# mysql -uroot -p‘1234567‘ -e "show databases"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+
    MySQL Common commands
  • View library Information
    • Show databases; | Querying all databases
    • Use db_name; | Switch libraries
    • Show tables; | To view a table in a library
    • Desc Tb_name | View the fields in the table
    • Show create table tb_name\g; | View the Build Table statement
    • Select User (); | View Current User
    • Select Database (); | View the database currently in use
    • SELECT * from User\g; | View All Users
  • Edit Library
    • Create DATABASE db_name; | Create a library
    • Use Db_name;create Table Tb_name | Create a table under a library
    • Select version (); | View Current database version
    • Show status; | View database Status
    • Show variables; | View all Parameters
    • Show variables like ' max_connet% ' | View a parameter,% is a wildcard character
    • Set Global max_connect_errors=100; | Modifying a parameter can be permanently modified in MY.CNF
    • Show Processlist; | View MySQL process queue
    • Show Full Processlist | View queue Details
    • Drop Database Db_name | Delete a library
  • Code
Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+4 rows in Set (0.00 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 | | Time_zone | | Time_zone_leap_second |+---------------------------+28 rows in Set (0.00 sec) mysql> desc time_zone;+--------------- ---+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------------+------------------+------+-----+---------+----------------+| Time_zone_ID | Int (Ten) unsigned | NO | PRI | NULL | auto_increment | | Use_leap_seconds | Enum (' Y ', ' N ') |     NO | |                N | |+------------------+------------------+------+-----+---------+----------------+2 rows in Set (0.11 sec) mysql> Show CREATE TABLE time_zone\g, #G =grep Filter text content, the law shows *************************** 1. Row *************************** table:time_zonecreate table:create Table ' time_zone ' (' time_zone_id ' int () uns igned NOT NULL auto_increment, ' Use_leap_seconds ' enum (' Y ', ' n ') is not null DEFAULT ' n ', PRIMARY KEY (' time_zone_id ')) ENGI Ne=myisam DEFAULT Charset=utf8 comment= ' time zones ' 1 row in Set (0.03 sec) error:no query specifiedmysql> Select User () ;+----------------+| User () |+----------------+| [email protected] |+----------------+1 row in Set (0.07 sec) mysql> Select Database (); +------------+| Database () |+------------+| MySQL |+------------+1 row in Set (0.00 sec) mysql> select * from user\g; creating a library:mysql> Create Database db1;  Query OK, 1 row affected (0.02 sec) CREATE table:mysql> use DB1; #先切换到指定库下Database changedmysql> CREATE TABLE T1 (' id ' int (4), ' name ' char (+) ') Engine=innodb DEFAULT charset=utf8;# In parentheses is the definition of field and field format, using back quotation marks to query OK, 0 rows affected (1.51 sec) mysql> Select version (); +-----------+| Version () |+-----------+| 5.6.35 |+-----------+1 row in Set (0.06 sec) mysql> show status;+-----------------------------------------------+--- ----------+| variable_name | Value |+-----------------------------------------------+-------------+| aborted_clients | 0 | | aborted_connects | 0 |+-----------------------------------------------+-------------+mysql> show variables\g;mysql> show Vari Ables like ' max_connect% ' \g; #like表示匹配;% is a wildcard change parameter:mysql> set global max_connect_errors=110; Query OK, 0 rows affected (0.04 sec) #在此只是临时更改, if you want to change permanently, you need to edit the configuration file to 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) mysql> drop table T1; Query OK, 0 rows affected (0.32 sec) mysql> drop database db1; Query OK, 0 rows affected (0.10 sec)
Extended
    • mysql5.7 Root Password change
    • MyISAM and InnoDB engine comparison
    • MySQL Configuration detailed:
    • MySQL Tuning:
    • Personal MySQL tuning experience shared by classmates:

MySQL change root password, connect MySQL, common operation

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.