Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Mysql related technology example analysis". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. Copy filter questions:

--replicate-do-db=db_name：

USE prices;

UPDATE sales.january SET amount=amount+1000;

Statement-based replication: For statement-based replication (or mixed level), replicate_do_db refers to the default database, i.e. the database currently in use. If the database specified by replicate_do_db is updated under the default database, the slave database will not be updated accordingly, and there will be problems.

Resolution: For binlog_format=statement or mixed, only replicate_wild_do_table= sales is set in the slave library.% Or replicate_wild_ignore_table. Cross-repository update problems can be avoided at this point.

For binlog_format=statement or mixed, replicate_wild_do_table= quyou.% is only set in the slave library Or replicate_wild_ignore_table. Cross-repository update problems can be avoided at this point.

For binlog_format=statement or mixed, replicate_wild_do_table= quyou.% is only set in the slave library Or replicate_wild_ignore_table. Cross-repository update problems can be avoided at this point.

Row-based replication: no problem

2. InnoDB single column index length cannot exceed 767bytes limit

In fact, there is another limit to the union index, which is 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.

Solution:

MySQL 5.5.14 and later resolved the issue of maximum index length of 767 bytes by introducing the innodb_large_prefix configuration keyword. This keyword must be paired with innodb_file_format and innodb_file_per_table

innodb_large_prefix = True

innodb_file_format = Barracuda

innodb_file_per_table = True

Barracuda adds Dynamic and Compressed line formats to the original (Antelope).

"Mysql related technology example analysis" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.