mysql replication
2012.02.26 19:57
http://blog.evandavey.com/2008/05/how-to-setup-mysql-replication-a-simple-guide.html
The following is a simple guide to help you setup Mysql replication – a fantastic way to ensure you have an up-to-date backup of your production databases.
This guide only describes a single master-slave relationship, however you can use mysql in various modes which include additional slaves as required.
Production Replication Setup
The master (production server) needs to have binary logging turned on to facilitate replication. To turn on binary logging, we need to take the following steps:
-
Using phpMyAdmin, add a new user called replication_user which only has REPLICATION SLAVE access rights
-
Add the following lines to the /etc/my.cnf file:
[mysqld] log-bin=mysql-bin server-id=1
-
Restart mysql (as root)
[user@server]# service mysqld restart
-
Stop the production server, and obtain its state information. Most importantly, note the File and Position values:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +---------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | mysql-bin.003 | 240 | test | manual,mysql | +---------------+----------+--------------+------------------+
-
While the database has the READ LOCK applied, export the data using mysqldump, including master data information:
mysqldump --all-databases --master-data > dbdump.db
-
Then unlock the master database:
mysql> UNLOCK TABLES;
Slave Replication Setup
To initialise the slave server, the following steps need to be taken:
-
Add the following to the slave servers config file /etc/my.cnf:
# Replication settings log-bin replicate-ignore-db=mysql relay-log=triton-relay-bin log-slave-updates log-warnings server-id=2 # Slave details report-host=slave.mydomain.com # YOUR SLAVE'S HOSTNAME # Master Host details master-host=master.mydomain.com # YOUR MASTER'S HOSTNAME master-user=replication_user master-password=mypasswordhere # YOUR REPLICATION USERS PASSWORD master-port=3306
-
Transfer the data (dbdump.db) file from Master and load it into the database:
shell> mysql < fulldb.dump
-
Set the file information – this needs to match the values you observed on the master when you locked the tables above. In this example the filename was mysql-bin.003 and the log position was 240:
mysql> change master to MASTER_LOG_FILE='mysql-bin.003', MASTER_LOG_POS=240;
-
Start the slave:
mysql> start slave;
You should now have a working mysql replication database! Login to phpMyAdmin or something similar on the slave to confirm that all the databases are there.
Checking Replication is Working
It’s important to check that replication is working once you set it up. Some information can be gathered from the mysql log (usually found in /var/log/mysqld.log), however the following steps are more conslusive:
-
On the production server, issue the following command to see which slaves are connected:
mysql> show slave hosts; +-----------+------------------------------+------+-------------------+-----------+ | Server_id | Host | Port | Rpl_recovery_rank | Master_id | +-----------+------------------------------+------+-------------------+-----------+ | 2 | slave.mydomain.com | 3306 | 0 | 1 | +-----------+------------------------------+------+-------------------+-----------+ 1 row in set (0.02 sec)
-
On the backup server, issue the following command to see the slave status:
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master.mydomain.com Master_User: replication_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 525088794 Relay_Log_File: slave-relay-bin.000004 Relay_Log_Pos: 250751844 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 525088794 Relay_Log_Space: 250751844 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
More Information
More information can be found online at the Mysql documentation Replication Guide.
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
1384 | 1111 | 1111 | 2006.05.23 | 2034 |
1383 | 도약 | NEWTYPE | 2003.06.06 | 2036 |
1382 | 20 대에 해야할 남은 일들 | han | 2005.11.28 | 2037 |
1381 | 나비처럼 날아가서 벌처럼 쏘아라 | han | 2006.08.29 | 2038 |
1380 | 승석아, 오랜만이다. | 정규다 | 2003.12.08 | 2039 |
1379 | 공부 | 한승석 | 2004.01.19 | 2040 |
1378 | 사랑이란. | han | 2005.09.11 | 2041 |
1377 | 도움 주실수 있을런지요. . ? [1] | 오재동 | 2004.05.24 | 2043 |
1376 |
windows xp serial viewer
![]() | han | 2004.08.22 | 2044 |
1375 | 복잡한 길 | han | 2005.11.27 | 2046 |
1374 | network drive [5] | han | 2005.08.29 | 2049 |
1373 | 즐겨찾기 바꾸기 | han | 2007.01.23 | 2049 |
1372 | 그동안 컴터가 다운되고 문제가 많아서... | 한승석 | 2004.04.23 | 2051 |
1371 | 적응 | HAN | 2004.04.28 | 2051 |
1370 | skype | WHRIA | 2006.02.11 | 2052 |
http://blog.evandavey.com/2008/05/how-to-setup-mysql-replication-a-simple-guide.html