Solved

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

Posted on 2008-09-30
12
2,619 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 W
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 W
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 W 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Grouping 2 41
How to send multiple emails at the same time in PHP 12 60
PHP & MySQL - Rounding Results from a Select Query 3 25
awk variable in printf 1 25
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

773 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