Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
[HOWTO] MySQL Replication
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
beandog
Bodhisattva
Bodhisattva


Joined: 04 May 2003
Posts: 2072
Location: /usa/utah

PostPosted: Fri Oct 22, 2004 9:11 pm    Post subject: [HOWTO] MySQL Replication Reply with quote

[HOWTO] MySQL Replication

About: It took me days to figure out how to replicate database in MySQL, and the official docs are somewhat sparse. Hopefully this will help -- it's geared towards any general MySQL installation.

Edit: This guide only works with MyISAM table types (the default for MySQL).

The Master Server

MySQL replicates the data between the two databases by using binary logs stored on the master server. The first step is to setup the server so it creates them.

Create a directory to store the binary logs

This creates the directories and sets the permissions so only the mysql daemon can read/write to the logs.
Code:
$ mkdir /var/log/mysql/binary
$ chown mysql: /var/log/mysql/binary
$ chmod 770 /var/log/mysql/binary


Edit the MySQL Configuration

This turns on the binary logging, tells it where to store the logs, and creates a server id.

Code:
$ vim /etc/mysql/my.cnf


Add under the [mysqld] section.
It doesn't matter what the value for server-id is, as long as it is different from the slave's (which you will set later).
Also make sure that "bind ip_address" and "skip-networking" are commented out, so that the slave user can log in remotely.

Code:
log-bin=/var/log/mysql/binary/mysql_binary_log
binlog-do-db=database_name
server-id=1


Once you're done, restart the mysql server for the changes to take effect, and for the logging to start.

Code:
/etc/init.d/mysql restart


If you were already using binary logging before this, you don't need to make any changes, except for adding a server-id. The slave can keep up with your current binary logs, so there's no reason to delete them or flush the logs.

Setup a slave account

This both creates a new account in mysql.user, and grants replication privileges only.

Code:
$ msyql
mysql> GRANT REPLICATION SLAVE ON database_name TO 'slave_user_name'@'%' IDENTIFIED BY 'slave_password';


Adding "@'%'" means that "slave_user_name" can connect from any host. Change yours to whatever host name options you want to use.

AFAIK, you don't need to run FLUSH PRIVILEGES; in mysql for it to recognize the new account.

Get a database dump

There are two ways to get the data from the master to the slave -- this howto only covers one: exporting the actual data to a file, and then restoring it on the slave. The other option is LOAD DATA FROM MASTER; which does pretty much the same thing.

Note that you will need to have two shells open at this point -- one mysql shell and one terminal shell. The mysql shell must stay open while you do the database dump to get a clean export.

Switch to the database you want to replicate. FLUSH TABLES only locks the database you're currently using.

Code:
mysql> USE database_name;
mysql> FLUSH TABLES WITH READ LOCK;


Once again, do NOT close your mysql session until you do a mysqldump in another shell (screen comes in handy). If you do, the tables will be unlocked.

Code:
mysql> SHOW MASTER STATUS;


Once you get the MASTER STATUS results, WRITE THEM DOWN because you'll need them in a minute. Should look something like this:

Code:
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_do_db | Binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| mysql_binary_log.025 | 796947   | database_name|                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


Get the db dump:

Code:
$ mysqldump --opt database_name > database_name.sql


and unlock the tables

Code:
mysql> UNLOCK TABLES;


You're done now with the master server.

The Slave Server

Import the mysqldump from the master server:

Code:
mysql database_name < database_name.sql


Edit the slave's MySQL configuration to setup replication:

Code:
$ vim /etc/mysql/my.cnf


Add under the [mysqld] section.
It doesn't matter what the value for server-id is, as long as it is different from the master's.

Code:
server-id=2
# master server settings
master-host=masterhost.com
master-user=slave_user_name
master-password=slave_password
master-connect-retry=60 # num of seconds, default is 60
replicate-do-db=database_name


Restart MySQL -- don't worry, it won't start slaving yet.

Code:
/etc/init.d/mysql restart


Tell the slave specifically where to start logging:

This is where you use the data from SHOW MASTER STATUS; from the master mysql database.

Code:
mysql> CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='slave_user_name', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql_binary_log.025', MASTER_LOG_POS=796947;


Note that you don't put quotes around the value for MASTER_LOG_POS or mysql will give you an error.

Now start slaving!

Code:
mysql> START SLAVE;


And that's it! You should be good to go. :)

