13.4 mysql用户管理 (创建用户以及授权)
create user 'test'@'%' identified by '123456'; 这种方法可以用来创建用户
查询mysql当前有多少个用户
use mysql;
select user,host from user;
grant all(所有的权限) on *.* to 'user1' identified by 'passwd';
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
grant all on db1.* to 'user3'@'%' identified by 'passwd';
show grants;
show grants for user2@192.168.133.1;
创建用户
创建授权权限最高的用户
mysql> grant all on *.* to 'user1' identified by 'passwd';
grant授权
all所有的权限(查看、创建、删除等)
创建的新用户user1
*.*表示所有 前面的*表示库名 后面的*表示表 可以写成:mysql.*表示mysql库中所有的表
创建用户指定登陆客户端IP
mysql> grant all on *.* to 'user1'@'192.168.1.3' identified by 'passwd';
%表示通配 这个用户可以所有的IP登陆
mysql> grant all on *.* to 'user1'@'%' identified by 'passwd';
创建用户指定本地socket连接
mysql> grant all on *.* to 'user1'@'localhost' identified by 'passwd';
针对具体权限(语句、库、表)创建用户
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.1.3' identified by 'passwd';
针对所有的IP授权(
可执行db1库所有的表用户user3所有的IP登陆)
mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';
查看root用户的授权
mysql> show grants\G;
*************************** 1. row ***************************
Grants for root
@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root
@localhost: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
3 rows in set (0.00 sec)
ERROR:
No query specified
查看指定用户的授权
mysql> show grants for user1@'127.0.0.1';
复制用户,多IP登陆
mysql> show grants for
txc2@'183.131.3.206';
假设此时183.131.3.207也需要同样的授权 直接复制前面的命令更换IP 就可以
针对一个段去授权
grant all on *.* to 'user1'@'192.168.130.%' identified by '密码';
13.5 常用sql语句
select count(*) from mysql.user;
select * from mysql.db;
select db from mysql.db;
select db,user from mysql.db;
select * from mysql.db where host like '192.168.%';
insert into db1.t1 values (1, 'abc');
update db1.t1 set name='aaa' where id=1;
truncate table db1.t1;
drop table db1.t1;
drop database db1;
select查看的 insert插入 update更改
mysql> use mysql;
Database changed
count(*)查找表的行数 mysql.user mysql数据库的user表
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
查看内容
mysql> select * from mysql.db\G; (*很耗资源)
mysql> select * from mysql.db\G;
*************************** 1. row ***************************
Host: localhost
Db: performance_schema
User: mysql.session
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 2. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: Y
2 rows in set (0.00 sec)
ERROR:
No query specified
注:主流的搜索引擎 myisam(自动统计行,搜索速度很快) innodb(不会自动统计行,搜索很耗时间)
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Create_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Drop_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
`Password_reuse_history` smallint(5) unsigned DEFAULT NULL,
`Password_reuse_time` smallint(5) unsigned DEFAULT NULL,
`Password_require_current` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`Host`,`User`)
) /*!50100 TABLESPACE `mysql` */
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Users and global privileges'
1 row in set (0.00 sec)
ERROR:
No query specified
指定一个字段搜索
mysql> select db from mysql.db;
+--------------------+
| db |
+--------------------+
| performance_schema |
| sys |
+--------------------+
2 rows in set (0.00 sec)
指定两个字段
mysql> select db,user from mysql.db;
+--------------------+---------------+
| db | user |
+--------------------+---------------+
| performance_schema | mysql.session |
| sys | mysql.sys |
+--------------------+---------------+
2 rows in set (0.00 sec)
模糊查询 like模糊匹配
mysql> select * from mysql.db where host like 'local%'\G;
*************************** 1. row ***************************
Host: localhost
Db: performance_schema
User: mysql.session
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 2. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: Y
2 rows in set (0.01 sec)
ERROR:
No query specified
mysql> select * from mysql.db where host like '183.131.3.%'\G
插入语句
mysql> select * from db1.t1;
Empty set (0.02 sec)
插入数据
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.11 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
字符串加'' 不加则表示是数字
mysql> insert into db1.t1 values (1, 234);
Query OK, 1 row affected (0.06 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 1 | 234 |
+------+------+
2 rows in set (0.00 sec)
更改
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | aaa |
+------+------+
2 rows in set (0.00 sec)
清除
mysql> delete from db1.t1 where id=1;
Query OK, 2 rows affected (0.11 sec)
清空表(清空表内容,不对表结构做任何操作)
mysql> truncate db1.t1;
Query OK, 0 rows affected (0.40 sec)
mysql> drop table t1;(将整个表删除)
13.6 mysql数据库备份恢复
库的备份可以跨机器, 将一个库备份到另一台机器上 首先这两个机器能够通信 mysqldump -h 远程mysql-ip -uuser-ppassword dbname > /本地backup.sql
mysqldump -uroot -p --database test mysql wordpress>
--database 参数 加与不加有啥区别 不加 --database 就不支持同时备份多个库。
备份库 mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql
备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
备份所有库 mysqldump -uroot -p -A >/tmp/123.sql
只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
备份指定库至指定位置
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 mysql > /tmp/mysqlbak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份的库恢复至指定的库
[root@txc208 mysql]# /usr/local/mysql/bin/mysql -uroot -p123 -e "create database mysql2"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@txc208 mysql]# /usr/local/mysql/bin/mysql -uroot -p123 mysql2 < /tmp/mysqlbak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@txc208 mysql]#/usr/local/mysql/bin/mysql -uroot -p123 mysql2(直接进入mysql2)
mysql> select database();
+------------+
| database() |
+------------+
| mysql2 |
+------------+
1 row in set (0.00 sec)
指定表备份
mysql库的user表
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 mysql user > /tmp/user.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
恢复表
指定要恢复的库名
[root@txc208 mysql]# /usr/local/mysql/bin/mysql -uroot -p123 mysql2 < /tmp/user.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份所有库 -A(ALL)
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 -A >/tmp/MYSQL_ALL.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份表结构
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 -d mysql > /tmp/mysql_jg.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.