Re: Master not creating new binary log.
Hi, in short what we did was the following: - binary logs was written to a 20GB filesystem and due to company policies we kept expire logs at 7 days. - system got quite busy over the years so the space was becoming a problem and thus we had to move to another directory. - setting that was changed is : log_bin = new directory - old binary logs were moved to the new directory after shutting down the database - database started up and continued as normal, however stopped at the last binary log when it filled up and complained about a corrupted binary log. - a flush logs and reset master was done and a new binary log was created mysql-bin.1 - however same thing happening here, the binlog file fills up to 100Mb as configured, then stops without creating a new binary log. - this is then the point where the replication crashes as well. Output of the queries: mysql SHOW VARIABLES LIKE '%bin%'; +-+--+ | Variable_name | Value| +-+--+ | binlog_cache_size | 1048576 | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 104857600| | sync_binlog | 0| +-+--+ mysql SHOW VARIABLES LIKE '%dir%'; +++ | Variable_name | Value | +++ | basedir| /usr/ | | character_sets_dir | /usr/share/mysql/charsets/ | | datadir| /var/lib/mysql/| | innodb_data_home_dir || | innodb_log_arch_dir|| | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | plugin_dir || | slave_load_tmpdir | /var/lib/mysql/tmp/| | tmpdir | /var/lib/mysql/tmp | +++ 10 rows in set (0.00 sec) Regards On 07/03/2013 08:29 PM, Rick James wrote: What setting(s) did you change to move to the separate partition? SHOW VARIABLES LIKE '%bin%'; SHOW VARIABLES LIKE '%dir%'; (there may be other VARIABLES worth checking) What steps did you take for the move? (Actually move bin.1? Start over? etc?) Consider using expire_logs_days. 5.0 -- I don't think anything relevant has changed during 4.0 thru 5.6. -Original Message- From: Machiel Richards - Gmail [mailto:machiel.richa...@gmail.com] Sent: Wednesday, July 03, 2013 3:20 AM To: mysql list Subject: Master not creating new binary log. Hi all I hope all are well. I would like some assistance with something really strange. We currently have a problem with a master slave setup running mysql 5.0. This is one of our legacy servers which are in the planning to be upgraded, however in order for this to be done the replication needs to be up and running. The problem we have currently however is that the binary logs on the master was moved to a seperate partition due to disc space restrictions. A new binlog file called mysql-bin.1 was created and everything seemed to work fine. However, the moment the file reached the file size of 100Mb, it does not go on to create a new binlog file called mysql-bin.2 and the replication fails stating that it is unable to read the binary log file. Thus far we have done a flush logs and reset master , but the same problem occurs, where it creates mysql-bin.1 and the moment it reaches it's max size and suppose to create a new file, it stops and does not create the new one. I really hope this makes sense, and that someone can perhaps point us in the correct direction. Any help would be appreciated. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Master not creating new binary log.
Hi all I hope all are well. I would like some assistance with something really strange. We currently have a problem with a master slave setup running mysql 5.0. This is one of our legacy servers which are in the planning to be upgraded, however in order for this to be done the replication needs to be up and running. The problem we have currently however is that the binary logs on the master was moved to a seperate partition due to disc space restrictions. A new binlog file called mysql-bin.1 was created and everything seemed to work fine. However, the moment the file reached the file size of 100Mb, it does not go on to create a new binlog file called mysql-bin.2 and the replication fails stating that it is unable to read the binary log file. Thus far we have done a flush logs and reset master , but the same problem occurs, where it creates mysql-bin.1 and the moment it reaches it's max size and suppose to create a new file, it stops and does not create the new one. I really hope this makes sense, and that someone can perhaps point us in the correct direction. Any help would be appreciated. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Assistance with replication
Hi Guys We need some assistance with a master slave setup we are experiencing some issues with. We have one master with 2 slaves replicating from it. Both the slaves stopped working this morning in the early hours at exactly the same bin_log file and position. We have since restarted the slave servers and when you run a show slave status the seconds behind master stays 0. Upon closer investigation, the Relay_Master_Log_File and Exec_Master_Log_Pos does not change, although all other values are changing. No errors are being logged, and there is enough disk space on the server so it can't be space related. Can anyone suggest some things we can check or do in order to get replication sorted and to avoid future issues like this? regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Assistance with replication
Hi Manuel Please take careful note of what I have stated in the original mail. 1. the fact that the slaves say seconds behind master = 0 does not neccesarily mean that repliication is working as I have found this a lot of times where it shows 0 then have proven that replication was not working (I.e. data not being processed on the slave). The 0 seconds simply means that it has read all the data but does not mean that it has been processed or up to date. 2. the Exec_Master_Log_Pos and Master_Log_File are in fact out of sync with the master (i.e. on the master the file and log position are way ahead of the slaves by about 9 hours). 3. We have tested replication by creating a test table in the database with some data and it was not replicated to the slaves. Regards Machiel On 11/12/2012 03:11 PM, Manuel Arostegui wrote: 2012/11/12 Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com Hi Guys We need some assistance with a master slave setup we are experiencing some issues with. We have one master with 2 slaves replicating from it. Both the slaves stopped working this morning in the early hours at exactly the same bin_log file and position. We have since restarted the slave servers and when you run a show slave status the seconds behind master stays 0. Upon closer investigation, the Relay_Master_Log_File and Exec_Master_Log_Pos does not change, although all other values are changing. No errors are being logged, and there is enough disk space on the server so it can't be space related. Can anyone suggest some things we can check or do in order to get replication sorted and to avoid future issues like this? Hello Machiel, If seconds behind the master is at 0 it means the replication is working fine - it's not broken (broken would show NULL) What you can do to make sure both machines are indeed replicating all the data arriving to the master is: Master: show master status\G Check File and Position field Slave: show slave status\G Check Exec_Master_Log_Pos and Master_Log_File The should be the same in both, master and slaves. Keep in mind these values will change if the master is getting writes. So try to run the commands at the same time in the 3 servers. Good luck! Manuel.
Mysql backup for large databases
Hi All I am busy investigating some options relating to the backup for MySQL databases when they get quite large. When using the MySQL enterprise, there is the option to use the MySQL enterprise backup as it is part of the Enterprise license. However, when using the GA (freely available) versions, the options for backups on large databases seems to be a bit limited. Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. Does anybody have some ideas on backup strategies that can be investigated in this regard? Just for information, the data in these databases are very crucial data and thus up to 2 years of data needs to be kept in the database for easy retrieval and thus archiving is limited to data older than 2 years while the database receives about 1mil entries per day. Any ideas would be appreciated. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Temporary table creation fails
Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, still no luck, same error being given immediately after pressing enter. On 09/10/2012 12:02 PM, Ananda Kumar wrote: start with 500MB and try On Mon, Sep 10, 2012 at 3:31 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
This is a current production database with about 100Gb + of data and the DB is extremely busy. On 09/10/2012 12:08 PM, Akshay Suryavanshi wrote: Hi, If you dont have data on the server, would you please initialize the data directory. Use mysql-install-db and give proper data directory and proper cnf file if you are giving so. Also specify the user as root if you have root access. Thanks On Mon, Sep 10, 2012 at 3:34 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, still no luck, same error being given immediately after pressing enter. On 09/10/2012 12:02 PM, Ananda Kumar wrote: start with 500MB and try On Mon, Sep 10, 2012 at 3:31 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
no selinux , checked this as well. We generally dont use selinux and disable it completely from installation. I have also gone through the firewall settings and that is only rules for connections. On 09/10/2012 02:40 PM, Ananda Kumar wrote: did u check if there any firewall settings, forbidding you to create files, check if SELinux is disabled On Mon, Sep 10, 2012 at 6:08 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, permissions are confirmed as being correct. Other applications and users are currently writing files to this directory yes. The only thing found in the log is innodb error 13 stating that either a second mysqld is running or that there is a filesystem permissions issue. I have now changed the mysql tmp directory and will be restarting tonight to let it take effect. Will keep everyone updated tomorrow. Regards On 09/10/2012 03:32 PM, Garot Conklin wrote: Apologies if I missed this in the thread but have you confirmed not only the effectve perms for the directory but that another user can write to this dirrectory? Perhaps outside of mysql for instance; I.e. other processes are successfully writting logs to /tmp? Anything to share from the mysql logs perhaps? Sent from Yahoo! Mail on Android *From: * Machiel Richards - Gmail machiel.richa...@gmail.com; *To: * Ananda Kumar anan...@gmail.com; *Cc: * mysql@lists.mysql.com; *Subject: * Re: Temporary table creation fails *Sent: * Mon, Sep 10, 2012 12:41:51 PM no selinux , checked this as well. We generally dont use selinux and disable it completely from installation. I have also gone through the firewall settings and that is only rules for connections. On 09/10/2012 02:40 PM, Ananda Kumar wrote: did u check if there any firewall settings, forbidding you to create files, check if SELinux is disabled On Mon, Sep 10, 2012 at 6:08 PM, Machiel Richards - Gmail machiel.richa...@gmail.com javascript:return mailto:machiel.richa...@gmail.com javascript:return wrote: Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com javascript:return mailto:machiel.richa...@gmail.com javascript:return wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Fwd: Question relating to transactions on innodb tables.
Hi All Just a quick question relating to the use of transactions on innodb tables. We are doing some archiving on some innodb tables, however there seems to be some issues somewhere in the process with data not being updated accordingly. We would like to make use of transactions for this , in order to allow us to roll back on changes if the after checks does not correspond. What I am trying to find out is whether a transaction will cause the locks to be handled as table locks or will it be row level locks? Regards Machiel
