mysql常用操作命令

標籤：mysql

Mysql 忘記root密碼

方法一：
在/etc/my.cfg [mysqld]下面添加
skip-grant-tables     或是skip-grant

重啟MySQL
use mysql;
UPDATE user SET Password = password ( ‘new-password‘ ) WHERE User = ‘root‘ ;
mysql flush privileges;
將my.cfg檔案修改回來 ，重啟MySQL即可。

方法二：

在啟動Mysql伺服器時加上參數--skip-grant-tables來跳過授權表的驗證
(./safe_mysqld --skip-grant-tables &)，這樣我們就可以直接登陸Mysql伺服器，
然後再修改root使用者的口令，重啟Mysql就可以用新口令登陸了。

添加環境變數
export PATH=/usr/local/mysql/bin:$PATH  加入到/etc/profile.d/mysql.sh中


只需本機使用Mysql服務
在啟動時還可以加上--skip-networking參數使Mysql不監聽任何TCP/IP串連
(./safe_mysqld --skip-networking &)，增加安全性。

查看支援的儲存引擎

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.01 sec)

查看當前MySQL的預設資料引擎

mysql> show variables like ‘%engine%‘;+---------------------------+--------+| Variable_name             | Value  |+---------------------------+--------+| default_storage_engine    | InnoDB || engine_condition_pushdown | ON     || storage_engine            | InnoDB |+---------------------------+--------+3 rows in set (0.00 sec)mysql> show variables;   查看相關參數

查看和修改最大串連數

使用MySQL 資料庫的網站，當訪問串連數過多時，就會出現 "Too many connections" 的錯誤。
出現這種錯誤有兩種情況:

一種是網站訪問量實在太大，伺服器已經負擔不起，此時就應該考慮負載平衡或者其它減少伺服器壓力的辦法。

另一種情況就是 MySQL最大串連數設定得太小，當訪問量稍大就出現串連過多的錯誤。
 
show variables like ‘%max_connections%‘;
要對 mysql最大串連數進行修改，只需要在my.cnf 設定檔裡面修改max_connections的值，然後重啟mysql就行。

如果my.cnf 檔案中沒有找到 max_connections 條目，可自行添加以下條目。
max_connections = 220
 或是
mysql>set global max_user_connections = 200; [僅本次修改有效]

即時查看mysql當前串連數
前提：對使用者和遠程主機有授權

mysql> grant all on *.* to [email protected] identified by ‘123456‘;
mysql> flush privileges;

[[email protected] ~]# mysqladmin -uroot -p -h192.168.9.9  processlist;

