Shulou(Shulou.com)05/31 Report--

This article focuses on "installation and use of MySQL Inception". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the installation and use of MySQL Inception".

Inception is a MySQL automated operation and maintenance tool that integrates audit, execution, backup and generation of rollback statements, and supports mainstream versions such as MySQL 5.5,5.6 and Percona.

Test environment: CentOS 6.9

Download the source code

[root@MySQL01 ~] # cd / fire/

[root@MySQL01 fire] # mkdir inception

[root@MySQL01 fire] # cd inception/

[root@MySQL01 inception] # git clone https://github.com/mysql-inception/inception.git

Initialized empty Git repository in / fire/inception/inception/.git/

Remote: Counting objects: 2018, done.

Remote: Total 2018 (delta 0), reused 0 (delta 0), pack-reused 2018

Receiving objects: 100% (2018 MiB), 11.80 MiB | 37 KiB/s, done.

Resolving deltas: 100% (522 amp 522), done.

Install dependency packages

Yum install bison

Yum install cmake

Yum install ncurses-devel

Yum install openssl-devel

Yum install gcc-c++ libgcc gcc

Install via sh inception_build.sh debug script

[root@MySQL01 inception] # sh inception_build.sh debug

...

-- Installing: / fire/inception/inception/debug/mysql/man/man1/mysql.1

-- Installing: / fire/inception/inception/debug/mysql/man/man1/mysql_fix_extensions.1

-- Installing: / fire/inception/inception/debug/mysql/man/man1/mysqltest.1

-- Installing: / fire/inception/inception/debug/mysql/man/man1/mysql_client_test.1

-- Installing: / fire/inception/inception/debug/mysql/man/man1/mysql_waitpid.1

-- Installing: / fire/inception/inception/debug/mysql/man/man1/mysql.server.1

-- Installing: / fire/inception/inception/debug/mysql/man/man8/mysqld.8

-- Installing: / fire/inception/inception/debug/mysql/support-files/solaris/postinstall-solaris

Will be installed under / fire/inception/inception/debug/mysql by default

Edit configuration file

[root@MySQL01 script] # vim / etc/inc.cnf

[inception]

General_log=1

General_log_file=/var/inception/log/inception.log

Port=6690

Socket=/fire/inception/inception/debug/mysql/inc.socket

Character-set-client-handshake=0

Character-set-server=utf8

# MySQL supports character sets

Inception_support_charset=utf8

Inception_remote_backup_host=192.168.56.102

Inception_remote_backup_port=3306

Inception_remote_system_user=inception

Inception_remote_system_password=inception

# inception_remote_charset=utf8mb4

Inception_enable_nullable=0

Inception_check_primary_key=1

Inception_check_column_comment=1

Inception_check_table_comment=1

Inception_enable_blob_type=1

Inception_check_column_default_value=1

# OSC

Inception_osc_on=1

Inception_osc_min_table_size=1

Inception_osc_bin_dir=/script/percona-toolkit-3.0.2/bin

Inception_osc_check_interval=5

Inception_osc_chunk_time=0.1

Start

# / fire/inception/inception/debug/mysql/bin/Inception-- defaults-file=/etc/inc.cnf &

Execute SQL

Edit the python 2 script to add fields to a large table with 1 million rows

[root@MySQL01 script] # vim inception.py

#! / usr/bin/python2.6

#-coding: utf-8-\ *-

Import MySQLdb

Sql='/*--user=neo;--password=neo;--host=192.168.56.101;--execute=1;--port=3306;*/\

Inception_magic_start;\

Use test;\

Alter table item_order add loc5 varchar (30) not null default\ 'xxx\' comment\ 'efg\;

Inception_magic_commit;'

Try:

Conn=MySQLdb.connect (host='127.0.0.1',user='',passwd='',db='',port=6690)

Cur=conn.cursor ()

Ret=cur.execute (sql)

Result=cur.fetchall ()

Num_fields = len (cur.description)

Field_names = [I [0] for i in cur.description]

Print field_names

For row in result:

Print row [0], "|", row [1], "|", row [2], "|", row [3], "|", row [4], "|"

Row [5], "|", row [6], "|", row [7], "|", row [8], "|", row [9], "|", row [10]

Cur.close ()

Conn.close ()

Except MySQLdb.Error,e:

Print "Mysql Error% d:% s"% (e.args [0], e.args [1])

Execute script

[root@MySQL01 script] # python2.6 inception.py

Log in to Inception to view the execution of OSC

# / mysql_software_56/bin/mysql-uroot-h 127.0.0.1-P 6690

Mysql > inception get osc processlist\ G

* * 1. Row *

DBNAME: test

TABLENAME: item_order

COMMAND: alter table item_order add loc5 varchar (30) not null default 'xxx' comment' efg'

SQLSHA1: * 862B1979B3751217FE56799A0216A2629F2FFD4C

PERCENT: 100

REMAINTIME: 00:00

INFOMATION: No slaves found. See-- recursion-method if host MySQL01 has slaves.

Not checking slave lag because no slaves were found and-- check-slave-lag was not specified.

Operation, tries, wait:

Analyze_table, 10, 1

Copy_rows, 10, 0.25

Create_triggers, 10, 1

Drop_triggers, 10, 1

Swap_tables, 10, 1

Update_foreign_keys, 10, 1

Not updating foreign keys because-- alter-foreign-keys-method=none. Foreign keys that reference the table will no longer work.

Altering `test`.`item _ order`...

Creating new table...

CREATE TABLE `test`.` _ item_order_ new` (

`order_ id`bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'order number'

`loc_ id`tinyint (3) unsigned NOT NULL COMMENT 'area code'

`order_create_ date`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'order generation date'

`order_expire_ date`timestamp NOT NULL DEFAULT '0000-00-0000: 00 COMMENT' order expiration date'

`item_ id`int (10) unsigned NOT NULL COMMENT 'item number'

`item_ CNT` int (10) unsigned NOT NULL COMMENT 'quantity of goods'

`order_ status` tinyint (3) unsigned NOT NULL COMMENT 'order status, 0-invalid, 1-transaction successful'

`tran_ amount `bigint (20) unsigned NOT NULL COMMENT 'transaction amount'

PRIMARY KEY (`order_ id`)

KEY `idx_order_loc_ status` (`loc_ id`, `order_ status`, `order_expire_ date`)

KEY `idx_order_loc_ exp` (`loc_ id`, `order_expire_ date`)

KEY `item_ item` (`order_ status`, `loc_ id`, `tran_ item`)

KEY `idx_item_ id` (`item_ id`)

) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COMMENT=' order form'

Created new table test._item_order_new OK.

Altering new table...

ALTER TABLE `test`.` _ item_order_ new`add loc5 varchar (30) not null default 'xxx' comment' efg'

Altered `test`.` _ item_order_ new` OK.

2018-04-07T02:03:33 Creating triggers...

CREATE TRIGGER `pt_osc_test_item_order_ del`AFTER DELETE ON `test`.`item _ order`FOR EACH ROW DELETE IGNORE FROM `test`.` _ item_order_ new`WHERE `test`.` _ item_order_ new`.`order _ id`OLD.`order _ id`

CREATE TRIGGER `test`.`item _ order`FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.` _ item_order_ new`WHERE! (OLD.`order _ id`NEW.`order _ id`) AND `test`.` _ item_order_ new`.`order _ id`OLD.`order _ id` REPLACE INTO `test`.` _ item_order_ new` (`order_ id`, `loc_ id`, `order_create_ date`, `item_ date`, `item_ cnt`, `order_ status`, `tran_ amount `) VALUES (NEW.`order _ id`, NEW.`loc _ id`, NEW.`order _ create_ date`, NEW.`order _ expire_ date`, NEW.`item _ id`, NEW.`item _ cnt`, NEW.`order _ status`, NEW.`tran _ amount t`);

