mysql replication

[hijinks]

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

 

Code:

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.

 

Code:

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.

 

Code:

FLUSH TABLES WITH READ LOCK;

 

Now that its locked you can run the following command

 

Code:

SHOW MASTER STATUS;

 

You will get something like this

 

Code:

+-------------------------+----------+--------------+------------------+
| 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.

 

Code:

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

 

Code:

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

 

Code:

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

 

Code:

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

 

now restart the mysql server

 

Now login to the mysql client and issue the following query

 

Code:

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

 

Code:

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

[[M]ax]

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

 

[/url][url=http://www.zcentric.com/wiki/tiki-index.ph...ase+replication]http://www.zcentric.com/wiki/tiki-index.ph...ase+replication

 

The Url you provided does not work anymore.

[hijinks]

ok i updated the post to remove the link and did a new howto

[znx]

o_O OMG .. its a tutorial!

 

nice :)

[Ashish]

Which is best replication strategy for cross geographical replication, it is master-master OR master-slave and WHY?

 

Thanks

Ashish