Set change root password, connect MySQL, MySQL common commands

One: Set change root password

(1) Edit MySQL master configuration file my.cnf
#vim/etc/my.cnf
Add a parameter under the [Mysqld] field
Skip-grant
Need to add under version 5.7
#skip-grant-tables
(2) Restart the database service
#service mysqld Restart
(3) So you can access the database without authorization.
#/usr/local/mysql/bin/mysql-uroot
(4) Modify the corresponding user password

Use MySQL;
Update user set Password=password (' Your password ') where user= ' root ';
Flush privileges; Let the configuration take effect without restarting MySQL
Quit
(5) Modify/ETC/MY.CNF remove Skip-grant, restart Mysqld service

Two: Connect MySQL

When the MySQL service starts, not only listens to the Ip:port, but also listens to a socket, our installation mysql is the monitor
Listen to the/tmp/mysql.sock. If PHP is local, then PHP and MySQL communication can be communicated through the socket, such as
If the result is remote, it needs to communicate via TCP/IP. Under the Linux command line, we can use the following method to connect the
Connect to the MySQL server.
(1) How TCP/IP is
#/usr/local/mysql/bin/mysql-uroot-h127.0.0.1
This connects, because the default MySQL root user password is empty, so without the-p option, we can
To set a password for him.
#/usr/local/mysql/bin/mysqladmin-uroot password ' aminglinux.com '
Of course, this is the first time you can set a password, but when you set it up again, you need to first enter the previous root
's password.
#/usr/local/mysql/bin/mysqladmin-uroot-paminglinux.com password ' aminglinux '
Have you ever thought of a problem, every time we knock orders are used absolute path, so very annoying, how not bored,
Have you ever thought about the points of knowledge we've learned before? I have two options, the first setting alias, and the second setting path.
#alias Mysql=/usr/local/mysql/bin/mysql
#alias Mysqladmin=/usr/local/mysql/bin/mysqladmin
If you want to be permanent, remember to put the two alias in the/ETC/BASHRC.
The other is to set the path, as follows.
#vim/etc/profile.d/path.sh
Join a row
Export path= $PATH:/usr/local/mysql/bin
After saving, execute
#source/etc/profile.d/path.sh
When you set a password for MySQL and then go to connect, you need to add the-p option.
#mysql-uroot-paminglinux-h127.0.0.1
Where-h specifies the IP, if it is a remote machine, then-H is followed by the remote server IP, the default port is 3306, as
The result is another port that needs to be defined with-p.
#mysql-uroot-paminglinux-h127.0.0.1-p3306

Authorize a login for an IP
Go to MySQL
Grant all on . to ' username ' @ ' client IP ' identified by ' password '; For all the tables
Use MySQL;
SELECT * from user where host= ' client IP '/g;
(2) Socket mode
This is only suitable for connecting to the native MySQL, the command is:
#mysql-uroot-s/tmp/mysql.sock-paminglinux
The-S can be omitted here.
If you use the Mysql-uroot-p logon times error
ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock ' (2)
Open My.cnf and add the following code
[Client]
Socket=/var/lib/mysql/mysql.sock
Restart Mysqld, problem solving

Three: MySQL common commands

See which libraries are available

show databases;
View a table for a library
Use DB; Show tables;
View the fields of a table
Desc TB;
View the Build Table statement
Show CREATE table TB;
Which user is currently
Select User ();
View Current Library
Select Database ();
Create a library
Create Database db1;
Create a table
CREATE TABLE T1 (idInt (4),nameCHAR (40));
Inserting data
Insert into TB1 (id,name) VALUES (1, ' aming ');
View Database version
Select version ();
View MySQL Status
Show status;
modifying MySQL parameters
Show variables like ' max_connect% ';
Set global max_connect_errors = 1000;
View MySQL Queue
Show Processlist;
Create a regular user and authorize
Grant All on.To User1 identified by ' 123456 ';
Grant all on DB1.To ' user2 ' @ ' 10.0.2.100 ' identified by ' 111222 ';
Grant all on DB1.To ' user3 ' @ '% ' of ' identified by ' 231222 ';
Change Password
UPDATE mysql.user SET Password=password ("Newpwd") WHERE
User= ' username ';
Inquire
Select COUNT () from Mysql.user;
SelectFrom Mysql.db; SELECT * from mysql.db where host like ' 10.0.% ';
Insert
Update db1.t1 set name= ' AAA ' where id=1;
Clear table
TRUNCATE TABLE db1.t1;
Delete a table
drop table db1.t1;
Deleting a database
Drop database db1;
Repair table
Repair table tb1 [use frm];
These are some of the common MySQL related operations I introduced, of course, do not think this will suffice, after all, this only
is a common operation, there are many other operations even I have not used, if you use in the future, check the MySQL text
File or search for it with the help of an engine. In addition, I would like to introduce a point of knowledge, in the shell command line down
Perform the MySQL operation.
#mysql-uroot-paminglinux mysql-e "Show Tables"
The MySQL in front of-E refers to the name of the library, and the-e option is followed by the MySQL command.

Set change root password, connect MySQL, MySQL common commands