MySQL第四天（用户授权与撤销，完全备份和完全恢复）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL第四天（用户授权与撤销，完全备份和完全恢复），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含31946字，纯文字阅读大概需要46分钟。

内容图文

day04
一、用户授权与撤销
数据库管理员密码设置？
1.1修改数据库管理员本机登录密码
[root@db2 ~]# mysqladmin -hlocalhost -uroot -p password "123456"
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@db2 ~]# mysql -uroot -p123456

1.2恢复数据库管理员本机登录密码
[root@db2 ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables
#validate_password_policy=0
#validate_password_length=6
:wq
[root@db2 ~]# systemctl stop mysqld
[root@db2 ~]# systemctl start mysqld
[root@db2 ~]# mysql
mysql> desc mysql.user;

mysql> select user,host,authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| user | host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.sys | localhost | THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> update mysql.user set authentication_string=password("654321") where user="root" and host="localhost"; ##修改authentication_string字段值，用函数password修改
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges; ##刷新特权
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@db2 ~]# vim /etc/my.cnf ##修改配置文件，注释掉skip-grant-tables
[mysqld]
#skip-grant-tables
validate_password_policy=0
validate_password_length=6
:wq
[root@db2 ~]# systemctl stop mysqld
[root@db2 ~]# systemctl start mysqld
[root@db2 ~]# mysql -uroot -p654321

用户授权
mysql> grant 权限列表 on 库名 to 用户名@"客户端地址" identified by "密码" with grant option; ##with grant option 给授予权限的权力，客户端拥有授予其他用户权限时，客户端必须要有服务器mysql.*所有表insert权限

mysql> grant all on . to student@"192.168.4.54" identified by "123789" with grant option; ##在所有数据库上授予所有权限，给student用户，192.168.4.54以student身份用密码123789可以登录
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant insert,select on db.t1 to student@"192.168.4.53" identified by "123789" with grant option; ##授予部分权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

1.3登录用户查看自己的访问权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

1.4显示当前登录用户信息
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
查看当前登录的数据库服务器主机名
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| db2 |
+------------+
1 row in set (0.00 sec)

1.5查看服务器上已有的授权用户
desc mysql.user
mysql> select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+-----------+--------------+
| user | host |
+-----------+--------------+
| student | 192.168.4.53 |
| student | 192.168.4.54 |
| mysql.sys | localhost |
| root | localhost |
+-----------+--------------+
4 rows in set (0.00 sec)

1.6管理员用户查看服务器已有授权用户的访问权限
mysql> show grants for root@"localhost";
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for student@"192.168.4.53";
+-----------------------------------------------------------------------------------------+
| Grants for student@192.168.4.53 |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘student‘@‘192.168.4.53‘ |
| GRANT SELECT, INSERT, CREATE ON db.t1 TO ‘student‘@‘192.168.4.53‘ WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for student@"192.168.4.54";
+---------------------------------------------------------------------------+
| Grants for student@192.168.4.54 |
+---------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘student‘@‘192.168.4.54‘ WITH GRANT OPTION |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.7权限撤销
mysql>revoke 权限列表 on 数据库名 from 用户名@"客户端地址"；

1.8删除授权用户
drop user 用户名@"客户端地址";

1.9数据库管理员修改授权用户的登录密码
set password 用户名@"客户端地址"=password("新密码")；

1.10授权用户登录后，重置自己的登录密码
SET PASSWORD=PASSWORD("密码")；

1.11授权信息记录在mysql库下不同的表里
use mysql;
show tables;
user表 记录服务器上有哪些授权用户
columns_priv表 记录授权用户对库中字段的访问权限
mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘References‘) | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)

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

