xiaolingzi's blog

每天都在成长...

欢迎您:亲

Linux中mysql主从镜像实时同步备份

xiaolingzi 发表于 2016-03-07 16:20:33

数据的容灾对任何互联网产品来说都是非常重要的,因为一旦出现数据丢失,后果不堪想象。数据不重要的简单做法有手动的定时备份,高级的现在使用的云盘都已经有灾备方案。对于普通服务器来说,数据的实时备份还是很重要的。

今天我们来看看mysql的主从备份的实现。

关于mysql数据库的安装这里就不说了,假设我们两台服务器都已经安装了mysql,主服务器已经有数据库数据。由于mysql5.0以下版本的配置会有些差异,这里说的都是针对mysql5.0以上的版本。

一、主数据库配置

1. 登录mysql,创建备份账号给从服务器用。sql脚本如下:

GRANT SUPER,REPLICATION SLAVE ON *.* TO 'db_slave'@'192.168.0.22' IDENTIFIED BY '123456';

说明

(1)super和replication为要给的权限,replication是必须的,super是需要监控主从的话才需要加上;

(2)db_slave为账号名;

(3)123456为密码

(4)192.168.0.22为从服务器ip。

各参数具体使用时请根据自身情况进行修改。

2.配置my.conf文件

CentOS通过yum安装的话,my.conf文件在/etc目录下。修改内容如下:
在[mysqld]节点下添加以下内容
#id,服务器的唯一标识,不能重复
server-id=1
#开启bin-log,并指定日志存储路径,从服务器通过它进行实时的同步工作
#注意:日志的目录需要先建立,并将所有者该为mysql
log-bin = /mnt/mysql_data/log-bin/mysql-bin.log
#要排除的的数据,这些数据库不会被同步,多个数据库以多行进行配置
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#要同步的数据库,多个数据库以多行进行配置。如果该项不配置,那么就是排除之后的所有数据库。
binlog-do-db = testdb1
binlog-do-db = testdb2
#开启该选项之后,如果该服务器还从其他服务器进行同步的话也一样会写入二进制log中,不开启的话就只记录自己机器上的数据库操作。这在链式备份上比较常用。开启就行。
log-slave-updates=1
#日志的过期时间,以下表示只保留2天内的log,如果不需要则不用配置
expire_logs_day=2
#日志文件的格式,有以下三种格式(只配其中一个),各种格式的差别请自行Google。
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"
#日志文件的大小,超过这个大小则以新的日志文件进行存储
max_binlog_size = 500M
#日志缓冲大小,日志一般不会直接写入文件,会先缓存在内存中,但满足设置的大小及其他条件时才写入文件中
binlog_cache_size = 128K


3.重启服务以生效

CentOS6.x及5.x

service mysqld restart

CentOS7.x

systemctl restart mysqld

其它linux版本使用相应的命令


二、从数据库配置

1. 配置my.conf文件

在[mysqld]节点下添加以下内容

#id,唯一,不能与主数据库相同
server-id=2
#从数据库从主数据库中同步过来的日志存储在该位置中,另外的线程再将日志中的数据同步到数据库
#注意:日志的目录需要先建立,并将所有者该为mysql
relay_log = /mnt/mysql_data/relay-log/relay-bin
#日志索引文件
relay-log-index = /mnt/mysql_data/relay-log/relay-bin.index
#记录日志的相关信息文件
relay-log-info-file = /mnt/mysql_data/relay-log/relay-log.info
#开启该选项之后,如果该服务器还从其他服务器进行同步的话也一样会写入二进制log中,不开启的话就只记录自己机器上的数据库操作。这在链式备份上比较常用。开启就行。
log_slave_updates = 1
#只读设置,从数据库只能读不能写,因为是单向主从配置
read_only = 1
#忽略掉的错误,都是些不太紧要的错误
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

2.重启服务以生效


三、数据同步

1. 首次数据同步

如果两台均为新数据库无数据则该步可以省略。如果主数据库已经存在数据,则需要手动同步一下现有数据。

(1)开启只读开关。为了保持数据一致,在备份数据先将数据库只读开关开启。在my.conf中[mysqld]节点加入:

read_only = 1

然后重启mysql服务以生效。

(2)备份数据库。备份要同步的数据库,在主服务器控制台执行备份命令,如下:

mysqldump -uroot -p mydb > /home/xxx/Dump20160307.sql;

数据库的账户密码、数据库名称以及备份名称请根据自身情况自行修改。

(3)还原数据库。将备份文件上传到从服务器,在从服务器中执行还原命令(执行前先将数据库建立,否则会报未知数据库错误),如下:

mysql -h127.0.0.1 -uroot -p  haviea < /home/xxx/Dump20160307.sql;

注意:我们在从数据库中配置了read_only = 1,在此步操作时记得先临时注释掉。

2. 查看主数据库bin-log信息

登录主数据库,执行以下命令:

show master status;

得到的结果如下:

+------------------+----------+--------------+--------------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |

+------------------+----------+--------------+--------------------------+-------------------+

| mysql-bin.000002 |      154 |              | mysql,information_schema |                   |

+------------------+----------+--------------+--------------------------+-------------------+

1 row in set (0.00 sec)


3. 从数据库执行同步

(1)执行命令如下:

stop slave; 
change master to
master_host='192.168.0.21',
master_user='db_slave',
master_password='Db&slave123',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos=154;
start slave;


其中master_log_file和master_log_pos为上面第2步中的,File和Position列对应的内容。

(2)查看同步情况

执行以下命令查看:

show slave status\G;

显示结果如下(版本为mysql5.7,其它版本可能会有差异)

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.21
                  Master_User: db_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 612
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 778
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 612
              Relay_Log_Space: 979
              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: 1
                  Master_UUID: 6ec37afa-db9a-11e5-a39e-00163e004dc4
             Master_Info_File: /mnt/mysql_data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

如果以下属性状态一致,则说明同步成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master_Log_File: mysql-bin.000002

Relay_Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 612

Exec_master_log_pos: 612

Seconds_Behind_Master: 0

(3)恢复主数据库可写操作

在备份主数据库我们将数据库改为了只读状态,所以同步工作配置完不要忘了改回来。

注释或者去掉my.conf中的

read_only = 1

然后重启mysql服务以生效。

(4)测试同步

手动将主数据库的数据进行修改或者添加新数据,如果从数据库同步所做修改说明同步没有问题。

      

转载请注明出处:http://www.xxling.com/article/2095.aspx

  • 分类: mysql
  • 阅读: (2586)
  • 评论: (0)
拍砖 取消
请输入昵称
请输入邮箱
*
 选择评论类型
300字以内  请输入评论内容