MySQL常用命令
标签(空格分隔): mysql
1.连接数据库的格式
mysql -h IP -u用户名 -p密码;
1.1连接远程数据库
mysql -h 192.168.1.214 -uroot -p123456
1.2连接本地数据库
mysql -uroot -p123456
2.查看数据库版本
mysql> status;
3.查看数据库
show databases;
4.查看数据库中的用户
select user from user;
select host,user,password from mysql.user\G;
5.更改用户密码
5.1更改本地root用户密码
格式:mysqladmin -u用户名 -p旧密码 password 新密码
mysqladmin -uroot -p123456 password ilanni
注意:新安装的mysql数据库,由于开始时root没有密码,所以在第一次修改root用户密码时,-p旧密码一项就可以省略。如下:
mysqladmin -uroot password 123456
5.2更改远程root用户密码
mysqladmin -h 192.168.1.214 -uroot -p123456 password ilanni
5.3更改普通用户密码
格式:update user set password=PASSWORD(‘new_password’) where user=’username’;
update user set password=PASSWORD('123456′) where user='ailanni';
FLUSH PRIVILEGES;
–立即生效,不需要重启
6.创建数据库
create database ailanni;
ps aux |grep mysqld 查看mysql的运行所在的位置,以及数据文件存放位置
ls /usr/local/mysql/data
7.使用数据库
use ailanni;
8.删除数据库
drop database ailanni;
此时会同时会删除OS上的数据库文件夹,如下:
ls /usr/local/mysql/data
9.创建数据库的表
create table ilannitable(id int(4));
10.查看数据库的表
show tables;
11.显示数据库表的结构
describe ilannitable;
12.向表中插入数据
insert into ilannitable values(1);
insert into ilannitable values(2);
13.删除插入的数据
delete from ilannitable where id='1′;
14.清空表
delete from ilannitable;
15.删除表
drop table ilannitable;
16.创建用户
格式:create user username@ip identified by ‘password’;
create user 'ailanni'@'%' identified by 'ailanni';
17.删除用户
drop user alianni;
18.用户授权
格式:grant privileges on database.table to user@ip identified by ‘password’;
注意该命令可以同时创建用户并授权,也就是说我们不需要先创建用户。
grant all on ailanni.* to 'ailanni'@'%' identified by 'ailanni';
FLUSH PRIVILEGES;
privileges权限分类:
all: 允许做任何事(和root一样)。这个平时使用的比较多,实际生产环境中就是一个用户对一个数据库。
usage: 只允许登录–其它什么也不允许做。
alter: 修改已存在的数据表(例如增加/删除列)和索引。
create: 建立新的数据库或数据表。
delete: 删除表的记录。
drop: 删除数据表或数据库。
index: 建立或删除索引。
insert: 增加表的记录。
select: 显示/搜索表的记录。
update: 修改表中已存在的记录。
replication slave: 权限应授予从服务器所使用的将当前服务器连接为主服务器的账户。没有这个权限,从服务器不能发出对主服务器上的数据库所发出的更新请求。这个权限在配置mysql主从时,会使用到。
注意在给用户授权时,一定不要这样授权:
grant all on . to ‘ailanni’@’%’ identified by ‘ailanni’;
因为这样就相当于把ailanni这个用户授予最高权限,具有管理所有数据库的能力,相当于root用户的权限。这样是非常不安全的。
19.查看用户权限
show grants for 'ailanni'@'%';
20.用户权限删除
格式:revoke privilege on database.table from user@ip identified by ‘password’;
revoke all on ailanni.* from 'ailanni'@'%' identified by 'ailanni';
21.数据库备份
导出全库备份到本地的目录
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --lock-all-tables --add-drop-database -A > db.all.sql
导出指定库到本地的目录(例如mysql库)
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --lock-all-tables --add-drop-database --databases mkey3gdbnew > /home/mkey3g/mkey3gdbnew.sql
导出某个库的表到本地的目录(例如mysql库的user表)
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --tables mysql user> mysql.user.sql
导出指定库的表(仅数据)到本地的目录(例如mysql库的user表,带过滤条件)
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --no-create-db --no-create-info --tables mysql user --where="host='localhost'"> mysql.user.sql
导出某个库的所有表结构
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --no-data --databases mysql > mysql.nodata.sql
22.数据库恢复
mysql -hhostname -uusername -ppassword databasename< backupfile.sql;
mysql数据库的恢复刚好和备份相反。
23.有关数据库用户及数据库的删除顺序
在删除数据库或者用户时,一定要先进行权限的删除。然后在进行数据库和用户的删除,否则会产生其他不可预知的错误。
24.mysql数据库的启动与关闭
24.1 源码编译安装
如果mysql是源码编译安装,我们可以通过mysqld_safe命令来启动,如下:
ls -l /usr/local/mysql/bin/
/usr/local/mysql/bin/mysqld_safe &
关闭mysql,可以通过kill命令,也可以使用pkill命令。
kill -9 mysqld_pid
pkill mysqld
24.2 rpm包安装
启动与关闭mysql,可以直接使用service命令,也可以直接在/etc/init.d/mysqld启动。如下:
/etc/init.d/mysqld
service mysqld start
其他常用命令
mysql> select version(); 查看版本
mysql> SHOW ENGINES; 查看数据库存储引擎
mysql> show databases; 查看数据库
mysql> show variables like ‘%char%’; 查看字符集
mysql> show variables like ‘%datadir%’; 查看数据存储位置 du -h –max-depth=1 /var/lib/mysql
设置远程登陆
mysql> GRANT ALL privileges ON urmp_prd.* TO 'urmpprd'@'%' identified by "Xianzai@2602";flush privileges;
禁用远程登陆
mysql> GRANT ALL privileges ON *.* to root@'10.92.82.34' identified by "Xianzai.123"; flush privileges;
优化数据库表
mysql> analyze table mip_syslog;
mysql> optimize table mip_syslog;
查看数据库存储引擎
mysql >show engine innodb status \G;
mysql> SELECT TABLE_SCHEMA,TABLE_NAME, TABLE_TYPE, ENGINE FROM information_schema.TABLES WHERE TABLE_NAME = 'mip_syslog';
修复受损数据库
mysql>myisamchk -o -f /home/mkey3g/data/mysql/mkey3gdbnew/mip_visit_log.MYI
查看数据库索引
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 6), ' MB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'onelog';
查看数据库大小
SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'onelog';
查看数据库表大小
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
table_rows AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'
FROM information_schema.TABLES
WHERE table_schema LIKE 'onelog';
查看数据库表大小(指定表)
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "onelog" AND table_name = "sysuser";