transaction check error – mysql-community-libs-compat conflicts with file from package compat-mysql51

While upgrading MySQL server on centos 6 from 5.5 to 5.7 you may encounter following error:

Transaction Check Error:
  file /usr/lib64/mysql/libmysqlclient.so.16.0.0 from install of mysql-community-libs-compat-5.7.11-1.el6.x86_64 conflicts with file from package compat-mysql51-5.1.54-1.el6.remi.x86_64
  file /usr/lib64/mysql/libmysqlclient_r.so.16.0.0 from install of mysql-community-libs-compat-5.7.11-1.el6.x86_64 conflicts with file from package compat-mysql51-5.1.54-1.el6.remi.x86_64

It can be resolved by running following yum transaction:

$ yum shell
> remove compat-mysql51
> install mysql-community-libs-compat
> run

This will update affected mysql libraries to newer version without breaking any dependencies.

Centos 7 systemctl tips and tricks

1. List all services enabled/disabled on boot (ex chkconfig –list)

systemctl list-unit-files

2. Enable HTTPD + Mysql service on system boot:

systemctl enable httpd
systemctl enable mariadb

3. Reverting to iptables from firewalld

systemctl stop firewalld
systemctl mask firewalld

yum install iptables-services

systemctl enable iptables
systemctl start iptables

# to save iptables rules use
service iptables save

MySQL Replication configuration example

Database replication will synchronize databases between two different servers.
Should any record change on primary server, it will be automatically sent to secondary (slave) server.

  • Primary server
  • Edit MySQL config file /etc/my.cnf

    server-id=1
    log_bin=/var/log/mysql/mysql-bin.log
    

    log_bin – specifies the location of MySQL binary log, which is used for replication.

    You can limit the binary logging to only one database by specifying parameter binlog_do_db=database .
    However this is not recommended, see (dev.mysql.com )

    Save changes and restart MySQL server.

    service mysql restart

    Now we should grant privileges on slave user which will be used for replcation.

    mysql -u root -p
    GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'pass';
    FLUSH PRIVILEGES;
    

    Now lock and dump DB to the file to transfer it to the second server.

    FLUSH TABLES READ LOCK;
    SHOW MASTER STATUS;
    

    Save “Position” field.

    If you do something in this window database will automatic unlock,
    you should open new window and do it there:

    mysqldump -u root -p -f db1 > db1.sql

    db1 – name of database, that you want to dump

    Unlock database.

    UNLOCK TABLES;

    Now it’s time to configure secondary server.

  • Secondary server
  • CREATE DATABASE db1;

    Import dump from first server:

    mysql -u root -p -f db1 < db1.sql

    Edit MySQL config file /etc/my.cnf

    server-id=2
    relay-log=/var/log/mysql/mysql-relay-bin.log
    log_bin=/var/log/mysql/mysql-bin.log
    

    Restart MySQL:

    service mysql restart

    Server configured and ready for replication.
    To activate replication on slave server execute following command in mysql console:

    CHANGE MASTER TO
    MASTER_HOST='IP-address or primary server',
    MASTER_USER='slave_user',
    MASTER_PASSWORD='pass',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=XXX;

    Remember “Position” field from “SHOW MASTER STATUS” command and write value in place of XXX.

    And start replacation:

    START SLAVE;

    Look at status::

    SHOW SLAVE STATUS\G

    Slave_IO_State: Waiting for master to send event

    To stop replication use:

    STOP SLAVE

    PS: MySQL uses TCP port 3306 for communication, so it should be open between them.