Home

zhangyiqun

Thoughts, stories and ideas.

2014年以前 About Feed

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 ~]# mysqldump  -q –opt –default-character-set=latin1  –single-transaction –extended-insert=false –max_allowed_packet=64M voice    gzip > voice.sql.gz

导入

[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 最常用

2.基于时间点mysqlbinlog –stop-date=”时间” /日志 mysql -u root -p

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中执行

mysqldump –opt db_name mysql -h hostname -P port -u user -p passwd db_name

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

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

3.mysql_fix_privilege_tables升级权限表


zhangyiqun

scribble

2014年以前 About Feed