Backups
Hello all. I am looking into the different back up methods available for safeguarding my MySQL databases. Ideally, I would like to have dedicated machine on our network which all data is dumped to periodically. The tool I would like to use is MySQL Administrator although I could also use Navicatt. We run Mac OS X/MySQL 4.0.21. What I am a bit confused on is the issue of record locking. If I have a schedule to dump the db structure with data 1 per day, and some of the tables may contain over a million records, the dump time is rather lengthy. During this time, if clients and all automated processes (reading and writing) scheduled to execute during this time, are locked out, what do they receive when they load a web page with data originating from this table? Furthermore, would it make more sense to have the data dump locally, and then use a script to move the contents of the dump to a machine on the network, perhaps even to a machine located on an alternate network accessed via a second ethernet card? Finally, after reading about replication, this seems most attractive as I would have a duplicate of the existing server from which my backups could be performed. This way my data is always available online to customers. However, if I read correctly, does this mean I will need 1 additional machine of at least equal speed to achieve this? How do others handle replication issues when you have several MySQL servers. . . having 1 machine for each seems like an expensive solution but it also seems like the most flexible. Any input is appreciated. -Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Experienced MySQL Admin Needed
I am looking for an experience MySQL administrator in the Connecticut area to help us with our MySQL configuration. ideally, we need someone experienced with configuring automated backups, server optimization, query optimization, replication and troubleshooting. We operate OS X (10.3.9)/ MySQL version 4.0.21 Complete MySQL from Server Logistics. We would be looking for the administrator to come to our location in Branford, CT to view our configuration and make modifications and suggestions. If you are available, contact me off list with your location and what your fees would be as well as your work history/experience. Thanks. Rick Rick Dwyer Computer Operations Manager Quick Link Information Services --- [EMAIL PROTECTED] ph: 203-483-2922 fx: 203-483-2920 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dropped table. . . Help Please
I made a major mistake with MySQL 4.1.x. While using Navicatt I dropped my database when I meant to drop a table. Other than backups which are not that up to date, is there an undo? Help here is greatly appreciated. Thanks, I'm desperate. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A bit of SQL help for a MySQL novice.
Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. Thank you. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A bit of SQL help for a MySQL novice.
Unfortunately, the phone numbers come from text logs that get imported into mysql. Because the phone number is often displayed on a document for the customer, they will dictate how they want it to appear (i.e. with ( ) etc.). The phone logs simply record those values as they are so data will be entered in an unclean manner. Therefore I must deal with it on the backend. Thanks for the pointers. Rick On Nov 17, 2005, at 11:15 AM, Rhino wrote: - Original Message - From: Rick Dwyer [EMAIL PROTECTED] link.com To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 10:28 AM Subject: A bit of SQL help for a MySQL novice. Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. You have two basic options: 1. Make the data uniform in format so that it is easily accessed. 2. Let the users input the data in whatever format they like and then try to deal with it. It looks like you have opted for the second choice. If it were me, I'd _strongly_ prefer the first choice. I would put edits on the forms or applications that prompt the user for the phone number and force the input to match one format. For instance, if you prefer to see the phone number as one long string of numbers, e.g. 12025551212, either ignore any characters they type that aren't digits or strip out the punctuation characters afterwards. By the way, I'm _not_ saying that you should store the numbers as one long string; there are other options but I would choose the one that was going to be most useful to you based on your business requirements. If the area code is going to be important to you, as it appears from your question, it might be a good idea to store it in a separate column. For instance, you could put the country code (the '1') in a Country_Code column, put the area code in an Area_Code column, put the 7 digit number in its own column, and then put the extension (if applicable) in yet another column if that would help you. Beware of foreign phone numbers though because they don't look like US ones (and don't make the mistake of thinking that the '1' at the beginning of the phone number automatically means the US; I'm in Canada and our phone numbers also start with 1, our area codes are also three digits, and the rest of the number is also 7 digits. Phone numbers in Europe or Africa or Asia follow rather different patterns that are shared by Canada and the US.) Now, your input routines _could_ mimic the way you store the phone numbers. For instance, if you want separate columns in the database for country code, area code, the rest of the number, and the extension (if any), you _could_ provide a separate field in your input form for each of those things. However, you don't have to do it that way; you could just as well put the full phone number in one input field and then split it out when you insert it into the database. That's up to you. But I would definitely use the input routines to force the phone numbers to follow whatever pattern you want it to have. It shouldn't be the database's job to handle this sort of thing, at least in my opinion. Of course, you'll want to fix the data that is already in the database, too. (If there are only a few rows in the table, you could do that manually. If not, you could write SQL to do it.) However, if you insist on allowing multiple formats for your phone numbers, the String Functions in MySQL should help you. Just look for them in the manual: http://dev.mysql.com/doc/refman/4.1/en/ index.html (chapter 12). You may have to use a combination of functions to create new temporary versions of the phone number that don't have the punctuation but you can probably manage something, although it might be ugly. Another possibility is that you could write a user-defined function to strip the punctuation out of the phone numbers. See http:// dev.mysql.com/doc/refman/4.1/en/adding-functions.html
ODBC Initial Setup Problems
Hello All: I am trying to configure ODBC for MySQL but have been unable to make a connection. We are running MySQL 4.0.15 supplied by Server Logistics on OS X 10.3.5. I have installed their ODBC Driver and 4 files show up in the library/MyODBC/Lib/ directory: libmyodbc3_r-3.51.06.bundle libmyodbc3_r.bundle (alias) libmyodbc3-3.51.06.bundle libmyodbc3.bundle (alias) Under the ODBC Admin I have added a driver with the name mysqlreporting specifying the driver file path as : /Library/MyODBC/lib/libmyodbc3-3.51.06.bundle. Didn't know what to put for setup file so the path is the same. I then setup 3 key values: keyword Value useruserloginname passworduserpasswordname databasemydatabase Under UserDSN, I add the above driver naming the data source name also mysqlreporting. I also enter the same keyvalues. When I go to test the connection locally, I get the following: Last login: Fri Nov 5 09:26:40 on ttyp1 Welcome to Darwin! My-Computer:~ myuserlogin$ /usr/bin/odbctest iODBC Demonstration program This program shows an interactive SQL processor Enter ODBC connect string (? shows list): ? DSN| Description --- mysqlreporting | mysqlreporting Enter ODBC connect string (? shows list): dsn=mysqlreporting [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQLSTATE=IM002 Have a nice day. My-Computer:~ myuserlogin$ Can someone explain what I am doing wrong. Because their are two different drivers in the MyODBC Lib folder, I tried both but get the same message. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODBC Initial Setup Problems
On 5 Nov 2004 at 10:01, Rick Dwyer wrote: Hello All: I am trying to configure ODBC for MySQL but have been unable to make a connection. Hi, I have no experience of Mac OS X, but I believe it is linux-ish which means case- sensetive path names. The line below shows library with a lower case L: We are running MySQL 4.0.15 supplied by Server Logistics on OS X 10.3.5. I have installed their ODBC Driver and 4 files show up in the library/MyODBC/Lib/ directory: ^ Thanks Ian, but the capital L is just how I typed it in the email. The actual path is picked with a picker button so the case is set properly. Thanks though. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL command timeout
Hi all. I have a lasso page with various Inlines to execute, updates, searches, deletes and SQL commands to summarize data from a database with several tables that have grown large and the query to the database lasts for several minutes during this process. I have just started noticing that it is no longer summarizing all the records. When I took out some inlines to shorten the length of time it takes to execute, all the records once again show up (just some of the data for each record is not summarized because I took out the respective inline). It looks like MySQL has a timeout for length of time a complex query can be made to it. If this is the case, what do other people do who have run into this situation? We run MySQL 4.0.15 on Mac OS X Server (10.3) Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL command timeout
Check your mysql system variables for timeout variables (SHOW VARIABLES;) Thanks Jay. After I do this I see there are a number of timeout values, none which refer to a sql command timeout. Are you able to pinpont which value I need to manipulate? Also, if I increase this value, what are the drawbacks of doing so (i.e. performance, etc.) Thank you again. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting up MySQL on Raid Mirror
Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL over Raid Mirror
Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) My config is a Mac Dual Processor G4 1.25 Ghtz running 10.3 with MysQL 4.0.15. both drives are 120GB 7200RM IDE Hitachi Deskstar's. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Setting up MySQL on Raid Mirror
Assuming its not a super-high performance situation, I would put everything on the RAID 1 volume. Richard, what would you define as super-high performance? This MySQL database server will serve as the backend for a Lasso/ OS X Apache webserver handling thousands of hits per day. Will installing OS and MySQL on the Mirrored Drive be within the scope of that type of activity? The database basically collects customer data. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing special characters via text file
Hello all. Any help on the following matter is greatly appreciated. I tried to import a text file containing numerous special characters (À, Æ, Ç , etc.) but they convert to incorrect characters. I can paste them and write them correctly using a form submission created with LP6. However, any import corrupts the characters. Do they require special encoding? The manual had very little info on the matter of special characters. We are running MySQL v4.0.13 on OS X (10.2.8). Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
