溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

MySQL Inception的安裝和使用

發(fā)布時(shí)間：2021-08-30 10:46:05 來源：億速云 閱讀：126 作者：chen 欄目：MySQL數(shù)據(jù)庫

本篇內(nèi)容主要講解“MySQL Inception的安裝和使用”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL Inception的安裝和使用”吧!

Inception 是一個(gè)集審核、執(zhí)行、備份及生成回滾語句于一身的MySQL自動化運(yùn)維工具，支持MySQL 5.5、5.6以及Percona等主流版本。

測試環(huán)境：CentOS 6.9
下載源碼
[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/2018), 11.80 MiB | 37 KiB/s, done.
Resolving deltas: 100% (522/522), done.

安裝依賴包
yum install bison
yum install cmake
yum install ncurses-devel
yum install openssl-devel
yum install gcc-c++ libgcc gcc

通過sh inception_build.sh debug腳本方式安裝
[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

默認(rèn)會安裝在/fire/inception/inception/debug/mysql下面

編輯配置文件

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

  2. [inception]

  3. general_log=1

  4. general_log_file=/var/inception/log/inception.log

  5. port=6690

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

  7. character-set-client-handshake=0

  8. character-set-server=utf8


  10. # MySQL支持字符集

  11. inception_support_charset=utf8


  13. inception_remote_backup_host=192.168.56.102

  14. inception_remote_backup_port=3306

  15. inception_remote_system_user=inception

  16. inception_remote_system_password=inception


  18. #inception_remote_charset=utf8mb4

  19. inception_enable_nullable=0

  20. inception_check_primary_key=1

  21. inception_check_column_comment=1

  22. inception_check_table_comment=1

  23. inception_enable_blob_type=1

  24. inception_check_column_default_value=1


  26. # OSC

  27. inception_osc_on=1

  28. inception_osc_min_table_size=1

  29. inception_osc_bin_dir=/script/percona-toolkit-3.0.2/bin

  30. inception_osc_check_interval=5

  31. inception_osc_chunk_time=0.1


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

執(zhí)行SQL
編輯python 2腳本，為一張100萬行的大表添加字段

  1. [root@MySQL01 script]# vim inception.py

  2. #!/usr/bin/python2.6

  3. #-\*-coding: utf-8-\*-

  4. import MySQLdb

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

  6. inception_magic_start;\

  7. use test;\

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

  9. inception_magic_commit;'

  10. try:

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

  12.     cur=conn.cursor()

  13.     ret=cur.execute(sql)

  14.     result=cur.fetchall()

  15.     num_fields = len(cur.description)

  16.     field_names = [i[0] for i in cur.description]

  17.     print field_names

  18.     for row in result:

  19.         print row[0], "|",row[1],"|",row[2],"|",row[3],"|",row[4],"|",

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

  21.     cur.close()

  22.     conn.close()

  23. except MySQLdb.Error,e:

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


執(zhí)行腳本
[root@MySQL01 script]# python2.6 inception.py

登錄Inception查看OSC的執(zhí)行過程

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

  2. mysql> inception get osc processlist\G

  3. *************************** 1. row ***************************

  4.     DBNAME: test

  5.  TABLENAME: item_order

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

  7.    SQLSHA1: *862B1979B3751217FE56799A0216A2629F2FFD4C

  8.    PERCENT: 100

  9. REMAINTIME: 00:00

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

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

  12. Operation, tries, wait:

  13.   analyze_table, 10, 1

  14.   copy_rows, 10, 0.25

  15.   create_triggers, 10, 1

  16.   drop_triggers, 10, 1

  17.   swap_tables, 10, 1

  18.   update_foreign_keys, 10, 1

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

  20. Altering `test`.`item_order`...

  21. Creating new table...

  22. CREATE TABLE `test`.`_item_order_new` (

  23.   `order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '訂單編號',

  24.   `loc_id` tinyint(3) unsigned NOT NULL COMMENT '地區(qū)編號',

  25.   `order_create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '訂單生成日期',

  26.   `order_expire_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '訂單過期日期',

  27.   `item_id` int(10) unsigned NOT NULL COMMENT '商品編號',

  28.   `item_cnt` int(10) unsigned NOT NULL COMMENT '商品數(shù)量',

  29.   `order_status` tinyint(3) unsigned NOT NULL COMMENT '訂單狀態(tài),0-失效,1-交易成功',

  30.   `tran_amount` bigint(20) unsigned NOT NULL COMMENT '交易金額',

  31.   PRIMARY KEY (`order_id`),

  32.   KEY `idx_order_loc_status` (`loc_id`,`order_status`,`order_expire_date`),

  33.   KEY `idx_order_loc_exp` (`loc_id`,`order_expire_date`),

  34.   KEY `idx_order_stat_loc_item` (`order_status`,`loc_id`,`item_id`,`tran_amount`),

  35.   KEY `idx_item_id` (`item_id`)

  36. ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COMMENT='訂單表'

  37. Created new table test._item_order_new OK.

  38. Altering new table...

  39. ALTER TABLE `test`.`_item_order_new` add loc5 varchar(30) not null default 'xxx' comment 'efg'

  40. Altered `test`.`_item_order_new` OK.

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

  42. 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`

  43. CREATE TRIGGER `pt_osc_test_item_order_upd` AFTER UPDATE ON `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`, `order_expire_date`, `item_id`, `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`);END

  44. CREATE TRIGGER `pt_osc_test_item_order_ins` AFTER INSERT ON `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_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`)

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

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

  47. 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_amount` FROM `test`.`item_order` FORCE INDEX(`PRIMARY`) WHERE ((`order_id` >= ?)) AND ((`order_id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 5178 copy nibble*/

  48. SELECT /*!40001 SQL_NO_CACHE */ `order_id` FROM `test`.`item_order` FORCE INDEX(`PRIMARY`) WHERE ((`order_id` >= ?)) ORDER BY `order_id` LIMIT ?, 2 /*next chunk boundary*/

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

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

  51. RENAME TABLE `test`.`item_order` TO `test`.`_item_order_old`, `test`.`_item_order_new` TO `test`.`item_order`

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

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

  54. SET foreign_key_checks=0

  55. DROP TABLE IF EXISTS `test`.`_item_order_old`

  56. 2018-04-07T02:05:11 Dropped old table `test`.`_item_order_old` OK.

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

  58. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_item_order_del`;

  59. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_item_order_upd`;

  60. DROP TRIGGER IF EXISTS `test`.`pt_osc_test_item_order_ins`;

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

  62. # Event Count

  63. # ====== =====

  64. # INSERT 727

  65. Successfully altered `test`.`item_order`.


  67. 1 row in set (0.00 sec)


