Thaidog
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/mys ql.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-rela y-bin
replicate-do-db=master_v2
master-connect-retry=60
relay-log-index=/var/lib/m ysql/relay -bin.index
relay-log-info-file=/var/l ib/mysql/r elay-bin.i nfo
key_buffer=512M
[mysqld_safe]
log-error=/var/log/mysqld. log
pid-file=/var/run/mysqld/m ysqld.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-rel ay-b
in.000001' not found (Errcode: 13)
080930 13:15:26 [ERROR] Failed to open log (file '/var/run/mysqld/mysql-rel ay-bi
n.000001', errno 13)
080930 13:15:26 [ERROR] Failed to open the relay log '/var/run/mysqld/mysql-rel a
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.
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/mys
#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/
replicate-do-db=master_v2
master-connect-retry=60
relay-log-index=/var/lib/m
relay-log-info-file=/var/l
key_buffer=512M
[mysqld_safe]
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
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-rel
in.000001' not found (Errcode: 13)
080930 13:15:26 [ERROR] Failed to open log (file '/var/run/mysqld/mysql-rel
n.000001', errno 13)
080930 13:15:26 [ERROR] Failed to open the relay log '/var/run/mysqld/mysql-rel
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
ibution
The OS is CentOS 5.2 with latest patches from yum... everything is as CentOS tell you to configure.
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
[#####@######## ~]# 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
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-re lay-
bin.000128' not found (Errcode: 2)
081001 9:35:16 [ERROR] Failed to open log (file '/var/run/mysqld/mysqld-re lay-b
in.000128', errno 2)
081001 9:35:16 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-re l
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
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
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
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-re
bin.000128' not found (Errcode: 2)
081001 9:35:16 [ERROR] Failed to open log (file '/var/run/mysqld/mysqld-re
in.000128', errno 2)
081001 9:35:16 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-re
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
ibution
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?
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
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
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.
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.
ASKER
[#####@##### ~]# cat /etc/my.cnf
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mys
#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/
replicate-do-db=master_v2
master-connect-retry=60
relay-log-index=/var/lib/m
relay-log-info-file=/var/l
#log-bin=/var/log/mysql/bi
key_buffer=512M
[mysqld_safe]
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
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.
Try 'datadir=/var/lib/mysql' and see if that corrects the problem.
ASKER
Still getting errors (that was the slave my.cnf) here is the master:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq l.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/bin ary/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/m ysqld.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-rel ay-b
in.1' not found (Errcode: 2)
081002 17:08:48 [ERROR] Could not use /var/lib/mysqld/mysql-rela y-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
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq
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/bin
key_buffer=512M
#table_cache=1800
max_allowed_packet=32M
[mysqld_safe]
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
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-rel
in.1' not found (Errcode: 2)
081002 17:08:48 [ERROR] Could not use /var/lib/mysqld/mysql-rela
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
ibution
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://wiki.kolmisoft.com/index.php/MySQL_Replication