When you shifted the IP within your application, any DB Connections that were open at the moment were totally unaware of the move. A quick netstat | grep -i mysql
would reveal that on Master1.
You would be better off doing the cutover of the IP as follows:
- On Master1
FLUSH HOSTS;
service mysql stop
- Change the IP in the App
- On all web servers,
service httpd restart
In order to safeguard the app from having to edit it for the sake of assigning a new IP, try using a DB VIP instead.
For example:
- Master1 is 10.1.2.30
- Master2 is 10.1.2.40
- Use 10.1.2.70 as the DBVIP
Here is what you can setup
- On Master1, run
ip addr add 10.1.2.70/24 dev eth1
- Use
10.1.2.70
in your app
Then, when it is time to cutover, do the following
- On Master1
FLUSH HOSTS;
service mysql stop
ip addr del 10.1.2.70/24 dev eth1
- On Master2
ip addr add 10.1.2.70/24 dev eth1
SHOW SLAVE STATUS\G
and make sure all final SQL statements from Master1 executed
- On all web servers,
service httpd restart
That way, a cutover would not involve editing any part of the app. Please notice that I did not mention running STOP SLAVE
anywhere because this would allow any final SQL statements to flow over from Master1 to Master2 once mysql is stopped on Master1.
Using replicate-wild-do-table
=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:
The replicate-*
options are restrictive by their presence. The *-do-*
options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-*
configuration variables means "replicate everything."
In the simplest case, when there are no --replicate-*
options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html
That, I think, is the point you are needing. It holds true for all MySQL 5.x.
Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ...
, and then START SLAVE
, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.
If you started out with identical data sets, everything should behave exactly as you would expect it to.
What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."
When you use the --replicate-*
options, the document cited above also offers this tip:
it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options
You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.
Best Answer
I do not use phpMyAdmin so maybe i'm out but take a look on a potential autocommit option in the UI... Maybe your PMA's session isn't in autocommit (test with : SHOW VARIABLES LIKE "autocommit").
Other thing, do you have filtered replication ? (do-replicate, do-ignore...)
Max.
Best regards.