MySQL 用户管理 备份复制 恢复

 

常用查看命令：

mysql> select version();

mysql> status;

mysql> show variables\G;

mysql> show global variables;

mysql> show processlist\G;

用户管理权限：

创建用户：

mysql> use mysql

mysql> create user linna;

Query OK, 0 rows affected (0.04 sec)

mysql> select Host,User,Password from user;

mysql> create user sandy identified by '123456';

Query OK, 0 rows affected (0.00 sec)

为用户设置密码：

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

Query OK, 0 rows affected (0.00 sec)

授予权限：

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;

创建一个用户并授权：

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;

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

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();    //** 登录时匹配到的用户账号（授权信息）

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

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

日志 备份和恢复

日志种类：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)

启用二进制日志：--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

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;

the general query log  一般查询日志

the slow query log  慢速查询日志

特殊日志：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;

mysql> repair table course2;

对表本身作分析和优化：

mysql> analyze table course2;

mysql> optimize table course2;

对MySQL备份和恢复：

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

          冷备 温备（只读不能写） 热备

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

物理备份：备份数据文件

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

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

常用备份工具：

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

相关参数：

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/

假设要备份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

时间点恢复（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

然后修改数据库：

将mydb数据库删除，然后尝试恢复：

mysql> create database mydb;

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

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

mysql> show binary logs;

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 免费开源备份工具，企业中使用。

转载于:https://blog.51cto.com/lyp0909/561847