GoInception

https://hanchuanchuan.github.io/goInception/
Admin
GoInception is a MySQL maintenance tool, which can be used to review, implement, backup, and...
Tokens:17,467
Snippets:245
Trust Score:9.7
Update:3 months ago
Show doc for...
Tokens:
Raw
### Install pymysql and prettytable

Source: https://hanchuanchuan.github.io/goInception/demo.html

Install the necessary Python libraries for database interaction and table formatting.

```bash
pip install pymysql prettytable

```

--------------------------------

### Start goInception with Configuration

Source: https://hanchuanchuan.github.io/goInception/install.html

Run the compiled goInception executable, specifying the path to the configuration file.

```bash
./goInception -config=config/config.toml
```

--------------------------------

### Example SQL Query for Syntax Tree

Source: https://hanchuanchuan.github.io/goInception/tree.html

This SQL query is used as an example to demonstrate the syntax tree printing functionality in goInception. Ensure goInception is configured with the appropriate print options.

```sql
insert into t (sno,name)
        select sno, name from t alias_t
        where sno=(
                select sno+1 from my
                where
                name like "%zhufeng%" and
                sno > '10010' and
                name=alias_t.name
        )
        order by name
        limit 100, 10;

```

--------------------------------

### Configuration File Example

Source: https://hanchuanchuan.github.io/goInception/levels.html

Example TOML configuration snippet showing how audit levels can be set for various error conditions. Options include 0 (correct), 1 (warning), and 2 (error).

```toml
[inc_level]
er_alter_table_once = 1
er_auto_incr_id_warning = 1
er_autoinc_unsigned = 1
...
```

--------------------------------

### Execute SQL with Backup Enabled

Source: https://hanchuanchuan.github.io/goInception/backup.html

To enable the backup feature, append `--backup=1` to the execution options when running SQL. This example shows the syntax for executing a simple `CREATE TABLE` statement with backup enabled.

```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;


```

--------------------------------

### CA Authentication for Encrypted MySQL Connections

Source: https://hanchuanchuan.github.io/goInception/params.html

Example showing how to configure GoInception for secure MySQL connections using CA authentication, including SSL mode and certificate paths.

```sql
# use ssl=verify_ca setting CA identify
# need to put the CA with goInception service on the same server
sql = '''/*--user=test;--password=xxx;--host=127.0.0.1;--port=3333;--check=1;\n--ignore-warnings=1;--ssl=verify_ca;\n--ssl-ca=/data/mysql/data/ca.pem;\n--ssl-cert=/data/mysql/data/client-cert.pem;\n--ssl-key=/data/mysql/data/client-key.pem;*/
inception_magic_start;
use test_inc;
...
inception_magic_commit;


```

--------------------------------

### GoInception SQL Review Example

Source: https://hanchuanchuan.github.io/goInception

This snippet demonstrates how to use GoInception for SQL review by specifying connection parameters and using special comments to delimit the SQL statements. It includes connection details, database usage, and table creation.

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

```

--------------------------------

### Manage pt-osc Processes with goInception

Source: https://hanchuanchuan.github.io/goInception/changelog.html

Commands for viewing and managing pt-osc processes. Use 'get osc processlist' to see all processes and 'stop alter' to abort a specific one.

```bash
inception get osc processlist
```

```bash
inception get osc_percent'sqlsha1'
```

```bash
inception stop alter'sqlsha1'
```

```bash
inception kill osc'sqlsha1'
```

--------------------------------

### Configure goInception with config.toml

Source: https://hanchuanchuan.github.io/goInception/options.html

Persistently configure goInception by modifying the `config.toml` file and starting the service with the `-config` flag. This method allows for permanent changes to audit rules and settings.

```bash
inception -config=config.toml
```

--------------------------------

### Show All Audit Levels

Source: https://hanchuanchuan.github.io/goInception/levels.html

Displays all available audit levels. Use this command to get an overview of the auditing system.

```bash
inception show levels;
```

--------------------------------

### Download and Build goInception

Source: https://hanchuanchuan.github.io/goInception/install.html

Clone the goInception repository, build the parser, and then compile the main package.

```bash
# download source code
git clone https://github.com/hanchuanchuan/goInception

cd goInception

make parser

