Bottomless, Endless

In this article the Master is 200.126.187.013 and the Slave is 200.126.187.066

On the Master, edit /etc/mysql/my.cnf:

server-id        = 126187013  # choose a unique integer
log_bin          = /var/log/mysql/mysql-bin.log
bind-address   = 0.0.0.0  # listen on all interfaces (by default 127.0.0.1 is used)
binlog_do_db   = mydb    # replicate only the 'mydb' database

Test your new configuration for syntax error:

bash# mysqld --help

Make sure the skip-networking option is not enabled.
Make sure server-id is unique. You can for instance use the last 3 octets of the IP address of the server.

Create a replication user on the Master using the IP address of the Slave that will access it (200.126.187.66):

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.37-1ubuntu5.1 |
+-------------------+
1 row in set (0.00 sec)
mysql> CREATE USER 'replication'@'200.126.187.66' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON mydb.users TO 'replication'@'200.126.187.66';
mysql> GRANT SELECT ON mydb.users TO replication;

Restart MySQL for the changes to take effect:

bash# /etc/init.d/mysql restart

On the Slave, edit /etc/mysql/my.cnf to set the server-id:

server-id  = 126187066    # choose a unique integer

Copy the current database on the Master with mysqldump and load it on the Slave.

Check the current status of the Master by retrieving its log file name and position:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |    23577 | mydb         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>

Start the replication on the Slave.

mysql> CHANGE MASTER TO MASTER_HOST='200.126.187.013', MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=0;
mysql> START SLAVE;

We did take some shortcuts here: the proper way would have been to:
1/ prevent the Master from committing anything
2/ backup the DB on the Master
3/ check the log file name and log position on the Master (‘SHOW MASTER STATUS’)
4/ load the backup on the Slave
5/ start replication using the log file name and position on the Master
6/ unlock the Master so the commits can happen again

A nightmare if the Master is already a live production server because it means from step 1/ to 6/ the Master is dead not committing anything.

As we did a ‘dirty’ backup, the replication will bump into errors that will stop the Slave (mainly because of entries already on the Slave, so we can safely skip them).
List them on the Slave with ’show slave status \G’ (look at the ‘Last_Error’) and if you can skip it, do it this way:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

Continue skipping the failed insert (if they can be skipped of course) until the slave completely catches up with the Master.

How do I know when the Slave is done catching up with the Master?

Issue a ‘SHOW SLAVE STATUS’ on the Slave:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 200.126.187.013
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 339528
             Relay_Log_File: prod1-relay-bin.000004
              Relay_Log_Pos: 339665
      Relay_Master_Log_File: mysql-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 339528
            Relay_Log_Space: 339665
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql>

You can see everything is fine because:
- ‘Seconds Behind Master’ reports 0, so the Slave is done catching up (it’s in sync with the Master)
- Both Slave IO and Slave SQL are running
- The Slave is just waiting for new data to come in: ‘Waiting for master to send event’

Source

Is it faster to issue two SQL statements (DELETE then INSERT) or craft a single query using ON DUPLICATE KEY UPDATE?

Single query:

INSERT INTO items SET user_id=3463574, item_id=1, amount = 1 ON DUPLICATE KEY UPDATE amount = amount + 1;

Multiple queries:

DELETE FROM items WHERE user_id=3463574 AND item_id=1;
INSERT INTO items SET user_id=3463574 , item_id=1, amount = 66;

Difference in time (using microtime() in PHP):
ON DUP – 0.00023600000000001
ON DUP – 0.00022699999999998
ON DUP – 0.00014599999999998
ON DUP – 0.00014899999999998
DEL AND INSERT – 0.000336
ON DUP – 0.00015000000000004
DEL AND INSERT – 0.00046600000000002

The del/insert is 3 times slower than the ON DUPLICATE statement.