mysql数据库入门、进阶和提升（续三）

57 阅读 0 评论 38 点赞

我是靠谱客的博主开放母鸡，最近开发中收集的这篇文章主要介绍mysql数据库入门、进阶和提升（续三），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

备份

实际上就是对数据另存为一个副本。

备份需要事先考虑的问题：

1、容忍丢失数据多少时间。

2、恢复时间

3、是否需要提供持续服务

4、需要恢复什么？整个系统？一个数据库？一个表？

备份类型：

根据是否允许数据库离线，可以分为那些类型？

冷备份（需要离线、关闭服务、读写请求均不允许）cold backup

温备（mysql在线，可读不可写）

热备（备份的同时，业务不受有影响）

根据要备份的数据范围，可以分为那些类型？

完全备份：full backup，备份全部数据。

增量备份：incremental backup ，从上一次完全备份或增量备份以来改变的部分。

差异备份：备份上一次完全备份以来改变的数据。周三的备份包括周一周二的备份。

根据备份数据或是文件，可以分为：

物理备份：直接备份数据备份

优点：备份恢复操作简单，能够跨多版本，恢复速度快。

缺点：只能够冷备份，

逻辑备份：通过工具将数据转换成另一种格式的情况。

优点：恢复简单、备份结果为ascii文件，可以编辑，与存储引擎无关，可以跨平台，可以跨网络。

缺点：备份和恢复需要服务器参与，消耗内存，导出的数据比源文件大，还原后，索引需要重建，所以恢复时间长。

备份对象：

数据

配置文件

代码：存储过程，存储函数，触发器

os相关的配置文件及设定

备份工具：

1、mysqldump（逻辑备份工具）

对于innodb引擎支持热备，myISAM支持温备

备份和恢复较慢

单线程备份，速度慢

备份整个服务器的单个或部分数据库，单个或部分表，表中某些行，存储过程、存储函数、触发器能自动记录备份时的二进制日志文件及相应的position。

参数：

-B，--database dbname 空格隔开可以有多个数据库，如果不指定具体库名，则备份全部数据库。

--lock-all-table 执行备份时锁定所有表，用于保证数据备份时的完整性（热备），用户不能写。

--lock-table 指定锁定的表，用于备份指定的表情况。

--single-transaction 基于快照样式的热备份，由此不需要同时使用--lock-all-tables

-u 用户名

-p 密码

-h 主机名

--opt 同时启用各种高级选项

-C,--compress 如果通过网络传输备份的话，可以先压缩后备份传输。

--E，--event 备份的同时也备份事件查看器代码

--R,--routines 备份数据库的同时备份存储过程和存储函数

-A,--all-database 备份所有数据库

--master-data=2 记录在通过mysqldump备份数据库的时候，二进制日志文件正在使用的文件及所处的位置，并以注释的形式显示，这样的好处就是方便我们在进行数据恢复的时候，知道备份后需要恢复二进制日志文件的开始位置。

注意：对于同样具有锁功能的命令还有flush tables with read lock，但是对于innodb引擎，需要使用show engine innodb status；来观察内存中的数据同步到磁盘上的情况。

查看内存当前写入情况，依次作为判断数据库能否备份的依据。

命令：show engine innodb statusG；

关注点：

FILE I/O

--------

I/O thread 0 state: waiting for i/o request (insert buffer thread)

I/O thread 1 state: waiting for i/o request (log thread)

I/O thread 2 state: waiting for i/o request (read thread)

I/O thread 3 state: waiting for i/o request (read thread)

I/O thread 4 state: waiting for i/o request (read thread)

I/O thread 5 state: waiting for i/o request (read thread)

I/O thread 6 state: waiting for i/o request (write thread)

I/O thread 7 state: waiting for i/o request (write thread)

I/O thread 8 state: waiting for i/o request (write thread)

I/O thread 9 state: waiting for i/o request (write thread)

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,

ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

384 OS file reads, 3 OS file writes, 3 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 137363456; in additional pool allocated 0

Dictionary memory allocated 424828

Buffer pool size   8192

Free buffers       7817

Database pages     372

Old database pages 0

Modified db pages  0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 372, created 0, written 0

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 372, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

mysqldump备份方法：

[root@dt0b4007c ~]# mysqldump -u root -p --database ec_school mysql --lock-all-tables >test.sql;

Enter password:

一次完整的备份恢复过程：（完全备份+增量备份）

a、针对innodb，热备份ec_school数据库，并保存当前二进制文件信息（完全备份）

[root@dt0b4007c ~]# mysqldump -u root -p --master-data=2 --single-transaction --database ec_school >ec_school_`date +%F`.sql;

b、增量备份，通过查看当前日志文件的记录位置和上次完全备份的结束二进制二进制日志位置来确定当前增量备份内容。

less ec_school_2016-08-05

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000053', MASTER_LOG_POS=320;

show master status；

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000053 | 524| | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

增量备份： [root@dt0b4007c ~]# mysqlbinlog --start-position=320 --stop-position=524 /data/mysql/mysql-bin.000053 >binlog_`date +%T`.sql

c、恢复

将最近一次二进制日志转换并保存下来（备份后到数据丢失损坏之间的二进制日志）比如：

mysqlbinlog --start-position=524 --stop-position=2211 /data/mysql/mysql-bin.000065>binlog_`date +%T`.sql

进入数据库并关闭二进制日志

set sql_log_bin=0；

滚动一下日志

flush logs；

导入数据库，先导入完整备份，再按照时间的先后顺序导入增量备份，最后导入二进制备份。

2、mydumper（逻辑备份工具）

多线程备份工具

安装过程中可能需要glibc、zlib、pure、pcre-devel、gcc、gcc-c++、make、mysql客户端等软件包，具体可以在安装过程中依据实际情况安装。

备份:

[root@localhost ~]# mydumper -u root -p dbroot@dtedu -h 10.40.0.5 -P 3306 -o /root/123/

查看备份情况：

[root@localhost ~]# ll /root/123

总用量 29624

-rw-r--r-- 1 root root 652 8月 15 17:44 ec_school.acl_features-schema.sql

-rw-r--r-- 1 root root 795 8月 15 17:44 ec_school.acl_resources-schema.sql

-rw-r--r-- 1 root root 702 8月 15 17:44 ec_school.acl_roles-schema.sql

-rw-r--r-- 1 root root 917 8月 15 17:44 ec_school.action_group-schema.sql

-rw-r--r-- 1 root root 3199 8月 15 17:44 ec_school.action_group.sql

-rw-r--r-- 1 root root 689 8月 15 17:44 ec_school.action-schema.sql

metadata:元数据 记录备份开始和结束时间，以及binlog日志文件位置。table data:每个表一个文件
table schemas:表结构文件
binary logs: 启用--binlogs选项后，二进制文件存放在binlog_snapshot目录下
daemon mode:在这个模式下，有五个目录0，1，binlogs，binlog_snapshot，last_dump。
备份目录是0和1，间隔备份，如果mydumper因某种原因失败而仍然有一个好的快照，当快照完成后，last_dump指向该备份。

还原：

[root@localhost ~]# myloader -u root -p dbroot@dtedu -P 3306 -h 10.40.0.5 -d /root/123/ -o

-o, --overwrite-tables 如果表存在则先删除，使用该参数，需要备份时候要备份表结构，不然还原会找不到表

参考文件：http://blog.csdn.net/lidan3959/article/details/20867711

3、lvm-snapshot

（物理备份，前提条件是事务日志和数据文件在一个lv上）

lvm的快照只针对lvm有效，他的工作原理是写时复制，就是说在用户对一个逻辑卷创建快照后，如果原始卷有数据写入，更新操作，在此操作之前会先将修改的数据备份到快照卷里，而对于读操作，如果是没有修改过的就从原始卷中读取，如果是修改过的，会到快照卷中读取。

那么可以有一下总结，1、快照是活动的、连续的运行额，2、快照的预留空间做大不会超过原始卷的空间，3、快照预留空间大小随着原始卷更新数据的频率增加而增大。4、快照的创建也是要在逻辑卷上，而不能创建在其他磁盘分区上，所以如果物理磁盘损坏同样是不能恢复的。所以建议将快照的内容复制到其他磁盘上。

接近热备的工具，但是要有读锁的

物理备份

备份和恢复时间短

工作步骤：

A、首要步骤也是需要停止数据库表的读写功能，flush tables with read lock；（一定不要退出，退出后锁就被释放掉了不起作用），记住show master status中的二进制日志文件位置。

B、新建一个终端连接到数据库上，并对逻辑卷进行快照创建（这里需要说明下的是数据库数据是保存在逻辑卷分区上的）

lvcreate -L 100M -n mydata.snap -p r -s /dev/vg_name/lv_name

C、将备份的快照复制到指定位置。

注意：

在通过lvm进行快照的时候，事务日志必须保证和数据在一个快照上，也就是要保证他们在一个逻辑卷上。

注意创建快照的大小足够满足已经存在的数据的空间需求，并且这个快照大小也是逻辑卷剩余空间所满足的。

恢复的时候注意目录权限

基于快照的备份过程详解：

备份策略：完全备份+增量备份

增量备份也是来源于二进制日志的备份，而最后的恢复点还要使用二进制日志文件。

4、mysql自带的命令导出

select into outfile 'filename （备份的路径要求用户mysql必须有访问权限）

load data infile ‘filename'

默认备份路径是在datedir参数中指定，可以通过show variable like ‘%dir%’来查看。

逻辑备份

快于mysqldump

实例：

1、备份年龄大于三十岁的学生信息。

mysql> select * from students where age >30;

+-------+-------------+-----+--------+---------+---------+

| stuid | name        | age | gender | classid | teacher |

+-------+-------------+-----+--------+---------+---------+

|     3 | zhouxin     |  42 | m      |       3 |       9 |

|     6 | zhouxi      |  42 | m      |       3 |       2 |

|     9 | gongbing    |  52 | m      |       3 |      11 |

|    10 | qianlong    |  76 | f      |       0 |       1 |

|    11 | meichaofeng |  90 | m      |       0 |       9 |

+-------+-------------+-----+--------+---------+---------+

5 rows in set (0.00 sec)

备份：

mysql> select * from ec_school.action_group into outfile '/tmp/ec_school.sql';

Query OK, 28 rows affected (0.00 sec)

恢复：

mysql> SET FOREIGN_KEY_CHECKS=0;   如果存在外键，要关闭外键约束

Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/tmp/ec_school.sql' into table ec_school.action_group; /root目录下可能会出现cannot get stat的报错

Query OK, 28 rows affected (0.04 sec)

Records: 28 Deleted: 0 Skipped: 0 Warnings: 0

mysql> SET FOREIGN_KEY_CHECKS=1;

Query OK, 0 rows affected (0.00 sec)

5、xtrabackup

面介绍mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。而使用lvm快照功能对数据库进行备份，可以实现几乎热备的功能，但备份过程较为复杂，不过现在倒是有个工具mylvmbackup可以实现自动化备份。

前面我们也说道，使用物理备份时最快的，那有没有办法实现物理热备呢？

目前主流的有两个工具可以实现热备：ibbackup和xtrabackup；ibbackup是商业软件，没服务器授权为5000美元，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我们这里就来介绍xtrabackup的使用。

特点：

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快；

(6)可以导出单张表

(7)实现索引优化和分析

13879726971

安装：

安装很简单，注意依赖包的安装。

安装路径查询：

[root@localhost ~]# rpm -qlp percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

warning: percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

/usr/bin/innobackupex#主备份工具，依据不同的备份内容调用xtrabackup、xbstream

/usr/bin/xbcloud

/usr/bin/xbcloud_osenv

/usr/bin/xbcrypt

/usr/bin/xbstream##支持流媒体备份

/usr/bin/xtrabackup##提供热备份innodb的数据表，不能备份其他类型的表，也不能备份表结构

/usr/share/doc/percona-xtrabackup-24-2.4.4

/usr/share/doc/percona-xtrabackup-24-2.4.4/COPYING

/usr/share/man/man1/innobackupex.1.gz

/usr/share/man/man1/xbcrypt.1.gz

/usr/share/man/man1/xbstream.1.gz

/usr/share/man/man1/xtrabackup.1.gz

使用innobackupex进行备份时，实际调用的是xtrabackup命令并备份所有的innoDB表，复制所有关于表结构定义的相关文件（*.frm）、myISAM、MERGE、CSV和archive表的相关文件，同时会备份触发器和数据库配置信息。

备份目录及文件介绍：

xtrabackup会自动在备份目录内创建一个以当前日期命名的目录，并存在多个信息文件。

