常用Mysql命令

標籤：

查看已存在的資料庫 show databases;
建立資料庫並查看 create database 資料庫名;
刪除資料庫 drop database 資料庫名;
使用指定的資料庫 use 資料庫名;
查看資料庫中的表 show tables;
建立表 create tables 表名;
查看錶結構 describe 表名;
刪除表 drop table 表名;
Database Backup mysqldump --opt 資料庫名>存放位置/檔案名稱
資料庫的恢複 drop database db1;->careate database db1;->mysql db1</tmp/db1.bak;->測試 use db1-> select ...
設定密碼 set password=password(‘123‘);
刪除匿名使用者 delete from user where user=‘‘;
更新授權表 flush privileges;
給使用者授權的標準文法：
grant 許可權 on 資料庫/表 to 使用者名稱 identified by ‘密碼‘ with ‘特殊許可權‘

如：grant all on *.* to [email protected] identified by ‘123‘ with grant option;
授權不限定主機 grant all on *.* to [email protected]‘%‘ identified by ‘123‘ with grant option;
授權使用者指定ip登入，對所有資料庫有全部的許可權

grant all on *.* [email protected]%.% identified by ‘123‘;

測試使用者dbau2
mysql -u dbau2 -p 123

撤銷使用者權限標準文法
revoke insert,delete on *.* from [email protected];
撤銷指定使用者的所有許可權
revoke all on *.* from [email protected]‘%‘;

查看軟體包
rpm -qa | grep mysql
安裝軟體包
rpm -ivh
啟動|停止|重啟mysql服務
service mysqld start|stop|restart
/etc/init.d/mysqld start|stop|restart
安全後台啟動mysql守護進程
safe_mysqld&
查看mysql守護進程
service msyqld status
關閉mysql
mysqladmin shutdown
不用使用者名稱密碼登入 mysql
指定root使用者登入
mysql -u root
輸入root密碼後登入
mysql -u root -p

複製表結構
create table new_table(id int(20) not null auto_increment primary key) select * from old_table

TRUNCATE TABLE `666`

mysql -u root -p
mysqldump -u root -p omsadmin > word.sql; 匯出表
mysql -u root -p ucms_2_0 < hubei.txt 執行一個命令

匯出某個特定表結構和資料
mysqldump -uroot -p omsadmin evaluating_data > /home/yuejd/evaluating_data.sql;

如果匯出為空白 可以加host
mysqldump -h 192.168.42.129 -u root -p vip bt_config > /apps/sql/bt_config.sql;

匯出某個特定表結構
mysqldump -uroot -p -d omsadmin evaluating_data>/home/yuejd/evaluating_data.sql;

建索引
ALTER TABLE `log_analysis`.`wap_query_pv_week_2011_28` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `id` );
ALTER TABLE `wap_query_pv_week_2011_28` ADD INDEX ( `query` );

ALTER TABLE `offical` ADD `ww` INT( 12 ) NOT NULL AFTER `link_wap`
==================================================================================
啟動mysql：
/usr/mysql/bin/mysqld_safe --user=mysql &
mysql授權:
grant all on *.* to [email protected] identified by "456";
查看授權表:
select user,host,password from mysql.user
重新整理日誌:
flush logs;
查看最後一條日誌:
show master status;
清除binlog日誌:
reset master;
查看二進位binlog日誌：
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/logs/mysql-bin.000001(後面加絕對路徑)
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/logs/mysql-bin.000001(忽略編碼問題)

socket登入
mysql -uroot -p -S /usr/local/mysql/3307/mysql.sock --port=3307
備份：
/usr/local/mysql/bin/mysqldump -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin -l -F > /usr/local/temp/test.sql
恢複：
/usr/local/mysql/bin/mysql -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin < /usr/local/temp/test.sql
恢複binlog日誌：
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/3307/logs/binlog/mysql-bin.000020 |/usr/local/mysql/bin/mysql -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin(資料庫)
mysqlbinlog後跟主要參數：
--start-position="200"
--stop-position="500"
--start-date="2012-03-29 21:20:20"
--stop-date="2012-03-29 22:00:00"
按照位置恢複：
/usr/local/mysql/bin/mysqlbinlog --stop-position="1501" /usr/local/mysql/3307/logs/binlog/mysql-bin.000021 |/usr/local/mysql/bin/mysql -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin(後面跟資料庫登入)
mysql主從同步:
配置從服務：
/etc/my.cnf的slave
server-id=2
master-host=192.168.10.1
master-user=user
master-password=pass
master-port=3306
log-bin=mysql-bin
#replicate-do-db=test
#replicate-do-table=test.t1
重新啟動:
service mysqld restart
查看：
mysql>show slave status\G;
Slave_IO_Running:YES
Slave_SQL_Running:YES
造成無法同步：
Slave_SQL_Running:NO
原因：a,程式可能在slave上進行了寫操作 b,也可能是slave重啟後，進行了交易回復
解決辦法:
(1)
mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;
(2)
查看binlog，然後實行手動同步binlog日誌
mysql>slave stop;
mysql>show master status;
mysql>change master to
master-host=192.168.10.1,
master-user=user,
master-password=pass,
master-port=3306,
master_log_file="mysql-bin.000003",
master_log_pos=98;
mysql>slave start;
Mysql分區：
分區 優點 缺點 共性
RANG分區 適合於日期類型，支援複合分區 有限分區 一般只針對某一列

LIST分區 適合於有固定取值的列，支援複合分區 有限分區，插入的值
不在LIST中，則資料丟失 一般只針對某一列

HASH分區 線性hash使得增加、刪除和分區合并 線性hash的資料分布不均勻，
更快捷一般的hash比較均勻 一般只針對某一列
KEY分區 列為字元型等其他非Int類型效率較之前較低，因為有複
雜的Function Compute 一般只針對某一列
查看使用的引擎
show engines;
查看是否支援分區
show plugins;
example by hash:
create table t2(
id int)
engine=myisam
partition by hash(id)
partitions 5;
預存程序加資料
mysql>\d // 換定界符
mysql>create procedure p3()
->begin
->set @i=1;
->while @i<10000 do
->insert into t3 values(@i);
->set @[email protected]+1;
->end while;
->end //

執行儲存:call p3();

innodb做分區必須設定
innodb_file_per_table = 1
才可以做分區。否則索引和資料都存在ibdata1裡面。
因為innodb分共用資料表空間和獨立資料表空間

Mysql基礎操作：
mysql表複製
結構表複製：create table t3 like t1;
資料表複製：insert into t3 select * from t1;
一條語句資料結果和資料：CREATE TABLE user1 AS SELECT * FROM `user`
只有複製資料：CREATE TABLE user1 AS SELECT * FROM `user` WHERE 1=2
取隨機數
SELECT * FROM FetionSquareContent WHERE id < 420 ORDER BY RAND() LIMIT 5;
使用rollup統計出更多的資訊，不可以和order by 一起使用
SELECT GroupID,COUNT(groupId) AS cnt FROM FG_GroupTopic GROUP BY GroupID WITH rollup

設定欄位預設為null 這樣會用到索引
and 和 or 前後面的欄位都的加索引，不然索引無效
設定的是字串索引，如果搜尋整數型時，最好加上雙引號，不然用不到索引

optimize:最佳化資料表空間、片段
optimize table t4;

匯入和匯出資料庫的某一列
匯出:select name from t4 into outfile "/tmp/test.txt";
匯入：load data infile "/tmp/test.txt" into table t4(name);
匯入前關閉普通索引：alter table t1 disable keys;
匯入後開啟普通索引：alter table t1 enable keys;
關閉唯一索引: set unique_checks=0;
開啟唯一索引：set unique_checks=1;

關掉分組排序提高效能
select * from t2 group by class order by null
讀鎖，只能讀不能做其它操作
lock table t1 read;
解鎖：unlock tables;
寫鎖：其它使用者 讀 修改 刪除都不能操作
lock table t1 write

日誌:
vi /etc/my.cnf
log-bin=mysql-bin
log_slow_queries=slow.log
忘記密碼如何修改
service mysqld stop
跳過登入
/user/local/mysql/mysqld_safe --skip-grant-tables --user=mysql &

==================2014======================
授權user使用者從伺服器登入許可權密碼為456
grant all on *.* to [email protected] identified by "456";

從192.168.10.2這台伺服器登入到10.1
/usr/local/mysql/bin/mysql -uuser -p456 -h192.168.10.1
===================mysql分區========================

//RANG分區，根據ID的定界分割
CREATE TABLE `exployee`.`exployeeRANGE` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`fname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`lname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`hired` DATE NOT NULL ,
`separated` DATE NOT NULL ,
`job_code` INT( 12 ) NOT NULL ,
`store_id` INT( 10 ) NOT NULL,
PRIMARY KEY(id,store_id)
) ENGINE = InnoDB PARTITION BY RANGE (store_id)(
PARTITION p0 VALUES less than( 6 ) ,
PARTITION p1
VALUES less than( 11 ) ,
PARTITION p2
VALUES less than( 16 ) ,
PARTITION p3
VALUES less than( 21 )
);

//LIST分區，根據地區分區
CREATE TABLE `exployee`.`exployeeLIST` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`fname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`lname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`hired` DATE NOT NULL ,
`separated` DATE NOT NULL ,
`job_code` INT( 12 ) NOT NULL ,
`store_id` INT( 10 ) NOT NULL,
PRIMARY KEY(id,store_id)
) ENGINE = myisam PARTITION BY LIST (store_id)(
PARTITION pNorth VALUES in( 3,5,6,9,17 ) ,
PARTITION pEast VALUES in( 1,2,10,11,19,20 ) ,
PARTITION pWest VALUES in( 4,12,13,14,18 ) ,
PARTITION pCenter VALUES in( 7,8,15,16 )
);

//HASH分區，根據年份分區
CREATE TABLE `exployee`.`exployeeHASH` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`fname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`lname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`hired` DATE NOT NULL ,
`separated` DATE NOT NULL ,
`job_code` INT( 12 ) NOT NULL ,
`store_id` INT( 10 ) NOT NULL,
PRIMARY KEY(id,hired)
) ENGINE = myisam PARTITION BY HASH(YEAR(hired))(
PARTITIONS 4
);

常用Mysql命令