I have recently setup a master and slave in MySQL.

the main reason I did this is so I could do some queries and light data mining looking for patterns in our datasets. The issue is that the Live database is optimized for inserts and updates, not for views or summaries.

in creating a replicated database, I was able to index my slave differently, and allow it to be better suited for pulling this report views.

I am NO MySQL database expert, but was able to follow the simple directions on the mysql.com website and have this up and running relatively quickly and painlessly.

This is how I did it:

  • I configured my master by adding the following to my.cnf and restarting the mysql instance:
  • server-id=1276024641
    log-bin=mysql-bin
    log-error=mysql-bin.err
    binlog_do_db=<my_target_db_name>
  • the db_name restricts only 1 db to be replicated. I think omitting this line will replicate all the databases on the master to the slave
  • I added a unique, random number as my server-id to my slave machine’s my.cnf file. the line I added looked like this : server-id=1281421047
  • I then restarted the slave mysql instance
  • I then setup the replication user on the master. I used the phpMyAdmin scripts, by clicking on the “Replication” tab, then the “add slave replication user” link. I saved this info
  • because I am running a live database, I needed to locate the master’s bin log coordinates. To do this, you need to ensure no one is changing the data, the manual recommends locking the tables, but on our transaction heavy system, I just closed the db for the next step.
  • I then proceeeded to output a mysqldump: mysqldump -p my_database_name > db_script.sql
  • while this was saving to file, I went into phpmyadmin under replication and clicked “show master status”, which displayed
  • Variable     Value
    File                mysql-bin.000120
    Position       83336594
    Binlog_Do_DB    my_databaswe_name
    Binlog_Ignore_DB
  • Once the data dump was complete, I re-enabled traffic back into the master db.
  • I then copied the .sql file to the slave machine and imported it using: mysql -p my_database_name < db_script.sql
  • Once the db snapshot was imported, I needed to tell the slave to start running the slave process thread, by running the query:
  • CHANGE MASTER TO
    MASTER_HOST=’master_host_name’,
    MASTER_USER=’replication_user_name’,
    MASTER_PASSWORD=’replication_password’,
    MASTER_LOG_FILE=’recorded_log_file_name’,
    MASTER_LOG_POS=recorded_log_position;
  • from there, the slave should keep in sync with the master

Man I don’t know how many times I’ve had to do this. I get a list , I want to clean dupes before I import, or after.

and I’d like to do this with a query ( it’s gotta be possible right ? )

so I hunt I test, i break stuff… it sucks, I undo.. I redo.. I blah and I blah.. then it’s done..

waste of time.. so here it is.

First make sure I find what I am looking for :

SELECT *
FROM email_db, email_db AS vtable
WHERE vtable.id > email_db.id
AND vtable.email = email_db.email

very simple and elegant right ?

This is the one I actually ended up using.. to remove the dupes

  1. CREATE TABLE temp_table AS SELECT * FROM foo WHERE 1 GROUP BY bar;
  2. DROP TABLE foo;
  3. RENAME TABLE temp_table TO foo;

Everytime we launch a new application in a social network, after a few weeks it’s important to know if we have upward growth.

The only thing I can think that demonstrates this is more installs per day. In our databases we capture full timestamps of the new installed user.

To turn this into something I can look at and understand, I have to run this query.

SELECT date_format( date_added, ‘%Y-%m-%d’ ) , count( * )
FROM fb_user
GROUP BY date_format( date_added, ‘%Y-%m-%d’ )
ORDER BY date_format( date_added, ‘%Y-%m-%d’ ) ASC

I am going to try to make a graph to view this in flex… first time Flex graphing 🙂 I’ll post an update when it works..

Ok this was extremely annoying but figure it out (again ) this time I am documenting what I did, to help me ( cause I know I’ll forget how I did it ) and for anyone else having the same issue.

Trying to insert a trigger through the phpmyadmin web interface.

  1. Ensure your SQL on it’s own is correct
  2. Ensure your MySQL trigger syntax is correct
  3. change the delimiter ( in the text field) to something like % ( see diagram )
  4. put the new delimiter at the end of the entire statement
  5. Pray for mercy !

Here is a diagram to really simplify it.

So nice to see that success message

So nice to see that success message