Mysql database

Command:

To view the installation path of MySQL:

1. select @@basedir as basePath from dual;
2. show variables like "%char%";
   - character_ sets_ This is the installation path.

View the data path of MySQL:

1. select @@datadir as dataPath from dual;
2. show variables Like '%datadir%';

Command line connection to database:

MySQL - H localhost - U user name (MySQL default user root) - P (this is the option to enter the password band)
PS: the command is MySQL - H localhost - U root - proot. The root after - P here is the password. Note here that there is no space between - P and password. If there is a space, the system will not treat the following string as a password

Switch current database: use database name;

View all databases:

1.show databases;
2.select schema_name from information_schema.schemataG

View all users: select user, host from mysql.user ;

View the running status of MySQL database: status;

To view the current database:

1. select database();
2. After the command show tables; view tables_ in_ Database name
3. After the status; command, view the current database: This is followed by the current database.

View table structure: desc table_ name;

Query database connection: show full processlist;

Query database connection without connecting to database: mysqladmin – uroot – P – processlist

View the maximum number of connections: show status like% max_ used_ connections%’;

Current connections: show status like% threads_ connected%’;

Table lock: show status like% table_ lock%’;

Row locking: show status like ‘InnoDB_ row_ lock%’;

Query Cache: show status like% qcache%;

Query Cache: Show variables like% query_ cache%”;

View the query cache usage status value: show status like ‘qcache%’;

View MySQL binlog mode: Show variables like% binlog%;

Because the client did not close the connection correctly, the number of abandoned connections is: show status like ‘abandoned_ clients’;

View the maximum number of connections: Show variables like% max_ connections%’;

View timeout: Show variables like% timeout%;

Check whether the log is started: Show variables like ‘log_ %’;

Format output random query: add g before

Users and permissions:

View the current MySQL user permissions: Show grants;

View the permissions of a user: Show grants for user name @ host;

MySQL user creation: create user ‘user name’ @ ‘host’ identified by ‘password’;

Give MySQL user permission:

Grant {privileges} on *. * to 'user name' @'host ';
Privileges represents permissions, which permissions should be given to view MySQL documents by yourself
*. * represents all tables of all databases. If it is a specific database, for example: test*