===== MySQL =====

==== Install ====

<code>
apt-get install mysql-server
</code>

==== Configure ====

Execute the following after the installation
<code>mysql_secure_installation</code>

You can find the config in
<code>
/etc/mysql/mysql.conf.d/mysqld.cnf
</code>

==== Passwordless login ====

If you want to automate things you might want to login without a plaintext password.

=== Add profile ===

:!: WARNING: YOU SHOULD NOT ADD ROOT AS A PROFILE, THE PASSWORD STORE IS NOT SECURE :!:

Add a profile to your MySQL config
<code>mysql_config_editor set --login-path=<YOURPROFILENAME> --host=<YOURHOSTIP> --user=<YOURUSERNAME> --password</code>

You can now login via
<code>mysql --login-path=<YOURPROFILENAME></code>

=== Show profiles ===

All profiles can be listed via
<code>mysql_config_editor print --all</code>

You cant edit the file ".mylogin.cnf" in your home and the command above also hides the password, but the following command will show your password and is a standard mysql-server tool
<code>my_print_defaults -s <YOURPROFILENAME></code>

==== Backup and Restore ====

=== Backup ===

Backup a single table in a database
<code>
mysqldump <db> <table> > dump.sql
</code>

Backup all tables in a single database
<code>
mysqldump <db> > dump.sql
</code>

Backup all tables in all databases
<code>
mysqldump --all-databases > dump.sql
</code>

Also check out a script to backup all databases from my collegue Tom [[https://tmade.de/wiki/doku.php?id=database:mysql#backup|wiki.tmade.de - Backup MySQL]]

=== Restore ===
<code>
mysql < dump.sql
</code>

Also check out a script to restore databases from my collegue Tom [[https://tmade.de/wiki/doku.php?id=database:mysql#restore|wiki.tmade.de - Restore MySQL]]

==== Commands ====

^  Command  ^  Function  ^
|  %%mysqldump%%  |  -  |
|  %%mysqlcheck --repair --databases <db>%%  |  Repair data types and character sets of a single db changed by updates  |
|  %%mysqlcheck --repair --all-databases%%  |  Repair data types and character sets of all dbs changed by updates  |
|  %%mysql -uroot -p -h 127.0.0.1 -P 3306%%  |  Connect to mysql, enter password after execution  |

==== Troubleshooting ====


==== Links ====

https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html