MySQL | Any IT here? Help Me!

Using xtrabackup to make fast MySQL backups – backup and restore

Percona provides a really interesting tool for MySQL backups! It works on a live running MySQL server by copying the MySQL binary files from the data directory and because the tool knows how the engines work (InnoDB, MyISAM, and so on) it can make point-in-time consistent MySQL data files. Of course, using this tool on a database with InnoDB tables only is the best case, because no write lock will be used.

There are two main tasks when making a backup and one, which is not mandatory:

Execute xtrabackup with –backup option to copy the MySQL data files and additional information for the tool
Execute xtrabackup with –prepare option to prepare the MySQL (InnoDB) files ready for use of a MySQL server. The files are consistented, i.e. the whole backup is consistent despite the copy of the different files that happened at different times.
Execute xtrabackup with –prepare option again to further prepare the MySQL (InnoDB) files ready for use of a MySQL server. Additional preparation such as InnoDB log files and more. This step is not mandatory and it may be skipped because the MySQL server, which uses the data files, will create the InnoDB log files.

The directory with MySQL copied files contains not only the MySQL files but additional information plus my.cnf (MySQL current configuration) backup. The backup files may be prepared (and restore) in a different server than the original, on which the backup was made.

xtrabackup may enable compressing on-the-fly when copying the binary files with the option –compress but the steps to use (restore) the backup in a new server are different – additional step to decompress before prepare. So the above steps become:

xtrabackup –backup –compress – copy and compress on-the-fly.
xtrabackup –decompress – decompress the backup files
xtrabackup –prepare – make point-in-time consistent MySQL data files, which are ready for a MySQL server.
xtrabackup –prepare – additional preparation to make the start up of the MySQL server with those files faster.

And here is a real-world example:

STEP 1) Install the xtrabackup utility

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-24 qpress

The percona-xtrabackup-24 provides the percona xtrabackup tool for backup of MySQL 5.1, 5.5, 5.6 and 5.7 servers, as well as Percona Server for MySQL with XtraDB. Install percona-xtrabackup-80 for use with MySQL 8 and later.
The whole output is included in the Bonus 3 section below.

STEP 2) Make backup

Make the backup. The datadir of the MySQL server is needed and a new direcotry for the backup files. The MySQL server is running and serving requests.

[root@srv ~]# xtrabackup --backup --slave-info --datadir=/var/lib/mysql/ --target-dir=/mnt/backups/sql-xtrabackup/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin --server-id=1 --open_files_limit=5000 --innodb_buffer_pool_size=256M --innodb_log_buffer_size=32M --innodb_log_files_in_group=2 --innodb_flush_log_at_trx_commit=0 --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT --datadir=/var/lib/mysql/ 
xtrabackup: recognized client arguments: --password=* --backup=1 --slave-info=1 --target-dir=/mnt/backups/sql-xtrabackup/ 
200919 14:35:11  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: YES).
200919 14:35:11  version_check Connected to MySQL server
200919 14:35:11  version_check Executing a version check against the server...
200919 14:35:11  version_check Done.
200919 14:35:11 Connecting to MySQL server host: localhost, user: not set, password: set, port: not set, socket: not set
Using server version 5.7.31-log
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 5000, set to 5000
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
200919 14:35:11 >> log scanned up to (1135932912)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 34 for mywordpress/wp_users, old maximum was 0
200919 14:35:12 [01] Copying ./ibdata1 to /mnt/backups/sql-xtrabackup/ibdata1
200919 14:35:12 >> log scanned up to (1135932912)
200919 14:35:13 >> log scanned up to (1135932912)
200919 14:35:14 [01]        ...done
200919 14:35:14 >> log scanned up to (1135932912)
200919 14:35:15 >> log scanned up to (1135932912)
200919 14:35:16 [01] Copying ./mywordpress/wp_users.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_users.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 [01] Copying ./mywordpress/wp_gglcptch_whitelist.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_gglcptch_whitelist.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 [01] Copying ./mywordpress/wp_usermeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_usermeta.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 >> log scanned up to (1135932912)
200919 14:35:17 [01] Copying ./mywordpress/wp_postmeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_postmeta.ibd
200919 14:35:17 [01]        ...done
200919 14:35:17 [01] Copying ./mywordpress/wp_posts.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_posts.ibd
200919 14:35:17 >> log scanned up to (1135932912)
200919 14:35:18 >> log scanned up to (1135932912)
200919 14:35:19 [01]        ...done
200919 14:35:19 >> log scanned up to (1135932912)
.....
.....
200919 14:36:30 [01] Copying ./mysql/columns_priv.MYD to /mnt/backups/sql-xtrabackup/mysql/columns_priv.MYD
200919 14:36:30 [01]        ...done
200919 14:36:30 >> log scanned up to (1135933567)
200919 14:36:31 [01] Copying ./mysql/proxies_priv.frm to /mnt/backups/sql-xtrabackup/mysql/proxies_priv.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 [01] Copying ./mysql/help_category.frm to /mnt/backups/sql-xtrabackup/mysql/help_category.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 [01] Copying ./mysql/proc.frm to /mnt/backups/sql-xtrabackup/mysql/proc.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 Finished backing up non-InnoDB tables and files
200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_slave_info
200919 14:36:31 [00]        ...done
200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_binlog_info
200919 14:36:31 [00]        ...done
200919 14:36:31 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1135933558'
xtrabackup: Stopping log copying thread.
.200919 14:36:31 >> log scanned up to (1135933567)

