List sizes of MySQL databases

Do you wonder which databases are actually taking up how much space but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don’t represent the actual database sizes?

Run from a mysql root console:

mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-------------+
| Database name | Size (MB) |
+--------------------+-------------+
| information_schema | 0.00878906 |
| mysql | 0.62860394 |
| performance_schema | 0.00000000 |
| roundcube | 0.46875000 |
+--------------------+-------------+
8 rows in set (1.21 sec)

	

How do I disable MySQL strict mode on the server?

Please follow the under mentioned instructions to turn off the MySQL strict mode. Make the following changes in the “my.ini/my.cnf”:

Look for the following line:

sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Change it to:


sql_mode=""



Restart the MySQL service.

or

Look for the following line , this line will set MySQL strict mode


sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

To disable MySQL strict mode, you can change the above line as follow:


sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER,,NO_ENGINE_SUBSTITUTION"

Restart the MySQL service.

You may be able to run an SQL query within your database management tool such as phpMyAdmin which can normally be found from your web hosting control panel:


SET @@global.sql_mode= '';

Test

Run the following:

#mysql -uroot -p -e 'select @@GLOBAL.sql_mode;'
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+

How large should be mysql innodb_buffer_pool_size?

First – run mysqltuner:

]# ./mysqltuner.pl
 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 445K (Tables: 9)
[--] Data in InnoDB tables: 204M (Tables: 13)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'sysadmin@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17d 19h 48m 54s (1M q [0.865 qps], 87K conn, TX: 39B, RX: 258M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 432.0M global + 6.5M per thread (151 max threads)
[OK] Maximum reached memory usage: 913.0M (49.61% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (76.81% of installed RAM)
[OK] Slow queries: 0% (1/1M)
[OK] Highest usage of available connections: 49% (74/151)
[OK] Aborted connections: 2.80%  (2442/87276)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 271K sorts)
[!!] Temporary tables created on disk: 71% (80K on disk / 114K total)
[OK] Thread cache hit rate: 94% (4K created / 87K connections)
[!!] Table cache hit rate: 2% (115 open / 5K opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] Binlog cache memory access: 99.95% ( 52384 Memory / 52412 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (49M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/291.0K
[OK] Read Key buffer hit rate: 100.0% (9M cached / 510 reads)
[!!] Write Key buffer hit rate: 1.4% (1K cached / 1K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/204.8M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.97% (88883891 hits/ 88912014 total)
[OK] InnoDB Write log efficiency: 96.95% (869671 hits/ 897014 total)
[OK] InnoDB log waits: 0.00% (0 waits / 27343 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 256)
Variables to adjust:
    query_cache_type (=1)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 256)
    innodb_buffer_pool_size (>= 204M) if possible.
    innodb_buffer_pool_instances (=1)

Here is what you should do. First run this query



mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    -> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    -> FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     1 |
+-------+
1 row in set (0.08 sec)

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size (in GB)based on all InnoDB Data and Indexes with an additional 60%.

After the restart, run mysql for a week or two. Then, run this query:


SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

This will give you how many actual pages of InnoDB data reside in the InnoDB Buffer Pool.

Change and Update WordPress URLS in Database When Site is Moved to new Host

UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldurl’, ‘http://www.newurl’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

UPDATE wp_posts SET guid = replace(guid, ‘http://www.oldurl’,’http://www.newurl’);

UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.oldurl’, ‘http://www.newurl’);

UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://www.oldurl’,’http://www.newurl’);

mysql> UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldurl’, ‘http://www.newurl’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, ‘http://www.oldurl’,’http://www.newurl’);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.oldurl’, ‘http://www.newurl’);
Query OK, 0 rows affected (0.05 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://www.oldurl’,’http://www.newurl’);g
Query OK, 0 rows affected (0.01 sec)
Rows matched: 686 Changed: 0 Warnings: 0

Explain mysql statement

# mysql -u root -p 
MariaDB [(none)]> wp_database -e "EXPLAIN SELECT * FROM posts"
 EXPLAIN SELECT * FROM 2qGr3sjm_posts;
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      |          posts | ALL  | NULL          | NULL | NULL    | NULL | 2519 |       |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+

Or from command line

#  mysql -u root -p wp_database -e "EXPLAIN SELECT * FROM posts"

Left Join on mysql

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:


SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

Example: You have a surveys table and a services table and the id’s are both client_id


select surveys.date, surveys.client_id, surveys.score 
from surveys 
left join services on surveys.client_id=services.client_id 
where cpu like Xeon
group by surveys.client_id;

IS my mysql database table Innodb or Mysiam

MariaDB [(none)]> show table status from admin_geek01;
+-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name                        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| 2qGr3sjm_commentmeta        | MyISAM |      10 | Dynamic    |    0 |              0 |        2704 |  281474976710655 |         9216 |      2704 |           1073 | 2015-12-10 13:59:26 | 2015-12-30 22:51:44 | 2016-01-29 23:01:12 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_comments           | MyISAM |      10 | Dynamic    |    0 |              0 |        8888 |  281474976710655 |         7168 |      8888 |            530 | 2015-12-10 13:59:26 | 2015-12-30 22:51:44 | 2016-01-29 12:35:32 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_links              | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         1024 |         0 |              1 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_options            | InnoDB |      10 | Compact    |  167 |           6671 |     1114112 |                0 |        16384 |   6291456 |          18191 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_postmeta           | MyISAM |      10 | Dynamic    | 1758 |            106 |      187856 |  281474976710655 |       116736 |         0 |           7564 | 2015-12-10 13:59:26 | 2016-02-19 11:42:42 | 2016-02-07 11:40:30 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_posts              | InnoDB |      10 | Compact    | 3547 |           1926 |     6832128 |                0 |       442368 |   6291456 |           2966 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_stb_styles         | MyISAM |      10 | Dynamic    |    7 |            845 |        5920 |  281474976710655 |         8192 |         0 |           NULL | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_term_relationships | MyISAM |      10 | Fixed      |  899 |             21 |       18900 | 5910974510923775 |        46080 |        21 |           NULL | 2015-12-10 13:59:26 | 2016-02-19 11:36:09 | 2016-02-07 11:40:30 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_term_taxonomy      | MyISAM |      10 | Dynamic    |  105 |             39 |        4148 |  281474976710655 |         9216 |         0 |            108 | 2015-12-10 13:59:26 | 2016-02-19 11:36:09 | 2016-02-07 11:40:29 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_termmeta           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |                0 |        32768 |   6291456 |              1 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_terms              | MyISAM |      10 | Dynamic    |  105 |             30 |        3176 |  281474976710655 |        13312 |         0 |            108 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_usermeta           | InnoDB |      10 | Compact    |   37 |            442 |       16384 |                0 |        32768 |   6291456 |             41 | 2015-12-10 13:59:26 | NULL                | NULL                | utf8_general_ci |     NULL |                |         |
| 2qGr3sjm_users              | MyISAM |      10 | Dynamic    |    1 |            120 |         120 |  281474976710655 |         4096 |         0 |              2 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | 2015-12-10 13:59:26 | utf8_general_ci |     NULL |                |         |
+-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

Why Backup your MySql Databases with Cron

There are a few reasons to install a cron job to create full database dumps versus copying the /var/lib/mysql data. The MySQL service may have a lock on the database file(s) when it attempts to replicate the data file, causing it to fail; if the backup on the data files does succeed, the InnoDB log files may not match with the ibdata increasing the chance of full corruption or lost data; MyISAM data and table structures can face similar issues.

Plesk

Create a location for the dumps. This will be the folder the dumps are exported to by the script.

# mkdir -pv /usr/local/db/dumps

Create a script that cron can execute. Create /usr/local/bin/dbdump with these contents:

nano /usr/local/bin/dbdump

Put this in the file (for Plesk)

#!/bin/bash
for db in $(mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show databases' -s --skip-column-names); do echo -n "dumping $db..."; mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $db > "/usr/local/db/dumps/$db.sql"; echo "[done]"; done

Make it executable:

# chmod +x /usr/local/bin/dbdump

For non plesk:

# nano /usr/local/bin/dbdump

Put this in the file

for db in $(mysql -uroot -pPASSWORD -e 'show databases' -s --skip-column-names); do echo -n "dumping $db..."; mysqldump -uroot -pPASSWORD $db > "/usr/local/db/dumps/$db.sql"; echo "[done]"; done

Make it executable:

# chmod +x /usr/local/bin/dbdump

Create /etc/cron.d/dbdump with these contents:

# nano /etc/cron.d/dbdump
# Cronjob to dump databases nightly
05     0      *       *       *       root       /usr/local/bin/dbdump >/dev/null 2>&1