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 = '';