Link to home
Start Free TrialLog in
Avatar of Thaidog
ThaidogFlag for United States of America

asked on

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

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.
Avatar of Michael Worsham
Michael Worsham
Flag of United States of America image

Avatar of Thaidog

ASKER

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
Avatar of Thaidog

ASKER

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
Avatar of Thaidog

ASKER

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?
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.

Avatar of Thaidog

ASKER


[#####@##### ~]# 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
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.
Avatar of Thaidog

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Michael Worsham
Michael Worsham
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial