MySQL Common Operations Command

########## #mysql常用操作命令 #############

1. Install MySQL
Yum install MySQL Mysql-server
/etc/init.d/mysqld start# #开启mysqld服务

2. Setup and Login
Mysql_secure_installation# #第一次安装mysql以后通过这条命令可以对mysql进行初始设置
Mysql-uroot-predhat# #从本机登录mysql数据库 (ps-aux|grep mysql kill-9)
Mysqladmin-uroot-predhat Password Westos# #修改本地mysql, root password
Mysqladmin-uroot-predhat-h 172.25.8.1 Password Westos# #修改远程172.25.8.1mysql Server, root password

3. Operation commands
Library Operations:
show databases;# #显示数据库
Use MySQL;# #进入数据库 (press ENTER to show the success of the database changed operation!) )
Show tables;# #显示数据库中的表
DESC user;# #查看user表的结构
Flush privileges;# #刷新数据库信息
Select Host,user,password from user;# #查询user表中的host, user,password field
Create Database Westos;# #创建westos数据库
Use Westos;# #进入数据库westos

Table Operation:
CREATE TABLE Linux (# #创建表, table name Linux, field Username,password
Username varchar () NOT NULL,
Password varchar () not NULL);

SELECT * from Mysql.user; # #查询mysql库下的user表中的所有内容

ALTER TABLE Linux add age varchar (4); # #添加age字段到linux表中

Desc Linux; # #查看linux表结构

ALTER TABLE Linux DROP age # #删除linux表中的age字段

ALTER TABLE Linux ADD Age VARCHAR (4) after username # #在linux表username字段后添加字段age

Desc Linux;# #查看linux表结构

INSERT into Linux values (' User1 ', ' passwd1 ');# #在linux表中插入Username=user1,age=18,password=password1
Update Linux set password= ' Passwd2 ' where username= "user1";# #更新linux表中user1的密码为password2
Delete from Linux where username= ' user1 ';# #删除linux表中user1的所有内容
SELECT * from Linux;# #可以进行查看

User management:
CREATE USER [email protected] identified by ' Westos ';# #创建本地用户hjy并添加密码westos, the default password is encrypted
CREATE USER [email protected] '% ' identified by ' Redhat ';# #创建用户hee,% indicates that this account can be logged in on any host
Select Host,user,password from User;# #查询user表中的host, user,password field

Grant SELECT On * * to [e-mail protected] identified by ' PASSWD1 ';# #授权user1, the password is PASSWD1, and you can only query the contents of your database locally
Grant all on mysql.* to [email protected] '% ' identified by ' passwd2 ';# #授权user2, the password is PASSWD2, you can log in to MySQL from any remote host and can do anything to MySQL database (% to IP can specify this IP login)

FLUSH privileges;# #重载授权表
SHOW GRANTS for [email protected];# #查看用户授权
REVOKE Delete,update,insert on mysql.* from [email protected];# #撤销用户对mysql的DELETE, Update,insert permissions
REVOKE all on mysql.* from [email protected];# #撤销用户所有权限
DROP USER [email protected];# #删除用户


Backup
/var/lib/mysql
Mysqldump-uroot-predhat--all-databases# #命令备份所有数据
Mysqldump-uroot-predhat mysql > Mysql.bak# #备份mysql库导到mysql. bak
MYSQL-UROOT-PREDHAT-E "CREATE database Westos;"# #创建一个数据库
Mysql-uroot-predhat Westos < Mysql.bak# #恢复mysql. bak to Westos library

Password recovery
/etc/init.d/mysqld stop
Mysqld_safe--skip-grant-tables & # #跳过grant-tables Authorization form, no authentication required login to local MySQL database
Update Mysql.user set Password=password (' Westos ') where user= ' root '; # #更新mysql The password for the condition root in the user table is Westos
/etc/init.d/mysql Restart # #重新启动nysql

This article is from the "12148275" blog, please be sure to keep this source http://12158275.blog.51cto.com/12148275/1910441

MySQL Common Operations Command