200919 14:36:32 Executing UNLOCK TABLES
200919 14:36:32 All tables unlocked
200919 14:36:32 [00] Copying ib_buffer_pool to /mnt/backups/sql-xtrabackup/ib_buffer_pool
200919 14:36:32 [00]        ...done
200919 14:36:32 Backup created in directory '/mnt/backups/sql-xtrabackup/'
MySQL binlog position: filename 'srv-bin.000001', position '1004'
200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/backup-my.cnf
200919 14:36:32 [00]        ...done
200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_info
200919 14:36:32 [00]        ...done
xtrabackup: Transaction log of lsn (1135932903) to (1135933567) was copied.
200919 14:36:33 completed OK!

In the end, there must be written: “completed OK!”.
The whole output of the command is included in the Bonus 1 section below. An example with compress option enabled is in Bonus 2 section below.
Here is what contains the backup diectory:
Keep on reading!

edit mysql options in docker (or docker-compose) mysql

Modifying the default options for the docker (podman) MySQL server is essential. The default MySQL options are too conservative and even for simple (automation?) tests the options could be .
For example, modifying only one or two of the default InnoDB configuration options may lead to boosting multiple times faster execution of SQL queries and the related automation tests.

Here are three simple ways to modify the (default or current) MySQL my.cnf configuration options:

Command-line arguments. All MySQL configuration options could be overriden by passing them in the command line of mysqld binary. The format is:
```
--variable-name=value
```
and the variable names could be obtained by
```
mysqld --verbose --help
```
and for the live configuration options:
```
mysqladmin variables
```
Options in a additional configuration file, which will be included in the main configuration. The options in /etc/mysql/conf.d/config-file.cnftake precedence.
Replacing the default my.cnf configuration file – /etc/mysql/my.cnf.

Check out also the official page – https://hub.docker.com/_/mysql.
Under CentOS 8 docker is replaced by podman and just replace the docker with podman in all of the commands below.

OPTION 1) Command-line arguments.

This is the simplest way of modifying the default my.cnf (the one, which comes with the docker image or this in the current docker image file). It is fast and easy to use and change, just a little bit of much writing in the command-line. As mentioned above all MySQL options could be changed by a command-line argument to the mysqld binary. For example:

mysqld --innodb_buffer_pool_size=1024M

It will start MySQL server with variable innodb_buffer_pool_size set to 1G. Translating it to (for multiple options just add them at the end of the command):

docker run

