mysql logs Archives - Black Sail Division

Hi there! Today I want to show you how to install latest version of MySQL Community server (5.7.16) on CentOS 7. I will show you how to install it, set root password, configure server and optimize it for performance. Also I will show you how to create databases and assign users to them.

How to install MySQL on CentOS in latest version

When you try to install MySQL on bare CentOS, you will actually install MariaDB (fork of MySQL) instead. It comes with version 5.5.* It's up to you what you want to use, but I prefer MySQL. Especially 5.7 version. There is great performance boost and lot of new features added, comparing to 5.5 or 5.6.

If you have any data on MySQL, it's best to create a backupof data, before you will change anything. Here is a tutorial how to perform backup of MySQL databases.

Before you will actually install MySQL, make sure that MariaDB is not installed. You can just remove it. TIP: removing MariaDB will not remove any databases. It will remain on your hard drive.

sudo yum remove mariadb -y

So let's get to installation. First you need to enable MySQL repository. It's easy by adding following file to yum repositories:

sudo vi /etc/yum.repos.d/mysql-community.repo

and place there following content:

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=http://dev.mysql.com/doc/refman/5.7/en/checking-gpg-signature.html

Save the changes. It will enable MySQL repository where from you can install latest version.

Installation is pretty easy:

sudo yum install mysql-community-server -y

That's it! You have MySQL installed in latest version!

Setting root password in MySQL

Setting root password for MySQL is important. You should not leave your database server unprotected, even if you only plan to use localhost connection.

Before you will start anything, make sure that MySQL is up and running:

sudo systemctl start mysqld

If this is fresh installation of MySQL (MariaDB or older version of MySQL was never installed) , MySQL will generate temporary password.

You can find it by using following command:

grep 'temporary password' /var/log/mysqld.log

You will get random password, for instance something like wG]_xj8tus. This is current password to root account. Keep in mind that this is only temporary password. It will expire, so you need to change it as soon as possible.

In order to change root password I like to use mysqladmin tool. So execute following command:

mysqladmin -u root -pwG]_xj8tus password NEW_STRONG_ROOT_PASSWORD

You need to replace wG]_xj8tus with the password from grepping log file. Note that there is no space after -p argument. Also replace NEW_STRONG_ROOT_PASSWORD with your new root password. Keep in mind, that it needs to be strong password, containing lower and upper case characters, numbers and special characters. MySQL 5.7 comes with plugin that validates password strength. It won't allow you changing password to something that is easy to guess or brute force.

If you just upgrading MySQL/MariaDB, your root password will be the same. You can of course change it with mysqladmin if you wish.

However if you are upgrading from MySQL/MariaDB version prior to 5.7 it's nice to execute following command:

mysql_upgrade -u root -p

Type your root password. It will check all the data and alter tables for 5.7 rules.

You might ask - how about mysql_secure_installation? You can also use this command for setting root password after installation. However when you install fresh version of MySQL 5.7 there is no test database, anonymous users etc. Everything is clean and secured.

Configuration of MySQL 5.7

I like to tune up default MySQL configuration. With 5.7 version, lot of defaults are set in order to provide high security and performance. However there are few things I like to change. Most of the stuff is stored under /etc/my.cnf file. MySQL is reading configuration from there during startup. You can also change it dynamically, but here I will focus only on this file.

Edit the file first:

sudo vi /etc/my.cnf

There will be some defaults, you can leave them as they are. Most probably, all options here will be missing, so you need to add them under [mysqld] section.

Temporary in-memory tables

tmp-table-size=32M
tmp-heap-table-size=32M

I increase the size of temporary in-memory tables created by MySQL. If you are doing lot of advanced GROUP BY queries, you will probably have to increase it. Make sure that you are setting both to the same value. If one will be lower, second one will be limited to lower value as well.

Query caching

Query caching is disabled by default. It caches result sets from most frequent queries to database. It usually give nice performance boost to MySQL server:

query-cache-type=1
query-cache-size=32M

InnoDB buffer

Also another performance boost is creating buffer for InnoDB pools. It will keep the data in RAM, so reads will be much faster. Make sure to not to set these values higher than your RAM limits. It should be large enough to store as much data as possible.

innodb-buffer-pool-instances=1
innodb-buffer-pool-size=128M

Logging slow queries

Slow queries can kill your server. It's nice to know earlier that something is going on. I usually turn this on and check it frequently for such queries. If there are some complicated / not optimal queries, you should fix them as soon as possible.

slow-query-log=1
slow-query-log-file=/var/lib/mysql/localhost-slow-query.log<

TIMESTAMP fields behavior

If you would check MySQL log during startup, you can note that there is a warning regarding TIMESTAMP fields. You can read more about these changes here. If you want to suppress that warning, use following option

explicit-defaults-for-timestamp=1

In order to apply changes, you need to restart MySQL daemon:

sudo systemctl restart mysqld

Few notes about playing with configuration. You must know that it's not easy and it's highly depends on number of tables, queries, inserts and lot of other factors. I usually use two tools. First is MySQLTuner that can get you some insights about what to change in order to gain performance. Usually it's just tuning query cache, and InnoDB buffer size. However practice shows that it doesn't always show correct values. Sometimes you can get slower performance instead.

So I use other tool - Datadog. You can also use New Relic, or any other monitoring tool that generates charts. So I usually change one thing at the time. I'm restarting MySQL server and waiting around 48h to see if it's actually performing better or not. This might seem to be slow approach but it's definitely safe:)