語句執(zhí)行完成
[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 |

備份及回滾語句的生成條件：
線上服務(wù)器必須要打開 binlog，在啟動時(shí)需要設(shè)置參數(shù)log_bin、log_bin_index等關(guān)于 binlog 的參數(shù)。不然不會備份及生成回滾語句。
參數(shù)binlog_format必須要設(shè)置為 mixed 或者 row 模式。
參數(shù) server_id 必須要設(shè)置為非0及非1。

  1. mysql> show global variables like '%log%bin%';

  2. +---------------------------------+------------------------------------+

  3. | Variable_name | Value |

  4. +---------------------------------+------------------------------------+

  5. | log_bin | ON |

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

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

  8. | log_bin_trust_function_creators | ON |

  9. | log_bin_use_v1_row_events | OFF |

  10. | sql_log_bin | ON |

  11. +---------------------------------+------------------------------------+

  12. 6 rows in set (0.00 sec)


  14. mysql> show global variables like 'binlog_format';

  15. +---------------+-------+

  16. | Variable_name | Value |

  17. +---------------+-------+

  18. | binlog_format | ROW |

  19. +---------------+-------+

  20. 1 row in set (0.00 sec)


  22. mysql> show global variables like 'server_id';

  23. +---------------+-------+

  24. | Variable_name | Value |

  25. +---------------+-------+

  26. | server_id | 102 |

  27. +---------------+-------+

  28. 1 row in set (0.00 sec)


查看備份表

  1. mysql> show databases;

  2. +--------------------------+

  3. | Database |

  4. +--------------------------+

  5. | information_schema |

  6. | 192_168_56_101_3306_test |

  7. | inception |

  8. | mysql |

  9. | performance_schema |

  10. | sale |

  11. | test |

  12. +--------------------------+

  13. 8 rows in set (0.00 sec)


  15. mysql> use 192_168_56_101_3306_test

  16. Reading table information for completion of table and column names

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


  19. Database changed

  20. mysql> show tables;

  21. +------------------------------------+

  22. | Tables_in_192_168_56_101_3306_test |

  23. +------------------------------------+

  24. | $_$inception_backup_information$_$ |

  25. | item_order |

  26. +------------------------------------+

  27. 2 rows in set (0.00 sec)


  29. mysql> select * from item_order;

  30. +----+-----------------------------------------------------+-----------------+

  31. | id | rollback_statement | opid_time |

  32. +----+-----------------------------------------------------+-----------------+

  33. | 1 | ALTER TABLE `test`.`item_order` DROP COLUMN `loc5`; | 1523037912_28_1 |

  34. +----+-----------------------------------------------------+-----------------+

  35. 1 row in set (0.00 sec)

到此，相信大家對“MySQL Inception的安裝和使用”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手