root@srv ~ # docker run --name my-mysql -v /var/lib/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=111111 \
-d mysql:8 \
--innodb_buffer_pool_size=1024M \
--innodb_read_io_threads=4 \
--innodb_flush_log_at_trx_commit=2 \
--innodb_flush_method=O_DIRECT
1bb7f415ab03b8bfd76d1cf268454e3c519c52dc383b1eb85024e506f1d04dea
root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
| innodb_buffer_pool_size                                  | 1073741824

docker-compose:

# Docker MySQL arguments example
version: '3.1'

services:

  db:
    image: mysql:8
    command: --default-authentication-plugin=mysql_native_password --innodb_buffer_pool_size=1024M --innodb_read_io_threads=4 --innodb_flush_log_at_trx_commit=2 --innodb_flush_method=O_DIRECT
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 111111
    volumes:
     - /var/lib/mysql_data:/var/lib/mysql
    ports:
      - "3306:3306"

Here is how to run it (the above text file should be named docker-compose.yml and the file should be in the current directory when executing the command below):

root@srv ~ # docker-compose up
Creating network "docker-compose-mysql_default" with the default driver
Creating my-mysql ... done
Attaching to my-mysql
my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
my-mysql | 2020-06-16T09:45:36.293747Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
my-mysql | 2020-06-16T09:45:36.293906Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
my-mysql | 2020-06-16T09:45:36.307654Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
my-mysql | 2020-06-16T09:45:36.942424Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
my-mysql | 2020-06-16T09:45:37.136537Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
my-mysql | 2020-06-16T09:45:37.279733Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
my-mysql | 2020-06-16T09:45:37.306693Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
my-mysql | 2020-06-16T09:45:37.353358Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

And check the option:

root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
| innodb_buffer_pool_size                                  | 1073741824

OPTION 2) Options in a additional configuration file.

Create a MySQL option file with name config-file.cnf:

[mysqld]
innodb_buffer_pool_size=1024M
innodb_read_io_threads=4
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT

docker run

The source path must be absolute path!

docker run --name my-mysql \
-v /var/lib/mysql_data:/var/lib/mysql \
-v /etc/mysql/docker-instances/config-file.cnf:/etc/mysql/conf.d/config-file.cnf \
-e MYSQL_ROOT_PASSWORD=111111 \
-d mysql:8

docker-compose
The source path may not be absolute path.

# Docker MySQL arguments example
version: '3.1'

services:

  db:
    container_name: my-mysql
    image: mysql:8
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 111111
    volumes:
     - /var/lib/mysql_data:/var/lib/mysql
     - ./config-file.cnf:/etc/mysql/conf.d/config-file.cnf
    ports:
      - "3306:3306"

OPTION 3) Replacing the default my.cnf configuration file.

Add the modified options to a my.cnf template file and map it to the container on /etc/mysql/my.cnf. When overwriting the main MySQL option file – my.cnf you may map the whole /etc/mysql directory (just replace /etc/mysql/my.cnf with /etc/mysql below), too. The source file (or directory) may be any file (or directory) not the /etc/mysql/my.cnf (or /etc/mysql)

docker run:
The source path must be absolute path.
```
docker run --name my-mysql \
-v /var/lib/mysql_data:/var/lib/mysql \
-v /etc/mysql/my.cnf:/etc/mysql/my.cnf \
-e MYSQL_ROOT_PASSWORD=111111 \
--publish 3306:3306 \
-d mysql:8
```
Note: here a new option “–publish 3306:3306” is included to show how to map the ports out of the container like all the examples with the docker-compose here.

docker-compose:
The source path may not be absolute path, but the current directory.

# Use root/example as user/password credentials
version: '3.1'

services:

  db:
    container_name: my-mysql
    image: mysql:8
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 111111
    volumes:
     - /var/lib/mysql_data:/var/lib/mysql
     - ./mysql/my.cnf:/etc/mysql/my.cnf
    ports:
      - "3306:3306"

docker mysql – Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!

Pulling the official MySQL image from the docker registry https://hub.docker.com/r/mysql/mysql-server to start a MySQL instance with your configuration file (and MySQL binary files). Adding the “–volume” option for the configuration directory (or file) and MySQL binary files and you stumble on the error:

