Common commands of MySQL in Linux

My blog

Sign in

mysql -u user name  -p
--  Then enter the password below ,Linux Your password will not show , Just hit blindly 

mysql -u user name  -p password 
--  This method will log in directly 

switch

Open database

service mysql start

I didn't put a semicolon here , If the terminal runs the command , Remember to add... At the end ; Otherwise, the following results may occur .

Close the database

service mysql stop

Restart the database

service mysql restart

Common operations

Show database list

show databases;

establish 、 Delete database

create database  Database name ;
​
drop database  Database name ;

Display the data table in the library

use mysql;
​
show tables;

Show data table structure

describe  Data table name ;

Change Password

SET PASSWORD command （ Login required ）

mysql> set password for  user name @localhost = password(' New password ');

Use sql Statement update mysql In the library user surface （ Login required ）

mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='localhost'; 
mysql> flush privileges;

mysqladmin command , Plaintext （ No login required ）

mysqladmin -u user name  -p Old password  password  New password 

command , Ciphertext （ No login required ）

mysqladmin -u user name  -p Old password  password+ enter , Then type the new password twice 

Other commands

Import 、 Export database （ No login required ）

Export all databases

mysqldump -u user name  -p password  --all-databases > export path 

Export specified database

mysqldump -u user name  -p password  --databases  Database name > export path 

Export the structure of the specified database （ No data ）

 grammar ：mysqldump -u user name  -p password  --no-data --databases  Database name > export path 
 give an example ：mysqldump -uroot -p123456 --no-data --databases testdatebase >/tmp/testdatebase_no_data.sql

Export the specified table in the specified database

 grammar ：mysqldump -u user name  -p password  --databases  Database name  --tables  Data table name > export path 
 give an example ：mysqldump -uroot -p123456 --databases testdatebase --tables t_user >/tmp/testdatebase_user.sql

Export the structure of the specified table in the specified database （ No data ）

 grammar ：mysqldump -u user name  -p password  --no-data --databases  Database name  --tables  Data table name > export path 
 give an example ：mysqldump -uroot -p123456 --no-data --databases testdatebase --tables t_user >/tmp/testdatebase_user_no_data.sql

Export the specified table in the specified database , Set query conditions

 grammar ：mysqldump -u user name  -p password  --databases  Database name  --tables  Data table name  --where=' Conditions '> export path 

 give an example ：mysqldump -uroot -p123456 --databases testdatebase --tables t_user --where='user_no=1'>/tmp/mysql_user_no.sql

Backing up databases across servers

--  take  host1  In the server  db1  All data from the database is imported into  host2  Medium db2 In the database ,db2 The database of must exist or it will report an error 
-- ( After testing , stay mysql5.5 In the version ,db2 To exist is to , The actual generated database name is the same as db1 Agreement ; add -C Parameter to enable compression passing ）

 grammar ：mysqldump --host=host1 -u user name  -p password  --databases db1 |mysql --host=host2 -u user name  -p password  --databases db2
 give an example ：mysqldump --host=192.168.1.101 -uroot -p123456 -C --databases testdatebase |mysql --host=192.168.3.102 -uroot -p123456 --database testdatebase

adopt sql File import database

 grammar ：mysql -u user name  -p password  <  Import path 
 give an example ：mysql -uroot -p123456 < /tmp/testdatebase.sql

notes ： Part from blog Garden