I have a SQL query, how can I create a CSV file from the SQL Query?
I had a customer looking for such an option. This can be easily achieved using ‘into outfile’ attribute of MySQL. Let me provide an example query like the following:
select * from wp_woocommerce_orders left join wp_posts on wp_posts.id = wp_woocommerce_orders.order_id into outfile '/var/lib/mysql/orders_list.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
The above query is joining the order table of woocommerce with the WordPress posts table, and putting the output file in /var/lib/mysql/orders_list.csv file, in which each field is being terminated by a comma (aka comma separated file) and after each row, it is terminating with a new line separator. One thing you need to remember is if you are not root, then you should provide the path to store the file in a place where you can write the file, for example, your home directory, which could be like /home/yourusername/something.csv
Hope this helps others to know how to achieve this.
My disk is full. When using du, I could see my MySQL log folder is taking up all the disk. These files are not mysql-bin files, instead, these are mysql-relay-bin files. How can I truncate or purge these files?
Mysql creates mysql-bin files which are called binary files for MySQL on the master side. It then streams the binary files to the replicas to reflect the changes. Mysql doesn’t really store the binaries on the replica side, hence it is safe to delete the relay-bin files. Although, there are other safe strategies to accomplish this or never fall into a situation of ‘out of space’.
Firstly, we need to understand, expire_log_days MySQL attribute doesn’t work for the replica. The reason is simple, it doesn’t store the binary files. Hence, using this is worthless for replicas. You can’t use ‘PURGE’ SQL command either for Replica log purging. Replica has a special attribute ‘relay_log_purge’ which purges the relay-bin logs periodically. You can also set replica to follow a specific size of the log file by using ‘relay_log_space_limit’ attribute.
Now at a very certain time, if you want to free up space, other than gross and brutal deleting relay-bin log files, what you can do is the following:
# start your mysql console session
# stop the slave
# reset the slave
# now start the slave again
Now, once the slave is reset, it will start streaming the bin log from the “pos” it has in its queue to populate the pending jobs.
Once the sync is done, you may check the replica status using:
show slave status \G;
Make sure the following two are set to yes:
If not, you should look at the ‘Last_SQL_Error’ or ‘Last_IO_Error’ section to find out why these are not pushing.
One key bottleneck, every software engineer who uses MySQL, fails to realize, is how to utilize MySQL cache. I have seen people believing the MySQL query cache as the MySQL cache. Sadly, it is not. The query cache is probably one of the worst cache attributes you may enable for your MySQL instance optimization for a high-traffic website.
MySQL utilizes buffers for caching. These buffers are stored in RAM. The key benefit of these buffers is that the changes can also be stored in RAM before flushing the data to disk, along with sync to the replica. Let’s look at an example.
Let’s say, you have selected a post with id 1001
select * from posts where id = 1001
If you have a MySQL buffer, then the result of this query would be placed in the memory.
Now, the subsequent call, let’s say the following:
set title = 'This is a new title'
where id = 1001
Now, imagine, how the above query will be handled in MySQL? MySQL would write in the RAM, and keep the chunk as dirty to be flushed to disk. Now, that makes the above update super fast to the interactive queries. Because writing to disk would be super slow compared to writing to RAM.
If you live under a heavy load, this is an awe-inspiring tweak. Because this makes your MySQL instance a RAM-based database, with an IO player, which keeps track of your dirty bits, and flushes them when needed. While the con of the approach is that if the system is crashed, dirty bits are lost. Now, we can’t directly say lost, if you are using a system with journaling enabled, that keeps track of the changes, and rolls back.
What is the right Innodb Buffer Pool Size?
There is much misinformation about the size of your buffer pool. MySQL says you should use 80% of your RAM as the pool, which is significantly incorrect. If your database is of size 500MB and the RAM you hold is much larger, then using 80% is quite a waste. You could probably use them for other buffers like a join buffer or a temporary table or a heap table. But unwisely using that for Innodb Buffer is not worthy.
To start with, you can set the buffer pool size to the total size of your database first and let the system run for a day or two. Once done, then you may analyze a few MySQL status variables to find out whether should you increase or decrease. Let’s find out a few details
To get the MySQL status variables, run the following from your MySQL console:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
Now, this should give you a details like the following:
We can use a few metrics from this to determine whether we need a change in buffer or not.
First, we look at the variable ‘Innodb_buffer_pool_pages_free’. It says the amount the free pages you have. If you have set a pretty large buffer pool, you should have a large number of pages in total, hence seeing the number ‘Innodb_buffer_pool_pages_free’ should not make you realize if you are going out of the pool page or not. You may calculate a ratio like the following:
free_page_ratio should not be less than 10% according to my experience until you are running out of RAM. If you have enough RAM, and the ratio is less than 10%, you have fewer free pages, you might want to increase the pool size.
Now, one thing to remember, the value, might stay below 10% all the time, it might not mean you are running out of pool space, instead, you are at the top of the optimization or near to cut off. To determine, if you are out of pool space, make sure to monitor the variable several times of the day and see how often does it change. If it gets lower too often, that suggests you are hitting the boundary and it is time to increase the buffer pool size.
Next, you need to look at the number of read requests and the number of reads you have performed in total. If you are seeing a lot of read requests and the ratio to reads is higher, then the buffer isn’t enough to store your full database requests in the RAM, and you should increase the value.
Remember, Innodb_buffer_pool_reads means the amount of requests Innodb had to query the disk to get the data from. The value should be less than 1% for maximum efficiency. In my above example, the value is less than .1%, which makes the Innodb performance the best.
Flushed & Wait Free
The other thing, we need to track is that, Innodb_buffer_pool_wait_free. The variable should be near 0 or 0. If the variable is higher, it suggests a situation has happened, where the flushing was essential to free pages. Even if you have a good read_ratio, but if the variable shows a large number, then you need to increase the buffer pool size to make sure we do not need to free pages at the peak times.
Continuous monitoring is essential to perfectly use Innodb Buffer Pool. It is an extremely essential part of MySQL-based software and should be used wisely. Proper usage can excessively decrease your server CPU cost, and maximize the overall performance.
Unfortunately, this was only possible until 10.3 or below. Since 10.4, Mariadb uses the following command:
systemctl start mariadb
Cyberpanel uses that command if you are using CentOS 8 or Ubuntu. But it looks like CentOS 7 now installs MariaDB 10.4 as default unlike 10.3 as default previously.
Even if you edit this command, Cyberpanel will reclone the script, hence it is not possible to change this from here. The nasty trick would be to load a mysql.service file before starting the cyberpanel installation. Here is what you would need to do to do this.
First reload your VPS with CentOS 7
Run yum update and install nano
yum update -y
yum install nano -y
3. Now, load the following file with the text given below:
Load the file with the following text
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# .include /usr/lib/systemd/system/mariadb.service
# ...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
# For more info about custom unit files, see systemd.unit(5) or
# Copyright notice:
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.
Description=MariaDB 10.4.21 database server
## Core requirements
# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
## Package maintainers
# CAP_IPC_LOCK To allow memlock to be used as non-root user
# CAP_DAC_OVERRIDE To allow auth_pam_tool (which is SUID root) to read /etc/shadow when it's chmod 0
# does nothing for non-root, not needed if /etc/shadow is u+r
# CAP_AUDIT_WRITE auth_pam_tool needs it on Debian for whatever reason
CapabilityBoundingSet=CAP_IPC_LOCK CAP_DAC_OVERRIDE CAP_AUDIT_WRITE
# PrivateDevices=true implies NoNewPrivileges=true and
# SUID auth_pam_tool suddenly doesn't do setuid anymore
# Prevent writes to /usr, /boot, and /etc
# Doesn't yet work properly with SELinux enabled
# Prevent accessing /home, /root and /run/user
# Execute pre and post scripts as root, otherwise it does it as User=
# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
# Do not panic if galera_recovery script is not available. (MDEV-10538)
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "[ ! -e /usr/bin/galera_recovery ] && VAR= || \
VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] \
&& systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"
# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql
# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [Service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
# Don't want to see an automated SIGKILL ever
# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
## USERs can override
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following under [Service] will override this file's
# Useful options not previously available in [mysqld_safe]
# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# Explicitly start with high IO priority
# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
# Set an explicit Start and Stop timeout of 900 seconds (15 minutes!)
# this is the same value as used in SysV init scripts in the past
# Galera might need a longer timeout, check the KB if you want to change this:
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
# Number of files limit. previously [mysqld_safe] open-files-limit
# Maximium core size. previously [mysqld_safe] core-file-size
# Nice priority. previously [mysqld_safe] nice
# Timezone. previously [mysqld_safe] timezone
# Library substitutions. previously [mysqld_safe] malloc-lib with explicit paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=
# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sysctl -q -w vm.drop_caches=3
# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......
# crash-script equalivent
Now, save the file, and start your cyberpanel installation as it is with the following:
sh <(curl https://cyberpanel.net/install.sh || wget -O - https://cyberpanel.net/install.sh)
Hope it solves the problem. I am hoping, soon enough Cyberpanel would be able to address the issue the way they solved this for CentOS 8 and Ubuntu.
For example, you manage a high traffic website, that utilizes an abstraction layer like an ORM to manage MySQL queries. Now, as a DevOps/System Admin, it becomes difficult for you to get a stat of which MySQL query being overused in the scenario. For these cases, one way, you may get some idea on what being overused, is called ‘MySQL General Logs’. Remember, it is very much different than the MySQL Slow Query Logging. It is not essential to have a slow query in the system to determine if your mysql is boggling. It is very much possible, there are queries, that take very small amount of time, but starves your CPU by executing many times and performs the same operation. Once you are able to identify them, you may utilize any Hashmap based caching strategy like Memcache or Redis or Simple file cache to reduce your load down on MySQL instance or cluster.
First, we create a query logging file and set the right permission:
Once the file creation is done, now we can enable general log either by using mysql shell, if you would like to avoid restarting your mysql instance or in my.cnf file to keep the change permanent. A point to note, you should not do query logging all the time, as it decreases MySQL performance by 15-25%, which might hurt your overall production performance, plus the size of log will cumulatively increase if you have a server that performs over a thousand or more queries per second.
# Type in your shell prompt
# this will open your mysql shell, you may run mysql commands as below:
mysql > SET global general_log = 1;
mysql > SET global log_output = '/var/log/mysql_query.log';
This should immediately advise mysql to push the logs to /var/log/mysql_query.log.
Now, if you observe the file, you may see the queries are coming up so quickly that you may hardly find anything out from it. The file has no output until you aggregate the result. If you have a large file, a better way to aggregate result by using Lotstash and Elasticsearch. We won’t do that here, that would be a topic for another blog post. We would instead use, some basic shell aggregation to see if we can determine anything useful from this. You may use the following tool, that list the last 10000 lines, then sort, and group the unique lines with the count and order by ascending to put the most frequent query at the end of the line:
This will help you by giving the top most used query in last 10K queries. If the number is more than 5%, you need to pay attention to that. If it is the same query, that means, you may use a Hashmap based caching technique to reduce database boggling and improve performance.
There are times, you may see the following error in your MySQL/MariaDB based Cpanel server:
[ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist in engine
The issue is most likely related to your Innodb tablespace got corrupted, and hence some tables under the mysql database got locked out as some of them use Innodb storage engine. One of the outcome of the symptom is, if you try to add a user to a database, it doesn’t add or show the green notification any longer in cpanel mysql databases section. Instead it just stops.
The only and best way to properly fix this would be restore the ‘mysql’ database or just the ‘servers’ table from your backup. If you don’t have one, you may just create the ‘servers’ table using the following SQL statement:
CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
You may require to drop the table first. Now, if you can’t do this either, then there is only one way left, is to uninstall your MariaDB installation, and let Cpanel/WHM to install them for you.
Now, you may install the latest MariaDB from WHM >> MariaDB/MySQL Upgrade and proceed accordingly. This should install the latest for you with a fresh ‘mysql’ database for you. But it will not alter your other data files, means your other databases should be fine.
One thing, you need to remember, after a fresh mysql installation with the old data files, you will have the authorizations missing. You would have to recreate the database users manually to get the privileged table filled up.
If you are here, that means, you probably have panicked the same way, I did around 12 years back. I lost my ib_logfile0/ib_logfile1/ibdata1 all at once for a server that excessively utilizes Innodb tables. I had to recover vital data from the same situation today on a random request who does not have backups, and thought it is better to keep this as a document for future.
One key purpose of utilizing Innodb tables instead of MyISAM is that, the benefit on writes. It always outperform MyISAM in writes due to the use of extra efficient buffers. But, this also causes Innodb to vulnerable from crashing. As Innodb stores some sensitive data to 3 specific files, loosing them, also looses some serious mapping instruments for the database engines to recognizes Innodb table structure and data.
Who can follow this technique?
If you have lost any of ib_logfile0, ib_logfile1, ibdata1 or all of them, but still manages to keep the database folder intact with the .frm and .ibd files (which you would, if you have accidentally deleted the log file or the data only) and also have the following option NOT DISABLED in your mysql configuration ‘innodb_file_per_table’. This option is enabled by default, until you are explicitly disabling this to increase performance. A suggestion: only do this, if you keeping real time backups of your databases. Otherwise, it is better to have this enabled
What is ‘innodb_file_per_table’?
Primarily the tablespace stores and uses data from system tablespace for Innodb. But, as this creates a single point of failure from ibdata and log files, Innodb by defaults also stores the tablespace in table’s own data file, which is .ibd file. That means, if I lose the ibdata/logfile mappings, I can still use the .ibd file to restore my tablespace and do the schema to data mapping only if I allowed innodb to store these information to the database’s own .ibd file. You may read more about the parameters from MySQL documentation:
How to Recover an Innodb Table from database files only?
There are two steps to this process. One is to identify and recognize the database schema from the frm file and then basically find a way to import the tablespace from .ibd file and introduce it to innodb engine system tablespace.
First Step First: How to get the schema from .frm files?
First, you must install mysql-utilities tools to get access mysqlfrm tool, you may get the instructions to install this here:
Once this is done, now you have two options to read mysqlfrm files. My favorite way is to use the ‘diagnostic’ attribute. To achieve this, run the following:
I assumed, your database name is ‘your_database’ and the table you are trying to recover is ‘assets’. The above command will return you the schema of ‘CREATE TABLE’ you need to use. First, create a new database, and run this on the SQL console to generate the table first on the new database.
Second Step: Get your data and mapping back from .ibd to system tablespace
Once the database has the table, it will also create a .frm and .ibd file for you. What we need to do, is to first, make it forget the existing .ibd file it created, sync the .ibd file from our collapsed database, make the mysql innodb engine to recognize tablespace from the backup tablespace of this .ibd file and store & use it from system tablespace. These lines are complex, and might sound a bit difficult. No worry, let’s do it.
Run the following command first to let it forget the .ibd it has created now:
alter table assets discard tablespace;
Remember the following, our table name is ‘assets’. If you have a different table name, make sure to replace this accordingly. What this has done, is removed the assets.ibd file it created in /var/lib/mysql/new_database/ folder as we asked him to forget the existing .ibd file. Now we first need to copy the backup/old .ibd file to this location with the correct permission. I would use rsync to make sure permissions remains intact here:
Once this is done, we know, .ibd contains a backup of our original tablespace. We only need to make mysql & innodb recognize this. To achieve this, you may do the following from the Sql console:
alter table assets import tablespace;
If it throws a warning on not being able to file the .cfg file, you may forget it, because it is not essential to have a .cfg to recognize permissions/configurations.
If everything runs well, you should see your rows are back. It’s because innodb has now fetched your tablespace data from .ibd file to system tablespace and it can now recognizes the mapping to your data, viola! All you now need is to repeat the process for all of your innodb tables, and recover the whole database.
This would be a simple step by step post for the help of my customers. You require two tools for this.
i) putty – ssh client ii) WinSCP – File transfer client over SSH (You may use FTP or File manager as well)
Once you download putty, open it, and use your server’s IP in the Host Name section:
Now, click on open. In the prompt, type ‘root’ as login as and enter. Then type the password and enter. If the password for the root is right, it will login to the command shell prompt or else it will put you in the authentication error console.
Now, open WinSCP to login to your server using the same manner of SSH. Once done, transfer your sql file to the home directory of root.
To transfer the file, navigate the sql file from your desktop on the left side and then drag it to the right side of WinSCP window. That will start the transfer. Remember the path of the destination on the server. Path is shown in the red marked area in the image.
Once the file is transferred, come back to putty, and change directory to the path you had noted in WinSCP, in our case, it is /root/
mysql -uusername -ppassword your_database_name < your_sql_file_name.sql
Remember the following. Your mysql username should go right after ‘-u’ without any space and the mysql password should be the same without space. Replace the ‘your_database_name’ with your original database name in the server and ‘your_sql_file_name.sql’ with the exact sql file name that you had uploaded.
Now if you allow sometime, the command should complete after the restore. If it returns any error, you would need to attend them to do a complete restore.