mysql allow remote access from any host

Granting mysql access from remote is not secure, so it’s not recommendable but sometimes we need to do it. Here’s how to do.

  1. Login to mysql with root and create user with remote connect.
    GRANT ALL PRIVILEGES ON remote_db.* TO 'remote_user'@'%' IDENTIFIED BY 'passwordhere';
    
    FLUSH PRIVILEGES;
  2. We need to allow remote connection from mysql configuration. mysql.cnf or mysqld.cnf files locations are differ but in my case, it is located in /etc/mysql/mysql.conf.d/ folder.
    vi /etc/mysql/mysql.conf.d/mysqld.cnf And comment out  below line.
    #bind-address = 127.0.0.1
    After that mysql restart desired.
    service mysql restart
  3.  Now if you have any firewall or network security, allow the port. In my case, I needed to allow 3306 port in AWS inbound rule.
  4. Try to connect from remote.

    mysql -u {username} -p -h {ip address}

    mysql remote connection
    mysql remote connection

Mysql root password reset on Ubuntu

Stop mysql service

sudo /etc/init.d/mysql stop

Start without password

sudo mysqld_safe --skip-grant-tables &

Connect to Mysql

mysql -uroot

Reset password

use mysql;

update user set password=PASSWORD("passwordgoeeshere") where User='root';

flush privileges;

quit

Restart mysql

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Mysql error 1364 Field doesn’t have a default values

When get this error: mysql error 1364 Field doesn’t have a default values.

This happens when there’s key index but no values given in STRICT_TRANS_TABLES mode.

So we need to change sql_mode which is defined in my.cnf file.

When I search about the file, unfortunately I couldn’t find the file.

So rather than modify file, use query to change mysql configuration:

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

If this doesn’t work or it doesn’t allow you to query, you can modify mysql.cnf file.

vi /etc/mysql/conf.d/mysql.cnf

Then add these line

[mysqld] sql_mode = "ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And reboot mysql.

sudo service mysql restart

If still not working, add this disable_strict_mode.cnf file below location.

vi  /etc/mysql/conf.d/disable_strict_mode.cnf

Then add these 2 lines.

sql_mode = ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After reboot, check it by running this query:

SHOW VARIABLES LIKE 'sql_mode';

Host a website on Raspberry Pi in 30 mins

I have old Raspberry Pi and I want to use it for personal server to running my task manager software.

Install raspbian on SD card

sudo dd bs=1m if=path_of_your_image.img of=/dev/diskn

And turn on Raspberry and check ip address.

ifconfig

And I get 10.50.101.62

Connect using terminal.

ssh pi@10.50.101.62

Make apt-get up to date.

apt-get update

Install apache2

sudo apt-get install apache2 -y

Install php

sudo apt-get install php5 libapache2-mod-php5 -y

Install mysql

sudo apt-get install mysql-server

Install phpmyadmin

sudo apt-get install phpmyadmin
sudo nano /etc/apache2/apache2.conf
Include /etc/phpmyadmin/apache.conf
sudo /etc/init.d/apache2 restart
Access to http://10.50.101.62/phpmyadmin/

I have downloaded task manager from mytinytodo.net and install on the server.

Boom! Here’s my website.

It took about 40 mins to install and host a website and hosting is free, isn’t it cool?