# MySQL

# Linux auto-login on MySQL

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

```shell
[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/](https://easyengine.io/tutorials/mysql/mycnf-preference/)

# Encoding and collation

Changing database encoding and collation

```SQL
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](https://pt.stackoverflow.com/questions/72139/qual-codifica%C3%A7%C3%A3o-de-caracteres-collation-devo-usar-em-mysql)

# Exporting data

To export structure only, use this option on mysqldump:

```SQL
mysqldump [...] --no-data
```

To export command result to csv file format:

```SQL
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 

```lang-sql
show table status;
```

### Purging MySQL Binlog files

##### To show binary logs

```lang-sql
mysql> SHOW BINARY LOGS;
```

##### To Purge binary logs manually until some point

```lang-sql
mysql> PURGE BINARY LOGS TO 'binlog.000776';
```

##### To show binary logs

```lang-sql
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://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](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html)

# Security

Iptables configuration to allow specific host connection to mysql:

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