Jansfer – 长风破浪会有时，直挂云帆济沧海

欢迎访问火丁笔记：http://huoding.com/
火丁的订阅地址：http://huoding.com/feed

作者：老王

首先设置MySQL主服务器：

在主服务器上为从服务器建立一个用户：

grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’;

如果使用的是MySQL 4.0.2之前的版本，则用file权限来代替replication slave

编辑主服务器的配置文件：/etc/my.cnf

server-id=1
log-bin
binlog-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db=不需要复制的数据库苦命，如果复制多个数据库，重复设置这个选项即可

注意：如果你想做一个复杂点的结构：比如说，A->B->C，其中B是A的从服务器，同时B又是C的主服务器，那么B服务器除了需要打开log-bin之外，还需要打开log-slave-updates选项，你可以再B上使用“show variables like ‘log%’;”来确认是否已经生效。

编辑从服务器的配置文件：/etc/my.cnf

server-id=2
master-host=主机
master-user=用户名
master-password=密码
master-port=端口
replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
replicate-ignore-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

配置主从服务器的my.cnf时，留心各自的server-id一定要彼此独立，不能重复，否则，会出现如下错误：

Slave: received end packet FROM server, apparent master shutdown

另一个需要注意的是最好在从服务器的my.cnf里设置read_only选项，防止发生意外（连接用户不能有SUPER权限，否则无效）。

记得先手动同步一下主从服务器，数据量小的话可以用mysqldump，它有一个master-data参数很有用，通过使用此参数，导出的SQL文件里会自动包含CHANGE MASTER TO MASTER_LOG_FILE=’…’, MASTER_LOG_POS=…;，这样创建从服务器就更方便了。

如果数据量大的话不太适合使用mysqldump（慢），如果是myisam表的话，加上–lock-all-tables参数，如果是innodb表的话，加上–single-transaction参数。

而应该采用拷贝文件的方式，请按如下操作步骤：

先在主服务器上锁定所有的表，以免在复制过程中数据发生变化：

mysql> flush tables with read lock;

然后在主服务器上查询当前二进制文件的文件名及偏移位置：

mysql > show master status;

然后停止主服务器上的MySQL服务：

shell> mysqladmin -u root shutdown

注意：如果仅是MyISAM的话，可以不停止MySQL服务，但要在复制数据文件的过程中保持只读锁，如果是InnoDB的话，必须停止MySQL服务。

再拷贝数据文件：

shell> tar -cvf /tmp/mysql-snapshot.tar .

拷贝完别忘了启动主服务上的MySQL服务了。

然后把数据文件应用到从服务器上，再次启动slave的时候使用，记得启动时加上skip-slave-start选项，使之不会立刻去连接master，再在从服务器上设置相关的二进制日志信息：

mysql> CHANGE MASTER TO
->     MASTER_HOST=’master_host_name’,
->     MASTER_USER=’replication_user_name’,
->     MASTER_PASSWORD=’replication_password’,
->     MASTER_LOG_FILE=’recorded_log_file_name’,
->     MASTER_LOG_POS=recorded_log_position;

启动从服务器上的复制线程：

mysql> start slave;

验证主从设置是否已经成功，可以输入如下命令：

mysql> show slave status\G

会得到类似下面的列表：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这两个选项不全是Yes，那就说明你前面某个步骤配置错了。

