MySql的Master/Slave
主庫配置檔案my.ini
server-id=1
log-bin=mysql-bin.log
server-id=2
#啟用從庫日誌,這樣可以進行鏈式複製
log-slave-updates
#從庫是否只讀,0表示可讀寫,1表示只讀
read-only=1
#只複製某個表
replicate-do-table=tablename
#只複製某些表(可用匹配符)
replicate-wild-do-table=tablename%
#只複製某個庫
replicate-do-db=dbname
#只複製某些庫
replicte-wild-do-db=dbname%
#不復制某個表
replicate-ignore-table=tablename
#不復制某些表
replicate-wild-ignore-table=tablename%
#不復制某個庫
replicate-ignore-db=dbname
#複製完的sql語句是否立即從中繼日誌中清除,1表示立即清除
relay-log-purge=1
#從伺服器主機,用於show slave hosts生成從庫清單
report-host=hostname
master:
mysql> grant replication slave on *.* to 'rep'@'%' identified by 'rep';
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 | 433 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
salve:
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.201',
-> MASTER_USER='rep',
-> MASTER_PASSWORD='rep',
-> MASTER_LOG_FILE='mysql-bin.000015',
-> MASTER_LOG_POS=433;
Query OK, 0 rows affected (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 615
Relay_Log_File: node2-relay-bin.000002
Relay_Log_Pos: 433
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 615
Relay_Log_Space: 588
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
master:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
slave:
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| 1 | root | localhost | test | Query | 0 | NULL | show processlist |
| 2 | system user | | NULL | Connect | 647 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 240 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
master:
mysql> show processlist;
+----+------+-------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------+------+-------------+------+----------------------------------------------------------------+------------------+
| 1 | rep | node2:56986 | NULL | Binlog Dump | 1837 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 2 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-------------+------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.02 sec)
在從庫的資料目錄下,有幾個和複製相關的檔案需要說明一下:
*-reloay-bin.* 從主庫同步過來的Bin log檔案,也叫中繼日誌
master.info 主庫帳號資訊和同步資訊,這裡記錄了複製使用者名稱和密碼,需要保護好許可權。
relay-log.info 跟蹤執行同步過來的Bin log的執行情況
參考 http://www.ningoo.net/html/2007/mysql_replication_configuration.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/758322/viewspace-680438/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL Master/Slave Master/MasterMySqlAST
- MySQL master/slaveMySqlAST
- mysql master和slave配置MySqlAST
- mysql slave 轉為 masterMySqlAST
- MySQL 5.5 Master/Slave 配置MySqlAST
- mysql master slave 主從同步MySqlAST主從同步
- mysql 同步 master-slave薦MySqlAST
- Mysql Slave群切換MasterMySqlAST
- Mysql Slave群切換Master (=)MySqlAST
- MySQL master and slave have equal MySQL server UUIDsMySqlASTServerUI
- MySQL錯誤之mysql.slave_master_infoMySqlAST
- MYSQL的master/slave資料同步配置(轉)MySqlAST
- mysql建立master/slave詳細步驟MySqlAST
- 利用mysql slave 修復master MyISAM tableMySqlAST
- Redis master and slaveRedisAST
- Mysql 5.6 Master和Slave 主備切換MySqlAST
- MYSQL5的master slave資料同步配置(轉)MySqlAST
- mysql slave 跟進 master 的關鍵狀態指標MySqlAST指標
- Mysqldump實現mysql的master-slave主從複製MySqlAST
- Mongodb的master-slave模式與master-master模式實驗MongoDBAST模式
- The slave I/O thread stops because master and slave have equal MySQL server UUIDthreadASTMySqlServerUI
- Mysql Master-slave複製簡單配置記錄MySqlAST
- mysql master-slave複製錯誤[解決事例]MySqlAST
- master and slave have equal MySQL server UUIDs問題解決ASTMySqlServerUI
- MySQL 5.5.x 配置Master-Slave主從複製MySqlAST
- Innobackupex實現mysql線上搭建master-slave主從複製MySqlAST
- MySQL報錯Slave: received end packet from server, apparent master shutdownMySqlServerAPPAST
- Linux環境下MySQL5.6Master-Slave配置實戰LinuxMySqlAST
- Jenkins : 安裝 master 和 slaveJenkinsAST
- 用mysqldump --master-data 建立slaveMySqlAST
- MYSQL5.7 MASTER-SLAVE 線上關閉和啟動GTIDMySqlAST
- mysql5.6,master/slave架構,master,不寫bingo原因,無法開啟同步複製MySqlAST架構Go
- Redis核心解讀-從Master到Slave的ReplicantionRedisAST
- slave之Seconds_Behind_Master析AST
- 【Mysql】Slave_IO_Running: No---Got fatal error 1236 from masterMySqlGoErrorAST
- replace into 導致MASTER/SLAVE的auto_increment值不同ASTREM
- 故障案例:主從同步報錯Fatal error: The slave I/O thread stops because master and slave have equal MySQL server主從同步ErrorthreadASTMySqlServer
- Setup MariaDB Master/Slave Replication for Docker MariaDBASTDocker