Solved

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

Posted on 2008-09-30
12
2,602 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
[MYSQL]: Delete is very slow 4 54
Create a Select Query and Populate a Table 3 45
updating table data with inner join 9 24
change database name 2 4
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…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
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.:

867 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

21 Experts available now in Live!

Get 1:1 Help Now