MySQL

From BarikWiki
Jump to: navigation, search

MySQL 5.1 Reference Manual

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.

SUPER

The SUPER privilege is needed for triggers.

InnoDB

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.


One-Way Replication

Chapter 16 of the MySQL documentation covers the topic of replication, as well as some common commands for checking the replication status, and creating a data snapshot with mysqldump.

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:

RESET MASTER

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 mysqldump -

Multi-Master Replication

This is also called two-way replication or master-master replication.

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

Locking

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
ALL REPORT

Bug #52832: Random lock up - Lock wait timeout exceeded - NDBCluster

If it's disk related, try smartctl.

Preventing SQL Injection Attacks