# build package
go build -o goInception tidb-server/main.go
```

--------------------------------

### Set Specific Audit Level

Source: https://hanchuanchuan.github.io/goInception/levels.html

Sets a specific audit level for a given condition. For example, setting the audit level for 'er_no_where_condition' to '2' (error).

```bash
inception set level er_no_where_condition = 2;
```

--------------------------------

### Basic SQL Review with Remote Server Specification

Source: https://hanchuanchuan.github.io/goInception/params.html

This snippet demonstrates how to use GoInception to review SQL statements by specifying remote server details and review options via annotations.

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


```

--------------------------------

### SSL Authentication for Encrypted MySQL Connections

Source: https://hanchuanchuan.github.io/goInception/params.html

Demonstrates setting up SSL authentication for GoInception to connect to MySQL, using the 'required' SSL mode.

```sql
sql = '''/*--user=test;--password=xxx;--host=127.0.0.1;--port=3333;--check=1;\n--ignore-warnings=1;--ssl=required;*/
inception_magic_start;
use test_inc;
...
inception_magic_commit;'''


```

--------------------------------

### Golang Inception Demo

Source: https://hanchuanchuan.github.io/goInception/demo.html

Demonstrates executing SQL statements using Inception via Go. This code connects to a MySQL database, executes a series of Inception commands including table creation and alteration, and then prints the column names and formatted results.

```go
package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/syohex/go-texttable"
	"log"
)

func main() {

	db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/")
	defer db.Close()

	sql := `/*--user=test;--password=test;--host=127.0.0.1;--port=3306;--check=1;*/
    inception_magic_start;
    use  test;
    create table t1(id int primary key);
    alter table t1 add index idx_id (id);
    create table t2(jid int primary key);
    inception_magic_commit;`

	rows, err := db.Query(sql)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(cols)
	tbl := &texttable.TextTable{}

	tbl.SetHeader(cols[0], cols[1], cols[2], cols[3], cols[4], cols[5], cols[6], cols[7], cols[8], cols[9], cols[10], cols[11])

	for rows.Next() {
		var order_id, affected_rows, stage, error_level, stage_status, error_message, sql, sequence, backup_dbname, execute_time, sqlsha1, backup_time []uint8
		err = rows.Scan(&order_id, &stage, &error_level, &stage_status, &error_message, &sql, &affected_rows, &sequence, &backup_dbname, &execute_time, &sqlsha1, &backup_time)
		tbl.AddRow(string(order_id), string(affected_rows), string(stage), string(error_level), string(stage_status), string(error_message), string(sql), string(sequence), string(backup_dbname), string(execute_time))
		// tbl.AddRow(string(nil_process(sqlsha1)))
	}
	fmt.Println(tbl.Draw())
}

```

--------------------------------

### goInception Configuration File Structure

Source: https://hanchuanchuan.github.io/goInception/config.html

This TOML snippet demonstrates the basic structure of the goInception configuration file, including general parameters and nested configuration blocks for various features.

```toml
host = "0.0.0.0"
port = 4000
path = "/tmp/tidb"

[log]
# log setting
level = "info"
format = "text"

[log.file]
# log file setting
filename = ""
max-size = 300

[inc]
# audit option
enable_nullable = true
enable_drop_table = false
check_table_comment = false
check_column_comment = false


[osc]
# pt-osc options
osc_on = false
osc_min_table_size = 16

[ghost]
# gh-ost options
ghost_allow_on_master = true


```

--------------------------------

### Show Inception Variables

Source: https://hanchuanchuan.github.io/goInception/demo.html

Displays the system variables configured for Inception using the MySQL client.

```bash
inception show variables;

```

--------------------------------

### Connect to Inception

Source: https://hanchuanchuan.github.io/goInception/demo.html

Establishes a connection to the Inception service using the MySQL client.

```bash
mysql -h127.0.0.1 -P4000

```

--------------------------------

### Run Go Inception Demo

Source: https://hanchuanchuan.github.io/goInception/demo.html

Execute a Go program that interacts with Inception. This command is used to run the `test.go` file using Go modules.

```bash
GO111MODULE=on go run test.go

```

--------------------------------

### Show Inception Process List

Source: https://hanchuanchuan.github.io/goInception/demo.html

Displays the current list of processes running within Inception using the MySQL client.

```bash
inception show processlist;

```

--------------------------------

### Manage gh-ost Processes with goInception

Source: https://hanchuanchuan.github.io/goInception/changelog.html

