Summary of common commands for mysql database under CentOS

Mysql database usage summary

This article mainly records some daily mysql commands for future query.

1. Change the root password

mysqladmin -uroot password 'yourpassword'

2. Remotely log in to the mysql server

mysql -uroot -p -h192.168.137.10 -P3306

3. Query 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_logG; (Not only table information can be displayed, but also table creation statements can be displayed)

7. View the current user

select user();

8. View the current database

select database();

9. Create a new database (Character set can be specified)

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 the database status

show status; Parameters

show variables;

14. Modify database parameters

show variables like 'max_connect%';

set global max_connect_errors = 1000; (restarting the database will invalidate, need to be modified in the configuration file)

15. View the current Database queue

show processlist;

16. Create a normal user and authorize it to a database

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

17. Query table data

select * from mysql.db; //Query all fields in the table

select count(*) from mysql.user; //count(*) indicates how many rows there are in the table

select db,user from mysql. db; //Query multiple fields in the table

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

18. Insert a row of data

insert into db1.t1 values ​​(1, 'abc');

19. Change a row of data in the table

update db1.t1 set name='aaa' where id=1;

20. Clear the table data

truncate table db1.t1;

21. Drop table db1.t1;

22. Clear all tables in the database (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.Drop database db1;

24.Database backup

mysqldump -uroot -p'yourpassword ' mysql >/tmp/mysql.sql

25. Database recovery

mysql -uroot -p'yourpassword' mysql /mysql.sql

26. Create a new normal user

CREATE USER name IDENTIFIED BY ' ssapdrow';

27. Change the ordinary user password

SET PASSWORD FOR name=PASSWORD('fdddfd');

28. View the name user permissions

SHOW GRANTS FOR name;

29. Execute the mysql command in the script

mysql -user -ppasswd -e"show databases"

echo "show databases"|mysql -user -ppassword

The following is the way to execute a large number of mysql statements

mysql -user -hhostname -ppasswd

mysql statement

EOF