Centos6.5 Mysql主从同步备份操作记录

最新推荐文章于 2021-02-06 15:35:32 发布

晓彬哟

最新推荐文章于 2021-02-06 15:35:32 发布

阅读量565

点赞数

分类专栏：工作问题记录文章标签：数据库备份 centos mysql

本文链接：https://blog.csdn.net/Xanthin/article/details/78532218

版权

工作问题记录专栏收录该内容

7 篇文章 0 订阅

订阅专栏

因项目需求，需要做一个数据库备份。于是从网上查了很多资料。最终通过执行数据库脚本来完成两个数据库之间的连接。为了巩固知识，在这里记录下实际操作以及操作时遇到的问题。

数据库主从复制的优点：
1、如果主服务器出现问题，可以快速切换到从服务器提供的服务，保证高可用性
2、可以在从服务器上执行查询操作，降低主服务器的访问压力
3、可以在从服务器上执行备份，以避免备份期间影响主服务器的服务

注意事项：
1、server-id必须唯一，一般使用ip的后三位
2、修改完my.cnf配置需要重启数据库服务service mysqld restart

操作系统：centos6.5
Mysql版本：5.6.24
主数据库ip：192.168.130.131
从数据库ip：192.168.130.132

一、配置主服务器
1、在主服务器上，设置一个从数据库的账户，使用REPLICATION SLAVE（从复制）赋予权限

mysql>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.130.132' IDENTIFIED BY '123456';

2、修改主数据库的配置文件my.cnf，开启bin-log，并设置server-id的值，修改之后必须重启mysql服务

[mysqld]
server-id=131  #主机标示，整数
log-bin=/var/log/mysql/mysql-bin.log  #确保此文件可写，开启bin-log
read-only=0  #主机，读写都可以
binlog-do-db=test   #需要备份的数据库，多个写多行
binlog-ignore-db=mysql #不需要备份的数据库，多个写多行
#可以通过mysql>show variables like 'log_%'; 验证二进制日志是否已经启动。

成功之后的展示如图所示：
这里写图片描述

3、查看主服务器master状态，查看当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复。

mysql> show master status;

成功之后的展示如图所示：
这里写图片描述

二、配置从服务器

1、修改从数据库的配置文件my.cnf

[mysqld]
server-id=132 #设置从服务器id，必须与主服务器不同
log-bin=/var/log/mysql/mysql-bin.log  #确保此文件可写，开启bin-log
replicate-do-db=test   #需要同步的数据库，多个写多行
replicate-ignore-db=mysql #不需要同步的数据库，多个写多行

2、远程导入主数据库的数据

#将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2
#加上-C参数可以启用压缩传递
mysqldump --host=192.168.130.131 -ubackup -p123456 -C --databases test |mysql --host=192.168.130.132 -uroot -proot test

3、配置主从同步

mysql>change master to
    ->master_host='192.168.130.131',
    ->master_user='beckup',
    ->master_password='123456',
    ->master_log_file='mysql-bin.000001',
    ->master_log_pos=524;

#log_file与log_pos是主服务器master状态下的File与Position

4、启动主从配置

mysql> start slave;

#验证配置结果
mysql>show slave status\G;

#配置结果
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.130.131
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 6982
               Relay_Log_File: VM_bl_beifen_centos-relay-bin.000002
                Relay_Log_Pos: 6965
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           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: 6982
              Relay_Log_Space: 7152
              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: 131
                  Master_UUID: 6238decc-93a9-11e7-9b11-52540079877f
             Master_Info_File: /mnt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

ERROR:
No query specified

配置结果重点关注提示
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都为Yes，则说明配置成功！

5、查看bin-log日志

mysql>show binlog events\G;

#正常显示结果
*************************** 12. row ***************************
   Log_name: mysql-bin.000001
        Pos: 985
 Event_type: Query
  Server_id: 1
End_log_pos: 1075
       Info: use `test`; delete from user where id=2
*************************** 13. row ***************************
   Log_name: mysql-bin.000001
        Pos: 1075
 Event_type: Query
  Server_id: 1
End_log_pos: 1175
       Info: use `test`; alter table user add name varchar(50)