Use these commands to interact with gh-ost processes. Specify process information, terminate, pause, or resume them as needed.

```bash
inception get osc processlist
```

```bash
inception get osc_percent'sqlsha1'
```

```bash
inception stop alter'sqlsha1'
```

```bash
inception kill osc'sqlsha1'
```

```bash
inception pause alter'sqlsha1'
```

```bash
inception pause osc'sqlsha1'
```

```bash
inception resume alter'sqlsha1'
```

```bash
inception resume osc'sqlsha1'
```

```bash
inception show variables like'ghost%'
```

--------------------------------

### Show gh-ost Variables

Source: https://hanchuanchuan.github.io/goInception/ghost.html

Use this command to display all GoInception variables related to gh-ost. This helps in understanding the current configuration.

```bash
inception show variables like 'ghost%';

```

--------------------------------

### Enable User Authentication in GoInception

Source: https://hanchuanchuan.github.io/goInception/safe.html

To enable the user authentication module in GoInception, add the `skip_grant_table = false` parameter to your `config.toml` file. This setting is crucial for activating user management features.

```toml
skip_grant_table = false
```

--------------------------------

### Show OSC Variables

Source: https://hanchuanchuan.github.io/goInception/osc.html

Displays all Inception variables related to OSC. This helps in understanding the available configuration options.

```bash
inception show variables like 'osc%';

```

--------------------------------

### List All OSC Processes

Source: https://hanchuanchuan.github.io/goInception/osc.html

Retrieves a list of all currently running OSC processes managed by Inception. It shows details like database, table, command, and progress.

```bash
inception get osc processlist;

```

--------------------------------

### Show goInception Variables

Source: https://hanchuanchuan.github.io/goInception/options.html

Use this command to view all available goInception audit variables and their current settings.

```bash
inception show variables;
```

--------------------------------

### Python Inception Demo

Source: https://hanchuanchuan.github.io/goInception/demo.html

Execute SQL statements using Inception via Python's pymysql library. This snippet demonstrates connecting to a database, executing a series of SQL commands including table creation and insertion, and then displaying the results in a formatted table.

```python
#!/usr/bin/env python
# -*- coding:utf-8 -*-

import pymysql
import prettytable as pt
tb = pt.PrettyTable()

sql = '''/*--user=root;--password=root;--host=127.0.0.1;--check=1;--port=3306;*/
inception_magic_start;
use test_inc;
create table t1(id int primary key,c1 int);
insert into t1(id,c1,c2) values(1,1,1);
inception_magic_commit;'''

conn = pymysql.connect(host='127.0.0.1', user='', passwd='',
                       db='', port=4000, charset="utf8mb4")
cur = conn.cursor()
ret = cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.close()

tb.field_names = [i[0] for i in cur.description]
for row in result:
    tb.add_row(row)
print(tb)

```

--------------------------------

### Batch DML Execution with Transactions

Source: https://hanchuanchuan.github.io/goInception/trans.html

This Python script demonstrates how to execute a batch of DML statements using GoInception with transaction control enabled. The `--trans=100` parameter specifies that DML statements should be submitted in batches of 100.

```python
import pymysql

sql = '''/*--host=127.0.0.1;--port=3306;--user=test;--password=test;\
--execute=1;--backup=1;--ignore-warnings=1;--trans=100;*/
inception_magic_start;
use test_inc;

-- drop table if exists t1;
create table t1 (id int primary key,c1 int ,c2 varchar(100));

insert into t1 values(1,2,'ccc');
insert into t1 values(2,2,'ccc');
insert into t1 values(3,3,'ccc');
insert into t1 values(4,2,'ccc');
insert into t1 values(5,2,'ccc');

inception_magic_commit;'''

conn = pymysql.connect(host='127.0.0.1', user='', passwd='',
                       db='', port=4000, charset="utf8mb4")
cur = conn.cursor()
ret = cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.close()

for row in result:
    print(row)


```

--------------------------------

### GoInception Expression and Limit Structure

Source: https://hanchuanchuan.github.io/goInception/tree.html

This snippet shows the detailed structure of an expression and limit clause as represented by GoInception. It includes fields for type, flags, names, and pagination offsets.

