Tag Archives: mysql

InnoDB log file size

Question 1: Is my innodb_log_buffer_size too small?

Answer:
Since MySQL 5.0 there is a status called Innodb_log_waits. This status shows the number of times that the log buffer was too small. A wait is required for it to be flushed before continuing.

 

If this value is 0 or near innodb_log_buffer_size is defined well. If it is high and continuously growing, increase it or reduce the size of your transactions.

Question 2: What would be a better value?
Answer: https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

PHPMyAdmin on Centos/Ubuntu

BASIC SETUP (Apache)

>> Set password authentication
/etc/httpd/conf.d/phpMyAdmin.conf (or apache.conf on Ubuntu)

<Directory /usr/share/phpMyAdmin/>
# <IfModule mod_authz_core.c>
# Apache 2.4
# <RequireAny>
# Require ip 127.0.0.1
# Require ip ::1
# </RequireAny>
# </IfModule>
# <IfModule !mod_authz_core.c>
# Apache 2.2
# Order Deny,Allow
# Deny from All
# Allow from 127.0.0.1
# Allow from ::1
# </IfModule>
AuthUserFile /etc/httpd/.htpasswdfile
AuthName Restricted
AuthType Basic
require valid-user

</Directory>

>> Generate random password
PASS=$(tr -cd ‘[:alnum:]’ < /dev/urandom | fold -w12 | head -n1)

>> Set password automatically
htpasswd -bmc /etc/httpd/.htpasswdfile phpadminuser $PASS

>> Set password manually
htpasswd -c /etc/httpd/.htpasswdfile phpadminuser
(FYI ‘phpadminuser’ it’s the username)
>> To ADD users, just remove the -c flag

=====================================================================
Troubleshooting

curl -I http://<URL>/phpmyadmin/ –basic –user <username>:<password>

Example: (with error)
# curl -I http://<SERVERIP>/phpmyadmin/ –basic –user serverinfo:mxuYr35TTD5rgT3SR9ND
HTTP/1.1 500 Internal Server Error
Date: Thu, 25 Sep 2014 13:14:44 GMT
Server: Apache
Connection: close
Content-Type: text/html; charset=UTF-8

+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=

UBUNTU
>> Install the package
# apt-get update && apt-get -y install phpmyadmin

# ln -s /etc/phpmyadmin/apache.conf phpmyadmin.conf
# a2enconf phpmyadmin

>> Open firewall
ufw allow 80

>> When/if it asks the following:
> Please choose the web server that should be automatically configured to run phpMyAdmin => select apache2
> Configure database for phpmyadmin with dbconfig-common? => NO!!!

>> Enable mcrypt
php5enmod mcrypt
service apache2 graceful

>> Create phpmyadmin database and pmaadmin user
cd /usr/share/doc/phpmyadmin/examples
gunzip create_tables.sql.gz
mysql < create_tables.sql
mysql -e “GRANT SELECT, INSERT, DELETE, UPDATE ON phpmyadmin.* TO ‘pmaadmin’@’%’ IDENTIFIED BY ‘<PASSWORD>'”

>> Configuration file for phpmyadmin /etc/dbconfig-common/phpmyadmin.conf

mv /etc/dbconfig-common/phpmyadmin.conf{,.orig} ; vim /etc/dbconfig-common/phpmyadmin.conf

dbc_install=’false’
dbc_upgrade=’true’
dbc_remove=”
dbc_dbtype=’mysql’
dbc_dbuser=’pmaadmin’
dbc_dbpass='<PASSWORD>’
dbc_dbserver='<CLOUD_DB_HOST>’
dbc_dbport=”
dbc_dbname=’phpmyadmin’
dbc_dbadmin=’pmaadmin’
dbc_basepath=”
dbc_ssl=”
dbc_authmethod_admin=”
dbc_authmethod_user=”

>> Apply configuration
/usr/sbin/dbconfig-generate-include/etc/dbconfig-common/phpmyadmin.conf -f php > /etc/phpmyadmin/config-db.php

>> Disable MySQL different library warning
echo “\$cfg[‘ServerLibraryDifference_DisableWarning’] = true;” >> /etc/phpmyadmin/config.inc.php

>> Fix DB table references
sed -i.orig ‘s/pma_/pma__/g’ /etc/phpmyadmin/config.inc.php

>> Secure the main page (this should be under SSL)
htpasswd -c /etc/phpmyadmin/htpasswd.setup phpadminuser

ADD this into Directory for /usr/share/phpmyadmin

<IfModule mod_authn_file.c>
AuthType Basic
AuthName “phpMyAdmin Setup”
AuthUserFile /etc/phpmyadmin/htpasswd.setup
</IfModule>
Require valid-user
————————————————————–
-> Example:
<Directory /usr/share/phpmyadmin>
Options FollowSymLinks
DirectoryIndex index.php

<IfModule mod_php5.c>
AddType application/x-httpd-php .php

php_flag magic_quotes_gpc Off
php_flag track_vars On
php_flag register_globals Off
php_admin_flag allow_url_fopen Off
php_value include_path .
php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/javascript/
</IfModule>
<IfModule mod_authn_file.c>
AuthType Basic
AuthName “phpMyAdmin Setup”
AuthUserFile /etc/phpmyadmin/htpasswd.setup
</IfModule>
Require valid-user

</Directory>
————————————————————–

====================================================================

Multiple DBs (Ubuntu) => /etc/phpmyadmin/config-db.php

/* Servers configuration */
$i = 0;

/* Server: db01 [1] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db01’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;

/* Server: db02 [2] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db02’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;

====================================================================
PHP-FPM (Ubuntu):

vim /etc/apache2/conf-enabled/phpmyadmin.conf

ProxyPassMatch ^/phpmyadmin/(.*\.php(/.*)?)$ fcgi://127.0.0.1:9001/usr/share/phpmyadmin/$1
ProxyPassMatch ^/phpmyadmin/(.*\.php(/.*)?)$ fcgi://127.0.0.1:9001/usr/share/phpmyadmin$1index.php

Multiple DBs (Ubuntu) /etc/phpmyadmin/config-db.php
/* Servers configuration */
$i = 0;

/* Server: db01 [1] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db01’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;

/* Server: db02 [2] */
$i++;
$cfg[‘Servers’][$i][‘verbose’] = ‘db02’;
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘port’] = ”;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
$cfg[‘Servers’][$i][‘user’] = ”;
$cfg[‘Servers’][$i][‘password’] = ”;

=====================================================================
Error: The mcrypt extension is missing. Please check your PHP configuration.

php5enmod mcrypt

sudo updatedb
locate mcrypt.ini

>> Verify that new files exists here (they should be auto created from the issue above)

ls -al /etc/php5/cli/conf.d/20-mcrypt.ini
ls -al /etc/php5/apache2/conf.d/20-mcrypt.ini

>> Otherwise… create symbol links now

ln -s /etc/php5/mods-available/mcrypt.ini/etc/php5/cli/conf.d/20-mcrypt.ini
ln -s /etc/php5/mods-available/mcrypt.ini/etc/php5/apache2/conf.d/20-mcrypt.ini

>> Restart Apacahe

service apache2 restart

+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=

CENTOS

>>Install right RH repositories (if not present):
yum install epel-release
yum install httpd php php-mycrypt phpmyadmin

> Centos 5/6
chkconfig httpd on
service httpd start
-> open port 80 in /etc/sysconfig/iptables

> Centos 7
systemctl enable httpd.service
systemctl start httpd.service

firewall-cmd –add-service http –permanent
firewall-cmd –list-services
firewall-cmd –permanent –zone=public –add-service=http
firewall-cmd –reload

cd /usr/share/doc/phpMyAdmin-4.0.10.9/examples/
mysql < create_tables.sql

mysql -e “GRANT SELECT, INSERT, DELETE, UPDATE ON phpmyadmin.* TO ‘pmaadmin’@’%’ IDENTIFIED BY ‘<PASSWORD>'”

cp config.sample.inc.php /etc/phpMyAdmin/config.inc.php

>> Change these accordingly
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
/* User used to manipulate with storage */
$cfg[‘Servers’][$i][‘controlhost’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘controluser’] = ‘pmaadmin’;
$cfg[‘Servers’][$i][‘controlpass’] = ‘<PASSWORD>’;

/* Storage database and tables */
$cfg[‘Servers’][$i][‘pmadb’] = ‘phpmyadmin’;
$cfg[‘Servers’][$i][‘bookmarktable’] = ‘pma__bookmark’;
$cfg[‘Servers’][$i][‘relation’] = ‘pma__relation’;
$cfg[‘Servers’][$i][‘table_info’] = ‘pma__table_info’;
$cfg[‘Servers’][$i][‘table_coords’] = ‘pma__table_coords’;
$cfg[‘Servers’][$i][‘pdf_pages’] = ‘pma__pdf_pages’;
$cfg[‘Servers’][$i][‘column_info’] = ‘pma__column_info’;
$cfg[‘Servers’][$i][‘history’] = ‘pma__history’;
$cfg[‘Servers’][$i][‘table_uiprefs’] = ‘pma__table_uiprefs’;
$cfg[‘Servers’][$i][‘tracking’] = ‘pma__tracking’;
$cfg[‘Servers’][$i][‘designer_coords’] = ‘pma__designer_coords’;
$cfg[‘Servers’][$i][‘userconfig’] = ‘pma__userconfig’;
$cfg[‘Servers’][$i][‘recent’] = ‘pma__recent’;

>> Add these two lines at the bottom of /etc/phpMyAdmin/config.inc.php to disable the remaining 2 warnings

>> MySQL different library warning
$cfg[‘ServerLibraryDifference_DisableWarning’] = true;

>> A newer version of phpMyAdmin is available and you should consider upgrading
$cfg[‘VersionCheck’] = false;

=====================================================================
Multiple DBs (Centos) =>/etc/phpMyAdmin/config.inc.php
(example of 2 servers – comment out the below lines)

// Server db01
$i++;
/* Authentication type */
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
/* Server parameters */
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘compress’] = false;

// Server db02
$i++;
/* Authentication type */
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
/* Server parameters */
$cfg[‘Servers’][$i][‘host’] = ‘<DB_IP/FQDN>’;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘compress’] = false;

#$i++;
#$cfg[‘Servers’][$i][‘host’] = ‘localhost’; // MySQL hostname or IP address
$cfg[‘Servers’][$i][‘port’] = ”; // MySQL port – leave blank for default port
$cfg[‘Servers’][$i][‘socket’] = ”; // Path to the socket – leave blank for default socket

#$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’; // How to connect to MySQL server (‘tcp’ or ‘socket’)
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’; // The php MySQL extension to use (‘mysql’ or ‘mysqli’)
#$cfg[‘Servers’][$i][‘compress’] = FALSE; // Use compressed protocol for the MySQL connection
// (requires PHP >= 4.3.0)

 


If you’d like to install this from source, use this link.

MySQL – How much memory for InnoDB buffer?

Use this to find out how much storage each engine uses:

From the data above, it may seem like a good idea to keep innodb_buffer_pool low, because we have so much MyISAM data.
BUT if I get one query a minute against that MyISAM data, and 500 queries per second against the considerably smaller InnoDB data-set, that doesn’t make sense anymore.
Then we’d want all of the InnoDB data in memory, hence a buffer pool of 512M+

It’s down to data usage and where the hotspots are!


Percona Toolkit to ‘get the hotspots’

Get the hotspots:
you can set NUMPACKETS or keep the tcpdump running for 20 minutes. JUST keep an eye on the file size!

FYI: https://bugs.launchpad.net/percona-toolkit/+bug/1402776  official bug for that database being wrong thing

Import/Export single MySQL table

 

Sources:

http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html

https://github.com/JoyceBabu/MySQL-Dump-Table-Extractor

http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html#

https://github.com/abg/dbsake

MySQL Reset admin root password

Recommended method:

1) Create file /var/lib/mysql/mysql.init with contents:

2) Add this line to /etc/my.cnf in the [mysqld] section:

3) Restart the MySQL service

4) At your leisure, delete the mysql.init file and remove the init_file line
from /etc/my.cnf

With MARIADB

Source: https://stroobants.io/reset-a-root-password-in-mariadb-on-linux/

 

Restore database from Holland XtraBackup using dbsake

How it works?

XtraBackup basically does a copy of ‘/var/lib/mysql’ – no dumps. So, it’s good for a full restore but a bit tricky for a specific db restore.
So, the following steps will create a second MySQL instance using dbsake utility (the version can be specified, but the below example uses the version currently running on the server), extracting only the specific backup (to save space) and from there, you will be able to extract the dump with the well known ‘mysqldump‘.

 

Space utilised in MySQL tables

 


Source: http://stackoverflow.com/questions/6474591/how-can-you-determine-how-much-disk-space-a-particular-mysql-table-is-taking-up

For a table mydb.mytable run this for:
BYTES

 

KILOBYTES

 

MEGABYTES

 

GIGABYTES

 

GENERIC

Here is a generic query where the maximum unit display is TB (TeraBytes)

 

MySQL DB creation and user management

Useful commands:

 

MySQL Engine – InnoDB vs MyISAM

InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default

How to check the engine of tables for a database

 


Summary of Engines’ space utilised

 

Check MyISAM tables

NOTE:

When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%

 

Change all to InnoDB
https://support.rackspace.com/how-to/mysql-engines-myisam-vs-innodb/
When?
https://docs.dev4press.com/tutorial/wordpress/choosing-mysql-database-engine-myisam-or-innodb/
Quick answer: Always EXCEPT If you want to use FULLTEXT or SPATIAL indexes for some tables: in this case there is no choice and you must use MyISAM.

NOTE: FULLTEXT index support for InnoDB tables requires MySQL 5.6.4 or higherMariaDB 10.0.5 or higher
Highly recommend upgrading to MariaDB 10.0 as this is the 5.6 MySQL equivalent. It does not alter the data structures as 5.6 does and can be downgraded much more easily. It contains all the features of 5.6 plus more as well as being a drop-in replacement for MySQL in RHEL7.
I would point you to these KB articles:
https://mariadb.com/kb/en/mariadb-vs-mysql-features/https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/Repositories: https://downloads.mariadb.org/mariadb/repositories/#mirror=somerset

Check if there are FULLTEXT or SPATIAL indexes:

Convert ALL MyISAM tables to InnoDB (all databases):

2 steps + backup

(one liner – CAREFUL!)

 

Convert MyISAM tables of a SPECIFIC database to InnoDB:

Backup and convert-file

NOTE: When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%