2018-3-22 13 weeks 4 lessons MySQL common operation (UP)

Source: Internet
Author: User
Tags reserved

13.1 Setting Change root password


Default MySQL password is empty


[[email protected] ~]# mysql -uroot-bash: mysql:  command not found [[email protected]  ~]# echo  $PATH/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin[[email  Protected] ~]# export path= $PATH:/usr/local/mysql/bin/[[email protected] ~]#  echo  $PATH/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin[[email  protected] ~]# mysql -urootwelcome 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)  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&Nbsp;trademarks of their respectiveowners. type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> quitbye

(If you want to configure permanent, you will also need to add export to/etc/profile)

If the profile file is changed only, and the export PATH is not executed, then source/etc/profile can make the environment variable effective


• Set the MySQL password:

[[email protected] ~]# mysqladmin-uroot password ' 123456 ' warning:using a password on the command line interface can be I Nsecure.

(It is best not to include the password in the command line)


• Log in to MySQL:

[Email protected] ~]# Mysql-uroot-penter 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> Quitbye


• If you do not know the MySQL password, do a password reset:


1, edit/etc/my.cnf, add Skip-grant:

[Email protected] ~]# VI/ETC/MY.CNF

[Email protected] ~]#/etc/init.d/mysqld restartshutting down MySQL. success! Starting MySQL. success!


2, enter Mysql,use mysql,update user set Password=password (' Password ') where user= ' root ';

[[email protected] ~]# mysql -urootwelcome 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)  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> use mysqlreading table information for completion  of table and column namesyou can turn off this feature  to get a quicker startup with -ADatabase changedmysql> update user  Set password=password (' 123456 ')  where user= ' root '; query ok, 3 rows affected  (0.00 sec) rows matched: 4  changed :  3  warnings: 0mysql> quitbye


3, edit/etc/my.cnf, remove Skip-grant:

[Email protected] ~]# VI/ETC/MY.CNF

(Remove the previously added skip-grant)


4. Restart MySQL:

[[Email protected] ~]# /etc/init.d/mysqld restartshutting down mysql].  success! starting mysql. success! [email protected] ~]# mysql -uroot -p                 # #可以重新登录mysql了Enter  password:welcome to the  MySQL monitor.  Commands end with ; or \g.Your MySQL  connection id is server 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



13.2 Connecting MySQL


• Connect to native MySQL:

[Email protected] ~]# mysql-uroot-p123456


• Connect other machines to MySQL:

[Email protected] ~]# mysql-uroot-p123456-h127.0.0.1-p3306

(-h specifies ip,-p specified port)


• Connect to MySQL using the socket:

[Email protected] ~]# Mysql-uroot-p123456-s/tmp/mysql.sock

(-s Specify socket position, only for native)


· after connecting to MySQL, do some things:

[Email protected] ~]# mysql-uroot-p123456-e "Show Databases"



13.3 MySQL Common commands


• Query library show database;

[Email protected] ~]# mysql-uroot-p123456warning: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 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> show databases;


• Switch the library use MySQL;

mysql> use mysqlreading table information for completion of table and column namesyou can turn off this feature to get A quicker startup With-adatabase changed


• View the table in the library show tables;

Mysql> Show tables;


• View the fields in the table desc tb_name;

mysql> desc User;


• View the Build Table statement show create TABLE tb_name\g;


• View current users select User ();


• View the database currently in use Select Databsase ();


• Create library creation database DB1;


• CREATE table use DB1; CREATE TABLE T1 (' id ' int (4), ' name ' char (40));

If you want to define the settings, you can

Mysql> CREATE TABLE B1 (' id ' int (4), ' name ' char (+) ') Engine=innodb DEFAULT Charset=utf8;

Query OK, 0 rows affected (0.01 sec)


• View current database version select version ();


• View database status show status;

(data not all shown)


• View each parameter show variables; Show variables like ' max_connect% ';

Show variables;

(Too many parameters)


• Modify parameter set global max_connect_errors=1000;

If you want to permanently take effect, you need to exit to SHELL,VIM/ETC/MY.CNF, define max_connect_errors=1000;


• View queue show processlist; Show full processlist;


If there are errors, please correct, learn from each other and progress together!!!

2018-3-22 13 weeks 4 lessons MySQL common operation (UP)

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.