# Backup Function

goInception support backup, at config.toml(at [inc] block)

parameters default type description
backup_host "" string Backup database ip address
backup_port 0 int Backup database port
backup_user "" string username to connect backup database
backup_password "" string password to connect backup database
backup_tls v1.4.0 "" string Backup database ssl authentication method, please refer to the optional values (https://github.com/go-sql-driver/mysql/issues/899#issuecomment-443493840)

Add --backup=true option when execute SQL

/*--user=root;--password=root;--host=127.0.0.1;--port=3306;--execute=1;--backup=1;*/
inception_magic_start;
use test;
create table t1(id int primary key);
inception_magic_commit;

# Backup Record Format

  • backup database naming format: IP_PORT_{dbname}, eg: 127_0_0_1_3306_test

  • create backup information table on backup schema $_$Inception_backup_information$_$ to save execute SQL and rollback SQL

    Column Type Comment
    opid_time varchar(50) operation ID, formatting {timestamp}_{thread_id}_{operation_id}
    start_binlog_file varchar(512) binlog start filename
    start_binlog_pos int(11) binlog start position
    end_binlog_file varchar(512) binlog end filename
    end_binlog_pos int(11) binlog end position
    sql_statement text SQL execute
    host varchar(64) which IP address sql execute
    dbname varchar(64) which schema sql execute
    tablename varchar(64) which table sql execute
    port int(11) which port sql execute
    time timestamp when execute
    type varchar(20) execution type
  • the table in the backup database has the same table as name of execution table, table structure as blow.

    Column Type Comment
    id bigint Auto_increment primary key
    rollback_statement mediumtext rollback SQL
    opid_time varchar(50) operation ID related

# Backup Process Details

  1. Config backup database and turn on backup function before executing SQL.
  2. Record binlog position and threadID before executing SQL one by one.
  3. Execute SQL.
  4. Record binlog position and threadID after executed SQL.
  5. Start backup, decode binlog on remote server.
  6. Create backup database on backup server.
  7. Create backup information table, record execution information and binlog position.
  8. Create backup table.
  9. Decode binlog, build rollback SQL and then insert into backup information table.