mysql 主备配置

一、主从备份的原理

主服务器数据库的每次操作都会记录在二进制日志文件mysql-bin.xxx中

从服务器的I/O线程使用专用帐号登陆到主服务器中读取该二进制文件，并将文件内容写入到自己本地的中继日志relay-log文件中，然后从服务器的SQL线程会根据中继日志中的内容执行SQL语句。

这要求两台服务器有同样的初态。

 

二、同步初态

1、将主服务器要同步的数据库加锁，避免同步时发生改变

use test;
flush tables with read lock;

2、使用mysqldump工具导出数据

mysqldump -uroot -pxxx test >test.sql

3、备份完成后，解锁数据库

unlock tables;

4、将初始数据导入从数据库

create database test;
use test;
source test.sql;

 完成以上操作后，主从服务器就有一样的初态了

 

三、主从同步设置

1，主库配置

#my.cnf
[mysqld]

server-id=1
log-bin=log
binlog-do-db=mstest      #要同步的mstest数据库,要同步多个数据库，就多加几个replicate-db-db=数据库名
binlog-ignore-db=mysql   #要忽略的数据库

#重启mysql server 
systemctl restart mysqld

#创建允许从服务器同步数据的账户
grant replication slave on *.* to 'slave'@'%' identified by 'slave';

show master status\G;

***************** 1. row ****************
            File: mysql-bin.000010       #当前记录的日志
        Position: 73608755               #日志中记录的位置
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

2、从数据库配置

#my.cnf
[mysqld]

server-id=2
read_only=1 #配置从库只读

配置主从复制：

CHANGE MASTER TO 
 MASTER_HOST='192.168.1.190',
 MASTER_USER='slave',
 MASTER_PASSWORD='slave',
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=73608755;

start slave;
show slave status\G;

结果：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.190
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 73608755
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes    #yes说明主从正常
            Slave_SQL_Running: Yes　　#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: 73608755
              Relay_Log_Space: 528
              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: 419e5b78-528c-11e8-b7cd-00155d01c508
             Master_Info_File: /var/lib/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)

 

四，主从切换

1. 修改配置文件/etc/my.cnf

read-only=1（主库添加）
#read-only=1（备库注释）

从库上执行

STOP SLAVE IO_THREAD;
SHOW PROCESSLIST;

system user 线程确保状态为：has read all relay log 或者消失

再执行：

STOP SLAVE;
RESET MASTER;
RESET SLAVE;
show master status \G;

2.原主库上执行

RESET MASTER;
RESET SLAVE;

CHANGE MASTER TO 
MASTER_HOST='192.168.1.212',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=106;

start slave

3 ，重启主备库

注意：先重启新的主库，再启动新的备库

service mysqld restart