Linux-Noob Forums

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

Quote: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.

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

o_O OMG .. its a tutorial!

nice :)

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

Thanks

Ashish

hijinks

[M]ax

hijinks

znx

Ashish