-4

Is it possible to restart MySQL using PHP?

I have a website which need to restart MySQL frequently because it use a lot of ressource. I have saved in this website the code source of a lot of webpage (about 3 000) and when I use my internal search form, he works lowely because there is a lot of informations to analyze, so after a search I need to restart my MySQL server.

Source PHP :

$sql = "SELECT * FROM site WHERE adresse LIKE ? || source LIKE ? ORDER BY ID DESC";
$requete = $bdd->prepare($sql);
$requete->execute(array("%".$_GET['recherche']."%", "%".$_GET['recherche']."%"));

Does exec allow to restart MySQL and how ?

Pierre HUBERT
  • 451
  • 7
  • 18
  • Restarting mysql is a kinda dirty fix. You want to find your 'resource leak' (e.g. missing close-calls to connections). If you can't find your the mistake, you might want to TIMEOUT connections that seem to be open for too long. – Manuel Arwed Schmidt Jun 28 '14 at 20:17
  • I think you are trying to cure the symptoms, not the actual illness. You should investigate the reasons why mysql is eating resources like it does. – didierc Jun 28 '14 at 20:18
  • 1
    OK, I'll search the problem in my code. – Pierre HUBERT Jun 28 '14 at 20:20
  • Hint: its most often a not called close() due to code path errors. Sometimes a return is preventing the call, sometimes your if/else statements don't let each case execute that line. But first of all, try to diagnose WHAT is causing the resource bloat. Is it dead connections ? Is it something else? – Manuel Arwed Schmidt Jun 28 '14 at 20:45
  • When I use my website my computer starts to run very lowely. – Pierre HUBERT Jun 28 '14 at 20:47
  • You might edit your question and add the code that is likely causing this; we might help you to spot it. – Manuel Arwed Schmidt Jun 28 '14 at 21:06
  • 2
    Read: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Martin Tournoij Jun 28 '14 at 21:26
  • What are you using to open up the db connection on $requete ? If its a framework or class, does that class have a 'destructor' which gets called? Also, how many entries are in the table ? LIKE's can be kinda expensive when deployed on a large scale comparision job.. – Manuel Arwed Schmidt Jun 28 '14 at 22:34

2 Answers2

3

Yes, it is possible. How depends on which OS you're running. One approach is the PHP exec function to execute a external program.

The command to be executed depend on the OS, as I said. Here are the command (If I'm correct, please tell me if I'm not):

Debian / Ubuntu:

/etc/init.d/mysql restart

Mac OS X

/usr/local/mysql/support-files/mysql.server restart

Windows

net stop MySQL
net start MySQL

On Windows MySQL might vary. If this does not work for you take a look at this question: restart mysql server on windows 7

Another approach might be through SSH using the SSH PHP extension, which needs to be installed first. Take a look at: http://www.php.net/manual/en/ssh2.installation.php

Then connect to the SSH server and execute the commands:

 $con = ssh2_connect("example.com", 22); // Connect to SSH server
 $exec = ssh2_exec($con, "/etc/init.d/mysql restart"); // Execute command

Hope this is helped you out ;)

Community
  • 1
  • 1
Melvin Koopmans
  • 2,994
  • 2
  • 25
  • 33
  • I didn't know I could use SSH with PHP. Thank you for your answer! – Pierre HUBERT Jun 28 '14 at 20:32
  • Connecting with SSH to the server with a kinda unsafe PHP Script (see mysql injection comment in #0), is probably the last thing you want to do. Also, why connecting with SSH to a 'localhost' ? – Manuel Arwed Schmidt Jun 28 '14 at 22:43
  • @ManuelArwedSchmidt he asked me for ways of doing this. SSH is one of them. I wouldn't use SSH for localhost as well. Can you explain me why this PHP script is unsafe? – Melvin Koopmans Jun 28 '14 at 22:56
  • This one seems ok. It's just that his initial post showed issues with string concatination which would be a big issue when passing such strings into exec() or sssh2_exec(). You might notice from the downvotes, that the SO community is disagreeing with restarting mysql for various reasons. That's why we didn't posted answers but tried to give tipps to the TO. – Manuel Arwed Schmidt Jun 29 '14 at 09:39
2

Exec will do the job only if system user that runs PHP (usually www-data) has rights to restart mysql daemon.

On *nix platforms you can achieve this by using visudo command and appending www-data ALL = NOPASSWD: /etc/init.d/mysql (platform-specific path to mysql)

However it is not recomended to give web server user right to do such thing and you perhaps should question that 'solution' with restarting mysql daemon.

Miloš Đakonović
  • 3,751
  • 5
  • 35
  • 55