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 | XP home 에서 동시 접속 늘리기 | WHRIA | 2012.08.25 | 8946 |
1383 | MBC 최강연승 퀴즈쇼 [2] | WHRIA | 2012.08.13 | 9376 |
1382 | 이랬으면 좋겠네. | WHRIA | 2012.07.15 | 7784 |
1381 | 좋은 화장품 | WHRIA | 2012.07.11 | 8960 |
1380 | 피얼룩 지우기 | WHRIA | 2012.05.26 | 10025 |
1379 | deskzoom | WHRIA | 2012.05.22 | 7657 |
1378 | yahoo 데이터 가져오기 | WHRIA | 2012.04.25 | 8513 |
1377 | 차트 php | WHRIA | 2012.04.24 | 7941 |
1376 | simple adblock | WHRIA | 2012.04.13 | 18177 |
1375 | Medicalphoto 홈페이지 개편 | WHRIA | 2012.04.10 | 7172 |
1374 | Medicalphoto 1.08 | WHRIA | 2012.04.04 | 8009 |
1373 | 우분투에서 NETBIOS 설정 | WHRIA | 2012.04.02 | 7566 |
» | mysql replication [1] | WHRIA | 2012.02.26 | 12458 |
1371 | 토렌트 검색 site 만들었음 | WHRIA | 2012.02.14 | 7055 |
1370 | 공동개원3 | WHRIA | 2012.02.11 | 5987 |
http://blog.evandavey.com/2008/05/how-to-setup-mysql-replication-a-simple-guide.html