Category Archives: MySQL

How to show the last queries executed on MySQL?

You can enable a general query log for that sort of diagnostic. Generally you don’t log all SELECT queries on a production server though, it’s a performance killer.
Edit your MySQL config, e.g. /etc/mysql/my.cnf – look for, or add, a line like this:

[mysqld] log = /var/log/mysql/mysql.log
Code language: JavaScript (javascript)

Restart mysql to pick up that change, now you can:

tail -f /var/log/mysql/mysql.log
Code language: JavaScript (javascript)

Hey presto, you can watch the queries as they come in.

HOW TO SELECT row as COUNT() or how to count rows

SELECT browser, COUNT(browser) AS freq FROM

SELECT browser, COUNT(browser) AS freq FROM (SELECT CASE WHEN useragent LIKE '%Chrome%' THEN 'Chrome' WHEN useragent LIKE '%Safari%' THEN 'Safari' WHEN useragent LIKE '%Firefox%' THEN 'Firefox' WHEN useragent LIKE '%MSIE 7%' THEN 'IE7' WHEN useragent LIKE '%MSIE 8%' THEN 'IE8' WHEN useragent LIKE '%MSIE 9%' THEN 'IE9' WHEN useragent LIKE '%MSIE 10%' THEN 'IE10' WHEN useragent LIKE '%rv:11%' THEN 'IE11' ELSE 'Other' END browser FROM loginhistory) AS browsers GROUP BY browser ORDER BY freq DESC
Code language: SQL (Structured Query Language) (sql)

Grant all privileges to root@localhost for mariadb or mysql on ubuntu 16.04 to use phpmyadmin

If you have ever had a problem to login to phpmyadmin with root you will need to add the rights.
login to mariadb/mysql:

mysql -uroot -p

check if user is added:

SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
Code language: HTML, XML (xml)
MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
Code language: HTML, XML (xml)
+----------+------+ | User | Host | +----------+------+ | librenms | % | +----------+------+

Now add the root user:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'YOUR PASSWORD' WITH GRANT OPTION;
Code language: JavaScript (javascript)

And now flush:

flush privileges;
MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
Code language: HTML, XML (xml)
+----------+-------------+ | User | Host | +----------+-------------+ | librenms | % | | root | % | +----------+-------------+

HOW TO MySQL DUMP from terminal

You can create mysqldump like this:

mysqldump --defaults-extra-file=/path/to/config.cnf
Code language: JavaScript (javascript)

create config.cnf:

[client] user = whatever password = whatever host = whatever

Now lets backup a database

mysqldump --defaults-extra-file=/path/to/config.cnf database > /root/export.sql
Code language: JavaScript (javascript)

How to create MySQL replication server

10.20.6.29 – Master Database
10.20.6.30 – Slave Database

pico /etc/mysql/mysql.conf.d/mysqld.cnf

chnage the bind address to your server real ip address 10.20.6.29

bind-address = 127.0.0.1

uncomment

server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
Code language: JavaScript (javascript)

at the bottom of the file add your databases

binlog_do_db = newdatabase binlog_do_db = newdatabase2
sudo service mysql restart

now log in with root on the mysql server via terminal

mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Code language: JavaScript (javascript)

copy the databases to the slave server with phpmyadmin
in 10.20.6.30 a.k. the slave

pico /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2 relay-log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = newdatabase binlog_do_db = newdatabase2
Code language: JavaScript (javascript)
sudo service mysql restart CHANGE MASTER TO MASTER_HOST='10.20.6.29',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001';
Code language: JavaScript (javascript)
START SLAVE; SHOW SLAVE STATUS\G

If there is an issue in connecting, you can try starting slave with a command to skip over it:

SLAVE STOP; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
Code language: PHP (php)

MySQL Query where id is not equal to (WHERE id <> 6)

Have you ever needed a query where you search for all except one thing?
Here is an example of a query where you search for all except the number 6:

SELECT user_id, username FROM users WHERE user_id <> 6
Code language: HTML, XML (xml)

If you have ten users with id from one to ten , number six won’t be in the executed query.