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

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)