Re: need Help - Mysqldump issue
Hello, Thank for your help I just try mysqldump with --quick or --opt option ... to avoid out of memory problem but -- dump fails with --max_allowed_packet=2048M and --quick : r...@pcjahia01:/# /usr/bin/mysqldump -A --max_allowed_packet=2048M --quick --default-character-set=UTF8 -u root -p /var/tmp/testbackup01.sql Enter password: mysqldump: Error 5: Out of memory (Needed 702898072 bytes) when dumping table `jahia_sl2_version_content` at row: 0 -- dump succeed with --max_allowed_packet=4096M and --quick ( but the computer freezes a lot ) I thought using --quick option avoid mysqldump out of memory problem...but I still need --max_allowed_packet=4096M ... Computer total memory is 8Gb, I run a J2EE application which own 4Gb, additionally a 3Gb mysql database, altought I use mysqldump with --quick Do you think I need more physical memory ? Bests, Christophe. Christophe Dumonet Centre de Ressources Informatiques Institut Francais de Mecanique Avancee (IFMA) Campus des Cezeaux BP 265 63175 AUBIERE Cedex Tel : +33 - 4.73.28.80.64 Fax : +33 - 4.73.28.81.00 Mail : christophe.dumo...@ifma.fr Le 19/11/2010 10:46, Christophe DUMONET a écrit : Hello, Starting today, my daily database backup script does not work :-( with mysqldump typically Out of memory error. So, I try to change max_allowed_packet option value, but I don't succeed (mysql run on ubuntu 10.04 OS with 5.1.41-3ubuntu12.7 0 mysql version.) On the last successfull backup, database size was : 2,59 Go Here is some of my test : (with --max_allowed_packet option = 512M ) /usr/bin/mysqldump -A --max_allowed_packet=512M --default-character-set=UTF8 -u root -p /tmp/testbackup01.sql Enter password: mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `jahia_sl2_version_content` at row: 0 (with --max_allowed_packet = 1024M or 2048 option : ) /usr/bin/mysqldump -A --max_allowed_packet=1024M --default-character-set=UTF8 -u root -p /tmp/testbackup01.sql Enter password: mysqldump: Out of memory (Needed 1405796107 bytes) mysqldump: Couldn't allocate memory (with --max_allowed_packet = 4096M option : ) /usr/bin/mysqldump -A --skip-quick --max_allowed_packet=4096M --default-character-set=UTF8 -u root -p /tmp/testbackup01.sql Warning: option 'max_allowed_packet': unsigned value 4294967296 adjusted to 2147483648 Enter password: mysqldump: Out of memory (Needed 702898104 bytes) mysqldump: Got error: 2008: MySQL client ran out of memory when retrieving data from server Adding --skip-opt --quick option does not solve the issue Adding --skip-quick, the error is : mysqldump: Out of memory (Needed 702898104 bytes) mysqldump: Got error: 2008: MySQL client ran out of memory when retrieving data from server Here is my config : /etc/mysql/my.cnf [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking bind-address= 127.0.0.1 key_buffer = 16M max_allowed_packet = 4096M thread_stack= 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size= 16M log_error= /var/log/mysql/error.log expire_logs_days= 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M includedir /etc/mysql/conf.d/ Any help would be appreciate !! Bests -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Changing database tables to different storage engine.
Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following: 1. this is a production system and can't afford any downtime or as little performance degration as possible. What is the best way to do this in order to have the least amount of effect on the database and it's performance? Regards Machiel
Re: Changing database tables to different storage engine.
Hi All Sorry but things have now changed, and I found the following. The tables was in fact restored as Innodb, however someone seems to have gone and changed something causing innodb to be disabled, this caused the tables to be defaulted back to MyIsam. Should this not rather have just resulted in an error allowing to fix the problem in the first place instead of changing the storage engines? Anyone have some thoughts on the best solution to fix this? I will look into the innodb not working soon. Machiel -Original Message- From: Machiel Richards machi...@rdc.co.za To: mysql mailing list mysql@lists.mysql.com Subject: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 11:59:03 +0200 Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following: 1. this is a production system and can't afford any downtime or as little performance degration as possible. What is the best way to do this in order to have the least amount of effect on the database and it's performance? Regards Machiel
Re: Changing database tables to different storage engine.
I have frequently seen Innodb 'silently' disabled if the innodb_log_file_size is different to the files size on disk (quite common when moving systems about). You wont be able to use innodb until you resolve this either by deleting the log files and restarting mysqld so they get recreated or changing the innodb_log_file_size to match the size of the files on disk. If the Innodb engine is not available then MySQL will use the default (usually MyISAM) storage engine even if Innodb was specified. You can stop this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION Regards John On 22 November 2010 10:12, Machiel Richards machiel.richa...@gmail.comwrote: Hi All Sorry but things have now changed, and I found the following. The tables was in fact restored as Innodb, however someone seems to have gone and changed something causing innodb to be disabled, this caused the tables to be defaulted back to MyIsam. Should this not rather have just resulted in an error allowing to fix the problem in the first place instead of changing the storage engines? Anyone have some thoughts on the best solution to fix this? I will look into the innodb not working soon. Machiel -Original Message- From: Machiel Richards machi...@rdc.co.za To: mysql mailing list mysql@lists.mysql.com Subject: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 11:59:03 +0200 Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following: 1. this is a production system and can't afford any downtime or as little performance degration as possible. What is the best way to do this in order to have the least amount of effect on the database and it's performance? Regards Machiel -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Changing database tables to different storage engine.
Thank you John I have in the meantime fond this to be the case (** someone changed config files without my knowledge it seems as this was setup properly and working**) Anyhow, in order for the innodb to be active again I need to restart the database, however aftewards I assume the tables will still be MyIsam. In this event I will need to manually alter each table, and I am concerned about the impact of this on the system performance. Regards Machiel -Original Message- From: John Daisley daisleyj...@googlemail.com To: Machiel Richards machiel.richa...@gmail.com Cc: mysql mailing list mysql@lists.mysql.com Subject: Re: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 10:51:23 + I have frequently seen Innodb 'silently' disabled if the innodb_log_file_size is different to the files size on disk (quite common when moving systems about). You wont be able to use innodb until you resolve this either by deleting the log files and restarting mysqld so they get recreated or changing the innodb_log_file_size to match the size of the files on disk. If the Innodb engine is not available then MySQL will use the default (usually MyISAM) storage engine even if Innodb was specified. You can stop this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION Regards John On 22 November 2010 10:12, Machiel Richards machiel.richa...@gmail.com wrote: Hi All Sorry but things have now changed, and I found the following. The tables was in fact restored as Innodb, however someone seems to have gone and changed something causing innodb to be disabled, this caused the tables to be defaulted back to MyIsam. Should this not rather have just resulted in an error allowing to fix the problem in the first place instead of changing the storage engines? Anyone have some thoughts on the best solution to fix this? I will look into the innodb not working soon. Machiel -Original Message- From: Machiel Richards machi...@rdc.co.za To: mysql mailing list mysql@lists.mysql.com Subject: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 11:59:03 +0200 Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following: 1. this is a production system and can't afford any downtime or as little performance degration as possible. What is the best way to do this in order to have the least amount of effect on the database and it's performance? Regards Machiel -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
MySQL replication server
Hi All sorry to bother everyone again. but now I have a question from a client which I am sure about my answer, however need to confirm. When setting up a master/slave replication set. As I understand it, the slave server can't accept any writes, however it will be able to accept reads. Is this correct, or will the slave server still be able to accept writes as well (even though it may not be replicated) ? Kind Regards Machiel
Upgrading of mysql database
Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel
Re: MySQL replication server
You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote: Hi All sorry to bother everyone again. but now I have a question from a client which I am sure about my answer, however need to confirm. When setting up a master/slave replication set. As I understand it, the slave server can't accept any writes, however it will be able to accept reads. Is this correct, or will the slave server still be able to accept writes as well (even though it may not be replicated) ? Kind Regards Machiel -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: MySQL replication server
Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisley daisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL replication server
My reason for asking this is the following The client have database A on one machine, Database B on a second machine both of which are production. They want to setup replication of Database B to Server hosting Database A and still keep Server A as the primary production system. -Original Message- From: a.sm...@ukgrid.net To: John Daisley daisleyj...@googlemail.com Cc: Machiel Richards machi...@rdc.co.za, mysql mailing list mysql@lists.mysql.com Subject: Re: MySQL replication server Date: Mon, 22 Nov 2010 13:03:38 + Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisley daisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote:
Re: MySQL replication server
The replicated database should not be accepting writes, if it is then you haven't set it up correctly On 22 November 2010 13:03, a.sm...@ukgrid.net wrote: Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisley daisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote: -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: MySQL replication server
Additionally, if a user has the SUPER privilege (eg. all privileges on *.*) they can write to a database running in read-only mode. Yet another reason to never allow this privilege for general purpose users. Tyler On 11/22/10 8:08 AM, John Daisley wrote: The replicated database should not be accepting writes, if it is then you haven't set it up correctly On 22 November 2010 13:03,a.sm...@ukgrid.net wrote: Hi, I think you are wrong, slaves will always accept writes unless you set readonly in the mysql config. Due to this, and if you dont specifically set readonly on the slave you have to be very careful in order to maintain data integrity on the slave and also not to break repliacton. Tools like Maatkit are designed to check data integrity on the slave due to exactly this issue, thanks Andy. Quoting John Daisleydaisleyj...@googlemail.com: You are correct, in a master slave setup the slave does not accept writes. John On 22 November 2010 11:06, Machiel Richardsmachi...@rdc.co.za wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL replication server
Hi, so yes you can do that, but then I guess you cannot set the server hosting database A as readonly (from memory this can only be set server wide, but worht checking it out). Which might leave you a few options to ensure data integrity, for example simply by user security either by disabling access to relevant users or via setting grants appropriately. Or you could look at a second MySQL instance on the database A server either listening on another port or in a virtual server/zone/jail, Andy. Quoting Machiel Richards machi...@rdc.co.za: My reason for asking this is the following The client have database A on one machine, Database B on a second machine both of which are production. They want to setup replication of Database B to Server hosting Database A and still keep Server A as the primary production system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Changing database tables to different storage engine.
On Mon, Nov 22, 2010 at 11:55 AM, Machiel Richards machiel.richa...@gmail.com wrote: In this event I will need to manually alter each table, and I am concerned about the impact of this on the system performance. That will indeed make for quite some locking time, depending on the size of your tables. It's going to be hard to get around that, unfortunately. What you could do, is create temporary tables, populate them with using insert into...select and then alter table rename them into place. One hell of a job if you have many tables, though, and even then you may feel some impact on performance. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Changing database tables to different storage engine.
Machiel, Each table will be write locked while it is being altered so this will most likely impact the application. In addition to the write lock, the conversion causes each table to be completely rewritten in the new format so this will have a high impact on IO write activity and so it will impact overall IO throughput. If your application is mostly reads, is well cached in memory, and the tables are small this should be pretty fast and relatively pain free. If you aren't sure about the impact and conversion time you may want to restore a backup of the database to another location and run through the conversion while monitoring performance numbers. Tyler On 11/22/10 5:55 AM, Machiel Richards wrote: Thank you John I have in the meantime fond this to be the case (** someone changed config files without my knowledge it seems as this was setup properly and working**) Anyhow, in order for the innodb to be active again I need to restart the database, however aftewards I assume the tables will still be MyIsam. In this event I will need to manually alter each table, and I am concerned about the impact of this on the system performance. Regards Machiel -Original Message- From: John Daisleydaisleyj...@googlemail.com To: Machiel Richardsmachiel.richa...@gmail.com Cc: mysql mailing listmysql@lists.mysql.com Subject: Re: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 10:51:23 + I have frequently seen Innodb 'silently' disabled if the innodb_log_file_size is different to the files size on disk (quite common when moving systems about). You wont be able to use innodb until you resolve this either by deleting the log files and restarting mysqld so they get recreated or changing the innodb_log_file_size to match the size of the files on disk. If the Innodb engine is not available then MySQL will use the default (usually MyISAM) storage engine even if Innodb was specified. You can stop this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION Regards John On 22 November 2010 10:12, Machiel Richardsmachiel.richa...@gmail.com wrote: Hi All Sorry but things have now changed, and I found the following. The tables was in fact restored as Innodb, however someone seems to have gone and changed something causing innodb to be disabled, this caused the tables to be defaulted back to MyIsam. Should this not rather have just resulted in an error allowing to fix the problem in the first place instead of changing the storage engines? Anyone have some thoughts on the best solution to fix this? I will look into the innodb not working soon. Machiel -Original Message- From: Machiel Richardsmachi...@rdc.co.za To: mysql mailing listmysql@lists.mysql.com Subject: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 11:59:03 +0200 Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following: 1. this is a production system and can't afford any downtime or as little performance degration as possible. What is the best way to do this in order to have the least amount of effect on the database and it's performance? Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrading of mysql database
That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.zawrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: MySQL replication server
On Mon, Nov 22, 2010 at 2:08 PM, John Daisley daisleyj...@googlemail.comwrote: The replicated database should not be accepting writes, if it is then you haven't set it up correctly *shrug* I never bother. The slave is way too useful to fuck around with optimisations and whatnot, reporting tools tend to do useful aggregations, et cetera. You may like to set it read-only, but that doesn't make it the only way, let a lone a requirement. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Changing database tables to different storage engine.
Another option, if your data hasn't changed in the mean time (I know, rare scenario) could be to set up a secondary instance from the same binaries and changing only the datafile location and the port in the config, re-importing, shutting both instances down and switching out the datafiles. You'll get some performance impact from the import's disk activity, but the switch should take almost no time - if the datafiles are on the same physical disk, of course. On Mon, Nov 22, 2010 at 3:24 PM, Tyler Poland tpol...@engineyard.comwrote: Machiel, Each table will be write locked while it is being altered so this will most likely impact the application. In addition to the write lock, the conversion causes each table to be completely rewritten in the new format so this will have a high impact on IO write activity and so it will impact overall IO throughput. If your application is mostly reads, is well cached in memory, and the tables are small this should be pretty fast and relatively pain free. If you aren't sure about the impact and conversion time you may want to restore a backup of the database to another location and run through the conversion while monitoring performance numbers. Tyler On 11/22/10 5:55 AM, Machiel Richards wrote: Thank you John I have in the meantime fond this to be the case (** someone changed config files without my knowledge it seems as this was setup properly and working**) Anyhow, in order for the innodb to be active again I need to restart the database, however aftewards I assume the tables will still be MyIsam. In this event I will need to manually alter each table, and I am concerned about the impact of this on the system performance. Regards Machiel -Original Message- From: John Daisleydaisleyj...@googlemail.com To: Machiel Richardsmachiel.richa...@gmail.com Cc: mysql mailing listmysql@lists.mysql.com Subject: Re: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 10:51:23 + I have frequently seen Innodb 'silently' disabled if the innodb_log_file_size is different to the files size on disk (quite common when moving systems about). You wont be able to use innodb until you resolve this either by deleting the log files and restarting mysqld so they get recreated or changing the innodb_log_file_size to match the size of the files on disk. If the Innodb engine is not available then MySQL will use the default (usually MyISAM) storage engine even if Innodb was specified. You can stop this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION Regards John On 22 November 2010 10:12, Machiel Richardsmachiel.richa...@gmail.com wrote: Hi All Sorry but things have now changed, and I found the following. The tables was in fact restored as Innodb, however someone seems to have gone and changed something causing innodb to be disabled, this caused the tables to be defaulted back to MyIsam. Should this not rather have just resulted in an error allowing to fix the problem in the first place instead of changing the storage engines? Anyone have some thoughts on the best solution to fix this? I will look into the innodb not working soon. Machiel -Original Message- From: Machiel Richardsmachi...@rdc.co.za To: mysql mailing listmysql@lists.mysql.com Subject: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 11:59:03 +0200 Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following: 1. this is a production system and can't afford any downtime or as little performance degration as possible. What is the best way to do this in order to have the least amount of effect on the database and it's performance?
Re: Upgrading of mysql database
Replace the software - if you're using packaged versions, they should take care of most anything. If not, there's mysql-upgrade or some script. See the online docs for specifics. On Mon, Nov 22, 2010 at 3:56 PM, Machiel Richards machi...@rdc.co.zawrote: How would I do an inplace upgrade? -Original Message- *From*: Johan De Meersman vegiv...@tuxera.bejohan%20de%20meersman%20%3cvegiv...@tuxera.be%3e *To*: Machiel Richards machi...@rdc.co.zamachiel%20richards%20%3cmachi...@rdc.co.za%3e *Cc*: mysql mailing list mysql@lists.mysql.commysql%20mailing%20list%20%3cmy...@lists.mysql.com%3e *Subject*: Re: Upgrading of mysql database *Date*: Mon, 22 Nov 2010 15:25:44 +0100 That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.za wrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Upgrading of mysql database
How would I do an inplace upgrade? -Original Message- From: Johan De Meersman vegiv...@tuxera.be To: Machiel Richards machi...@rdc.co.za Cc: mysql mailing list mysql@lists.mysql.com Subject: Re: Upgrading of mysql database Date: Mon, 22 Nov 2010 15:25:44 +0100 That would work, yes. You could also try to upgrade in place - the upgrade scripts *should* take care of everything between those versions, I think. Make sure you have a backup in any case :-) On Mon, Nov 22, 2010 at 12:57 PM, Machiel Richards machi...@rdc.co.za wrote: Hi All Sorry for all my posts today but this one client is keeping me busy. the version of MySQL installed on the ubuntu server is 5.0.51a-3ubuntu5.8-log as this was the latest one available in the repository. We will need to upgrade this to version 5.1.53. Am I correct in assuming the following steps? 1. setup version 5.1.53 on the machine (different port) 2. shutdown the current database. 3. create backup file 4. restore backup 5. change port to 3306 6. startup new database. 7. disable the old database so that it would not start up during reboot. Regards Machiel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Changing database tables to different storage engine.
Another thing to keep in mind is to make sure all your foreign keys are re-created if you have any. We had a similar accident in our prod box a few years back and converting MyIsam to InnoDB won't necessarily re-create the foreign keys. Kyong On Mon, Nov 22, 2010 at 6:39 AM, Johan De Meersman vegiv...@tuxera.be wrote: Another option, if your data hasn't changed in the mean time (I know, rare scenario) could be to set up a secondary instance from the same binaries and changing only the datafile location and the port in the config, re-importing, shutting both instances down and switching out the datafiles. You'll get some performance impact from the import's disk activity, but the switch should take almost no time - if the datafiles are on the same physical disk, of course. On Mon, Nov 22, 2010 at 3:24 PM, Tyler Poland tpol...@engineyard.comwrote: Machiel, Each table will be write locked while it is being altered so this will most likely impact the application. In addition to the write lock, the conversion causes each table to be completely rewritten in the new format so this will have a high impact on IO write activity and so it will impact overall IO throughput. If your application is mostly reads, is well cached in memory, and the tables are small this should be pretty fast and relatively pain free. If you aren't sure about the impact and conversion time you may want to restore a backup of the database to another location and run through the conversion while monitoring performance numbers. Tyler On 11/22/10 5:55 AM, Machiel Richards wrote: Thank you John I have in the meantime fond this to be the case (** someone changed config files without my knowledge it seems as this was setup properly and working**) Anyhow, in order for the innodb to be active again I need to restart the database, however aftewards I assume the tables will still be MyIsam. In this event I will need to manually alter each table, and I am concerned about the impact of this on the system performance. Regards Machiel -Original Message- From: John Daisleydaisleyj...@googlemail.com To: Machiel Richardsmachiel.richa...@gmail.com Cc: mysql mailing listmysql@lists.mysql.com Subject: Re: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 10:51:23 + I have frequently seen Innodb 'silently' disabled if the innodb_log_file_size is different to the files size on disk (quite common when moving systems about). You wont be able to use innodb until you resolve this either by deleting the log files and restarting mysqld so they get recreated or changing the innodb_log_file_size to match the size of the files on disk. If the Innodb engine is not available then MySQL will use the default (usually MyISAM) storage engine even if Innodb was specified. You can stop this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION Regards John On 22 November 2010 10:12, Machiel Richardsmachiel.richa...@gmail.com wrote: Hi All Sorry but things have now changed, and I found the following. The tables was in fact restored as Innodb, however someone seems to have gone and changed something causing innodb to be disabled, this caused the tables to be defaulted back to MyIsam. Should this not rather have just resulted in an error allowing to fix the problem in the first place instead of changing the storage engines? Anyone have some thoughts on the best solution to fix this? I will look into the innodb not working soon. Machiel -Original Message- From: Machiel Richardsmachi...@rdc.co.za To: mysql mailing listmysql@lists.mysql.com Subject: Changing database tables to different storage engine. Date: Mon, 22 Nov 2010 11:59:03 +0200 Good day all Hope all is well. I have something to ask as someone might have done this as well and may have a good solution on how to fix this. During a database migration this weekend to move a MySQL database from windows to linux, we created a backup and restore of the database. However, form my part I made a mistake by overlooking the fact that the windows database was configured to use default storage engine as Innodb. On the new server, the default was set to MyIsam. This resulted in all the tables being restored to the new system as MyIsam instead of Innodb. In order to fix this, I know you can use alter table to change the storage engine, however I need to know the following:
