MySQL Common statements

1. Basic View and Login:
   Mysql-u User-p password
   show databases; # #查看数据库
   Use library name; # #进入数据库
   Show tables; # #查看表
   DESC table name; # #查看表的结构, table header

2. Table operation: Check, change, delete, increase
   Select field from table \g; # #查询表中的所有数据记录
   Update library name. Table Name Set field = new where condition expression; # #修改更新数据
   Delete from library name. Table name where conditional expression; # #删除数据记录
   Create database name; # #创建库
   CREATE table library name. Table name (field); # #创建表
   Insert into library name. Table Name values (' field value ', ' field value '); # #新增记录
   drop table library name. Table name; # #删除表
   drop database name; # #删除库

3. Rights Management for MySQL
   Grant permission list on Library name. Table name to user @ ' client address ' identified by ' password '; # #授权
   Show grants for user @ ' client address '; # #查看权限
   Revoke permissions list on library name. Table name from user @ ' client address '; # #撤销授权
   

Flush privileges; # #刷新权限

4. Backup and restore MySQL

Copying tables to other databases can be used directly
Log in to MySQL

/etc/init.d/mysqld Stop # #停止服务
Cd/usr/local/mysql/data # #Mysql服务的安装目录
CP-RF mysql/library name. Table name Library name/# #复制一个库下的表到另一库下
Chown Mysql:mysql Library Name/-R # #更改宿主和属组
chmod 755 Library Name # #更改库权限, with permission to execute
CHMOD 660 Library name/* # #更改表权限
/etc/init.d/mysqld Start # #启动服务
Log in to MySQL
Use library name; # #进入库
Show tables; # #查看复制表是否生效

Cold backup of MySQL:
/etc/init.d/mysqld Stop # #停止服务
Tar JCF/backup location/backup name. Tar.xz/usr/local/mysql/data # #备份数据库的库文件目录
MySQL Recovery:
Tar jxf/backup location/backup name. tar.xz-c/root # #解压备份文件到指定目录
Cd/root/usr/local/mysql/data # #进入到数据库的库文件目录
CP-RFP Recover File//usr/local/mysql/data # #选择需要恢复的库-P does not change file attributes, if property is changed Change permission is required, there is a change permission procedure in the "Copy table" above
/etc/init.d/mysqld Start # #启动服务
MySQL Login verification
show databases; # #数据已经恢复

Hot backup; mysqldump
NETSTAT-UTPLN |grep 3306 # #确保mysql启动
Mysqldump-u User name-p password--all-databases >/backup directory/backup name. SQL #备份
Mysqldump-u password-P user name--all-databases--lock-talbes=0 >/backup directory/backup name. sql # #不锁表备份
Mysql-u User name-p password </backup directory/backup name. SQL # #恢复

5.mysql Forgotten Password Solution:
Vim/etc/my.cnf
[Mysqld]
Skip-grant-tables # #添加该行, skip password verification
: Wq
/etc/init.d/mysqld restart
MySQL # #登录后操作
Update Mysql.user Set Password=password ("123123") where user= "root"; # #修改root密码
Exit
Vim/etc/my.cnf
[Mysqld]
#skip-grant-tables # #注释该行
: Wq
/etc/init.d/mysqld restart

6. Manage users individually:
User Management
Mysql>use MySQL;
Mysql>create user identified by ' password '; # #identified by will store plaintext password encryption as a hash value
Mysql>rename user user to username, # #mysql 5 can be used, before updating the user table with update
Mysql> Set password for user name =password (' password '); # #加密密码
mysql> Update Mysql.user set Password=password (' Password ') where user= ' user '; # #单独修改某一用户密码
Mysql> Show grants for user name; # #查看用户权限
Mysql> Grant Select on Mysql.user to user name; # #赋予权限
Mysql> Revoke select on Mysql.user from user name; # #如果权限不存在会报错
Mysql>drop user username; # #mysql5之前删除用户时必须先使用revoke Remove the user right and then delete the user, mysql5 after the drop command can delete the user's associated permissions while deleting the user

7. Set mysql5.5 display Chinese name:
Vi/etc/my.cnf
[Client]
Default-character-set = UTF8 # #5.4 Version [client] and [mysqld] enter this line only
[Mysqld]
Character-set-server = UTF8
init_connect= ' SET NAMES UTF8 '
: Wq
/etc/init.d/mysqld restart

This article is from the "Ukkun" blog, make sure to keep this source http://xiaoayu.blog.51cto.com/12001978/1851546

MySQL Common statements