Category Archives: Linux

MySQL – How much memory for InnoDB buffer?

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

mysql -e "select engine, SUM(DATA_LENGTH)/1024 as data_size, SUM(INDEX_LENGTH)/1024 as index_size, ((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024) as total_size from information_schema.tables group by engine;"
+--------------------+-----------+------------+------------+
| engine | data_size | index_size | total_size |
+--------------------+-----------+------------+------------+
| CSV | 0.0000 | 0.0000 | 0.0000 |
| InnoDB | 480.0000 | 0.0000 | 480.0000 |
| MEMORY | 0.0000 | 0.0000 | 0.0000 |
| MyISAM | 0.0000 | 1.0000 | 1220.5859 |
| PERFORMANCE_SCHEMA | 0.0000 | 0.0000 | 0.0000 |
+--------------------+-----------+------------+------------+

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’

wget http://www.percona.com/downloads/percona-toolkit/2.2.12/tarball/percona-toolkit-2.2.12.tar.gz
tar -zxvf percona-toolkit-2.2.12.tar.gz -C /opt/ 
cd /opt/percona-toolkit-2.2.12/ 
perl Makefile.PL 
make

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

NUMPACKETS=1000
tcpdump -s 65535 -x -nn -q -tttt -i any -c $NUMPACKETS port 3306 > /tmp/mysql.tcp.txt 

bin/pt-query-digest --type tcpdump /tmp/mysql.tcp.txt

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

Import/Export single MySQL table 

>> Extract single table from a running database
mysqldump dbname tableName > tableName.sql

>> Extract table from a MySQL dump:
> Option 1) Using 'sed'
sed -n "/^-- Table structure for table \`TABLE_NAME_HERE\`/,/^-- Table structure for table/p" DATABASEDUMP.SQL > TABLENAME-TABLE.sql

> Option 2) Using dbsake: https://github.com/abg/dbsake
cat file.sql | dbsake split-mysqldump -C /tmp/sql

>> Import the table:
# mysql -D dbname < tableName.sql

 

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:

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('mynewpassword');

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

init_file=/var/lib/mysql/mysql.init

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/

service mysql stop
mysqld_safe --skip-grant-tables --skip-networking &

# mysql
use mysql;
update user set password=PASSWORD("<PASSWORD>") where User='root'
flush privileges;
exit

service mysql restart

 

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‘.

>> Download dbsake. Bear in mind it requires python2.6 which can be installed from IUS.
mkdir /tmp/dbsake
cd $_
curl -s http://get.dbsake.net > dbsake
chmod +x dbsake
./dbsake --version

>> Install pigz from EPEL repo
yum install pigz

>> Use dbsake to unpack Percona XtraBackup stream extracting only the DB required
screen -S xtrabackup_restore

./dbsake sandbox --sandbox-directory=/tmp/sandbox/ \
--data-source=/var/spool/holland/xtrabackup/newest/backup.tar.gz -t <database_name>.*

>> Start a dbsake instance and dump the required database.
/tmp/sandbox/sandbox.sh start
/tmp/sandbox.sh mysqldump <database_name> | gzip -1 ><database_name>.sql.gz

>> Clean up
/tmp/sandbox/sandbox.sh stop
rm -rf /tmp/sandbox

 

Space utilised in MySQL tables 

mysql> use information_schema;
mysql> select table_schema, sum(DATA_LENGTH)/(1024*1024), sum(INDEX_LENGTH)/(1024*1024) from tables group by table_schema;

 

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

SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

 

KILOBYTES

SELECT (data_length+index_length)/power(1024,1) tablesize_kb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

 

MEGABYTES

SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

 

GIGABYTES

SELECT (data_length+index_length)/power(1024,3) tablesize_gb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

 

GENERIC

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

SELECT
CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
FROM
(
SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
FLOOR(LOG(IF(data_length+index_length=0,1,data_length+index_length))/LOG(1024)) pz
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable'
) AA
) A,(SELECT 'B KBMBGBTB' units) B;

 

MySQL DB creation and user management

Useful commands:

>>Create DB and user
create database $DB;
grant all privileges on $DB.* to $DBUSER@localhost identified by '$PASS';

>> Check GRANT permissions:
SHOW GRANTS FOR 'root'@'localhost';

>> How to grant privileges
GRANT ALL PRIVILEGES ON *.* TO 'admin1'@'localhost' IDENTIFIED BY '$password';
GRANT ALL PRIVILEGES ON 'db01.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON 'db02.* TO 'db02admin'@'localhost' WITH GRANT OPTION;

>> Grant read-only
GRANT SELECT ON $database_name.* TO $user@$host IDENTIFIED BY '$password';

>> Set password:
set password for 'root'@'%' = password('yournewpassword');
set password for 'root'@'localhost' = password('yournewpassword');

>> Show all users
mysql> use mysql;
mysql> SELECT User,host FROM mysql.user;

>> Remove user
mysql> use mysql;
mysql> DROP USER 'bob'@'localhost';

>> Update user
SELECT User,host FROM mysql.user where user='chris';
update host set host='%' FROM mysql.user where user='chris';

>> Check grants/permission for a user
show grants for 'chris'@'%';

 

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

mysql> select engine from tables.information_schema where table_schema='<DB_NAME>';

 

Summary of Engines’ space utilised

select engine,sum(index_length+data_length)/1024/1024,count(engine) from information_schema.tables group by engine;

 

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%

 

SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql") and engine = 'MyISAM';

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:

mysql> use information_schema;
mysql> SELECT DISTINCT CONCAT(STATISTICS.TABLE_SCHEMA, '.', STATISTICS.TABLE_NAME) AS fulltext_table, (TABLES.DATA_LENGTH + TABLES.INDEX_LENGTH)/1024/1024 AS size_M FROM STATISTICS INNER JOIN TABLES ON TABLES.TABLE_SCHEMA = STATISTICS.TABLE_SCHEMA AND TABLES.TABLE_NAME = STATISTICS.TABLE_NAME WHERE STATISTICS.INDEX_TYPE IN ("SPATIAL", "FULLTEXT") GROUP BY STATISTICS.TABLE_SCHEMA, STATISTICS.TABLE_NAME;

Convert ALL MyISAM tables to InnoDB (all databases):

2 steps + backup

# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='MyISAM';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > rollback_all2Innodb.sql
# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > all2Innodb.sql
# mysql < all2Innodb.sql

(one liner – CAREFUL!)

# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' | mysql

 

Convert MyISAM tables of a SPECIFIC database to InnoDB:

Backup and convert-file

# DB="<dbname>"
# mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'MyISAM\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > rollback_$DB_all2Innodb.sql

# mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'InnoDB\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > $DB_all2Innodb.sql

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%

MySQL temporary table to RAM

Best practise

Check ID/GID for MySQL

# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

Create and mount

# mkdir -p /var/lib/mysqltempinram
# chown 27:27 /var/lib/mysqltempinram
# echo "tmpfs /var/lib/mysqltempinram tmpfs rw,gid=27,uid=27,size=512M,nr_inodes=10k,mode=0700 0 0" >> <a href="/etc/fstab">/etc/fstab</a>
# mount /var/lib/mysqltempinram
# df -Th /var/lib/mysqltempinram

Add this line under [mysqld] into my.cnf configuration file:

tmpdir = /var/lib/mysqltempinram

restart mysql

Check if all is applied:

# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';

Test

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

 

Quick method – careful! This might make the server run out of memory!!!

Check what’s the RAM filesystem on the server

# mount | grep tmpfs
tmpfs on /dev/shm type tmpfs (rw,noexec,nosuid,nodev)

=> /dev/shm

Add this to my.cnf file:

[mysqld]
tmpdir = /dev/shm/

restart mysql

Check if all is applied:

# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| tmpdir | /dev/shm/ |
+---------------+-------------+

 

Test:

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

 

MySQL notes

MySQL backup – mysqldump
shell> mysqldump [options] db_name [tbl_name …] > db_name.sql
shell> mysqldump [options] –databases db_name … > multi_db.sql
shell> mysqldump [options] –all-databases > all_dbs.sql

Importing MySQL Table
To import the table run the following command from the command line:
shell> mysql -D dbname < tableName.sql

Check database space
SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ;

MySQL Uptime
a) mysql> SHOW GLOBAL STATUS;
b) # mysqladmin version | grep -i uptime

innodb_open_files
mysql> show global variables like “innodb_open_files”\G

Binary Logs
>> Enable:
> /etc/my.cnf
log-bin = /var/lib/mysql/bin-log

Enable the slow query log
slow-query-log = 1

Log queries that take longer than 2 seconds
long-query-time = 2

Set ‘max_connections’:
>> On the fly (GLOBAL variable. we can increase it on the fly without restarting mysqld service)
[Check] select @@global.max_connections;
[Set] set @@global.max_connections=300;
[Re-Check] select @@global.max_connections;
(or mysql> set global max_connections=250;)

>> CHANGE on /etc/my.cnf
max_connections = 50
max-connections = 50

set @@global.max_connections=default;

Set the query_cache_size to 16MB, query_cache_type to 1 and query_cache_limit to 1MB

mysql> set global query_cache_size=16*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set global query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global query_cache_limit=1*1024*1024;
Query OK, 0 rows affected (0.00 sec)

Check variables
select @@global.max_connections;OR
show variables;show variables like ‘%max%’;

Disable InnoDB
[mysqld]
skip-innodb
default-storage-engine = myisam

Check if Query Cache is enabled:
SHOW VARIABLES LIKE ‘have_query_cache’;

Check Query Cache statistics:
show status like ‘Qcache%’;

MySQL’s maximum memory usage is dangerously high
>> (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size) x max_connections
=> change max_connections

wait_timeout (global variable)
mysql> show processlist;
If there are too many queries, it might be a bug in the code (for example no “close connections”). In this case, it would be safer to setup a wait_timeout low, maybe 180 (seconds -> 3mins) to make sure the sleeping connections will get dropped at that time.

 

 

Apache not www to www redirects

not www to www

RewriteCond %{HTTP_HOST} !^www\.
RewriteRule (.*) http://www.%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

Excluding ‘mysubdomain’

RewriteCond %{HTTP_HOST} !^(www|mysubdomain)\.
RewriteRule (.*) https://www.%{HTTP_HOST}%{REQUEST_URI} [L,R=301]