```json
{
          "Expr": {
            "text": "",
            "k": 0,
            "collation": 0,
            "decimal": 0,
            "length": 0,
            "i": 0,
            "b": null,
            "x": null,
            "Type": {
              "Tp": 0,
              "Flag": 0,
              "Flen": 0,
              "Decimal": 0,
              "Charset": "",
              "Collate": "",
              "Elems": null
            },
            "flag": 8,
            "Name": {
              "text": "",
              "Schema": {
                "O": "",
                "L": ""
              },
              "Table": {
                "O": "",
                "L": ""
              },
              "Name": {
                "O": "name",
                "L": "name"
              }
            },
            "Refer": null
          },
          "Desc": false
        }
      ],
      "ForUnion": false
    },
    "Limit": {
      "text": "",
      "Count": {
        "text": "",
        "k": 2,
        "collation": 0,
        "decimal": 0,
        "length": 0,
        "i": 10,
        "b": null,
        "x": null,
        "Type": {
          "Tp": 8,
          "Flag": 160,
          "Flen": 2,
          "Decimal": 0,
          "Charset": "binary",
          "Collate": "binary",
          "Elems": null
        },
        "flag": 0,
        "projectionOffset": -1
      },
      "Offset": {
        "text": "",
        "k": 2,
        "collation": 0,
        "decimal": 0,
        "length": 0,
        "i": 100,
        "b": null,
        "x": null,
        "Type": {
          "Tp": 8,
          "Flag": 160,
          "Flen": 3,
          "Decimal": 0,
          "Charset": "binary",
          "Collate": "binary",
          "Elems": null
        },
        "flag": 0,
        "projectionOffset": -1
      }
    },
    "LockTp": 0,
    "TableHints": null,
    "IsAfterUnionDistinct": false,
    "IsInBraces": false
  }
}
```

--------------------------------

### JSON Representation of Query Tree

Source: https://hanchuanchuan.github.io/goInception/tree.html

This JSON output represents the syntax tree of the provided SQL query. It is generated when goInception's query print options are enabled. The structure details the query's components, including tables, columns, and conditions.

```json
{
  "text": "insert into t (sno,name)\n        select sno, name from t alias_t\n        where sno=(\n                select sno+1 from my\n                where\n                name like \"%zhufeng%\" and\n                sno > '10010' and\n                name=alias_t.name\n        )\n        order by name\n        limit 100, 10",
  "IsReplace": false,
  "IgnoreErr": false,
  "Table": {
    "text": "",
    "TableRefs": {
      "text": "",
      "resultFields": null,
      "Left": {
        "text": "",
        "Source": {
          "text": "",
          "resultFields": null,
          "Schema": {
            "O": "",
            "L": ""
          },
          "Name": {
            "O": "t",
            "L": "t"
          },
          "DBInfo": null,
          "TableInfo": null,
          "IndexHints": null
        },
        "AsName": {
          "O": "",
          "L": ""
        }
      },
      "Right": null,
      "Tp": 0,
      "On": null,
      "Using": null,
      "NaturalJoin": false,
      "StraightJoin": false
    }
  },
  "Columns": [
    {
      "text": "",
      "Schema": {
        "O": "",
        "L": ""
      },
      "Table": {
        "O": "",
        "L": ""
      },
      "Name": {
        "O": "sno",
        "L": "sno"
      }
    },
    {
      "text": "",
      "Schema": {
        "O": "",
        "L": ""
      },
      "Table": {
        "O": "",
        "L": ""
      },
      "Name": {
        "O": "name",
        "L": "name"
      }
    }
  ],
  "Lists": null,
  "Setlist": null,
  "Priority": 0,
  "OnDuplicate": null,
  "Select": {
    "text": "",
    "resultFields": null,
    "SQLCache": true,
    "CalcFoundRows": false,
    "StraightJoin": false,
    "Priority": 0,
    "Distinct": false,
    "From": {
      "text": "",
      "TableRefs": {
        "text": "",
        "resultFields": null,
        "Left": {
          "text": "",
          "Source": {
            "text": "",
            "resultFields": null,
            "Schema": {
              "O": "",
              "L": ""
            },
            "Name": {
              "O": "t",
              "L": "t"
            },
            "DBInfo": null,
            "TableInfo": null,
            "IndexHints": null
          },
          "AsName": {
            "O": "alias_t",
            "L": "alias_t"
          }
        },
        "Right": null,
        "Tp": 0,
        "On": null,
        "Using": null,
        "NaturalJoin": false,
        "StraightJoin": false
      }
    },
    "Where": {
      "text": "",
      "k": 0,
      "collation": 0,
      "decimal": 0,
      "length": 0,
      "i": 0,
      "b": null,
      "x": null,
      "Type": {
        "Tp": 0,
        "Flag": 0,
        "Flen": 0,
        "Decimal": 0,
        "Charset": "",
        "Collate": "",
        "Elems": null
      },
      "flag": 40,
      "Op": 7,
      "L": {
        "text": "",
        "k": 0,
        "collation": 0,
        "decimal": 0,
        "length": 0,
        "i": 0,
        "b": null,
        "x": null,
        "Type": {
          "Tp": 0,
          "Flag": 0,
          "Flen": 0,
          "Decimal": 0,
          "Charset": "",
          "Collate": "",
          "Elems": null
        },
        "flag": 8,
        "Name": {
          "text": "",
          "Schema": {
            "O": "",
            "L": ""
          },
          "Table": {
            "O": "",
            "L": ""
          },
          "Name": {
            "O": "sno",
            "L": "sno"
          }
        },
        "Refer": null
      },
      "R": {
        "text": "",
        "k": 0,
        "collation": 0,
        "decimal": 0,
        "length": 0,
        "i": 0,
        "b": null,
        "x": null,
        "Type": {
          "Tp": 0,
          "Flag": 0,
          "Flen": 0,
          "Decimal": 0,
          "Charset": "",
          "Collate": "",
          "Elems": null
        },
        "flag": 32,
        "Query": {

```

