13 weeks Four lessons change the root password, MySQL connection, common commands

Set Change root password

What we need to note here is that the root user of MySQL and the root user of the system are not the same thing.
By default, the root user of MySQL is empty, so it's not safe, so we're going to set a password for him.
To change the root password for MySQL, first we enter a command
[Email protected] ~]# Mysql-uroot
-bash:mysql: Command not found
But the system prompts us not to find this command, because this command is not valid under/usr/local/mysql/bin/, so we will first add/usr/local/mysql/bin/to the environment variable path.
[Email protected] ~]# export path= $PATH:/usr/local/mysql/bin/
[Email protected] ~]# Mysql-uroot
That's all you can do.
If we want to make export path= $PATH:/usr/local/mysql/bin/This command is permanent, then we need to put it in/etc/profile and put it in the last line.

Set the password for MySQL
Mysqladmin-uroot Password
[Email protected] ~]# mysqladmin-uroot password
New Password: (Enter password)
Confirm New Password: (duplicate password)

Change the root password for MySQL
Mysqladmin-uroot-p ' original password ' password ' new password '
We'll be able to log in with the new password after this change.

Reset the root password for MySQL
First edit the configuration file vi/etc/my.cnf
[Mysqld]
Skip-grant (add a new line of content, meaning ignore authorization)
Then save the exit and restart MySQL.
This way we can log in to MySQL without entering a password.
After entering MySQL we first switch MySQL
Then set a new password
Use MySQL;
Update user set Password=password (' Aminglinux ') where user= ' root ';
This will change the password. After exiting we will re-enter the configuration file, log skip-grant off, and then restart MySQL again.

When we set the password is, we have to add the single quotation mark "This is because our password may have special symbols, if you do not add single quotation marks may appear unrecognized situation.

Connect to MySQL

Mysql-uroot-p password (connect this machine)
Mysql-uroot-p target Password-h target ip-p destination port (remote connection) It is important to note that the letter P in front of the port is uppercase.
Mysql-uroot-p Password-s/tmp/mysql.sock (sock connection)-S is the specified sock, which is only suitable for use in this machine.
Mysql-uroot-p password-e "show Databases"-e executes the command. This is the case for Shell scripting.

MySQL Common commands

Query library show databases;

Switch the library use MySQL;

View the table in the library show tables;

View table's Fields desc + library Name

View Build Table statement Show create TABLE user\g;

View the current user Select User ();

View the database you are currently using Select Database ();

Create library name;

CREATE table use DB1; The CREATE TABLE table name ( id int (4), name char (40)), () is a field in parentheses, contains two fields, the first field is ID, the second field is name, and the field is enclosed in anti-quotation marks. where the int after the ID is a simple number, the meaning of int (4) is the longest is 4 digits. Char is a string, and char (40) means a maximum of 40 strings. We can also change its character set, as long as the creation of the time after adding Engine=innodb DEFAULT Charset=utf8 can be changed to Chinese.

DROP table Name

View current database version select version ();

View database status Show status;

View each parameter show variables; Show variables like ' max_connect% '; (where% is a wildcard character)

Modify the parameter set global max_connect_errors=1000; (this is only a temporary change, if you want to restart after the reset is still the value of our changes, then we have to quit MySQL, and then change the MYSQL.CNF configuration files under Linux. ）

View queue show Processlist; Show full processlist; this is a common command that works like the PS or top view in Linux, as it is.

13 weeks Four lessons change the root password, MySQL connection, common commands