Summary of common commands for MySQL database under CentOS

Source: Internet
Author: User

1. Change the root password

Mysqladmin-uroot password ' yourpassword '

2. Remote Login to MySQL server


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


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 statement


Summary of common commands for MySQL database under CentOS

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.