Category: Mysql
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
https://dbatricksworld.com/change-mysql-data-directory-location-in-windows/
Thanks,
Sheikvara
91-9840688822
mysql queries
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
- https://www.a2hosting.in/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql
- https://www.tutorialspoint.com/mysqli/mysqli_where_clause.htm
- https://www.dbrnd.com/2015/11/mysql-how-to-log-general-and-long-running-queries-into-log-table/
- https://www.exoscale.com/syslog/keeping-mysql-in-check/
- How to create a daily dump in MySQL?
Ask Question
up vote
0
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
DO
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