非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 norlog-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 beloadserver-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 = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# add by connorlog-bin = /var/lib/mysql/mysql-binsync_binlog = 1# innodb_flush_logs_at_trx_commit = 1  # Flush every log writeinnodb_support_xa = 1 # MySQL 5.0 and newer only# innodb_safe_binlog  #MySQL 4.1 only, roughly equivalent to innodb_support_xa## master config beloadserver-id = 2## slave config beloadrelay_log = /var/lib/mysql/relay-binlog_slave_updates = 1read_only = 1skip_slave_startsync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1replicate-wild-ignore-table =mysql.%replicate-wild-ignore-table =test.%replicate-wild-ignore-table =log.%

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

mysql> show slave status\G

可以看见以下两项：

Slave_IO_Running: NoSlave_SQL_Running: No

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

mysql> start slave;

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