MySQL Settings change root password, connection, common commands

13.1 Setting Change root password

• Change environment variable path, add MySQL absolute path
  The first direct use of MySQL will prompt ' This command does not exist ' because it has not been added to the environment variable, if you want to use the command, you need to use its absolute path:/usr/local/mysql/bin/mysql, for convenience, first add it to the system environment variable:

[[email protected] ~]# export PATH=$PATH:/usr/local/mysql/bin/

The MySQL command path is temporarily added to the environment variable, the variable is invalidated after the system restarts, and it needs to be added to the environment variable configuration file for permanent entry:

vi /etc/profile#在配置文件最后 把上面的命令加#执行source命令生效[[email protected] ~]# source /etc/profile

• First Login

Note: -p=passwd, log in with a password, you can enter the password directly on the command line (followed by-P, no space:-P ' 123456 ' < here single quotation marks can not add, but when the password has a special symbol must be added, So when you enter a password in the command line habits: Add single quotes >), you can not enter the command line, only with the-P option, and then according to the prompt message: "Enter Password", enter the password to log in (this method does not expose the user password, security).

• Set the root password && change for MySQL

[[email protected] ~]# mysqladmin -uroot password ‘123456‘Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pEnter password: #输入密码#更改新的密码[[email protected] ~]# mysqladmin -uroot -p‘123456‘ password ‘taoyuan‘Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -p‘taoyuan‘Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.

• Password reset

[[email protected] ~]# vi/etc/my.cnf#my.cnf configuration file contents [mysqld]skip-grantdatadir=/data/mysql #增加skip-grant# Ignore authorization, It means no password login # restart MySQL service [[email protected] ~]#/etc/init.d/mysqld restartshutting down MySQL. success! Starting MySQL. success!  #登陆mysql Modify a table mysql> use MySQL; #切换表Reading table information for completion of table and column namesyou can turn off this feature to get a quicker start Up With-adatabase changed# View Table mysql> Select * from user, #查看user表mysql > select password from user;+----------------- --------------------------+| Password |+-------------------------------------------+|                                           *758aba8398ef87c993d2c4420dacd8946907c873 | |                                           ||                                           ||                                           ||                                           || |+-------------------------------------------+6 rows in Set (0.00 sec) #修改密码mysql > Update uSer set Password=password (' Aa123456 ') where user= ' root '; Query OK, 4 rows Affected (0.00 sec) Rows Matched:4 changed:4 warnings:0#quit, change the/etc/my.cnf configuration file back, restart the MySQL service [email  protected] ~]# vi/etc/my.cnf[[email protected] ~]#/etc/init.d/mysqld restartshutting down MySQL. success! Starting MySQL. success! [[email protected] ~]# mysql-uroot-p ' Aa123456 ' warning:using a password on the command line interface can be INSECU Re.  Welcome to the MySQL Monitor. Commands End With; or \g.

Note: after the completion of the operation can be arbitrary login mysql (no password), so at this time MySQL security is very poor, in peacetime configuration file must not add this parameter!!

13.2 Connecting MySQL

• This machine directly login

[[email protected] ~]# mysql -uroot -pAa123456

• Connection via port (TCP/IP)

[[email protected] ~]# mysql -uroot -pAa123456 -h127.0.0.1 -P3306# -P 指定端口

• Connection using the sock

[[email protected] ~]# mysql -uroot -pAa123456 -S/tmp/mysql.sock#适用于本机连接

• Connect MySQL operation a command

[[email protected] ~]# mysql -uroot -pAa123456 -e "show databases"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+#shell脚本使用中比较方便，可以直接获取数据

13.3 MySQL Common commands

MySQL Settings change root password, connection, common commands