2019-12-03 01:13:38 0 [Note] mysqld (mysqld 5.6.46-log) starting as process 67 ...
2019-12-03 01:13:38 67 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2019-12-03 01:13:38 67 [ERROR] Aborting

2019-12-03 01:13:38 67 [Note] Binlog end
2019-12-03 01:13:38 67 [Note] mysqld: Shutdown complete

Apparently, the server option is not configured to run properly as a root user and you do not want to run it, but why it keeps insisting to run it as root?

Because of the entry point script will execute only “mysqld” as a command, which expects to have a “user” option in the “[mysqld]” section of your my.cnf configuration file!

Do not miss the user option in my.cnf! This is how the MySQL server will be using the “mysql” username not the root!

user=mysql

Typical error, because it is not so common to include the username in my.cnf configuration file of the mysqld process to run as. If you use the official docker MySQL image to create your configuration file you would not encounter the above error, but if you use an existing (probably old and from non virtualized environment) my.cnf make sure to include the username, which should be used to run the mysqld process as.

Here is our command to execute the container:

docker run --privileged -d -v /mnt/storage/docker/mysql-slave/files:/var/lib/mysql -v /mnt/storage/docker/mysql-slave/etc/my.cnf:/etc/my.cnf mysql/mysql-server:5.6

MariaDB/MySQL replication error – Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos

When in aggressive parallel mode MariaDB/MySQL replication could fail with:

Last_Errno: 1942
Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-46158188501' for key 'PRIMARY'

This table is used for tracking the replication process and you might probably just do:

STOP/START SLAVE i.e. restart the replication and it would continue without errors.

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

Optimistic or aggressive mode runs conflicting transactions in parallel and it sometimes happens to roll back. In our case probably something happened and the rollback failed and STOP/START saved the replication.

* Additional thoughts

If you try STOP/START and you get the same error, probably it worth trying truncating the table “mysql.gtid_slave_pos” if you do not use GTID Replication feature (the “show slave status” says “Using_Gtid: No”). And even if you use “Using_Gtid: No” you could probably always stop the replication, “change master” to use the old style and start again? Probably switching off the aggressive mode might help, too!
Keep on reading!

The impact of enabling MySQL sync_binlog – really high disk IO

If you enable this feature in your MySQL you could

increase your disk IO time and write by 8-10x times.

Generally, this feature could save your replication scheme if a power failure occurs or OS crash and it could guarantee that no transaction is lost from the binary log. When enabled the binary log is synchronized on disk before transactions are committed. You can check the manual here: https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog and it also says there could be a great impact on disk writes but how many?
So here are two setups:

SETUP 1) 2 x 3T hard drives TOSHIBA DT01ACA300 in software RAID1

The impact of setting the sync_binlog=1 is 8-10 times the IO time and IO writes. Here is images of several hours of sync_binlog=1 and then we disabled it online:

SCREENSHOT 1) Enable the binary log synchronization with sync_binlog=1.

As you can see the increase in the disk IO time and disk write IOPS are significant – somewhere between 5 and 6 times more! The load is not increased more than 1.5x as normal, but it should be noted the server is off-peak and it has plenty of RAM 32G. Still mush load when some other IO appears.

main menu — Set MySQL sync_binlog=1 in a software raid of two hard drives

SCREENSHOT 2) Disabling the binary log synchronization with sync_binlog=0.

The decrease of the disk IO time and disk write IOPS are significant – somewhere between 5 and 6 times more! Everything back to normal.

SCREENSHOT 3) Enable the binary log synchronization with sync_binlog=1.

As you can see the increase in the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more! The load is not increased, but it should be noted the server is off-peak and it has plenty of RAM 192G. Still mush load when some other IO appears.

SET GLOBAL sync_binlog=0;

SETUP 2) 2 x 960G SSD SAMSUNG SM863 in software RAID1

The impact of setting the sync_binlog=1 is also 8-10 times the IO time and IO writes. Here is images of several hours of sync_binlog=1 and then we disabled it online:

SCREENSHOT 4) Disabling the binary log synchronization with sync_binlog=0.

The decrease of the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more and even more! Everything back to normal.

SCREENSHOT 5) Enable the binary log synchronization with sync_binlog=1.

Just the period of the graphs are bigger. As you can see the increase in the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more! The load is not increased, but it should be noted the server is off-peak and it has plenty of RAM 192G. Still mush load when some other IO appears.

SCREENSHOT 6) Disabling the binary log synchronization with sync_binlog=0.

Just the period of the graphs are bigger. The decrease of the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more and even more! Everything back to normal.

BONUS – MySQL changed the default value from 0 (disabled) to 1 (enabled) from 5.7 (in fact MySQL >= 5.7.7).

SO BE CAREFUL now when upgrading from older versions like MySQL 5.1, 5.5, 5.6 – you would probably need to disable it in the MySQL configuration file my.cnf.

Upgrade MySQL 5.6 to 5.7 what problems to expect with old my.cnf configuration file

Finally, we do not have any more MySQL 5.6 servers. We upgraded our last part of the system with MySQL 5.6 to 5.7. In our opinion, this upgrade is one of the major referred to MySQL configuration file my.cnf – multiple deprecated directives are removed in this new 5.7 version so when upgrading you should remove them before restarting or starting the new version if you want to have running MySQL server instance.
Keep in mind our my.cnf are old, they are created with MySQL 5.0 and they are edited in every upgrade to a new version (5.0 to 5.1, 5.1 to 5.5, and 5.5 to 5.6), and when we needed a specific optimization for our workload. And this is only for our configuration, there surely are more deprecated/removed variables in the new version. Here is a good starting point – https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html This article is not how to upgrade your old MySQL 5.6 to the new MySQL 5.7 it shows what problems you might have after you upgrade MySQL 5.6 to the new MySQL 5.7.
There are two parts of this article:

Removed variables, which were perfectly OK in the old version 5.6
Changed default value of variables, which impact greatly the IO or the the SQL execution

The error messages are included, too.

PART 1) Removed variables.

Some MySQL variables first get deprecated and then removed in later versions (some are just renamed) and if they are contained in the my.cnf configuration file your server will not start up at all. The MySQL log shows that the server starts and then throws an error about “unknown variable” and starts a shutdown procedure. So you end up without a database server and it is important to remove them from the configuration or find the new name of a renamed one.

2019-02-26T09:50:12.612950Z 0 [ERROR] unknown variable 'key_buffer=512M'
2019-02-26T09:50:36.361870Z 0 [ERROR] unknown variable 'thread_concurrency=6'
2019-02-26T09:51:17.658546Z 0 [ERROR] unknown variable 'thread_cache=10'
2019-02-26T09:51:32.473210Z 0 [ERROR] unknown variable 'innodb_additional_mem_pool_size=256M'

All four

key_buffer, thread_concurrency, thread_cache, innodb_additional_mem_pool_size

MySQL variables were removed and your server won’t start up if they are contained in the configuration. The “key_buffer” has been renamed to “key_buffer_size“ so replace it with key_buffer_size in your my.cnf. It’s important to replace it, because commenting it out would activate the default value and in this case 8M key_buffer_size, which is pretty low (in fact almost all default values of the MySQL variables are really low and it is a problem and a topic of discussions in many forums).
The “thread_cache” also renamed long ago to “thread_cache_size“, so replace it with thread_cache_size.
thread_concurrency and innodb_additional_mem_pool_size were removed long ago they first stopped doing anything and with this version, they removed the variables. As you can see old configuration files could carry on many old names over the years.

The important thing here is you must rename the ones, which got renamed and remove the ones, which got removed, because your server is not going to start up with them in the configuration.

PART 2) Changed default value

Some default values of MySQL variables got changed and if you have not included it in the my.cnf configuration you might be really surprised how big an impact they have on the IO or even on the behavior of the SQL statements.

2.1) Our first MySQL variables is

sync_binlog