1、xtrabackup_checkpoints：用来记录备份类型，备份状态（是否已经为prepared，就是在恢复之前将已提交的事务进行前滚（提交），而未提交的事务进行回滚（撤销）的操作）状态和LSN范围信息（日志序列），每个innoDB也都包含一个日志序列号，即LSN。LSN是整个数据库系统的版本号，每个页面的LSN能够反映出最近发生页面了什么变化

2、xtrabackup_binlog_info：mysql服务器正在使用的二进制日志文件及备份时二进制事件的位置。

3、xtrabackup_binlog_pos_innodb：二进制日志文件及用于innoDB或xtraDB表的二进制文件的position。

4、xtrabackup_binary：备份中使用的xtrabackup的可执行文件

5、backup-my.cnf：备份命令中使用的配置选项信息。

授予用户最小备份权限的方法：

mysql> create user 'backup-user' identified by '123123';

Query OK, 0 rows affected (0.01 sec)

mysql> revoke all privileges,grant option from 'backup-user';

Query OK, 0 rows affected (0.00 sec)

mysql> grant reload,lock tables,replication client on *.* to 'backup-user'@'localhost';

Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mysql -u backup-user -p -h 10.40.0.5

Enter password:

1、完全备份

建议：在使用xtrabackup进行备份的话，建议将innodb设置成每表一个表空间。设置方法是在my.cnf文件中的[mysqld]区域添加innodb_file_per_table=1(如果不是新数据库，需要先备份数据库再操作，然后删除数据库数据，数据库初始化、)

innobackupex —user=username —password=usernamepassword /path/to/backup-dir/(指定备份到那个目录下，并依据时间自动创建一个子目录)

备份：

[root@localhost 2016-08-23_17-20-56]# innobackupex --user=root --password=dbroot@dtedu /root/mysql/

分析：

160823 17:21:02 Finished backing up non-InnoDB tables and files//备份完毕

160823 17:21:02 [00] Writing xtrabackup_binlog_info//填写二进制日志信息

160823 17:21:02 [00] ...done

160823 17:21:02 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '234361645'

xtrabackup: Stopping log copying thread.

.160823 17:21:02 >> log scanned up to (234361645)

160823 17:21:02 Executing UNLOCK TABLES//解锁表

160823 17:21:02 All tables unlocked

160823 17:21:02 Backup created in directory '/root/mysql/2016-08-23_17-20-56’//备份存放位置

MySQL binlog position: filename 'mysql-bin.000006', position ‘29034591’//二进制日志文件及位置相关信息

160823 17:21:02 [00] Writing backup-my.cnf

160823 17:21:02 [00] ...done

160823 17:21:02 [00] Writing xtrabackup_info

160823 17:21:02 [00] ...done

xtrabackup: Transaction log of lsn (234361645) to (234361645) was copied.

160823 17:21:02 completed OK!

[root@localhost mysql]# pwd

/root/mysql

[root@localhost mysql]# ll

总用量 8

drwxr-x--- 5 root root 4096 8月 23 17:21 2016-08-23_17-20-56

drwxr-x--- 5 root root 4096 8月 23 17:29 2016-08-23_17-29-50

[root@localhost mysql]# ls 2016-08-23_17-2

[root@localhost 2016-08-23_17-20-56]# cat xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0//备份从二进制日志文件的0纪录开始

to_lsn = 234361645

last_lsn = 234361645//到二进制文件的此纪录结束，此纪录和binlog_info的不同主要是因为这个是每个innodb表都有一个二进制纪录，而非那个二进制纪录。

compact = 0//是否压缩

recover_binlog_info = 0

[root@localhost 2016-08-23_17-20-56]# cat xtrabackup_binlog_info

mysql-bin.00000629034591//具体到备份这一刻时为止，二进制日志文件及指针工作的位置。

恢复：

备份的文件并不能直接进行恢复，需要先将备份文件设置为预准备状态“prepare”。因为此时备份的数据可能有尚未提交的事务或已经提交但尚未同步至数据文件中的事务，此时数据处于一个不一致状态，prepare的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件中。同样如果有增量备份的时候，则完全备份的内容只能“提交事务”，不能“回滚事务”，因为回滚事务可能会和后面的增量备份产生冲突。切记。

通用步骤：

1、安装mysql数据库

2、初始化数据库 mysql_install_db —user=mysql —datadir=/data/dtedu/ —basedir=/usr/local/

3、启动mysql

涉及完全备份（没有增量备份）的恢复工作：

1、prepare一个完全备份，需要指定备份文件的位置