tables_priv表 记录授权用户对库中表的访问权限 ##host为客户端地址，db为库，user为用户
mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(93) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘Delete‘,‘Create‘,‘Drop‘,‘Grant‘,‘References‘,‘Index‘,‘Alter‘,‘Create View‘,‘Show view‘,‘Trigger‘) | NO | | | |
| Column_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘References‘) | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> select * from mysql.tables_priv;
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2018-02-22 01:09:54 | Select | |
| 192.168.4.53 | db | student | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Create,Grant | |
| 176.121.205.59 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Grant | |
| 192.168.4.254 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert | |
| 192.168.4.54 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Grant | |
| 192.168.4.54 | db | admin | db | root@localhost | 0000-00-00 00:00:00 | Create,Grant | |
| 192.168.4.54 | mysql | admin | user | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | |
| 192.168.4.51 | db | admin | t1 | admin@192.168.4.54 | 0000-00-00 00:00:00 | Select | |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
8 rows in set (0.00 sec)

db表 记录授权用户服务器上库的访问权限

1.7撤销权限
mysql>revoke 权限列表 on 数据库名 from 用户名@"客户端地址";
mysql> select * from mysql.tables_priv;
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2018-02-22 01:09:54 | Select | |
| 192.168.4.53 | db | student | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Create,Grant | |
| 176.121.205.59 | db | admin | t1 | root@localhost | 2018-02-26 04:05:19 | Select | |
| 192.168.4.254 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert | |
| 192.168.4.54 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Grant | |
| 192.168.4.54 | db | admin | db | root@localhost | 0000-00-00 00:00:00 | Create,Grant | |
| 192.168.4.54 | mysql | admin | user | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | |
| 192.168.4.51 | db | admin | t1 | admin@192.168.4.54 | 0000-00-00 00:00:00 | Select | |
| 192.168.4.54 | db | teacher | t1 | root@localhost | 0000-00-00 00:00:00 | Update | |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
9 rows in set (0.00 sec)

mysql> revoke insert on db.t1 from student@"192.168.4.53";
Query OK, 0 rows affected (0.00 sec)
mysql> revoke grant option on db.t1 from student@"192.168.4.53";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.tables_priv;
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2018-02-22 01:09:54 | Select | |
| 192.168.4.53 | db | student | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Create | |
| 176.121.205.59 | db | admin | t1 | root@localhost | 2018-02-26 04:05:19 | Select | |
| 192.168.4.254 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert | |
| 192.168.4.54 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Grant | |
| 192.168.4.54 | db | admin | db | root@localhost | 0000-00-00 00:00:00 | Create,Grant | |
| 192.168.4.54 | mysql | admin | user | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | |
| 192.168.4.51 | db | admin | t1 | admin@192.168.4.54 | 0000-00-00 00:00:00 | Select | |
| 192.168.4.54 | db | teacher | t1 | root@localhost | 0000-00-00 00:00:00 | Update | |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
9 rows in set (0.00 sec)

通过修改记录字段值的方式删除权限。
mysql> select * from mysql.tables_priv;
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2018-02-22 01:09:54 | Select | |
| 192.168.4.53 | db | student | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Create,Grant | |
| 176.121.205.59 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Grant | |
| 192.168.4.254 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert | |
| 192.168.4.54 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Grant | |
| 192.168.4.54 | db | admin | db | root@localhost | 0000-00-00 00:00:00 | Create,Grant | |
| 192.168.4.54 | mysql | admin | user | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | |
| 192.168.4.51 | db | admin | t1 | admin@192.168.4.54 | 0000-00-00 00:00:00 | Select | |
| 192.168.4.54 | db | teacher | t1 | root@localhost | 0000-00-00 00:00:00 | Update | |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
9 rows in set (0.00 sec)