– the default value was “0” (deactivated synchronization) and now it is “1” (bin log synchronization). This could greatly impact the performance of your MySQL database server with like 8-10 times more writes and IO disk wait time (really!!!) – you can see it here: (coming soon). So if you haven’t used this variable before you should put it in your my.cnf configuration for sure (in [mysqld] section):

sync_binlog=0

do not need to restart the server, just put in the my.cnf configuration file and open a mysql root console and execute:

SET GLOBAL sync_binlog=0;

it can be live changed.

2.2) And the second example is

sql_mode

– the default value was “NO_ENGINE_SUBSTITUTION” and now it is “ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”, which is a pretty substantial difference. You can lose INSERTs and UPDATEs easily because a much strict mode is activated by default.
For example, with an INSERT if you do not set the value to a field, which column does not have a default value (yes, it is wrong, but it was OK before), your insert won’t be executed and you’ll get an error (or just a FALSE after execution of your query like with PHP PDO). Here is the MySQL explanation:

A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition.

And more in https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict
So if you haven’t used this variable before you should put it in your my.cnf configuration for sure (in [mysqld] section):

sql_mode=NO_ENGINE_SUBSTITUTION

do not need to restart the server, just put in the my.cnf configuration file and open a MySQL root console and execute:

SET GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION'

mysql – Error ‘Your password does not satisfy the current policy requirements’ or zero length mysql password

We got this error when granting permissions for one of our new slave server (it could be for an ordinary MySQL server, too):

Error 'Your password does not satisfy the current policy requirements' on query. Default database: ''. Query: 'GRANT REPLICATION SLAVE ON *.* TO 'reusr'@'127.0.01''

It appeared that MySQL has activated by default a password checking plugin and our password in the GRANT (or SET PASSWORD) option didn’t meet the requirements.
So here is what you can do:

OPTION 1) Lower the password policy level

Check the policy level and lower it if it is MEDIUM or HIGH (they are there options LOW=0, MEDIUM=1 the default and HIGH=2). The policy level controls how to check and what is involved in the complexity algorithm for the passwords. More details here – https://dev.mysql.com/doc/refman/5.7/en/validate-password-options-variables.html#sysvar_validate_password_policy. Here is what you have:

[myuser@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
....
....
mysql> SHOW VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

So set the validate_password_policy=0 and try again your query:

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

If you still get the error your password is lower than the validate_password_length (=8 by default) so you need to change it at last to 8 characters. But what if you what zero password (or with 1,2,3 characters)? Setting validate_password_length to 0 won’t work, because there is a hard limit to 4, so you cannot set it to 0 event the set query is not reporting error when using 0 with validate_password_length.

You should uninstall the plugin.

OPTION 2) Uninstall the MySQL Validation Plugin

You can uninstall the validation plugin on-the-fly in a working server without restarting or reloading and then you can set whatever password you like.
Here is how to do it:

[myuser@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
....
....
mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW VARIABLES LIKE 'validate%';
Empty set (0.01 sec)

As you can see no “validate_password” variables are available anymore! Now set your password.
But there is a catch, if you have started the server with “–validate-password=FORCE_PLUS_PERMANENT” (you can check it with “ps axuf|grep mysqld” in the command line) you won’t be able to uninstall the plugin live even with the root MySQL user. So at the end if you do not have root permissions to restart the MySQL service without this option it might be better to change your password or skip the query if it is received by the slave in the MySQL replication bin log.
You can install the plugin again with:

[myuser@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
....
....
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0.00 sec)

And it will be available over restarts, too, because it is registered in “mysql.plugin” table.

mysql – Error ‘Column count of mysql.user is wrong. Expected 45, found 43. The table is probably corrupted’ on query.

If you

upgraded your MySQL server (from 5.6 to 5.7 or above)

imported a MySQL dump SQL file from older version

than your current server you may encounter when granting permissions to a user:

Error 'Column count of mysql.user is wrong. Expected 45, found 43. The table is probably corrupted' on query. Default database: ''. Query: 'GRANT REPLICATION SLAVE ON *.* TO 'replusr'@'144.76.156.182''

Do not panic probably it is not corrupted just continue reading.

There is the simple fix, just

execute mysql_upgrade

It will automatically detect what to upgrade and it will upgrade it:

[myuser@mysql1 ~]# screen -R upgrade
[myuser@mysql1 ~]# mysql_upgrade 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
phpmyadmin.pma__bookmark                           OK
phpmyadmin.pma__central_columns                    OK
phpmyadmin.pma__column_info                        OK
phpmyadmin.pma__designer_settings                  OK
phpmyadmin.pma__export_templates                   OK
phpmyadmin.pma__favorite                           OK
phpmyadmin.pma__history                            OK
phpmyadmin.pma__navigationhiding                   OK
phpmyadmin.pma__pdf_pages                          OK
phpmyadmin.pma__recent                             OK
phpmyadmin.pma__relation                           OK
phpmyadmin.pma__savedsearches                      OK
phpmyadmin.pma__table_coords                       OK
phpmyadmin.pma__table_info                         OK
phpmyadmin.pma__table_uiprefs                      OK
phpmyadmin.pma__tracking                           OK
phpmyadmin.pma__userconfig                         OK
phpmyadmin.pma__usergroups                         OK
phpmyadmin.pma__users                              OK
sys.sys_config                                     OK
db1.access                                         OK
db1.users                                          OK
db1.objects                                        OK
db1.isp                                            OK
db1.desc                                           OK
Upgrade process completed successfully.
Checking if update is needed.

It works when the server is up and running and it is a good idea to execute the command in a screen.
It does not need to be logged as root, but mysql_upgrade does need to have the root MySQL password. In the example above it did not asked for password, because we have it in ~/.my.cnf file.

Just to note you might upgraded a long before this error to appear!

If you do not use a certain functionality you could live up happily with the old mysql.user scheme (and all old mysql.* tables). In our case we upgraded one of our slaves and several days after when a grant command on the master was issued the replication just stopped with this error! Of course, if someone were used the command in our slave the error would have appeared there sooner.
We also had case where old MySQL SQL dump file (5.6) was imported in a newer MySQL server 5.7 and there had been no issues for weeks till the GRANT command.

perror

Th error code is 1805.

[myuser@mysql1 ~]# perror 1805
MySQL error code 1805 (ER_COL_COUNT_DOESNT_MATCH_CORRUPTED_V2): Column count of %s.%s is wrong. Expected %d, found %d. The table is probably corrupted

Replication hangs with Relay_Master_Log_File mysql-bin.999999 -wrong variables in mariadb (MySQL) show slave status

Several days after another .999999 hang out – mariadb (MySQL) stopped flushing relay-bin log after mysqld-relay-bin.999999, the monitoring of one of the slaves got critical with replication delayed. Then several hours it kept delaying without any apparent reason.
The slave status was weird, the

“Relay_Master_Log_File: mysql-bin.999999” and
“Exec_Master_Log_Pos: 104858214” were not changing,

which is essential for the slave server! In fact without these two values we do not know the real position, which is executed in the slave! And we cannot recover the slave if anything happened.
Keep on reading!

mariadb (MySQL) stopped flushing relay-bin log after mysqld-relay-bin.999999

We have a pretty big MariaDB master-slave setup, which is really under heavy load and suddenly one of our slaves stopped purging relay logs! And soon the free space got less and it was clear we were going to run out of space for the data partition.

Here is the problem we have only 49G left free space for our mysql datadir “/var/lib/mysql-datadir”. You can see the replication is OK and running (it’s not behind – “Seconds_Behind_Master: 0”), the

Relay_Log_File says mysqld-relay-bin.999999

but it is not true! You can see there are tones of more relay logs after “mysqld-relay-bin.999999” – in fact the current MySQL relay binlog file is “mysqld-relay-bin.1071413” (look below we included a listing of the datadir).

And “FLUSH RELAY LOGS” does not help, at all!

It just not freed any byte on the partition and no files were removed after “mysqld-relay-bin.999999” as before.
Keep on reading!