MYSQL操作命令

MYSQL操作命令

2018年8月8日

19:02

1、查看HOME数据库及表大小：

use information_schema;

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

select

table_schema as '数据库',

table_name as '表名',

table_rows as '记录数',

truncate(data_length/1024/1024, 2) as '数据容量(MB)',

truncate(index_length/1024/1024, 2) as '索引容量(MB)'

from information_schema.tables

order by data_length desc, index_length desc;

2、show status和show variables的区别

show status：查看系统运行的实时状态，便于dba查看mysql当前运行的状态，做出相应优化，动态的，不可认为修改，只能系统自动update

mysql> show status like '%conn%';

+-----------------------------------------------+---------------------+

| Variable_name | Value |

+-----------------------------------------------+---------------------+

| Aborted_connects | 4469 |

| Connection_errors_accept | 0 |

| Connection_errors_internal | 0 |

| Connection_errors_max_connections | 428 |

| Connection_errors_peer_address | 0 |

| Connection_errors_select | 0 |

| Connection_errors_tcpwrap | 0 |

| Connections | 821752 |

| Locked_connects | 0 |

| Max_used_connections | 215 |

| Max_used_connections_time | 2018-08-20 14:57:02 |

| Performance_schema_session_connect_attrs_lost | 0 |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_finished_connects | 0 |

| Threads_connected | 166 |

+-----------------------------------------------+---------------------+

16 rows in set (0.00 sec)

show variables：查看系统参数，系统默认设置或者dba调整优化后的参数，静态的。可以通过set或者修改https://www.360docs.net/doc/2e11472240.html,f配置文件修改

mysql> show variables like '%conn%';

+-----------------------------------------------+-----------------+

| Variable_name | Value |

+-----------------------------------------------+-----------------+

| character_set_connection | utf8 |

| collation_connection | utf8_general_ci |

| connect_timeout | 10 |

| disconnect_on_expired_password | ON |

| init_connect | |

| max_connect_errors | 1000000 |

| max_connections | 2000 |

| max_user_connections | 0 |

| performance_schema_session_connect_attrs_size | 512 |

+-----------------------------------------------+-----------------+

9 rows in set (0.00 sec)

3、MYSQL查看用户授予的权限

在MySQL中，如何查看一个用户被授予了那些权限呢？ 授予用户的权限可能分全局层级权限、数据库层级权限、表层级别权限、列层级别权限、子程序层级权限。具体分类如下：

全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在https://www.360docs.net/doc/2e11472240.html,er表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销

show grants for test;

数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

select host,db,user,select_priv,insert_priv,update_priv,delete_priv from mysql.db where user='sqluser';

select host,user,select_priv,insert_priv,update_priv,delete_priv from https://www.360docs.net/doc/2e11472240.html,er where user='sqluser';

grant select,insert,update,delete on merch_accs_app.* to sqluser@'192.168.202.%';

grant select,insert,update,delete on merch_accs_app.* to sqluser@'192.168.202.201';

grant select,insert,update,delete on merch_accs_app.* to sqluser@'10.124.101.3';

grant select,insert,update,delete on merch_accs_app.* to sqluser@'10.124.101.4';

grant select,insert,update,delete on MyDB.* to test@'%' identified by 'test';

flush privileges;

表层级

表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON

db_name.tbl_name只授予和撤销表权限。

grant all on MyDB.kkk to test@'%' identified by 'test';

flush privileges;

show grants for test;

select * from mysql.tables_priv\G;

列层级

列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。grant select (id, col1) on MyDB.TEST1 to test@'%' identified by 'test';

flush privileges;

select * from mysql.columns_priv;

show grants for test;

子程序层级

CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

grant execute on procedure MyDB.PRC_TEST to test@'%' identified by 'test';

flush privileges;

show grants for test;

select * from mysql.procs_priv where User='test';

4、复制完整数据库至同一台机：

mysqldump wk_wallet_app -S /mysqldbdata/mysql_3306/mysql/mysql.sock -uroot -p'travel-racial*murmur' --add-drop-table --

set-gtid-purged=off | mysql wk_wallet_app_demo -S /mysqldbdata/mysql_3306/mysql/mysql.sock -uroot -p'travel-racial*murmur'

5、导出数据至文本

SELECT * from wk_recharge where trans_type=1 and create_time BETWEEN '2018-10-07 00:00:00' and '2018-10-07

23:59:59' into outfile '/tmp/wk_recharge_export.txt';

SELECT * from test into outfile '/tmp/test.txt' fields terminated by "," optionally enclosed by '"';

6、mysqlimport导入数据进MYSQL，要注意编码一致

mysqlimport -S /mysqldata/mysql/data_23306/mysql/mysql.sock -uroot -p'gerRk229sS' dev_risk /tmp/dna_diction.del --fields-terminated-by="@^$" --ignore=1

4、删除某用户产生的大量连接

mysqladmin --socket=/tmp/mysql3306.sock -ppwd@123 processlist | awk -F "[| ]+" '{if($3 == "root") {print $2}}' | xargs -n 1 mysqladmin --socket=/tmp/mysql3306.sock -ppwd@123 kill

5、索引

(1)、show index from tblname;

(2)、show keys from tblname;

（3）、查看SQL执行计划

explain select * from t where a between 1000 and 2000;

（4）、强制使用索引a

explain select * from t force index(a) where a between 10000 and 20000;

(5)、重新统计索引信息

6、开启内存监控参数：

查看监控内存参数是否开启：

SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%';

开启内存监控参数：

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';

7、MYSQLDUMP全备份MYSQLBINLOG实时同步BINLOG操作需要的权限

grant select,reload,process,lock tables,replication slave,replication client,event on *.* to backupuser@'localhost' identified by '123456';

8、查询长事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

9、查看表的状态

show table status like 'statis_grc_msg'\G;

10、