MYSQL-Common-Problems.md

MySQL common solutions to irritating problems

Creating users.

How to create remote root user from bash script.

export MYSQLPASSWORD="-ppassword" # with password to root user, include parameter switch
export MYSQLPASSWORD=""           # without any password set to root user
export NEWUSERPASSWORD=""         # new user password

## Pre Mysql-8, oneliner to create a new database and a new remote account with access (no local access).
mysql -uroot ${MYSQLPASSWORD} -e "GRANT ALL PRIVILEGES ON newdatabase.* TO 'remoteroot'@'%' IDENTIFIED WITH mysql_native_password BY '${NEWUSERPASSWORD}';" 

## post Mysql-8 you now first need user then the grants.
mysql -uroot ${MYSQLPASSWORD} -e "CREATE USER 'remoteroot'@'%' IDENTIFIED BY '${NEWUSERPASSWORD}';" 
mysql -uroot ${MYSQLPASSWORD} -e "GRANT ALL PRIVILEGES ON *.* TO 'remoteroot'@'%';"
mysql -uroot ${MYSQLPASSWORD} -e "FLUSH PRIVILEGES;"

#  List users from terminal with their host and password plugin
mysql -uroot ${MYSQLPASSWORD} -e "SELECT Host,User,plugin FROM mysql.user;"

Config server to allow connections to all interfaces

[mysqld] 
bind-address = 0.0.0.0 # In versions prior to mysql 8 the bind to all interfaces is uncommented usually
bind-address = 127.0.0.1 # in mysql 8 its instead defined to only listen to local interface, still should be changed,

Default locations for mysql config file

Ubuntu and other debians: /etc/mysql/mysql.conf.d/mysqld.cnf

CentOS (correct me if i'm wrong here. /etc/mysql/mysqld.conf

note reload is not enough

sudo service mysql restart

check so its listening

sudo netstat -plutn | grep 3306

UTF8 - DUMP AND IMPORTING

When dumping and importing from/to MySQL in an UTF-8 safe way, Use -r to export and SOURCE when importing.

Do not do this, since it might screw up encoding

mysqldump -uroot -p database > utf8.dump # this is bad

Do this:

mysqldump -uroot -p database -r utf8.dump

Note that when your MySQL server is not set to UTF-8 you need to do mysqldump --default-character-set=latin1 (!) or whatever charset our server is to get a correct dump. In that case you will also need to remove the SET NAMES='latin1' comment at the top of the dump, so the target machine won't change its UTF-8 charset when sourcing.

If you only want to dump the structure without data, use

mysqldump -uroot -p --no-data database -r utf8.dump

Importing a dump safely

Do not do this, since it might screw up encoding also it doesent work on windows.

mysql -u username -p database < dump_file # this is bad

DO THIS:

mysql -uroot -p --default-character-set=utf8mb4 database

mysql> SET names 'utf8'
mysql> SOURCE utf8.dump

Firewall, UFW

If UFW and allow only from one ip

sudo ufw allow from 5.150.200.14/24 to any port 3306

If UFW and allow from all IPs

sudo ufw allow 3306

reload rule

sudo ufw reload

check rules

ufw status verbose