应该保证从服务器上任何数据的修改都是通过从主服务器上复制操作获取的，换句话说，从服务器应该是只读的，如果不能保证这一点，则可能造成主从数据不一致。可以在从服务器的my.cnf里加入read-only参数来实现这一点，唯一需要注意的一点事read-only仅对没有super权限的用户有效。所以最好核对一下连接从服务器的用户，确保其没有super权限。
从理想角度看，主从数据库应该无故障的运转下去，可以有时候还是会出现一些莫名其妙的问题，比如说即便从未在从服务器上手动更新过数据，但还是可能遇到“Error: 1062 Duplicate entry”错误，具体原因不详，可能是MySQL本身的问题。遇到这类问题的时候，从服务器会停止复制操作，我们只能手动解决问题，具体的操作步骤如下：
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
同样的操作可能需要进行多次，也可以设置自动处理此类操作，在从服务器的my.cnf里设置：
slave-skip-errors=1062
最后再唠叨一下日志的问题：时间长了，数据库服务器上的二进制文件会越来越多，清理是必要的，你可以设置自动清理，相关参数是expire_logs_days，也可以使用手动删除的方式，但这里说的手动不是指rm，而是指PURGE BINARY LOGS，删除任何日志前，最好在所有的从服务器上通过show slave status命令确认一下相关日志是否已经无用。
更详细的介绍参考官方文档：How to Set Up Replication，不喜欢英文的话可以看老叶同志的中文翻译。
补充：[ERROR] Error in Log_event::read_log_event(): ‘Event too big’
在使用主从复制的时候，出现的问题多半是和日志（主服务器的二进制日志，从服务器的延迟日志）相关的。比如说加入你遇到了上面的错误，你可以根据错误日志的信息在主从数据库服务器上分别执行：
mysqlbinlog 日志文件 > /dev/null
查看错误，如果没有错误，则不会有任何输出，反之会输出错误信息，如果确定了错误是出现在主服务器二进制日志上，可以跳过适当的位置，再在从服务器上重新设定LOG_POS，如果确定了错误是出现在从服务器延迟日志上，则可以删除从服务器的延迟日志（使用CHANGE TO MASTER的时候，除非设定了延迟日志信息，否则会自动删除延迟日志），并在从服务器上重新设定LOG_POS。期间也可以考虑手动执行不能自动执行的SQL日志。
补充：配置的时候如果版本允许最好打开sync_binlog选项。
补充：有时候，从服务器延迟日志可能已经损坏，这时需要执行CHANGE MASTER TO设置新的日志文件信息，但是在从服务器上SHOW SLAVE STATUS会显示很多日志信息，他们的含义有所不同：
Master_Log_File:Read_Master_Log_Pos 是IO相关的日志信息
Relay_Master_Log_File:Exec_Master_Log_Pos 是SQL相关的日志信息
从服务器需要设置的是SQL相关的日志信息：
slave stop;
change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number);
slave start;
1) When you are using the master as a consistent snapshot, use SHOW MASTER STATUS to determine the position.
2) When you are using a slave as a consistent snapshot, use SHOW SLAVE STATUS and Exec_Master_Log_Pos.
参考链接
补充：缺省情况下，从服务器会以主机名命名延迟日志，所以一旦你修改了从服务器的主机名就会造成问题，新版MySQL会提示你这个情况：
[Warning] Neither –relay-log nor –relay-log-index were used;
so replication may break when this MySQL server acts as a slave and has his hostname changed!!
Please use ‘–relay-log=name-relay-bin’ to avoid this problem.
设定relay-log可以避免此类问题。
升级：主从服务器最好时不时检查一下内容是否同步：核对MySQL主从服务器的数据一致性

来源：

http://hi.baidu.com/thinkinginlamp/blog/item/446953438ef616119313c632.html

在windows要让python能链接到mysql需要为添加正确的驱动， 我们可以通过

http://www.technicalbard.com/files/MySQL-python-1.2.2.win32-py2.6.exe

下载 for python 2.6 的mysql驱动。

接下来让我们写一段测试代码看看是不是已经可以正确连接了：

import MySQLdb
db = MySQLdb.connect(user='me', db='mydb', passwd='secret', host='localhost')
cursor = db.cursor()
cursor.execute('SELECT name FROM books ORDER BY name')
names = [row[0] for row in cursor.fetchall()]
print names
db.close()

如果在import MySQLdb的时候出现下面的提示错误：

