常用查看命令:

mysql> select version();

wps_clip_p_w_picpath-2582

mysql> status;

wps_clip_p_w_picpath-2585

mysql> show variables\G;

wps_clip_p_w_picpath-25370

mysql> show global variables;

wps_clip_p_w_picpath-9688

mysql> show processlist\G;

wps_clip_p_w_picpath-19786

用户管理权限:

wps_clip_p_w_picpath-15650

wps_clip_p_w_picpath-23424

创建用户:

mysql> use mysql

mysql> create user linna;

Query OK, 0 rows affected (0.04 sec)

mysql> select Host,User,Password from user;

wps_clip_p_w_picpath-5471

mysql> create user sandy identified by '123456';

Query OK, 0 rows affected (0.00 sec)

wps_clip_p_w_picpath-19660

为用户设置密码:

mysql> set password for linna@'%' =password('123456');

Query OK, 0 rows affected (0.00 sec)

wps_clip_p_w_picpath-19359

授予权限:

mysql> grant create on *.* to linna@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> grant select on *.* to linna@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

显示赋予用户的权限:

mysql> show grants for linna@'%'\G;

wps_clip_p_w_picpath-9030创建一个用户并授权:

mysql> grant select on mydb.stu to kate@'192.168.0.%' identified by 'redhat';

Query OK, 0 rows affected (0.05 sec)

mysql> drop user kate@'192.168.0.%';

Query OK, 0 rows affected (0.00 sec)

收回权限:

mysql> revoke select on *.* from linna@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for linna@'%'\G;

wps_clip_p_w_picpath-5531

将某个字段的查询权限授权用户:

mysql> grant select(course) on mydb.course to sandy@'%';

允许sandy将权限授予其他人:

mysql> grant select on mydb.course to sandy@'%' with grant option;

将表中的所有权限授予用户:

mysql> grant all privileges on db.* to kate@'%' identified by 'redhat';

PS:super privilege权限终止客户端连接,并能改变全局变量。

限定资源使用数:

mysql> grant select on *.* to 'linna'@'%' with max_queries_per_hour 5;

Query OK, 0 rows affected (0.00 sec)

with_queries_per_hour 每小时发起的最大查询数

max_update_per_hour  每小时最大的修改数

max_connections_per_hour 同一个用户每小时内的最多连接数

max_user_connections 同一个用户在同一时刻内的最大并发连接数

mysql> select current_user();    //** 登录时匹配到的用户账号(授权信息)

wps_clip_p_w_picpath-12716

mysql> select user();          //**登录时真正的用户身份

wps_clip_p_w_picpath-10107

mysql> show grants;        //**查看自己所拥有的权限

wps_clip_p_w_picpath-15991

日志 备份和恢复

日志种类:4+1种日志

the error log  错误日志

mqld在启动或者关闭过程中所产生的信息;

重要的关键的错误信息;

调度事件的执行过程;

如果启用主从架构,从服务器的说明信息。

启动错误日志:mysqld_safe  --log-error=/var/log/mysqld.err  如果不指路径,默认存放在数据目录下host_name.err。

--log_warnings 1(默认值1,启用) 是否将警告信息记录到错误日志中。

大于1,一些非法终止信息也会被记录到错误日志中。

PS:如果没有定义错误日志 错误信息将被定义到标准错误输出中去。

the binary log  二进制日志

对数据本身产生修改(或者可能发生修改)的动作将被记录下来。

作用:实现point-in-time-recovery(时间点恢复)的功能;实现复制功能。

存放位置:记录在数据目录下mysql-bin.index(0000001)

wps_clip_p_w_picpath-19707

启用二进制日志:--log-bin = /data/logs/binary/changelog

                --log-bin-index = /data/logs/relay/binarylog.index

查看二进制日志:mysqlbinlog

[root@server27 data]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000001

wps_clip_p_w_picpath-12531

mysqlbinlog mysql-bin.000001 \

    --start-datetime="2011-03-05 17:34:40" \

--end-datetime="2011-03-05 17:34:56" > sixteensecs.sql

mysqlbinlog --read-from-remote-server -uuser -p \     //**查看远程服务器上的二进制日志

     mysql-bin.000001 -h 192.168.0.182 -P 3306 \

--start-position=932 --end-position=1132 > remote000001.sql

在MySQL中查看:

mysql> show binlog events in 'mysql-bin.000001' from 7548 limit 3\G;

wps_clip_p_w_picpath-30898

the general query log  一般查询日志

the slow query log  慢速查询日志

wps_clip_p_w_picpath-8174

特殊日志:relay log  中继日志 主从复制模式中在从服务器上使用到的一种日志。

默认情况下日志存放在数据目录下,不建议将数据与二进制文件放在一起。

日志滚动:

flush logs 手动的方式滚动错误日志和二进制日志

purge binary logs 删除指定位置之前所有的日志

mysql> PURGE BINARY LOGS TO ’mysql-bin.00007’;

mysql> PURGE BINARY LOGS BEFORE ’2011-3-5 23:00:00’;

日志错误检查,修复:

[root@server27 mydb]# myisamchk --extend-check course2.MYI

Checking MyISAM file: course2.MYI

Data records:       0   Deleted blocks:       0

- check file-size

- check record delete-chain

- check key delete-chain

- check index reference

- check records and index references

[root@server27 mydb]# myisamchk --recover course2.MYI

- recovering (with keycache) MyISAM-table 'course2.MYI'

Data records: 0

在MySQL命令行下进行检查:

mysql> use mydb

Database changed

mysql> check tables course2;

wps_clip_p_w_picpath-25215

mysql> repair table course2;

wps_clip_p_w_picpath-5871

对表本身作分析和优化:

mysql> analyze table course2;

mysql> optimize table course2;

对MySQL备份和恢复:

备份方式:完全备份 增量备份 差异备份

          冷备 温备(只读不能写) 热备

逻辑备份:备份数据本身内容

物理备份:备份数据文件

一致性备份:所有的备份文件的最后修改时间都在备份时间点之前,而且所有日志中的数据都已经同步到硬盘中去。可以做恢复。

非一致性备份:备份文件的最后修改时间可能在备份时间点之后。

常用备份工具:

wps_clip_p_w_picpath-22118

Mysqldump 逻辑备份 温备

Provides a logical backup of entire database servers, individual databases, individual tables, or even subsets of data using the --where option

The logical backup created using mysqldump is often called a data dump

The output is in ASCII format — easily readable and manipulated

备份:

[root@server27 data]# mysqldump -uroot -p mydb > /root/mysql.sql

[root@server27 data]# mysqldump -uroot -p --events --routines --triggers --databases mydb mysql > /root/mydb-`date +"%F"`.sql

恢复:

PS:恢复前要先建立mydb数据库。

[root@server27 data]# mysql -uroot -p mydb < /root/mysql.sql

相关参数:

wps_clip_p_w_picpath-1×××

wps_clip_p_w_picpath-8045

PS:参考叶金荣《MySQL备份和恢复》。

SELECT  INTO OUTFILE

实现只针对某一个表或者表中的数据进行备份。

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

恢复,先创建表:

使用场景:truncate table stu;

mysql> load data infile '/tmp/stu.sql' into table stu;

Maatkit 第三方备份工具 www.maatkit.org/

mk-parallel-dump --basedir=/backups

mk-parallel-restore /backups

File system snapshot

通过LVM快照了备份和恢复

[root@server27 ~]# pvcreate /dev/sda{5,6}

[root@server27 ~]# vgcreate vg1 /dev/sda{5,6}

[root@server27 ~]# lvcreate -L 2G -n mydata vg1

[root@server27 ~]# mkfs.ext3 /dev/vg1/mydata

[root@server27 ~]# mkdir /mydata

[root@server27 ~]# mount /dev/vg1/mydata /mydata

[root@server27 ~]# cp -rp /data/* /mydata/

[root@server27 mydata]# chown mysql:mysql /mydata/

编辑mysql的主配置文件将数据目录改为mydata。

mysql> flush tables with read lock;

mysql> flush logs;

[root@server27 mydata]# lvcreate -L 20M -s -n mysnap /dev/vg1/mydata

[root@server27 mydata]# mkdir /mysnap

[root@server27 mydata]# mount /dev/vg1/mysnap /mysnap/

wps_clip_p_w_picpath-12830

假设要备份mydb数据库

[root@server27 mysnap]# cp -rp mydb /tmp/mydb2

[root@server27 /]# umount /mysnap/

[root@server27 /]# lvremove /dev/vg1/mysnap

[root@server27 /]# cp -rp /tmp/mydb2 /mydata/mydb

Mysqlhotcopy

yum install perl-DBI -y

yum install perl-DBD-MySQL -y

mysqlhotcopy  mydb  /backup

mysqlhotcopy --method=scp mydb \ username@backup.company.com:/backup

wps_clip_p_w_picpath-5680

时间点恢复(PITR)

mysqldumo+binlog 做时间点恢复

[root@server27 data]# mysqldump -uroot --flush-logs --lock-all-tables --master-data=2 --events --routines --triggers mydb >/root/mydb-`date +"%F-%H-%M-%S"`.sql

然后修改数据库:

wps_clip_p_w_picpath-17660

将mydb数据库删除,然后尝试恢复:

mysql> create database mydb;

[root@server27 data]# mysql -uroot -p mydb < /root/mydb-2011-04-10-18-23-57.sql

wps_clip_p_w_picpath-22854

看,没有备份后新添加的表t1,如何恢复t1?这就要基于时间点来恢复了!

mysql> show binary logs;

wps_clip_p_w_picpath-25934

mysql> show binlog events in 'mysql-bin.000002'\G;

做时间点恢复:

[root@server27 data]# mysqlbinlog /data/mysql-bin.000002 --start-position 6355 --stop-position 7701 > /tmp/mydbrestore.sql

[root@server27 data]# mysql -uroot -p &lt; /tmp/mydbrestore.sql

PS:xtrabackup 免费开源备份工具,企业中使用。