只查看當前串連數(Threads就是串連數）
[[email protected] ~]# mysqladmin -uroot -p123456 status;
Uptime: 4449  Threads: 2  Questions: 18697  Slow queries: 0  Opens: 77  Flush tables: 1  Open tables: 31  Queries per second avg: 4.202

mysql> show full processlist;

或者

mysql> show status;

查看一下所有串連進程，注意查看進程等待時間以及所處狀態 是否locked
如果進程過多，就把進程列印下來，然後查看.
mysql -e ‘show full processlist;‘ -p > 111.txt
尋找非locked的進程，一般就是當前執行中卡死，導致後面的進程排隊的原因。

查看MySQL串連數和目前使用者Mysql串連數
先用管理員身份進入mysql提示符。
mysql -uroot -pxxxx
mysql >show processlist;         #可以顯示前100條串連資訊
mysql >show full processlist;    #可以顯示全部。如果用普通帳號登入，就只顯示這使用者的。

修改MySQL監聽連接埠
 1.如果安裝的是系統內建的rpm包的情況：
 修改 /etc/my.cnf
 [mysqld]
 datadir=/var/lib/mysql
 port=3300                    #修改成你自己想要的連接埠
 socket=/var/lib/mysql/mysql.sock
 [mysql.server]
 user=mysql
 basedir=/var/lib
 [safe_mysqld]
 err-log=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

2.如果安裝的是源碼包或者在同一伺服器上安裝多個mysqld服務情況：
 找出my.cnf 檔案。添加port= 連接埠號碼
 需要注意的是
 [client]
 port            = 3300
 這裡也要改一下。

在同一台機器上配置兩個MySQL服務（跑兩個連接埠）
cd /usr/local/;

cp -r mysql mysql_2

cd mysql_2;
初始化mysql2

./scripts/mysql_install_db --user=mysql --datadir=/data/mysql2   
拷貝設定檔：

cp /etc/my.cnf ./my.cnf
修改設定檔相關參數：vim my.cnf  #更改port 以及socket
啟動：/usr/local/mysql_2/bin/mysqld_safe --defaults-file=/usr/local/mysql_2/my.cnf --user=mysql &
若開機啟動它，需加入到 /etc/rc.local中

查看mysql伺服器目前的版本，當前日期，目前使用者

mysql> select version();
mysql> select current_date();

mysql> select user();

查看mysql狀態

mysql> show status;

修改mysql參數
mysql> show variables like ‘max_connect%‘;

mysql> set global max_connect_errors = 1000;

開啟伺服器　　
/etc/rc.d/init.d/mysqld start

或者
mysqld_safe  --user=mysql &

停止伺服器
/etc/rc.d/init.d/mysqld stop
或者
/usr/bin/mysqladmin -u root -p shutdown

更改MySQL目錄
MySQL預設的資料檔案儲存目錄為/var/lib/mysql。假如要把目錄移到/home/data下需要進行下面幾步：
1、home目錄下建立data目錄
mkdir /home/data
2、把MySQL服務進程停掉：
mysqladmin -u root -p shutdown
3、把/var/lib/mysql整個目錄移到/home/data
mv /var/lib/mysql　/home/data/
這樣就把MySQL的資料檔案移動到了/home/data/mysql下
4、找到my.cnf設定檔
如果/etc/目錄下沒有my.cnf設定檔，請到/usr/share/mysql/下找到*.cnf檔案，拷貝其中一個到/etc/並改名為my.cnf中。命令如下：
[[email protected] mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf
5、編輯MySQL的設定檔/etc/my.cnf
為保證MySQL能夠正常工作，需要指明mysql.sock檔案的產生位置。 修改socket=/var/lib/mysql/mysql.sock一行中等號右邊的值為：/home/mysql/mysql.sock 。
操作如下：
vim /etc/my.cnf
# The MySQL server
[mysqld]
port　　　= 3306
#socket　 = /var/lib/mysql/mysql.sock（原內容，為了更穩妥用#注釋此行）
socket　 = /home/data/mysql/mysql.sock　　　（加上此行）
6、修改MySQL啟動指令碼/etc/rc.d/init.d/mysql
修改MySQL啟動指令碼/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等號右邊的路徑改成你現在的實際存放路徑：/home/data/mysql。
[[email protected] etc]# vi　/etc/rc.d/init.d/mysql
#datadir=/var/lib/mysql　　　　（注釋此行）
datadir=/home/data/mysql　　   （加上此行）
7、重新啟動MySQL服務
/etc/rc.d/init.d/mysql　start
如果工作正常移動就成功了，否則對照前面的7步再檢查一下。

賦權　　
myaql >grant all on dbname.* to [email protected]% identified by ‘password‘;
mysql >flush privileges;重新整理許可權表　

最佳化表　

mysql> use qqtexas;
mysql> optimize table users;

mysql> optimize table users,userid;

或者進行最佳化並檢查修複任務

[[email protected] ~]# mysqlcheck -o dbname tblA tblB  tblC -u root -p

對錶進行分析　　
mysql> use qqtexas;

mysql> analyze table tblA;
或
[[email protected] ~]# mysqlcheck -a dbname [tblA  tblB tblC ] -u root -p

對錶進行檢查　

mysql> use qqtexas;

mysql> check table users;

從命令列登入MySQL資料庫伺服器

1. 登入使用預設3306連接埠的MySQL
   /usr/local/mysql/bin/mysql -u root -p

2. 通過TCP串連管理不同連接埠的多個MySQL（注意：MySQL4.1以上版本才有此項功能）
   /usr/local/mysql/bin/mysql -u root -p --protocol=tcp --host=localhost --port=3307

3. 通過socket通訊端管理不同連接埠的多個MySQL
   /usr/local/mysql/bin/mysql -u root -p --socket=/tmp/mysql3307.sock

4. 通過連接埠和IP管理不同連接埠的多個MySQL
   /usr/local/mysql/bin/mysql -u root -p -P 3306 -h 127.0.0.1
   

修改登入密碼
MySQL預設沒有密碼，安裝完畢增加密碼的重要性是不言而喻的。
usr/bin/mysqladmin -u root password ‘new-password‘
格式：mysqladmin -u使用者名稱 -p舊密碼 password 新密碼

顯示資料表的結構

mysql> use qqtexas;

mysql> desc tablesname;

顯示表的建立資訊
show create table tablesname;

更改表結構，將zhangyan表username欄位的欄位類型改為CHAR(25)
ALTER TABLE zhangyan CHANGE username username CHAR(25);

建立名稱為ocean的資料庫

mysql> create database ocean;

mysql> drop database ocean;

mysql> drop tables tablesname;

將表中記錄清空
mysql> delete from tablesname;

mysql> truncate table db1.t1;

將目前的目錄下的mysql.sql匯入到資料庫中
mysql> source ./mysql.sql;

添加主鍵： alter table 表名 add primary key(列名)
刪除主鍵： alter table 表名 drop primary key;
給某列改名： alter table 表名 change 列名 屬性列表

比如alter table student change id no char(4)
添加某列：
1 在表的最前面添加列 比如給student表添加id屬性 放在表的最前面
alter table student add id int(4) not null auto_increment primary key first;
2 在表的某個列後加列 比如給student表添加id屬性 放在列no的最前面
alter table student add a int(11) afer no;
刪除某列： alter table 表名 drop column 列名;
更改某列屬性 比如修改student no欄位屬性 alter table student modify no char(4);
添加索引：alter table 表名 add index 列名
刪除索引：alter table 表名 drop index 列名
刪除外鍵：alter table 表名 drop foreign key 約束名
limit的使用：
select * from 表名 limit 2;//如果只給定一個參數，它表示返回最大的記錄行數目
select * from 表名 limit 1,3;// 第一個參數指定第一個返回記錄行的位移量，第二個參數指定返回記錄行的最大數目.

本文出自 “從心開始” 部落格，請務必保留此出處http://hao360.blog.51cto.com/5820068/1655572

mysql常用操作命令