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)