MySQL user management, common SQL statements, MySQL database backup

Source: Internet
Author: User

mysql用户管理

Creating a Web site requires a connection to MySQL, the default is the root user, but we cannot always connect to root, in case there is a problem with the operation error

mysql> grant all on *.* to ‘user‘ identified by ‘123‘;Query OK, 0 rows affected (0.00 sec)
grant all on *.* to ‘user1‘ @‘127.0.0.1‘ identified by ‘123456a‘;
grant:授权all:所有的权限*.*:表示所有,第一个*表示库名@后面也可写成%,表示通配,所有的ip使用的是socket通信
此时,我们退出mysql数据库后再连接mysql,出现报错,因为默认连接使用的是socket,当授权是localhost时,可以直接连接mysql

show grants;             //查看所有的授权,当前用户的授权
show grants for [email protected];           //查看指定用户的授权
                                            常用的sql语句
select count(*) from mysql.user;                  //count(*)表示查找表的行数
select count(*) from mysql.user;                  //count(*)表示查找表的行数
select * from mysql.db;
select db from mysql.db;              //db表示字段
select * from mysql.db where host like ‘192.168.%‘;            //模糊匹配
insert into db1.t1 values (1, ‘abc‘);                       //name字段abc是字符串,最好加上单引号
update db1.t1 set name=‘aaa‘ where id=1;
delete from db1.t1 where id=2;                              //删除操作
truncate table db1.t1;                  //只是清空数据
drop table db1.t1;                      //所有的数据、结构等全清空
drop database db1;                    //删掉数据库db1
                                       mysql数据库备份
备份库  
备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql  //库与表之间用空格分开
恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql             //恢复表只需要写上库名
备份所有库 mysqldump -uroot -p123456 -A >/tmp/123.sql          //-A表示所有的库
只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

MySQL user management, common SQL statements, MySQL database backup

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.