Solved

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

Posted on 2008-09-30
12
2,582 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
  • 5
  • 4
12 Comments
 
LVL 29

Expert Comment

by:Michael W
Comment Utility
0
 

Author Comment

by:Thaidog
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Thaidog
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:Michael W
Comment Utility
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
Comment Utility

[#####@##### ~]# 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 W
Comment Utility
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
Comment Utility
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 W earned 500 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have a server on collocation with the super-fast CPU, that doesn't mean that you get it running at full power. Here is a preamble. When doing inventory of Linux servers, that I'm administering, I've found that some of them are running on l…
SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now