I have the files .myd .myi and .frm files in a archive .tar.gz
My host do not provide shell access.
Is the any way that i can convert the files into .sql queries
Or is there any method other that using shell access.
Or is there any software to convert the database files into .sql query in my desktop computer so that i can restore it using phpmyadmin
My site has been down from several days
Our local authority is looking into using MySQL database for some internal applications and possibly our website.
Do we need a commercial or enterprise license for this particular use or can we use a free version?
What is the good value to set query_cache_size. I have set it to 60 M and here
is the status. Is it good? I am not sure how to check this? Can anyone explain?
query_cache_size=60M
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 5 |
| Qcache_free_memory | 62666440 |
| Qcache_hits | 922 |
| Qcache_inserts | 381 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 565 |
| Qcache_queries_in_cache | 182 |
| Qcache_total_blocks | 400 |
+-------------------------+----------+
8 rows in set (0.00 sec)
some vBulletin customers facing to that error message, with session table with attachment only. Other just work fine. It's not because of server load. The load is just around 0 - 0.1
I have increased wait_timeout to 200 and max_allowed_packet to 64MB. Each attachment file in those vBulletin is just around 1-3MB.
Is there any idea why and how to fix?
We have a dedicated server and we host 4 news portals based on e107 and joomla.
My partner on Sunday has upgraded mysql from version 4 to 5. Now the server experiences very high CPU usage and query time on portals has been increased very much.
We used to have query times lower than 1 sec for the main page of [url](which is a site that receives about 2000 visits/day) and now query times are 5-15sec !
Does anybody know if this is makes sense ?
Below is the server Status from WHM :
-------------------------------------
cpdavd up
cpsrvd up
exim (exim-4.69-15.1_cpanel_maildir) up
ftpd up
httpd (2.2.11 (Unix)) up
imap up
interval up
mysql (5.0.67-community-log) up
named (9.3.4) up
pop up
rsyslogd up
spamd up
syslogd up
Server Load 6.97 (1 cpu)
Memory Used 49.4 %
Swap Used 0.23 %
Disk /dev/hda3 (/tmp) DMA+ 70 %
Disk /dev/hda5 (/) DMA+ 22 %
Disk /dev/hda1 (/boot) DMA+ 14 %
Below is the Server Information from WHM :
------------------------------------------
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Core(TM)2 Duo CPU E4600 @ 2.40GHz
Processor #1 speed: 2394.068 MHz
Processor #1 cache size: 2048 KB
Memory Information
Memory: 2064420k/2087424k available (2091k kernel code, 21752k reserved, 846k data, 236k init, 1169920k highmem)
System Information
Linux server.aktoweb.biz 2.6.20-1.2319.fc5 #1 Mon May 21 19:52:13 EDT 2007 i686 i686 i386 GNU/Linux
Physical Disks
hda: WDC WD3200AAJB-00TYA0, ATA DISK drive
hda: hw_config=600d
hda: hw_config=600d
hda: max request size: 512KiB
hda: 625142448 sectors (320072 MB) w/8192KiB Cache, CHS=38913/255/63<6>hda: hw_config=600d
hda: cache flushes supported
Current Memory Usage
total used free shared buffers cached
Mem: 2066428 1756324 310104 0 339576 486636
-/+ buffers/cache: 930112 1136316
Swap: 1052248 2428 1049820
Total: 3118676 1758752 1359924
Current Disk Usage
Filesystem Size Used Avail Use% Mounted on
/dev/hda5 288G 60G 213G 22% /
/dev/hda1 99M 13M 82M 14% /boot
tmpfs 1009M 0 1009M 0% /dev/shm
/dev/hda3 494M 215M 254M 46% /tmp
Below is the CPU/Memmory/Mysql Usage from WHM :
-------------------------------------------------
mysql42.707.300.0
Top Process%CPU 42.7/usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.aktoweb.biz.pid --skip-external-locking
Top Process%CPU 42.6/usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.aktoweb.biz.pid --skip-external-locking
giaprakigiapraki.com2.7012.111.7
Top Process%CPU 1.3httpd [urll] [/e107_plugins/pm/pm.php?inbox]
Top Process%CPU 1.1httpd [url] [/e107_plugins/forum/forum_viewforum.php?87]
Top Process%CPU 1.0httpd [url] [/e107_plugins/chatbox_menu/chatbox_iframe.php]
adsnetadsnet.gr1.177.900.7
Top Process%CPU 0.5httpd [adsnet.gr] [/www/delivery/lg.php?bannerid16&campaignid4&zoneid5&chan]
Top Process%CPU 0.4httpd [adsnet.gr] [/www/delivery/lg.php?bannerid50&campaignid6&zoneid15&cha]
Top Process%CPU 0.3httpd [adsnet.gr] [/www/delivery/lg.php?bannerid183&campaignid77&zoneid1&ch]
r40r40.gr1.073.340.0
Top Process%CPU 1.1httpd [r40.gr] [/index.php?optioncom_fireboard&Itemid188&funcshowcat&cat]
Top Process%CPU 0.5httpd [r40.gr] [/images/fbfiles/avatars/s_64.png]
Top Process%CPU 0.3httpd [r40.gr] [/index2.php?optioncom_jim&taskxml&no_html1]
root0.278.532.0
Top Process%CPU 0.2[kjournald]
alex123alexandroupoli.net0.030.270.0
Top Process%CPU 0.1httpd [alexandroupoli.net] [/e107_images/banners/banner468x60-alexandroupolinet.gif]
galfasgalfas.gr0.030.270.0
Top Process%CPU 0.1httpd [galfas.gr] [/racing/images/galfas_R40_banner.jpg]
eximstats0.000.000.3
nobody0.001.100.0
mailnull0.000.000.0
unauthenticated0.000.000.0
named0.000.10
how to enable slow-query logging in MySQL ? Im using mysql version 4.1.20
well i just added following line in my.cnf can you please tell me is it correct way or not?
Code:
log-slow-queries=/tmp/slow_queries.log
log-update = /var/lib/mysql/updates.log
I want to run flush query cache, saying once a week. Is there anyway to schedule it, so system can do it automatically? Currently, I have to login server, and run it manually
View 1 Replies View Relatedhow to add a mysql access host by command line? or or how to add a mysql access host through an sql query?
View 3 Replies View RelatedI've got a problem with an SQL query that is behaving badly. Its probably really obvious, but I can't see the problem!
I have a table with 3 fields:
Code:
money bigint(20)
income int(12)
userID mediumint(7)
I have a row that has a negative money and a value of 0 for income. When I update the money value to add "0" to it, nothing happens (as expected). When I update the money field to add the income field (which is equal to 0) to it, MySQL flips the sign to make the money field positive.
The following queries show the problem:
Code:
mysql> SELECT money, income from users where userID=327961;
+----------------------+--------+
| money | income |
+----------------------+--------+
| -9223372036854775807 | 0 |
+----------------------+--------+
1 row in set (0.00 sec)
mysql> UPDATE `users` SET money = money + 0 WHERE userID =327961;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> SELECT money, income from users where userID=327961;
+----------------------+--------+
| money | income |
+----------------------+--------+
| -9223372036854775807 | 0 |
+----------------------+--------+
1 row in set (0.00 sec)
mysql> UPDATE `users` SET money = money + income WHERE userID =327961;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> SELECT money, income from users where userID=327961;
+---------------------+--------+
| money | income |
+---------------------+--------+
| 9223372036854775807 | 0 |
+---------------------+--------+
1 row in set (0.00 sec)
mysql>
Notice that there is no negative sign after the third select!!
So, we are saying that executing an update where 0 is specified in the query results in the correct action but if you take the 0 from another field it takes the negative sign away...
This for various reasons is a rather urgent problem. This problem has only appeared after upgrading from MySQL 4.0 to MySQl 5.1. This problem does not occur in MySQL 4.1.
I recently had a harddrive failure and luckliy I can still access certain directories on this failed drive. I can still access the /var/lib/mysql/ directory which holds all the users databases and have backed all these up separately using tar.
Now what I need to know is how do you restore these database files to another server? I tried simply untar'ing one of these to the new servers /var/lib/mysql/ direcotry and it stuffed Mysql up - it went offline. I had to get a cpanel tech to bring Mysql back online.
how can I get these database files to fully work on a new server?
The mysql dir on my server seems to fill up sometimes and I see these files that aren't actually DB's. They all vary in size.
Quote:
u15149596-bin.000001
u15149596-bin.000002
u15149596-bin.000003
u15149596-bin.000004
u15149596-bin.000005
u15149596-bin.000006
u15149596-bin.000007
u15149596-bin.000008
u15149596-bin.000009
u15149596-bin.000010
u15149596-bin.000011
u15149596-bin.000012
u15149596-bin.000013
u15149596-bin.000014
u15149596-bin.000015
u15149596-bin.000016
u15149596-bin.000017
u15149596-bin.index
I can remove them and I see no problems with the server after that. Does anyone have any idea what these are?
I'm running Plesk 8.0.1
Basically mysql is behaving very very intermittently. Crashes were every 4 hours, I've brought them down to once every 8 or so hours but mysql keeps dying.
the error log will show the same routine each time.
on mysql start:
Quote:
091101 21:58:03 [Warning] option 'open_files_limit': unsigned value 120000 adjusted to 65535
091101 21:58:03 [Warning] Could not increase number of max_open_files to more than 65535 (request: 200110)
091101 21:58:03 [Note] /usr/sbin/mysqld: ready for connections.
Then we'll see errors due to crashed databases:
Quote:
091102 0:33:07 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './<nameofdatabase.frm>'
following this a heap of:
Quote:
091102 0:36:35 [ERROR] /usr/sbin/mysqld: Can't open file: '>another database here.frm>
091102 0:36:36 [ERROR] /usr/sbin/mysqld: Sort aborted
091102 0:36:52 [ERROR] /usr/sbin/mysqld: Sort aborted
091102 0:43:00 [ERROR] Error in accept: Too many open files
I did the absolutely most stupid thing yesterday, I emptied the wrong mysql table via phpmyadmin. Fortunately my host managed to grab a the table in question from a few days ago.
So I have:
auctions.frm, auctions.MYD, and auctions.MYI
My assumption, is that I can just overwrite the current files of the same name in the mysql folder, and everyone will be happy. Is there some more to this process?
I been having a heck of a time just with this one cpanel server and open files limit. At first using open_files_limit did not work so I changed it to open-files-limit that seemed to work but now it rejects the value and sets its down to 65535.
Then system open files limit is 500000. I try to set it to any value about 65535 in my.cnf and here is usual error:
090630 9:32:07 [Warning] option 'open_files_limit': unsigned value 120510 adjusted to 65535
090630 9:32:07 [Warning] option 'open_files_limit': unsigned value 120510 adjusted to 65535
When I run something like the tuning-primer it shows:
Current open_files_limit = 120510 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
But Im not sure if it is just reading my.cnf or something. I am still getting complaints from users about lost connections and I see the errors in error log. Ive looked everywhere and cant seem to find a solution to this.
Is there a way to restore a MySQL database from the files located in /var/lib/mysql?
Unfortunately I don't have a mysqldump of the databases, just the raw files from an image of the server.
I'm trying to use the "mysqldump" command to get a copy of all databases on the server...
but I need each database to go into a "databaseXYZ.tar.gz" file.
Does anyone know how to do this? So far, I can only get all the databases into a single file. But I need each database to have its own file.
I have following problem-i have over 20 sites on server and each site has own database.Is there a way to speed up backup and transfer or other server?
Method which i use right now it's following:first i archive entire directory using command tar -pczf name.tar.gz public_html,and then repeat with each directory.But i think i could simply archive all requied directories,but that will took toomuch time,so if i drop connection during archiving it wont be archived at all then.So i think best solution will be to create some kind of batch command which can load in background,so that means command wont stop if client lost connection.
So let's say i have 2 sites and two directories located and different places.
One is at home/site1 and other at home/site2
So i think i would need to put command into batch file
tar -pczf site.tar.gz. /home/site1 and tar -pczf site2.tar.gz /home/site2
Will that work? Also second part,mysql databases,i founded if i login into phpmyadmin as root i can see all databases.I managed to export all databases,but question will import again to phpmyadmin work.I think phpmyadmin create command for each database "if there is no db sitename_mysqlbase,create it",but howmuch i know phpmyadmin have limit with importing size of mysql database.Could that be done with import/export mysql command?
I need a command way of backing up my website files and Mysql databases. I no longer have access to the static ip to access the panel.
View 1 Replies View RelatedI run a small Fan community / forum that I am looking to move to a VPS server as these are a little cheaper than Dedicated
I have a couple of questions & wondered if anyone could assist?
I need a Control panel as I have had experience with Plesk & Cpanel & do not have any SSH experience whatsoever
I see Cpanel offered in some VPS offers, Would this include things like Fantastico etc?
Would I better suited to a Managed or Unmanaged account? I assume seeing I have no SSH knowledge Managed account would be better
I had looked at a site called nameway dot com & like the look of their VPS offer but would love to hear other peoples thoughts on them (I haven't posted enough to post a url so site is
www dot nameway dot com/resellers-hosting.htm )
If I were to go unamanaged or part managed if there is such a thing, would this mean I would have to upload things like apache for php, mysql etc? or would this still be standard in an unmanaged account if a cpanel or control panel is offered?
check the dns settings on my server?
I have a feeling there's something not quite right with it
I ran DNSreport.com's query and it said there were a few problems which might cause slowdowns (Which is what i'm experiencing), but didn't go into any detail on how to fix it
They've now put in a fee for doing anymore dns checks, so i'm abit in the dark as to what to do?
I'm running a WHM/Cpanel VPS at present.
Am just about to emigrate to USA (yay) and I want to keep my xxxxxxxx.co.uk domain active so I can recieve emails etc. I would also like to register a yyyyyyyyy.com domain.
Can anyone advise me on this please?
Can I host a .co.uk in the US?
Can I host a different .com and .co.uk on the same servers?
Can anyone recommend a good reliable, cheapish ($10-$20per month) host that provide MSSQL and asp.net 2.0 support.
I have a dedicated server with the 'typical' 2 ips pointing to the same instance of BIND on it for its nameservers.
I would like to actually have an ns2 to remove this single point of failure. Even though my 'infrastructure,' such as it is, is only a single server; my research on here and elsewhere suggests that this separate DNS server will make my site appear to be non-responsive when the server is down rather than simply non-existent. This is the behavior I would like to have!
I am still left with questions on implementing this. I know I need either a small VPS account, or an account with a provider such as DNSMadeEasy.com. What is not clear to me is how I would set up such an arrangement with the separate DNS server.
Does anyone have a link to a resource that shows how to do this, or would be willing to do a quick run through here?
I am not technically incompetent but am unfamiliar with this procedure and cannot seem to find documentation.
I've got a VPS, and its performance sucks.
I've got 18 domains parked on it, with only 4 of those having active websites. There are 3 mailman lists set up, and a further 10 or so email accounts with SpamAssassin active on them.
It also runs cPanel/WHM.
The server itself has 384Mb RAM, with 512Mb burst.
My beancounters are.....
Version: 2.5
uid resource held maxheld barrier limit failcnt
2102: kmemsize 8406834 10504002 30000000 30000000 2722182
lockedpages 0 0 256 256 0
privvmpages 111738 132127 131072 131072 1034096
shmpages 731 747 21504 21504 0
dummy 0 0 0 0 0
numproc 73 96 240 240 0
physpages 55335 75477 0 2147483647 0
vmguarpages 0 0 65536 2147483647 0
oomguarpages 55335 75477 26112 2147483647 0
numtcpsock 24 47 360 360 0
numflock 9 20 188 206 0
numpty 1 1 16 16 0
numsiginfo 0 17 256 256 0
tcpsndbuf 214656 1750788 53687296 61551616 0
tcprcvbuf 393216 786996 53687296 61551616 0
othersockbuf 30416 275664 53687296 61551616 0
dgramrcvbuf 0 55460 53687296 61551616 0
numothersock 24 42 360 360 0
dcachesize 0 0 2273280 2416640 0
numfile 2078 2449 5820 5820 0
dummy 0 0 0 0 0
dummy 0 0 0 0 0
dummy 0 0 0 0 0
numiptent 10 10 128 128 0
Am I putting too much strain on the VPS with the amount of domains/emails I run through it? Is cPanel/WHM the problem? Is the server config suspect? Are VPS accounts only good for 1 or 2 domains?
I been logging queries for a whole 3 days and my logs is huge and the problem seems to be my phpBB search table thats causing such problems for me heres what I logged
Code:
# Time: 070219 19:41:53
# User@Host: admin_db[admin_db] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 13274745
SELECT word_id
FROM phpbb_search_wordmatch
GROUP BY word_id
HAVING COUNT(word_id) > 259756;
Everything from phpbb_search_wordmatch takes more then 11 sec Query time.
How can I optimze this and lower the Query time to something like 1 or 2.
Some adivce would be helpful me not that good with mysql.
I currently have my jimpix site hosted by a really good Canadian company called Alentus. BUT, they are based in Canada, and I'm based in the UK.
I am working on a site at the moment which will require a timetable / scheduling facility, so working in the 7 hour time difference between the UK and Canada is a pain.
The package I need is:
ASP Hosting
MySQL Hosting
Use of ASPJPeg and ASPUpload Components
I've not been able to find a UK hosting firm offering these - or haven't spent hours looking for one, so wanted to check here if anyone could recommend a UK firm offering these - and a good one if possible! I have used other hosts before with other web clients, and some have been pretty ropey to say the least!
what does this mean? its been flooding /var/log/messages
Jun 28 08:12:50 host named[7649]: client 209.86.63.238#9427: query (cache) 'root.domain.tld/A/IN' denied
Jun 28 08:12:50 host named[7649]: client 209.86.63.230#42462: query (cache) 'root.domain.tld/A/IN' denied
Jun 28 08:12:50 host named[7649]: client 200.23.242.203#37863: query (cache) 'root.domain.tld/A/IN' denied
Jun 28 08:12:50 host named[7649]: client 212.93.151.237#8080: query (cache) 'root.domain.tld/A/IN' denied
Jun 28 08:12:50 host named[7649]: client 212.202.215.18#35119: query (cache) 'root.domain.tld/A/IN' denied
Jun 28 08:12:50 host named[7649]: client 212.93.151.237#40106: query (cache) 'root.domain.tld/A/IN' denied
Jun 28 08:12:50 host named[7649]: client 209.86.63.231#51272: query (cache) 'root.domain.tld/A/IN' denied
what causes an imbalanced load on a server? what/who makes a demand that causes something to be unbalanced?how is the problem solved?
View 1 Replies View Related