Tagged: mysql

“too many connections mysql” error occurred in MariaDB, CentOS 7

Problem: Too many connections error occurred and you can’t access via PHPMyAdmin or command line.

This is because no more connection can be made, so you have to stop MySQL service and change the setting.

Solution: first stop MySQL service, I user MariaDB so the service will be mariadb:

systemctl stop mariadb
systemctl start mariadb

then enter MySQL prompt via MySQL, and enter below query to check current maximum connection:

SHOW VARIABLES LIKE "max_connections";

The default value is 151. You can set a higher one like

SET GLOBAL max_connections = 1000;

Failed to remote connect to MySQL because of many connection errors;unblock witj ‘mysqladmin flush-hosts’

Problem: My window 10 want to remote connect to Linux MySQL server, but popup with the following error:

This is because the command uses host cache tables to exceed the maximum number of connections for a particular host, MySQL server will not able to make new connections.

Solution: enter Linux server command prompt, enter below command:

mysqladmin flush-hosts

or enter MySQL command prompt, enter below query:

FLUSH HOSTS;

Enable remote MySQL/MariaDB connection

Problem: Want an outside notebook/website able to remote connect AWS server’s database.

Solution: First allow all IP to access 3306 in the security group on the AWS portal.

then change my.cnf file, add bind-address:

[mysqld]
    ...
    skip-networking
    ...
    bind-address = <some ip-address>
    ...

the ip address should be 127.0.0.1 or localhost

At last, go to the mysql command-line client by entering mysql, and enter below command to grant access for outside IP address

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD' with grant option;

so that specific user from specific IP can remote connect to this database.

Run backup script to backup MySQL Database using cron

Problem: already add backup command in cron:

mysql -s -r -u root -e ‘show databases’ -N | while read dbname; do mysqldump -u root --complete-insert --single-transaction “$dbname” > /backup/sql/”$dbname”.sql; done

and add dbusername and dbpassword in ~/.my.cnf

[client]
user = dbusername
password = dbpassword

but fail to backup due to ‘INFORMATION_SCHEMA.GLOBAL_VARIABLES’ feature is disabled.

According to suggestion, enable MySQL 5.6 compatibility.

Solution: enter MySQL mode and enter

set global show_compatibility_56=on;

or add below directive to /etc/my.cnf :

show_compatibility_56=ON

Insert empty ” to integer type column cause error in MySQL

Problem: MySQL Error “Incorrect integer value for column.. ” when I want to insert empty string to integer type column.

It is because SQL_MODE being in STRICT mode. Normally it should work before MySQL 5.7

Solution: edit my.cnf file and remove strict mode value

First use

mysql --help | grep /my.cnf | xargs ls

to find my.cnf location

Then edit /etc/my.cnf, change sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES to

sql_mode=""

Then restart MySQL, for CentOS:

service mysqld restart

Can not uninstall MySql completely in CentOS 7

Problem: Want to download MariaDB via yum, but There have conflicts between MariaDB and MySql.

This a problem because these two RDBMS use the same port, simply just use one of them.

Solution: Completely uninstall MySql

First using

 yum list installed | grep mysql

to find out all packages that contain “mysql”

then remove one by one, below commend is to remove the first one(you can keep php-mysqlnd.x86_64 because it is PHP package)

sudo yum remvoe mysql-community-client.x86_64 -y

Then clean MySQL files inside /var/lib/mysql/ and /etc/

sudo rm -r /var/lib/mysql/*
sudo rm -rf /etc/my.cnf

Finally, install MariaDB

sudo yum install MariaDB-server MariaDB-client -y

Chinese Character from mysql turned into a question mark in PHP

Problem: already knew the solution below and follow them but still not work:

DB (phpmyadmin) -- change encoding to utf8_general_ci

Backend -- header(“Content-Type:text/html; charset=utf-8”); and mysqli_set_charset( “utf8”,$connect); added.

frontend -- <meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″>

Solution: Check again if the codes and methods are all correct. This time the
mysqli_set_charset parameter is not correct -- should be mysqli_set_charset($connect,”utf8″);

Secured By miniOrange