Solved

MySQL replication confiuguration issues: /var/run directory being used for log files

Posted on 2008-09-30
12
2,666 Views
Last Modified: 2013-12-16
I am trying to get MySQL replication going on two boxes. One master, one slave. I have found out that the compiled version I am configuring has a bug where the /var/run directory is being used for storage for the relay logs which is in turn erasing them... see this url:

http://arjen-lentz.livejournal.com/115899.html

I have configured the /etc/my.conf file as advised but I am still getting errors. (I had it running but after a reboot it is failing again... not sure what I did to mess it up again other than maybe there is still files being wiped in /var/run/)

Here is my /etc/my.conf file on the slave:

[root@####### ~]# cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1
server-id=2
master-host=#######
master-user=#########
master-password=#####
max_allowed_packet=32M
relay-log=/var/run/mysqld/mysql-relay-bin
replicate-do-db=master_v2
master-connect-retry=60
relay-log-index=/var/lib/mysql/relay-bin.index
relay-log-info-file=/var/lib/mysql/relay-bin.info
key_buffer=512M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here is the errors I am still getting in the /var/log/mysqld.log file:

080930 13:15:26  mysqld started
080930 13:15:26 [Warning] The syntax for replication startup options is deprecat
ed and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
080930 13:15:26  InnoDB: Started; log sequence number 0 167834069
080930 13:15:26 [ERROR] /usr/libexec/mysqld: File '/var/run/mysqld/mysql-relay-b
in.000001' not found (Errcode: 13)
080930 13:15:26 [ERROR] Failed to open log (file '/var/run/mysqld/mysql-relay-bi
n.000001', errno 13)
080930 13:15:26 [ERROR] Failed to open the relay log '/var/run/mysqld/mysql-rela
y-bin.000001' (relay_log_pos 4)
080930 13:15:26 [ERROR] Could not open log file
080930 13:15:26 [ERROR] Failed to initialize the master info structure
080930 13:15:26 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distr
ibution

The OS is CentOS 5.2 with latest patches from yum... everything is as CentOS tell you to configure.
0
Comment
Question by:Thaidog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
12 Comments
 
LVL 29

Expert Comment

by:Michael Worsham
ID: 22610328
0
 

Author Comment

by:Thaidog
ID: 22614212
Actually it turns put that I have MySQL 5 on the system:

[#####@######## ~]# yum list | grep mysql
mysql.x86_64                             5.0.45-7.el5           installed
mysql-devel.x86_64                       5.0.45-7.el5           installed
mysql-server.x86_64                      5.0.45-7.el5           installed
0
 

Author Comment

by:Thaidog
ID: 22617037
I got this log from a backup taken before the slave stopped working...

080924 14:59:54  mysqld started
080924 14:59:54 [Warning] The syntax for replication startup options is deprecat
ed and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
080924 14:59:54  InnoDB: Started; log sequence number 1 2563376611
080924 14:59:54 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distr
ibution
080924 14:59:54 [Note] Slave SQL thread initialized, starting replication in log
 'mysql_binary_log.000003' at position 453, relay log '/var/run/mysqld/mysqld-re
lay-bin.000094' position: 597
080924 14:59:54 [Note] Slave I/O thread: connected to master 'replicator@10.0.1.
124:3306',  replication started in log 'mysql_binary_log.000003' at position 453

080924 15:01:40 [Note] /usr/libexec/mysqld: Normal shutdown

080924 15:01:40 [Note] Slave I/O thread killed while reading event
080924 15:01:40 [Note] Slave I/O thread exiting, read up to log 'mysql_binary_lo
g.000003', position 453
080924 15:01:40 [Note] Error reading relay log event: slave SQL thread was kille
d
080924 15:01:40  InnoDB: Starting shutdown...
080924 15:01:42  InnoDB: Shutdown completed; log sequence number 1 2563376621
080924 15:01:42 [Note] /usr/libexec/mysqld: Shutdown complete

080924 15:01:42  mysqld ended

080924 15:02:25  mysqld started
080924 15:02:25 [Warning] The syntax for replication startup options is deprecat
ed and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
080924 15:02:25  InnoDB: Started; log sequence number 1 2563376621
080924 15:02:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distr
ibution
080924 15:02:25 [Note] Slave SQL thread initialized, starting replication in log
 'mysql_binary_log.000003' at position 453, relay log '/var/run/mysqld/mysqld-re
lay-bin.000096' position: 242
080924 15:02:25 [Note] Slave I/O thread: connected to master 'replicator@10.0.1.
124:3306',  replication started in log 'mysql_binary_log.000003' at position 453

080925 21:13:49 [Note] /usr/libexec/mysqld: Normal shutdown

080925 21:13:49 [Note] Slave I/O thread killed while reading event
080925 21:13:49 [Note] Slave I/O thread exiting, read up to log 'mysql_binary_lo
g.000003', position 631
080925 21:13:49 [Note] Error reading relay log event: slave SQL thread was kille
d
080925 21:13:49  InnoDB: Starting shutdown...
080925 21:13:52  InnoDB: Shutdown completed; log sequence number 1 2563376829
080925 21:13:52 [Note] /usr/libexec/mysqld: Shutdown complete

080925 21:13:52  mysqld ended

081001 09:35:16  mysqld started
081001  9:35:16 [Warning] The syntax for replication startup options is deprecat
ed and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
081001  9:35:16  InnoDB: Started; log sequence number 1 2563376829
081001  9:35:16 [ERROR] /usr/libexec/mysqld: File '/var/run/mysqld/mysqld-relay-
bin.000128' not found (Errcode: 2)
081001  9:35:16 [ERROR] Failed to open log (file '/var/run/mysqld/mysqld-relay-b
in.000128', errno 2)
081001  9:35:16 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-rel
ay-bin.000128' (relay_log_pos 242)
081001  9:35:16 [ERROR] Could not open log file
081001  9:35:16 [ERROR] Failed to initialize the master info structure
081001  9:35:16 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distr
ibution
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:Thaidog
ID: 22617300
I think I found the cause but I am still having issues fixing it. It seems that the mysqld-relay-bin directory was still pointed to /var/run/mysql instead of /var/lib/mysql/ Whe I fixed the issue and restarted MySQL I got this:

081001 14:07:52  mysqld ended

081001 14:07:53  mysqld started
081001 14:07:53 [Warning] The syntax for replication startup options is deprecat
ed and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
081001 14:07:53  InnoDB: Started; log sequence number 1 2563376829
081001 14:07:53 [ERROR] /usr/libexec/mysqld: File '/var/lib/mysqld/relay-bin.1'
not found (Errcode: 2)
081001 14:07:53 [ERROR] Could not use /var/lib/mysqld/relay-bin for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
081001 14:07:53 [ERROR] Failed in open_log() called from init_relay_log_info()
081001 14:07:53 [ERROR] Failed to initialize the master info structure
081001 14:07:53 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distr
ibution

Now I'm stuck on:

"081001 14:07:53 [ERROR] Could not use /var/lib/mysqld/relay-bin for logging (error 2). Turning logging off for the whole duration of the MySQL server process. T
o turn it on again: fix the cause, shutdown the MySQL server and restart it."

What is "(error 2)" and how do I fix it?
0
 
LVL 29

Expert Comment

by:Michael Worsham
ID: 22617498
Operating System Error Codes
http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html

If I remember right, you don't need a direct path for the log files. I believe it takes it path direction from the 'datadir' setting.

Cut/paste your /etc/my.cnf file here for both the master and the slave.

0
 

Author Comment

by:Thaidog
ID: 22627003

[#####@##### ~]# cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1
server-id=2
master-host=10.0.1.124
master-user=replicator
master-password=eruawk
max_allowed_packet=32M
relay-log=/var/lib/mysqld/relay-bin
replicate-do-db=master_v2
master-connect-retry=60
relay-log-index=/var/lib/mysql/relay-bin.index
relay-log-info-file=/var/lib/mysql/relay-bin.info
#log-bin=/var/log/mysql/binary/mysql_binary_log
key_buffer=512M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
0
 
LVL 29

Expert Comment

by:Michael Worsham
ID: 22628022
I think you need to give the datadir a setting. Curentlly you have it commented out.

Try 'datadir=/var/lib/mysql' and see if that corrects the problem.
0
 

Author Comment

by:Thaidog
ID: 22628696
Still getting errors (that was the slave my.cnf) here is the master:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
binlog-do-db=master_v2
server-id=1
log-bin=/var/log/mysql/binary/mysql_binary_log
key_buffer=512M
#table_cache=1800
max_allowed_packet=32M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

It still has the the data directory string enabled... I tried uncommenting the directory string on the slave and I still got errors:

081002 17:08:47  mysqld ended

081002 17:08:48  mysqld started
081002 17:08:48 [Warning] The syntax for replication startup options is deprecat
ed and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
081002 17:08:48  InnoDB: Started; log sequence number 1 2563885473
081002 17:08:48 [ERROR] /usr/libexec/mysqld: File '/var/lib/mysqld/mysql-relay-b
in.1' not found (Errcode: 2)
081002 17:08:48 [ERROR] Could not use /var/lib/mysqld/mysql-relay-bin for loggin
g (error 2). Turning logging off for the whole duration of the MySQL server proc
ess. To turn it on again: fix the cause, shutdown the MySQL server and restart i
t.
081002 17:08:48 [ERROR] Failed in open_log() called from init_relay_log_info()
081002 17:08:48 [ERROR] Failed to initialize the master info structure
081002 17:08:48 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distr
ibution
0
 
LVL 29

Accepted Solution

by:
Michael Worsham earned 500 total points
ID: 22629570
One possible solution...

1) Setup only one database to run (no master/slave) then dump your database to a sql file.
2) Deprovision (uninstall) both MySQL database instances.
3) Use the instructions from my first post to setup your master/slave environment.
4) Reimport your database sql dump and see if it works then.

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question