Category Archives: MySQL

Fixing phpmyadmin, Connection for controluser as defined in your configuration failed.

Posted on by

This happens when the phpmyadmin package is installed, but for some reason or another the automation the package manager and phpmyadmin have for setting up the phpmyadmin user, and phpmyadmin database doesn’t properly use the table schema from /usr/share. Here is the process of fixing this error for those that get it.

Create a database called phpmyadmin

create database phpmyadmin;

You can actually call the database anything as long as you remember what you changed it to later.

Create a database user

MariaDB [(none)]> GRANT ALL PRIVILEGES ON phpmyadmin.* to phpmyadmin@localhost identified by 'AVERYSECUREpasswordgoeshere98123123sdabcsd123' ;
Query OK, 0 rows affected (0.00 sec)

Locate the create_tables.sql file copied by the package manager (or zip if installing from source)


[root@linuxbox phpMyAdmin]# find /usr/share | grep create_table
/usr/share/phpMyAdmin/sql/create_tables.sql
/usr/share/phpMyAdmin/sql/create_tables_drizzle.sql
/usr/share/phpMyAdmin/libraries/display_create_table.lib.php
/usr/share/phpMyAdmin/test/libraries/PMA_display_create_table_test.php

Import the database schema

# Check the file is correct
[root@wlinuxbox---wp phpMyAdmin]# vi /usr/share/phpMyAdmin/sql/create_tables.sql

# Import it
[root@wlinuxbox wp phpMyAdmin]# mysql -u root -p < /usr/share/phpMyAdmin/sql/create_tables.sql
Enter password:

Afterwards you will need to make phpmyadmin aware of the creds in /etc/phpMyAdmin/config.inc.php 

vi /etc/phpMyAdmin/confing.inc.php

Confirm your changes

[root@wd-linux-8---wp phpMyAdmin]# cat /etc/phpMyAdmin/config.inc.php | grep -A3 phpmyadmin
 * wiki <http://wiki.phpmyadmin.net>.
 */

/*
--
$cfg['Servers'][$i]['controluser']   = 'phpmyadmin';          // MySQL control user settings
                                                    // (this user must have read-only
$cfg['Servers'][$i]['controlpass']   = 'AVERYSECUREpasswordgoeshere98123123sdabcsd123';          // access to the "mysql/user"

$cfg['Servers'][$i]['pmadb']         = 'phpmyadmin'

Your work is done, and that pesky error is gone now phpmyadmin has it’s DB. This tutorial has been a long time coming as I see this all the time.

Enabling MySQL Slow Query Logs

Posted on by

In the case your seeing very long pageload times, and have checked your application. It always pays to check the way in which the database performs when interacting with the application, especially if they are on either same or seperate server, as these significantly affect the way that your application will run.


mysql> SET GLOBAL slow_query_log = 'ON' ;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL slow_query_log_file = '/slow_query_logs/slow_query_logs.txt';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.00 sec)

Moving a WordPress site – much ado about nothing !

Posted on by

Have you noticed, there is all kinds of advise on the internet about the best way to move WordPress websites? There is literally a myriad of ways to achieve this. One of the methods I read on
wp.com was:

Changing Your Domain Name and URLs

Moving a website and changing your domain name or URLs (i.e. from http://example.com/site to http://example.com, or http://example.com to http://example.net) requires the following steps - in sequence.

    Download your existing site files.
    Export your database - go in to MySQL and export the database.
    Move the backed up files and database into a new folder - somewhere safe - this is your site backup.
    Log in to the site you want to move and go to Settings > General, then change the URLs. (ie from http://example.com/ to http://example.net ) - save the settings and expect to see a 404 page.
    Download your site files again.
    Export the database again.
    Edit wp-config.php with the new server's MySQL database name, user and password.
    Upload the files.
    Import the database on the new server.

I mean this is truly horrifying steps to take, and I don’t see the point at all. This is how I achieved it for one my customers. 

1. Take customer Database Dump
2. Edit the database searching for 'siteurl' with vi
vi mysqldump.sql
:?siteurl

And just swap out the values, confirming after editing the file;

[root@box]# cat somemysqldump.sql  | grep siteurl -A 2
(1, 'siteurl', 'https://www.newsiteurl.com', 'yes'),
(2, 'home', 'https://www.newsiteurl.com', 'yes'),
(3, 'blogname', 'My website name', 'yes'),

Job done, no stress https://codex.wordpress.org/Moving_WordPress.

There might be additional bits but this is certainly enough for them to access the wp-admin panel. If you have problems add this line to the wp-config.php file;

define('RELOCATE',true);

Just before the line which says

/* That’s all, stop editing! Happy blogging. */

And then just do the import/restore as normal;

mysql -u newmysqluser -p newdatabase_to_import_to < old_database.sql

Simples! I really have no idea why it is made to be so complicated on other hosting sites or platforms.

Controlling MySQL Queries, and killing if necessary.

Posted on by

Today we had a customer call in who had a problem with their magento site. What had happened was they had reached the maximum number of connections for their MySQL. 

 250481 | someuser | localhost:7777 | somewebsite | Query   | 2464 | Writing to net               | SELECT /*!40001 SQL_NO_CACHE */ * FROM `mg_sales_flat_quote_address`                                 

| 250486 | someuser | localhost       | somewebsite | Query   | 2459 | Waiting for table level lock | INSERT INTO `mg_sales_flat_quote_address` (`quote_id`, `created_at`, `updated_at`, `customer_id`, `a |         0 |             0 |         0 |

.. and a whole load more waiting for table level lock were underneath

Solution is simple;

kill 250481

250481 is the processid of the select query causing these issues. I suspect it will go away, eventually but in the meantime, want to make sure the max connections don’t get reached, otherwise website will be down again!

Creating a New user with permissions for MySQL

Posted on by

As it says on the tin

Create the user

mysql> create user mysqlusergoeshere@localhost identified by 'somepasswordcangoheremakeitsecure';
Query OK, 0 rows affected (0.03 sec)

Create the Permissions for the user 

mysql> GRANT ALL PRIVILEGES ON database_name.* to mysqlusergoeshere@localhost;
Query OK, 0 rows affected (0.01 sec)

Simples.

Fixing Innodb Registered as a storage Engine Failed

Posted on by

So you have a nice wordpress (or similar site) running, but then all of a sudden you got a nasty message telling you that, the InnobDB registration as a storage engine failed.

# tail /var/log/mariadb/mariadb.log
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/error-creating-innodb.html
160914  6:45:29 [ERROR] Plugin 'InnoDB' init function returned error.
160914  6:45:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160914  6:45:29 [ERROR] Failed to initialize plugins.
160914  6:45:29 [ERROR] Aborting

160914  6:45:29 [Note] /usr/libexec/mysqld: Shutdown complete

160914 06:45:29 mysqld_safe mysqld from pid file /var/lib/mysql/wpstack.localdomain.pid ended

# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─limits.conf
   Active: failed (Result: exit-code) since Wed 2016-09-14 06:25:38 UTC; 2min 32s ago
  Process: 1434 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
  Process: 1433 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 1305 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 1433 (code=exited, status=0/SUCCESS)

Sep 14 06:25:35 wpstack.localdomain systemd[1]: Starting MariaDB database se....
Sep 14 06:25:37 wpstack.localdomain mysqld_safe[1433]: 160914 06:25:37 mysqld...
Sep 14 06:25:37 wpstack.localdomain mysqld_safe[1433]: 160914 06:25:37 mysqld...
Sep 14 06:25:38 wpstack.localdomain systemd[1]: mariadb.service: control pro...1
Sep 14 06:25:38 wpstack.localdomain systemd[1]: Failed to start MariaDB data....
Sep 14 06:25:38 wpstack.localdomain systemd[1]: Unit mariadb.service entered....
Sep 14 06:25:38 wpstack.localdomain systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.
[root@wpstack ~]# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
[root@wpstack ~]# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

This is naturally a problem since until it is resolved you have no database service running. It is fairly easy to resolve in most cases, by removing the log from var/lib/mysql

mv /var/lib/mysql/ib_logfile0{,.bak}  	   	 	   
mv /var/lib/mysql/ib_logfile1{,.bak}  	   	 	   
mv /var/lib/mysql/ibdata1{,.bak}

These simply move the files away, which allows InnoDB to continue operating. Please note that this may not always fix your issue and in some situations might result in data loss, so it is advisable to take a backup of the database filesystem before proceeding.

Discovering siteurl variable for WordPress

Posted on by

So I recently read a little piece by one of my colleagues about this. It comes up fairly frequently so it’s worth mentioning. It’s possible to determine the address that wordpress is using as the siteurl by directly querying the database or looking for the value in the sql dump.

Database changed
mysql> SELECT option_name,option_value FROM wp_options WHERE option_name='siteurl';
+-------------+---------------------------------------+
| option_name | option_value                          |
+-------------+---------------------------------------+
| siteurl     | http://mywordpresssite.com/ |
+-------------+---------------------------------------+
1 row in set (0.00 sec)

It turns out there is a second ‘home’ page variable in the database:

mysql> SELECT option_name,option_value FROM wp_options WHERE option_name='home';
+-------------+---------------------------------------+
| option_name | option_value                          |
+-------------+---------------------------------------+
| home        | http://mywordpresssite.com/test |
+-------------+---------------------------------------+
1 row in set (0.00 sec)

I’m not 100% on the difference between ‘siteurl’ and ‘home’, but guessing the siteurl is the tld definition of the domain, and home is the default landing page for requests to that TLD. As I understand it anyway, I am sure someone will correct me if this isn’t completely correct.

Backing up a MySQL Database remotely

Posted on by

So, you might want to backup a MySQL database remotely, like one of our customers did today. This is relatively simply utilizing the inbuilt mysqldump facility. This customer in particular was running varnish in front of his apache2 webserver so setting up phpmyadmin wasn’t entirely straight forward for this non technical customer. It’s easily achievable with something like;

Specific database

ssh -l user 1.1.1.1 "mysqldump -mysqldumpoptions databasenamegoeshere | gzip -3 -c" > /localpath/localfile.sql.gz

All databases

mysqldump -uroot -ppassword -h162.13.137.249 > backup.sql

The formatting of the command should look like

mysqldump -u root -p[root_password] -h [hostname] [database_name] > dumpfilename.sql

Installing Drupal 8 the hard way

Posted on by

Many people use phpmyadmin, but we’re going to do this properly to add users, databases and privileges. Heres how I did it.
Please note that this is a work in progress and is not finished yet .

Install httpd and mysql-server

yum install httpd mariadb-server php php-mysql

What you might find is that drupal 8 requires php5.5.9 lets install that;

[root@web-test-centos7 html]# yum install centos-release-scl
[root@web-test-centos7 html]# yum install php55-php-mysqlnd

Open Firewall port 80 http for CentOS 

     sudo iptables -I INPUT 1 -p tcp --dport 80 -j ACCEPT
     sudo iptables -I INPUT 1 -p tcp --dport 443 -j ACCEPT

Save firewall rules in CentOS 

/etc/init.d/iptables save

Alternatively, save firewall rules Ubuntu 

iptables-save > /etc/iptables.rules

Save firewall rules for all other distros 

iptables-save > /etc/sysconfig/iptables

Connect to MysQL to configure database user ‘drupal’ 

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.47-MariaDB MariaDB Server

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

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

Create Drupal database

MariaDB [(none)]> create database drupal;

Grant ability to connect with drupal user 

MariaDB [(none)]> grant usage on *.* to drupal@localhost identified by '@#@DS45Dfddfdgj334k34ldfk;DF';
Query OK, 0 rows affected (0.00 sec)

Grant all Privileges to the user drupal for database drupal

MariaDB [(none)]> grant all privileges on drupal.* to drupal@localhost;
Query OK, 0 rows affected (0.00 sec)