Setting up database replication on MySQL
Database replication is, as the americans would say, quite awesome. It provides, for one, an always fresh database backup. But most of the time you won't do it for backups. The greatest thing about database replication is that you can use it for load balancing.
Just replicate the database onto another server, and you have two atabase servers for SELECTs and you can leave the master for INSERTs, DELETEs and UPDATEs. The only bad thing about this is that you will need different connections for read and write (nothing that Dr. Nic's magic can't resolve), and you must have a fast connection between the two servers or you may have records that don't show up instantly when you store them (that's bad for a web application).
First of all, I'm supposing you already have a production database running in a server. If you start from scratch with several database servers, then it's much easier, as you don't have to dump/restore databases.
Go to your master server (let's say her name is scherie), and edit /etc/mysql/my.cnf:
Make sure you have set server-id to 1:
You need to uncomment the bind-address line, as we need port 3306 open for our slave:
By default, Debian for instance, already saves the transaction log. Uncomment or add it if you don't have it:
Add a line for the database that will be dumped to the binary log:
You can now restart the database:
Open a mysql client, and create a new user called replication_user (change this to whatever you want):
Now we have to lock temporarily the database while we proceed to dump it, so we have the correct binary log offset (Position).
Don't close the mysql client. Leave the terminal open, otherwise, the database will be unlocked. Write down the File and Position values, we will need them later.
Dump the database (you could use load data from master, but it's deprecated and will be removed in the future).
Once dumped return to the open terminal, and unlock tables:
It would be a good idea to compress the database dump before transferring it:
You should also allow access from the slave to the master server, but block it otherwise (basic iptables rules):
You're done with the master configuration. Let's open a terminal to our slave server (let's say she is called athena):
Check whether we have access to the master:
We can restore now the database dump from the master:
Add the following data to the slave's my.cnf, under the [mysqld] section:
Restart mysql:
Connect as root to our slave server:
Stop the slave:
Now, we need those File and Position parameters we got from the master, change the values on master_log_file and master_log_pos for those you wrote down previously:
We start the slave:
Now, if we issue a show slave status, you should see the following variables to 'Yes':
If you have Slave_IO_Running set to No, you haven't probably locked correctly the database prior to dumping it. You will have to repeat the dumping process locking correctly the database.
If everything is correct, your database will be automatically replicated. Doing a simple show processlist; in either the master or the slave will show whether the communication is working.
You can also take a look at the full guide to replication at the MySQL official site.
Just replicate the database onto another server, and you have two atabase servers for SELECTs and you can leave the master for INSERTs, DELETEs and UPDATEs. The only bad thing about this is that you will need different connections for read and write (nothing that Dr. Nic's magic can't resolve), and you must have a fast connection between the two servers or you may have records that don't show up instantly when you store them (that's bad for a web application).
First of all, I'm supposing you already have a production database running in a server. If you start from scratch with several database servers, then it's much easier, as you don't have to dump/restore databases.
Go to your master server (let's say her name is scherie), and edit /etc/mysql/my.cnf:
Make sure you have set server-id to 1:
server-id = 1
You need to uncomment the bind-address line, as we need port 3306 open for our slave:
#bind-address = 127.0.0.1
By default, Debian for instance, already saves the transaction log. Uncomment or add it if you don't have it:
log_bin = /var/log/mysql/mysql-bin.log
Add a line for the database that will be dumped to the binary log:
binlog-do-db=database_to_be_replicated
You can now restart the database:
scherie:~# /etc/init.d/mysql restart
Open a mysql client, and create a new user called replication_user (change this to whatever you want):
scherie:~# mysql -u root -p
mysql> grant replication slave on *.* to 'replication_user'@'%' identified by 'password';
mysql> grant replication slave on *.* to 'replication_user'@'%' identified by 'password';
Now we have to lock temporarily the database while we proceed to dump it, so we have the correct binary log offset (Position).
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.00002
Position: 230
Binlog_Do_DB: database_to_be_replicated
Binlog_Ignore_DB:
1 row in set (0.00 sec)
*************************** 1. row ***************************
File: mysql-bin.00002
Position: 230
Binlog_Do_DB: database_to_be_replicated
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Don't close the mysql client. Leave the terminal open, otherwise, the database will be unlocked. Write down the File and Position values, we will need them later.
Dump the database (you could use load data from master, but it's deprecated and will be removed in the future).
scherie:~# mysqldump -u root -p database_to_be_replicated > database_to_be_replicated.sql
Enter password:
Enter password:
Once dumped return to the open terminal, and unlock tables:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye
It would be a good idea to compress the database dump before transferring it:
scherie:~# bzip2 database_to_be_replicated.sql
scherie:~# scp database_to_be_replicated.sql daniel@athena:
scherie:~# scp database_to_be_replicated.sql daniel@athena:
You should also allow access from the slave to the master server, but block it otherwise (basic iptables rules):
-A INPUT -s ip_of_slave_server -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j REJECT --reject-with icmp-port-unreachable
-A INPUT -p tcp -m tcp --dport 3306 -j REJECT --reject-with icmp-port-unreachable
You're done with the master configuration. Let's open a terminal to our slave server (let's say she is called athena):
Check whether we have access to the master:
athena:~# mysql -u replication_user -p -h scherie
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 38
Server version: 5.0.45-Debian_1-log Debian etch distribution
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 38
Server version: 5.0.45-Debian_1-log Debian etch distribution
We can restore now the database dump from the master:
athena:~# bzip2 -d insolitus.sql.bz2
athena:~# mysql -u root -p insolitus < insolitus.sql
Enter password:
athena:~# mysql -u root -p insolitus < insolitus.sql
Enter password:
Add the following data to the slave's my.cnf, under the [mysqld] section:
server-id = 2
master_host = ip_of_master_server
master_user = replication_user
master_password = password
replicate_do_db = database_to_be_replicated
master_host = ip_of_master_server
master_user = replication_user
master_password = password
replicate_do_db = database_to_be_replicated
Restart mysql:
athena:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Connect as root to our slave server:
athena:~# mysql -u root -p
Stop the slave:
mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Now, we need those File and Position parameters we got from the master, change the values on master_log_file and master_log_pos for those you wrote down previously:
mysql> change master to master_host='ip_of_master_server', master_user='replication_user', master_password='password',master_log_file='mysql-bin.000002', master_log_pos=230;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
We start the slave:
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Now, if we issue a show slave status, you should see the following variables to 'Yes':
mysql> show slave statusG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If you have Slave_IO_Running set to No, you haven't probably locked correctly the database prior to dumping it. You will have to repeat the dumping process locking correctly the database.
If everything is correct, your database will be automatically replicated. Doing a simple show processlist; in either the master or the slave will show whether the communication is working.
You can also take a look at the full guide to replication at the MySQL official site.
