Please see MySQL Notes for basic details about what MySQL can do.

Replication

As of at least MySQL 4.0, there are mechanisms to allow a database on one server to be replicated to databases on other servers. Typically this is done to allow requests to be serviced more quickly by spreading them across different database servers instead of having all requests hit the same server (AKA Load Balancing).

Replication - Master / Slave

On Master, make sure it specifies a unique server-id.  The log-bin directive is critical, typically just cite a file for it in the default directory MySQL already stores its data.  Optional: cite bin-log-db directives to restrict replication to specifc databases.

diff -r1.1 /etc/my.cnf

  > ### Server
  > server-id = 3
  > binlog-do-db = test
  > log-bin =             /var/lib/mysql/bin 

Now, make sure the mysql:user table has an entry for each slave IP address with at least slave replication privledges enabled.

# service restart mysqld

  # mysql ...

  mysql> show master status;

  mysql> master reset;

  mysql> \q 

Now prepare a database snapshot to load on to slave servers.  The --master-data directive embeds special replication sync information as backup s created. The slave servers will use this to retroactively recognize updates that happened after the database snapshot but before the slave servers are activated.  For just 'test' database ...

# mysqldump -u root --single-transaction --master-data=1 test > test_dump.sql 

... or if prefer to replicate all databases ...

# mysqldump -u root --all-databases --single-transaction --master-data=1 > all_dump.sql

Now copy the database snapshot to your slave servers ...

# scp [snapshop].sql user@[slave-server]:.

  [ ############### ]

On Slave ...

# rcsdiff /etc/my.cnf 

  > ### slave
  > master-host = 192.168.2.21
  > ### master-port = 3306
  > master-user = root
  > master-password =
  > ### [ above can be supplied using
  > ###   mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.21',
  > ###          MASTER_USER='user',
  > ###          MASTER_PASSWORD='passwd'; ]
  > replicate-do-db = test
  > relay-log =           /var/lib/mysql/relay
  > relay-log-index =     /var/lib/mysql/relay.index
  > relay-log-info-file = /var/lib/mysql/relay.info
  > master-info-file =    /var/lib/mysql/master.info
  > log-bin =             /var/lib/mysql/bin

# mysql -u root [database if not specified in snapshot] < [snapshot].sql

# mysql -u root

mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.21', MASTER_USER='root';

mysql> show slave status;

mysql> start slave;

mysql> show slave status;

Slave Recovery, check /etc/my.cnf settings on Slave.

# mysql

mysql> stop slave;

mysql> reset slave;

mysql> reset master;

mysql> \q

#

On Master, prepare a fresh database snapshot ...

# mysqldump ... [ with special paramters, see above ]

# scp ... [ see above ]

On Slave ...

# mysql -u root [ ... ] < [snapshot].sql

# mysql -u root

mysql> start slave;

mysql> show slave status\G;

  Slave_IO_State: Waiting for master to send event
  ...

Handy Commands and Queries [edit]

Reporting duplicate field values in given table.

> SELECT COUNT(*) AS repetitions, uniqueid FROM user
  GROUP BY uniqueid HAVING repetitions > 1;

  +-------------+----------------------------------+
  | repetitions | uniqueid                         |
  +-------------+----------------------------------+
  |        1589 |                                  | 
  |           2 | 00888a840f6eefb91eec3612bd93e3c4 | 
  |           2 | ...                              | 
  |           2 | ff8a67aa3f5c162ec6e7a8b3a98dbffc | 
  +-------------+----------------------------------+
  334 rows in set (22.76 sec)  

Check for the presence of a foreign key/constraint.

> show create table user_emailcampaign;

 +--------------------+--------------+
 | Table              | Create Table |
 +--------------------+--------------+
 | user_emailcampaign | CREATE TABLE `user_emailcampaign` (
   ...
   CONSTRAINT `user_emailCampaign_userid`
   FOREIGN KEY (`userid`) REFERENCES `user` (`userid`)
   ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `user_emailCampaign_emailCampaignid`
   FOREIGN KEY (`emailid`) REFERENCES `emailcampaign` (`emailid`)
   ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 +--------------------+--------------+ 

Non-English text shows up as ??? or other silly characters.

MySQL by default handles requests and queries using very basic character text encoding.  If you are performing inserts and selects for records that contain non-English text, best to tell MySQL to use utf8 character encoding.  Otherwise, MySQL will insert and retrieve a crude approximation of the text, often reduced to streams of ? and/or other strange characters.

<PHP?

mysql_connect($dbhost, $username, $password) or die("mysql_connect() failed");
mysql_select_db($database) or die("mysql_select() failed");
mysql_query("SET CHARACTER SET utf8"); 
mysql_query("SET NAMES utf8");
$result = mysql_query($query) or die(mysql_error());

?> 

Sub-query, Union.

UNION allows the results of more than one select statement to be combined (each select should ask for the same fields).  MySQL behavior seems to default to eliminating identical records when UNION is used.  A sub-query allows SQL statements that use a list of values to be dynamic, making for recursive SQL statements.  Innermost queries are resolved first, then processing spins outward until entire statement is processed.  Sub-queries can be fun, but if not carefully crafted they can be tremendously inefficient.

select distinct user.email, user.uniqueid, user.userid from user
join user_attrItem as uaPermissions on user.userid=uaPermissions.userid
where user.active and
!user.is_disabled and
(user.expc_passed or !user.expc_checked) and
user.email_confirmed and
user.countryid IN (47) and
user.email NOT IN (
  SELECT distinct user.email FROM user
  JOIN user_team ON user_team.userid = user.userid
  JOIN teamRole ON user_team.teamRoleid=teamRole.teamRoleid AND teamRole.groupid=1500
    UNION
  SELECT user.email FROM user
  JOIN user_audience ON user_audience.userid = user.userid
  JOIN audience ON user_audience.audienceid = audience.audienceid
  WHERE audience.audienceid = 19)
ORDER BY email;