How to know the character set and collation of mysql database, table and column

Here are the SQL commands to know the character set and collation of mysql database and tables/columns

mysql> use database_name;
mysql> show variables like "character_set_database";
mysql> select data_type,collation_name from information_schema.columns where table_schema='database_name' and table_name='table_name' and column_name='column_name';

Sphere: Related Content

Enable mysql to connect from desired remote IP

Recently I had to work on mysql server on Linux platform.

All our development was done on windows machine and everything went fine.

When we tried to migrate the same code to Linux platform, everything worked fine except that our program was not able to connect to mysql. It was continuously throwing this exception.

Caused by: java.net.ConnectException: Connection refused
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
        at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
        at java.net.Socket.connect(Socket.java:529)
        at java.net.Socket.connect(Socket.java:478)
        at java.net.Socket.(Socket.java:375)
        at java.net.Socket.(Socket.java:218)
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:254)
        at com.mysql.jdbc.MysqlIO.(MysqlIO.java:292)

However we were able to connect via mysqladmin as follows

#mysql -u username -h localhost -p password


After doing some google I found that mysql was actually not allowing remote connections from our program. It was only allowing the Unix socket connections in case of mysql admin, thats why it was able to connect to it.

I had to change few configuration parameters in mysql configuration files as follows to allow remote connections to mysql and restart it.

# vi /etc/my.cnf
Once file opened, locate line that read as follows

[mysqld]

Add the following line at the end of this block

bind-address=YOUR-SERVER-IP
#skip-networking

Where,

    * bind-address : IP address to bind to.
    * skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state

Restart the mysql server, enter:
# /etc/init.d/mysql restart

Sphere: Related Content