21 Jun 2009

Mysql实战笔记（管理与维护）

linux下的安装

rpm 安装路径不灵活

二进制 灵活性好，直接使用即可

./scripts/mysql_install_db –user=okooo 注意log和tmp的目录设置

源码 最灵活，性能最好，根据需要定制编译。下面是一个比较稳妥和普遍的编译指令。

./configure –prefix=/usr/local/webserver/mysql/ –enable-assembler –with-extra-charsets=complex –enable-thread-safe-client –with-big-tables –with-readline –with-ssl –with-embedded-server –enable-local-infile –with-plugins=innobase

常用命令

因模板对特殊符号（–）支持的不好，所以当命令无法执行时请把单-换成双-

查看服务器参数值：show variables;

查看服务器运行状态：show status;

shell下直接查看数据库状态：mysqladmin -uroot variables

查看当前连接的用户：select current_user();

查看当前使用的数据库：select database();

查询用户：select user,host from user;

查询正在执行的语句：show processlist;

查询数据库和表的统计信息：mysqlshow -uroot –count

查询数据库中表的信息：mysqlshow -uroot test –count

查询具体表结构：desc 表名;

查询错误号：perror 30

启动mysql：bin/safe_mysqld –user=mysql &

帮助：mysqld –verbose –help

查看默认的存储引擎：mysql> show variables like ‘table_type’;

当前数据库支持的引擎：mysql> show ENGINES \G;

涉及到修改数据库的常用命令

将wp_posts表中post_content字段中文字”old”替换为”new” ：update wp_posts set post_content=replace(post_content,’old’,’new’)

更改表名：rename table 原表名 to 新表名;

数据库导入导出

导出以voice库为例

[root@jeantoe ~]# mkdir DUMPDIR && cd DUMPDIR

导入

[root@jeantoe ~]# mysqladmin create voice

[root@jeantoe ~]# zcat voice.sql.gz | mysql -uroot –database=voice

在进行大数据导入时使用renice 把进程优先级调高（范围是19到-20）

[root@jeantoe ~]#renice -20 888

Mysql Tips

免登录执行sql语句(shell)

mysql -u root -p 123 -e “…”

错误处理(批量sql语句)

-f 强制执行sql

-v显示出错语句

–show-warnings全部错误信息

myisam表压缩，压后无法DML

mysqladmin

mysqlbinlog 日志管理

mysqlcheck (myisam维护，优化，分析)

mysqldump

mysqlhotcopy (myisam表热备份)

mysqlimport (数据库导入)

mysqlshow (数据库对象查看)

perror (错误代码查看)

replace (文本替换)

日志

错误日志：普通，系统故障时查看

二进制日志：记录了DDL和DML语句，不包括数据库查询语句，此日志对于灾难恢复极有用。

查询日志：记录客户端所有语句，默认关闭，影响性能

慢查询日志：记录时间超过long_query_time的日志，通常开启，分析性能问题

备份与恢复

策略

1.确定引擎，不同引擎处理数据一致性方面是不太一样的。

2.确定全备份或增量备份

3.备份周期

4.开启log-bin (rpm安装时日志的默认位置比较诡异/var/lib/mysql/log_bin)

5.经常作恢复测试，确保备份是有效的。

逻辑备份和恢复(速度慢)

1.mysqldump 最常用

3.基于位置mysqlbinlog –start-date=”时间” –stop-date=”时间” /日志 > /tmp/mysql_restore.sql

物理备份(兼容性问题)

1.冷备份cp

2.热备份

MYISAM引擎。本质上是将要备份的表加读锁，然后再cp数据文件到备份目录。通常使用mysqlhotcopy。在前者使用不正常时则手工加读锁(flush tables for read;)。

innoDB，通常使用ibbackup，缺点是收费因此普及率不高。

表的导入和导出

SELECT … INTO OUTFILE

LOAD DATA INFILE

跨平台操作时注意设置line-terminated-by

安全

1.不以root权限运行mysql

2.防止DNS欺骗

3.删除匿名帐号

4.只授予帐号必须的权限

5.除root外任何人不应该有mysql库和user表的存取权限

6.不把FILE,PROCESS或SUPER授予除管理员以外的帐号

7.DROP TABLE并不收回以前的相关访问授权

8.使用SSL

9.访问限制（HOST）

10.注意REVOKE漏洞

mysql常见问题总结

1.忘记mysql的root密码

–skip-grant-tables

2.myISAM表损坏

myisamchk -r tablename活myisamchk -o tablename

3.myISAM表超过4G无法访问

alter table tbl_name MAX_ROWS=……

4.DNS反向解析引起的宕机

–skip-name-resolve

5.mysql.sock丢失

mysql –protocol=TCP -uroot -p3306 -hlocalhost

优化（未实践）

1.去掉不需要的模块

2.只选择需要使用的字符集

3.静态编译，然而，如果希望装入用户自定义的函数，则不能使用静态二进制系统，因为 UDF 机制依赖于动态连接

升级p325

方法一：通过网络

1.在新mysql中创建和老版本相同的数据库

mysqladmin -h hostname -P port -u user -p passwd create db_name

老版mysql中执行

2.cp原mysql数据库目录覆盖新版mysql目录

以rpm包的位置为例，cp -R /var/lib/old_mysql/ /var/lib/new_mysql

3.mysql_fix_privilege_tables升级权限表