mysql主從複製

標籤：mysql

mysql主從複製
 1、基本主從伺服器配置
  (1)主伺服器配置
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
user = mysql
innodb_buffer_pool_size = 128M
log_bin = master-log  ===========
max_binlog_size = 64M
binlog_format = mixed   ========
basedir = /usr/local/mysql
datadir = /database/mydata
port = 3306
server_id = 1  ==========
socket = /tmp/mysql.sock
log_error = /database/mydata/server1.err
character_set_server = utf8
explicit_defaults_for_timestamp = on
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
   
資料庫初始化
 ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata
 
 給從伺服器授權
 mysql> grant replication slave on *.* to ‘repuser‘@‘10.0.5.151‘ identified by ‘aixocm‘;
 mysql> flush privileges;
 mysql> show master status\G
 (2)從伺服器配置
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
user = mysql
innodb_buffer_pool_size = 128M
log_bin = slave-log  ============
max_binlog_size = 64M
log_slave_updates = on =====
binlog_format = mixed  =====主從伺服器才需要這兩項，從伺服器不需要
relay_log = relay-bin  ============
basedir = /usr/local/mysql
datadir = /database/mydata
port = 3306
server_id = 2  ======
socket = /tmp/mysql.sock
log_error = /database/mydata/server1.err
character_set_server = utf8
explicit_defaults_for_timestamp = on
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
 
     資料庫初始化
 # ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata
 
 設定主伺服器相關資訊
 mysql> change master to master_host=‘10.0.5.150‘,master_user=‘repuser‘,master_password=‘aixocm‘,master_log_file=‘master-log.000003‘,master_log_pos=409,master_port=3306;
 mysql> start slave;
 mysql> show slave status\G
 mysql> stop slave;
 
 (3)二進位日誌操作  
 # mysqlbinlog master-log.000001
  # mysqlbinlog --start-datetime=‘2015-07-13 7:10:0‘ master-log.000003
  # mysqlbinlog --start-datetime=‘2015-07-13 7:10:0‘ --stop-datetime=‘2015-07-13 7:33:0‘ master-log.000003
  # mysqlbinlog --start-position=330 master-log.000003
  # mysqlbinlog --start-position=330 --stop-position=1100 master-log.000003
  # mysqlbinlog --start-position=330 --stop-position=1100 master-log.000003 | mysql -u root \\恢複指定二進位日誌的內容
 
  mysql> show master logs;
  mysql> show binary logs;
 
  mysql> show binlog events in ‘master-log.000003‘ limit 20;
  mysql> show binlog events in ‘master-log.000003‘ from 409 limit 10;
 
  mysql> purge master logs to ‘master-log.000002‘;  \\刪除指定編號之前的日誌
  mysql> purge master logs before ‘2015-07-22 08:00:00‘;
   (4)設定忽略的資料庫
    主伺服器設定
    binlog_do_db = sxjy           \\記錄二進位日誌的資料庫
    binlog_ignore_db = test       \\不記錄二進位日誌的資料庫
    binlog_ignore_db = teach
   
    從伺服器設定
    replicate_do_db = sxjy        \\設定預設進行二進位日誌複製的資料庫
    replicate_ignore_db = test    \\不進行二進位日誌複製的資料庫
    replicate_ignore_db = teach
    replicate_ignore_db = mysql
    replicate_ignore_db = information_schema
    replicate_do_table = sxjy.stu         \\設定進行更新的表
    replicate_ignore_table = sxjy.class   \\不進行更新的表
 
 
 
 從伺服器建議設定
    replicate_ignore_db = test            \\不進行二進位日誌複製的資料庫
    replicate_ignore_db = mysql
    replicate_ignore_db = information_schema
    replicate_wild_do_table = sxjy.stu    \\複製指定的資料庫或表的二進位日誌
    replicate_wild_do_table = sxkj.%
    replicate_wild_ignore_table = sxjy.class \\不複製指定的資料庫或表的二進位日誌
   
 練習:先搭建一台mysql資料庫伺服器,不啟用二進位日誌,然後啟動伺服器，先建立一個sxjy資料庫,
     在sxjy資料庫下建立stu和teach兩個表,欄位自己添加3-4個,然後插入4-5個記錄。然後將
     伺服器改為主從結構,要求原先的伺服器做主伺服器，且在從伺服器上要有主伺服器上原來的資料,
     主從結構搭建好後,以後添加的資料能夠自動同步到從伺服器,主從伺服器資料要完全一致。
         2、基於GTID的事務複製
   (1)能夠基於資料庫進行多線程複製(要求2個或2個以上的資料庫進行同步)
   (2)能夠自動判斷要複製的位置
   (3)不能支援非事務儲存引擎,如MyISAM
   (4)不支援create table ... select from ... 語句
   
   
   主伺服器配置
   log_slave_update = on
   gtid_mode = on   \\開啟GTID模式
   enforce_gtid_consistency = on      \\強制GTID的一致性
   master_info_repository =TABLE      \\主伺服器資訊的記錄方式(TABLE或FILE)
   relay_log_info_repository = TABLE  \\中繼日誌資訊的記錄方式
   sync_master_info = 1               \\同步主要資料庫資訊
   slave_parallel_workers = 4         \\從伺服器的sql線程數,和要複製的資料庫相同
   binlog_checksum =CRC32             \\二進位日誌的校正方式
   master_verify_checksum = 1         \\主伺服器啟用校正
   slave_sql_verify_checksum = 1      \\從伺服器啟用校正
   binlog_rows_query_log_events = 1   \\二進位日誌詳細記錄事件
   report_port = 3306                 \\提夠複製的報告連接埠,和資料庫連接埠一致
   report_host = 10.0.5.150           \\提供複製報告的主機,設為本機地址
   
   mysql> grant replication slave on *.* to ‘repuser‘@‘10.0.5.151‘ identified by ‘aixocm‘;
   mysql> flush privileges;
 從伺服器配置
   log_slave_update = on
   gtid_mode = on   \\開啟GTID模式
   enforce_gtid_consistency = on      \\強制GTID的一致性
   master_info_repository =TABLE      \\主伺服器資訊的記錄方式(TABLE或FILE)
   relay_log_info_repository = TABLE  \\中繼日誌資訊的記錄方式
   sync_master_info = 1               \\同步主要資料庫資訊
   slave_parallel_workers = 4         \\從伺服器的sql線程數,和要複製的資料庫相同
   binlog_checksum =CRC32             \\二進位日誌的校正方式
   master_verify_checksum = 1         \\主伺服器啟用校正
   slave_sql_verify_checksum = 1      \\從伺服器啟用校正
   binlog_rows_query_log_events = 1   \\二進位日誌詳細記錄事件
   report_port = 3306                 \\提夠複製的報告連接埠,和資料庫連接埠一致
   report_host = 10.0.5.151           \\提供複製報告的主機,設為本機地址
   
   mysql> change master to master_host=‘10.0.5.150‘,master_user=‘repuser‘,
          master_password=‘aixocm‘,master_auto_position=1;
   mysql> start slave;
   mysql> show processlist\G
   mysql> show status like ‘thread%‘;
   
   
  slave_skip_errors = all
   slave_skip_errors = 1062,1756,2003
    3、雙主複製
   第一台伺服器設定
   auto_increment_increment = 2  \\資料表記錄的自增量,一般等於伺服器的數量
   auto_increment_offset = 1     \\資料表記錄每次的遞增量,第一台為1,第二台為2,...
   sync_binlog = 0               \\二進位日誌寫入磁碟的方式
   replicate_same_server_id = 0  \\防止mysql迴圈更新
   
   mysql> grant replication slave on *.* to ‘repuser‘@‘10.0.5.151‘ identified by ‘aixocm‘;
   mysql> flush privileges;
   mysql> change master to master_host=‘10.0.5.151‘,master_user=‘repuser‘,
          master_password=‘aixocm‘,master_auto_position=1;
   mysql> start slave;
         

本文出自 “kenasel” 部落格，請務必保留此出處http://kenasel.blog.51cto.com/10620829/1839522

mysql主從複製