MySQL Master Slave

MySQL server replication

 

Reasons for master-slave replication using MySQL.

 

Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

 

Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

 

 

For this setup we will connect the mirroring between the server through a cross over cable.

Using basically a unroute-able IP address.

 

 

Hardware Setup and user information

 

Master server ip: 10.10.10.1
Slave server ip: 10.10.10.2
Slave username: repl
Slave pw: slavepass
Your data directory is: /var/lib/mysql/

 

INSTALL MYSQL FROM RPM On All Servers

First lets get the latest RPM from MySQL and install it and verify versions

cd /

cd /usr/src

mkdir mysqlrpm

cd mysqlrpm

 

FOR: 5.1.48

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-5.1.48-1.glibc23.x86_64.rpm/from/http://mirror.services.wisc.edu/mysql/

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-client-5.1.48-1.glibc23.x86_64.rpm/from/http://mirror.services.wisc.edu/mysql/

 

FOR: 5.1.49

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-client-5.1.49-1.glibc23.x86_64.rpm/from/http://mirror.services.wisc.edu/mysql/

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-5.1.49-1.glibc23.x86_64.rpm/from/http://mysql.llarian.net/

 

 

FOR: 5.1.48

rpm -i MySQL-server-5.1.48-1.glibc23.x86_64.rpm

 

 

Setting Up Master Server:

OK.. Time to modify the ini or cnf file

# service mysql stop

lets find the ini or cnf

#find / -name 'my.ini'

#find / -name 'my.cnf'

 

if you don't find the file we will make our own.

cd /etc

nano my.cnf

 

if you found the file type:

nano [full path to you file]

 

paste the following into your master:

 

[mysqld]
skip-name-resolve
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_connections=100000
max_connect_errors=1844674407370954751
key_buffer = 2048M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 512M
read_buffer_size = 8M                                                    $
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 32M
thread_concurrency = 16
long_query_time = 5
innodb_file_per_table

 

 

if your still in nano press ctrl-o, then click enter.  that should save it.

ctrl-x, to exit

 

#service mysql restart

 

mysqladmin variables | grep server_id

 

 

Setting up the Slave Server

It is exactly the same as above except for the information in the my.cnf file is as follows

 

 

[mysqld]
server-id=2

skip-name-resolve
slave_net_timeout=30 connect_timeout=20 max_connect_errors=1844674407370954751

 

 

#mysqladmin variables | grep server_id

If you have more then 1 slave repeat the above and increment the server-id as you see fit.

 

 

 

Master Server: Creating Salve users.

you can use 1 user for all your slaves or how ever you see fit.

I'm going to use just one

 

Get back on the master box

we will create a new MySQL user with replication permissions.

mysql> CREATE USER 'repl'@'10.10.10.2' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.10.10.2';

 

 

 

If you Have a really large Database and don't want to wait for replication

(lets say over a few 100 megs )

Do a dump of data to move to slave

Login to master server:

mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql

copy file to you slave.

 

On slave:

import dump on slave

mysql < masterdump.sql

 

 

 

Setup Slave to talk to Master:

Let us tell the slave which master to connect to and what login/password to use:

Get on your slave box(es)

 

mysql> CHANGE MASTER TO MASTER_HOST='10.10.10.1', MASTER_USER='repl', MASTER_PASSWORD='slavepass';

 

Let us start the slave:

mysql> start slave;

You can check the status of the slave by typing

mysql> show slave status\G;

if you have a big database and didn't dump your seconds behind master will take a few to get back to zero.

Also you may wanna note the top of the list..

Should say waiting for events from master.. If it is stuck on Connection you need to trouble shoot.

See below... a few sections below

 

 

 

 

 

 

Use When re-syncing Databases and you need the file and binary location

On Master:

mysql> FLUSH TABLES WITH READ LOCK;

mysql>SHOW MASTER STATUS;

Do not close this session or exit mysql otherwise you will lose the lock.

After you update your salve then you can exit;

 

On Slave(s) using logifle and pos from above.

 

mysql>

CHANGE MASTER TO MASTER_HOST='10.10.10.1',MASTER_USER='repl',MASTER_PASSWORD='slavepass',

MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1710;

 

 

 

You can also do this to append everything new and wipe out any errors

 

Step 1. On the Slave
Issue the following commands to mysql:
mysql>STOP SLAVE; # stop the Slave I/O threads
mysql>RESET SLAVE; # forget about all the relay log files
services mysql stop     # stop the database

Step 2. On the Master
Issue the following commands to mysql:
mysql> RESET MASTER; # reset the bin log counter and wipe out bin log files
mysql> FLUSH TABLES WITH READ LOCK; # flush buffers and LOCK tables (both MyISAM and InnoDB)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

 

Dump Master... import to slave

 

then this

 

mysql>

CHANGE MASTER TO MASTER_HOST='10.10.10.1',MASTER_USER='repl',MASTER_PASSWORD='slavepass',

MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=98;

 

 

 

Useful things:

Get MySQL version.

• mysql
• select version();

 

 

Stop and start mysql

• service mysql stop
• service mysql start

(note its not mysqld for 5.1x)

 

 

 

Trouble Shooting Connection:

Check connection between servers:

on server 1: type

# telenet 10.10.10.2 3306

on server 2: type

# telenet 10.10.10.1 3306

 

You should basically get a message say MySQL won't allow you.( this is good)

if you get can't connect.. check your IP tables and ports an firewalls

 

if you are good connection wise.

Make sure you used the right IP address when you created your users and granted permissions.

Check your passwords, etc..

 

Also on the salve(s) try

mysql> show slave status;

without the \G to see a little more detail on the failure.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When the replication gets interrupted (either via a clean server shutdown or a forced one), replication refuses to restart citing the following error:

ERROR 1201: Could not initialize master info structure, more error messages can be found in the MySQL error log

 

The master.info file is present in the data directory and has all the required information in it but MySQL refuses to use it. Searching on the net did not yield anything definitive.

The simplest way to restart replication from this point is to re-enter the master.info data manually. Do the following:

1. STOP SLAVE
2. RESET SLAVE
3. CHANGE MASTER TO MASTER_HOST=’your_master’, MASTER_USER=’your_login’, MASTER_PASSWORD=’your_password’, MASTER_LOG_FILE=’log_file’, MASTER_LOG_POS=log_position
4. START SLAVE

At this point you want to execute ‘SHOW SLAVE STATUS’ and check to see if everything is running. Most times MySQL will stop since it tried to replay a duplicate record from the logs. We need to tell MySQL to skip that record and continue:

1. SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1
2. START SLAVE

That should get replication up and running again.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

References:

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

 

16.1.1.4. Obtaining the Replication Master Binary Log Coordinates

the below is use to start replication from a binary point

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