MySQL change root password, connect MySQL, common operation

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