mysql> update mysql.tables_priv set Table_priv="select" where host="176.121.205.59" and Db="db";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.tables_priv;
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2018-02-22 01:09:54 | Select | |
| 192.168.4.53 | db | student | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Create,Grant | |
| 176.121.205.59 | db | admin | t1 | root@localhost | 2018-02-26 04:05:19 | Select | |
| 192.168.4.254 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert | |
| 192.168.4.54 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Grant | |
| 192.168.4.54 | db | admin | db | root@localhost | 0000-00-00 00:00:00 | Create,Grant | |
| 192.168.4.54 | mysql | admin | user | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | |
| 192.168.4.51 | db | admin | t1 | admin@192.168.4.54 | 0000-00-00 00:00:00 | Select | |
| 192.168.4.54 | db | teacher | t1 | root@localhost | 0000-00-00 00:00:00 | Update | |
+----------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
9 rows in set (0.00 sec)

mysql> drop user admin@"176.121.205.59"; ##删除用户方式撤销所有权力
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.tables_priv;
+---------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+---------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2018-02-22 01:09:54 | Select | |
| 192.168.4.53 | db | student | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Create | |
| 192.168.4.254 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert | |
| 192.168.4.54 | db | admin | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Grant | |
| 192.168.4.54 | db | admin | db | root@localhost | 0000-00-00 00:00:00 | Create,Grant | |
| 192.168.4.54 | mysql | admin | user | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger | |
| 192.168.4.51 | db | admin | t1 | admin@192.168.4.54 | 0000-00-00 00:00:00 | Select | |
| 192.168.4.54 | db | teacher | t1 | root@localhost | 0000-00-00 00:00:00 | Update | |
+---------------+-------+-----------+------------+--------------------+---------------------+----------------------------------------------------------------------------------------------------+-------------+
8 rows in set (0.00 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
在客户端使用授权连接数据库服务器
mysql -h数据库服务器ip地址 -u授权用户名 -p密码
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
安装数据库图形管理工具-phpmyadmin

[root@db2 09.mysql]# yum -y install httpd php php-mysql
[root@db2 09.mysql]# systemctl restart httpd
[root@db2 09.mysql]# systemctl enable httpd
Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.
[root@db2 09.mysql]# vim /var/www/html/test.php
<?php
$db=mysql_connect("localhost","root","654321");
if($db){echo "ok";}else{echo "no";};
?>
:wq
[root@db2 html]# php test.php
http://192.168.4.52/test.php

phpmyadmin
[root@db2 09.mysql]# tar -xf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/
[root@db2 09.mysql]# cd /var/www/html/
[root@db2 html]# ls
phpMyAdmin-2.11.11-all-languages test.php
[root@db2 html]# mv phpMyAdmin-2.11.11-all-languages/ phpmyadmin
[root@db2 html]# ls
phpmyadmin test.php
[root@db2 html]# ls -ld phpmyadmin/
drwxr-xr-x. 9 root root 4096 9月 7 2010 phpmyadmin/
[root@db2 html]# chown -R apache:apache phpmyadmin/
[root@db2 html]# ls -ld phpmyadmin/
drwxr-xr-x. 9 apache apache 4096 9月 7 2010 phpmyadmin/
[root@db2 html]# cd phpmyadmin/
[root@db2 phpmyadmin]# cp config.sample.inc.php config.inc.php
[root@db2 phpmyadmin]# sed -n ‘17p;31p‘ config.inc.php
vim config.inc.php
$cfg[‘blowfish_secret‘] = ‘plj123‘; / YOU MUST FILL IN THIS FOR COOKIE AUTH! / ##单引号中间随意填
$cfg[‘Servers‘][$i][‘host‘] = ‘localhost‘; ##填写数据库ip地址

mysql> create database t44;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db |
| db1 |
| mysql |
| performance_schema |
| sys |
| t44 |
+--------------------+
7 rows in set (0.00 sec)

mysql> grant all on t44.* to yaya88@"localhost" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
数据备份与恢复-完全备份和恢复
1 备份数据的目的？

2 备份方式？
物理备份：拷贝库或表对应的文件
#cp -r /var/lib/mysql/数据库名 /opt/mysql.bak
或者
#tar -zcvf /opt/数据库名.tar.gz /var/lib/mysql/*
恢复：
#cp -r /opt/mysql.bak /var/lib/mysql/数据库名
#chown -R mysql:mysql /var/lib/mysql/数据库名
#systemctl restart mysqld

逻辑备份：执行备份根据备份的库或表，生成对于的sql命令，然后把sql命令存储到指定的文件里。

3 备份策略
完全备份：备份所有数据 #一台数据库服务器//某个库的所有数据//某一张表的所有数据
mysqldump -uroot -p123456 数据库名> 目录名/xxx.sql

只备份新产生的数据？
binlog日志 innobackup
差异备份：备份自完全备份后所有新产生的
增量备份：备份自上次备份后所有新产生的

备份策略的使用方式？
完全+差异
完全+增量

crond 执行 备份脚本

30 23 1 /rootallbaksh #每周一晚上完全备份
50 23 2-7 /root/newdata.sh #周二到周日差异备

1.备份服务器上所有数据库到/datadir/ 文件名为all.sql
#mysqldump -uroot -p123456 --all-databases > /datadir/all.sql

2.备份服务器上db3 库的所有数据到/datadir 文件名db3.sql
#mysqldump -uroot -p123456 db3 > /datadir/db3.sql

3.备份db3库下的user表的所有数据到/datadir 文件名db3-user.sql
#mysqldump -uroot -p123456 db3 user > /datadir/db3-user.sql

4.删除db3库下的user表记录 使用备份文件恢复数据
mysql>delete from db3.user
#mysql -uroot -p123456 db3 < /datadir/db3-user.sql

5.把db3库下的所有数据恢复到db55库里
mysql> create database db55;

mysql -uroot -p654321 db55 < /datadir/db3.sql

6.删除db3库使用备份文件恢复db3库的数据
Mysql> drop database db3;
Mysql>create database db3;
#mysql -uroot -p654321 db3 < /datadir/db3.sql

7.备份数据库服务的所有的授权信息，
#mysqldump -uroot -p65432

8.编写脚步alladta.sh功能备份db55库的所有数据到系统的/mydata目录下，
备份名字：年-月-日-库名.sql

vim /alldata.sh

#!/bin/bash
Day=date +%F
If [ ! -e /mydata ];then
Mkdir /mydata
fi
mysqldump -hlocalhost -uroot -p654321 db55 > /mydata/$day-db55.sql
编写计划任务：
]#crontab -e -uroot
30 23 1 /root/alldata.sh
################

