- 09
- Jun
MySQL is an excellent open source database system. Replication is a great way to keep data redundant in case of a server crash. However, replication should not take the place of backups in case of data corruption or mis-entered data - as this data will also be replicated to the slave.
MySQL replication takes place in a master-slave configuration. Be aware that by using the configuration - only changes made on the master are replicated to the slave. Any changes on the slave will not be replicated to the master.
Following the steps below, you can have MySQL replication setup in no time at all.
Source: MySQL Dev Site
1. Open the my.cnf or my.ini (depending on linux or windows).
2. Enter somewhere below ‘[mysqld]‘ on the master server.
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
3. Restart mysqld on the master server
4. Create a user on the master with the ‘REPLICATION SLAVE’ privilege. This user needs no other privileges. Replace X.X.X.X with the IP address of the slave server.
GRANT REPLICATION SLAVE ON *.* TO ‘user’@'X.X.X.X’ IDENTIFIED BY ‘password’;
5. Execute ‘FLUSH TABLES WITH READ LOCK;’ on the master to prevent writing to the databases.
6. Execute ‘SHOW MASTER STATUS;’ on the master and record the values for later.
7. Execute ‘UNLOCK TABLES;’ on the master.
8. Open the my.cnf or my.ini on the slave server.
9. Enter somewhere below ‘[mysqld]‘ on the slave server:
10. Save the file and restart mysqld.
11. Execute the following on the slave server (adjust values accordingly to user setup in step 4 and values retrieved from step 6):
MASTER_HOST=’X.X.X.X’,
MASTER_USER=’user’,
MASTER_PASSWORD=’password’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS=98,
MASTER_CONNECT_RETRY=10;
12. Execute the following on the slave server:
13. Check the mysql log on the slave to ensure that the connection to the master has been successful. You should see a line similar to the following:
You should now have a successful MySQL master-slave configuration. If you have any questions please let me know.
![[del.icio.us]](http://images.del.icio.us/static/img/delicious.small.gif){kind=small}
Related Posts
Tags: Featured, Linux, MySQL, Networking, Replication, Tutorials, Windows
June 9th, 2008 at 7:25 pm
[...] Vía: Hackosis [...]
June 10th, 2008 at 12:10 am
Hi,
Great how-to !
I’d just add that it would be good to limit the binary log files retentions with the expire_logs_days on the master, otherwise they’ll just stay for ever !
Stephane
http://www.sakana.fr/blog/
June 10th, 2008 at 7:43 am
Thanks Stephane - any ideas on how to do this automatically?
June 30th, 2008 at 10:43 am
[...] Setting up mySQL replication in 10 minutes [...]
July 3rd, 2008 at 9:33 pm
Hello - A weekly cron job to purge master log files on a rolling 31-day basis:
0 9 * * mon mysql -uroot -e “PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);”
http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html
you can also set the system variable expire_logs_days in
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#option_mysqld_expire_logs_days
July 9th, 2008 at 9:34 am
[...] How To Setup MySQL Replication in 10 minutes Following the steps below, you can have MySQL replication setup in no time at all. (tags: mysql replication) [...]
July 12th, 2008 at 11:34 am
[...] How To Setup MySQL Replication in 10 minutes | Hackosis (tags: mysql database howto backup replication) [...]
July 12th, 2008 at 4:48 pm
Shane,
Try this…
expire-logs-days = 20
max_binlog_size = 104857600
July 12th, 2008 at 7:47 pm
Thanks for the tips Jamie.
July 16th, 2008 at 11:22 am
How do I configure the replicaiton to take only certain database:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.15
Master_User: dhi16_slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master Configuration:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Replication Configuraiton
# C. Chandler - 07.15.2008
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
relay-log=/var/log/mysql/mysql-relay-bin
relay-log-index=/var/log/mysql/mysql-relay-bin.index
log-error=/var/log/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
datadir=/var/lib/mysql/
log-bin=/var/lib/mysql/mysql-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Slave Configuration:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Replicaiton Configuration
# C. Chandler - 07.15.2008
server-id=2
relay-log=/var/log/mysql/mysql-relay-bin
relay-log-index=/var/log/mysql/mysql-relay-bin.index
log-error=/var/log/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
datadir=/var/lib/mysql
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Thanks for the help.
Chris
July 16th, 2008 at 12:44 pm
Hrm, I haven’t tried only replicating one database, but I guess you could try this:
On the slave my.cnf:
replicate-do-db=databasename
July 25th, 2008 at 1:34 am
[...] e veloce per replicare un db in MySQL in modo da avere sempre un backup dei dati. Ritrovo su hackosis una bel tutorial che potrebbe esservi di [...]
July 26th, 2008 at 9:34 am
Hi,
Great Tutorial :)
Just wanted to confirm, that this tutorial is also applicable for two instances of MySQL running on the same server? One on port no. 3306 and other on 3307.
In case not, then please provide me some insight on that as well.
Thanks
July 26th, 2008 at 10:01 am
I have not tested this with two instances running on the same machine. Let us know what you find Pankaj.
July 27th, 2008 at 11:30 pm
Hi All,
This is to inform that the tutorial works perfectly fine for two instance running on same machine but different ports. I will be posting complete steps soon.
August 26th, 2008 at 9:01 pm
[...] How To Setup MySQL Replication in 10 minutes (tags: replication Database cluster mysql tips tech tutorial) [...]