table_open_cache – the number of cached open tables for all threads. Opening tables is a resource-intensive process, so it is recommended to keep a certain number of tables open in the cache.
Continue reading “Changing table_open_cache in MySQL”Category Archives: Databases
Installing and using innotop
innotop is a utility for monitoring the status of MySQL server.
I will give an example of installing innotop in Ubuntu. Since innotop is present in mysql-client, we install it and the necessary components:
Continue reading “Installing and using innotop”Solution apparmor=”DENIED” operation=”open” profile=”/usr/sbin/mysqld”
Once optimizing one of the servers, I executed the command:
dmesg
And I saw the following messages:
Continue reading “Solution apparmor=”DENIED” operation=”open” profile=”/usr/sbin/mysqld””
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.
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 = '';
How to run MySQL server on specific IP
The appropriate solution I found for running MySQL server on specific IP addresses is to run it at all and then filter the connected clients through iptables.
For the test, I used Ubuntu Server 16.04.5 LTS, which had more than 200 external white IPs and was highly loaded.
SQL queries for ABillS
In this article I will give examples of some SQL queries for the base of the ABillS billing system.
Continue reading “SQL queries for ABillS”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