mysql主从复制

23113fff

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;