Table of Contents

MySQL

Install

apt-get install mysql-server

Configure

Execute the following after the installation

mysql_secure_installation

You can find the config in

/etc/mysql/mysql.conf.d/mysqld.cnf

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

mysql_config_editor set --login-path=<YOURPROFILENAME> --host=<YOURHOSTIP> --user=<YOURUSERNAME> --password

You can now login via

mysql --login-path=<YOURPROFILENAME>

Show profiles

All profiles can be listed via

mysql_config_editor print --all

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

my_print_defaults -s <YOURPROFILENAME>

Backup and Restore

Backup

Backup a single table in a database

mysqldump <db> <table> > dump.sql

Backup all tables in a single database

mysqldump <db> > dump.sql

Backup all tables in all databases

mysqldump --all-databases > dump.sql

Also check out a script to backup all databases from my collegue Tom wiki.tmade.de - Backup MySQL

Restore

mysql < dump.sql

Also check out a script to restore databases from my collegue Tom 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

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