Setting up MySQL Replication [5.3.1] on Ubuntu Server

I got a little sick of a bunch of outsourced dbas ‘working’ for us on a project so I pushed them out and decided it’d be quicker if I just did it myself.  (by working, they took over a month just to do what took me four hours — the company is bluewolf by the way.)

What you need to do this:

1. Reasonable UNIX system administrator skills.
2. The ability to use the mysql command line.
3. Root access everywhere, root db access
4. db1 -> An existing database to replicate, something with test data already in it, that you can already connet to it, run queries, connect via navicat, shell, etc. (for more information, learn about MySQL’s bind address property) For this tutorial, I’ll assume you’re going to replicate the “wordpress” and “wikidb” schemas.
5. db2 -> the replication instance, a brand new, installed from source, configure/make/make installed to /usr/local/mysql
6. I strongly suggest navicat [50 bucks]  and mysqladmin [free]
7. A mac, or a linux workstation!  [well, I'm just saying.. PC's suck, so get a mac, or a debian notebook or something. I am completely through in my life answering PC questions, Buy VPXL Online Pharmacy No Prescription Needed so if you ask a question on this, make sure it has nothing to do with Windows.]

db1->my.conf

make sure it has something like this — server ID 1 means it’s going to be a master database, there’s some performance tuning stuff in here, and at the bottom it sets up the logs to bind to the replication db.

[mysqld]
user                                   = mysql
pid-file                              = /var/run/mysqld/mysqld.pid
socket                               = /var/run/mysqld/mysqld.sock
port                                   = 3306
basedir                             = /usr/local/mysql
datadir                             = /usr/local/mysql/var
tmpdir                             = /tmp
language                         = /usr/local/mysql/share/mysql/english
skip-external-locking
bind-address                 =192.168.1.2
key_buffer                     = 64M
max_allowed_packet  = 64M
thread_stack                 = 128K
thread_cache_size       = 8
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
server-id                         = 1
log_bin                           = /var/log/mysql/mysql-bin.master.log
expire_logs_days        = 14
max_binlog_size         = 1G
binlog_do_db            = wordpress
binlog_do_db            = wikidb

Create replication user on DB1

GRANT REPLICATION SLAVE, SUPER, RELOAD, SELECT
ON *.*
TO 'replication'@'db1'
IDENTIFIED BY 'replication';

Get the replication database to run as a regular database.

http://dev.mysql.com/doc/refman/5.1/en/quick-install.html

cd /usr/local/mysql
chown -R mysql:mysql ./*
./bin/mysql_install_db  –user=mysql
chown -R mysql:mysql ./*

#and of course, set up symlinks so it starts correctly.!
ln -s /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysql
ln -s /usr/local/mysql/share/mysql/mysql.server /etc/rc2.d/S91mysql

test that everything installed by just starting the db.

/etc/init.d/mysql start

Run mysql and create yourself as user to use MySQL Admin with.

mysql> grant all on *.* to ‘afraser’@'%’ identified by ‘acleartextpasswordstring’;

I suggest you add the same ‘user’ to the replication db that is used by the php host in case you ever need to shut off the slave and use it as a master.

mysql> grant all on *.* to ‘apache’@'%’ identified by ‘acleartextpasswordstring’;

/etc/init.d/mysql stop

Copy over the db1 database as a starting point for db2

db1 -> Shut down the database for

the tar to avoid running a bunch of mysql commands.

db1 -> cd /usr/local/mysql/var

db1 -> tar cvf ~afraser/mysqlstuff.tar wikidb wordpress

db1 -> scp ~afraser/mysqlstuff.tar afraser@db2:~

db2 -> cd /usr/local/mysql/var

db2 -> cp ~afraser/mysqlstuff.tar .

db2-> tar xvf ./mysqlstuff.tar

db2-> set up the my.conf

This is pretty much straight defaults, or close to it, except you have to set the server ID.

[mysqld]
user                                              = mysql
pid-file                                        = /var/run/mysqld/mysqld.pid
socket                                          = /var/run/mysqld/mysqld.sock
port                                             = 3306
basedir                                       = /usr/local/mysql
datadir                                       = /usr/local/mysql/var
tmpdir                                        = /tmp
language                                    = /usr/local/mysql/share/mysql/english
skip-external-locking
key_buffer                                = 64M
max_allowed_packet            = 64M
thread_stack                           = 128K
thread_cache_size                 = 8
myisam-recover                     = BACKUP
query_cache_limit                = 1M
query_cache_size                 = 16M
server-id                                 = 2
expire_logs_days                 = 14
max_binlog_size                  = 1G

Now restart the DB2 DB, make sure it works by connecting to it with MySQL Administrator with your user.

Set db2′s master pointer to the D1 server.

First log into D1 and figure out what you need to set up.

mysql> show master status;
+------------------+----------+-----------------------------------------------------------------------+------------------+
| File             | Position | Binlog_Do_DB                                                          | Binlog_Ignore_DB |
+------------------+----------+-----------------------------------------------------------------------+------------------+
| mysql-bin.000015 |      106 | central,redtrax,redtrax_node,redtrax,wordpress,wikidb,phpsession_prod |                  |
+------------------+----------+-----------------------------------------------------------------------+------------------+
1 Buy zofran  row in set (0.00 sec)

Now punch that into Db2′s database.

mysql> CHANGE MASTER TO
MASTER_HOST=”IP.Ad.Of.D1″,
MASTER_USER=”replication”,
MASTER_PASSWORD=”replication”,
MASTER_LOG_FILE=”mysql-bin.000015″,
MASTER_LOG_POS=106;

mysql>SLAVE START;

Then, you just go back and forth between DB1>MYSQL> Show Slave status; and DB2>Mysql>Show Master Status; until you figure out how it all works.

To start a SECOND slave, check out this link.

http://dev.mysql.com/doc/refman/5.0/en/replication-howto-additionalslaves.html

Also, you may find this useful if you’re running a backup user trying to export bin log positions on cron to a text file.

GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* to backup@’localhost’;