MySQL_DBA资料整理(持续更新...)

简单说说我公司的环境
系统 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)有两个方面：字符集(Characterset)和排序方式(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 数据转移功能比较强大方便。可以两台服务器进行数据库转移操作，大家可以尝试使用。

===============================================================================================================

MySQL复制设置步骤

前言：
       镜像机制是一种能够让运行在不同计算机上的两个或更多个MySQL服务器保持同步变化的机制。目前MySQL只支持“主-从”镜像关系。即只有一台主控制系统（可读写），所有的数据修改操作都必须在这台MySQL服务器上进行；有一台或多台从属系统（只读），它们有着与主控系统完全一样的数据，主控系统上的数据变化在经过一个短暂的延迟后也将会发生在它们身上。
       “主-从”镜像关系中的数据同步是通过主控系统的二进制日志文件实现的；主控系统把自己执行过的SQL命令记载到自己的二进制日志文件里，从属系统则通过从主控系统的二进制日志文件读出SQL命令，并加以执行的办法来同步它们自己的数据库数据。“主-从”镜像机制不要求主控与从属系统都必须使用同一种操作系统。
        MySQL复制功能在MySQL5.0以上版本比较稳定、而且性能也比较好。从其他用户测试与MySQLAB公司介绍可以获知，本人曾恶意地使用Update语句修改了一张表的所有数据（数据量：17万多条，主从机器都不在同一网段，主机-外网，从机为虚拟机（内网）），几乎没有感觉到时延。
       另外，若企业使用MySQL复制特性，主要是出于安全性与速度方面的考虑，若读操作是导致数据库系统变慢的主要原因，建议大家还是先进行SQL优化与服务器配置优化，或增加内存与CPU。至于安全性方面，大家关注的可能是担心硬盘损坏等造成数据丢失，这种情况可以考虑使用RAID系统进行冗余存储，即硬盘数据建立镜像。
建议大家不要首先就考虑采用MySQL的镜像机制，投入的资金与取得的实际效果并不是最优的（引用大师的话：Michael Kofler）。
那什么情况下可以考虑采用镜像机制？我个人认为至少是这几点的结合体（因为我们必须为老板省钱且做好事情，才可能为公司创造更多价值，才可能发更多奖金，嘿嘿！）。
1>       主要是读操作影响数据库服务器性能，并不是SQL语句性能差，也不是程序性能差与服务器配置等的问题，确实访问量太多（主要指网站类型的，发财了！），使用MySQL群集解决负载的问题，公司不想投入那么多资金。这个是时候你可以考虑使用MySQL复制进行折中。
2>        需要进行在线分析处理（OLAP），但是数据库又要影响大量的事务处理（OLTP），，并且是需要实时数据分析，而且在线事务分析处理后的数据对公司的经营决策非常重要，这个时候你可以考虑用一台服务器作为从服务器（比较适合游戏公司）。
3>       为了数据的安全性、备份、用于查询操作。因为使用RAID只可能解决因硬盘损坏造成的数据损失，而不能防止操作系统崩溃、电源故障或其他类似意外。出现问题，可以立即把从机升级为主服务器，若限制了从机只能读的话，需要修改（还有权限信息是否也镜像过来了或是否一致）。若限制从机不能修改，则建议不要把mysql数据库镜像过来，还有就是可以把一些查询操作放在从机上执行，以及数据库的备份操作也可以放在从机上进行（比较适合游戏公司与中小型企业）。
金融以及对数据安全、影响性能要求高的企业或国家部门，肯定会采用群集的！以上是本人根据学习与实际工作写的东东，只是我个人的观点噢，是否合理还有待大家的实际检验，有不正确的地方还请大家指定。下面介绍复制的过程。
一、测试环境
        主机：
                主控机操作系统：FreeBSD
                IP：10.21.0.5
                MySQL版本：5.0.27
               
从属机操作系统：Linux AS3.0（安装在虚拟机上）
                IP：192.168.0.229
                MySQL版本：5.0.27
                                                                                                                                                                     
二、主控机的my.cnf文件与复制相关的配置
[mysqld]
log_bin = ccxy_db
binlog-do-db = ccxyaccount
binlog-ignore-db = mysql,ccxydata_lz,ccxydata_ws,ccxydata_wy,test,ccxydata

三、从属机的my.cnf文件与复制相关的配置
[mysqld]
server-id            = 2
master-user          = eugene
master-password      = 2006eugene
master-port          = 3306
master-connect-retry = 120
read-only            = 1
relay-log-purge      = 1
replicate-do-db      = ccxyaccount
replicate-ignore-db  = mysql,test
report-host          = eugene
slave-skip-errors    = all
#master-log-file      = ccxy_db.000008

在主控服务器上进行权限设置：
grant replication slave on *.* to 'eugene'@'192.168.0.229' identified by '2006eugene'
对需要进行镜像的数据库进行备份，当然你也可以不备份直接使用通过镜像来完成（不建议使用这种方法）。由于我的数据库存储引擎为MyISAM，所以使用我的备份脚本接可以完成备份与打包并且传送到指定的服务器上。然后进行解压缩等相关操作。
先在主控服务器上执行：

mysql> show master  status;
+----------------+----------+--------------+---------------------------------------------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB                                        |
+----------------+----------+--------------+---------------------------------------------------------+
| ccxy_db.000008 |       98 | ccxyaccount  | mysql,ccxydata_lz,ccxydata_ws,ccxydata_wy,test,ccxydata |
+----------------+----------+--------------+---------------------------------------------------------+
1 row in set (0.00 sec)

使用了master-log-file设置选项，就无法启动从属服务器上的mysqld，提示找不到日志文件。注释掉后就可以启动mysqld（加上master-log-pos也不行）。进入mysql命令模式下执行：
mysql> slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
出现错误，然后再执行Change master语句就可以解决：
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
在从属服务器上执行：
mysql> change master tomaster_host='10.21.0.3',master_user='eugene',master_password='2006eugene',master_log_file='ccxy_db.000008',master_log_pos=98;
Query OK, 0 rows affected (0.01 sec)

接着执行：
mysql> slave start;
Query OK, 0 rows affected (0.01 sec)
就可以解决，至于为什么会出现这样的问题，目前我无法解释（需要等待我找出更多证据后再告诉大家）。

在从属服务器上也可以设置某张或多张表的数据不进行复制，只要在my.cnf 文件中添加：
replicate-wild-ignore-table=dbname.tablename
也可以限制不复制某个数据库：
replicate- ignore-db =dbname
注意：
        在主服务器上设置了binlog-ignore-db就会把这些被列出来的数据库任何更改都不会写入二进制日志文件中的，若需要保留这些更改日志，请不要在主服务器上设置这个选项。而是在从属服务器的my.cnf文件中设置需要复制的数据库与不复制的数据库。
        另外：使用复制功能应该根据用途不同与实际情况，考虑服务器之间的物理位置关系（指是否在同一域或同网段内等），因为网络传送会影响到你复制的速度，从而可能抵消一部分你为了提供系统响应的速度。

===============================================================================================================

MySQL系统日常管理

日常管理的主要职责是对MySQL服务器程序mysqld的运行情况进行管理，使数据库用户能够顺利地访问MySQL服务器。下面是这项工作的主要职责：
服务器的启动和关闭。这一职责的具体内容包括：
1）从命令行以手动方式启动和关闭MySQL服务器；
2）安排MySQL服务器在系统开机和关机过程中自动的启动和关闭；
3）在MySQL服务器崩溃或者非正常启动时把它恢复到正常的运行状态。
对用户账户进行管理。这一职责的具体内容包括：
1）了解MySQL用户账户与UNIX或Windows注册账户之间的区别；
2）设置MySQL用户账户，限制用户只能从指定的机器上去连接MySQL服务器；
3）把正确的连接参数通知给新用户，使他们能顺利地连接上MySQL服务器--他们的工作是使用数据库而不是设置账户！
4）如果用户（或者你本人）忘记了口令，你还要知道怎样才能重新设置一个新口令。
对日志文件进行管理。这一职责的具体内容包括：
1）知道自己都能对哪些类型的日志文件进行管理；
2）在什么时候以及如何去进行管理；
3）制定并实施日志循环和失效机制，防止日志文件把文件系统的可用空间消耗殆尽。
对数据库进行备份和搬迁。当系统发生崩溃的时候，数据库备份将发挥至关重要的作用。你肯定希望自己能够以尽可能小的数据损失与最少时间把系统恢复到崩溃发生之前的状态。但要注意的是，数据库备份工作与普通意义上的系统备份工作（比如用UNIX工具程序dump进行的备份工作）是有区别的。系统备份工作通常由系统管理员负责，他在备份工作开始之前不一定把MySQL服务器关闭掉。于是，在系统备份工作的进行过程中，可能会有某些数据表的内容因为MySQL服务器仍在对它们进行着读写而发生变化--用这样的备份来恢复系统将导致那些数据表的内容发生错乱。mysqldump程序生成的备份文件更适用于数据库恢复操作，而且它不要求你必须在备份开始之前先关闭MySQL服务器。你还可能需要磁盘满时移动数据库。
数据库的搬迁指的是把数据库从一个硬盘转移到另一个硬盘上去。当磁盘的可用空间所剩无几时或者你想把某些数据库转移到另一台速度更快的主机上时，就需要对有关的数据库进行搬迁。这里要提醒大家注意这样一个问题：数据库文件依赖于具体的操作系统，所以数据库的搬迁操作不一定总能用简单的文件拷贝命令完成。                                                                                                                                                                                                                                                                                                                                                                  
建立数据库镜像。如果把对数据库进行备份或者拷贝比喻成数据库拍"照片"的话，建立数据库镜像就相当于给数据库拍"录像"了。建立数据库镜像需要同时运行两个数据库服务器并使它们构成主、从关系，这样对主服务器所管理的某个数据库所做的修改将同步地（会稍有延迟）反映在从服务器所管理的与之对应的数据库里。
对服务器进行配置和优化。数据库用户都希望数据库服务器运行在最佳状态，而改善服务器性能的最简单方法是添置更多的内存和更高速的硬盘。但这绝不能成为你不钻研数据库工作原理的理由--在如此"蛮干"之后，仍需要对服务器进行配置和优化。这一职责的具体内容包括：
1）知道有哪些参数可以用来对服务器进行优化；
2）如何根据具体情况来进行这些优化。某些站点上的查询多为数据检索操作，而另一些站点上的查询却多为数据插入和修改操作。具体到你的站点，应该根据实际观察到的查询"混合比例"去选择最有效的参数来加以改变。
对数据库服务器进行"本地化"（比如设置适当的字符集和时区等）也是其配置工作之一。
同时运行多个服务器。某些场合需要同时运行多个服务器。你或许是相对MySQL软件的一个新版本进行测试但又必须让现有的服务器保持运行，或许是想通过让不同的用户组去使用不同的服务器以便为各组用户提供更好的隐私保护机制。（后一种情况特别适合于ISP。）无论哪一种情况都需要你掌握同时安装并启动多个MySQL服务器的技术。
对MySQL软件进行升级。与其他软件产品一样，MySQL也在不断地更新换代。想适用漏洞更少、功能却更丰富的新版本，就必须掌握软件的升级技术。这一职责的具体内容包括：
1）知道如何对MySQL软件进行升级；
2）在哪些情况下不进行升级更合理；
3）如何在稳定版本和测试版本之间做出选择。