But before you start to playing with your configuration, make sure that you don't have slow queries or large tables that you scan without indexes. Sometimes just adding index will reduce query time. No need to change MySQL configuration files:)

MySQL group

MySQL during installation create it's own group - mysql. Most of the data files, and logs have mysql:mysql ownership. It makes it hard to read the logs when you are non-root user. You need to use sudo to get access to logs. You can make it simpler by adding user to mysql group. When user will be in such group, he can easily read logs. In my case, user is named developer.

sudo usermod -a -G mysql developer

Create databases and users

Probably the most important part from MySQL user is how to create database and users? First login to MySQL with your root account:

mysql -u root -p

And then start with creating databases. You need to enter following query:

CREATE DATABASE database_name_here;

Replace database_name_here with your database name. I like to create name for database from domain. So for instance for blacksaildivision.com, database name would be blacksaildivision. Remember that you can't use the . in database name.

Once you have your database, you should create dedicated user that only has access to this database. Having separate user per database is good approach from security perspective.

In order to create user in database execute following query:

CREATE USER 'blacksaildivision'@'localhost' IDENTIFIED BY 'NotEasyToGuessPassword123^#';

It will create blacksaildivision user that can connect to MySQL server only via localhost. It means that he won't have access from outside. After IDENTIFIED BY you need to type password for user you want to create. Same rules as for root password, it can't be easy to guess.

After user is created it's time to give him access to database you created before. Query goes like this:

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

So you give user full access to blacksaildivision database. .* means that he should have access to all tables in blacksaildivision database. After TO you need to specify user that you created in step before.

To apply privileges, you need to reload them. Fire following query:

FLUSH PRIVILEGES;

And that's it! Create as many databases and users as you wish:) You can test connection to database with new credentials from command line. First exit from current MySQL session and than use following command:

mysql -u blacksaildivision -p blacksaildivision

-u stands for user, -p means that user will connect by using password. Last thing is database name you want to connect to.

Start MySQL on system boot

Last thing is to add MySQL to boot list. So after CentOS will start, MySQL will start as well:

sudo systemctl enable mysqld

Remember that you can use our lamponsteroids project based on Ansible that will automate whole server setup:)

Hi guys!

Today I would like to tell you a bit more about MySQL query logging. It's very useful to preview all queries that goes to your database. Sometimes you don't have to access to direct query that is generated by ORM, but you have to check what's going on? Not a problem. Enable logging in your MySQL service and get all queries.

Problem with MySQL query logging

When using Drupal I can not previewed exact query that goes to database. I checked documentation for any useful function that can print full SQL query but I didn't find anything like that. So after further investigation and checking the code I get the PDO method. So yes, now I can print SQL query but without parameters... You can get queries like:

SELECT * FROM table_name WHERE name LIKE :name

or very similar. Unfortunately I needed full query with binded parameters. Sure, I could write some short function that will bind all parameters for me, it would be easy. But sometimes there goes very large query with hell of a lot of parameters including arrays etc. I needed something more than that.

I looked for specialized software etc but hey! Why not use the database build-in logging mechanism? It can catch all the database queries that are executed to your database. It was exactly what I needed.

How to turn on MySQL query log

OK, case is very simple, basically there are 3 ways of turning on the MySQL query log in your installation.

Method 1

You can turn on log on MySQL start/restart via command:

mysqld --general_log=1 --general_log_file=my_logs.txt

First option will turn on query log, second is path to the file where log will be saved.

Depend on the version of MySQL you're using: You should use --log parameter. More informations here: http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Method 2

Method 2 is very similar to method 1 above. It's hard to remember that every time you're run MySQL you should pass logging parameters if You want to turn on MySQL query log. It's easier if you put these settings into MySQL configuration file.

Open Your configuration file and append these lines in [mysqld] section:

general_log=1
general_log_file=my_logs.txt

Have in mind version of your mysql installation. Check method 1 with --log parameter. After saving changes in mysql configuration file restart MySQL service to enable logging mechanism.

*Method 3

My favorite method:) If you don't want to restart MySQL server or you want to turn logging only for single page refresh than turn off logging it's your method.

Execute queries below:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

You will find all logs in mysql.general_log table. It's pretty useful because you can perform select operation on this table and you don't have to bother searching in text file. For me it's best method for good MySQL query log:)

If you want to log queries into file except of database table execute these queries:

SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = 'my_logs.txt';
SET GLOBAL general_log = 'ON';

You can execute these queries in runtime! There's no need to restart your MySQL service.

You can read more about setting global configuration options here:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

Viewing MySQL query log

Log file is very easy to read. It's text file so you can open it in any editor you prefer. Open your log file, you will find there 4 columns:

Time - date time of query execution in yymmdd hh:mm:ss format.
Id - Thread Id of the connected client
Command - Command type, usually Query
Argument - Query body executed on MySQL body

Important info

OK, so now you have the list of the queries that are executed on database. It's great to check the full query executed on database. It's really helpful especially when You don't have direct access to generated queries for example like in Drupal.

But don't forget to turn off your logging. When You have many queries executed on your database it will make query log very big and you can run out of space on your disk. Second thing is lot of write operation when logging is turned off. So it's nice to turn logging on only for debug purposes and turn off logging on production environments.

Tag Archives: mysql logs

How to install and configure latest version of MySQL on CentOS?