Tag Archives: MySQL

WordPress

How To Clean Unnecessary Tables From MySQL Database?

Leave a comment

Cleaning unnecessary database tables means deleting tables that were created before by some plugin and now are not in use on your website. Removing such tables lightens up your MySQL database. In this lesson, you will learn about cleaning up a database manually and with plugins’ help.

Before making any changes, always ensure and be careful when deleting database tables, as removing the wrong ones can break your WordPress site. Always keep a full WordPress database backup.

Step-1, access your hosting’s cPanel and locate the phpMyAdmin option under the Databases section. Click on it to access the database management interface.

Step-2 In phpMyAdmin, find your WordPress database on the left-hand side panel. Click on the name of the database to view the tables it contains.

Step-3 Now identify unnecessary tables. Unnecessary tables might come from uninstalled plugins or themes. Tables that don’t seem to match the standard WordPress table names like wp_posts, wp_users, or wp_options may be safe to delete. Some tables have a prefix based on the name of a plugin (e.g., wp_woocommerce_* for WooCommerce).

Step-4 Once you identify the tables you no longer need, select them by checking the box next to each table. Then, scroll down and choose Drop from the options in the dropdown menu. Confirm the deletion.

Automatically Identify & Clean MySQL Database Tables

Alternatively, You can clean your database with a plugin. Plugins such as WP-Optimize or Advanced Database Cleaner let you automatically identify and clean up unnecessary tables without manually going into phpMyAdmin.

These WordPress plugins provide you with an easy way to clean up unnecessary database tables from your WordPress site.

WordPress

What Are the Standard WordPress Database Tables? A Breakdown Of Core WP Tables

Leave a comment

A standard WordPress installation creates a set of database tables that store various types of data, such as posts, users, comments, and settings. Apart from this plugins create their tables and some themes may also do that, but deactivating such plugins and themes leaves their tables on your website clattering your database.

In this lesson, you will learn about the main 12 standard WordPress database tables so that whenever you like to clean up your database you don’t disturb the core tables that are the life-line of your WordPress site.

  1. wp_posts – Stores all content types like posts, pages, revisions, and custom post types.
  2. wp_postmeta – Contains metadata (custom fields) for posts, such as additional information like SEO settings or image data.
  3. wp_users – Holds information about all registered users, including username, password, email, and user roles.
  4. wp_usermeta – Stores additional metadata about users, such as permissions, preferences, and profile information.
  5. wp_options – Contains various site-wide settings such as the site URL, admin email, time zone, and installed plugins.
  6. wp_terms – Stores the categories, tags, or any custom taxonomies used to classify content.
  7. wp_term_taxonomy – Describes the taxonomy (e.g., category, tag) for each term from the wp_terms table.
  8. wp_term_relationships – Links posts, pages, or custom post types to their respective terms (categories, tags).
  9. wp_comments – Stores comments on posts and pages, including comment content, user details, and approval status.
  10. wp_commentmeta – Contains metadata about comments, such as IP addresses, ratings, or spam status.
  11. wp_links – Used to manage the blogroll feature, which is now deprecated but still exists in older versions.
  12. wp_actionscheduler_actions (optional in newer WordPress versions) This is used to store scheduled actions or cron jobs, commonly created by WooCommerce and other plugins.

Each table uses a default prefix of wp_.

While installing WordPress manually on your hosting account, a step asks you to enter the database name, database username, database user password, and host and the last field asks for the table prefix which by default is wp_.

This wp_ is the table prefix. this can be changed during installation to enhance security (e.g., mywebsite_) and it also lets you host multiple websites on a single database.

To change the table prefix while installing WordPress using Installatron, in the “Advanced Settings” section, you’ll find an option for “Table Prefix.” By default, the table prefix is set to wp_, but you can modify this to a unique prefix, such as mywebsite_ or any combination of letters and numbers for added security.

After entering your desired prefix, continue with the installation process as usual. This change helps secure your site by preventing common SQL injection attacks that target the default table prefix.

WordPress

How To Run Multiple Websites In One Database?

Leave a comment

Some low-cost hosting options offer you one or a limited amount of MySQL databases. But do you know that you can run multiple websites in one database, and all websites will work independently without affecting the content and logins of other websites running on the same database.

You can also install different CMS or applications on the same database. For instance, WordPress and MediaWiki can run on a single database without affecting each other.

Running multiple websites in a single database is possible by using table prefixes or customizing your database structure. Here’s how you can set it up, especially if you are using a CMS like WordPress.

While installing WordPress manually on your hosting account, a step asks you to enter the database name, database username, database user password, and host and the last field asks for the table prefix which by default is wp_. This wp_ is the table prefix.

So all you have to do instead of using wp_ you have to use a different table prefix for every new website on your database.

For example,

When you install a CMS like WordPress, you can specify a table prefix during installation. If you want to run multiple websites in one database, use different table prefixes for each website. For example:

  • Website 1: wp1_
  • Website 2: wp2_

This way, the tables for each website will be separated within the same database.

For easy remembrance you can use wp1_ , wp2_, wp3_, wp4_ and so on. Not only WordPress but you can install any CMS by using a different unique for each website you install on your database.

For non-CMS-based websites, you can manually create separate tables for each website within the same database.

For example, you can create tables for:

  • website1_users
  • website2_users
  • website1_posts
  • website2_posts

Alternatively, installing a WordPress multisite network is an advanced way of making multiple WordPress sites on the same database. Its main advantage is that you don’t have to install WordPress separately for every new website you create.

Simply add the following line to your wp-config.php file:

define( ‘WP_ALLOW_MULTISITE’, true );

Now log in to your WordPress dashboard and navigate to Tools -> Network Setup to configure it.

By implementing these methods, you can efficiently run multiple websites in one database without needing separate databases for each.

System Engineering

How To Restore MySQL Databases Using cPanel?

Leave a comment

In the previous lesson, you learned how to easily generate and download MySQL database backups anytime from the cPanel. Next is how to restore them.

To restore a MySQL database from cPanel, you need to follow these steps:

Step 1: Log into cPanel

Open your browser and log into your cPanel account by entering your username and password.

Step 2: Access the Backup Feature

Once logged in, scroll to the Files section on the cPanel dashboard. Click on Backup or Backup Wizard.

Step 3: Choose the Database Restore Option

Under the Restore a MySQL Database section, locate the Restore a MySQL Database Backup option. Select this to begin the process.

Step 4: Upload Your Database Backup

Click the Choose File button and select the .sql or .gz backup file you downloaded. After selecting the file, click on Upload or Restore to initiate the restoration process.

Step 5: Completion

Once the file is uploaded, cPanel will automatically restore your database. As soon as the restoration is complete, you will receive a confirmation message.

By following these steps, your MySQL database will be successfully restored in cPanel.

System Engineering

How To Backup & Download MySQL Databases Using cPanel?

Leave a comment

You can easily generate and download MySQL database backups anytime from the cPanel, by following these steps:

Step 1: Log into cPanel

Open your browser and log into your cPanel account by entering your username and password.

Step 2: Access the Backup Feature

Once logged in, navigate to the Files section on the cPanel dashboard. Click on Backup or Backup Wizard, both of which allow you to back up your database.

Step 3: Choose the Database Backup

If you’re using the Backup Wizard, choose the option to back up a specific part of your site. Under the Partial Backups section, locate the Download a MySQL Database Backup option. A list of databases will appear; select the name of the database you wish to back up.

Step 4: Download the Backup

After selecting the database, the backup process will begin automatically. Your browser will prompt you to download the .sql or .gz file. Save the backup file to your local computer.

This backup file can be used for future database restoration.

WordPress

Performing Bulk Edits In WordPress Database With Better Search Replace Option

Leave a comment

In the previous lesson, you learned about editing MySQL databases using the search-replace option on the cPanel. For the more complex tasks, WordPress users may use the ‘Better Search Replace‘ plugin to effortlessly update their website’s database from their admin area dashboard.

Using the Better Search Replace WordPress plugin is straightforward and can save you a lot of time when you need to perform bulk updates across your database. Here’s a step-by-step guide on how to use it:

Step 1: Install and Activate the Plugin

  1. Log in to Your WordPress Admin Dashboard
  2. Install and activate the ‘Better Search Replace’ plugin from Plugins -> Add New page.
  3. Go to the Plugin Settings: In your WordPress dashboard, navigate to Tools > Better Search Replace to open the plugin’s settings page.

Step 2: Configure Your Search and Replace Operation

  1. Enter the Search Term: In the Search for field, type the text or URL you want to search for in your database.
  2. Enter the Replace Term: In the Replace with field, type the text or URL you want to replace the original text with.
  3. Select the Tables to Update: Under the Select Tables section, choose the database tables where you want to perform the search and replace operation. You can select multiple tables or use the Select All option.
    • Common Tables:
      • wp_posts for post content and pages.
      • wp_options for site settings.
      • wp_postmeta for custom fields.

Step 3: Run a Dry Run (Optional but Recommended)

  1. Enable the Dry Run Option: Check the Run as dry run? option. This will simulate the search and replace operation without making actual changes, allowing you to see how many fields will be affected.
  2. Run the Dry Run: Click the Run Search/Replace button. Review the results to ensure that the operation will do what you intend.

Step 4: Execute the Search and Replace

  1. Disable the Dry Run Option: Uncheck the Run as dry run option.
  2. Run the Search/Replace: Click the Run Search/Replace button to execute the operation. The plugin will now make changes to your database based on your configuration.
  3. Check the Affected Content: After the plugin has run, review your site to ensure the changes have been applied correctly.
  4. Clear Caches (If Applicable): If you’re using a caching plugin, clear your site’s cache to ensure that the changes are reflected on the front end.

Step 5: Repeat if Necessary

If you need to run another search and replace operation on different tables or with different search/replace terms, repeat the steps above.

Additional Tips:

  • Backup Your Database: It’s always good practice to back up your database before making bulk changes. So that if you encounter any issues, you can restore your database from the backup you created earlier.
  • Serialized Data: The plugin automatically handles serialized data, which is essential for many WordPress plugins and themes. This means you don’t have to worry about breaking serialized arrays during the search and replace process.
  • Multisite Support: If you’re running a WordPress Multisite network, you can perform search and replace operations on individual sites or across the entire network.

Following these steps, you can efficiently update URLs, text, or any other content across your WordPress site.

System Engineering

How To Search And Replace Across The MySQL Database Via cPanel?

Leave a comment

There are several scenarios where someone might need to edit or perform a search and replace operation on MySQL database tables. The reasons for editing MySQL database can be website migration, updating URLs, migrating to HTTPS, fixing errors, replacing content, etc.

To search and replace across a MySQL database via cPanel, follow these steps:

Step 1: Access phpMyAdmin in cPanel

  1. Log in to cPanel: Use your username and password to access your cPanel account.
  2. Open phpMyAdmin: Scroll down to the “Databases” section and click on phpMyAdmin.

Step 2: Select the Database

  1. Choose Your Database: In phpMyAdmin, locate the list of databases on the left-hand side. Click on the database where you want to perform the search and replace operation.
  2. Backup Your Database (Optional but recommended): Before making any changes, it’s wise to back up your database. You can do this by selecting Export at the top menu and saving the file to your local computer.

Step 3: Execute the Search and Replace Query

  1. Go to the SQL Tab: Once your database is selected, click the SQL tab at the top of the phpMyAdmin interface.
  2. Enter the SQL Query: In the query box, enter the following SQL command and click the ‘Go’ button.

UPDATE table_name
SET column_name = REPLACE(column_name, ‘old_value’, ‘new_value’);

  • Replace table_name with the name of the table you want to update.
  • Replace column_name with the name of the column where the search and replace should occur.
  • Replace 'old_value' with the text you want to search for.
  • Replace 'new_value' with the text you want to replace it with.

For example, if you want to replace all instances of “oldsite.com” with “newsite.com” in the wp_posts table under the post_content column, the query would look like this:

UPDATE wp_posts
SET post_content = REPLACE(post_content, ‘oldsite.com’, ‘newsite.com’);

Clicking the Go button will execute the query. phpMyAdmin will now search for the old value and replace it with the new one.

Step 4: Review the Changes

  1. Check the Results: After running the query, review your website or relevant database entries to ensure the search and replace operation works correctly.
  2. Restore from Backup if Necessary: If something goes wrong, you can restore your database using the backup you created earlier.

Step 5: Repeat for Other Tables (If Necessary)

If you need to perform the search and replace across multiple tables or columns, repeat the process, adjusting the table_name and column_name values as needed.

Remember:

  • To be cautious when performing database operations, as they can affect your website’s functionality.
  • Always back up your database before making significant changes.
  • Consider using a WordPress plugin like Better Search Replace for more complex or extensive search and replace operations, especially if you’re dealing with serialized data.
System Engineering

How To Repair MySQL Database?

Leave a comment

A MySQL database is a relational database management system (RDBMS) that utilizes structured query language (SQL) to store, retrieve, and manage structured data.

MySQL databases consist of tables organized into rows and columns, with each table representing a specific data entity and each row representing a single record or entry. MySQL is widely used for web applications and websites, offering features such as data integrity, transaction support, scalability, and robust security mechanisms.

It is an open-source database system, making it accessible and customizable for a wide range of applications and industries. MySQL is known for its reliability, performance, and ease of use, making it a popular choice for developers and businesses seeking a powerful and cost-effective solution for managing their data.

Repairing a MySQL database involves several steps to identify and fix any issues that may be causing corruption or damage to the database. But to fix a database you need to access it. To access a MySQL database on cPanel, you typically follow these steps:

  1. Log in to cPanel: Open your web browser and navigate to your cPanel login page. This is usually accessed by appending “/cpanel” to your domain name (e.g., yourdomain.com/cpanel). Enter your cPanel username and password to log in. If this doesn’t works, then alternatively you can access the cPanel from your hosting’s account page.
  2. Locate the MySQL Databases Icon: Once logged in, you’ll see a variety of icons representing different features and functions available in cPanel. Look for the “MySQL Databases” or “Databases” icon, which is usually located under the “Databases” section.
  3. Access MySQL Databases: Click on the “MySQL Databases” icon to access the MySQL Databases interface. This interface allows you to manage your MySQL databases, including creating new databases, adding users, and assigning privileges.
  4. Locate Your Database: In the MySQL Databases interface, you’ll see a list of existing databases under the “Current Databases” section. Each database will have a name preceded by your cPanel username and an underscore (e.g., username_database1). Locate the database you want to access.
  5. Access phpMyAdmin: To interact directly with your MySQL database, click on the “phpMyAdmin” icon, which is usually located next to the database name. phpMyAdmin is a web-based application that provides a graphical user interface for managing MySQL databases.
  6. Log in to phpMyAdmin: After clicking on the phpMyAdmin icon, you’ll be directed to the phpMyAdmin login page. Enter the username and password for the MySQL database user associated with the database you want to access. This may be different from your cPanel login credentials.
  7. Navigate Your Database: Once logged in to phpMyAdmin, you’ll see a list of databases on the left-hand side of the screen. Click on the name of the database you want to access to expand it and view its tables. You can then click on individual tables to view and manage their contents.
  8. Perform Database Operations: From the phpMyAdmin interface, you can perform various database operations, such as running SQL queries, creating or modifying tables, importing or exporting data, and managing users and permissions.
  9. Log Out and Close phpMyAdmin: After you’ve finished working with your database, be sure to log out of phpMyAdmin to secure your data. You can do this by clicking the “Log Out” button in the phpMyAdmin interface. Once logged out, you can close the phpMyAdmin window or tab.

By following these steps, you can easily access and manage your MySQL databases on cPanel using phpMyAdmin.

Here’s a general guide on how to repair a MySQL database:

Backup Your Database: Before making any changes to your database, it’s essential to create a backup to ensure that you can restore your data in case anything goes wrong during the repair process. You can use MySQL’s built-in tools or third-party backup solutions to create a backup of your database.

Identify Database Issues: Use MySQL’s diagnostic tools to identify any issues or errors in your database. You can check the MySQL error log, run diagnostic queries, or use tools like MySQL Workbench to analyze the database’s structure and integrity.

Use MySQL Check Table Command: MySQL provides a built-in command called CHECK TABLE that allows you to check the integrity of tables in your database and repair any corruption or damage. You can use this command to check individual tables or the entire database.

CHECK TABLE table_name;

Use MySQL Repair Table Command: If the CHECK TABLE command identifies any issues with your tables, you can use the REPAIR TABLE command to fix them. This command repairs corrupted tables by rebuilding the table’s indexes and data structure.

REPAIR TABLE table_name;

Use MySQL Repair Database Command: If you suspect that your entire database is corrupted, you can use the REPAIR DATABASE command to repair all tables in the database at once.

REPAIR DATABASE database_name;

Use MySQL Table Maintenance Operations: MySQL provides additional table maintenance operations, such as OPTIMIZE TABLE and ANALYZE TABLE, which can help improve the performance and integrity of your database. You can use these commands periodically to optimize and maintain your database.

OPTIMIZE TABLE table_name;

ANALYZE TABLE table_name;

Monitor Repair Progress and Errors: During the repair process, monitor the progress and check for any errors or warnings that may occur. This will help you ensure that the repair is successful and that no further issues arise.

Verify Database Integrity: After repairing your database, verify its integrity by running diagnostic queries and checking for any remaining issues. Ensure that your data is intact and that the database functions properly.

Regular Maintenance and Monitoring: To prevent future database issues, implement regular maintenance tasks such as backups, monitoring, and optimization. Regularly check for errors, monitor database performance, and address any issues promptly to keep your MySQL database healthy and reliable.

By following these steps and best practices, you can repair your MySQL database and ensure that it remains stable, reliable, and free from corruption or damage.

System Engineering

Configuring MySQL Repository On Linux Server CentOS

Leave a comment

CentOS offers you no native MySQL package so you need to add it on your own. This you can do by adding a MySQL hosted repository on your Linux System CentOS.

Add the MySQL Yum repository to your system’s repository list. This can be done by installing an RPM that comes with MySQL.

  1. Go to the Download MySQL Yum Repository page (https://dev.mysql.com/downloads/repo/yum/) in the MySQL Developer Zone
  2. Select and download the release package for your platform
  3. Use the yum package manager to install MySQL (yum -y install mysql-server)
  4. Enable the MySQL Daemon Service, use these commands systemctl start mysql followed by systemctl enable mysql

That’s it. Now you may check and make sure that MySQL is up and running, simply use this command – sudo service mysqld status.

System Engineering, Websites

Importing MySQL Databases Via SSH Command Line On Self Managed Servers

Leave a comment

SANGKRIT.net not only offers you Managed and Fully Managed servers but it also lets you subscribe self-managed server option which is only recommended for command line experts. Because self-managed servers are without a control panel such as cPanel, Plesk, or Simple Control Panel.

These self-managed servers do not have phpMyAdmin installed which is used for managing MySQL databases. So in order to manage databases, you are required to use the SSH command line to import .SQL backup files.

This post guides you through importing MySQL databases via SSH.

Continue reading Importing MySQL Databases Via SSH Command Line On Self Managed Servers →

WordPress

What Is Increased Time To 1st Byte? How It Affects Your Website’s Speed?

Leave a comment

An increased time to 1st byte means your website is running too many SQL queries or non-optimized SQL queries which can also include server-side calls to third-party APIs.

If you’re a WordPress user, there are a number of plugins you can check out to decrease the number of database queries and speed up your website. And if you are not using WordPress then you need to check and optimize your database then use some website cache functionality. For this, you may buy expert’s support via system@sangkrit.net on per hour per terminal basis. Continue reading What Is Increased Time To 1st Byte? How It Affects Your Website’s Speed? →

WordPress

How To Find Your WordPress Hostname & Server IP Address?

Leave a comment

Not generally but many a times website owners require knowing their hostname and server IP address, it is only useful in certain situations like many security plugins and SSL certificate extensions asks you for your IP and host information to set up configuration settings and there are many more reasons.

Continue reading How To Find Your WordPress Hostname & Server IP Address? →

WordPress

Best Plugins For Optimizing & Managing Database Tables In WordPress

Leave a comment

Generally there are two ways webmasters use for optimization database tables, some do it via phpMyAdmin which works from your webserver or your web hosting account and the other way is using a database optimization plugin which is more easy and user friendly way. There are hundreds of database optimization plugins available in WordPress plugin repository which confuses the new WordPress users.

Continue reading Best Plugins For Optimizing & Managing Database Tables In WordPress →

Technology, WordPress

Check CPU Performance Of PHP & MySQL Server Right From WordPress Admin Area

Leave a comment

With PHP/MySQL CPU performance statistics WordPress plugin you can check your CPU performance of your PHP and MySQL database time to time right from your WordPress  admin area dashboard.

Continue reading Check CPU Performance Of PHP & MySQL Server Right From WordPress Admin Area →

Technology, WordPress

How To Change WordPress Site Domain Name By Editing Its Database ?

1 Comment

We have already discussed about changing your website domain name or URL by editing functions.php file and by editing wp-config.php file. Now this tutorial explains you how you can update your website web address to new one (when you are migrating your website to a new domain) directly by updating your WordPress site’s MySQL database.

Continue reading How To Change WordPress Site Domain Name By Editing Its Database ? →

Technology, WordPress

Top 5 Plugins For Migrating Your Blog To WordPress

Leave a comment

Thinking of migrating your blog to WordPress, here are 5 cool plugins that can help you doing that in a better and easy way possible and ofcourse I am takling about WordPress.Org self hosted blog not WordPress.Com. If you are migrating your blog from Blogger then you must read this post.

Continue reading Top 5 Plugins For Migrating Your Blog To WordPress →

Technology, WordPress

Add Debug Bar Showing Query, Cache, And Other Helpful Information On Debugging In WordPress

Leave a comment

WordPress developers launched WordPress.Com’s debug bar for Self Hosted WordPress Blogs . It adds a debug menu to WP admin bar that shows query, cache, and other helpful debugging information. Good for developers.

Continue reading Add Debug Bar Showing Query, Cache, And Other Helpful Information On Debugging In WordPress →

Technology, WordPress

Display Your Host’s PHP, MySQL & Memcached Information On WordPress Dashboard

Leave a comment

Display your host’s PHP, MySQL & Memcached (if installed) information on your WordPress site dashboard.

Continue reading Display Your Host’s PHP, MySQL & Memcached Information On WordPress Dashboard →