mysql的基礎操作命令

標籤：mysql

命令的總結：ss、killall、kill、pkill

關閉資料庫方法：

mysqladmin -uroot -pPassword shutdown

/etc/inti.d/mysqld stop

kill -USR2 $(cat path/pid)

更改mysql資料登入提示符的方法：

1.mysql> prompt \[email protected] \r:\m\s->

  PROMPT set to ‘\[email protected] \r:\m\s->‘

  [email protected] 10:41:22->

2.vim my.cnf

  [mysql]

  prompt=\\[email protected] \\r:\\m:\\s->

使用mysql的help，太厲害了吧。。。

設定root密碼：

mysqladmin -uroot password ‘setpassword‘

更改密碼：

mysqladmin -uroot -p‘oldpassword‘ password ‘newpassword‘ -S /data/3306/mysql.sock

適合忘記root密碼的密碼更改：--skip-grant-tables

mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables &

mysqld_safe --skip-grant-tables --user=mysql &

update mysql.user set password=password(‘newpassword‘) where user=‘root‘ and host=‘localhost‘;

flush privileges;

沒有root密碼：

mysql> set password=password(‘setpassword‘);

mysql> flush privileges;

SQL語句的使用：

DQL（資料查詢語言）、DML（資料操縱語言）、TPL（交易處理語言）、DCL（資料控制語言）、DDL（資料定義語言 (Data Definition Language)）、CCL（指標控制語言）

DQL: select

DML: insert , update , delete

TPL: begin , transaction , commit , rollback

DCL: grant , revoke

DDL: create , drop 

CCL: declare cursor, fetch into, update where current

select database();

select user();

drop刪除不了的時候使用delete刪除，一般指使用者。

建立使用者並授權：

grant all privileges on dbname.* to [email protected] identified by ‘password‘;

flush privileges;

查看使用者權限：

show grant for [email protected];

收回使用者權限：

revoke insert on dbname.* from [email protected];

生產環境的使用者授權：

1、blog、CMS：

安裝期間需要select, insert, update, delete, create, drop。安裝完成後收回create和drop。

常規情況下：select, insert update, delete.

2、bbs，discuz：

都需要select, insert, update, delete, create, drop

對於主從的授權：

主庫（寫為主讀為輔）：

本機：lnmp，lamp

grant all privileges on blog.* to ‘blog‘@‘localhost‘ identified by ‘blog‘;

應用伺服器和資料庫伺服器不在一個主機上的授權：

grant all privileges on ‘blog‘.* to ‘blog‘@‘hostname‘ identified by ‘blog‘;

嚴格的授權：重視安全，忽略方便：

grant select, insert, update, delete on ‘blog‘.* to ‘blog‘@‘hostname‘ identified by ‘blog‘;

從庫（唯讀）：

grant select on ‘blog‘.* to ‘blog‘@‘hostname‘ identified by ‘blog‘;

主從進階授權策略：

第一種：

寫庫：blog   password：password   port：3306    hostname：10.0.0.x

讀庫：blog   password: password   port: 3306    hostname: 10.0.0.x

第二種：

寫庫：blog_w password: password   port: 3306    hostname: 10.0.0.x

讀庫：blog_r password: password   port: 3306    hostname: 10.0.0.x

授權的具體命令：

主庫：

grant select, insert, update, delete on ‘blog‘.* to ‘blog‘@‘10.0.0.x‘ identified by ‘blog‘;

從庫：

grant select on ‘blog‘.* to ‘blog‘@‘10.0.0.x‘ identified by ‘blog‘;

從庫除了做select外，還可以加read-only參數，嚴格控制web使用者寫從庫。

主從庫的mysql庫和表是同步的，無法針對同一個使用者授權不同的許可權。因為，主庫授權後會自動同步到從庫上，導致從庫的授權唯讀失敗。

resolve funciton：

1.取消mysql庫的同步。

2.授權主庫許可權後，從庫執行收回增刪改。

3.不在授權上控制增刪改，而是用read-only參數，控制普通使用者更新從庫，注意，read-only參數對超級使用者無效。

查看mysql使用者授權：

show grant for ‘username‘@‘hostname‘;

資料表的操作：

create table tname(

field type,

field type,

field type);

為表的欄位建立索引：

create table tname(

field type auto_increment,

field type,

field type

primary key(id),

key index_name(name)

);

刪除主鍵：

alter table tname drop primary key;

增加主鍵：

alter table tname change id id int primary key auto_increment;

刪除索引：

alter table student drop index index_name;

在某列上添加索引：

alter table tname add index index_name(name);

對欄位的前n個字元建立普通索引：

create index index_name on tname(col_name(8));

對錶的多個欄位建立聯合索引：

create index index_name on tname(col_name1, col_name2);

show index form tname\G

刪除索引：

drop index index_nanme on tname;

對錶的多個欄位的前n個字元列建立聯合索引：

create index index_name on tname(col_name1(8), col_name2(10));

