Differences

This shows you the differences between two versions of the page.

--- linux:general:mysql [2019/06/06 14:16] – lunetikk
+++ linux:general:mysql [2020/01/13 22:31] (current) – lunetikk
@@ Line 1: / Line 1: @@
-FIXME
 ===== MySQL =====
 ==== Install ====
+<code>
+apt-get install mysql-server
+</code>
 ==== Configure ====
-==== Backup and Restore ====
+Execute the following after the installation
+<code>mysql_secure_installation</code>
- To rebuild a table by dumping and reloading it, use mysqldump to create a dump file and mysql to reload the file:
+You can find the config in
+<code>
+/etc/mysql/mysql.conf.d/mysqld.cnf
+</code>
-mysqldump db_name t1 > dump.sql
+==== Passwordless login ====
-mysql db_name < dump.sql
-To rebuild all the tables in a single database, specify the database name without any following table name:
+If you want to automate things you might want to login without a plaintext password.
-mysqldump db_name > dump.sql
+=== Add profile ===
-mysql db_name < dump.sql
-To rebuild all tables in all databases, use the --all-databases option:
+:!: 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 ====
@@ Line 28: / Line 71: @@
 ^  Command  ^  Function  ^
 |  %%mysqldump%%  |  -  |
-|  %%mysqlcheck --repair --databases db_name%%  |  Repair data types and character sets of a single db changed by updates  |
+|  %%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 ====

Lunetikk's IT Wiki

User Tools

Site Tools

Differences

Page Tools