Jump to content
Sign in to follow this  
hijinks

mysql replication

Recommended Posts

So I did this once before but then my webserver sort of died.. ok I just left it at my old office cause it was on a p2 and I didn't want to move it, so i'll redo this.

 

This little tutorial thinks you have a basic mysql setup and have never tried to setup replication. If you have you generally have to remove all the binary logs and the settings in the my.cnf

 

Master Server

 

So these are the settings on the master server you need. For me I edit the /etc/my.cnf

 

server-id = 1
log-bin=/var/lib/mysql/mysql_binary_log

 

Now restart the mysql server

 

Now log into the mysql client and you need to setup a replication user.

 

GRANT REPLICATION SLAVE ON *.* TO slaveuser@'10.10.10.10' IDENTIFIED BY 'slavePass';

 

When creating users I always like to use ip's you can use hostnames and wildcards and such.. for more information on that visit the mysql doc page for the new user section.

 

Ok now the next step requires you to stop your mysql server for a bit cause you want to make sure the db is locked when you do a dump and no hidden data gets inserted or such.

 

FLUSH TABLES WITH READ LOCK;

 

Now that its locked you can run the following command

 

SHOW MASTER STATUS;

 

You will get something like this

 

+-------------------------+----------+--------------+------------------+
| File					| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| mysql_binary_log.000004 |	   79 |			  |				  |
+-------------------------+----------+--------------+------------------+

 

You want to make sure you note the file and the Position.. you will need these values for the slave.

 

Ok now exit out of the mysql client and you need to get a dump of all your databases.

 

mysqldump -u root -p --extended-insert --all-databases > /tmp/backup.sql

 

now move the /tmp/backup.sql file to the slave server

 

You can now unlock the db's now

 

unlock tables

 

 

Slave Server

 

Now on to the slave server. So this takes into account that it is a new install or such of mysql and you have no tables.. so lets import the backup from the master server

 

mysql < backup.sql

 

You want to add -u root -p if you setup a root password already on your slave

 

Now we want to add the following into /etc/my.cnf. The server id muse be unique

 

server-id=2
log-slave-updates 
log-warnings

 

now restart the mysql server

 

Now login to the mysql client and issue the following query

 

CHANGE MASTER TO MASTER_HOST='<master host name>',
MASTER_USER='slaveUser',
MASTER_PASSWORD='slavePass',
MASTER_LOG_FILE='mysql_binary_log.000004',
MASTER_LOG_POS=79;

 

So you can see the last 2 lines are where i told you to note the filename and position and the others are from the grant.. now if everything worked you should be able to issue the following command in the mysql client

 

start slave

 

and replication should be working.. you can test this by creating a new DB on the master and it should appear on the slave.. if it doesn't work remember to check those logs

  • Like 1

Share this post


Link to post
Share on other sites
I am starting a wiki for my own use on tips/tricks/howto's I have done in the past. I am basically taking all my notes I have and putting the good ones online for others.

 

The first major one I have done is mysql replication. This is a kickass feature in mysql that a lot of people use oracle just for. It basically allows you to keep multiple backups in sync with a master server as soon as any changes are done on the server they are on the slave servers. I have decided to do a howto since its an area not very covered i've found.

 

Plus i've needed to do it since it looks like out database server is about to die.. both raid strips look like they aren't gonna last very long.

 

here is the link

 

http://www.zcentric.com/wiki/tiki-index.ph...ase+replication

 

The Url you provided does not work anymore.

Share this post


Link to post
Share on other sites

:blink: OMG .. its a tutorial!

 

nice :)

Share this post


Link to post
Share on other sites
Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...