MYSQL数据库主从同步(包含MariaDB)

近日因为项目的需要,需要将MYSQL数据在两台机器之间进行同步。场景如下:

有一个后台服务器和前台服务器A,前台服务器B,两台服务器均为LINUX系统,其中

服务器A的数据库是MYSQL5.5.44 服务器

服务器B的数据库是MariaDB 10.0.29

将服务器A中的数据库数据(指定了某一个库),实时同步至服务器B中。

同时服务器B只是只读表,不参与任何写操作。(我看网上有些时候写一些配置都是AB之间互相同步和写入,这样太复杂容易出错,当然我这里没有这个需求)

因此根据主从的划分,服务器A为主数据库,服务器B为从数据库,根据A的变化B做数据更新

主数据库配置

编辑服务器A中的my.cnf 文件,在 [mysqld] 下添加如下配置

  1. log-bin          = binlog #开启binlog功能
  2. log-bin-index    = binlog.index
  3. sync_binlog      = 0
  4. server_id        = 1 #两台机器不能重复,一个1 一个2 就好
  5. binlog-do-db     = test #指定只允许某个数据库复制功能

重启数据库。

在服务器A的数据库总为数据库添加一个用户,用于从数据库访问,如下:

  1. GRANT replication slave ON *.* TO 'test'@'%' identified by 'test';
  2. flush privileges;

这里我之前犯了一个错误,以为可以设置为 ON table_name.* 这样设置,后来SQL一直报错,根据提示查看了手册说明后才知道,当执行Grant replication 时只能设置为全局。

如果需要单独指定某一个库的话,可以在my.cnf里配置。

接下来是查看当前主数据库的MASTER信息,如下命令:

  1. flush tables with read lock; ‘先锁表,然后执行下面的命令
  2. show master status;

主要记住的参数为 File 和 Position
File 主要用于未来配置从数据库中的参数 master_log_file 的值
Position 主要用于配置从数据库中的参数 master_log_pos 的值

记录后执行SQL来解开前面锁

  1. unlock tables;

到这里主服务器的配置结束。

从数据库配置

从数据库配置,我的数据库是 MariaDB的数据库,ubuntu环境,配置文件的位置比较坑,通过APT安装的位置在:

  1. /etc/mysql/mariadb.conf.d/50-server.cnf

进入后在[mysqld]添加如下内容:

  1. log-bin         = binlog
  2. log-bin-index   = binglog.index
  3. sync_binlog     = 0
  4. server-id       = 2 #这与主数据库的值绝对不能重复
  5. replicate-do-db = news #这里可以设置你只想同步的库

重启数据库

然后执行以下SQL语句来配置从数据库的slave信息

  1. change master to master_host='xxx.xxx.xxx.xxx',master_port=3306,master_user='test',master_password='test',master_log_file='binlog.000004',master_log_pos=78039991;

其中:
master_host 是主数据库机器的IP
master_port 是数据库的端口号
master_user 同步时登录用的用户名
master_password 用户密码
master_log_file 日志编号,就是对应我们前面查看的内容
master_log_pos 位置编号,就是赌赢我们前面查看的内容

SQL成功执行后,执行如下SQL命令,启动从服务

  1. start slave;

我们可以通过如下SQL命令来查看同步服务器是否正常

  1. show slave status;

执行后会看到如下内容(内容是我COPY别人的,看看就行了):

  1. mysql> show slave status \G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.167.1.1
  5.                   Master_User: root
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000004
  9.           Read_Master_Log_Pos: 28125
  10.                Relay_Log_File: VM_128_194_centos-relay-bin.000004
  11.                 Relay_Log_Pos: 26111
  12.         Relay_Master_Log_File: mysql-bin.000004
  13.              Slave_IO_Running: Yes  #主要关注这两个状态
  14.             Slave_SQL_Running: Yes  #主要关注这两个状态
  15.               Replicate_Do_DB: ufind_db
  16.           Replicate_Ignore_DB: mysql
  17.            Replicate_Do_Table:
  18.        Replicate_Ignore_Table:
  19.       Replicate_Wild_Do_Table:
  20.   Replicate_Wild_Ignore_Table:
  21.                    Last_Errno: 0
  22.                    Last_Error:
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 28125
  25.               Relay_Log_Space: 26296
  26.               Until_Condition: None
  27.                Until_Log_File:
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File:
  31.            Master_SSL_CA_Path:
  32.               Master_SSL_Cert:
  33.             Master_SSL_Cipher:
  34.                Master_SSL_Key:
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error:
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error:
  41.   Replicate_Ignore_Server_Ids:
  42.              Master_Server_Id: 2
  43.                   Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd
  44.              Master_Info_File: /data/mysqldb/master.info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind:
  50.       Last_IO_Error_Timestamp:
  51.      Last_SQL_Error_Timestamp:
  52.                Master_SSL_Crl:
  53.            Master_SSL_Crlpath:
  54.            Retrieved_Gtid_Set:
  55.             Executed_Gtid_Set:
  56.                 Auto_Position: 0
  57. 1 row in set (0.00 sec)
  58.  
  59. ERROR:
  60. No query specified

如果均为YES的话表明从服务已经正常运行了。

到这里主从的服务器已经正常运作了。

当然这里补充一下,如果你的主数据库已经有表和数据存在,那么第一次要先通过dump导出已有数据存入从数据库中,不然从数据库会一直报错找不到数据。

如果你是先搭建主从关系的服务后,再创建库和表添加数据等,从数据库就会自动同步了。



发表评论?

1 条评论。

  1. 猜猜我是谁?

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

无觅相关文章插件,快速提升流量