MySQL

Linux auto-login on MySQL

Create file ~/.my.cnf and add the following lines, replace mysqluser and mysqlpass values with the correct ones

[client]
user=mysqluser
password="mysqlpass"

For safety, make this file readable to you only by running chmod 0600 ~/.my.cnf 

Next time you run mysql commands mysql, mysqlcheck, mysqdump, etc; they will pick username & password from this file if you do not provide them as argument (-u and -p). It can save your time.

Of-course, if you specify username and password explicitly as part of commands arguments, they will be used.

https://easyengine.io/tutorials/mysql/mycnf-preference/

Encoding and collation

Changing database encoding and collation

ALTER DATABASE `sua_base` CHARSET = Latin1 COLLATE = latin1_swedish_ci;

https://pt.stackoverflow.com/questions/72139/qual-codificação-de-caracteres-collation-devo-usar-em-mysql

Exporting data

To export structure only, use this option on mysqldump:

mysqldump [...] --no-data

To export command result to csv file format:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

 

Useful commands

Show status 

show table status;

Purging MySQL Binlog files

 

To show binary logs
mysql> SHOW BINARY LOGS;
To Purge binary logs manually until some point
mysql> PURGE BINARY LOGS TO 'binlog.000776';
To show binary logs
mysql> SET GLOBAL binlog_expire_logs_seconds = 259200;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST binlog_expire_logs_seconds = 259200;
Query OK, 0 rows affected (0.01 sec)

 

https://askubuntu.com/questions/1322041/how-to-solve-increasing-size-of-mysql-binlog-files-problem

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

 

Security

Iptables configuration to allow specific host connection to mysql:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -s IP.ADD.RE.SS -j ACCEPT