需要参数：—apply-log用于提交事务，回滚事务。

[root@localhost mysql]# innobackupex --apply-log /root/mysql/2016-08-23_17-20-56/

160823 17:58:32 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

prints "completed OK!".

2、从一个完全备份中恢复数据

需要参数：--copy-back 用于执行恢复操作，其通过复制所有数据相关的文件到mysql服务器datadir目录中来执行恢复操作，innobackupex通过back-my.cnf来获取datadir目录的相关信息

初始化数据库

[root@localhost ftp]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/ftp/ --basedir=/usr/local/mysql/

Installing MySQL system tables...

删除初始化的数据库

[root@localhost ftp]# rm -rf /ftp/*

恢复数据库

[root@localhost ftp]# innobackupex --copy-back --datadir=/ftp/ /root/mysql/2016-08-23_17-20-56/

160825 13:54:02 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

prints "completed OK!".

注意：1、在mysql的datadir目录的文件进行恢复的时候，恢复过来的文件所有者很可能并不是mysql用户，所以需要修改文件所有权

2、恢复数据库的时候，mysql数据库应该处于停止运行状况否则会报错。

使用innobackupex进行增量备份并恢复

每个innodb的页面都包含一个LSN信息，每当相关的数据发生变化，相应的页面的LSN就会自动增长，这正是innobackupex能够进行增量备份的基础。

注意：在进行恢复操作之后一定要再做一次完全备份。

增量备份仅用于innodb或者xtradb表，对于myISAM引擎的表执行增量备份实际就是完全备份。

“准备”prepare增量备份和“prepare”完全备份有着不同的方式，尤其要注意的是：

1、需要在每个备份（包括完全和各个增量备份）上，将已经提交的事务进行“重新提交”，并在“重新提交”后，所有的备份数据将合并到完全备份上。此时不要有回滚。

2、基于所有的备份将未提交的事务进行“回滚”。

实现增量备份的方法：

基本参数：

—incremental指定备份类型为增量备份，后面跟备份数据的存放位置

—incremental-basedir指定上次备份的存放位置，innobackupex会依据上次备份信息来进行增量备份。

第一次增量备份：

[root@localhost ec_school]# innobackupex --user=root --password=dbroot@dtedu --incremental /root/mysql-increment/ --incremental-basedir=/root/mysql/2016-08-25_16-16-32/

160825 16:26:27 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

prints "completed OK!".

查看分析

[root@localhost ec_school]# cat /root/mysql/2016-08-25_16-16-32/xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 234361906

last_lsn = 234361906

compact = 0

recover_binlog_info = 0

[root@localhost ec_school]# cat /root/mysql/2016-08-25_16-16-32/xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 234361906

last_lsn = 234361906

compact = 0

recover_binlog_info = 0

第二次增量备份：

[root@localhost ~]# innobackupex --user=root --password=dbroot@dtedu --incremental /root/mysql-increment/ --incremental-basedir=/root/mysql-increment/2016-08-25_16-26-27/

[root@localhost ~]# ll /root/mysql-increment/

总用量 12

drwxr-x--- 5 root root 4096 8月 25 16:26 2016-08-25_16-26-27//第一次增量备份

drwxr-x--- 5 root root 4096 8月 26 14:59 2016-08-26_14-59-03//第二次增量备份的存放位置

官方网址：https://www.percona.com/

针对mysql的工具集：https://www.percona.com/software

参考博客： http://www.toxingwang.com/database/mysql/1539.html

6、mysqlhotcop（自带）

冷备份工具

很少使用，速度也慢

恢复操作的注意事项：

1、物理备份恢复

首先需要停止服务器的对应服务，这里是mysql，然后记录相关目录的权限和相关配置参数，将需要恢复的内容复制到相应目录中，然后恢复目录权限，最后启动服务。

2、逻辑备份恢复

用户管理篇

用户权限贯穿于使用mysql的始终，通常涉及到的用户权限表包括user，columns_priv（字段级别授权）、procs_priv（函数级别授权）、proxies_priv（代理授权）、tables_priv（表授权）、db（数据库级别授权）

用户权限的划分：（上面各个表的另一种说法）

全局级别

数据库级别

表级别

字段级别

存储过程、函数等级别

管理级别权限

用法	说明
create temporary tables	创建临时表
create user	创建其他用户
file	读写文件
lock tables	锁定表
reload	类似flush和reset