MySQL Docs



Please lemme know about errors / fixes / suggestions whatever.

Steve
_________________
If it ain't broke, tweak it. dvds | blurays | blog | wiki


Last edited by beandog on Tue Nov 23, 2004 6:26 pm; edited 1 time in total
Back to top
View user's profile Send private message
oliver
Apprentice
Apprentice


Joined: 25 Jun 2003
Posts: 170

PostPosted: Fri Oct 22, 2004 9:21 pm    Post subject: Reply with quote

great! thanks. We just had a requirement to do this at work so this guide is extremely timely
Back to top
View user's profile Send private message
maztaim
n00b
n00b


Joined: 07 Dec 2002
Posts: 10
Location: Pittsburgh, PA, USA

PostPosted: Fri Oct 22, 2004 9:57 pm    Post subject: Cool. Reply with quote

I like it when it's simple. That's EXACTLY what I was looking for. Now to give it a try...
_________________
Taim
taim@coredump.org
Back to top
View user's profile Send private message
thrasher6670
Apprentice
Apprentice


Joined: 04 Aug 2003
Posts: 269
Location: London, Ontario

PostPosted: Sun Oct 31, 2004 12:54 am    Post subject: Reply with quote

Hey mind if I post this on http://gentoo-wiki.com ?
_________________
Gentoo-Portage.com - Where the portage meets the web
Gentoo Wiki
Back to top
View user's profile Send private message
beandog
Bodhisattva
Bodhisattva


Joined: 04 May 2003
Posts: 2072
Location: /usa/utah

PostPosted: Sat Nov 06, 2004 7:01 pm    Post subject: Reply with quote

Of course, the wiki rocks. :)
_________________
If it ain't broke, tweak it. dvds | blurays | blog | wiki
Back to top
View user's profile Send private message
Cagnulein
l33t
l33t


Joined: 18 Sep 2003
Posts: 861
Location: Modena, Italy

PostPosted: Sat Feb 05, 2005 6:03 pm    Post subject: Reply with quote

mmm...exactly what i was looking for...if i understood is possibile to "share" a mysql db, right?

Example:
at the moment i have a database (100 mb of stuff) on one machine. The traffic and the cpu load push me to thinking about split this db in 2 parts.
The first part that contains the recent data and it stores to the actual machine, and the second part that contains the other stuff (in on other machine :) ).
So all the users will have to log in into the first machine and this mechaninsm will have to be same as now for their.

Is it possibile to create?

Thanks.
Back to top
View user's profile Send private message
Sfynx
n00b
n00b


Joined: 01 Jun 2002
Posts: 50

PostPosted: Mon Feb 14, 2005 10:21 pm    Post subject: Reply with quote

Cagnulein wrote:
mmm...exactly what i was looking for...if i understood is possibile to "share" a mysql db, right?

Example:
at the moment i have a database (100 mb of stuff) on one machine. The traffic and the cpu load push me to thinking about split this db in 2 parts.
The first part that contains the recent data and it stores to the actual machine, and the second part that contains the other stuff (in on other machine :) ).
So all the users will have to log in into the first machine and this mechaninsm will have to be same as now for their.

Is it possibile to create?

Thanks.


The replicated information on the master and the slave is the same, it's like a mirror. You may not edit the replicated data on the slave with normal clients, or things will get messed up (there is a server option to prevent that from happening accidentally). The master gets edited and the changes get propagated to the slave, not the other way around.

Some extra notes:

- You don't have to write down the log file number and offset when you call mysqldump with the --master-data option, it will include the right data in the dump file in that case and works for both MyISAM and InnoDB tables. From the MySQL replication HOWTO on http://dev.mysql.com/doc/mysql/en/replication-howto.html:

Quote:
An alternative that works for both MyISAM and InnoDB tables is to take an SQL dump of the master instead of a binary copy as described in the preceding discussion. For this, you can use mysqldump --master-data on your master and later load the SQL dump file into your slave. However, this is slower than doing a binary copy.



- Replication over an SSL connection (which is wise to do through an unstrusted network like the Internet) requires MySQL 4.1 or higher if you don't want to use an external tunneling program like stunnel. Should be unmasked in Portage someday, it reached production level a while ago.
_________________
I'm the great Cornholio!
Are you threatening me?
Back to top
View user's profile Send private message
efratk
n00b
n00b


