MySQL Common operations

MySQL common operation (1) Change the MySQL database root password # to the root user of MySQL set password mysqladmin -uroot password  ' YourPassword ' # Log in to local mysqlmysql -uroot -p (2) Connect to remote database #remote_host_ip: remote or local LAN database server ip#-p: Remote or local LAN database server MySQL port mysql -uroot -p -hremote_host_ip -pport (3) MySQL common operation (a) querying the current library show  databases; (b) Querying a library of tables;use databasename;show tables; (c) Querying all fields of a table desc table_name;# Definition of query table show create table_name\g; (d) See which user is currently select user ();(e) View the currently used database Select database ();( f) Create a database create database db1; (g) Create a new table use db1; #字段名需要用反引号括起来create  table tablename   (' ID '  int (4),  ' name '  char (+));(h) View current database version select version ();(i) view the current status of MySQL # see Show_ Status.txt document show status; (j) View MySQL parameters # See variables.txt documentation show variables; (k) modify MySQL parameters # symbol% similar to show  variables like  ' max_connect% '; #如修改max_connect_errorsset  global max_connect_errors =  1000; (l) View the current server's queue show processlist; (m) Create a regular user and authorize # to grant thisUser #all: Represents all permissions (such as read, write, query, delete, etc.) #*.*: The previous * represents the database name, and the following * represents the table name in the Library #*.*: represents all data tables in all databases in the current MySQL database #identified  By: followed by password grant all on *.* to user1 identified by  ' 123123 '; #给网络上其他主机授权 # User name and host name separated by @ grant all on *.* to  ' user2 ' @ ' 10.0.0.1.2 '  identified by  ' 111222 '; #主机名用% override, indicating all hosts grant all on db1.* to  ' user3 ' @ '% '  identified by   ' AAABBB ';(4) Common SQL statement (a) query statement select count (*)  from mysql.user;select * from  mysql.db;select db from mysql.db;select db,user from mysql.db;select *  from mysql.db where host like  ' 10.0.1.% ';(b) Insert a row insert into db1.t1  values (1,  ' abc ');(c) Change one line update db1.t1 set name= ' AAA '  where id=1; (d) Empty a data table truncate table db1.t1; (e) Delete table drop table db1.t1; (f) Delete database drop database db1; ( 5) MySQL Data backup and recovery (a) MySQL backup mysqldump -Uroot -p ' password '  mysql > /tmp/mysql.sql; (b) Restore mysql -uroot -p ' password '  mysql < /tmp/mysql.sql;

MySQL Common operations