库级别表级别权限

alter、alter routin	create view、delete
grant option、转授自己的权限给其他用户	show create view 查看表创建的方法的命令
create、create routine	drop、execute
index、insert、select	update
show create table table name

实例：

1、创建用户。

mysql> create user gongbing identified by 'gongbing';

2、授予用户权限，grant priv，priv2 on [table|function|procedure] db.tbname to user@host,user2@host identified by ‘password'

授予用户查询、更新修改的权限，并且可以在任意主机登录%，（指定主机输入ip地址，本地是localhost）,字段级别授权select（username，age）

mysql> grant select,update on mysql.* to gongbing@localhost identified by 'gongbing';

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3、查看可以授予用户的权限有哪些？

mysql> show privileges;

+-------------------------+---------------------------------------+-------------------------------------------------------+

| Privilege | Context | Comment |

+-------------------------+---------------------------------------+-------------------------------------------------------+

| Alter | Tables | To alter the table |

| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |

| Create | Databases,Tables,Indexes | To create new databases and tables |

| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |

| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |

| Create view | Tables | To create new views |

| Create user | Server Admin | To create new users |

| Delete | Tables | To delete existing rows |

| Drop | Databases,Tables | To drop databases, tables, and views |

| Event | Server Admin | To create, alter, drop and execute events |

| Execute | Functions,Procedures | To execute stored routines |

| File | File access on server | To read and write files on the server |

| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |

| Index | Tables | To create or drop indexes |

| Insert | Tables | To insert data into tables |

| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |

| Process | Server Admin | To view the plain text of currently executing queries |

| Proxy | Server Admin | To make proxy user possible |

| References | Databases,Tables | To have references on tables |

| Reload | Server Admin | To reload or refresh tables, logs and privileges |

| Replication client | Server Admin | To ask where the slave or master servers are |

| Replication slave | Server Admin | To read binary log events from the master |

| Select | Tables | To retrieve rows from table |

| Show databases | Server Admin | To see all databases with SHOW DATABASES |

| Show view | Tables | To see views with SHOW CREATE VIEW |

| Shutdown | Server Admin | To shut down the server |

| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |

| Trigger | Tables | To use triggers |

| Create tablespace | Server Admin | To create/alter/drop tablespaces |

| Update | Tables | To update existing rows |

| Usage | Server Admin | No privileges - allow connect only |

+-------------------------+---------------------------------------+-------------------------------------------------------+

31 rows in set (0.00 sec)

4、查看指定用户所拥有的权限,grant可以直接追加新的权限到已有权限中。

mysql> show grants for 'gongbing'@'localhost';

+-----------------------------------------------------------------------------------------------------------------+

| Grants for gongbing@localhost |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'gongbing'@'localhost' IDENTIFIED BY PASSWORD '*569D4F969A530462D10D797857B82EAAF6C36E99' |

| GRANT SELECT, UPDATE ON `mysql`.* TO 'gongbing'@'localhost' |

| GRANT SELECT, UPDATE ON `mysql`.`user` TO 'gongbing'@'localhost' |

+-----------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

5、撤销指定用户的指定权限

mysql> show grants for 'gongbing';

+-------------------------------------------------------------------------------------------------------------------------------+

| Grants for gongbing@% |

+-------------------------------------------------------------------------------------------------------------------------------+

| GRANT SELECT, UPDATE, CREATE USER ON *.* TO 'gongbing'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |

| GRANT SELECT, UPDATE ON `mysql`.* TO 'gongbing'@'%' |

+-------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> revoke update on mysql.* from 'gongbing'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'gongbing';

+-------------------------------------------------------------------------------------------------------------------------------+

| Grants for gongbing@% |

+-------------------------------------------------------------------------------------------------------------------------------+

| GRANT SELECT, UPDATE, CREATE USER ON *.* TO 'gongbing'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |

| GRANT SELECT ON `mysql`.* TO 'gongbing'@'%' |

+-------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

远程登录mysql数据库

需要修改/etc/my.cnf配置文件内容，即将bind-address注释掉即可，如果还是不行，需要grant进行用户授权。

mysql> grant all on *.* to 'root'@'10.40.0.37' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

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

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*767C46297043DBC2F444C7D28E4B226047DA5C38' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'root'@'10.40.0.37';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for root@10.40.0.37 |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.40.0.37' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)