a working my.cnf for Mariadb

#
# The MySQL database server configuration file.
#

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
#skip-innodb
default-storage-engine=InnoDB
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1

#
# * Fine Tuning
#
key_buffer = 128M
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size       = 64
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 1000

interactive_timeout= 1
wait_timeout= 1

#log-slow-queries = /var/log/mysql/slow_query.log
#long_query_time = 10
#log_queries_not_using_indexes = 1

table_cache            = 6048
thread_concurrency     = 22

join_buffer_size = 8M
sort_buffer_size = 32M
max_heap_table_size = 4412M
max_connect_errors = 10
tmp_table_size = 4412M




#*** MyISAM Specific options
key_buffer_size = 32M
read_buffer_size = 4M
read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G



#finally added
table_definition_cache = 2048


#default-storage-engine = innodb
innodb_buffer_pool_size = 25000M
innodb_log_file_size = 1500M
innodb_flush_method = O_DIRECT #O_DSYNC
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

# num cpu's/cores *2 is a good base line for innodb_thread_concurrency
innodb_thread_concurrency = 16




#
# * Query Cache Configuration
#
query_cache_limit = 2M
query_cache_size  = 256M
query_cache_min_res_unit = 1k

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /var/log/mysql/error.log

#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#binlog_do_db = shahbin

#expire_logs_days = 10
#max_binlog_size         = 100M


[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

Simple bash command to convert all tables in a mysql database to utf8

  for t in $(mysql --user=root --password=admin  --database=DBNAME -e "show tables";);
    do 
       echo "Altering" $t;
       mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
    done

Source

Delete dupicate entries in mysql table

CREATE TABLE wpdb.wp_posts_deduped like wpdb.wp_posts;
INSERT wpdb.wp_posts_deduped SELECT * FROM wpdb.wp_posts GROUP BY post_content, post_title;
RENAME TABLE wpdb.wp_posts TO wpdb.wp_posts_with_dupes;
RENAME TABLE wpdb.wp_posts_deduped TO wpdb.wp_posts;
#OPTIONAL
ALTER TABLE wpdb.wp_posts ADD UNIQUE `unique_index` (`post_content`, `post_title`);
#OPTIONAL
DROP TABLE wpdb.wp_posts_with_dupes;

Find mysql database and tabe size

These queries are very useful to find bloated databases/tables:

Show size of all databases (in MB)

SELECT s.schema_name, 
   SUM(t.data_length + t.index_length)/1024/1024 total_size, 
  (SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used,    SUM(data_free)/1024/1024 data_free
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size DESC;


Show size of all tables (in MB)

SELECT s.schema_name, table_name,   
  SUM(t.data_length + t.index_length)/1024/1024 total_size,   
(SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used,  
  SUM(data_free)/1024/1024 data_free 
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t 
    ON s.schema_name = t.table_schema 
GROUP BY s.schema_name, table_name 
ORDER BY total_size DESC;


Source

Slow query log parser

A very nice MySQL log analyzer tool from Percona

#!/usr/bin/perl 
#
# Nathanial Hendler
# http://retards.org/
#
# 2001-06-26 v1.0
#
# This perl script parses a MySQL slow_queries log file
# ignoring all queries less than $min_time and prints
# out how many times a query was greater than $min_time
# with the seconds it took each time to run.  The queries
# are sorted by number of times it took; the most often
# query appearing at the bottom of the output.
#
# Usage: mysql_slow_log_parser logfile
#
# ------------------------
# SOMETHING TO THINK ABOUT (aka: how to read output)
# ------------------------
#
# Also, it does to regex substitutions to normalize
# the queries...
#
#   $query_string =~ s/\d+/XXX/g;
#   $query_string =~ s/([\'\"]).+?([\'\"])/$1XXX$2/g;
#
# These replace numbers with XXX and strings found in
# quotes with XXX so that the same select statement
# with different WHERE clauses will be considered
# as the same query.
#
# so these...
#
#   SELECT * FROM offices WHERE office_id = 3;
#   SELECT * FROM offices WHERE office_id = 19;
#
# become...
#
#   SELECT * FROM offices WHERE office_id = XXX;
#
#
# And these...
#
#   SELECT * FROM photos WHERE camera_model LIKE 'Nikon%';
#   SELECT * FROM photos WHERE camera_model LIKE '%Olympus';
#
# become...
#
#   SELECT * FROM photos WHERE camera_model LIKE 'XXX';
#
#
# ---------------------
# THIS MAY BE IMPORTANT (aka: Probably Not)
# --------------------- 
#
# *SO* if you use numbers in your table names, or column
# names, you might get some oddities, but I doubt it.
# I mean, how different should the following queries be
# considered?
#
#   SELECT car1 FROM autos_10;
#   SELECT car54 FROM autos_11;
#
# I don't think so.
#


$min_time       = 0; # Skip queries less than $min_time
$min_rows = 0;
$max_display    = 10; # Truncate display if more than $max_display occurances of a query

print "\n Starting... \n";

$query_string   = '';
$time           = 0;
$new_sql        = 0;


##############################################
# Loop Through The Logfile
##############################################

while (<>) {

 # Skip Bogus Lines

 next if ( m|/.*mysqld, Version:.+ started with:| );
 next if ( m|Tcp port: \d+  Unix socket: .*mysql.sock| );
 next if ( m|Time\s+Id\s+Command\s+Argument| );
 next if ( m|administrator\s+command:| );


 # print $_;
 # if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s/ ) {
 #if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s+Rows_examined:\s+(\d+)/ ) {
 if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s+Rows_examined:\s+(.*)/ ) {

  $time    = $1;
  $rows  = $3;
  $new_sql = 1;
  # print "found $1 $3\n";
  next;

 }


 if ( /^\#/ && $query_string ) {

   if (($time > $min_time) && ($rows >= $min_rows)) {
    $orig_query = $query_string;

    $query_string =~ s/\d+/XXX/g;
    $query_string =~ s/'([^'\\]*(\\.[^'\\]*)*)'/'XXX'/g;
    $query_string =~ s/"([^"\\]*(\\.[^"\\]*)*)"/"XXX"/g;
    #$query_string =~ s/([\'\"]).+?([\'\"])/$1XXX$2/g;
    #$query_string =~ s/\s+/ /g;
    #$query_string =~ s/\n+/\n/g;

    push @{$queries{$query_string}}, $time;
    push @{$queries_rows{$query_string}}, $rows;
    $queries_tot{$query_string} += $time;
    $queries_orig{$query_string} = $orig_query;
    $query_string = '';

   }

 } else {
  
  if ($new_sql) {
   $query_string = $_;
   $new_sql = 0;
  } else {
   $query_string .= $_;
  }
 }

}


##############################################
# Display Output
##############################################

foreach my $query ( sort { $queries_tot{$b} <=> $queries_tot{$a} } keys %queries_tot )  {
 my $total = 0; 
 my $cnt = 0;
 my @seconds = sort { $a <=> $b } @{$queries{$query}};
 my @rows    = sort { $a <=> $b } @{$queries_rows{$query}};
 ($total+=$_) for @seconds;
 ($cnt++) for @seconds;

 print "### " . @{$queries{$query}} . " Quer" . ((@{$queries{$query}} > 1)?"ies ":"y ") . "\n";
 print "### Total time: " . $total .", Average time: ".($total/$cnt)."\n";
 print "### Taking ";
 print @seconds > $max_display ? "$seconds[0] to $seconds[-1]" : sec_joiner(\@seconds);
 print " seconds to complete\n";
 print "### Rows analyzed ";
        print @rows > $max_display ? "$rows[0] - $rows[-1]": sec_joiner(\@rows);
 print "\n";
 
 print "$query\n";
 print $queries_orig{$query}."\n\n";
}


sub sec_joiner {
 my ($seconds) = @_;
 $string = join(", ", @{$seconds});
 $string =~ s/, (\d+)$/ and $1/;
 return $string;
}

exit(0);

Query cache check

mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 3051 |
| Qcache_free_memory | 10365720 |
| Qcache_hits | 1650400 |
| Qcache_inserts | 757805 |
| Qcache_lowmem_prunes | 348499 |
| Qcache_not_cached | 9146 |
| Qcache_queries_in_cache | 10180 |
| Qcache_total_blocks | 24809 |
+-------------------------+----------+
8 rows in set (0.00 sec)

A couple important numbers from these values:
free space / free mem blocks = average free block size (in bytes)
10365720 / 3051 = 3398
used space / num queries in cache = average result size (in bytes)
(32 * 1024 * 1024 – 10365720) / 10180 = 2278

Source

reset MySQL password

Many suggested solutions did not work. But this one does the job

First You have to  Stop mysql server:
 

service mysqld stop

Now Start mysql server in safe mode with Follwing Options:
 

mysqld_safe –user=mysql –skip-grant-tables –skip-networking &

Now you have to Login to mysql server without password:
 

mysql -u root mysql

You will get Mysql Prompt.
 

mysql> UPDATE user SET Password=PASSWORD('newrootpassword') WHERE User='root';
 
mysql> flush privileges;
 
mysql> exit
 

Restart mysql server:
 

service mysqld restart

Login to MySQL With the New Password:
 

 mysql -uroot -pnewrootpassword

Thanks UnixLab