CREATE TRIGGER `test`.`item _ order`FOR EACH ROW REPLACE INTO `test`.` _ item_order_ new` (`order_ id`, `loc_ id`, `order_create_ date`, `order_expire_ date`, `item_ id`, `item_ cnt`, `order_ status`, `tran_ quantit`) VALUES (NEW.`order _ id`, NEW.`loc _ id`, NEW.`order _ create_ date`, NEW.`order _ date`, NEW.`item _ id`, NEW.`item _ cnt`, NEW.`order _ status`, NEW.`tran _ amount

2018-04-07T02:03:33 Created triggers OK.

2018-04-07T02:03:33 Copying approximately 1000219 rows...

INSERT LOW_PRIORITY IGNORE INTO `test`.` _ item_order_ New` (`order_ id`, `loc_ id`, `order_create_ date`, `order_expire_ date`, `item_ id`, `item_ cnt`, `order_ status`, `tran_ amount `) SELECT `order_ id`, `loc_ id`, `order_create_ date`, `order_expire_ date`, `item_ id`, `item_ cnt`, `order_ status`, `tran_ quantit`FROM `test`.`item _ order` FORCE INDEX (`PRIMARY`) WHERE ((`order_ id` > =?) AND ((`order_ id` =?)) ORDER BY `order_ id` LIMIT?, 2 / * next chunk boundary*/

2018-04-07T02:05:07 Copied rows OK.

2018-04-07T02:05:07 Swapping tables...

RENAME TABLE `test`.`item _ order`TO `test`.` _ item_order_ old`, `test`.` _ item_order_ new`TO `test`.`item _ order`

2018-04-07T02:05:10 Swapped original and new tables OK.

2018-04-07T02:05:10 Dropping old table...

SET foreign_key_checks=0

DROP TABLE IF EXISTS `test`.` _ item_order_ old`

2018-04-07T02:05:11 Dropped old table `test`.` _ item_order_ old` OK.

2018-04-07T02:05:11 Dropping triggers...

DROP TRIGGER IF EXISTS `test`.`pt _ osc_test_item_order_ del`

DROP TRIGGER IF EXISTS `test`.`pt _ osc_test_item_order_ upd`

DROP TRIGGER IF EXISTS `test`.`pt _ osc_test_item_order_ ins`

2018-04-07T02:05:11 Dropped triggers OK.

# Event Count

# = =

# INSERT 727

Successfully altered `test`.`item _ order`.

1 row in set (0.00 sec)

Statement execution completed

[root@MySQL01 script] # python2.6 inception.py

['ID',' stage', 'errlevel',' stagestatus', 'errormessage',' SQL', 'Affected_rows',' sequence', 'backup_dbname',' execute_time', 'sqlsha1']

1 | RERUN | 0 | Execute Successfully | None | 2 | EXECUTED | 0 | Execute Successfully

Backup successfully | None |

Conditions for the generation of backup and rollback statements:

The online server must open binlog. When starting, you need to set the parameters log_bin, log_bin_index and other parameters about binlog. Otherwise, the rollback statement will not be backed up and generated.

The parameter binlog_format must be set to mixed or row mode.

The parameter server_id must be set to non-0 and non-1.

Mysql > show global variables like'% log%bin%'

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | / mysql_56_3306/log/mysql-bin |

| | log_bin_index | / mysql_56_3306/log/mysql-bin.index |

| | log_bin_trust_function_creators | ON |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+

6 rows in set (0.00 sec)

Mysql > show global variables like 'binlog_format'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | ROW |

+-+ +

1 row in set (0.00 sec)

Mysql > show global variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 102 | |

+-+ +

1 row in set (0.00 sec)

View backup tabl

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | 192_168_56_101_3306_test |

| | inception |

| | mysql |

| | performance_schema |

| | sale |

| | test |

+-+

8 rows in set (0.00 sec)

Mysql > use 192_168_56_101_3306_test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_192_168_56_101_3306_test |

+-+

| | $_ $inception_backup_information$_$ |

| | item_order |

+-+

2 rows in set (0.00 sec)

Mysql > select * from item_order

+-+-- +-+

| | id | rollback_statement | opid_time | |

+-+-- +-+

| | 1 | ALTER TABLE `test`.`item _ order` DROP COLUMN `loc5`; | 1523037912285` |

+-+-- +-+

1 row in set (0.00 sec)

At this point, I believe you have a deeper understanding of "the installation and use of MySQL Inception". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.