RE: Upgrading How To
Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading How To
Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. Any other comments are welcome. -G
Upgrading How To
Hi all, I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Example: Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. 1) How does one correctly move the users and all the permissions (grant tables), 2) How does one move the data. 3) Assume lots of backups have been done and there is no risk of permanently loosing data. Also, move/copy to be done using files (to memstick or external disk or NFS), not using ssh directly (rcopy etc). 4) Basically, dumpt the data and users and perms and put it on a new box J I suspect this question is trivial to a lot of admins out there, but, I suspect it would be helpful to many out there. Happy Holidays! -Grant
Mysql 4 to 5
Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G
Re: Mysql 4 to 5
- Original Message - From: Michael Dykman mdyk...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:20 PM Subject: Re: Mysql 4 to 5 One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; - md On Sun, Jul 11, 2010 at 7:50 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G -- - michael dykman - mdyk...@gmail.com May the Source be with you. Thanks Mike, I will try that :-) -G -- 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 4 to 5
- Original Message - From: Grant Peel gp...@thenetnow.com To: Michael Dykman mdyk...@gmail.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:25 PM Subject: Re: Mysql 4 to 5 - Original Message - From: Michael Dykman mdyk...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:20 PM Subject: Re: Mysql 4 to 5 One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; - md On Sun, Jul 11, 2010 at 7:50 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G -- - michael dykman - mdyk...@gmail.com May the Source be with you. Thanks Mike, I will try that :-) -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Mike, UPDATE: THe backtick worked, thanks for the help! -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updating 4 to 5
- Original Message - From: Rob Wultsch wult...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Friday, July 02, 2010 3:57 AM Subject: Re: Updating 4 to 5 On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. It would be good if mentioned what release of the various series you were using or wanting to upgrade to. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). By restoring from backup for mysql a sql dump, or a filesystem backup? Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant It depends. The way I generally do upgrades is the following: 1. Identify the backup point for the current server. Do a 'mysqldump --all-databases --complete-insert' from it using the mysqldump from the version of mysql I will be using after the upgrade and record the 'show slave status' while it is running. 2. Import the backup on to the new server after removing any commands that would perform ddl on the mysql schema. 3. Setup replicaton and fail over to the new server at an opportune time. So, you should keep in mind a few things: 1. Between version of MySQL the table format changes, and it is generally worthing while to take advantage of the changes. 2. mysqlupgrade runs REPAIR TABLE which acts differently in different versions of MySQL See http://www.mysqlperformanceblog.com/2010/05/14/mysql_upgrade-and-innodb-tables/. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Rob, Thanks for taking the time to reply, The mysql databases would be restored from a filesystem backup. The whole server, mysql and all, are FreeBSD 6.x I usually: Run a complete backup of all filesystems, (/,/var,/home,/user) to an NFS server, Build the new server, Restore all filesystems to the new disk (that is built with mysql 5), Run the mysql upgrade script, start the mysql servers, then fix any PHP issues (as php is upgraded from 4 to 5 as well). Comments please, -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Updating 4 to 5
Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updating from 4 to 5
nilnandan, I meant to say Mysqlupgrade :-) Further, I can use the old mysql tables, (the mysql gant tables, user/passwd/host tables etc) and the mysqlupgrade will update them as necessary? -Grant - Original Message - From: Nilnandan Joshi To: Grant Peel Cc: mysql@lists.mysql.com Sent: Tuesday, June 29, 2010 4:00 AM Subject: Re: Updating from 4 to 5 Grant, I think, you should run mysqlupgrade after copying old data in new servers. Regards, nilnandan Grant Peel wrote: Hi all, I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server version will be changed from 4 to 5. I am assuming I can load all the users tables, and the mysql database (grant tables and all) to the new server, then run mysqlupdate, and everything that needs to be updated will be. Any comments? -Grant
Updating from 4 to 5
Hi all, I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server version will be changed from 4 to 5. I am assuming I can load all the users tables, and the mysql database (grant tables and all) to the new server, then run mysqlupdate, and everything that needs to be updated will be. Any comments? -Grant
Bin Files.
Hi all, I had a server pretty much locked up this morning due to the mysql bin logs filling up the /var filesystem. I had been investigating the my.cnf settings file a white back, got sidetracked, and never finished it. The bin logs are named: /var/db/mysql/myserver-bin.01 /var/db/mysql/myserver-bin.02 /var/db/mysql/myserver-bin.03 /var/db/mysql/myserver-bin.04 I assume a new one is created each time the mysql server is started. I had to shut down all services on the server, delete all these files and restart everything. Anywho, the two questions are: 1. Are these files used when 'fixing' tables, or, are they only used for the master/salve replication? 2. Is it safe to, and how do I, turn off the bin logs altogether (there are no slave servers), 3. What my.cnf settings would you all reccomend for: Mysql4, running on, FreeBSD 6, 1 GB Memory, var fs is 5 GB, 250 virtual domains on the server, of which 50 may be using mysql/php (for bulliten boards etc). (I am reading through the /usr/local/share/mysql *.cnf files ...)
Permissions
Hi all, I run a shared Apache, Perl, PHP, Mysql, on FreeBSD environment. Here is a question: IF I have a user, that has no permissions, but with a decent password, (in the mysql 'Users' table), AND that user only has access to his/her database through the local host (i.e. perl or PHP scripts), IS it safe to grant 'All' privs to that user in the database grants table? -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help!
Hi all, I have a table I needed to prune because it had grown to 5 million rows and it appeared that that app that uses it couldnt anymore. I am in the process of: DELETE FROM mytable WHERE id 300; the above has been running for close than an hour and I am starting to get a little nervous. it is on freebsd 6.3, mysql 4.x on a Single core dell 2.8 GHx I think. I have shut down all process but necessary one so mysql has full access to the cpu. Should I be worried? Is there anything I can do to speed this up? -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to restart after crash
Have you looked at the results of netstat -an ? -Grant - Original Message - From: Ross Crawford To: mysql@lists.mysql.com Sent: Saturday, January 12, 2008 5:12 PM Subject: Unable to restart after crash Hi, My mysql server crashed last night, and when it rebooted, was unable to restart. Here is the error log: Jan 13 00:12:54 localhost mysqld_safe[1324]: started Jan 13 00:12:55 localhost mysqld[1327]: 080113 0:12:55 InnoDB: Database was not shut down normally! Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Starting crash recovery. Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Reading tablespace information from the .ibd files... Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Restoring possible half-written data pages from the doublewrite Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: buffer... Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 InnoDB: Starting log scan based on checkpoint at Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: log sequence number 0 111349. Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: Doing recovery: scanned up to log sequence number 0 111349 Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: Last MySQL binlog file position 0 3587, file name /var/log/mysql/mysql-bin.000489 Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 InnoDB: Started; log sequence number 0 111349 Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Recovering after a crash using /var/log/mysql/mysql-bin Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Starting crash recovery... Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Crash recovery finished. Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Do you already have another mysqld server running on port: 3306 ? Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Aborting Jan 13 00:12:57 localhost mysqld[1327]: Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 InnoDB: Starting shutdown... Jan 13 00:12:59 localhost mysqld[1327]: 080113 0:12:59 InnoDB: Shutdown completed; log sequence number 0 111349 Jan 13 00:12:59 localhost mysqld[1327]: 080113 0:12:59 [Note] /usr/sbin/mysqld: Shutdown complete Jan 13 00:12:59 localhost mysqld[1327]: Jan 13 00:12:59 localhost mysqld_safe[1374]: ended And since then I am unable to start it. mysqld_safe aborts with: Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[3150]: started STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[3164]: ended And error file: Jan 13 06:03:06 localhost mysqld_safe[1318]: started Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 InnoDB: Started; log sequence number 0 111349 Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Do you already have another mysqld server running on port: 3306 ? Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Aborting Jan 13 06:03:08 localhost mysqld[1321]: Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 InnoDB: Starting shutdown... Jan 13 06:03:11 localhost mysqld[1321]: 080113 6:03:11 InnoDB: Shutdown completed; log sequence number 0 111349 Jan 13 06:03:11 localhost mysqld[1321]: 080113 6:03:11 [Note] /usr/sbin/mysqld: Shutdown complete Jan 13 06:03:11 localhost mysqld[1321]: Jan 13 06:03:11 localhost mysqld_safe[1368]: ended Nothing is running on port 3306, telnet gets connection refused. No mysql processes are running. Does anyone have any ideas what might be wrong? Thanks ROSCO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Total Control Panel Login To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Remove lists.mysql.com from my allow list You received this message because the domain lists.mysql.com is on your allow list.
Virtual Hosting Privileges
Hi all, This is my first post to this list so bear with me if I ramble on a bit. I have been reading as much as I can on this subject and am yet to feel confident about an answer so I thought I would throw it at this list and see what comes back. One thing I should mention up front is I am in no way a SQL power user, although, I have enough reference material that I can work my way through most problems. We run a small web hosting and design service and have been offering MySQL as part of the basic setup for quite a while without any problems. One of the things we offer it PHPMyAdmin which has server us well so far. Our clients seem to use it as a browsing tool more that anything, but there are a few that might be called 'Power Users that use it for more SQL intense purposes. (FYI, I am using the PMA user, who only has 'select' privileges to the mysql users table). Recently, I noticed something that caused me to reflect on the privileges that I currently have setup on the server, and was wondering if I could get some feedback and/or some light tutoring on the topic of privileges. It is perhaps prudent to start with some setup info: FreeBSD 6.2 - Not using jails. MySQL 4.1 PHP 4.4 - Soon to be upgraded to 5.x Apache 2.2 PHPMyAdmin 2.11.0 What I am looking for at the end of this discussion is if my current privileges are secure enough, and, give all users the maximum functionality within the security model. All mysql users are alos unix users. When I setup the accounts, I use scripts to setup the mysql user at the same time the UNIX users is setup. That having been said, Users can not log into a shell of the operating system, the unix user is setup to give ftp access, and access to thier back end admin panel. When a new mysql user is setup: 1. the user is assinged a password but is given no privileges in the mysql 'users' table, but is allowed to connect from localhost so thier scripts can run; sampleuser localhost XXX None 2. his/her blank database is setup, and that user is given access to is from localhost, although I have a few users that have requested, and were granted connections (via the 'hosts' table) remote access. sampleuser_database sampleuser localhost Select | Insert | Update | Delete | Create | Drop | Grant | References | Index | Alter I suspect that I want to remove the 'Grant' access from the privileges above. Here is where my confusion escelates If i remove it, can the user still add tables? I suspect that I should consider removing the 'Drop' privilege. If I do that will the user be able to drop tables that he/she created? i.e. ones that reside in thier own database? Hopefully, you all can see my confusion. At the end of the day, I gues the question is: What privileges can I grant each user that will only allow them to completely manange thier own database without allowing them access to others databases? or What priveleges should I grant users at setup time? Thanks a million, -Grant P.S. I will also keep reading. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
