CentOS MySQL Common commands

1. Change the root password

Mysqladmin-uroot password ' yourpassword '

2. Remote Login to MySQL server

Mysql-uroot-p-h192.168.137.10-p3306

3. Querying the Database

show databases;

4. Enter a database

Use DatabaseName;

5. List the tables in the database

Show tables;

6. View all fields of a table

Desc Slow_log;

Show CREATE TABLE slow_log\g; (Not only can you display table information, but you can also display the Build table statement)

7. View Current User

Select User ();

8. View your current database

Select Database ();

9. Create a new database (you can specify a character set)

Create Database db1 charset UTF8;

10. Create a new table

CREATE TABLE T1 (' id ' int (4), ' name ' char (40));

11. View the database version

Select version ();

12. View Database Status

Show status; Current session state

Show global status; Global database state

show slave status\g; View master and slave database status information

13. Querying Database Parameters

Show variables;

14. Modifying Database parameters

Show variables like ' max_connect% ';

Set global max_connect_errors = 1000; (Restart the database will be invalidated, to be modified in the configuration file)

15. View the current database queue

Show Processlist;

16. Create a regular user and authorize a database

Grant all on databasename.* to ' user1 ' @ ' localhost ' identified by ' 123456 ';

17. Querying table Data

SELECT * from Mysql.db; Query all the fields in the table

Select COUNT (*) from Mysql.user; COUNT (*) indicates how many rows are in the table

Select Db,user from Mysql.db; Querying multiple fields in a table

SELECT * from mysql.db where host like ' 10.0.% '; You can use the universal match "%" in a query statement

18. Inserting a row of data

INSERT into DB1.T1 values (1, ' abc ');

19. Change a row of data in a table

Update db1.t1 set name= ' AAA ' where id=1;

20. Clear the table data

TRUNCATE TABLE db1.t1;

21. Delete a table

drop table db1.t1;

22. Clear all tables in the database (the database name is EAB12)

Mysql-n-S Information_schema-e "select CONCAT (' TRUNCATE TABLE ', table_name, '; ') From TABLES WHERE table_schema= ' eab12 ' | Mysql-f EAB12

23. Deleting a database

Drop database db1;

24. Database backup

Mysqldump-uroot-p ' YourPassword ' MySQL >/tmp/mysql.sql

25. Database recovery

Mysql-uroot-p ' YourPassword ' MySQL </tmp/mysql.sql

26. Create a new normal user

CREATE USER name identified by ' Ssapdrow ';

27. Change the normal user password

SET PASSWORD for Name=password (' FDDDFD ');

28. View name User rights

SHOW GRANTS for name;

29. Execute MySQL command in script

Mysql-uuser-ppasswd-e "Show Databases"

echo "Show Databases" |mysql-uuser-ppassword

Here's how to execute a large number of MySQL statements

MYSQL-UUSER-HHOSTNAME-PPASSWD <<eof

MySQL statement

Eof

----------------------Transferred from https://www.cnblogs.com/Mr-Zhong/p/7798186.html

CentOS MySQL Common commands