Tag Archives: mysql

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

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

MySQL insert or update using ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE - introduction

This is really simple and easy in use. Let's take a look on some really simple example. Let's assume that we want to collect number of views for the article in our website.

It will be as simple as possible, I just want to show You how to use ON DUPLICATE KEY UPDATE:)

CREATE TABLE `stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article_id` int(11) NOT NULL,
  `views_count` int(11) NOT NULL DEFAULT '1',
  `created` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

We create simple table with 4 columns:

id - auto-incremented unique ID number
article_id - ID of our article
views_count - number of views of the article
created - when stats info was created. To show You more advanced usage of ON DUPLICATE KEY UPDATE lets save only the date without time.

So when we load the page we want to insert row into table if it's not exists. Otherwise we want to increase views_count field by 1. As I wrote before, we can do two queries. SELECT and INSERT or UPDATE. But let's use ON DUPLICATE KEY UPDATE.

First of all we have to have UNIQUE key if we want to use ON DUPLICATE KEY UPDATE. It's a must, we have to have some key that we want to perform check on. So let's think, which field we want to be unique? It's obviously article_id.

Let's add the key then by executing this query:

ALTER TABLE  `stats` ADD UNIQUE uk_article_id(
`article_id`
);

After that query we will have UNIQUE key named uk_article_id. MySQL will generate name of the key by itself, but good practice is to name Your key. It will be simple to remove it for instance.

Alright, we have our unique key. So let's create really simple query that will insert the data to database or update the existing one.

INSERT INTO stats (article_id, created) VALUES 
	(12, CURRENT_DATE())
ON DUPLICATE KEY UPDATE
	views_count = views_count + 1

If we execute such query for the first time we will have single record with our article_id = 12, views_count = 1 (1 is default value, if not given), and current date.

If we execute this query several times we will still have single record in database but views_count value will be increased. Really simple, isn't it?

Multiple keys in ON DUPLICATE KEY UPDATE

Let's upgrade our concept a little bit. Let's say that we want to have daily statistics for each article. We can't use our example as there is only creation date of the first insert. How to solve this situation? We have to have separate rows for each day.

First of all we have to delete our previous key as it won't be needed anymore. If we named our index we can simply execute this query to remove the unique key:

DROP INDEX uk_article_id ON stats

If we didn't name our key, we don't know how to delete it. Fastest option is to list all keys in our table, like that:

SHOW INDEX FROM stats

This query will list all keys in our table. In column Key_name there will be the name of the key we want to delete.

OK, we are ready to add new key to the table with article_id and created columns. To add UNIQUE key for two or more columns we have to execute such query:

ALTER TABLE  `stats` ADD UNIQUE uk_article_id(
`article_id`, `created`
);

Now, if we execute our insert or update query, we will see that we can add multiple article_id rows with the same value, but date in created field must be unique. We won't have two exactly same rows with article_id and created date. For testing purposes You can change CURRENT_DATE() function to the particular date, for instance "2012-12-12" to see how it really works.

Conditional ON DUPLICATE KEY UPDATE with IF

Sometimes there might be need to make additional IF on update process. For instance we want to update our record only if another passed column has different value or something. Let's take a look at this example:

INSERT INTO stats (article_id, created) VALUES 
	(12, CURRENT_DATE())
ON DUPLICATE KEY UPDATE
	views_count = IF(VALUES(article_id) = 12, views_count + 2, views_count + 1) /*,
	additional_field = additional_field + 1,
	next_field = IF(article_id = 1, 1, 0)

Let's say that we want to "cheat" a little bit and increase views_count by two on article_id = 12. We have to add IF statement. It has three parts, condition, value if true, value if false.

In condition we use VALUES() function, which returns given value for the field. Which mean that VALUES(article_id) will return 12, because we passed this value in INSERT statement above. So this condition will be true and views_count will be increased by 2. We can pass multiple IF statements both with update multiple values. Code in commented section have some simple examples.

Keep in mind, that on first insert, when there's no record in the table, insert will be performed. It means that all code after ON DUPLICATE KEY UPDATE won't be executed. For instance in our example when we don't have such values in database, after this query we will have row with article_id = 12 and views_count = 1 (not 2).

Batch insert with ON DUPLICATE KEY

There is also option to make batch insert to database using ON DUPLICATE KEY UPDATE. It will execute update for each given row if there is duplication. Here is some simple example:

INSERT INTO stats (article_id, created) VALUES 
	(13, CURRENT_DATE()),
	(14, CURRENT_DATE()),
	(15, CURRENT_DATE())
ON DUPLICATE KEY UPDATE
	views_count = views_count + 1

If You want read a little bit more about ON DUPLICATE KEY and rest of the stuff I wrote about here are some external links to MySQL manual:

Database backup and restore (export and import) with mysqldump

Database backup

Ok, lets look at basic example:

mysqldump -u user -ppassword -h host database_name > database_dump_file.sql

Where:

user - database user
password - database user password (there's no space between -p and your password!)
host - host where your database is located (by default it's localhost, so this parameter is optionally, use it when you want to export database from external host)
database_name - database name you want to backup
database_dump_file.sql - file with exported data

Practice:

mysqldump -u astaz3l -psupersecretpassword1234 wp_database > 2013_06_18.sql

After some time (depends on the database size) in 2013_06_18.sql file we will have complete database dump from wp_database. I didn't use -h parameter as it's localhost.

Except of -u, -p, -h we can use more parameters.

--no-data will export only structures(tables, fields, etc...) without data
--no-create-info will export only data without structure. Be careful when you will use this parameter. You must be sure, that you have exactly the same structure in database when you want to import such dump.
--all-databases pretty useful option when you want to backup all databases

More options you can find in mysqldump manual here:
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Database restore

Ok, now we know how to export database. How to import dumped database? Using mysql command.

mysql -u user -ppassword -h host database_name < database_dump_file.sql

It's very similar to making database backup, isn't it? Only difference is tool name and < sing. Parameters are exactly the same as during export. Real life usage:

mysql -u astaz3l -psupersecretpassword1234 wp_database < 2013_06_18.sql

It is possible to restore database with mysqldump file and phpMyAdmin. Simply choose import tab from phpMyAdmin and use exported file.