Slow MySQL service? enable slow query log

One of the main requirements for a fast web server is to has efficient and effective SQL server. If you having the slow mysql service, and you are out of idea where gone wrong, you can actually try is enable slow query log.

Sometime, a single SQL query may be the cause of all the server’s problems.

To enable slow query log, simply add the following line to MySQL configuration file (my.cnf or my.ini), and then restart the MySQL server:

log-slow-queries

or

log-slow-queries = [path to the log file]

Restore InnoDB database from RAW file (Window)

Note:  ensure the mysql version is same in both server, and also have the root password.

(1) create VM and install the same version of mysql that need to restore

(2) Stop the mysql service

(3) Restore the file as below to your VM

-database raw file (.frm files)
-mysql raw file
-ibdata1 (InnoDB data files)
-ib_logfile1 (InnoDB log files)
-ib_logfile0 (InnoDB log files)
-my.ini

(4) Start the mysql service
-in this step, you may discover the mysql service unable to start. Hence you need to confirm that all setting is same with the database previous host

(5) Dump the db out as .sql
-You need to use the previous host root password as your mysql is restore as previous host.

Done, you have save the world….

Reference:

MySQL

Change the collation for all tables in a MySQL database

How to change the database collation is easy, but how to change all the table collation? You can use script as below

This example is change all table collation to utf8_general_ci

<?php
$db = mysql_connect('localhost','dbuser','password');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('dbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";
?>

Note:
dbuser= database user
password= database user password
dbname= database name

PHP-MySQL connection string

Using the Host

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Using Hostname

<?php
// we connect to example.com and port 3307
$link = mysql_connect('example.com:3307', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);

// we connect to localhost at port 3307
$link = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Using Socket Path

<?php
// we connect to localhost and socket e.g. /tmp/mysql.sock

// variant 1: omit localhost
$link = mysql_connect(':/tmp/mysql', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);

// variant 2: with localhost
$link = mysql_connect('localhost:/tmp/mysql.sock', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>