Category Archives: MySQL

Disable Reverse DNS Lookup in MySQL

Edit /etc/mysql/my.cnf as follow: [mysqld] skip-name-resolve[mysqld] skip-name-resolve Then restart mysql service: /etc/init.d/mysql restart/etc/init.d/mysql restart

MySQLi Persistent Connection

Here is an example of PHP MySQLi persistent connection: $mysqli = new mysqli("p:123.123.123.123", "user123", "123", "testdb1");$mysqli = new mysqli("p:123.123.123.123", "user123", "123", "testdb1"); Do not use “localhost” as host.

Make sure MySQL uses all available server RAM

To make sure MySQL uses all available server RAM: 1) Edit /etc/mysql/my.cnf 2) Increase innodb buffer pool size to 85% of RAM Example, if you have 64GB RAM: innodb_buffer_pool_size = 55Ginnodb_buffer_pool_size = 55G So InnoDB will load as much data it can in the buffer pool. 3) Increase the max connections: This is good for […]

MySQLi Cannot assign requested address

If you get this error message on your PHP script: Warning: mysqli::real_connect(): (HY000/2002): Cannot assign requested address in …Warning: mysqli::real_connect(): (HY000/2002): Cannot assign requested address in … Make sure to use a persistent connection “p:”: $mysqli = new mysqli("p:10.1.1.1", "user", "pass", "db");$mysqli = new mysqli("p:10.1.1.1", "user", "pass", "db");

Select Unique Values from MySQL Column

Use DISTINCT() to select only unique values from a MySQL column: SELECT DISTINCT(Email) AS Email FROM Users ORDER BY Email DESC;SELECT DISTINCT(Email) AS Email FROM Users ORDER BY Email DESC;

MySQL Got timeout reading communication packets

If you get this error in /var/log/syslog: mysqld[611]: 2017-01-21 11:03:14 12313188453123 [Warning] Aborted connection 12351011 to db: ‘clients’ user: ‘username’ host: ‘localhost’ (Got timeout reading communication packets)mysqld[611]: 2017-01-21 11:03:14 12313188453123 [Warning] Aborted connection 12351011 to db: ‘clients’ user: ‘username’ host: ‘localhost’ (Got timeout reading communication packets) Then there is a problem with the MySQL timeouts. […]

Increase MySQL Max Allowed Packets

Edit /etc/mysql/my.cnf as follow: [mysqld] max_allowed_packet = 1024M[mysqld] max_allowed_packet = 1024M Restart MySQL server: /etc/init.d/mysql restart/etc/init.d/mysql restart

Increase the MySQL timeouts

Edit /etc/mysql/my.cnf as follow: [mysqld] max_allowed_packet = 1024M connect_timeout = 300 wait_timeout = 300[mysqld] max_allowed_packet = 1024M connect_timeout = 300 wait_timeout = 300 More “unpopular” timeouts: net_write_timeout = 300 interactive_timeout = 300 net_buffer_length = 512M net_read_timeout = 300net_write_timeout = 300 interactive_timeout = 300 net_buffer_length = 512M net_read_timeout = 300 Restart MySQL server: /etc/init.d/mysql restart/etc/init.d/mysql restart

MySQL server has gone away

To solve this MySQL error: MySQL server has gone awayMySQL server has gone away You can try one or more solutions: 1) Increase /etc/mysql/my.cnf allowed packets [mysqld] max_allowed_packet = 1024M[mysqld] max_allowed_packet = 1024M 1) Increase /etc/mysql/my.cnf timeouts [mysqld] connect_timeout = 300 wait_timeout = 300[mysqld] connect_timeout = 300 wait_timeout = 300 2) Increase more /etc/mysql/my.cnf timeouts […]

Reset MySQL Root Password Linux

If you lost the MySQL root password you can reset MySQL root password: 1) Stop the MySQL service /etc/init.d/mysql stop/etc/init.d/mysql stop 2) Start MySQL without a password mysqld_safe –skip-grant-tables &mysqld_safe –skip-grant-tables & 3) Set a new MySQL root password use mysql; update user set password=PASSWORD("mynewpassword") where User=’root’; flush privileges; quituse mysql; update user set password=PASSWORD("mynewpassword") […]