Mysql – Cloud & Virtualization

mysql queries

mysqldump -u root-p $sw0rdf1sh –all-databases > db_backup.sql
mysqldump -u root-p $sw0rdf1sh –databases sampledatabase mysql | gzip > db_backup.sql.gz

max connection can be calculated by the formula

max.connection=(available RAM-global buffers)/thread buffers

To determine the sizes of all of your databases, at the mysql> prompt type the following command: All database

SELECT table_schema AS “Database”,

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)”

FROM information_schema.TABLES

GROUP BY table_schema;

To determine the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check: Specific database

SELECT table_name AS “Table”,

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”

FROM information_schema.TABLES

WHERE table_schema = “database_name”

ORDER BY (data_length + index_length) DESC;

After the command finishes, it displays a list of all of the tables and th

Try out the following example to create a database −

<html>

<head>

<title>Connecting MySQLi Server</title>

</head>

<body>

<?php

$dbhost = ‘localhost:3306’;

$dbuser = ‘root’;

$dbpass = ‘<password here>’;

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);

if(! $conn ){

echo ‘Connected failure<br>’;

}

echo ‘Connected successfully<br>’;

$sql = “CREATE DATABASE TUTORIALS”;

if (mysqli_query($conn, $sql)) {

echo “Database created successfully”;

} else {

echo “Error creating database: ” . mysqli_error($conn);

}

mysqli_close($conn);

</body>

</html>

Be careful while deleting any database because you will lose your all the data available in your database.

Here is an example to delete a database created in previous chapter −

[root@host]# mysqladmin -u root -p drop TUTORIALS

Enter password:******

This will give you a warning and it will confirm if you really want to delete this database or not.

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the ‘TUTORIALS’ database [y/N] y

Database “TUTORIALS” dropped

Finding long-running queries in MySQL

query_finder.sql

SELECT id,state,command,time,left(replace(info,’\n’,'<lf>’),120)

FROM information_schema.processlist

WHERE command <> ‘Sleep’

AND info NOT LIKE ‘%PROCESSLIST%’

ORDER BY time DESC LIMIT 50;

Script without using Performance_schema:

SELECT

trx.trx_id

,trx.trx_started

,trx.trx_mysql_thread_id

FROM INFORMATION_SCHEMA.INNODB_TRX AS trx

INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl

ON trx.trx_mysql_thread_id = pl.id

WHERE trx.trx_started < CURRENT_TIMESTAMP – INTERVAL 59 SECOND

AND pl.user <> ‘system_user’;

Script using Performance_schema:

SELECT

pl.id ‘PROCESS ID’

,trx.trx_started

,esh.event_name ‘EVENT NAME’

,esh.sql_text ‘SQL’

FROM information_schema.innodb_trx AS trx

INNER JOIN information_schema.processlist pl

ON trx.trx_mysql_thread_id = pl.id

INNER JOIN performance_schema.threads th

ON th.processlist_id = trx.trx_mysql_thread_id

INNER JOIN performance_schema.events_statements_history esh

ON esh.thread_id = th.thread_id

WHERE trx.trx_started < CURRENT_TIME – INTERVAL 59 SECOND

AND pl.user <> ‘system_user’

ORDER BY esh.EVENT_ID;

As a Database Administrator, this is our responsibility to find all locked and blocked transactions of MySQL Server.

Below is a script (work for MySQL 5.5 and above versions) :

SELECT

pl.id

,pl.user

,pl.state

,it.trx_id

,it.trx_mysql_thread_id

,it.trx_query AS query

,it.trx_id AS blocking_trx_id

,it.trx_mysql_thread_id AS blocking_thread

,it.trx_query AS blocking_query

FROM information_schema.processlist AS pl

INNER JOIN information_schema.innodb_trx AS it

ON pl.id = it.trx_mysql_thread_id

INNER JOIN information_schema.innodb_lock_waits AS ilw

ON it.trx_id = ilw.requesting_trx_id

AND it.trx_id = ilw.blocking_trx_id

When DBA is monitoring the database, it requires to find and kill some unwanted processes of MySQL Server.

How to find running processes or sessions?

Below are three different ways:

SELECT *

FROM INFORMATION_SCHEMA.PROCESSLIST

WHERE COMMAND != ‘Sleep’;

show processlist;

/*Execute using MySQL Console*/

mysqladmin -u root -p -i 1 processlist;

How to kill running processes or sessions?

KILL CONNECTION <processid>

This script is very useful for DBA for checking the size of tables and databases.

If you find freer space, DBA has to recover free space.

Please read interesting about MVCC:

Below are scripts:

/* Script for Database */

SELECT

table_schema AS DataBase_Name

,ROUND(sum( data_length + index_length ) / 1024 /1024,1) AS OccupiedSize_inMB

,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB

FROM information_schema.TABLES

GROUP BY table_schema ;

/* Script for Tables */

SELECT table_Name AS Table_Name,

ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS TableSize_InMB

,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB

FROM information_schema.tables

WHERE Table_Schema = ‘db_name’

Innodb buffer pool size script

I have a rough figure like, if you have 3 GB of InnoDB data and indexes, you require at least 1 GB size of innodb_buffer_pool_size.

Below is a script to calculate the size for InnoDB buffer pool.

SELECT

CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) AS RIBPS

FROM

(

SELECT SUM(data_length+index_length) Total_InnoDB_Bytes

FROM information_schema.tables WHERE engine=’InnoDB’

) AS T;

After executing above statement, You will get one RIBPS value. e.g. result in 2 RIBPS, so you require 2 GB size of the buffer pool.

You can change this parameter in the config file and restart your MySQL Service.

InnoDB reserves additional memory for buffers and control the structures, so that the total allocated space is approximately 10% greater than the specified size.

First check the current status of the different Log Parameters:

SHOW VARIABLES LIKE ‘%log%’;

Using above statement, you can check the current value of log parameter.

Now enable logging for general query and save the output into “mysql.general_log” table:

SET GLOBAL log_output = ‘TABLE’;

SET GLOBAL general_log = ‘ON’;

SELECT *FROM mysql.general_log;

Now enable logging for long running queries:

SET GLOBAL slow_query_log = 1;

SET GLOBAL long_query_time= 1;

SET GLOBAL log_queries_not_using_indexes= ON;

Sometimes, it is required to execute the FLUSH command of the log to get immediate updates into the Log Table.

FLUSH LOGS;

Best mysql sites

How to create a daily dump in MySQL?

Ask Question

up vote

down vote

favorite

I want to make a daily dump of all the databases in MySQL using

Event Scheduler

, by now I have this query to create the event:

DELIMITER $$

CREATE EVENT `DailyBackup`

ON SCHEDULE EVERY 1 DAY STARTS ‘2015-11-09 00:00:01’

ON COMPLETION NOT PRESERVE ENABLE

BEGIN

mysqldump -user=MYUSER -password=MYPASS all-databases > CONCAT(‘C:\Users\User\Documents\dumps\Dump’,DATE_FORMAT(NOW(),%Y %m %d)).sql

END $$

DELIMITER ;

The problem is that MySQL seems to not recognize the command ‘mysqldump’ and shows me an error like this: Syntax error: missing ‘colon’. I am not an expert in SQL and I’ve tried to find the solution, but I couldn’t, hope someone can help me with this.

Edit:

Help to make this statement a cron task

The InnoDB buffer pool performance can be calculated by using the following formula:

Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

innodb_buffer_pool_reads: It indicates the number of requests that cannot be satisfied with InnoDB buffer pool. Need to read from the disk.

innodb_buffer_pool_read_requests: It indicates the number of requests of logical reads from memory.

For example on my server, let me check the InnoDB buffer pool performance:

innodb_buffer_pool_reads = 91661

innodb_buffer_pool_read_requests = 4029033624

Performance = 91661 / 4029033624 * 100

InnoDB Performance = 0.0022750120389663. This means the InnoDB can satisfy most of the requests from Buffer pool itself. Very minimal percentage of reads are done from the disk. So there is no need to increase the innodb_buffer_pool_size value.
So the logic is if your InnoDB buffer pool is able to satisfy all your query requests, then you have enough innodb_buffer_pool_size. If it reads from disk (the working data set is not available in buffer pool memory) then you should increase the innodb_buffer_pool_size.

When the performance of the InnoDB buffer pool maximum, it should not be the reason for the slow performance of the database server, in this case, you can check for other configurations.

Stop the MySQL server process with the command sudo service mysql stop
Start the MySQL server with the command sudo mysqld_safe —skip-grant-tables —skip-networking &
Connect to the MySQL server as the root user with the command mysql -u root

At this point, you need to issue the following MySQL commands to reset the root password:

mysql> use mysql;

mysql> update user set authentication_string=password(‘NEWPASSWORD’) where user=’root’;

mysql> flush privileges;

mysql> quit

Where NEWPASSWORD is the new password to be used.

Restart the MySQL daemon with the command sudo service mysql restart. You should now be able to log into MySQL with the new password.

And that’s it. You can now set, reset, and recover your MySQL password.

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD (‘Newpassword’);

Database size check command.

SELECT table_schema AS “Database”, SUM(data_length + index_length) / 1024 / 1024 / 1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema

Outlook password – db@dmin@123

Create table – command;

CREATE TABLE IF NOT EXISTS tasks (

task_id INT(11) NOT NULL AUTO_INCREMENT,

subject VARCHAR(45) DEFAULT NULL,

start_date DATE DEFAULT NULL,

end_date DATE DEFAULT NULL,

description VARCHAR(200) DEFAULT NULL,

PRIMARY KEY (task_id)

) ENGINE=InnoDB

Cloud & Virtualization

How to repair a mysql database in a easy way

Optimize Your MySQL Server With the MySQL Tuner Script

Setting up a Mysql cluster with MariaDB Galera

HIGH AVAILABILITY: MYSQL CLUSTER WITH GALERA + HAPROXY

mysql-how-to-log-general-and-long-running-queries-into-log-table/

Installation of MySQL on CentOS plain

Backup & Restore MySQL database with compression option

How to backup your MySQL Database[s]

Change MySQL Data directory location in Windows

mysql queries