MySQL Replication Skip "Duplicate Error"
This simple script will allow you to skip MySQL duplicate errors.
Showing posts with the label mysqlShow all
MySQL with HAProxy for Failover and Load Balancing
As discussed in a previous blog post about different types of MySQL HA architectures , using HAProxy for failing over and load balancing over cluster…
A Comparison of MySQL HA Architectures
I was recently asked to design a new MySQL HA architecture for an internal project which currently runs on a Master-Slave. The acceptable criteria we…
Joining MySQL tables from different schemas
Today I ran into a situation whereby a client needed to join 2 tables which happened to be in different schemas but on the same host. One of the sche…
MySQL dumping triggers and procedures
To dump MySQL triggers and stored procedures using mysqldump, you have two options: 1) Dump the mysql schema (which may not always be feasible) O…
Mysqldump - dump insert statements only
If you are only interested in generating the insert statements (i.e. exclude create/drop table, comments, locks, and stuff like that) you may want to…
MySQL Error while dropping tables: Cannot delete or update a parent row: a foreign key constraint fails
If you know what you are doing and you still want to drop a table containing foreign constraints, then you need to follow this procedure: SET FOREI…
Perl DBI - Connecting Perl to MySQL database
Knowing how to connect Perl with a database is imperative and very useful. Luckily there is a Perl module called DBI which makes our things easier. I…
Introduction to pt-table-sync and pt-table-checksum
In this post I would like to introduce you to a couple of must-have tools from Percona which enable you to validate the table contents between two My…
Display multiple variables in mysql
Sometimes you might need to gather a handful of variables in one statement. This can be done by using the following syntax: james@ebony ~ $ mysql …
Drop/Truncate tables quickly in MySQL
Today I learned a nifty trick from the PalominoDB MySQL geeks to improve the delete or truncate time in MySQL. In both delete and truncate the table …
MySQL Enable Slow Query Logging
In MySQL, slow queries can be logged in the 'slow query log'. To enable this feature you need to set the following variable: log_slow_queri…
Connect Perl to MySQL
If you are a MySQL DBA, one of the most useful things you need to know is Perl. This will make you able to create custom tools such as for example be…
Moving Master or Slave nodes in MySQL
In this post I want to discuss with you a concept which simplifies failing-over or migration of a master or slave node in a MySQL replication setup w…
How Tables are arranged in JOINS
I often get asked how does an optimizer (being in Oracle or MySQL) decide which would be the outer table in a join. Consider the following three tabl…
Efficient Random Rows in MySQL
MySQL's official documentation dictates the use of the ORDER BY RAND() (coupled with LIMIT if you want) to select one or more random rows from a …
Choosing the ideal InnoDB log file size
I refer to an incredible post by Baron Schwartz where he wittingly explains a technique to choose the best InnoDB log file size. The idea is so mon…
Delete Duplicates fast in MySQL
In an earlier article I explained how to delete duplicate rows in Oracle using the rowid pseudocolumn. In MySQL there is no pseudocolumn, and in ord…
How Covering Indexes work in MySQL
I believe that MySQL has many cool and advanced index types which provide the best performance strategies (if used properly) compared to other RDBMS&…
MySQL Index Prefix for better performance
In MySQL you can optimize a query running against a table by choosing the correct index. Furthermore MySQL allows you to index strings as well (text,…
