mysql-optimization.sql

#Know your need for Recommended InnoDB Buffer Pool Size
# innodb_buffer_pool_size
# https://tools.percona.com/dashboard
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1.25 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

#set /etc/my.conf
[mysqld]
innodb_buffer_pool_size=2G #sample value

#after restarting the server

#how many data storage you are actuali using
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;



##create user and set database
#
set @username = 'mysqlusername';
set @password = 'mysqluserpassword';
set @database = 'database_name';

SET @query1 = CONCAT('
            CREATE USER "',@username,'"@"localhost" IDENTIFIED BY "',@password,'" '
        );
PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @query1 = CONCAT('
            CREATE DATABASE  IF NOT EXISTS ',@database,' '
    );

PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @query1 = CONCAT('
        GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES ON ',@database,'.* TO "',@username,'"@"localhost" IDENTIFIED BY "',@password,'" '
    );
PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

FLUSH PRIVILEGES;