Now owned by Sun

Replication

• http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

NB: data sent over 3306 is plain text?!?!?!

• http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

• http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/

• http://wiki.joyent.com/facebook:kb:mysql-tunnel

• http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

Steps

setup

1. Setup remote access user on master for the slave
2. Setup binary logs and master configuration
3. Obtain master replication information
4. Create a data snapshot
5. Set the master configuration on the slave
6. Start the slave threads

for backups

1. Stop the slave
2. Dump the data
3. Start the slave

useful commands

• PURGE MASTER LOGS
• RESET MASTER
• SET SQL_LOG_BIN
• SHOW BINARY LOGS
• SHOW MASTER STATUS
• SHOW SLAVE HOSTS
• LOAD DATA FROM MASTER;
• MASTER_POS_WAIT()
• RESET SLAVE
• SET GLOBAL SQL_SLAVE_SKIP_COUNTER
• SHOW SLAVE STATUS \G
• START SLAVE
• STOP SLAVE
• 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;
• FLUSH TABLES WITH READ LOCK;
• SHOW MASTER STATUS;

• In another session: mysqldump --all-databases --lock-all-tables >dbdump.db

• UNLOCK TABLES;

To kill the slave run STOP SLAVE, shut down the server, delete the master.info and relay-log.info files, and restart the server with its original options.

multiple servers

• http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html

• http://www.linux.com/articles/52390

useful settings

Choose what databases to log on the server

• binlog-do-db=db1

Choose what databases/tables/data to replicate on the client

• replicate-do-db
• replicate-do-table
• replicate-wild-do-table

Sample code for MySQL C API

#include <stdio.h>
#include <mysql/mysql.h>

int main()
{
    MYSQL connection;
    MYSQL_RES* result;
    MYSQL_ROW row;
    unsigned int num_fields;
    unsigned int i;

    mysql_init(&connection);
    mysql_real_connect(&connection,
                       "localhost",
                       "david",
                       "mypassword",
                       "test",
                       0,
                       0,
                       0);

    mysql_query(&connection, "select * from sampledata");

    result = mysql_store_result(&connection);

    num_fields = mysql_num_fields(result);

    row = mysql_fetch_row(result);
    while (row)
    {
        for (i = 0; i < num_fields; i++)
        {
            printf("%s ", row[i]);
        }
        printf("\n");

        row = mysql_fetch_row(result);
    }

    mysql_free_result(result);

    return 0;
}

gcc -o db db.c -L /usr/lib/mysql -lmysqlclient

Linux | Software | PostgreSQL | DatabaseLinks | CategoryDatabases