常用查看命令:
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
# /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’;
日志错误检查,修复:
# 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
# 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
备份:
# mysqldump -uroot -p mydb > /root/mysql.sql
# mysqldump -uroot -p --events --routines --triggers --databases mydb mysql > /root/mydb-`date +"%F"`.sql
恢复:
PS:恢复前要先建立mydb数据库。
# 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快照了备份和恢复
# pvcreate /dev/sda{5,6}
# vgcreate vg1 /dev/sda{5,6}
# lvcreate -L 2G -n mydata vg1
# mkfs.ext3 /dev/vg1/mydata
# mkdir /mydata
# mount /dev/vg1/mydata /mydata
# cp -rp /data/* /mydata/
# chown mysql:mysql /mydata/
编辑mysql的主配置文件将数据目录改为mydata。
mysql> flush tables with read lock;
mysql> flush logs;
# lvcreate -L 20M -s -n mysnap /dev/vg1/mydata
# mkdir /mysnap
# mount /dev/vg1/mysnap /mysnap/
假设要备份mydb数据库
# cp -rp mydb /tmp/mydb2
# umount /mysnap/
# lvremove /dev/vg1/mysnap
# 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 做时间点恢复
# 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;
# 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;
做时间点恢复:
# mysqlbinlog /data/mysql-bin.000002 --start-position 6355 --stop-position 7701 > /tmp/mydbrestore.sql
# mysql -uroot -p < /tmp/mydbrestore.sql
PS:xtrabackup 免费开源备份工具,企业中使用。
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
# /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’;
日志错误检查,修复:
# 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
# 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
备份:
# mysqldump -uroot -p mydb > /root/mysql.sql
# mysqldump -uroot -p --events --routines --triggers --databases mydb mysql > /root/mydb-`date +"%F"`.sql
恢复:
PS:恢复前要先建立mydb数据库。
# 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快照了备份和恢复
# pvcreate /dev/sda{5,6}
# vgcreate vg1 /dev/sda{5,6}
# lvcreate -L 2G -n mydata vg1
# mkfs.ext3 /dev/vg1/mydata
# mkdir /mydata
# mount /dev/vg1/mydata /mydata
# cp -rp /data/* /mydata/
# chown mysql:mysql /mydata/
编辑mysql的主配置文件将数据目录改为mydata。
mysql> flush tables with read lock;
mysql> flush logs;
# lvcreate -L 20M -s -n mysnap /dev/vg1/mydata
# mkdir /mysnap
# mount /dev/vg1/mysnap /mysnap/
假设要备份mydb数据库
# cp -rp mydb /tmp/mydb2
# umount /mysnap/
# lvremove /dev/vg1/mysnap
# 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 做时间点恢复
# 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;
# 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;
做时间点恢复:
# mysqlbinlog /data/mysql-bin.000002 --start-position 6355 --stop-position 7701 > /tmp/mydbrestore.sql
# mysql -uroot -p < /tmp/mydbrestore.sql
PS:xtrabackup 免费开源备份工具,企业中使用。