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.

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.

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:

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.

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?

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

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?

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.

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