MySQL主从同步配置及相关坑

主

[mysqld]

# 主从同步主配置开始 #
server-id=1             # 节点ID，确保唯一
log-bin=master-bin      # 开启mysql的binlog日志功能
sync_binlog = 1         # 控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   # binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7    # binlog过期清理时间
max_binlog_size = 100m  # binlog每个日志文件大小
binlog_cache_size = 4m  # binlog缓存大小
max_binlog_cache_size= 512m     # 最大binlog缓存大

auto-increment-offset = 1       # 自增值的偏移量
auto-increment-increment = 1    # 自增值的自增量
slave-skip-errors = all         # 跳过从库错误
log_bin_index=master-bin.index

binlog_do_db=table2           ##table2是要同步的数据库的名称
binlog_do_db=table3  		  ##table3是要同步的数据库的名称

binlog-ignore-db=mysql  #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行

user=mysql
# 主从同步主配置结束 #


# 设置端口
port=3306
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# datadir=/var/lib/mysql
datadir=/data/mysql/data/mysql
# socket=/var/lib/mysql/mysql.sock
socket=/data/mysql/data/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8

init_connect='SET NAMES utf8'

# 大小写
lower_case_table_names=1

# innodb_force_recovery=0

#最大连接数
max_connections=1000

从

[mysqld]

# 主从同步从配置开始 #
server-id=2					# 节点ID，确保唯一
log-bin=slave-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
# 主从同步从配置结束 #

# 设置端口
port=3307
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# datadir=/var/lib/mysql
datadir=/data/mysqldata/mysql
# # socket=/var/lib/mysql/mysql.sock
socket=/data/mysqldata/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8

init_connect='SET NAMES utf8'


# 大小写
lower_case_table_names=1



[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3307
socket=/data/mysqldata/mysql/mysql.sock

改好配置后分别重启数据库

主库执行

-- 查询主服务器状态
show master status;

从库：

mysql> stop slave; 
Query OK, 0 rows affected (0.03 sec)

mysql> change master to master_host='192.168.0.152',master_user='root',master_password='12345',master_port=3306,master_log_file='master-bin.000009',master_log_pos=7616;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.0.152
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000009
          Read_Master_Log_Pos: 7616
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000009
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 7616
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0

说明：192.168.0.152是主服务器的id，master_log_file='master-bin.000009’是主服务器的File（你主服务器查出来的是什么就写什么），master_log_pos=7616是主服务器的Position（你主服务器查出来的是什么就写什么）；
每次重新启动主服务器，master_log_file和master_log_pos都会变。
————————————————
版权声明：本文为CSDN博主「qq_43154385」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_43154385/article/details/88838202

遇到的坑：

主从数据库大小写不统一造成的错误

只同步某一个库，或者是某一张表，或者是不同步某一个库，不同步某一张表，改怎么设置呢？ 以上说的都能实现，参考MySQL官网：http://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html
举例忽略不同步某一张表(db1.mytable)：

mysql> stop slave;
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db1.mytable);
mysql> start slave;

————————————————
版权声明：本文为CSDN博主「yinnnnnnn」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_35440678/article/details/52860600