完全备份：
#mysqldump -hlocalhost -uroot -p123456 数据库名 > 目录名/xxx.sql
数据库名的表示方式

一台服务器上的所有数据
--all-databases

某一个库的所有数据
库名 #db1

某一张表的所有数据
库名 表名 #db1 t1

把多个库的所有数据备份到一个备份文件里
-B 库名列表

#mkdir /mydata
#mysqldump -uroot -p654321 db2 > /mydata/db2.sql ##备份
Mysql>use db2;
Mysql>drop table t7,t8;
恢复
#mysql -uroot -p654321 库名 < /备份文件 ##还原
#mysql -uroot -p654321 db2 < /mydata/db2.sql
#############################
备份数据时需要考虑的事情？
备份策略
备份时间
数据备份频率
备份文件的命名 yyyy-mm-dd-dbname.sql
存储空间 可扩展LV RAID

完全备份策略有哪些缺点？
备份和恢复都会锁表（写锁）
使用完全备份文件，只能把数据恢复到备份时的状态（比如周一备份，周2到周6产生的数据后，还原后只有周一数据）

MySQL第四天（用户授权与撤销，完全备份和完全恢复）

标签：运维

本文系统来源：http://blog.51cto.com/13587169/2074437

内容总结

以上是互联网集市为您收集整理的MySQL第四天（用户授权与撤销，完全备份和完全恢复）全部内容，希望文章能够帮你解决MySQL第四天（用户授权与撤销，完全备份和完全恢复）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---