- If you are planning on using JDBC, you need the MySQL Connector for Java] (Connector/J).
- For Python, use MySQL for Python. You can get some binaries for Windows at this third-party site, which is pretty crazy. After installation, you can use
import MySQLdbto make it available. Yay!
- For Python, don't forget to check the MySQLdb User's Guide.
Getting weird lock issues when using
mysqldump with MySQL Cluster.
On Windows, the default location of the data files is:
C:\Documents and Settings\All Users\Application Data \MySQL\MySQL Server 5.1\Data
Don't forget about expire_logs_days, otherwise your binlog will grow forever.
The SUPER privilege is needed for triggers.
In MySQL Administrator, the row count for InnoDB can appear to be completely bogus. This is actually explained under Restrictions on InnoDB Tables. Specifically:
- SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.
- InnoDB does not keep an internal count of rows in a table.
Setup the user on the master:
GRANT REPLICATION SLAVE ON *.* TO slave_user IDENTIFIED BY 'slave_password'
Confirm that the grant has been completed using SHOW GRANTS:
SHOW GRANTS [FOR user]
Checking the status of replication. For the slave one can type:
SHOW SLAVE STATUS\G
or for the master (SHOW MASTER STATUS).
Using \G instead of ; enables vertical output; this can be handy when data does not fit on a single terminal line.
If you want to delete all binary logs from the system:
Perhaps the most useful command for getting a slave back up and running is CHANGE MASTER TO:
STOP SLAVE; RESET SLAVE; CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
Multi-Master Replication Step by Step
Since the MySQL documentation appears to be completely idiotic when actually needing to accomplish this task, here is the step by step guide.
1. Dump your database with
This is also called two-way replication or master-master replication.
- MySQL Multi-Master and Circular Replication. Look at this later; it involves MySQL Clustering.
- Advanced MySQL Replication Techniques
- MySQL Master Master Replication. A decent HowToForce tutorial with some minor annoyances. Seems to ignore the important step of doing CHANGE MASTER, which is addressed here.
- Some notes on the MySQL Binary Log.
Auto-increment numbers for Master 1 (odd numbers):
auto_increment_increment = 2 auto_increment_offset = 1
Auto-increment numbers for Master 2 (even numbers):
auto_increment_increment = 2 auto_increment_offset = 2
I have a non-reproducible MySQL locking issue. When using SHOW PROCESSLIST, the State of the PID processes change to Locked. These are simple delete and update queries (no joins) on memory tables.
LockPagesInMainMemory = 1
If it's disk related, try smartctl.