--------------------------------

### Check Specific OSC Process Percentage

Source: https://hanchuanchuan.github.io/goInception/osc.html

Queries the progress percentage of a specific OSC process using its SQL SHA1 hash. This allows for detailed monitoring of a single operation.

```bash
inception get osc_percent '*E53542EFF4E179BE267210114EC5EDBEF9DC5D8F';

```

--------------------------------

### Recover Specific OSC Process

Source: https://hanchuanchuan.github.io/goInception/ghost.html

Resume a paused online schema change process. This command allows you to continue a migration that was previously stopped.

```bash
inception resume osc '*E53542EFF4E179BE267210114EC5EDBEF9DC5D8F';
-- or --
inception resume alter '*E53542EFF4E179BE267210114EC5EDBEF9DC5D8F';

```

--------------------------------

### Calculate Updaterate from Statistics

Source: https://hanchuanchuan.github.io/goInception/statistics.html

This SQL query calculates the updaterate by summing the 'updating' operations and dividing by the total count of relevant operations from the inception.statistic table. Ensure statistics are enabled and backup is turned on for this data to be available.

```sql
select sum(a.oprate)/count(1) updaterate from
(select (updating)/(usedb+deleting+inserting+updating+selecting+altertable+
createtable+droptable+createdb+truncating) oprate from
inception.statistic) a;

```

--------------------------------

### Set OSC Check Interval

Source: https://hanchuanchuan.github.io/goInception/osc.html

Sets the interval in seconds for checking OSC status. This is useful for fine-tuning the monitoring frequency.

```bash
inception set osc_check_interval = 10;

```

--------------------------------

### SQL Query with WHERE Clause

Source: https://hanchuanchuan.github.io/goInception/tree.html

This snippet represents a SQL query with a WHERE clause that includes a LIKE condition and a numerical comparison. It's useful for understanding how complex filtering criteria are parsed.

```sql
select sno+1 from my
                where
                name like "%zhufeng%" and
                sno > '10010' and
                name=alias_t.name
        
```

--------------------------------

### Pause Specific OSC Process

Source: https://hanchuanchuan.github.io/goInception/ghost.html

Temporarily halt an ongoing online schema change process. The process can be resumed later using the 'resume' command.

```bash
inception pause osc '*E53542EFF4E179BE267210114EC5EDBEF9DC5D8F';
-- or --
inception pause alter '*E53542EFF4E179BE267210114EC5EDBEF9DC5D8F';

```

--------------------------------

### Enable and Check 'WHERE' Clause Audit

Source: https://hanchuanchuan.github.io/goInception/levels.html

Enables the 'check_dml_where' option to enforce 'WHERE' clauses in delete statements and then sets the audit level for related conditions.

```bash
inception show variables like '%where%';

inception set check_dml_where = 1;

inception show levels like '%where%';

inception set level er_no_where_condition = 2;
```
Tokens:
Raw