MySQL5.6 数据库主从服务器配置

安装环境

操作系统：CentOS 6.8
数据库版本：MySQL 5.6
主机A：192.168.0.139 （Master）
主机B：192.168.0.108 （Slave）

环境配置

service iptables stop 
setenforce 0

Master主机配置

vim /etc/my.cnf

log-bin=mysql-bin                    # 启动binlog,必须要启用
server-id=2                          # 服务ID唯一，
binlog-ignore-db=mysql               # 设置不需要同步的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

binlog-do-db=school

# 重启数据库库
service mysqld restart
mysql -u root -p
mysql> grant replication slave on *.* to 'mysync'@'%' identified by 'Mysync@123';
# 创建mysync权限用户，密码12345678 添加replication slave权限，*.* 表示所有数据库所有表，% 表示任意主机

# 显示主服务器状态
mysql> show master status;

+------------------+----------+--------------+----------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000004 |      120 | school       | information_schema,cluster,mysql |                   |
+------------------+----------+--------------+----------------------------------+-------------------+
1 row in set (0.00 sec)

# 记录 File 的值 mysql-bin.000004,Position 的值 120；从服务器需要用到 

Slave主机配置

vim /etc/my.cnf

log-bin=mysql-bin                       # 从库binlog可以不启用
server-id=3                             # 服务ID唯一
replicate-do-db=school                  # 设置需要同步的数据库名
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

log-slave-updates=1                     # 启用从机服务器上的slave日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)，可以让从库上面产生二进制日志文件，在从库上再挂载一个从库

# 重启数据库库
service mysqld restart  
mysql -u root -p

mysql> change master to
     > master_host='192.168.0.139',master_user='mysync',master_password='Mysync@123',
     > master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=120;

# 在从服务器配置需要同步的主服务器 主机IP，用户名，密码，端口号,binlog文件binlog位置等信息

mysql> start slave;            # 启动slave
mysql> show slave status\G     # 查看从服务器配置信息

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.139             # 主服务器IP
                  Master_User: mysync                    # 主服务器用户
                  Master_Port: 3306           
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004          # 主库上的二进制文件名
          Read_Master_Log_Pos: 120                       # 从库的IO进程，读取主库上的二进制日志的位置
               Relay_Log_File: mysqld-relay-bin.000009   # 中继日志二进制文件名
                Relay_Log_Pos: 283                       # 从库的sql进程，读取从库上的中继日志的位置
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes                       # 必须为yes
            Slave_SQL_Running: Yes                       # 必须为yes
              Replicate_Do_DB: school                    # 同步的数据库
          Replicate_Ignore_DB: mysql
           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: 120                       # 从库的sql线程执行到的位置，相对于主库
              Relay_Log_Space: 820
              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: 2
                  Master_UUID: 538c5e58-8cb6-11e9-b86a-080027972ea7
             Master_Info_File: /var/lib/mysql/master.info  # 此文件告诉主库下次重哪里开始读取
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

#上述信息 Slave_IO_Running: Yes，Slave_SQL_Running: Yes 表示同步成功

设置不同步的表

replicate-ignore-table=db_name.tbl_name

需要注意的是！！！ 
线上的项目导出数据的时候，保证主库上锁，等slave导入、start slave之后在解锁； 
slave重启的时候也一样，重启之前，主库上锁，重启完毕，解锁；

flush tables with read lock  #锁表
unlock tables  #解锁

清除日志

purge master logs to 'mysql-bin.000003';   # 清除mysql-bin.000003以前的日志

刷新日志

flush logs   # 自此刻开始产生一个新编号的binlog日志文件

主重跳过错误

stop slave;
set global sql_slave_skip_counter=1；
start slave;

MySQL 主主同步注意事项

原理与主从同步一样，（既是主又是从，既是从又是主）配置相关参数，额外增加 以下

server-1（IP：192.168.0.139）
relay-log=relay-mysql                # 启用中继日志
relay-log-index=relay-mysql.index    # 中继日志索引
auto-increment-increment=2           # 步进值auto_imcrement 一般有n台主MySQL就填n
auto-increment-offset=1              # 起始值，一般填第n台主MySQL，此时为第1台主MySQL

server-2（IP：192.168.0.108）
relay-log=relay-mysql
relay-log-index=relay-mysql.index
auto-increment-increment=2
auto-increment-offset=2              # 此时为第2台主MySQL

1、在两台服务器上互相创建具有复制权限的用户 grant replication slave on.... 以及配置 change master to……

2、两台机器分别运行 statr slave,显示YES，就可以同步成功

设置不需要同步的表

replicate-ignore-table=school.tables名

设置只同步哪些表

replicate_do_table=school.tables名

或通配符
replicate_wild_do_table=sch%.stu%

 如果主从数据库不同名

replicate-rewrite-db = school -> 从库数据库名