????Waiting for slave mutex on exit
????????????????????????????
????3.?????SQL?????
????Reading event from the relay log
?????????????м??????????????????????????д??????
????Has read all relay log; waiting for the slave I/O thread to update it
?????????????????м????????е???????????????????I/O?????????д???м??????
????Waiting for slave mutex on exit
????????????????????????????
?????????????????????
??????????????м??????????????????????????????????????????????????????????????????????Щ???????????????Щ?????
?????м????????????????????????????????mysqlbinlog?????SQL?????????м?????е??????????????????????????????????????????–relay-log??–relay-log-index???????????????м??????????????????????????????????????????????????м??????
???????????????????????μ??м??????
????1.???I/O???????????????μ??м??????
????2.????????????????磬??FLUSH LOGS??mysqladmin flush-logs??
????3.????????м????????????????“???”??????????????
????max_relay_log_size?????max_relay_log_size > 0
????max_binlog_size?????max_relay_log_size = 0
???????????????master.info??relay-log.info??????IO??????master.info?????SQL??????relay-log.info?????
????????е??к?SHOW SLAVE STATUS??????е?????????
????master.info?????
?????? ????
????1 ????е??к?
????2 Master_Log_File
????3 Read_Master_Log_Pos
????4 Master_Host
????5 Master_User
????6 ????(????SHOW SLAVE STATUS???)
????7 Master_Port
????8 Connect_Retry
????9 Master_SSL_Allowed
????10 Master_SSL_CA_File
????11 Master_SSL_CA_Path
????12 Master_SSL_Cert
????13 Master_SSL_Cipher
????14 Master_SSL_Key
????relay-log.info?????
?????? ????
????1 Relay_Log_File
????2 Relay_Log_Pos
????3 Relay_Master_Log_File
????4 Exec_Master_Log_Pos
??????????????????????????????????????С???????м?????????????????????????????????????????и?????????????м??????????? relay-log.info?????????????????????????SQL????????е??????????ж????????????????????? Master_Log_File??Master_LOG_POS??????CHANGE MASTER TO???????????????′????????????????????????????????????????????????????y??齫???????м????????????????дshell???????????????????
???????????????ò???
????1.??????????????????(??????????)??????????????????????????????
????GRANT REPLICATION SLAVE ON . TO ‘rep’@’%’ IDENTIFIED BY ‘logzgh’ ;
????????????????????????????LOAD TABLE FROM MASTER??LOAD DATA FROM MASTER????????????????????????
???????????SUPER??RELOAD???????
?????????????????????SELECT?????κθ? ???????SELECT???????????????LOAD DATA FROM MASTER??????
????2.??????????????????????
????????????????MyISAM??
????mysql> FLUSH TABLES WITH READ LOCK;
????(??????б???????????д?????????????????????????Ч???????????????????)
????????????????????????????
????tar?cvf/home/mysql/snapshot.tar./data(??master??) tar -xvf /home/mysql/snapshot.tar (??slave??)
??????????????? mysql ??????????slave????????master????????????????????????????????
????????????????????????κ????????????????master.info??relay-log.info??
????mysql> SHOW MASTER STATUS;
????+——————+———-+————–+——————+
????| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
????+——————+———-+————–+——————+
????| mysql-bin.000058 | 45036137 | | |
????+——————+———-+————–+——————+
????mysql> UNLOCK TABLES;
????????????????InnoDB??
??????????????InnoDB Hot Backup?????????????master???????κ?????????????????????????????????slave???????????????????????????????????λ?á?
?????????????????????????????λ?????master??????У?
????mysql> FLUSH TABLES WITH READ LOCK;
????mysql> SHOW MASTER STATUS;
??????????????????е????????????λ?á?????????????????1??master?????master?????????????????
???????master????????$ mysqladmin -u root shutdown
???????? InnoDB ??????????????????????????????(.frm???)??
?????????????????????MyISAM??InnoDB??
??????master????SQL???????????????????????????????????mysqldump –master-data??????????????????slave???
?????????????????????????????
????3.???my.cnf???
??????master??my.cnf?????(??????Ч)
????[mysqld]
????log_bin
????server_id=1 (??? 1 ?? 2^32-1 ??????????)
??????slave??my.cnf?????
????[mysqld]
????server_id=2 (ID?????master??ID????????ж??slave???????slave???????е?id??)
????????slave????????
????master_host=db-master.mycompany.com
????master_port=3306
????master_user=rep
????master_password=freitag
????master_connect_retry=60 (??master崻?????slave????????slave????????????master?????????????????????????????60??)
????report_host=db-slave.mycompany.com
????slave_net_timeout=3600 (slave??????3600??????????????master??????????????????????????????)
?????????????master.info????????????????my.cnf???
????????????slave???master.info???????????my.cnf?ж?????????????????????master.info?С?
?????′?????slave?????????master.info???????????????my.cnf?е???????
?????????ò?????????????????master.info??????slave?????????CHANGE MASTER TO???(???)??????????
????4.?????????????
????mysqld_safe –user=mysql –skip-slave-start & (???MySQL???????????????slave)
????????master_log_file?????
????mysql> CHANGE MASTER TO MASTER_HOST=’qa-sandbox-1′??
????MASTER_USER=’rep’??
????MASTER_PASSWORD=’logzgh’??
????MASTER_LOG_FILE=’mysql-bin.000007′??
????MASTER_LOG_POS=471632;
????mysql> START SLAVE??
?????????Щ??????????????????????????????????????????????????κθ????