非GTID模式MySQL主从同步配置

预设：

主服务器IP为：192.168.0.12，简称A服务器

备服务器IP为：192.168.0.13，简称B服务器

由于主数据库版本是mariadb5.5.47版本，所以只能用非GTID模式搭建主从数据库服务器进行数据同步。具体步骤如下：

在A服务器打开MySQL的配置文件my.cnf

[root@localhost nor]# vi /etc/my.cnf

具体配置文件如下：

[mysqld]


# add by nor
log-bin = /var/log/mariadb/mysql-bin  #开启二进制日志

sync_binlog = 1<span style="white-space:pre">	</span>#设置日志同步方式，根据个人需要对值进行不同设置

# innodb_flush_logs_at_trx_commit = 2  # Flush every log write  
innodb_support_xa = 1 # MySQL 5.0 and newer only
# innodb_safe_binlog  #MySQL 4.1 only, roughly equivalent to innodb_support_xa

## master config beload
server-id = 1   #服务器ID，要求每个服务器的ID不能相同


## slave config beload
# relay_log = /var/log/mariadb/relay-bin
# log_slave_updates = 1
# read_only = 1
# skip_slave_start
# sync_master_info = 1
# sync_relay_log = 1
# sync_relay_log_info = 1
# replicate-wild-ignore-table =mysql.%
# replicate-wild-ignore-table =test.%
# replicate-wild-ignore-table =log.%
# replicate-wild-ignore-table =information_schema.%
# replicate-wild-ignore-table =performance_schema.%



#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

配置完成后保存文件，然后重启mariadb服务。

[root@localhost nor]# systemctl restart mariadb.service

重启之后，进入mysql控制台命令行：

[root@localhost nor]# mysql -uroot -p

进入控制台后输入：

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000004
        Position: 245
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

这时记住得到的File名称为：mysql-bin.000004，Position为245

这里对于A服务器的配置已经完成。

接下来对B服务器，也就是备用服务器进行配置。

注意：备服务器的MySQL或者mariadb的版本一定不能比主服务器的版本低，最好是和主服务器版本一样。我自己的是MySQL5.6的，所以这也是没有问题的。

首先打开B服务器的配置文件my.cnf，添加一些参数设置：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# 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
socket=/var/lib/mysql/mysql.sock

# add by connor
log-bin = /var/lib/mysql/mysql-bin

sync_binlog = 1

# innodb_flush_logs_at_trx_commit = 1  # Flush every log write
innodb_support_xa = 1 # MySQL 5.0 and newer only
# innodb_safe_binlog  #MySQL 4.1 only, roughly equivalent to innodb_support_xa

## master config beload
server-id = 2


## slave config beload
relay_log = /var/lib/mysql/relay-bin
log_slave_updates = 1
read_only = 1
skip_slave_start
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
replicate-wild-ignore-table =mysql.%
replicate-wild-ignore-table =test.%
replicate-wild-ignore-table =log.%

保存配置后重启数据库服务器，进入MySQL控制台

mysql> show slave status\G

可以看见以下两项：

Slave_IO_Running: No
Slave_SQL_Running: No

说明同步还没有开启，可以通过输入命令开启：

mysql> start slave;

再次使用show slave status，如果以上两项变为Yes就说明从库已经开启了同步，这时只需要在主服务器A进行一些增删改来测试两服务器之间是否可以同步。