Tag Archives: mysql

MySQL Replication

This is a copy and paste of some old notes about MySQL replication. I have never fully reviewed this content, or neither finished with the script. I save this anyway, in case I will need some of this info in the future 😉

MySQL Replication NOTES

Master Setup/etc/my.cnf changes

# The following items need to be set:
log-bin=/var/lib/mysqllogs/ServerID-theServerShortName-binary-log
binlog-format=MIXED
expire_logs_days=7
server-id=<server_number>

# replication user
PASS=$(tr -cd '[:alnum:]' < /dev/urandom | fold -w12 | head -n1)
echo "This is the password (take note): $PASS"
mysql -e "GRANT REPLICATION SLAVE ON *.* to repl_user IDENTIFIED BY '$PASS'"

# Dump and copy across
mysqldump -A --flush-privileges --master-data=1 | gzip -1 > ~myuser/master_data.sql.gz
scp ~myuser/master_data.sql.gz $SLAVEIP:/home/myuser

# Restart Master
service mysqld restart

# === take notes of the following ====
# Get replication POSITION
zgrep -m 1 -P 'CHANGE MASTER' ~myuser/master.sql.gz | sed 's/^.*\(MASTER_LOG_FILE=.*\)$/\1/'

# Get new MySQL password to set on the slave
grep password /root/.my.cnf | awk -F= '{print $2}'

Slave Setup

# Verify timezones match between master and slave!/etc/my.cnf changes

# The following items need to be set:
relay-log=/var/lib/mysqllogs/ServerID-theServerShortName-relay-log
relay-log-space-limit = 16G
read-only=1
server-id=<server_number>
report-host=<server_number> #This allows show slave hosts; to work on the master.

# Import the data
echo "zcat /home/myuser/master.sql.gz | mysql"

# Update /root/.my.cnf with password set in the Master (importing ALL the db will overwrite users and passwords too)

# Restart Slave
service mysqld restart

# Enable repication (replace accordingly with position from latest Master's steps)
mysql
mysql> CHANGE MASTER TO MASTER_HOST = '$MASTERIP', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = '$PASS', MASTER_LOG_FILE='752118-Db01A-binary-log.000001', MASTER_LOG_POS=107;
mysql> START SLAVE;
mysql> CHECK SLAVE STATUS\G

==========================================================================
Trying to automate: ****DRAFT*****

#>>> On MASTER <<<#

MASTERIP=""
SLAVEIP=""

# On DEDICATED:
MYHOST=$(hostname -a)
SERVERID=$(echo $MYHOST| awk -F- '{print $1}')

# On CLOUD:
MYHOST=$(hostname)
SERVERID=$(echo $MYHOST| awk -F- '{print $1}')

#>> Create a dump and copy across
mysqldump -A --flush-privileges --master-data=1 | gzip -1 > ~myuser/master.sql.gz
scp ~myuser/master.sql.gz $SLAVEIP:/home/myuser/

#>> Set my.cnf

#> Unset possible pre-sets
for LINE in log-bin binlog-format expire_logs_days server-id ; do sed -i "/^.*$LINE.*=.*$/ s/^/#/" -i /etc/my.cnf ; done

#> Make sure all are commented out
for LINE in log-bin binlog-format expire_logs_days server-id ; do grep $LINE /etc/my.cnf ; done

#> Apply new parameters
PASS=$(tr -cd '[:alnum:]' < /dev/urandom | fold -w12 | head -n1)
sed -i "/\[mysqld\]/a \#REPLICATION\nlog-bin=\/var\/lib\/mysqllogs\/$SERVERID-binary-log\nbinlog-format=MIXED\nexpire_logs_days=7\nserver-id=$SERVERID" /etc/my.cnf

service mysqld restart

#>> Set replication user
mysql -e "GRANT REPLICATION SLAVE ON *.* to repl_user IDENTIFIED BY '$PASS'"

#>> Get output to run on the SLAVE

echo "zcat /home/myuser/master.sql.gz | mysql"

POSITION=zgrep -m 1 -P 'CHANGE MASTER' ~myuser/master.sql.gz | sed 's/^.*\(MASTER_LOG_FILE.*\)$/\1/'
echo "mysql -e \"CHANGE MASTER TO MASTER_HOST = '$MASTERIP', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = '$PASS', $POSITION;"

POSITION=zgrep -m 1 -P 'CHANGE MASTER' ~myuser/master.sql.gz | sed 's/^.*\(MASTER_LOG_FILE=.*\)$/\1/'

MASTER_LOG_FILE='752118-Db01A-binary-log.000001', MASTER_LOG_POS=107;

#>>> On SLAVE <<<#
for LINE in relay-log relay-log-space-limit read-only server-id report-host ; do grep $LINE /etc/my.cnf ; done

relay-log=/var/lib/mysqllogs/ServerID-theServerShortName-relay-log
relay-log-space-limit = 16G
read-only=1
server-id=<server_number>

report-host=<server_number> #This allows show slave hosts; to work on the master.

MySQL – Increase I/O performance on InnoDB

1) Increased innodb_write_io_thread from 4 to 16
2) Increased innodb_read_io_thread from 4 to 16
3) Altered innodb_flush_method=O_DIRECT

Sources:
http://dev.mysql.com/doc/innodb/1.1/en/innodb-performance-multiple_io_threads.html http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html

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.

SHOW GLOBAL STATUS 
LIKE 'innodb_log_waits';

+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_log_waits | 0 |
+------------------+-------+

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;

=====================================================================
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:

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'@'%';

My notepad

Chris' IT notes…