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 |