'deprecation warning':
C:\Python\lib\site-packages\MySQLdb\__init__.py:34:
DeprecationWarning: the sets module is deprecated
from sets import ImmutableSet

那我们试着改写类库文件：

1) 打开 “__init__”, 替换:

from sets import ImmutableSet
class DBAPISet(ImmutableSet):

改为

class DBAPISet(frozenset)

2) 打开”converters.py”, 删除:

from sets import BaseSet, Set

3) 在”converters.py” 文件中把 “Set” 替换成 “set” (IMPORTANT: 有两处需要修改):

line 48: return set([ i for i in s.split(',') if i ])
line 128: set: Set2Str,

MySQL支持超过 100 个的可调节设置；但是幸运的是，掌握少数几个就可以满足大部分需要。查找这些设置的正确值可以通过 SHOW STATUS 命令查看状态变量，从中可以确定mysqld 的运作情况是否符合我们的预期。给缓冲区和缓存分配的内存不能超过系统中的现有内存，因此调优通常都需要进行一些妥协。

MySQL 可调节设置可以应用于整个 mysqld 进程，也可以应用于单个客户机会话。

服务器端的设置

每个表都可以表示为磁盘上的一个文件，必须先打开，后读取。为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存，其最大数目由 /etc/mysqld.conf 中的 table_cache 指定。清单 4 给出了显示与打开表有关的活动的方式。

清单 4. 显示打开表的活动

mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 5000  |
| Opened_tables | 195   |
+---------------+-------+
2 rows in set (0.00 sec)

清单 4 说明目前有 5,000 个表是打开的，有 195 个表需要打开，因为现在缓存中已经没有可用文件描述符了（由于统计信息在前面已经清除了，因此可能会存在 5,000 个打开表中只有 195 个打开记录的情况）。如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加，就说明缓存命中率不够。如果 Open_tables 比 table_cache 设置小很多，就说明该值太大了（不过有空间可以增长总不是什么坏事）。例如，使用 table_cache = 5000 可以调整表的缓存。

与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。

清单 5 显示如何确定是否缓存了足够的线程。

清单 5. 显示线程使用统计信息

mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 27     |
| Threads_connected | 15     |
| Threads_created   | 838610 |
| Threads_running   | 3      |
+-------------------+--------+
4 rows in set (0.00 sec)

此处重要的值是 Threads_created，每次 mysqld 需要创建一个新线程时，这个值都会增加。如果这个数字在连续执行 SHOW STATUS 命令时快速增加，就应该尝试增大线程缓存。例如，可以在 my.cnf 中使用 thread_cache = 40 来实现此目的。

关键字缓冲区保存了 MyISAM 表的索引块。理想情况下，对于这些块的请求应该来自于内存，而不是来自于磁盘。清单 6 显示了如何确定有多少块是从磁盘中读取的，以及有多少块是从内存中读取的。

清单 6. 确定关键字效率

mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads         | 98247     |
+-------------------+-----------+
2 rows in set (0.00 sec)

Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数。命中磁盘的读请求数除以读请求总数就是不中比率 —— 在本例中每 1,000 个请求，大约有 0.6 个没有命中内存。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。例如，key_buffer = 384M 会将缓冲区设置为 384MB。

临时表可以在更高级的查询中使用，其中数据在进一步进行处理（例如 GROUP BY 字句）之前，都必须先保存到临时表中；理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。清单 7 给出了与临时表创建有关的统计信息。

清单 7. 确定临时表的使用

mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files       | 2     |
| Created_tmp_tables      | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)

每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。tmp_table_size 和 max_heap_table_size 都可以控制临时表的最大大小，因此请确保在 my.cnf 中对这两个值都进行了设置。

每个会话的设置

下面这些设置针对于每个会话。在设置这些数字时要十分谨慎，因为它们在乘以可能存在的连接数时候，这些选项表示大量的内存！您可以通过代码修改会话中的这些数字，或者在 my.cnf 中为所有会话修改这些设置。

