Tip nhỏ về database WordPress Database Tables từ MyISAM thành InnoDB with WP-CLI

Written by Tommy
Posted on Sat, 09/04/2021 - 03:22
12 views

Share Everywhere

Table of contents

Đầu tiên phải cài đặt WP-CLI

curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar

php wp-cli.phar --info

chmod +x wp-cli.phar 

sudo mv wp-cli.phar /usr/local/bin/wp

If WP-CLI was installed successfully, you should see something like this when you run

$ wp --info
OS:     Linux 4.19.128-microsoft-standard #1 SMP Tue Jun 23 12:58:10 UTC 2020 x86_64
Shell:  /usr/bin/zsh
PHP binary:     /usr/bin/php
PHP version:    8.0.5
php.ini used:   /etc/php/8.0/cli/php.ini
MySQL binary:   /usr/bin/mysql
MySQL version:  mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
SQL modes:
WP-CLI root dir:        /home/wp-cli/
WP-CLI vendor dir:      /home/wp-cli/vendor
WP_CLI phar path:
WP-CLI packages dir:    /home/wp-cli/.wp-cli/packages/
WP-CLI global config:
WP-CLI project config:  /home/wp-cli/wp-cli.yml
WP-CLI version: 2.5.0

Thứ 2 là backup database

wp db export before-engine-change.sql --all-tablespaces --add-drop-table --allow-root

Thứ ba là chạy Bash Script

1) Create a new text file with a .sh extension.

>> Hướng dẫn Modify the Shipping States for a selected country in WooCommerce

>> Hướng dẫn gửi email dựa trên select option trong Contact Form 7

>> Hướng dẫn How to set a shipping cost by item in Woocommerce

I created a new file called deploy.sh for my website.

Lưu ý: là trong thư mục public_html/

2) Add  #!/usr/bin/env bash to the top of it.

This is necessary for the “make it executable” part.

3) Here is a short bash script to convert all WordPress MyISAM database tables to InnoDB with WP-CLI.

#!/usr/bin/env bash
# Author Mike https://guides.wp-bullet.com
# Purpose - Convert MyISAM tables to InnoDB with WP-CLI

# create array of MyISAM tables
WPTABLES=($(wp db query "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" --allow-root --silent --skip-column-names | awk '{ print $1}'))

# loop through array and alter tables
for WPTABLE in ${WPTABLES[@]}
do
    echo "Converting ${WPTABLE} to InnoDB"
    wp db query "ALTER TABLE ${WPTABLE} ENGINE=InnoDB" --allow-root
    echo "Converted ${WPTABLE} to InnoDB"
done

4) At the command line, run chmod u+x YourScriptFileName.sh

I ran chmod u+x deploy.sh to make mine executable.

Lệnh run đây

./deploy.sh

Cuối cùng check lại các table xem đã ok chưa

wp db query "SHOW TABLE STATUS WHERE Engine = 'InnoDB'" --allow-root

You should get a large table output like this showing all tables were converted from MyISAM to InnoDB engine.

+------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name                   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| wp_commentmeta         | InnoDB |      10 | Compact    |  250 |             65 |       16384 |               0 |        49152 |         0 |            405 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_comments            | InnoDB |      10 | Compact    |  163 |            603 |       98304 |               0 |        81920 |         0 |            248 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_ewwwio_images       | InnoDB |      10 | Compact    | 1575 |            228 |      360448 |               0 |       425984 |         0 |           2110 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_links               | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_options             | InnoDB |      10 | Compact    |  530 |           4235 |     2244608 |               0 |        49152 |   4194304 |          65960 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_popularpostsdata    | InnoDB |      10 | Compact    |   93 |            176 |       16384 |               0 |            0 |         0 |           NULL | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_popularpostssummary | InnoDB |      10 | Compact    |  940 |             87 |       81920 |               0 |       114688 |         0 |           1154 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_postmeta            | InnoDB |      10 | Compact    | 3326 |            472 |     1572864 |               0 |       344064 |   4194304 |          16266 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_posts               | InnoDB |      10 | Compact    |  607 |          11174 |     6782976 |               0 |       131072 |   5242880 |           4791 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_prli_clicks         | InnoDB |      10 | Compact    | 1878 |            253 |      475136 |               0 |      1081344 |   4194304 |           2009 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_prli_groups         | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        16384 |         0 |              3 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_prli_link_metas     | InnoDB |      10 | Compact    |    3 |           5461 |       16384 |               0 |        32768 |         0 |              6 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_prli_links          | InnoDB |      10 | Compact    |   13 |           1260 |       16384 |               0 |       163840 |         0 |             16 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_term_relationships  | InnoDB |      10 | Compact    |  638 |            102 |       65536 |               0 |        16384 |         0 |           NULL | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_term_taxonomy       | InnoDB |      10 | Compact    |   30 |            546 |       16384 |               0 |        32768 |         0 |             32 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_termmeta            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |         0 |              1 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_terms               | InnoDB |      10 | Compact    |   29 |            564 |       16384 |               0 |        32768 |         0 |             32 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_usermeta            | InnoDB |      10 | Compact    |  113 |            144 |       16384 |               0 |        32768 |         0 |            123 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_users               | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        49152 |         0 |              5 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_yoast_seo_links     | InnoDB |      10 | Compact    |  877 |            168 |      147456 |               0 |        49152 |         0 |           3837 | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| wp_yoast_seo_meta      | InnoDB |      10 | Compact    |  642 |             76 |       49152 |               0 |            0 |         0 |           NULL | 2018-01-17 06:45:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
1 + 11 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
Câu nói tâm đắc: “Điều tuyệt với nhất trong cuộc sống là làm được những việc mà người khác tin là không thể!”