程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql數據庫常用命令

mysql數據庫常用命令

編輯:MySQL綜合教程

mysql數據庫常用命令


1. 數據庫賦權

進入mysql數據庫:
mysql> use mysql;

給root用戶設置新密碼:
mysql> update user set password=password("新密碼") where user="root";

刷新數據庫
mysql> flush privileges;

nagios監控
mysql> GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'nagiosshow'@'10.172.172.12' IDENTIFIED BY PASSWORD '*79B36E3D5F430AF5B15934D61D71C031B6502834';

查看用戶信息
use mysql;
select User,Host,Password from user;

查看用戶權限
show grants for root@'localhost';

刪除用戶
delete user form user where Host='xxxx' and User='xxxx';

2. 新建數據庫

查看已有用戶:
mysql> use mysql;
mysql> select Host,User,Password from user;

新建數據庫
CREATE DATABASE marketing_base DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

新建用戶並賦權
grant all privileges on marketing_base.* to dysql@'%' identified by '1Qaz2wsx';

mysql  -uroot -p123456 --default-character-set=gb2312 test

3. 導出數據庫

導出數據時排除某些表的數據

可以運行兩條命令,可以一起運行。
首先導出表結構:
mysqldump -uxxx -p -d db_name > db_name.sql

然後導出你要的數據:
mysqldump -uxxx -p dbname --ignore-table=test.t --ignore-table=test.t1 ...>>test.sql;

僅導出數據庫的數據:
mysqldump -uxxx -p -t db_name > db_data.sql

注:--ignore-table=xx “=” 左右不能有空格。

導出數據庫包含pos、file值
mysqldump -uroot --quick --flush-logs --master-data=1 -p ryp1_production > ryp1_production20140924.sql

4. 導出表結構

命令行下具體用法如下: 
mysqldump -u用戶名 -p密碼 -d 數據庫名 表名 > 腳本名; 

導出整個數據庫結構和數據 
mysqldump -h localhost -uroot -p123456 database > dump.sql 

導出單個數據表結構和數據 
mysqldump -h localhost -uroot -p123456  database table > dump.sql 

導出整個數據庫結構(不包含數據) 
/usr/local/mysql/bin/mysqldump -uroot -d entrym> dump.sql 

導出單個數據表結構(不包含數據) 
mysqldump -h localhost -uroot -p123456  -d database table > dump.sql


字段分隔符
mysqldump -uroot -p hsb MERCHANT_INFO --tab="/tmp/" --fields-terminated-by="#;@"

mysql> select * into outfile '/tmp/CASH_USE_INFO.sql' FIELDS TERMINATED BY '#;@' from CASH_USE_INFO;

mysql> select * into outfile '/tmp/MERCHANT_ORDER.sql' FIELDS TERMINATED BY '#;@' from MERCHANT_ORDER where MERCHANT_ID in(select ID from MERCHANT_INFO); 

導出存儲過程 
mysqldump -u 數據庫用戶名 -p -n -t -d -R 數據庫名 > 文件名

5. 主從同步設置

主數據庫
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.252.%' IDENTIFIED BY '123456';
mysql> show master status\G

從數據庫
mysql> change master to  master_host='192.168.252.150', master_user='backup', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=107;
mysql> start slave;
mysql> SHOW SLAVE STATUS\G

6. 查看數據庫大小

進入information_schema數據庫(存放了其他的數據庫的信息)
use information_schema;

查詢所有數據的大小:
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

查看指定數據庫home的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

查看指定數據庫home中 members 表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';

7. 查詢新建表索引

查看表是否存在索引(二選一)
show index from table_name from db_name
show index from db_name.table_name

查看表結構
desc table_name

為表字段(field_name)創建索引
create index field_name_index on table_name(field_name); 

8. 外部執行sql語句

mysql -uroot -p -e “seclect * from ” > seclect.sql

9. KILL Mysql Processlist ID

#!/bin/bash
DATAFILE=`date +%Y%m%d%s`
/usr/bin/mysql -uroot -p123456 -e "select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/$DATAFILE.txt';"
KILLCOM=`/usr/bin/mysql -uroot -p123456 -e "source /tmp/$DATAFILE.txt"`
 git fetch
 1038  git merge origin/develop

10. 鎖表處理

use information_schema

select * from INNODB_LOCK_WAITS

select r.trx_id as waiting_trx_id,
r.trx_mysql_thread_id as waiting_thread,
timestampdiff(second,r.trx_wait_started,current_timestamp) as wait_time,
r.trx_query as waiting_query,
l.lock_table as waiting_table_lock,
b.trx_id as blocking_trx_id,
b.trx_mysql_thread_id as blocking_thread,
substring(p.host,1,instr(p.host,':')-1) as blocking_host,
substring(p.host,instr(p.host,':')+1) as blocking_port,
if(p.command='sleep',p.time,0) as idle_in_trx,
b.trx_query as blocking_query
from information_schema.INNODB_LOCK_WAITS as w 
inner join information_schema.innodb_trx as b on b.trx_id=w.blocking_trx_id 
inner join information_schema.innodb_trx as r on r.trx_id=w.requesting_trx_id
inner join information_schema.INNODB_LOCKS as l on w.requested_lock_id =l.lock_id 
left join information_schema.PROCESSLIST as p on p.id=b.trx_mysql_thread_id
order by wait_time desc\G;

11. 分析頻繁操作的表

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000009 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}' | column -t | sort -k3nr >>~/$$.sql  & 2>/dev/null 

12. 數據庫分析工具

yum install percona-toolkit-2.2.8-1.noarch
pt-query-digest mysql-slow-queries_20150313.log

13. 跳過某個同步錯誤

slave stop;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
slave start;

14. 開啟慢查詢日志

配置文件開啟(需重啟)
vi /etc/my.cnf
long_query_time = 2
log-slow-queries=/var/log/slow.log

在線開啟(無需重啟)
set long_query_time=2
set global slow_query_log = 1;
set global slow_query_log_file = "/var/log/slow.log";

15. 存儲過程執行權限(待驗證)

definer ('lpdba'@'%')  確定定義者為lpdba
grant all on mysql.* to lpdba@'%' identified by 'l1ghtp@l3'; 
grant execute on test.* to lpdba@'%' identified by 'l1ghtp@l3';

16. 開啟事件

SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;

17. 修改數據庫觸發器

查看數據庫(vas_manage)的觸發器創建語句
use vas_manage;
show CREATE TRIGGER  before_update_game_product;

刪除觸發器
drop TRIGGER before_update_game_product;

創建觸發器
CREATE TRIGGER before_update_game_product BEFORE UPDATE ON game_product
FOR EACH ROW
BEGIN
    IF LOCATE(" ",new.game_type)>0 THEN
        set NEW.game_type=REPLACE(NEW.game_type," "," ");
  END if;
END

添加執行觸發器權限

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved