Archives

Use phpMyAdmin to manage your databases

phpMyAdmin is a PHP web script that allows you to manage databases.
cPanel includes phpMyAdmin. Also, you can install phpMyAdmin from Softaculous. But if for any reason, you can’t access it in cPanel or Softaculous, you can just set it up on your web hosting account.

How to configure phpMyAdmin on your account:

  1. Download the latest version archive from https://phpmyadmin.net/downloads/
  2. Extract the files to your computer.
  3. Create a folder on your web hosting account (like /public_html/mymysql).
    You should password protect this directory. Check out our article How to password protect a directory via cPanel
  4. Upload the files you extracted at 2. (it can take some time as there are ~4,843 files)
  5. Now access the phpMyAdmin script at yourdomain.com/mymysql/
  6. Enter your MySQL/MariaDB database Username and Password and click the Go button to login.

phpMyAdmin Login Screen

On all our web hosting plans you have access to phpMyAdmin from cPanel. Also you can easily install phpMyAdmin from Softaculous. If you need any help, don’t hesitate to contact us. Thank you.
Share this post:

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:

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:

How to get MySQL/Apache/PHP versions

You can use these commands on a virtual private server (VPS) or dedicated server running CentOS. Usually, you will use the CentOS for a web hosting server. Many times you want to check the versions of Apache, PHP and MySQL server.

For MySQL:

root@web [/home/admin]# mysql -V
mysql Ver 14.14 Distrib 5.5.29, for Linux (i686) using readline 5.1
root@web [/home/admin]# 

Notice that capital “V” from the first command.

Alternatively you can use:

root@host [/]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 273052
Server version: 5.5.28-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.1.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.28-cll |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql>
root@host [/]#

For PHP use:

root@host [/]# php -v
PHP 5.3.21 (cli) (built: Jan 27 2013 06:28:24)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2013 Zend Technologies
with the ionCube PHP Loader v4.2.2, Copyright (c) 2002-2012, by ionCube Ltd.
root@host [/]#

For Apache try :

root@host [/]# /usr/local/apache/bin/httpd -v
Server version: Apache/2.2.23 (Unix)
Server built: Jan 27 2013 06:23:25
Cpanel::Easy::Apache v3.16.7 rev9999
root@host [/]#

As you can see, this command will also output the Easy Apache version – if the server has cPanel installed.

Share this post:
Page 2 of 2
1 2