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]

Group By, Count - 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)  

Group By, Count - counting groups of field values including a join.

$ mysql

> use user

  Database changed

> SELECT user.countryid, COUNT(*) AS repetitions, country.countryname
  FROM user
  JOIN country ON user.countryid = country.countryid
  GROUP BY user.countryid order by repetitions DESC;

  +-----------+-------------+---------------+
  | countryid | repetitions | countryname   |
  +-----------+-------------+---------------+
  |       237 |      710721 | United States | 
  |        40 |       58652 | Canada        | 
          ...           ...   ...
  |        96 |          10 | Guinea-Bissau | 
  |       211 |           9 | Sudan         | 
  +-----------+-------------+---------------+ 

Check for the presence of a foreign key/constraint, stored procedures.

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

> SHOW PROCEDURE STATUS;

  +-----------------+-------------------------+-----------+---------------+----------+---------+---------------+---------+
  | Db              | Name                    | Type      | Definer       | Modified | Created | Security_type | Comment |
  +-----------------+-------------------------+-----------+---------------+----------+---------+---------------+---------+
  | test_10apr_v33  | CopyComponent           | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER       |         | 
  | test_10apr_v33  | GetComponentLineageInfo | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER       |         | 
    ...               ...                       ...         ...             ...        ...       ...             ...
  | studiowebui_v30 | GetComponentLineageInfo | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER       |         | 
  | studiowebui_v30 | MoveComponent           | PROCEDURE | foo@localhost | 2010-... | 2010-.. | INVOKER       |         | 
  +-----------------+-------------------------+-----------+---------------+----------+---------+---------------+---------+ 

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; 

Conditionals and Virtual Fields

Users have 4 different attributes kept in a sister table, list users with 1st attribute and 'yes'/'no' for the other three attributes.

> select user.email,
  if (user.email in (
    select user.email from user
    join user_attrItem as ua1 on
    user.userid=ua1.userid and ua1.attrItemid=275),
    'yes', 'no') as news,
  if (user.email in (
    select user.email from user
    join user_attrItem as ua2 on
    user.userid=ua2.userid and ua2.attrItemid=276),
    'yes', 'no') as business,
  if (user.email in (
    select user.email from user
    join user_attrItem as ua3 on
    user.userid=ua3.userid and ua3.attrItemid=277),
    'yes', 'no') as tech
  from user
  join user_attrItem as ua on user.userid=ua.userid and ua.attrItemid=1230
  ORDER BY email

  +----------------------+------+----------+------+
  | email                | news | business | tech |
  +----------------------+------+----------+------+
  | 1973marcos@nails.com | yes  | yes      | yes  | 
  | 353034836@jj.com     | no   | no       | no   | 
    ...                    ...    ...        ...
  | zhyizm@621.com       | yes  | yes      | yes  | 
  | zm.mars@361.com      | yes  | yes      | yes  | 
  +----------------------+------+----------+------+ 

Derived Tables

The following nifty queries assume a single table called bounce containing at least these fields: email, datetime.  Note the derived table must be given a name (e.g. grr) even though it's not used.

SELECT COUNT(*) AS qty, repetitions FROM (
  SELECT COUNT(*) AS repetitions, email FROM bounce
  GROUP BY email HAVING repetitions > 1) as grr
GROUP BY repetitions

SELECT COUNT(*) AS repetitions, email FROM bounce
GROUP BY email HAVING repetitions > 1

SELECT DATE_FORMAT(datetime, '%Y-%m-%d') ymd, COUNT(*)
FROM bounce GROUP BY ymd 

Update using a Join

UPDATE  `x_property_codes_synxis`
LEFT JOIN `x_property`
ON `x_property_codes_synxis`.`PROPERTY_ID` = `x_property`.`ID`
SET  `EMAIL_HOURS_BEFORE_ARRIVAL` =  '168'
WHERE `x_property`.`CHAIN_ID` = 6 

tablet | printable