MySQL – LINUXBG

Увеличаване броя на max_connections в MySQL

Posted on август 10, 2017 | by rutera | Leave a Comment

Проверяваме броя като се логнем с root в mysql

mysql> SHOW VARIABLES LIKE "max_connections";

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "max_connections";

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 150 |

+-----------------+-------+

1 row in set (0.00 sec)

стандарнто са 150.

Сега може да ги увеличим по следния начин

mysql> SET GLOBAL max_connections = 250;

1	mysql> SET GLOBAL max_connections = 250;

след това отиваме в конфигурацията на mysql – my.cnf

За Debian базираните системи обикновенно се намира в /etc/mysql/my.cnf

За CentOS и RHEL базираните системи обикновенно се намира в /etc/my.cnf

и добавяме следния ред

max_connections = 250

1	max_connections = 250

след което рестартираме mysql.

Резултата е

 SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 250   |
+-----------------+-------+
1 row in set (0.00 sec)

SHOW VARIABLES LIKE "max_connections";

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 250 |

+-----------------+-------+

1 row in set (0.00 sec)

Добавяне на база през конзола

Posted on юни 11, 2017 | by rutera | Leave a Comment

Качване на база през конзола

mysql -u USERNAME  -pPASSWORD DATABASE < file.sql

1	mysql -u USERNAME -pPASSWORD DATABASE < file.sql

сетване с различен енкодинг

mysql -u USERNAME  -pPASSWORD --default_character_set utf8  DATABASE < file.sql

1	mysql -u USERNAME -pPASSWORD --default_character_set utf8 DATABASE < file.sql

Mysql_stat

Posted on ноември 6, 2015 | by rutera | Leave a Comment

(PHP 4 >= 4.3.0, PHP 5)

mysql_stat — Get current system status

Warning

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi orPDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

Example #1 mysql_stat() example

<?php
$link   = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$status = explode('  ', mysql_stat($link));
print_r($status);
?>

<?php

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$status = explode(' ', mysql_stat($link));

print_r($status);

Array
(
    [0] =&gt; Uptime: 5380
    [1] =&gt; Threads: 2
    [2] =&gt; Questions: 1321299
    [3] =&gt; Slow queries: 0
    [4] =&gt; Opens: 26
    [5] =&gt; Flush tables: 1
    [6] =&gt; Open tables: 17
    [7] =&gt; Queries per second avg: 245.595
)

Array

(

[0] => Uptime: 5380

[1] => Threads: 2

[2] => Questions: 1321299

[3] => Slow queries: 0

[4] => Opens: 26

[5] => Flush tables: 1

[6] => Open tables: 17

[7] => Queries per second avg: 245.595

)

<?php
    mysql_connect("localhost", "username", "password");
    $array = explode("  ", mysql_stat());
    foreach ($array as $value){
        echo $value . "<br />";
    }
?>

<?php

mysql_connect("localhost", "username", "password");

$array = explode(" ", mysql_stat());

foreach ($array as $value){

echo $value . "<br />";

}

Outputs something similar to:

Uptime: 698
Threads: 1
Questions: 4
Slow queries: 0
Opens: 19
Flush tables: 1
Open tables: 12
Queries per second avg: 0.5

MySQL show status – show open database connections

Posted on ноември 6, 2015 | by rutera | Leave a Comment

MySQL „show status“ FAQ: Can you demonstrate how to use the MySQL show statuscommand to show MySQL variables and status information, such as the number of open MySQL connections?

I don’t have a whole lot of time today to give this a detailed discussion, but here is a quick look at some MySQL work that I did recently to show MySQL open database connections.

MySQL show status – Open database connections

You can show MySQL open database connections (and other MySQL database parameters) using the MySQL show status command, like this:

mysql> show status like 'Conn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 8     | 
+---------------+-------+
1 row in set (0.00 sec)


mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     | 
| Connections              | 8     | 
| Max_used_connections     | 4     | 
| Ssl_client_connects      | 0     | 
| Ssl_connect_renegotiates | 0     | 
| Ssl_finished_connects    | 0     | 
| Threads_connected        | 4     | 
+--------------------------+-------+
7 rows in set (0.00 sec)

mysql> show status like 'Conn%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Connections | 8 |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show status like '%onn%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| Aborted_connects | 0 |

| Connections | 8 |

| Max_used_connections | 4 |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_finished_connects | 0 |

| Threads_connected | 4 |

+--------------------------+-------+

7 rows in set (0.00 sec)

All those rows and values that are printed out correspond to MySQL variables that you can look at. Notice that I use like 'Conn%'in the first example to show variables that look like „Connection“, then got a little wiser in my second MySQL show status query.

Continue reading →