Archives

How to optimize MySQL/MariaDB tables

The commands from this post will work on MySQL and MariaDB server.

It’s a good idea to perform database maintenance from time to time. One thing is to do is to optimize the tables. We have two options:

1. OPTIMIZE TABLE command

Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

See below how to use it.

root@web [~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3670
Server version: 10.1.22-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use roundcube
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [roundcube]> OPTIMIZE TABLE cache;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| roundcube.cache | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| roundcube.cache | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

MariaDB [roundcube]> quit
Bye
root@web [~]#

If you want to run the command for multiple tables from the same database, use:

OPTIMIZE TABLE table1,table2,table3;
OPTIMIZE TABLE works with InnoDB, MyISAM, and ARCHIVE tables.

2. mysqlcheck command

The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

To check one table use: mysqlcheck db_name tbl_name
To check all tables from a database: mysqlcheck –databases db_name
To check the tables from all the databases on the server: mysqlcheck –all-databases

Notice that database tables are locked while mysqlcheck is running. No records can be inserted or deleted from the tables.
root@web [~]# mysqlcheck roundcube
roundcube.cache                                    OK
roundcube.cache_index                              OK
roundcube.cache_messages                           OK
roundcube.cache_shared                             OK
roundcube.cache_thread                             OK
roundcube.contactgroupmembers                      OK
roundcube.contactgroups                            OK
roundcube.contacts                                 OK
roundcube.cp_schema_version                        OK
roundcube.dictionary                               OK
roundcube.identities                               OK
roundcube.searches                                 OK
roundcube.session                                  OK
roundcube.system                                   OK
roundcube.users                                    OK
root@web [~]# 

To optimize a database, use:

root@web [~]# mysqlcheck -o roundcube
roundcube.cache
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_index
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_messages
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_shared
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_thread
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroupmembers
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroups
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contacts
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cp_schema_version                        Table is already up to date
roundcube.dictionary
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.identities
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.searches
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.session
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.system
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
root@web [~]#

To optimize all the database on the sever use:

root@web [~]# mysqlcheck -o -A

Resources:
OPTIMIZE TABLE manual
mysqlcheck manual

Share this post:

Change the default SSH server port number

The Secure Shell (SSH) Protocol is using by default port 22. As a virtual/dedicated server administrator, it’s always a good idea to change this. Changing the port to another number will stop many attacks – as attack tools will try to connect to port 22.

The commands we use are tested on a CentOS distribution with cPanel installed.
If you are using a firewall on your server, whitelist the new port number. After finishing the port change, block the old port number.

To change the SSH port: 

  1. Log in to your server as root.
  2. Open the /etc/ssh/sshd_config file in a text editor (like vi, nano etc.).
    vi /etc/ssh/sshd_config
  3. Look for the line
    Port 22
  4. Change the 22 value to 3700 for example. You can use a port from the User Ports Interval (1024-49151), as assigned by the Internet Assigned Numbers Authority (IANA).
  5. Save the file, exit the editor and restart the SSH server.
    service sshd restart
  6. Open a new terminal window (or using PuTTY) and try to connect to the new assigned port:
    ssh username@yourserver.com 3700

Port numbers are assigned in various ways, based on three ranges: System Ports (0-1023), User Ports (1024-49151), and the Dynamic and/or Private Ports (49152-65535); the difference uses of these ranges is described in [RFC6335].

Related KB article: How to install nano editor with yum

Share this post:

Set up an Apache+PHP+MySQL local environment

You want to have a local Apache web server to test your new site? We’ll show you how to setup a web server and an MySQL server using XAMPP. XAMPP is a software that will install Apache+PHP+MySQL on your computer. Of course, you can download Apache, PHP and MySQL and install them by yourself, but using a suite like XAMPP will make the things much easier.

This tutorial is for XAMPP on Windows OS. XAMPP is also available for Linux and Max OS.

So, the steps to install the Apache+MariaDB(MySQL) development environment are:

1.Download XAMPP from https://www.apachefriends.org/index.html (the Windows Installer is about 110MB)

XAMPP Site

2. Install XAMPP. During the installation, choose the components you want to install.

XAMPP Installer Components

3. Start XAMPP Control Panel. 

4. Start the modules you want to run – in our case Apache(which includes PHP) and MySQL.

XAMPP Control Panel

You can now access the web server on your web browser at http://localhost/. The root folder of the web server is c:\xampp\htdocs\ In this folder you will put your site’s files.

To manage the database server go to http://localhost/phpmyadmin/. phpMyAdmin is a graphical tool that allow you perform various database operations.

And the video tutorial:

Resources: https://www.apachefriends.org/index.html

Share this post:

Working with cron jobs – create, edit and delete them with cPanel

Cron jobs allow you run a command or script on your site at user-defined time intervals.

To create a cron job in cPanel:

    1. Login to your cPanel account
    2. Go to Cron Jobs (ADVANCED Section)
    3. If you want to receive the output of the cron job then enter the email in the Email field and click the Update Email button
    4. Go to the Add New Cron Job section, select the Common Settings from the drop-down list. You can choose from the predefined values or you can enter your own values.
    5. Enter the command you wish to run. For example, to run a php file, use the command : home/username/public_html/test.php
    6. Click the Add New Cron Job button
    7. The new cron job will appear in the Current Cron Jobs Section

For the Common Settings you can choose from :

-Once Per Minute (* * * * *)

-Once Per Five Minutes (*/5 * * * *)

-Twice Per Hour (0,30 * * * *)

-Once Per Hour (0 * * * *)

-Twice Per Day (0 0,12 * * *)

-Once Per Day (0 0 * * *)

-Once Per Week (0 0 * * 0)

-On the 1st and 15th of the Month (0 0 1,15 * *)

-Once Per Month (0 0 1 * *)

-Once Per Year (0 0 1 1 *)

To delete a cron job in cPanel:

  1. Login to your cPanel account
  2. Go to Cron Jobs (ADVANCED Section)
  3. On the Current Cron Jobs Section, click the Delete link on the row with the cron job you want to delete.
  4. Click the Delete button when the cPanel is asking “Delete this cron job?
  5. The cron job is deleted now.

To edit a cron job in cPanel:

  1. Login to your cPanel account
  2. Go to Cron Jobs (ADVANCED Section)
  3. On the Current Cron Jobs Section, click the Edit link on the row with the cron job you want to edit.
  4. Make the necessary changes. When done, click the Edit Line button.
cPanel Cron Jobs
cPanel Cron Jobs
Many web hosting providers will block your cron job if the running interval is too short. Ask your host what is the minimum value.
A cron job must include Minute, Hour, Day, Month, Weekday and Command fields.
Share this post:

How to remove .php, .html extensions with .htaccess

Many times you will want to have user-friendly URLs on your site. Instead of https://www.example.com/index.html you will want to have https://www.example.com/index . The second URL looks much better. Also, from the SEO point of view is better to don’t use file extensions.

 The steps to remove file extensions are:

  1. Login to your cPanel account.
  2. Go to File Manager – in the FILES Section
  3. In the File Manager go to the Settings button on the top right corner.
  4. On the Preferences window that will appear check the Show Hidden Files (dotfiles) option. Click Save button to apply the settings.
  5. Now navigate to the .htaccess file. If the file doesn’t exist you will need to create it.
  6. Click the Edit button from the File Manager top menu.
  7. Add the below lines to the .htaccess file. Click the Save Changes button and then the Close button. 
    #remove php file extension-e.g. https://example.com/file.php will become https://example.com/file
    RewriteEngine on 
    RewriteCond %{REQUEST_FILENAME} !-d
    RewriteCond %{REQUEST_FILENAME}\.php -f
    RewriteRule ^(.*)$ $1.php [NC,L]
    
  8. Now the Apache web server will remove .php extension from URLs.

To remove .html extension use:

#remove html file extension-e.g. https://example.com/file.html will become https://example.com/file
RewriteEngine on 
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME}\.html -f
RewriteRule ^(.*)$ $1.html [NC,L]
Notice that many PHP scripts (like WordPress, Drupal, MediaWiki etc) need the .htaccess files to function properly. Don’t delete these files. Also take care when editing them.

Check the video tutorial we made:

You can also edit .htaccess files on your web hosting account via an FTP client like FileZilla.
Share this post:
Page 3 of 4
1 2 3 4