第四十三篇 Mysql的权限管理，和数据备份

心得：今天的太阳不错，待会出去晒晒

权限管理：

1.创建帐号

1.1本地帐号
create user ‘tian’@‘localhost’ identified by ‘234’;
1.2远程帐号
create user ‘tian’@‘192.168.1.1’ identified by ‘234’;
create user ‘tian’@’%’ identified by ‘234’;

2.授权
user: *.*
db:db1.*
tables_priv:db1.t1
columns_priv:id,name

user:
grant all on *.* to ‘tian’@‘localhost’;
grant select on *.* to ‘tian’@‘localhost’;
revoke select on *.* from ‘tian’@‘localhost’;

db
grant select on db1.* to ‘tian’@‘localhost’;
revoke select on db1.* from ‘tian’@‘localhost’;

table
grant select on db1.t1 to ‘tian’@‘localhost’;
revoke select on db1.t1 from ‘tian’@‘localhost’;

column
grant select(id,name) on db1.t1 to ‘tian’@‘localhost’;
revoke select(id,name) on db1.t1 from ‘tian’@‘localhost’;

数据备份

单表备份

mysqldump -uroot -p321 db1 t1 >t1.sql;

单库备份

mysqldump -uroot -p321 db1 > /home/db1.sql

多库备份

mysqldump -uroot -p321 --databases db1 db2 > /home/db2.sql

全部备份

mysqldump -uroot -p321 --all-databases> /home/db3.sql

备份数据格式

mysqldump -uroot -p321 -d db1 t1 >/home/t2.sql

root@tianjain-TM1701:~# ll /home/
总用量 1716
drwxr-xr-x  3 root     root       4096 12月 15 19:59 ./
drwxr-xr-x 24 root     root       4096 12月  9 07:34 ../
-rw-r--r--  1 root     root       5388 12月 15 19:56 db1.sql
-rw-r--r--  1 root     root     865233 12月 15 19:59 db2.sql
-rw-r--r--  1 root     root     865233 12月 15 19:59 db3.sql

导入备份的库：

导入单个表和表结构

mysql -uroot -p321 db4 < /home/t1.sql

导入单个库

mysql -uroot -p321 db4 < /home/db1.sql

导入全部库

mysql -uroot -p321 < /home/all.sql`在这里插入代码片`

导入库例子：

#1 导出数据库（提示不要在公屏输入密码这是不安全的）
mysqldump -uroot -ptianjian321 db1 > /home/db1_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#2 插入数据库
 mysql -uroot -ptianjian321 db4 < /home/db1_data.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

#3查看数据库下数据
mysql> use db4;
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> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| dep           |
| employee      |
| person        |
| t1            |
| t2            |
+---------------+
5 rows in set (0.00 sec)

导入表结构,只存在结构，不存在数据

root@tianjain-TM1701:~# mysqldump -uroot -p321 -d db1 t2 >/home/t7.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#导入时没有-d参数
root@tianjain-TM1701:~# mysql -uroot -p321 -d db4 </home/t7.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [ERROR] mysql: unknown option '-d'
root@tianjain-TM1701:~# mysql -uroot -p321 db4 </home/t7.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
root@tianjain-TM1701:~# 
root@tianjain-TM1701:~# mysql -uroot -p321

mysql> use db4;
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> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| dep           |
| employee      |
| person        |
| t2            |
+---------------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> select * from t2;
Empty set (0.00 sec)

mysql> 
mysql> 
mysql> desc t2;
+-------+------------------------------------------------+------+-----+---------+----------------+
| Field | Type                                           | Null | Key | Default | Extra          |
+-------+------------------------------------------------+------+-----+---------+----------------+
| id    | int(11)                                        | NO   | PRI | NULL    | auto_increment |
| name  | char(10)                                       | NO   |     | NULL    |                |
| age   | int(11)                                        | YES  |     | 18      |                |
| sex   | enum('male','female')                          | YES  |     | NULL    |                |
| hobby | set('readding','running','swimming','eatting') | YES  |     | NULL    |                |
| ip    | char(15)                                       | YES  | MUL | NULL    |                |
| port  | char(4)                                        | YES  |     | NULL    |                |
+-------+------------------------------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql>