mysql-socket

MySQL cannot connect via localhost

On an Apache server, MySQL can’t connect when you use localhost, but it work when you chane to IP address 127.0.0.1

When you use “localhost”, it use socket for connecting to MySQL server, this is faster than using TCP/IP connection, that is used when you use IP address to connect to MySQL server.

First find out socket path. To do this login to MySQL server, run

mysql socket

See if you can connect using this socket with command

login to mysql using socket

In this cause, login to MySQL using socket worked.

I created a simple test PHP script to verify MySQL connection, it was able to connect to MySQL server using “localhost”.

Same script did not work when i try access it using web server. So the problem is web server user not able to connect to MySQL socket. You need to check permission for socket and parrent folders. In this case problem is fixed by running

You can verify enabling SSH access for web server user, then connect to MySQL using command line or try access socket file as apache user.

MySQL Socket Path in php.ini

When a PHP application use localhost to connect, PHP find location of socket from php.ini, you need to verify this path set in php.ini is same as the socket path used by MySQL server.

If path is differnt, you need to make it same. You can either modify php.ini or MySQL server config file.

EasyEngine Connect to MySQL Database

To Find MySQL root password on EasyEngine, run

Or

To connect to MySQL console, run

See EasyEngine

MySQL

MySQL ERROR 1193 Unknown system variable GTID_PURGED

When restoring MySQL backup taken on Amazon RDS, i get error

To fix this, open db.sql in text editor, remove the line

Another solution is take a new MySQL backup with –set-gtid-purged=OFF option and restore it.

mysql create database

MySQL create database

To create a database, you can use

Example

To create a database with specific charset use

You can use whatever character set you wish instead of utf8mb4.

Example

mysql create database

To delete a database, use

MySQL

MySQL 8

MySQL 8 use caching_sha2_password as default autentication method. Many mysql clients still not support this method. If you need to use old method, you can set autentication plugin as mysql_native_password. You can also set this as default method by editing my.cnf file.

To change a user to use mysql_native_password, run

MySQL

Disable MySQL bin log on Bitnami

if you have cloud sevrer with less disk space, it is better disable MySQL bin log as it take approx 3 GB of disk space on bitnami server.

To disable MySQL binlog, edit mysql config file.

Find

Add below

Here is what i have in the my.cnf

Now restart MySQL

You can now remove the file starting with binlog from folder /opt/bitnami/mysql/data

MySQL server has gone away

When restoring a MySQL database, i get error “MySQL server has gone away”.

To fix this, edit MySQL configuration file. Add following line under [mysqld] section.

Restart MySQL

If you use MariaDB

Related Posts

MySQL

Aria engine is not enabled or did not start

When starting MaraDB, i got error

To fix error, remove the file