Converting MyISAM tables to InnoDB on MediaWiki

Converting MyISAM tables to InnoDB is generally safe and recommended for most use cases, as InnoDB offers better reliability, performance, and features like transactions and row-level locking. 

However, there are some potential risks and considerations you should be aware of before proceeding. 

Here's a detailed breakdown:

---

Potential Risks and Considerations

1. Full-Text Search Limitations

• MyISAM: Supports full-text indexing and searching natively.

• InnoDB: Full-text search support was added in MySQL 5.6+ and MariaDB 10.0+, but it may behave differently or have performance implications compared to MyISAM.

• Action: If your MediaWiki installation relies heavily on full-text search (e.g., for searching article content), test the performance after conversion.

---

2. Table-Level Locking vs. Row-Level Locking

• MyISAM: Uses table-level locking, which can cause contention during heavy write operations.

• InnoDB: Uses row-level locking, which is more efficient for concurrent writes but may require more system resources (e.g., CPU and RAM).

• Action: Ensure your server has sufficient resources to handle InnoDB's row-level locking.

---

3. Storage Requirements

• MyISAM: Typically uses less disk space than InnoDB.

• InnoDB: May use more disk space due to its support for transactions, foreign keys, and other features.

• Action: Ensure you have enough disk space before converting large tables.

---

4. Foreign Key Constraints

• MyISAM: Does not support foreign keys.

• InnoDB: Enforces foreign key constraints, which can cause errors if your schema relies on MyISAM's lack of enforcement.

• Action: Review your database schema for any implicit dependencies that might break when foreign keys are enforced.

---

5. Performance Differences

• MyISAM: Can be faster for read-heavy workloads with minimal writes.

• InnoDB: Generally performs better for mixed read/write workloads and high concurrency.

• Action: Test the performance of your MediaWiki installation after conversion to ensure it meets your expectations.

---

6. Downtime During Conversion

• Converting large tables can take time and may require downtime, especially if the table is heavily used.

• Action: Schedule the conversion during a maintenance window or when traffic is low.

---

7. Backup and Rollback

• If something goes wrong during the conversion, you’ll need a backup to restore the original state.

• Action: Always back up your database before converting tables.

---

Steps to Safely Convert MyISAM Tables to InnoDB

1. Backup the Database

Before making any changes, back up your entire database:

bash

Copy

sudo mysqldump -h localhost -u user_name -p db_name > db_name_backup.sql

---

2. Identify MyISAM Tables

Run the following SQL query to list all MyISAM tables in your database:

sql

Copy

SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name
' AND ENGINE = 'MyISAM';

---

3. Test Conversion on a Single Table

Before converting all tables, test the conversion on a single, less critical table to ensure there are no issues:

sql

Copy

ALTER TABLE table_name ENGINE=InnoDB;

Replace table_name with the name of the table.

---

4. Monitor Performance and Functionality

After converting a table:

• Check for errors in the MariaDB logs.

• Test the functionality of your MediaWiki installation (e.g., editing pages, searching, etc.).

• Monitor server performance (CPU, RAM, disk I/O).

---

5. Convert All MyISAM Tables

If the test conversion is successful, convert all MyISAM tables to InnoDB. You can generate the conversion commands dynamically:

sql

Copy

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'MyISAM';

Copy and execute the generated commands in the MariaDB shell.

---

6. Verify the Conversion

After converting all tables, verify that they are now using the InnoDB engine:

sql

Copy

SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_name';

---

7. Optimize Tables (Optional)

After conversion, you may want to optimize the tables to reclaim unused space and improve performance:

sql

Copy

OPTIMIZE TABLE table_name;

---

What to Do If Something Goes Wrong

If you encounter issues after converting tables:

1. Restore the database from the backup:

   bash

   Copy

   sudo mysql -h localhost -u user_name -p db_name < db_name
   _backup.sql

2. Check MariaDB logs, review schema dependencies, etc.

3. Unfortunately I won't be able to help, so restore your backup

4. Seek help from the  MediaWiki support community  or a database administrator.

Installing a local WordPress development environment on Manjaro

Installing a local WordPress development environment on Manjaro (or any other Linux distribution) typically involves setting up a LAMP (Linux, Apache, MySQL, PHP) or LEMP (Linux, Nginx, MySQL, PHP) stack and then installing WordPress on top of it. 

How to set up a LAMP stack and WordPress on Manjaro: 

1. Update System:

First, make sure your system is updated:

sudo pacman -Syu

2. Install Apache:

sudo pacman -S apache

Start and enable Apache:

bash

sudo systemctl start httpd
sudo systemctl enable httpd

You can check if Apache is working by opening your browser and visiting http://localhost. You should see the Apache test page.

3. Install MariaDB (MySQL):

sudo pacman -S mariadb

After installation, you need to initialize the MariaDB data directory:

css

sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

Then, start and enable the MariaDB service:

bash

sudo systemctl start mariadb
sudo systemctl enable mariadb

Secure your installation:

sudo mysql_secure_installation

You'll be asked a series of questions. For a local development environment, you can often hit enter to choose the default options, but make sure to set a strong root password.

4. Install PHP:

sudo pacman -S php php-apache

Now, you need to modify the Apache configuration to use PHP. Edit /etc/httpd/conf/httpd.conf:

bash

sudo nano /etc/httpd/conf/httpd.conf

Find and uncomment the line that says:

bash

LoadModule mpm_prefork_module modules/mod_mpm_prefork.so

Then, further down, find and uncomment:

shell

#LoadModule php7_module modules/libphp7.so

and add the following below it:

bash

Include conf/extra/php7_module.conf

Restart Apache to apply the changes:

sudo systemctl restart httpd

5. Install WordPress:

First, navigate to a directory where you want to download WordPress. Typically, this might be inside /srv/http/.

Download the latest WordPress:

bash

cd /srv/http/
sudo wget https://wordpress.org/latest.tar.gz
sudo tar -xvf latest.tar.gz

You might want to rename the resulting WordPress directory for convenience.

6. Create a Database for WordPress:

Log in to MariaDB:

css

mysql -u root -p

Then:

sql

CREATE DATABASE wordpress;
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;
EXIT;

7. Configure WordPress:

Navigate to the WordPress directory and rename wp-config-sample.php to wp-config.php. Then edit the file:

arduino

cd wordpress
sudo cp wp-config-sample.php wp-config.php
sudo nano wp-config.php

Find the following lines and fill in the details:

sql

define('DB_NAME', 'wordpress');
define('DB_USER', 'wordpressuser');
define('DB_PASSWORD', 'your_password');
define('DB_HOST', 'localhost');

Now, navigate to http://localhost/wordpress (or the directory name you chose) in your web browser and complete the WordPress installation.

That's it! Your LAMP stack is now set up on Manjaro with WordPress!. Adjust configurations as needed based on your specific development requirements.

How to set up a LAMP stack and WordPress on Manjaro: 

Setting up a LEMP (Linux, Nginx, MySQL, PHP) stack on Manjaro and then installing WordPress on top of it involves the following steps:

1. Update System:

Make sure your system is updated:

sudo pacman -Syu

2. Install Nginx:

sudo pacman -S nginx

Start and enable Nginx:

bash

sudo systemctl start nginx
sudo systemctl enable nginx

To confirm Nginx is working, open a browser and go to http://localhost. You should see the Nginx welcome page.

3. Install MariaDB (MySQL):

sudo pacman -S mariadb

Initialize the MariaDB data directory:

css

sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

Start and enable the MariaDB service:

bash

sudo systemctl start mariadb
sudo systemctl enable mariadb

Secure the installation:

sudo mysql_secure_installation

Follow the on-screen prompts, setting a root password and making selections appropriate for your environment.

4. Install PHP and PHP-FPM:

sudo pacman -S php php-fpm

Start and enable PHP-FPM:

bash

sudo systemctl start php-fpm
sudo systemctl enable php-fpm

5. Configure Nginx for PHP:

Edit the default Nginx server block or create a new configuration file:

arduino

sudo nano /etc/nginx/conf.d/default.conf

Add or modify the location block for PHP:

nginx

location ~ \.php$ {
    root           /usr/share/nginx/html;
    fastcgi_pass   127.0.0.1:9000;
    fastcgi_index  index.php;
    fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
    include        fastcgi_params;
}

Restart Nginx to apply the changes:

sudo systemctl restart nginx

6. Install WordPress:

Navigate to Nginx's default web directory or your chosen directory:

bash

cd /usr/share/nginx/html

Download the latest WordPress:

arduino

sudo wget https://wordpress.org/latest.tar.gz
sudo tar -xvf latest.tar.gz

If you'd like, you can move the contents of the wordpress directory to the root of your web directory.

7. Create a Database for WordPress:

Log in to MariaDB:

css

mysql -u root -p

Create the WordPress database and user:

sql

CREATE DATABASE wordpress;
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;
EXIT;

8. Configure WordPress:

Navigate to the WordPress directory (if you didn't move the files, this would be inside a wordpress subdirectory) and rename wp-config-sample.php to wp-config.php:

arduino

cd wordpress
sudo cp wp-config-sample.php wp-config.php
sudo nano wp-config.php

Update the database details:

sql

define('DB_NAME', 'wordpress');
define('DB_USER', 'wordpressuser');
define('DB_PASSWORD', 'your_password');
define('DB_HOST', 'localhost');

Finally, navigate to http://localhost/wordpress in your browser and complete the WordPress installation steps.

Your LEMP stack is now set up on Manjaro with WordPress! Adjust configurations as necessary based on your specific requirements.

A Web Developer's Collection of Web Resources

Cheat Sheets, Code, Tutorials and just about any Web Development resource that can be snatched.

• Apache (1)
• ASP (1)
• Banners (1)
• Cheat Sheets (8)
• CMS (1)
• CMSimple (1)
• CSS (5)
• DOCTYPE (1)
• DokuWiki (1)
• e-mails (1)
• eoCMS (1)
• Flash (1)
• Forms (2)
• Frog CMS (1)
• FUDforum (1)
• Habari (1)
• HTML (2)
• JavaScript (1)
• Jaws (1)
• jQuery (1)
• Lanius (1)
• MySQL (4)
• PDO (1)
• PHP (4)
• phpSQLiteCMS(1)
• razorCMS (1)
• Screen Resolutions (1)
• SEO (4)
• Serendipity (1)
• Skeleton (1)
• SQLite (2)
• Visuals (1)
• Website (1)
• WordPress (1)