<\/p>\n
mysql replication \uad6c\uc131<\/p>\n
1. mysql rpm \uc124\uce58
\nmysql \ud648\ud398\uc774\uc9c0\uc5d0\uc11c \ucd5c\uc2e0\ubc84\uc804 \ub2e4\uc6b4\ub85c\ub4dc
\nhttp:\/\/www.mysql.com\/downloads\/mysql\/
\nMySQL Community Server 5.5.20 – Linux – Generic
\n\uc6b0\ub9ac\uac00 \ud544\uc694\ud55c Client Utilities\uc640 MySQL Server\ub97c \ubc1b\ub294\ub2e4.
\n(MySQL-client-5.5.20-1.linux2.6.x86_64.rpm, MySQL-server-5.5.20-1.linux2.6.x86_64.rpm)<\/p>\n
Master\uc5d0 Mysql rpm\uc73c\ub85c \uc124\uce58<\/p>\n
Master]# rpm -ivh MySQL-server-5.5.20-1.linux2.6.x86_64.rpm\r\nPreparing... ########################################### [100%]\r\n 1:MySQL-server ########################################### [100%]\r\n...\r\nMaster]# rpm -ivh MySQL-client-5.5.20-1.linux2.6.x86_64.rpm\r\nPreparing... ########################################### [100%]\r\n 1:MySQL-client ########################################### [100%]\r\n<\/pre>\nSlave\uc5d0 Mysql rpm\uc73c\ub85c \uc124\uce58<\/p>\n
Slave]# rpm -ivh MySQL-server-5.5.20-1.linux2.6.x86_64.rpm\r\nerror: Failed dependencies:\r\n MySQL conflicts with mysql-5.0.95-1.el5_7.1.x86_64\r\n MySQL conflicts with mysql-5.0.95-1.el5_7.1.i386\r\n<\/pre>\n\ud750\ubbf8.. dependencies \ub54c\ubb38\uc5d0 \uc124\uce58\uac00 \uc548\ub428,,\u3160\u314d\u3160<\/p>\n
\uc774\ubbf8 \uc124\uce58\ub41c mysql \ud655\uc778\ud558\uace0 remove..<\/p>\n
Slave]# yum list installed |grep mysql\r\nmysql.i386 5.0.95-1.el5_7.1 installed\r\nmysql.x86_64 5.0.95-1.el5_7.1 installed\r\nmysql-devel.i386 5.0.95-1.el5_7.1 installed\r\nmysql-devel.x86_64 5.0.95-1.el5_7.1 installed\r\nSlave]# yum remove mysql*\r\n...\r\nRemoved:\r\n mysql.i386 0:5.0.95-1.el5_7.1 mysql.x86_64 0:5.0.95-1.el5_7.1 mysql-devel.i386 0:5.0.95-1.el5_7.1 mysql-devel.x86_64 0:5.0.95-1.el5_7.1\r\n\r\nComplete!\r\n<\/pre>\n\uc790, \ub2e4\uc2dc \uc124\uce58 \uc2dc\ub3c4.<\/p>\n
Slave]# rpm -ivh MySQL-server-5.5.20-1.linux2.6.x86_64.rpm\r\nPreparing... ########################################### [100%]\r\n 1:MySQL-server ########################################### [100%]\r\n...\r\nSlave]# rpm -ivh MySQL-client-5.5.20-1.linux2.6.x86_64.rpm\r\nPreparing... ########################################### [100%]\r\n 1:MySQL-client ########################################### [100%]\r\n<\/pre>\nmy.conf \uc0dd\uc131<\/p>\n
Master]# cp \/usr\/share\/mysql\/my-innodb-heavy-4G.cnf \/etc\/my.cnf\r\nSlave]# cp \/usr\/share\/mysql\/my-innodb-heavy-4G.cnf \/etc\/my.cnf\r\n<\/pre>\nmysql replication\uc744 \uc704\ud55c \uc124\uc815<\/p>\n
\ucc38\uace0\ub85c A —> B \uc740 A\uc124\uc815\uc744 B\uc73c\ub85c \ubcc0\uacbd
\n—> B \ub294 B\ub77c\uc778 \ucd94\uac00<\/p>\nMaster my.cnf \uc218\uc815<\/p>\n
Master]# vi \/etc\/my.cnf\r\n...\r\n---> datadir = \/data1\/mysql \r\n\r\nmax_connections = 100 ---> max_connections = 300\r\ntable_open_cache = 2048 ---> table_open_cache = 2048\r\nbinlog_cache_size = 1M ---> binlog_cache_size = 2M\r\ndefault-storage-engine = MYISAM ---> default-storage-engine = INNODB\r\nbulk_insert_buffer_size = 64M ---> bulk_insert_buffer_size = 16M\r\nmyisam_sort_buffer_size = 128M ---> myisam_sort_buffer_size = 64M\r\ninnodb_buffer_pool_size = 2G ---> innodb_buffer_pool_size = 3G\r\n#innodb_data_home_dir = <directory> ---> innodb_data_home_dir = \/data1\/mysql\/\r\ninnodb_write_io_threads = 8 ---> innodb_write_io_threads = 4\r\ninnodb_read_io_threads = 8 ---> innodb_read_io_threads = 4\r\ninnodb_thread_concurrency = 16 ---> innodb_thread_concurrency = 8\r\ninnodb_flush_log_at_trx_commit = 1 ---> innodb_flush_log_at_trx_commit = 2\r\n#innodb_log_group_home_dir ---> innodb_log_group_home_dir = \/data1\/mysql\/\r\n\r\n## \ucd94\uac00\ubd80\ubd84\r\n---> binlog_cache_size = 2M # binlog cache \uc0ac\uc774\uc988\r\n---> max_binlog_size = 500M # bin\ub85c\uadf8 \ud30c\uc77c \uc0ac\uc774\uc988\r\n---> expire_logs_days = 20 # \ubcf4\uad00\uae30\uac04\r\n---> init_connect=SET collation_connection = utf8_general_ci\r\n---> init_connect=SET NAMES utf8\r\n---> character-set-server=utf8\r\n---> collation-server=utf8_general_ci\r\n---> default-character-set=utf8\r\n...\r\nMaster]# mkdir \/data1\/mysql\r\nMaster]# chown -R mysql.mysql \/data1\/mysql\r\n<\/pre>\nmysql_install_db \uc2a4\ud06c\ub9bd\ud2b8 \uc2e4\ud589<\/p>\n
Master]# mysql_install_db --user=mysql\r\nInstalling MySQL system tables...\r\nOK\r\nFilling help tables...\r\nOK\r\n...\r\n<\/pre>\nMaster Mysql \uad6c\ub3d9<\/p>\n
Master]# \/usr\/bin\/mysqld_safe &\r\nMaster]# ps -ef |grep mysql\r\nroot 13019 9307 0 16:10 pts\/0 00:00:00 \/bin\/sh \/usr\/bin\/mysqld_safe\r\nmysql 13812 13019 4 16:10 pts\/0 00:00:00 \/usr\/sbin\/mysqld --basedir=\/usr --datadir=\/data1\/mysql --plugin-dir=\/usr\/lib64\/mysql\/plugin --user=mysql --log-error=\/data1\/mysql\/master.err --open-files-limit=8192 --pid-file=\/data1\/mysql\/master.pid --socket=\/var\/lib\/mysql\/mysql.sock --port=3306\r\nroot 13833 9307 0 16:10 pts\/0 00:00:00 grep mysql<\/pre>\nMysql\uc5d0 \uc811\uc18d\ud574\uc11c Master \uc758 \uc0c1\ud0dc \ud655\uc778<\/p>\n
Master]# mysql -u root -p\r\nEnter password:\r\nWelcome to the MySQL monitor. Commands end with ; or \\g.\r\nYour MySQL connection id is 1\r\nServer version: 5.5.20-log MySQL Community Server (GPL)\r\n\r\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql>\r\nmysql> show master status;\r\n+------------------+----------+--------------+------------------+\r\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000004 | 107 | | |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql><\/pre>\nMysql Slave \uac00 replication \uc218\ud589\uc744 \uc704\ud574 \uc811\uc18d\ud560 \uacc4\uc815 \uc0dd\uc131
\nmaster\uc5d0\uc11c \uacc4\uc815\uc0dd\uc131<\/p>\nmysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave-ip' IDENTIFIED BY 'slavepass';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n<\/pre>\nSlave my.cnf \uc218\uc815<\/p>\n
Slave]# vi \/etc\/my.cnf\r\n...\r\n---> slave-skip-errors = 1062\r\n---> binlog-ignore-db = mysql\r\n---> binlog-ignore-db = test\r\n---> binlog-ignore-db = davidoff\r\n\r\n---> datadir = \/data1\/mysql\r\n\r\ntable_open_cache = 2048 ---> table_open_cache = 256\r\n\r\nquery_cache_size = 64M ---> query_cache_size = 16M\r\ndefault-storage-engine = MYISAM ---> default-storage-engine = INNODB\r\n\r\ntmp_table_size = 64M ---> tmp_table_size = 16M\r\n\r\nserver-id = 1 ---> server-id = 2\r\n#master-host = <hostname> ---> # master-host =\r\n#master-user = <username> ---> # master-user = repluser\r\n#master-password = <password> ---> # master-password =\r\n\r\nkey_buffer_size = 32M ---> key_buffer_size = 16M\r\nbulk_insert_buffer_size = 64M ---> bulk_insert_buffer_size = 16M\r\nmyisam_sort_buffer_size = 128M ---> myisam_sort_buffer_size = 64M\r\ninnodb_buffer_pool_size = 2G ---> innodb_buffer_pool_size = 500M\r\n#innodb_data_home_dir = <directory> ---> innodb_data_home_dir = \/data1\/mysql\/\r\ninnodb_write_io_threads = 8 ---> #innodb_write_io_threads = 8 \r\ninnodb_read_io_threads = 8 ---> #innodb_read_io_threads = 8 \r\n--- > innodb_file_io_threads = 8\r\n\r\n## \ucd94\uac00\ubd80\ubd84\r\n---> binlog_cache_size = 2M # binlog cache \uc0ac\uc774\uc988\r\n---> max_binlog_size = 500M # bin\ub85c\uadf8 \ud30c\uc77c \uc0ac\uc774\uc988\r\n\r\n---> init_connect=SET collation_connection = utf8_general_ci\r\n---> init_connect=SET NAMES utf8\r\n---> character-set-server=utf8\r\n---> collation-server=utf8_general_ci\r\nmax_allowed_packet = 16M ---> max_allowed_packet = 64M\r\n---> default-character-set=utf8\r\nkey_buffer_size = 512M ---> key_buffer_size = 128M\r\nsort_buffer_size = 512M ---> sort_buffer_size = 128M\r\n...\r\nSlave]# mkdir \/data1\/mysql\r\nSlave]# chown -R mysql.mysql \/data1\/mysql\r\n<\/pre>\nSlave Mysql \uad6c\ub3d9 <\/p>\n
Slave]# \/usr\/bin\/mysqld_safe &<\/pre>\nReplication \uc124\uc815\uc744 \ud558\uae30\uc804\uc5d0 Master\uc5d0\uc11c Table Lock<\/p>\n
mysql> FLUSH TABLES WITH READ LOCK;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SHOW MASTER STATUS;\r\n+------------------+----------+--------------+------------------+\r\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000004 | 264 | | |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\nSlave\uc7a5\ube44\uc5d0\uc11c Replication\uc744 \uc704\ud55c Slave \uad6c\ub3d9<\/p>\n
Slave]# mysql -u root -p\r\nEnter password:\r\nWelcome to the MySQL monitor. Commands end with ; or \\g.\r\nYour MySQL connection id is 1\r\nServer version: 5.5.20-log MySQL Community Server (GPL)\r\n\r\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> change master to master_host='master-ip', master_user='repl', master_port=3306, master_password='', master_log_file='mysql-bin.000004', master_log_pos=630;\r\nQuery OK, 0 rows affected (0.01 sec)\r\n\r\nmysql> START SLAVE;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql>\r\n\r\nmysql> show slave status\\G\r\n*************************** 1. row ***************************\r\n Slave_IO_State: Connecting to master\r\n Master_Host: master-ip\r\n Master_User: repl\r\n Master_Port: 3306\r\n Connect_Retry: 60\r\n Master_Log_File: mysql-bin.000004\r\n Read_Master_Log_Pos: 630\r\n Relay_Log_File: master-relay-bin.000001\r\n Relay_Log_Pos: 4\r\n Relay_Master_Log_File: mysql-bin.000004\r\n Slave_IO_Running: Connecting\r\n Slave_SQL_Running: Yes\r\n Replicate_Do_DB:\r\n Replicate_Ignore_DB:\r\n Replicate_Do_Table:\r\n Replicate_Ignore_Table:\r\n Replicate_Wild_Do_Table:\r\n Replicate_Wild_Ignore_Table:\r\n Last_Errno: 0\r\n Last_Error:\r\n Skip_Counter: 0\r\n Exec_Master_Log_Pos: 630\r\n Relay_Log_Space: 107\r\n Until_Condition: None\r\n Until_Log_File:\r\n Until_Log_Pos: 0\r\n Master_SSL_Allowed: No\r\n Master_SSL_CA_File:\r\n Master_SSL_CA_Path:\r\n Master_SSL_Cert:\r\n Master_SSL_Cipher:\r\n Master_SSL_Key:\r\n Seconds_Behind_Master: NULL\r\nMaster_SSL_Verify_Server_Cert: No\r\n Last_IO_Errno: 1045\r\n Last_IO_Error: error connecting to master 'repl@master-ip:3306' - retry-time: 60 retries: 86400\r\n Last_SQL_Errno: 0\r\n Last_SQL_Error:\r\n Replicate_Ignore_Server_Ids:\r\n Master_Server_Id: 0\r\n1 row in set (0.00 sec)\r\n\r\nmysql>\r\n<\/pre>\nMaster\uc758 lock\uc744 \ud480\uc5b4\uc90c <\/p>\n
mysql> UNLOCK TABLES;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n<\/pre>\n* Replication \uccb4\ud06c \uc0ac\ud56d
\n\ub3d9\uae30\ud654
\n– Master Server\uc758 Position \uacfc Slave Server\uc758 Read_Master_Log_Pos \uac12\uc774 \ub3d9\uc77c\ud574\uc57c \ud55c\ub2e4.
\n (Slave Server\uc758 Read_Master_Log_Pos \uac12\uacfc Exec_Master_Log_Pos \uac12\ub3c4 \ub3d9\uc77c \ud574\uc57c\ud55c\ub2e4)
\n– Slave Server\uc758 Slave_IO_Running \uacfc Slave_SQL_Running \uac12\uc774 \ubaa8\ub450 Yes \uc774\uc5b4\uc57c \ud55c\ub2e4. (\ubc84\uc804\uc5d0 \ub530\ub77c \uceec\ub7fc\uc774 \ub2e4\ub978\ub4ef)
\n– Slave Server\uc5d0\uc11c Pos \uac12\uc774 \ubcc0\ud558\ub294\uc9c0 \ud655\uc778.
\n– Master Server\uc5d0 \ud14c\uc774\ube14\uc744 \ub9cc\ub4e4\uc5b4\ubcf4\uace0 insert \ud574\ubcf4\uace0 \ub3d9\uae30\ud654\uac00 \ub418\ub294\uc9c0 \ud655\uc778.<\/p>\n\ub300\uae30
\n– Slave Server\uc758 Seconds_Behind_Master \uac12\uc774 \ud06c\uba74 \ud074\uc218\ub85d \uc9c0\uc5f0 \ub418\uace0 \uc788\ub2e4\ub294 \ub73b.<\/p>\nreplication\uc774 \uc815\uc0c1\uc801\uc73c\ub85c \ub3d9\uc791\ud558\ub294\uc9c0 \ud655\uc778<\/p>\n
Master\uc5d0\uc11c \uc0c8\ub85c\uc6b4 DB\uc640 table\uc744 \uc0dd\uc131<\/p>\n
mysql> create database apollo89;\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> CREATE TABLE IF NOT EXISTS `apollo89`.`TBL_test` (\r\n `ID` INT NOT NULL AUTO_INCREMENT COMMENT '',\r\n `DIVISION` VARCHAR(30) NOT NULL COMMENT '',\r\n `NAME` VARCHAR(30) NOT NULL COMMENT '',\r\n PRIMARY KEY (`ID`)\r\n);\r\n\r\nmysql> insert into TBL_test values ('','1','a');\r\nQuery OK, 1 row affected, 1 warning (0.00 sec)\r\n<\/pre>\nMaster\uc5d0\uc11c position \ud655\uc778<\/p>\n
mysql> SHOW MASTER STATUS;\r\n+------------------+----------+--------------+------------------+\r\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| mysql-bin.000004 | 1835 | | |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\nslave \uc5d0\uc11c Read_Master_Log_Pos \ud655\uc778<\/p>\n
mysql> show slave status\\G\r\n*************************** 1. row ***************************\r\n Slave_IO_State: Waiting for master to send event\r\n Master_Host: master-ip\r\n Master_User: repl\r\n Master_Port: 3306\r\n Connect_Retry: 60\r\n Master_Log_File: mysql-bin.000004\r\n Read_Master_Log_Pos: 1835\r\n Relay_Log_File: slave-relay-bin.000002\r\n Relay_Log_Pos: 1458\r\n Relay_Master_Log_File: mysql-bin.000004\r\n Slave_IO_Running: Yes\r\n Slave_SQL_Running: Yes\r\n Replicate_Do_DB:\r\n Replicate_Ignore_DB:\r\n Replicate_Do_Table:\r\n Replicate_Ignore_Table:\r\n Replicate_Wild_Do_Table:\r\n Replicate_Wild_Ignore_Table:\r\n Last_Errno: 0\r\n Last_Error:\r\n Skip_Counter: 0\r\n Exec_Master_Log_Pos: 1835\r\n Relay_Log_Space: 1616\r\n Until_Condition: None\r\n Until_Log_File:\r\n Until_Log_Pos: 0\r\n Master_SSL_Allowed: No\r\n Master_SSL_CA_File:\r\n Master_SSL_CA_Path:\r\n Master_SSL_Cert:\r\n Master_SSL_Cipher:\r\n Master_SSL_Key:\r\n Seconds_Behind_Master: 0\r\nMaster_SSL_Verify_Server_Cert: No\r\n Last_IO_Errno: 0\r\n Last_IO_Error:\r\n Last_SQL_Errno: 0\r\n Last_SQL_Error:\r\n Replicate_Ignore_Server_Ids:\r\n Master_Server_Id: 1\r\n1 row in set (0.00 sec)\r\n\r\nmysql>\r\n<\/pre>\nMaster\uc758 postion\uacfc Slave\uc758 Read_Master_Log_Pos\uc774 \ub3d9\uc77c\ud558\ub2e4!!<\/p>\n
\ucc38\uace0
\nhttp:\/\/www.mysqlkorea.co.kr\/sub.html?mcode=manual&scode=01_1&m_no=22382&cat1=753&cat2=0&cat3=0&lang=k
\nhttp:\/\/shonm.tistory.com\/435
\nhttp:\/\/ksewookk.blog.me\/100169519921
\nhttp:\/\/gr8.kr\/120066131409?Redirect=Log&from=postView
\nhttp:\/\/yupmin.com\/entry\/mysql_replication<\/p>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
mysql replication \uad6c\uc131 1. mysql rpm \uc124\uce58 mysql \ud648\ud398\uc774\uc9c0\uc5d0\uc11c \ucd5c\uc2e0\ubc84\uc804 \ub2e4\uc6b4\ub85c\ub4dc http:\/\/www.mysql.com\/downloads\/mysql\/ MySQL Community Server 5.5.20 – Linux – Generic \uc6b0\ub9ac\uac00 \ud544\uc694\ud55c Client Utilities\uc640 MySQL Server\ub97c \ubc1b\ub294\ub2e4. (MySQL-client-5.5.20-1.linux2.6.x86_64.rpm, MySQL-server-5.5.20-1.linux2.6.x86_64.rpm) Master\uc5d0 Mysql rpm\uc73c\ub85c \uc124\uce58 Master]# rpm -ivh MySQL-server-5.5.20-1.linux2.6.x86_64.rpm Preparing… ########################################### … Continue reading