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):

// 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

How to change a WordPress theme through MySQL

To change the WordPress theme via MySQL, first see what theme is specified at the moment, for this, execute the SQL query via phpMyAdmin or MySQL client:

SELECT * FROM wp_options
WHERE option_name = 'template'
OR option_name = 'stylesheet'
OR option_name = 'current_theme';

Next, see what themes are in the /wp-content/themes/ directory.

For example, to change to the standard Twenty Fifteen theme, let’s execute three SQL queries:

UPDATE wp_options SET option_value = 'twentyfifteen' WHERE option_name = 'template';
UPDATE wp_options SET option_value = 'twentyfifteen' WHERE option_name = 'stylesheet';
UPDATE wp_options SET option_value = 'Twenty Fifteen' WHERE option_name = 'current_theme';

How to disable the WordPress plug-in via MySQL

To disable all WordPress plugins via MySQL, you must:

1) Be sure to make a backup copy of the database.

2) Open the phpMyAdmin or MySQL client from the terminal:

mysql -u USER -p

3) Execute the SQL query (if necessary, specify the correct prefix wp_):

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

After that, all plug-ins will be disabled and you can activate them again one by one in the admin panel.

You can also temporarily disable the plugin by renaming the directory with its files, the plugins are in the /wp-content/plugins/ directory.

SQL queries for Zabbix

I will write some useful examples of sql queries for the Zabbix database:

Search for a host by name:

SELECT * FROM hosts WHERE host like '%name%';
SELECT * FROM hosts WHERE name like '%name%';

Find the data items of the specified host:

SELECT * FROM items WHERE hostid = '10105';

Find the history of the values for the specified data item:

SELECT * FROM history WHERE itemid = '24526';

Delete the whole history of the data element until 01.11.2014 (the time is specified in Unix format, converters can be found through the search engine):

DELETE FROM history WHERE itemid = '24526' AND clock < '1414800000';

Delete all data history until 01.11.2014:

DELETE FROM history WHERE clock < '1414800000';

Massively changed the interval and dynamics of changes to the data elements of templates and hosts, I have the following queries (the first identifies the template ID or host, the second changes the intervals):

SELECT * FROM `hosts` WHERE host="Template ICMP Ping";
UPDATE items SET delay=3600 WHERE hostid=10105 AND delay=600;
UPDATE items SET trends=180 WHERE hostid=10047 AND trends=365;

Executing a SQL query from a script in Linux

It was necessary recently to write a script that executes a sql query into the MySQL database.
Example content:

# Description, here I wrote for others that the script is added to the crontab, so that it is not moved
mysql -u USER -pPASSWORD -h -e "UPDATE nika_system.abon SET otkl=0 WHERE depozit > '10' AND (otkl='-1' OR otkl='-2');";

In order for the script to run automatically, open the crontab file in any text editor (in the nano editor CTRL+X for the output and y/n for saving or canceling the changes):

sudo nano /etc/crontab

Add the following line to it:

0 9 * * * root /home/nika/scripts/ > /dev/null 2>&1

Now every day at 9 am the script will be executed.

Here is an example of daily automatic output of data from a sql table into a text file:

mysql -u USER -pPASSWORD -h -s -N -e "SELECT id FROM nika_system.abon WHERE tarif=109;";

In /etc/crontab we add:

0 8 * * * root /scripts/ > /srv/samba/dir/mirazh/$(date +%Y-%m-%d).txt

For security reasons, it’s better not to specify the password in scripts, see my article – Connecting to MySQL from localhost without entering a password

How to change MySQL database encoding and its tables

Here is an example of changing the MySQL encoding of a database and tables.
Before any actions on important data it is necessary to make a backup copy, for example:

mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz

For the test, we will connect to MySQL and create a couple of new databases without specifying the encoding and specifying:

mysql -u root -p
CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Create a test table in the first database and see its encoding:

USE test_db1;

firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP

show table status like 'users';

Create a test table in the second database and see its encoding:

USE test_db2;

firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP

show table status;

Let’s also look at the encoding of both databases:

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1";
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";

To see the encoding of a column in a specific table, you can do this:

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "test_db1"
AND table_name = "users"
AND column_name = "firstname";

In my case, the table in the first database was encoded with latin1_swedish_ci, since it is standard, and in the second one utf8_general_ci, since I specified it beforehand.

You can see the table of possible encodings by such requests:

show collation;
show collation like 'utf8%';
show collation like 'latin1%';

View existing databases as follows:

show databases;

View existing tables in the database:

USE test_db1;
show tables;

Now we change the encoding of the first database and its tables to utf8 and immediately check:

USE test_db1;
ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
show table status;

If you need to change the encoding in a sql file, open it in the Notepad ++ editor, for example, convert it to UTF-8/without BOM, and if the encoding in SET NAMES is specified at the beginning of the file, change it there, then you can import the file into the database.

How to create a MySQL user and configure access rights

To create a user, we first connect to the MySQL server console:


Let’s see what users are:

select * from mysql.user;
select user,host from mysql.user;

Create a user (where localhost is specified from where the user can connect, you can specify the IP address, localhost – from the local machine where the MySQL server itself, or % from any addresses):

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

If you intend to connect not only locally, you need to comment out the line in my.cnf:

#bind-address =

And restart the MySQL server:

sudo service mysql restart

After that, I recommend restricting access to MySQL using IPTables.
See also – Configuring IPTables

To assign the newly created user unlimited permissions to a specific database, execute the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

If necessary on all bases:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

You can specify specific access rights:

GRANT SELECT ON database_name.* TO 'user'@'localhost';
GRANT SELECT, INSERT ON database_name.table_name TO user@;

If you want to create a new database:

CREATE DATABASE database_name;

For the changes to take effect, execute:


You can delete the user as follows:

DROP USER 'user'@'localhost';

Example of viewing privileges:

SHOW GRANTS FOR 'user'@'localhost';
SELECT * FROM information_schema.user_privileges;