Mysql Replication(轉)
Mysql Replication(轉)[@more@]類似於從一臺伺服器複製資料庫到另一臺伺服器上,但它是透過定義Master 和Slave的關係去實時地保證兩個資料庫的完全同步,這個功能在Mysql的3.23版中開始出現。Master/Slave模式備份TestEnv:Master:Mysql-4.1.12 on Redhat9.0 IP:192.168. 0.217Slave: Mysql-4.1.12 on Redhat9.0 IP:192.168.10.2441、編譯,安裝1. #tar –zxvf Mysql-4.1.12.tar.gz2. #cd Mysql-4.1.123. .#/configure –prefix=/var/eyou/mysql4. #make5. #make install6. #chown –R root /var/eyou/mysql7. # chown –R mysql /var/eyou/mysql/var8. #chgrp –R mysql /var/eyou/mysql9. #scripts/mysql_install_db10. #cp support-files/my-medium.cnf /etc/my.cnf2、Master 機器設定許可權,賦予Slave機器FILE及Replication Slave權利,並打包要同步的資料庫結構。Master# pwd/var/eyou/mysql/binMaster#./mysql –u root –pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2 to server version: 4.1.12Type 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> GRANT FILE ON *.* TO rep@192.168.0.244 IDENTIFIED BY ‘eyou’;mysql> GRANT REPLICATION SLAVE ON *.* TO rep@192.168.0.244 IDENTIFIED BY ‘eyou’;賦予192.168.10.244也就是Slave 機器有File許可權, 這個4.1.12版對replication的許可權好像做了調整,只賦予Slave機器有File許可權還不行,還要給它REPLICATION SLAVE的許可權才可以。然後打包要複製的資料庫Master# cd varMaster# tar czvf reptest.tar.gz reptest這樣,我們得到一個reptest資料庫的打包檔案reptest.tar.gz2設定主伺服器Master的my.cnf,啟動Mysql服務Master# vi /etc/my.cnf在[mysqld]新增或修改以下的[mysqld]log-bin #開啟logbin選項以能寫到slave的 I/O執行緒;server-id=1 #表示是本機的序號為1,一般來講就是master的意思.sql-bin-update-samebinlog-do-db= reptest #表示同步reptest資料庫;然後把Master主伺服器的Mysql重啟。Master# /var/eyou/mysql/bin/mysqladmin –u root –p shutdownMaster# /var/eyou/mysql/bin/safe_mysqld --user=mysql &3、建立Slave資料庫剛才在Master中打包了reptest.tar.gz,它的作用就是要在Slave恢復成一樣的資料庫。先把Master 的reptest.tar.gz檔案傳到Slave機器中去。然後Slave# tar zxvf reptest.tar.gz -C /var/eyou/mysql/var/4、修改Slave伺服器的my.cnfSlave# vi /etc/my.cnf在[mysqld]新增或修改以下的master-host=192.168.10.217master-user=repmaster-password=eyoumaster-port=3306server-id=2master-connect-retry=60replicate-do-db=reptest [要更新的資料庫]log-slave-updates5、刪除Slave端資料庫目錄中的master.infoSlave# rm /var/eyou/mysql/var/master.info6、重啟動Slave的slave start。Slave# /var/eyou/mysql/bin/mysqladmin –u root –p shutdownSlave# /var/eyou/mysql/bin/safe_mysqld --user=mysql &7、測試先檢測兩個Mysql資料庫中的reptest是否正常。正常情況應該是Master和Slave 中的Mysql 都有相同的reptest 資料庫,並且裡面的資料都一樣。然後測試replication 功能是否起用。在Master中的reptest資料庫新增一筆資料:Master# /var/eyou/mysql/bin/mysql –u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 12 to server version: 4.1.12Type 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> use reptest;Database changedmysql> INSERT INTO rep_table VALUES ('test1', '4321', 'T',24);Query OK, 1 row affected (0.00 sec)mysql>然後檢視Slave機器的reptest資料庫:Slave#/var/eyou/mysql/bin/mysql –u root –pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 12 to server version: 4.1.12Type 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> use reptest;Database changedmysql>select * from reptable;;+------+------+------+------+| id | name | sex | age |+------+------+------+------+| test1| 4321 | T | 24 |+------+------+------+------+1 row in set (0.00 sec)PS :1,Slave機器的許可權問題,不但要給slave機器File許可權,還要給它REPLICATION SLAVE的許可權。2.在修改完Slave機器/etc/my.cnf之後,slave機器的mysql服務啟動之前,記得要刪除掉master.info3,在show master status 或著show slave status 不正常時,看看.err是怎樣說的。4,Slave上Mysql的Replication工作有兩個執行緒, I/O thread和SQL thread 。I/O 的作用是從master 3306埠上把它的binlog取過來(master在被修改了任何內容之後,就會把修改了什麼寫到自己的binlog等待slave更新),然後寫到本地的relay-log,而SQL thread則是去讀本地的relay-log,再把它轉換成本Mysql所能理解的語句,於是同步就這樣一步一步的完成.決定I/O thread的是/var/lib/mysql/master.info,而決定SQL thread的是/var/lib/mysql/relay-log.info.雙向複製模式1:Slave 機器設定許可權,賦予Master機器FILE及Replication Slave權利.Master#./var/eyou/mysql –u root –pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2 to server version: 4.1.12Type 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> GRANT FILE ON *.* TO rep@192.168.0.217 IDENTIFIED BY ‘eyou’;mysql> GRANT REPLICATION SLAVE ON *.* TO rep@192.168.0.217 IDENTIFIED BY ‘eyou’;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8225414/viewspace-940553/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Build mysql replicationUIMySql
- MySQL Group ReplicationMySql
- MySQL基於GTIDs的MySQL ReplicationMySql
- Mysql replication check指令碼MySql指令碼
- MySQL group replication介紹MySql
- An Overview of PostgreSQL & MySQL Cross ReplicationViewMySqlROS
- Mysql Replication學習記錄MySql
- 使用ProxySQL實現MySQL Group Replication的故障轉移、讀寫分離(一)MySql
- MYSQL Group Replication搭建過程記錄MySql
- MGR(MySQL Group Replication)部署搭建測試MySql
- 【MySQL】MySQL Replication 一主一備搭建步驟(GTID方式)MySql
- MySQL8.0.16新特性:The Communication Protocol In Group ReplicationMySqlProtocol
- 【MySQL】MySQL Replication 一主一備搭建步驟(傳統方式)MySql
- 配置Mysql Group Replication遇到的問題筆記MySql筆記
- Mysql 5.7 基於組複製(MySQL Group Replication) - 運維小結MySql運維
- MySQL 8.0 Reference Manual(讀書筆記90節--Replication)MySql筆記
- Geo-replication: 從 Copysets 到 Tiered Replication
- MySQL 8.0 Reference Manual(讀書筆記94節--Replication(5))MySql筆記
- MySQL 8.0 Reference Manual(讀書筆記93節--Semisynchronous Replication)MySql筆記
- MySQL 8.0 Reference Manual(讀書筆記92節--Replication(3))MySql筆記
- MySQL 8.0 Reference Manual(讀書筆記91節--Replication(2))MySql筆記
- Mysql 建立心跳錶來監控Replication的Slave是否延遲MySql
- HBase Replication詳解
- How to Optimize PostgreSQL Logical ReplicationSQL
- Replication (Part 2): Transactions, Consistency and Consensus
- Redis replication 中的探活Redis
- Redis 主從複製(Replication)Redis
- MySQL 轉dorisMySql
- Oracle 轉MySqlOracleMySql
- Step by Step Data Replication Using Oracle GoldenGateOracleGo
- Setup MariaDB Master/Slave Replication for Docker MariaDBASTDocker
- Mysql - 行轉列、列轉行MySql
- 轉MySQL--mysql常用函式打全MySql函式
- Overview of LiquidUI WS&Designer-Customization-Replication on iOSViewUIiOS
- mysql 轉義問題MySql
- Redis replication主從複製原理及配置Redis
- group_replication_bootstrap_group 用於什麼boot
- [玩轉MySQL之四]MySQL快取機制MySql快取
- MySQL鎖詳解!(轉載)MySql