按條件列查詢時，聯合索引是有首碼生效特性的。

index(a,b,c)僅a, ab, abc三個查詢條件列表可以走索引。b, bc, ac, c等無法走索引。

建立唯一非主鍵索引：

create unique index index_name on tname(col_name);

小表無需建立索引，寫頻繁，讀少的業務要少建立索引。

select user,host from mysql.user where host=....索引一定要建立在條件列，而不是select後的選擇資料的列，另外我們要盡量選擇在唯一值多的大表上建立索引。

建立主鍵索引：

alter table tname change id id int primary key auto_increment;

刪除主鍵索引：

alter table tname drop primary key;

建立普通索引：

alter table tname add index index_name(col_name(8));

create index index on tname(col_name(8));

create index index_name on tname(col_name1, col_name2);

刪除普通索引：

alter table tname drop index index_name;

alter和create的協助相關

help alter table;

插入資料：

insert into tname (col1, col2...) values (value1, value2...);

查詢select語句的執行計畫：

explain select * from tname;

help explain

手冊需要掌握： 5， 6， 7， 8， 10， 11， 13， 14， 15

查看mysql二進位日誌使用mysqlbinlog命令查看二進位記錄檔。二進位記錄檔只記錄更改，不記錄查詢。

# mysqlbinlog mysql-bin.log

使用二進位日誌恢複資料的思路及方法：

首先使用資料庫的全備份，恢複到全備份的那個時候的資料，然後再使用二進位日誌恢複從全備份到資料出錯的時間段的資料。命令如下：

mysql -uroot -proot dbname < /path/to/dbname.sql

mysqlbinlog mysql-bin.000001 > mysql-bin.sql

在mysql.sql檔案中找到相關出錯時間的出錯語句，刪除之。在恢複到dbname中即可

mysql -uroot -proot dbnmae < /path/to/mysql-bin.sql

恢複完成。

防止mysql的誤操作：使用mysql -U的命令登入。

刪除表資料：

delete from tname;表中的資料一條一條刪除。

truncate table tname;先drop掉整個表，然後重新建立該表。（效率高）

增刪改表的列（field）：

alter table tname add/change.......

更改表名：

rename table old_tname to new_tname;

sql語句重點：建庫、建表、索引、列

解決mysql亂碼的5種方法：

1.登入mysql後執行：set names utf8;

2.在sql檔案中指定：set names utf8; 登入mysql執行：source dbname.sql

3.在sql檔案中指定：set names utf8; 使用mysql匯入：mysql -uroot -proot dbname < dbname.sql

mysql -uroot -proot -e "set names utf8; select * from dbname.tname;"

4.通過mysql匯入指定字元集：mysql -uroot -proot --default-character-set=utf8 dbname < dbname.sql

5.修改設定檔：vim my.cnf

[client]

default-character-set=utf8

[mysqld]

default-character-set=utf8 5.1版本

character-set-server=utf8 5.5版本

linux，用戶端，服務端，庫，表，程式，使用同一字元集。

show variables;

show global status;

set global key_buffer_size=

show vairables like ‘key_buffer%‘;

my.cnf 加 key_buffer_size=

mysql -e

mysqldump, mysqlbinlog, mysql, mysqladmin

修改mysql資料庫表的字元集：

alter database character set * 

alter table tname character set * 

對已有的資料表不能修改，只能修改新建立的。

對已有的資料表的字元集修改，需要匯出資料，修改字元集後重新匯入即可：

alter database dbname character set utf8;

1.匯出表結構：

mysqldump -uroot -p --default-character-set=gbk -d dbname > dbname.alltable.sql

--default-character-set=gbk表示用gbk串連，-d只匯出表結構。

2.編輯dbname.sql將原字元集改成現在的字元集

3.確保資料庫不再更新，匯出所有資料

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 dbname > alldata.sql

--quick：用於轉儲大的表，強制mysqldump從伺服器一次一行的檢索資料而不是檢索所有行，並輸出前cache到記憶體中。

--no-create-info：不建立create table語句。

--extended-insert：使用包括幾個values列表的多行insert文法，這樣檔案更小，IO也小，匯入資料時會更快。

--default-character-set=utf8：按照原有字元集匯出資料，這樣匯出的檔案中，所有中文到時可見的，不會儲存成亂碼。

4.編輯alldata.sql將set names utf8改成gbk

5.建庫

create database dbname default charset gbk;

6.建立表，執行dbname.alltable.sql

mysql -uroot -proot dbname < dbname.alltable.sql

7.匯入資料：

mysql -uroot -proot dbname < alldata.sql

使用mysqldump備份所有的資料庫的命令：

mysql -uroot -proot -e "show databases;" | grep -Evi "database|infor|perfor" | sed -r ‘s#^([a-z].*$)#mysqldump -uroot -proot -B \1|gzip >/opt/\1.sql.gz#g‘ | bash

mysql的基礎操作命令