MySQL管理_資料庫常用命令

標籤：建立   fetch   bash   create   查看   git   sql語句   同步   配置   

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 [email protected]‘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 [email protected]‘%‘ identified by ‘1Qaz2wsx‘;mysql  -uroot -p123456 --default-character-set=gb2312 test<F:/pushingdb.sql mysql -uroot -p1qaz2wsx --default-character-set=utf8 ryp_production  <  cnapsbank.sql

建立資料庫

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

mysqldump匯出資料庫

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 資料庫名 > 檔案名稱

mysqldump匯出表結構

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_nameshow 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

外部執行sql

9.KILL Mysql Processlist ID

#!/bin/bashDATAFILE=`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

KILL Mysql Processlist ID

10鎖表處理

use information_schemaselect * from INNODB_LOCK_WAITSselect 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_queryfrom 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_idinner 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_idorder 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.noarchpt-query-digest mysql-slow-queries_20150313.log

資料庫分析工具

13.跳過某個同步錯誤

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

跳過某個同步錯誤

14.開啟慢查詢日誌

設定檔開啟（需重啟）vi /etc/my.cnflong_query_time = 2log-slow-queries=/var/log/slow.log線上開啟（無需重啟）set long_query_time=2set global slow_query_log = 1;set global slow_query_log_file = "/var/log/slow.log";

開啟慢查詢日誌

15.預存程序執行許可權

definer (‘lpdba‘@‘%‘)  確定定義者為lpdbagrant all on mysql.* to [email protected]‘%‘ identified by ‘[email protected]l3‘; grant execute on test.* to [email protected]‘%‘ identified by ‘[email protected]‘;

15.預存程序執行許可權

16.開啟事件

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

16.開啟事件

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_productFOR EACH ROWBEGIN    IF LOCATE(" ",new.game_type)>0 THEN        set NEW.game_type=REPLACE(NEW.game_type," "," ");  END if;END

修改資料庫觸發器

 

MySQL管理_資料庫常用命令