Shell script:< Bulk Check and change MySQL database table storage engine >< Work summary >

Work Description : After building the Percona XtraDB Cluster (a MySQL architecture, referred to as PXC), you need to import the old MySQL data into new MySQL, but PXC only support the InnoDB table, Non-InnoDB tables cannot be synchronized to the other nodes of PXC, so I need to batch-unify the table engine.

Event Description :

1. Need to write a shell script, complete two purposes:<1> query (CK) engine status <2> change (AL) Engine status

2. Need to access the remote MySQL on the management machine

< method: Mysql-uadmin-h10.10.10.12-p3001-ppassword-e "Sql_cmd" >

3.mysql storage Engine Viewing options:

<1>:select ENGINE from INFORMATION_SCHEMA. TABLES where table_schema= ' db_name ' and table_name=tb_name};

<2>:show CREATE TABLE Tb_name;

4. To achieve the purpose:

<1>: After you want to execute the command, the screen can display < library name, table name, and engine status > Easy to navigate directly

<2>: The number of tables is large, hoping to count the number of engine tables and output, in order to analyze the impact of Al

<3>: Don't want to do CK every time before AL, also do not want CK project in the AL, I think CK and Al can be used together can also be used alone, so flexible

<4>: Consider writing an IP list of files, batch to multiple machines CK (not very suitable for my environment, but also every vim file), I want to do a single server CK, this will be more flexible

<5>: I only CK the non-system library table (System library: Mysql,test,information_schema,performance_schema)

<6>: Execute SH ck_engine_status.sh followed by insufficient parameters, will print help information

5. Script check_engine.sh See attachment or link address: Http://yunpan.cn/cLeVbLN9AnItQ (Extract code: 08D7)

Script execution and Description :

Execution:

$sh ck_engine_status.sh 10.10.10.12 3001 ck '#查看ENGINE status

$sh ck_engine_status.sh 10.10.10.12 3001 al '#更改ENGINE status

Description:

1. Variables that require flexible changes in the script:< users: User password:pass> has 1. Remote access rights 2.select permissions < own definition: Check_log file path >

2. Perform CK, the screen will only list the engine status, will not do AL

After 3.ck completes, the MyISAM table is automatically written in: Db_name.tb_name format: Check_log < only involves MyISAM and innodb> <ck before automatically deleting check_log>

4.al reads the MyISAM table in the Check_log and makes changes. <al will automatically delete check_log, avoid multiple al><al will first determine whether the log file exists, there will be error notification >

5. You can manually write a specific table to Check_log and execute the AL

Related:

1. Help information
   

   650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/76/3C/wKioL1ZNotmjT7MxAAAqZdK_BDY588.png "title=" Usage.png "alt=" Wkiol1znotmjt7mxaaaqzdk_bdy588.png "/>

2. CK results

   650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/76/3D/wKiom1ZNos6wIxBJAAAntqMzfIE807.png "title=" ck.png "alt=" Wkiom1znos6wixbjaaantqmzfie807.png "/>

3. Al Results

   650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/76/3D/wKiom1ZNozbRJAgJAAAYjawmK9Q146.png "title=" al.png "alt=" Wkiom1znozbrjagjaaayjawmk9q146.png "/>
   

   
   

Shell script:< Bulk Check and change MySQL database table storage engine >< Work summary >