Useful MySQL queries and configurations

MySQL_Sheet.sh

#MYSQL

#For login to mysql
mysql -h hostname -u dbusername -p dbname 

#For taking dump of mysql
mysqldump -h hostname -u dbusername -p dbname > dbbackup.sql

#For taking dump without tablespaces
mysqldump -h hostname -u dbusername -p dbname --no-tablespaces > dbback.sql

#For taking dump of all databases
mysqldump --all-databases  > all_databases.sql

#For taking dump of all databases in their respective application tmp folder
for i in $(ls -l | grep '^d' | awk '{print $9}'); do echo "Dumping $i";mysqldump $i > /home/master/applications/$i/tmp/$i.sql;done
"OR"
for i in $(ls -l | grep '^d' | awk '{print $9}'); do echo "Dropping $i"; echo "Y" | mysqladmin drop $i; echo "Creating $i"; mysqladmin create $i;echo "Importing $i" ;mysql $i < /home/master/applications/$i/tmp/$i.sql;done
"OR"
for i in $(ls -l | grep '^d' | awk '{print $9}'); do echo "Dumping $i";mysqldump $i --single-transaction > /home/master/applications/$i/tmp/$i.sql;done

#For importing database as root
mysql dbname < dbfile.sql

#For repairing all databases
mysqlcheck -u root --auto-repair --all-databases

#For Deleting Database
DROP DATABASE database_name;

#For Creating Datbase
CREATE DATABASE database_name;

#FOr Deleting Table
DROP TABLE table_name;

#To check all the users in MySQL 
SELECT user FROM mysql.user;

#For Creating User
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

#To Change User password
ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
FLUSH PRIVILEGES;

#For granting privileges to database
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';

#In case of error "table doesnt exist in engine" while taking mysqldump
ALTER TABLE __yourtable_name__ DISCARD TABLESPACE ;
ALTER TABLE __yourtable_name__ IMPORT TABLESPACE ;

#For deleting entries in table older than 7 days
DELETE FROM table_name WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

#MySQL Tuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

#Changes that can be made in my.cnf or custom.cnf

"Values are obtain after running sqltuner on a small server of 1 GB"

max_connections=150

innodb_buffer_pool_instances=2
innodb_buffer_pool_size=256M
innodb_log_file_size=128M
key_buffer_size=128M
join_buffer_size=64M

max_allowed_packet=1024M

net_read_timeout=120 
net_write_timeout=240
interactive_timeout=180
connect_timeout=25
innodb_lock_wait_timeout=180
wait_timeout=60

query_cache_type=0
query_cache_size=0M
query_cache_limit=100M

table_open_cache=250
tmp_table_size=128M
max_heap_table_size=128M


#For Enabling MySQL query logging
SET global general_log = 1;
SET global log_output = 'file';
SET global general_log_file = /tmp/mysql/query.log
'OR'
general_log      = 1
log_output = 'file'
general_log_file = /tmp/mysql/query.log

#For setting default storage engine MyISAM/InnoDB
default-storage-engine= MyISAM

#For changing file format to Barracuda
innodb_file_format = Barracuda
#If Index column size is too large
innodb_large_prefix = on
innodb_file_per_table = on
#For setting default row as Dynamic
innodb_default_row_format = DYNAMIC
#For changing innodb log filesize
innodb_log_file_size = 512M

'OR'

show variables like "%innodb_file%";
SET GLOBAL innodb_file_format = barracuda;
SET GLOBAL innodb_large_prefix = `ON`;
SET GLOBAL innodb_file_per_table = 1 ;
SET GLOBAL innodb_default_row_format = DYNAMIC;
SET GLOBAL innodb_log_file_size = 512M;

#For changing read and write timeout (Default value is 30 for read while 60 for write)
net_read_timeout = 120 
net_write_timeout = 900

'OR'

SET GLOBAL net_read_timeout = 120; 
SET GLOBAL net_write_timeout = 900;

#Max allowed packet value cannot be greater than 1024M
max_allowed_packet=512M

#For storing all tables in lower case
lower_case_table_names=1

#In case mysql consuming huge amount of RAM due to performance schema, disable it
[mysqld]
performance_schema = 0

#In case of error of table spaces while taking databasedump
[mysqldump]
no-tablespaces

#For changing encoding type

#For Changing MySQL Encoding to utf8
character_set_server=utf8
collation_server=utf8_general_ci
'OR'
ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

#For changing MySQL Encoding to utf8mb4
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
'OR'
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#For changing MySQL Encoding to support Chinese, Japanaese, Swedish Characters
character-set-server = latin1
collation_server = latin1_swedish_ci
'OR'
ALTER DATABASE database_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

#For performing search and replace of character encoding in Database
sed -i 's/encoding_to_be_search/encoding_to_be_replace/g' dbname.sql
(For Example)
sed -i 's/utf8mb4/utf8/g' dbname.sql
sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' dbname.sql


#If error occurs while importing Database
ERROR 1142 (42000) at line <line number>: ANY command denied to user 'username'@'%' for table '<table name>'

'Then it means that any occurence for another database name is present in the database file which is required to be replace by the database name of application'


#MySQL Service Debugging

#In case if mysql is down

1. Check for disk usage and inodes.
2. Check for mysql status through "service mysql status"

#In case of error "Can't init tc log"
rm /var/lib/mysql/tc.log
service mysql restart

(tc.log is the distributed transaction (XA) log coordinato)


#In case of error "Could not open mysql.plugin table. Some plugins may be not loaded"
#check for the processes of mysql
ps -ef | grep mysqld
lsof '/var/lib/mysql/aria_log_control'

"kill the processes which are occupying aria_log_control and then restart mysql service"

#In case issue persists,
rm /var/lib/mysql/aria_log_control
service mysql restart

If issue still persists, restart on recovery mode

[mysqld]
innodb_force_recover = 1

'Max value is 6, attempt will be made from lower to higher value'

When mysql is run on recovery mode, then take database dump and proceed with removing ib_logfile

rm -r /var/lib/mysql/ib_logfile0
rm -r /var/lib/mysql/ib_logfile1

'Remove line for force recovery and restart mysql.
After restart, import database dump to recover data'