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:

[code]
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
[/code]

It can be resolved by running following yum transaction:

[code]
$ yum shell
> remove compat-mysql51
> install mysql-community-libs-compat
> run
[/code]

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)

[code]systemctl list-unit-files[/code]

2. Enable HTTPD + Mysql service on system boot:

[code]
systemctl enable httpd
systemctl enable mariadb
[/code]

3. Reverting to iptables from firewalld

[code]
systemctl stop firewalld
systemctl mask firewalld

yum install iptables-services

systemctl enable iptables
systemctl start iptables

# to save iptables rules use
service iptables save

[/code]

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

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

    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.
    [code]service mysql restart[/code]

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

    [code]mysql -u root -p
    GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘pass’;
    FLUSH PRIVILEGES;
    [/code]

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

    [code]
    FLUSH TABLES READ LOCK;
    SHOW MASTER STATUS;
    [/code]
    Save “Position” field.

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

    [code]mysqldump -u root -p -f db1 > db1.sql[/code]
    db1 – name of database, that you want to dump

    Unlock database.
    [code]UNLOCK TABLES;[/code]

    Now it’s time to configure secondary server.

  • Secondary server
  • [code]CREATE DATABASE db1;[/code]

    Import dump from first server:

    [code]mysql -u root -p -f db1 < db1.sql[/code]

    Edit MySQL config file /etc/my.cnf

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

    Restart MySQL:
    [code]service mysql restart[/code]

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

    [code]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;[/code]

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

    And start replacation:
    [code]START SLAVE;[/code]

    Look at status::
    [code]SHOW SLAVE STATUS\G[/code]

    Slave_IO_State: Waiting for master to send event

    To stop replication use:
    [code]STOP SLAVE[/code]

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