Tip nhỏ về database WordPress Database Tables từ MyISAM thành InnoDB with WP-CLI
4th Sep 2021
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 | | | +------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
Bạn thấy bài viết này như thế nào?
Add new comment