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();
?>

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 127.0.0.1 -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/reset_credit_nika.sh > /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:

#!/bin/bash
mysql -u USER -pPASSWORD -h 192.168.1.1 -s -N -e "SELECT id FROM nika_system.abon WHERE tarif=109;";

In /etc/crontab we add:

0 8 * * * root /scripts/freektb.sh > /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

SQL queries for WordPress

Changing the user password:

UPDATE wp_users SET user_pass = MD5('password') WHERE user_login = 'name';
UPDATE wp_users SET user_pass = MD5('password') WHERE ID=1;

Changing the user login:

UPDATE wp_users SET user_login='name' WHERE user_login='name';

Building a list of email commenters:

SELECT DISTINCT comment_author_email FROM wp_comments;

Disable comments for all posts:

UPDATE wp_posts SET comment_status = 'closed';

Enable comments for all posts:

UPDATE wp_posts SET comment_status = 'open';

Enable comments for all posts for registered users only:

UPDATE wp_posts SET comment_status = 'registered_only';

Delete all spam comments:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Delete all unapproved comments:

DELETE FROM wp_comments WHERE comment_approved = 0;

Clear URL field values for all comments:

UPDATE wp_comments SET comment_author_url='';

Closing comments in old posts:

UPDATE wp_posts SET comment_status = 'closed'
WHERE post_date < '2014-01-01' AND post_status = 'publish';

Closing of comments in a certain category:

UPDATE wp_posts p
LEFT JOIN wp_term_relationships rel ON ( p.ID = rel.object_id )
LEFT JOIN wp_term_taxonomy tax ON ( tax.term_taxonomy_id = rel.term_taxonomy_id  )
LEFT JOIN wp_terms tm ON ( tm.term_id = tax.term_id )
SET p.comment_status = 'closed' WHERE tm.slug = 'category_name';

Removing comments in the url of the following characters:

DELETE a,b FROM wp_comments a
LEFT JOIN wp_commentmeta b ON ( a.comment_ID = b.comment_id )
WHERE a.comment_author_url LIKE '%characters%';

Bulk editing of the commentor url:

UPDATE wp_comments SET comment_author_url = 'http://new_address'
WHERE comment_author = 'admin' AND comment_author_email = 'admin@example.com';

Another version of the massive change of url commentators:

UPDATE wp_comments
SET comment_author_url = REPLACE( comment_author_url, 'old_address', 'new_address' );

Bulk editing of the commenter name:

UPDATE wp_comments SET comment_author = REPLACE ( comment_author, 'old_name', 'new_name' );

Bulk edit email comment:

UPDATE wp_comments SET comment_author_email = REPLACE ( comment_author_email, 'old@example.com', 'new@example.com' );

Closing pings:

UPDATE wp_posts p SET p.ping_status = 'closed';

Opening pings:

UPDATE wp_posts p SET p.ping_status = 'open';

Delete all comments from pings:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Changing a specific website address or any expression in all posts to another:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'http://OLD', 'http://NEW');

Mass change of the author of records:

UPDATE wp_posts SET post_author=1 WHERE post_author=2;
UPDATE wp_posts SET post_author=1 WHERE post_author=16 AND post_modified<'2014-11-17 00:00:00';

Deleting record revisions:

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

Clearing the feed cache:

DELETE FROM `wp_options`
WHERE `option_name` LIKE ('_transient%_feed_%');

See also:
How to disable the WordPress plug-in via MySQL

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_db1;
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;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
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;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
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:

ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
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:

mysql

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 = 127.0.0.1

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@192.168.1.5;

If you want to create a new database:

CREATE DATABASE database_name;

For the changes to take effect, execute:

FLUSH PRIVILEGES;

You can delete the user as follows:

DROP USER 'user'@'localhost';

Example of viewing privileges:

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

The solution of error “ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla'”

I noticed once when importing a sql file the following error:

ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’

It arises because new versions of MySQL server use strict mode and parameters such as NO_ZERO_DATE do not allow entering date values like ‘0000-00-00’ into the database.

Connect to mysql server:

mysql -u root -p

Execute a query that displays the values of sql_mode:

show variables like 'sql_mode';

Copy the string with these values and exit mysql:

exit

Open the configuration file for example in the text editor nano (Ctrl+X for exit, y/n for saving or canceling the changes):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

I did not have sql_mode = in the file, so at the end of the file I inserted the line with the previously copied values, removing NO_ZERO_IN_DATE, NO_ZERO_DATE from it, in my case, the following happened:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart mysql to apply the changes:

sudo service mysql restart

Done, now when importing this error should not be.