Shulou(Shulou.com)05/31 Report--

This article mainly introduces what MySQL basic commands have, which can be used for reference by interested friends. I hope you will gain a lot after reading this article. Let's take a look at it.

-- query user permissions

Show grants for xxx

-- Save the execution result to a file

Pager cat >'$HOME/text.txt'

-- View current session

Show precesslist

-- kill process

Kill process ID

-- change password

Select host,user,password from mysql.user

UPDATE mysql.user SET password=PASSWORD ('xxx') WHERE user='xxx'

FLUSH PRIVILEGES

-- build users

GRANT select ON mysql.user TO 'xxxx'@'%' IDENTIFIED BY' xxxx'

Flush privileges

-- Login

Mysql-uroot-p-socket=/xxx/mysql/5.6/var/mysql.sock

Stop the library

Mysqladmin-uroot-p-socket=/xxx/mysql.sock shutdown

Take up the library

Mysqld_safe-- defaults-file=/xxx/my.cnf &

This parameter:

Set global read_only=OFF

Show variables like'% read_only%'

-- Library size:

Select concat (round (sum (DATA_LENGTH/1024/1024), 2), 'MB') as data from information_schema.TABLES

-- login path

Mysql_config_editor set-login-path=xxxxx-socket=/xxx/mysql.sock-user=root-password

Export:

Mysqldump-login-path=root-socket=/xxx/mysql.sock-default-character-set=UTF8-single-transaction-events-triggers-routines-all-databases > / xxx/all_databases.sql

Single table export:

Mysqldump-login-path=root-socket=/xxx/mysql.sock-default-character-set=UTF8-single-transaction-events-triggers-routines database_name table_name > / xxx/xxx.sql

Mysqldump-uroot-p-- socket=/xxx/mysql.sock-- set-gtid-purged=OFF-- default-character-set=UTF8-- single-transaction-- events-- triggers-- routines database_name table_name > / xxx/three_tables.sql

Import:

Mysql-login-path=xxxx-socket=/xxx/mysql.sock

< /paic/mydat/tmp/all_databases.sql 导全库的话要先：reset master; 否则会报错：ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 几个常用用例： 1.导出整个数据库 mysqldump -u 用户名 -p 数据库名 >

Exported file name

Mysqldump-u wcnc-p database_name > database_name.sql

two。 Export a table

Mysqldump-u user name-p database name table name > exported file name

Mysqldump-u database_name-p database_name table_name > database_name.sql

3. Export a database structure

Mysqldump-u wcnc-p-d-- add-drop-table database_name > d:\ database_name.sql

-d No data-- add-drop-table adds a drop table before each create statement

Export the structure of a table with no data plus-d

Mysqldump-uroot-pdbpasswd-d dbname test > db.sql

Mysqldump-u wcnc-p database_name users > database_name.sql

Is DB too big? Try backup recovery.

Backup:

Mysqlbackup-uroot-p-socket=/xx/mysql.sock-backup-dir=/xxx/tmp/-compress=true backup

Mysqlbackup-uroot-p-- socket=/xx/mysql.sock-- backup-dir=/xxx/tmp backup-and-apply-log

Restore:

Mysqlbackup-uroot-p-- socket=/xxx/mysql.sock-- backup-dir=/xxx/tmp/-- uncompress-- innodb_log_files_in_group=3-- innodb_log_file_size=314572800 apply-log

Mysqlbackup-defaults-file=/xxx/my.cnf-backup-dir=/xxx/tmp-innodb_data_file_path='ibdata1:12M:autoextend'-innodb_log_files_in_group=3-innodb_log_file_size=314572800-innodb_page_size=16384 copy-back

Select user,substr (host,1,13), db,count (1) from information_schema.processlist group by user,substr (host,1,13), db

Thank you for reading this article carefully. I hope the article "what are the basic commands of MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.