MySQL_DBA资料整理

cyt2005 · 发表于 2007-8-23 18:06

关注留名

天下大晴 · 发表于 2007-10-26 10:45

简单说说我公司的环境
系统 linux
数据库 mysql 5.0.22
表格数量 188
备份每月一次性数据备份
日防问量不是太大，主要是程序软件连接，操作使用。

感觉mysql数据库在中小型系统中还是比较稳定和可靠的。公司未设定专门的mysql dba 是由WEB项目组兼职管理，专业性上不是特别强，采用的mysqldump命令月末一次性数据备份。

发现的一个问题是在升级mysql数据库版本，原来的软件使用的是4.0，现在更新到5.0.22上发现数据转移乱码问题，根据分析如下：

1.mysql数据库表格字段类型长度算法发生改变。
2.mysql数据库4.X升级5.X 管理员密码加密处理函数改变问题。
3.mysql数据库4.X升级5.X数据库，表格字符集参数配置改变问题。
4.mysql数据库4.X升级5.X表格内原有数据转移转码问题。

首先在执行转移数据时候发生一个错误，字段长度错误，根据查询，在4.0版本中字段varchar类型最长限制为255，5.X版本中提升为65535。在执行数据转移的过程中，表格从类型、字段、列、行都应该进行转换。当数据库表格有字符串字段长度定义为255的时候在转移时候发生错误。错误原因如下：

在4.0中varchar长度是1到255，最长255个字节。
在4.1以后varchar长度是0到255，最长256个字节。
在5.0以后varchar长度是0到65535，最长65536个字节。

随后根据数据转移，4.0版本升级后，数据依旧判定为最长255个字节，编译器根据5.0内核判定表格字段长度定义应该在0到254，最长255个字节，才是正确的，字段定义为255，从0到255一共256字节长度判定错误。另外一个错误原因是如果将此字段做为索引键，它的最大长度就不能大于255个字节。而4.0和5.X虽然只有0和1开头的一点区别，但是足以造成数据无法转移的错误。

解决方法是在数据转移之前改变相关设置字符串长度255的表格，将字符串字段长度修改为254，即可解决问题，转移过后再修改回255即可。此操作同时解决以上两方面的错误。其中值得注意的一点是当字段有字符串值的时候，此错误不会发生，因为mysql编译器是根据当前字符串长度计算，如果是空就按最长字节数计算。

然后第二个问题是mysql数据库从4.0升级到5.X之后内核函数发生改变，错误发生在用户通过WEB，服务端或者客户端尝试连接mysql数据库时。原因是mysql数据库管理员密码系统加密错误。解决方法如下：

1.进入mysql命令行模式。
2.输入命令1：
Mysql>UPDATE mysql.user SET Password = OLD_PASSWORD('数据库管理员密码')
WHERE User = '数据库管理员帐号';
3.输入命令2：
Mysql>FLUSH PRIVILEGES;

处理过后，数据库管理员密码加密方式已经更新，通过各种程序连接即可成功。但是网页上显示内容乱码。原因是第三个和第四个问题造成。

第三个问题表现在mysql数据库4.0版本未支持多语言，在创建数据库的时候只有一个默认配置latin1，而5.X以上版本在创建数据库的时候都可选择不同的字符集参数配置。其简单描述如下：
MySQL 5的字符集支持(Character Set Support)有两个方面：字符集(Character set)和排序方式(Collation)。对于字符集的支持细化到四个层次: 服务器(server)，数据库(database)，数据表(table)和连接(connection)。也就是说MySQL 5 对于字符集的指定可以细化到一台机器上安装的 MySQL，其中的一个数据库，其中的一张表，其中的一栏，应该用什么字符集。
所以我们在转移数据之前就应该在安装mysql5.X版本数据库时配置正确，其具体细节说明如下：
1.编译 MySQL 时，指定了一个默认的字符集，这个字符集是 latin1；这个是我们不能改变的。
2.安装 MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的。
3.启动 mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的（my.ini）。
4.此时 character_set_server 被设定为这个默认的字符集；当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为 character_set_server。
5.当选定了一个数据库时，character_set_database 被设定为这个数据库默认的字符集；在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，也就是这个数据库默认的字符集；当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集。

以上1-4点操作是在进行数据库升级，数据转移之前所做的工作，结果是让数据转移之后，用户使用新版本数据库不会产生错误。

因为我们WEB采用的UTF-8编码，方便多语言处理，4.0数据库只有一个默认字符集编码，而5.X可以选择支持多语言字符集，所以在转移数据的时候发生问题。处理过后能够正常使用，但是某些字符串还是有吞噬半角情况发生，暂无完整的解决办法。

另外，我使用的Navicat 数据库操作工具，里面的Data Transfer 数据转移功能比较强大方便。可以两台服务器进行数据库转移操作，大家可以尝试使用。

thrashz · 发表于 2007-10-26 14:32

除了备份和恢复以外，还有数据的迁移。
另外还有 MySQL 的高可用性。

希望大家能多发表意见，顶！

jinguanding · 发表于 2007-10-27 00:05

今天看到自己的很久之前的贴被人更新。。。。感觉很意外。。。。看来我也是该时候进行MySQL方面的整理了，，，下周我会很忙。。等忙完下周就整理出来共大家分享下我最近的经验。。
谢谢大家的支持！

cyt2005 · 发表于 2007-10-29 10:04

好
期待lz的总结~

star_zhang · 发表于 2007-10-30 07:58

等待你的分享哦,
樓主...

jinguanding · 发表于 2007-10-30 15:45

大家也开始整理一下嘛....我已经整理一部分..等整理完毕会上传上来的......对于理解不正确的部分,或者描述不正确的地方...但时候还请大家指定.......对于不完善的地方,也请大家到时候补充下.......

反正这段时间,事情不多了...因为我可了下自己最近一段时间的优化结果..已经几乎没有再优化的东西了...除了数据库的设计与字段类型.....关于这个优化问题...阻力大......说出来..都会让大家笑话我们公司的设计开发人员......

jinguanding · 发表于 2007-10-30 18:09

利用今天下午的时间整理了备份与还原的部分,供大家参考.....若不对的地方请大家批评与指正.....并且也乐于听取大家的关于此部分的意见.

zhycjun · 发表于 2007-11-27 10:17

很好，谢谢

jinguanding · 发表于 2007-11-27 11:22

很感谢大家的关注,接下来......我会继续整理下这方面的资料.......但是速度可能会慢点....因为目前我一个人要解决三款游戏的问题,而且问题还很多....其他人都被公司裁掉了........另外一个是我主管...副经理...是不会干这些脏活的了........

还得自己主动去检控下服务器的性能...这是我最想干的...因为我需要积累足够这方面的经验...
接下先谈下....SQL语句的优化与存储的问题.. 继续以网络游戏为例

[原创] MySQL_DBA资料整理

支持你

楼主应该再加上一点

关于还原与备份的部分