建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網營銷解決方案
mysql主從復制架構及實現(xiàn)
MySQL主從復制架構及實現(xiàn)
mysql主從復制配置
主節(jié)點：
創(chuàng)新互聯(lián)是專業(yè)的廣安網站建設公司，廣安接單;提供成都網站建設、成都做網站,網頁設計,網站設計,建網站,PHP網站建設等專業(yè)做網站服務;采用PHP框架,可快速的進行廣安網站開發(fā)網頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網站,專業(yè)的做網站團隊,希望更多企業(yè)前來合作!
啟動二進制日志
為當前節(jié)點設置一個全局唯一的ID號（server-id）
- 創(chuàng)建有復制權限的用戶賬號（replication slave，replication client）
從節(jié)點：
啟動中繼日志
為當前節(jié)點設置一個全局唯一的ID號
- 使用有復制權限的用戶賬號連接至主服務器，并啟動復制線程
具體步驟
主節(jié)點（192.168.182.130）
編輯配置文件/etc/my.cnf，添加以下內容
[mysqld] log_bin=1 server_id=1 innodb_file_per_table=on- 創(chuàng)建有復制權限的用戶賬號
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;
從節(jié)點（192.168.182.132）
編輯配置文件/etc/my.cnf，添加以下內容
[mysqld] relay_log=relay_log relay_log_index=relay_log.index server_id=7 skip_name_resolve=1使用有復制權限的用戶賬號連接至主服務器
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=245;啟動復制線程
MariaDB [(none)]> START SLAVE;- 查看slave的狀態(tài)
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.182.130 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 245 Relay_Log_File: 1.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000075 Slave_IO_Running: Yes ##io線程啟動了 Slave_SQL_Running: Yes ## sql線程啟動了 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: 245 Relay_Log_Space: 809 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1
mysql主從復制中應該要注意的問題
限制從服務器為只讀
限制從服務器為只讀，在從服務器上設置read_only=on，想要永久有效，就寫在配置文件中，但是此限制對擁有SUPER權限的用戶均無效
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ MariaDB [(none)]> SET GLOBAL read_only=1;- 如果想要限制所有用戶不能夠進行寫操作，那么啟動一個連接加讀鎖，并且不要退出該連接
MariaDB [(none)]> FLUSH TABLE WITH READ LOCK;
如何保證主從復制的事務安全？
在master節(jié)點啟用參數
sync_binlog=如果用到的是InnoDB存儲引擎
innodb_flush_log_at_trx_commit innodb_support_xa- 在slave節(jié)點上，以下參數表示在從服務器啟動的時候，是否自動啟動復制線程，on表示不用自動啟動，使用手動啟動的方式
skip_slave_start=on
slave節(jié)點上的兩個文件
master.info文件中保存了slave連接至master時的相關信息，例如主服務器的ip地址，復制使用的用戶，密碼，端口，以及當前同步的二進制日志文件和位置
- relay-log.info文件中保存了當前slave節(jié)點已經復制的二進制日志文件和位置，復制的內容保存到了哪個中繼日志的哪個位置
[root@backserver data]# cat relay-log.info ./1.000002 693 mysql-bin.000075 409
復制的監(jiān)控和維護：
清理日志：使用PURGE命令，清理之前確保數據已經備份過了
復制監(jiān)控
MariaDB [(none)]> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 483 | | mysql-bin.000002 | 264 | | mysql-bin.000003 | 264 | | mysql-bin.000004 | 264 | | mysql-bin.000005 | 990 | | mysql-bin.000006 | 514 | | mysql-bin.000007 | 264 | | mysql-bin.000008 | 245 | | mysql-bin.000009 | 245 | +------------------+-----------+ MariaDB [(none)]> SHOW BINLOG EVENTS; +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.45-MariaDB-log, Binlog ver: 4 | | mysql-bin.000001 | 245 | Query | 1 | 315 | BEGIN | | mysql-bin.000001 | 315 | Intvar | 1 | 343 | INSERT_ID=9 | | mysql-bin.000001 | 343 | Query | 1 | 456 | use `S_SC_C`; INSERT INTO S (sname,sdept) VALUES ('xiao','MA') | | mysql-bin.000001 | 456 | Xid | 1 | 483 | COMMIT /* xid=43 */ | +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+ MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000009 | 245 | | | +------------------+----------+--------------+------------------+ MariaDB [(none)]> SHOW PROCESSLIST; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ | 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | | 6 | system user | | NULL | Connect | 3850 | Waiting for master to send event | NULL | 0.000 | | 7 | system user | | NULL | Connect | 3806 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+ MariaDB [(none)]> SHOW SLAVE STATUS\G從服務器是否落后與主服務器，在從服務器的salve有Seconds_Behind_Master可以查看
MariaDB [(none)]> SHOW SLAVE STATUS -> \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.182.130 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000079 Read_Master_Log_Pos: 334 Relay_Log_File: 1.000006 Relay_Log_Pos: 618 Relay_Master_Log_File: mysql-bin.000079 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: MYDB 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: 334 Relay_Log_Space: 1182 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1確定主從節(jié)點數據是否一致，使用一些工具，例如percona-tools
- 如果數據不一致，那么就要將從節(jié)點的所有數據刪除，將主節(jié)點的數據備份一份導入到從節(jié)點上，重新復制
在主節(jié)點上有一個參數，設置為1表示每一次dump事件到從節(jié)點的時候，本地的master.info信息要立即同步到磁盤上，讓從節(jié)點的master.info及時得到更新，
MariaDB [(none)]> SET GLOBAL sync_master_info=1; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'sync_master_info'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | sync_master_info | 1 | +------------------+-------+- 在從節(jié)點上有關于relay_log的參數，例如sync_relay_log_info，表示每一次寫操作立即同步到磁盤文件上，還有sync_relay_log
雙主模型
遇到的問題：
數據不一致，這時，我們可能就需要將數據可靠性比較高的服務器留下來，根據保留的mysql服務器重新再做一個從服務器
對于自動增長的字段，如果一個主節(jié)點是1，2，3，...，另一個主節(jié)點也是1，2，3，...，那么合并的時候就會出現(xiàn)問題，所以，我們可以讓一個節(jié)點的自動增長的字段使用偶數id，另一個主節(jié)點使用奇數id，這樣合并的時候就不會出現(xiàn)問題
奇數id的設置
auto_increment_offset=1 #表示從1開始 auto_increment_increment=2 #表示一次增長2個- 偶數id的設置
auto_increment_offset=2 auto_increment_increment=2
配置步驟
各節(jié)點使用一個唯一的server_id
都啟動binary log和relay log
創(chuàng)建擁有復制權限的用戶賬號
- 定義自動增長id字段的數值范圍為奇偶
具體步驟
其中一個節(jié)點為:
編輯配置文件
log_bin=1 server_id=1 innodb_file_per_table=on relay_log=relay-log relay_log_index=relay-log.index auto_increment_offset=1 auto_increment_increment=2創(chuàng)建用戶賬號
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;- 使用有復制權限的用戶賬號連接至主服務器并開啟slave線程
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=245; MariaDB [(none)]> START SLAVE;
在另一個主節(jié)點上的操作：
編輯配置文件
[mysqld] bin_log=1 relay_log=1 relay_log_index=relay-log.index server_id=7 auto_increment_offset=2 auto_increment_increment=2創(chuàng)建具有復制權限的用戶賬號
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.130' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;- 使用具有復制權限的用戶賬號連接至主節(jié)點并啟用slave線程
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=665; MariaDB [(none)]> START SLAVE;
半同步復制
需要用到一個插件，如果是rpm包安裝的插件路徑在/usrl/lib64/mysql/plugin，我使用二進制格式安裝的插件路徑是/usr/local/mysql/lib/plugin
主節(jié)點上的操作
編輯配置文件
[mysqld] log_bin=1 server_id=1 innodb_file_per_table=on plugin_dir=/usr/local/mysql/lib/plugin添加具有復制權限的用戶賬號
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.182.132' IDENTIFIED BY 'replpass'; MariaDB [(none)]> FLUSH PRIVILEGES;安裝插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';查看有哪些插件
MariaDB [(none)]> SHOW PLUGINS;查看半同步相關的變量
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+rpl_semi_sync_master_enabled：為OFF表示禁用為半同步復制的主節(jié)點
rpl_semi_sync_master_timeout：表示等待從服務器應答的超時時長，默認是10s，如果超過這個時間從服務器沒有給主服務器應答，那么就降級為異步方式運行，不再等待
rpl_semi_sync_master_trace_level：表示跟蹤級別
rpl_semi_sync_master_wait_no_slave：表示在沒有從節(jié)點的時候是否要等待，on為等待
- 啟用半同步
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
從節(jié)點上的操作：
編輯配置文件
[mysqld] relay_log=1 relay_log_index=relay-log.index server_id=7 skip_name_resolve=1 plugin_dir=/usr/local/mysql/lib/plugin/使用具有復制權限的用戶賬號連接至主節(jié)點
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='rpluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000078',MASTER_LOG_POS=245;安裝插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';查看半同步相關的變量
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+啟用半同步
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;- 啟用slave線程
MariaDB [(none)]> START SLAVE;
- 以上操作就可以完成一個半同步的復制，可以在主節(jié)點上查看相關的狀態(tài)變量，查看是否有半同步的slave節(jié)點
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ # Rpl_semi_sync_master_clients為0表示沒有，為1表示有
復制過濾器
讓從節(jié)點僅復制指定的數據庫，或指定數據庫的指定表
有兩種實現(xiàn)方式:
主服務器僅向二進制日志中記錄與特定數據庫（特定表）相關的事件，但是可能會導致時間點還原無法實現(xiàn)（畢竟二進制日志記錄的事件是不完全的），不建議使用
binlog_do_db #指定只記錄哪些數據庫的相關修改操作到二進制日志文件中，可以指定一個列表，使用逗號隔開 binlog_ignore_db #指定只忽略哪些不記錄，其他都記錄，可以指定一個列表，使用逗號隔開從服務器SQL_THREAD在replay中繼日志中的事件時，僅讀取與特定數據庫（特定表）相關的事件并應用與本地；但是會造成網絡及磁盤io浪費
replicate_do_db= #該處指定的數據庫的相關事件都要進行復制 replicate_ignore_db= replicate_do_table= replicate_ignore_table= replicate_wild_do_table= #在指定表的時候可以使用通配符 replcate_wild_ignore_table=- 示例：復制時只復制MYDB數據庫的相關事件
#在從服務器上進行的操作 MariaDB [(none)]> SET GLOBAL replicate_do_db='MYDB'; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%replicate%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | replicate_annotate_row_events | OFF | | replicate_do_db | MYDB | | replicate_do_table | | | replicate_events_marked_for_skip | replicate | | replicate_ignore_db | | | replicate_ignore_table | | | replicate_wild_do_table | | | replicate_wild_ignore_table | | +----------------------------------+-----------+ MariaDB [(none)]> SHOW SLAVE STATUS\G;
網站標題：mysql主從復制架構及實現(xiàn)
當前路徑：http://ef60e0e.cn/article/gciiec.html
