Mysql commands commonly used in operation and maintenance

  • 1. Check which libraries are available
> show databases;
  • 2. View the table of a library
> use db1; show tables;
  • 3. View the fields of the table
> desc tb1;
  • 4. View the statement of table creation
> show create table tb1;
  • 5. Which user is it currently
> select user();
  • 6. View the current library
> select databases();
  • 7. create Library
> create database db1;
  • 8. create table
> create table t1 (`id` int(4), `name` char(40));
  • 9. Insert data
> insert inti tb1 (id,name) values(1,`yuntai`,)
  • 10. View database version
> select version();
  • 11. View Mysql status
> show status;
  • 12. Modify mysql parameters
> show variables like 'max=connect%';
> set global max_connect_errors=1000;
  • 13. View mysql queue
> show processlist;
  • 14. Create a normal 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'@'%" identified by by '231222'
  • 15. Change password
> UPDATE mysql.user SET password=PASSWORD("newpwd") WHERE user='username';
  • 16. query
> select count(*) from mysql.user;
> select * from mysql.db; select * from mysql.db where host like '10.0.%';
  • 17. insertion
> update db1.t1 set name='aaa' where id=1;
  • 18. Clear list
> truncate table db1.t1;
  • 19. delete table
> drop table db1.t1;
  • 20. Delete database
> drop database db1;
  • 21. repair table
> repair table tb1 [use frm]
  • 22. backup
#Mysqldump - uroot - P db1 > / data / beifen1.sql backup Library
 #Mysqldump - uroot - P db1 TB2 > / data / beifen2.sql backup table
  • 23. recovery
# mysql -uroot -p db1 < /data/beifen1.sql
# Mysql-uroot-p db1 < / data / beifen2.sql does not need to add a table name to restore a table, just use the library name directly.
  • 24. Often used in shell
# mysql -uroot -pYuntai     mysql -e "show tables"
-e Ahead mysql Refers to the name of the library, followed by the double quotes mysql Command.

Tags: MySQL Database SQL mysqldump

Posted on Wed, 01 Apr 2020 09:42:57 -0700 by phil88