当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。如果sort_merge_passes 状态变量很大，这就指示了磁盘的活动情况。清单 8 给出了一些与排序相关的状态计数器信息。

清单 8. 显示排序统计信息

mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 1       |
| Sort_range        | 79192   |
| Sort_rows         | 2066532 |
| Sort_scan         | 44006   |
+-------------------+---------+
4 rows in set (0.00 sec)

如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size。例如， sort_buffer_size = 4M 将排序缓冲区设置为 4MB。

MySQL 也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT 语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。实现这种功能的命令如清单 9 所示。

清单 9. 确定表扫描比率

mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 318243 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)

Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过 4000，就应该查看 read_buffer_size，例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！

MySQL进入Oracle产品体系，获得了更多研发投入，新一代MySQL产品—MySQL5.5即将面世，较之之前的5.1版本，将获得诸多特性方面的提升，简单总结如下：

1. 默认存储引擎更改为InnoDB

InnoDB作为成熟、高效的事务引擎，目前已经广泛使用，但MySQL5.1之前的版本默认引擎均为MyISAM，此次MySQL5.5终于做到与时俱进，将默认数据库存储引擎改为InnoDB，并且引进了Innodb plugin 1.0.7。此次更新对数据库的好处是显而易见的：InnoDB的数据恢复时间从过去的一个甚至几个小时，缩短到几分钟(InnoDB plugin 1.0.7，InnoDB plugin 1.1， 恢复时采用红-黑树)。InnoDB Plugin 支持数据压缩存储，节约存储，提高内存命中率，并且支持adaptive flush checkpoint, 可以在某些场合避免数据库出现突发性能瓶颈。

Multi Rollback Segments： 原来InnoDB只有一个Segment，同时只支持1023的并发。现已扩充到128个Segments，从而解决了高并发的限制。

2. 多核性能提升

Metadata Locking (MDL) Framework替换LOCK_open mutex (lock)，使得MySQL5.1及过去版本在多核心处理器上的性能瓶颈得到解决，官方表示将继续增强对MySQL多处理器支持，直至MySQL性能“不受处理器数量的限制”

3. 复制功能(Replication)加强

MySQL复制特性是互联网公司应用非常广泛的特性，作为MySQL最实用最简单的扩展方式，过去的异步复制方式已经有些不上形势，对某些用户来说“异步复制”意味着极端情况下的数据风险，MySQL5.5将首次支持半同步(semi-sync replication)在MySQL的高可用方案中将产生更多更加可靠的方案。另外Slave fsync tunning;Relay log corruption recovery和Replication Heartbeat也将实现。

4. 增强表分区功能

MySQL 5.5的分区对用户绝对是个好消息，更易于使用的增强功能，以及TRUNCATE PARTITION命令都可以为DBA节省大量的时间，有时对最终用户亦如此：

1) 非整数列分区：任何使用过MySQL分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 5.1只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。很麻烦，而MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。在MySQL 5.1中使用分区另一个让人头痛的问题是date类型(即日期列)，你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单;

2) 多列分区：COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区;

3) 可用性增强：truncate分区。分区最吸引人的一个功能是瞬间移除大量记录的能力，DBA都喜欢将历史记录存储到按日期分区的分区表中，这样可以定期删除过时的历史数据。 但当你需要移除分区中的部分数据时，事情就不是那么简单了，删除分区没有问题，但如果是清空分区，就很头痛了，要移除分区中的所有数据，但需要保留 分区本身，你可以：使用DELETE语句，但我们知道DELETE语句的性能都很差。使用DROP PARTITION语句，紧跟着一个EORGANIZE PARTITIONS语句重新创建分区，但这样做比前一个方法的成本要高出许多。MySQL 5.5引入了TRUNCATE PARTITION，它和DROP PARTITION语句有些类似，但它保留了分区本身，也就是说分区还可以重复利用。TRUNCATE PARTITION应该是DBA工具箱中的必备工具;

4) 更多微调功能：TO_SECONDS：分区增强包有一个新的函数处理DATE和DATETIME列，使用TO_SECONDS函数，你可以将日期/时间列转换成自0年以来的秒数，如果你想使用小于1天的间隔进行分区，那么这个函数就可以帮到你。

5. Insert Buffering 如果在buffer pool中没找到数据，那么直接buffer起来，避免额外的IO;Delete & Purge Buffering 跟插入一样，如果buffer pool中没有命中，先buffer起来，避免额外的IO。

6. Support for Native AIO on Linux

以上的特性在MySQL 5.5的社区版当中都将包括，在MySQL企业版当中，除以上更新之外，Oracle还加强了更多实用的企业级功能，包括：

1. 实现在线物理热备

MySQL 企业版将包含Innodb Hotbackup(这也许是MySQL和InnDB多年之后重新聚首的新亮点)，从而一举解决过去MySQL无法进行可靠的在线实时物理备份的问题， InnoDB Hot Backup 不需要你关闭你的服务器也不需要加任何锁或影响其它普通的数据操作，这对MySQL DBA来说应该是一个不错的消息。

2. MySQL Enterprise Monitor 2.2 & Oracle Enterprise Monitor

是的，你没有看错，MySQL将可以被Oracle Enterprise Monitor监控，这是一个实现起来并不复杂，但在过去绝无可能的变化。并且MySQL企业版监控器(MySQL Enterprise Monitor)得到了更大的加强，版本更新至2.2，对MySQL服务器资源占用降低到可以忽略的地步，集成了监控，报警，SQL语句分析和给出优化建议，MySQL的一些开源监控方案相比之下显得过于简陋，对企业客户来说，MySQL变得更加可靠。

3. MySQL Workbench

过去MySQL的图形界面工具做的实在是令人难以恭维，当然这也给众多MySQL管理工具提供了市场空间，现在Oracle打算将MySQL做得比SQL-Server更加简单易用，MySQL Workbench是一款专为MySQL设计的ER/数据库建模工具，可以用来设计和创建新的数据库图示，建立数据库文档，以及进行复杂的MySQL 迁移等操作，因此内置workbench将使MySQL使用起来更简便高效。

4. 关于未来的重要提醒：Oracle的管理工具，MySQL也将能够使用，Oracle比MySQL社区想象要聪明，不是吗?当然MySQL 5.5我们还没看到这个变化，但变化已经在时间表上，MySQL社区版也能够被Oracle管理工具管理，前提你得是Oracle数据库的用户。

如果项目中使用多数据库Master+Slave实现数据负载分享，那么CakePHP如何对应实现读写分离呢？记得上一篇我们读到beforeFind和beforeSave方法吗？没错，整个实现过程非常简单。

首先修改database.php设置：

 'mysql',
'persistent' => false,
'host' => '192.160.1.110',
'port' => '',
'login' => 'root',
'password' => '1234',
'database' => 'cakephp',
'schema' => '',
'prefix' => '',
'encoding' => 'UTF8'
);
public $master = array(
'driver' => 'mysql',
'persistent' => false,
'host' => '192.168.1.100',
'port' => '',
'login' => 'root',
'password' => '',
'database' => 'cakephp',
'schema' => '',
'prefix' => '',
'encoding' => 'UTF8'
);
}
?>

然后修改app_module.php

useDbConfig = 'master';

}

function afterSave() {

$this->useDbConfig = 'default';

}

function beforeDelete() {

$this->useDbConfig = 'master';

}

function afterDelete() {

$this->useDbConfig = 'default';

}

}

?>

如果是多台Slave数据库呢？可以配置$slave_db1, $slave_db2….$slave_db10，然后在用随机数来简单实现从多台Slave获取数据。当然实际上会有更高效的方法实现均衡从多台Slave获取数据，这个下次讲。