Joined: 15 Feb 2005
Posts: 1

PostPosted: Tue Feb 15, 2005 8:12 am    Post subject: How to set replication to be the master of other replicatio? Reply with quote

Hello,

I managed to set a replication server, but now I want the replication server to be the master for other replication servers.
I enabled the bin-log in the slave too, but when "show master status;" returns static position of 79, although it's being updated
from his master. Did anyone do it?

Thanks
Efrat Kadosh
Back to top
View user's profile Send private message
Klavs
Guru
Guru


Joined: 22 May 2002
Posts: 536
Location: Denmark

PostPosted: Sun Mar 27, 2005 3:10 pm    Post subject: Reply with quote

Just wanted to note, that atleast in MySQL 4.0 this won't work:
log-bin=/backup/bigdisk/mysqlbinlogs
(when mysqlbinlogs is a directory) - you need to give a filename too - according to MySQL (took me a while to find this annoying detail :)
This will work:
log-bin=/backup/bigdisk/mysqlbinlogs/binlog
_________________
Best regards,

Klavs Klavsen
Denmark

Working with Unix is like wrestling a worthy opponent.
Working with windows is like attacking a small whining child
who is carrying a .38.
Back to top
View user's profile Send private message
meulie
l33t
l33t


Joined: 17 Jun 2003
Posts: 845
Location: a Dutchman living in Norway

PostPosted: Mon Apr 18, 2005 1:16 pm    Post subject: Reply with quote

Sfynx wrote:
Cagnulein wrote:
mmm...exactly what i was looking for...if i understood is possibile to "share" a mysql db, right?

Example:
at the moment i have a database (100 mb of stuff) on one machine. The traffic and the cpu load push me to thinking about split this db in 2 parts.
The first part that contains the recent data and it stores to the actual machine, and the second part that contains the other stuff (in on other machine :) ).
So all the users will have to log in into the first machine and this mechaninsm will have to be same as now for their.

Is it possibile to create?

Thanks.


The replicated information on the master and the slave is the same, it's like a mirror. You may not edit the replicated data on the slave with normal clients, or things will get messed up (there is a server option to prevent that from happening accidentally). The master gets edited and the changes get propagated to the slave, not the other way around.


What is the recommended/easy solution for 2-way replication where data can be added/edited on either host, and gets replicated (or synced later if the other is down for the count).

Can I configure both servers as both master & slave for each other?
_________________
Greetz,
Evert Meulie
Back to top
View user's profile Send private message
kashou
n00b
n00b


Joined: 05 Apr 2004
Posts: 13

PostPosted: Fri Sep 16, 2005 4:17 pm    Post subject: Re: [HOWTO] MySQL Replication Reply with quote

beandog wrote:
MySQL replicates the data between the two databases by using binary logs stored on the master server. The first step is to setup the server so it creates them.

Please lemme know about errors / fixes / suggestions whatever.


This is a great resource. I was wondering if you could specify how to do this for specific databases only? I have 5 databases on my server, but I only want to replicate 3 of them to the slave.

Any help would be great!

-kash
Back to top
View user's profile Send private message
SeeksTheMoon
Apprentice
Apprentice


Joined: 24 Sep 2003
Posts: 163

PostPosted: Mon Jan 22, 2007 6:34 pm    Post subject: Reply with quote

can I use the same slave server to replicate databases from two different master computers?
Back to top
View user's profile Send private message
lakshmi.g
n00b
n00b


Joined: 31 Jul 2007
Posts: 1

PostPosted: Tue Jul 31, 2007 9:29 am    Post subject: Hi Reply with quote

Could you please provide me the details regarding this documentation. This doc applies to Mysql Cluster also?. Shall I use this for database synchronization through WAN?.

Thanks in Advance.
Back to top
View user's profile Send private message
9h1lo
n00b
n00b


Joined: 18 Oct 2007
Posts: 2

PostPosted: Thu Oct 18, 2007 11:19 pm    Post subject: multiple schemas Reply with quote

Does this apply when I have about 5 databases on 1 mysql server ? will they all be synced from master to slave? I need the slave to do mysqldumps without locking the database
Back to top
View user's profile Send private message
9h1lo
n00b
n00b


Joined: 18 Oct 2007
Posts: 2

PostPosted: Fri Oct 19, 2007 9:25 am    Post subject: Reply with quote

when i try this i get

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum