This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on a second slave server, and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave, but replication will help protect against loss of data due to hardware failures.
I will be discussing how I set up replication on CentOS with MySQL 5. The actual replication setup is pretty platform independent, so it should work pretty much the same on any Linux distro. This setup has been tested on CentOS, Fedora, RHEL, Debian and Gentoo Linux. I decided to write this howto because after reading every other doc I could find, and discussing replication concepts with members of the MySQL team, we came to the conclusion that some aspects of the docs on the MySQL website are outdated and need to be updated. Before I go into detail about how I set up replication, let me give some background of why you should not follow the slightly outdated (at the time of this writing) information posted on the MySQL website. (Bug Id: 23615) Odds are good that by the time you read this, I will have submitted more current documentation to MySQL and their instructions will reflect these instructions. The MySQL site speaks of using binlog-do-db statements on the master. In most cases this does not do what you think it does. It also creates a replication environment where replication processing is performed on the master, and worse, has many reports of creating an incomplete binlog which is makes point in time (PIT) recovery difficult. An ideal replication architecture would allow a "lazy" master server, which only builds the binlog, and allows the slaves to handle the actual replication. The MySQL docs also make use of the replicate-do-db statement, this is also ill advised in favor of replicate-wild-do-table statements which allow for more efficient partial replication.
My setup involves one master with two slaves, the configuration of the slaves is identical with the exception of a different server-id. Replication will work with any database storage engine, InnoDB and MyISAM seem to be the favorites. Many like to use InnoDB for the master for added robustness and MyISAM for the slaves for added speed. This is really a matter of personal choice and if you opt for this option, you should consult the documentation on the MySQL site for replication options catering to InnoDB. On to setting up the master.
The first thing needed to prepare the master for replication is to enable networking if it is not already enabled. This can be done by making sure that the following lines are commented out, or non-existent.
#skip-networking
#bind-address = 127.0.0.1
Next, you have to designate a unique server-id. By convention the server-id for the master is 1.
server-id = 1
Finally, you have to set the pathway for the master to create its binary log. It is best practice to write the binlog to another filesystem than that which the tablespace files are located, this way the tablespace filesystem and the binlog filesystem can fail independently.
log-bin=/some/other/partition/
Other, optional statements that you should include is expire-logs-days, to set an expiration date for the binlog.
expire-logs-days=7
This will fix the binlog name to binlog.xxxxxx and will expire binlogs older than seven days automatically. These practices will ensure that the binlog on the master will be complete, and good for seven days backlog. After this, you may restart MySQL.
/etc/init.d/mysql restart
Now, we must log into the MySQL server as root to create a user with replication privileges.
mysql -u root -p
When you are presented with the MySQL shell, you must add a replication user account with replication slave privileges. It is best practice to have a dedicated account for replication, here we name this account 'repl'.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'some-password';
The above account will create an account where any slave can log into the master. This is most likely not what you want. However, you may wish to set it up initially this way to test the connection. After replication is working, you may wish to remove this account and reissue the grant statement to something like:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.foo.com' IDENTIFIED BY 'some-password';
To restrict slaves to a certain subdomain. After you have successfully created the account, you can execute the following to flush the privileges, the tables, and get some needed output for the slave.
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
This should provide similar output
+---------------+----------+--
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--
| mysql-bin.009 | 205 | | |
+---------------+----------+--
1 row in set (0.00 sec)
This information is needed for the slave, so note it. You will notice the two right columns are blank, this shows that all of the replication logic is being performed on the slave side. This is ideal as it makes for easy configuration of slaves which may perform different actions on different databases or tables. Now, you can "quit" to leave the MySQL shell, and we will make a dump of the databases or tables that are to be replicated using mysqldump. On the master (which should have the current database to be replicated), create a dump file:
mysqldump --opt --verbose -p databasename > databasename.sql
You may wish to check the man page for mysqldump, in case special options apply. This should work in most cases. This will dump database 'databasename' to a dump file appropriately named 'databasename.sql'. You can scp this over to the slave machine so that we can dump it into a newly created database there. That finishes up our requirements on the master, you can log back in to the MySQL shell and release the lock.
mysql -u root -p
(enter your password)
UNLOCK TABLES;
quit;
Now, on to the slave. First we need to create the database that we will dump our dump file into. In this case, on the slave we would:
mysql -u root -p
(enter password)
CREATE DATABASE DATABASENAME;
quit;
Now dump your schema file that you copied from the master into your newly created database.
mysql -p databasename < /path/to/databasename.sql
You have to let the replication slave know that it is the replication slave. Specify an original server-id, along with the following options (with your values obviously) in the my.cnf file on the slave server. Remember, if you have more than one slave server, each server-id must be unique.
server-id=2
master-host=master.foo.com
master-port=3307
master-user=repl
master-password=some-password
master-connect-retry=60
In this example we are running MySQL on a non-standard port. If you want to run MySQL on the default port (3306) you can remove this line. You must also add a statement to tell the replicating slave which databases or tables to replicate. Prior this was done with the replicate-do-db statement, however, being new and improved we will use replicate-wild-do-table. To replicate the entire database 'databasename' add the following entry to my.cnf on the slave.
replicate-wild-do-table=
If you wish to specify a specific table, you can replace the wild (%) character with the table name. You should also create a directory to specify relay logs, make sure that the MySQL process has permissions to write to this directory. I created a directory called mysql-replication in /var/log (mkdir /var/log/mysql-replication) and set the ownership accordingly (chown -R mysql:mysql /var/log/mysql-replication). Then, inside of your my.cnf file add the following entries:
log-error = /var/log/mysqld.log
relay-log = /var/log/mysql-replication/
relay-log-info-file = /var/log/mysql-replication/rel
relay-log-index = /var/log/mysql-replication/
The first statement you may already have, this is just a standard log. Check to make sure that you have it in the my.cnf file somewhere, and add the proceeding statements to define the relay logs.
Then, restart MySQL.
/etc/init.d/mysql restart
If you are watching you logs, you will notice that the connection fails. This is because we still have not set the MySQL host value. Log into the MySQL shell on the slave and stop the slave replication IO thread.
mysql -u root -p
(enter password)
SLAVE STOP;
This will kill the replication thread and allow us to set the host. While inside the MySQL shell, pass the following command to the database server. Remember to substitute the values with those that you noted earlier.
CHANGE MASTER TO MASTER_HOST='master.foo.com', MASTER_USER='repl', MASTER_PASSWORD='some-
MASTER_HOST is the IP address or hostname of the master (in this example it is master.foo.com).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_PORT is the port number that MySQL is running on. (optional and defaults to 3306).
Now, all that you have to do to start replication is to start the slave. Log back into the MySQL shell and issue the following command.
START SLAVE;
You will see in the logs that it syncs to the point in the binlog that you noted, and all future updates to the master will be replicated to the slave. Cool ha?
=============================
No comments:
Post a Comment