Mysql技术小疑问

Mysql Tips:

1.复制过滤问题：
 --replicate-do-db=db_name：
USE prices;
UPDATE sales.january SET amount=amount+1000;
Statement-based replication： 对于基于语句级的复制（或mixed级别），replicate_do_db这个参数指的是默认的数据库，即当前使用的数据库（use database），在默认数据库下更新replicate_do_db指定的数据库，从库不会随之更新，会有问题
解决：对于binlog_format=statement或mixed，只在从库设置replicate_wild_do_table= sales.%或replicate_wild_ignore_table即可。此时可以避免跨库更新问题。
对于binlog_format=statement或mixed，只在从库设置replicate_wild_do_table= quyou.%或replicate_wild_ignore_table即可。此时可以避免跨库更新问题。
对于binlog_format=statement或mixed，只在从库设置replicate_wild_do_table= quyou.%或replicate_wild_ignore_table即可。此时可以避免跨库更新问题。
Row-based replication：不存在问题

2.InnoDB单列索引长度不能超过767bytes限制问题
实际上联合索引还有一个限制是3072:
By default, the index key prefix length limit is 767 byte
When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.
The limits that apply to index key prefixes also apply to full-column index keys.
If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.


解决：
MySQL 5.5.14及其后版本解决了最大767字节索引长度的问题，引入了innodb_large_prefix配置关键字。这项关键字必须配合innodb_file_format和innodb_file_per_table
innodb_large_prefix = True
innodb_file_format = Barracuda
innodb_file_per_table = True


Barracuda 在原来的基础上(Antelope)新增了Dynamic和Compressed两种行格式。


Barracuda is the newest file format. It supports all InnoDB row formats including the newer COMPRESSED and DYNAMIC row formats. The features associated with COMPRESSED and DYNAMIC row formats include compressed tables, off-page storage for long column data, and index key prefixes up to 3072 bytes (innodb_large_prefix).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91975/viewspace-2123899/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-2123899/