Tag 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.

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.

Ubuntu mycli install (mysql terminal – modern alternative to the default MySQL client)

Mycli is a modern alternative to the default MySQL client. This tool does to MySQL what bpython does to the standard Python REPL. Mycli will auto-complete keywords, table names, columns, and functions as you type them.

The completion suggestions are context-sensitive. For example, after the SELECT * FROM, only tables from the current database are listed in the completion, rather than every possible keyword under the sun.

1. install some packages:

apt-get install python3-click -y
Code language: Bash (bash)

2. now to install the software

apt-get install mycli -y
Code language: Bash (bash)