13.4 MySQL用户管理
MySQL 创建用户以及授权
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;
grant 不会记录到命令历史记录里，不安全
操作过程
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)
[root@linux-01 ~]# mysql -uuser1 -p123456a -h127.0.0.1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> grant all on *.* to 'user1'@'localhost' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)
[root@linux-01 ~]# mysql -uuser1 -p123456a
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.106.1' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for user2@'192.168.106.1';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.106.1 |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.106.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.106.1' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
同一用户，一样密码，在两个不同IP终端登陆
mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.106.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user2@'192.168.106.2';
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;
操作过程
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
mysql> select * from mysql.db; //耗费资源大，不建议这样操作
mysql> select db from mysql.db;
+---------+
| db |
+---------+
| db1 |
| test |
| test\_% |
| db1 |
+---------+
4 rows in set (0.00 sec)
mysql> select db,user from mysql.db;
+---------+-------+
| db | user |
+---------+-------+
| db1 | user3 |
| test | |
| test\_% | |
| db1 | user2 |
+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from mysql.db where host like '192.168.%';
插入数据：没有做限制时，插入数据可以是相同的ID name
mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.00 sec)
mysql> insert into db1.t1 values (1, '123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into db1.t1 values (1, '123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 1 | abc |
| 1 | 123 |
+------+------+
3 rows in set (0.00 sec)
更新表
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | aaa |
| 1 | aaa |
+------+------+
3 rows in set (0.00 sec)
mysql> update db1.t1 set id=2 where name='aaa';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 2 | aaa |
| 2 | aaa |
| 2 | aaa |
+------+------+
3 rows in set (0.00 sec)
删除
mysql> delete from db1.t1 where id=2;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)
删除清空表，truncate 清空信息，表结构保留；drop 整个清除
工作中尽量不这样操作
mysql> insert into db1.t1 values (1, '123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | 123 |
+------+------+
1 row in set (0.00 sec)
mysql> truncate db1.t1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
13.6 MySQL数据库备份恢复
备份库 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@linux-01 ~]# mysqldump -uroot -paminglinux mysql> /tmp/mysqlbak.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux-01 ~]# mysql -uroot -paminglinux -e "create database mysql2"
Warning: Using a password on the command line interface can be insecure.
[root@linux-01 ~]# mysql -uroot -paminglinux mysql2 < /tmp/mysqlbak.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux-01 ~]# mysqldump -uroot -paminglinux mysql user > /tmp/mysqluser.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux-01 ~]# mysql -uroot -paminglinux mysql2 < /tmp/mysqluser.sql
Warning: Using a password on the command line interface can be insecure.
[root@linux-01 ~]# mysqldump -uroot -paminglinux mysql -A > /tmp/mysql-all.sql
Warning: Using a password on the command line interface can be insecure.
