Gentoo Wiki ArchivesGentoo Wiki

HOWTO_MySQL_Replication

Moving from original forum post: http://forums.gentoo.org/viewtopic-t-241123.html

Contents

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.

Ceates the directories and set the permissions so only the mysql daemon can read/write to the logs:

# mkdir /var/log/mysql/binary
# chown mysql: /var/log/mysql/binary
# chmod 770 /var/log/mysql/binary

MySQL Configuration

Open /etc/mysql/my.cnf with your favorite editor. You'll need to enable binary logging in the configuration file.

Add this under the [mysqld] section.

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

# /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.

Slave Account

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

# mysql -p
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.

Remark: GRANT REPLICATION is an administrative privilege so I needed to go like this:

grant replication slave on *.* to 'replikator'@'%' identified by 'mypass';

Database Dumps

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 but is deprecated.

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.

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 (GNU screen comes in handy). If you do, the tables will be unlocked.

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:

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

# mysqldump --opt database_name > database_name.sql

and unlock the tables

mysql> UNLOCK TABLES;

You're done now with the master server.

Slave Server

Import the mysqldump from the master server:

# mysql database_name < database_name.sql

Edit the slave's MySQL configuration to setup replication:

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

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.

# /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.

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!

mysql> START SLAVE;

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

MySQL Documentation

This article is still a Stub. You can help Gentoo-Wiki by expanding it.

Retrieved from "http://www.gentoo-wiki.info/HOWTO_MySQL_Replication"

Last modified: Mon, 08 Sep 2008 16:36:00 +1000 Hits: 10,512

Created by NickStallman.net, Luxury Homes Australia
Real estate agents should list their apartments, townhouses and units in Australia.