Installing and using mysqltuner.pl

MySQLTuner – This is a Perl script that will give recommendations for improving the performance and stability of the MySQL server.
GitHub Page – https://github.com/major/MySQLTuner-perl

MySQLTuner in Ubuntu can be installed with the command:

sudo apt-get install mysqltuner

In CentOS as follows:

yum install mysqltuner

Continue reading Installing and using mysqltuner.pl

How to pull data from one column of a mysql table

To see data from one column, it is enough to execute the SQL query (where “abcd” is the name of the column in the table):

SELECT abcd FROM table;

To export to a file, just run the command in Linux:

mysql -u root -e "SELECT abcd FROM database;" -s -N > file.txt

Here is an example of exporting email addresses from a mysql table to an http page using PHP.
The thought immediately came to this plan (create a php file and open it through the browser):

<?php
// Connecting to mysql server
mysql_connect("localhost", "USER", "PASSWORD") or die (mysql_error ());
// Choosing a database
mysql_select_db("users") or die(mysql_error());
// SQL query
$rows = "SELECT * FROM account";
// Run this SQL query
$d = mysql_query($rows);
// Each row becomes an array ($row) using the mysql_fetch_array
while($row = mysql_fetch_array($d)) {
// Display the values of the email column
echo $row['email'] . "<br />";
}
// Close the connection to the database
mysql_close();
?>

Change the default value in MySQL columns

I’ll give an example of specifying or changing the default value in the MySQL column.
Let’s see the list of tables in the database:

SHOW TABLES;

Let’s see the structure of the table we are interested in:

DESCRIBE internet_main;

Let’s say the activate column has the type date and the default value is 0000-00-00, and we want to make 3000-01-01, then we will execute sql query:

ALTER TABLE internet_main ALTER activate SET DEFAULT '3000-01-01';

You can also delete the default value:

ALTER TABLE internet_main ALTER activate DROP DEFAULT;

Or return it as it was:

ALTER TABLE internet_main ALTER activate SET DEFAULT '0000-00-00';

In strict mode MySQL can not set the value 0000-00-00, so you can temporarily disable the strict mode:

SET sql_mode = '';

The script for adding IP addresses from a file to ipset

It took one day to write a script to add to ipset all the IP for which the session was started on the access server, Abills billing was used, so I decided to take IP addresses from the MySQL billing table.

The first step is to create a test ipset:

ipset create test iphash

Continue reading The script for adding IP addresses from a file to ipset

Solution MySQL ERROR 1055 (42000)

Recently performed the necessary SQL queries and noticed the following error:

ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_name.table_name.column_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Continue reading Solution MySQL ERROR 1055 (42000)

How to see the process of importing a SQL file?

In order to see the process of importing SQL files, I use the Pipe Viewer utility.
Pipe Viewer is very useful especially when the SQL file is large, allows you to roughly estimate the remaining time before the import of the file is completed, displays the import process in percent and the time passed.

You can install the Pipe Viewer in Ubuntu/Debian using the command:

sudo apt install pv

In Fedora:

yum install pv

Suppose earlier we imported a sql file and did not see the process:

mysql -u root DATABASE_NAME < dump.sql

Now, to see the process, we will perform the import using the Pipe Viewer:

pv dump.sql | mysql -u root DATABASE_NAME