設定更改root密碼、串連mysql、mysql常用命令

標籤：mysql用法

設定更改root密碼

1、將mysql加入環境變數中

[[email protected] ~]# grep mysql /etc/profile

export PATH=/usr/local/mysql/bin/:$PATH

2、直接登入，無密碼

[[email protected] ~]# mysql -uroot

3、設定密碼

[[email protected] ~]# mysqladmin -uroot password ‘123456‘

Warning: Using a password on the command line interface can be insecure.

You have new mail in /var/spool/mail/root

4、登入

[[email protected] ~]# mysql -uroot -p‘123456‘ -h127.0.0.1 -P3306

-h：指定主機

-P：指定連接埠

5、更改密碼為1234

[[email protected] ~]# mysqladmin -uroot -p‘123456‘ password ‘1234‘

忘記密碼後，怎麼更改？

1、[[email protected] ~]# vi /etc/my.cnf

[mysqld]

skip-grant#跳過授權登入

2、重啟mysql服務

[[email protected] ~]# /etc/init.d/mysqld restart

3、[[email protected] ~]# mysql -uroot

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set password=password(‘123456‘) where user=‘root‘; #更改密碼

4、將skip-grant配置刪除後重啟mysql服務

/etc/init.d/mysqld restart

5、已更改密碼

串連mysql

1、[[email protected] ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306

2、通過socket串連

[[email protected] ~]# mysql -uroot -p123456 -S/tmp/mysql.sock

mysql常用命令

1、顯示所有資料庫

show databases;

2、進入哪個資料庫

user db

3、顯示資料庫中所有的表

mysql> show tables;

4、顯示表結構（由什麼欄位組成）

desc user;

5、查詢

select host,user from user;

6、顯示建表語句過程

show create table user\G;

7、顯示哪個使用者登入

select user();

8、顯示表有多少行

select count(*) from user ;

9、顯示變數

show variables;

10、尋找

show variables like ‘%error%‘;

11、設定臨時變數

mysql> set global max_connect_errors = 1000;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘max_connect_errors‘;

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| max_connect_errors | 1000  |

+--------------------+-------+

11、顯示mysql的進程隊列

mysql> show processlist;

+----+------+-----------+-------+---------+------+-------+------------------+

| Id | User | Host      | db    | Command | Time | State | Info             |

+----+------+-----------+-------+---------+------+-------+------------------+

|  3 | root | localhost | mysql | Query   |    0 | init  | show processlist |

+----+------+-----------+-------+---------+------+-------+------------------+

1 row in set (0.01 sec)

mysql> show full  processlist;  #完整情況

+----+------+-----------+-------+---------+------+-------+------------------------+

| Id | User | Host      | db    | Command | Time | State | Info                   |

+----+------+-----------+-------+---------+------+-------+------------------------+

|  3 | root | localhost | mysql | Query   |    0 | init  | show full  processlist |

+----+------+-----------+-------+---------+------+-------+------------------------+

12、建立表，欄位

mysql> create table tb(`id` int,`num` varchar(10));

Query OK, 0 rows affected (0.02 sec)

mysql> show tables

    -> ;

+----------------+

| Tables_in_test |

+----------------+

| tb             |

+----------------+

1 row in set (0.00 sec)

mysql> show create table tb;

+-------+--------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                             |

+-------+--------------------------------------------------------------------------------------------------------------------------+

| tb    | CREATE TABLE `tb` (

  `id` int(11) DEFAULT NULL,

  `num` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------------------------------------------+

mysql> desc tb;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)     | YES  |     | NULL    |       |

| num   | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

13、刪除庫及表

drop database test;

drop table tb;

14、插入資料

insert tb value(1,4);

本文出自 “探索發現新事物” 部落格，請務必保留此出處http://jacksoner.blog.51cto.com/5802843/1982740

設定更改root密碼、串連mysql、mysql常用命令