Differences

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

--- programming:mysql:mysql [2017/10/04 12:22] – kroduit
+++ programming:mysql:mysql [2019/10/11 09:34] (current) – [Export database] maferreira
@@ Line 4: / Line 4: @@
   Enter password:
-  mysql> create database myDatabase;
+===== Export database =====
+With data:
+<code mysql>mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql</code>
+Without data:
+<code mysql>mysqldump --no-data -u YourUser -p YourDatabaseName > wantedsqlfile.sql</code>
+===== Import database =====
+<code mysql>mysql -u username -p database_name < file.sql</code>
+<note important>Make sure you have created an empty database named <data_basename></note>
+<code mysql>mysql> CREATE DATABASE <myDatabase>;</code>
   Query OK, 1 row affected (0.01 sec)
+===== Create user =====
+<code mysql>CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';</code>
   mysql> grant usage on *.* to myUsername@localhost identified by 'myPassword';
@@ Line 84: / Line 96: @@
   $ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
 For more info see http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
+If this error,
+<note warning>ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'</note>
+<note tip><code mysql>mysql>SET GLOBAL show_compatibility_56 = ON;</code>
+To keep variable always ON even if you restart the server, create file my.cnf
+Read :
+http://mysql-tools.com/home/1-mysql/125-showcompatibility56-mysql-57-connection-problem.html
+</note>
 <code mysql>
@@ Line 124: / Line 146: @@
 bigint unsigned -- 8 bytes, approx. 9 quintillions elements (10E18)</code>For all physical device, it's useless to use bigint. It's reasonable to assert that we don't install more than 16 millions sensors or centrals. And if it will be the case one day, the database will be splitted. \\ **So, for physical elements, mediumint is enough.** \\ For sample, the bigint can be justified.
   - For timestamp:<code mysql>int unsigned -- Overflow is on year 2'106
-bigint unsigned -- Overflow is on year 219'250'468 (easyControl in a Deathstar?)</code>**So, mediumint is enough for timestamp.**
+bigint unsigned -- Overflow is on year 292'277'026'596 (easyControl in a Deathstar?)</code>**So, mediumint is enough for timestamp.**
-  - Double VS smallint: \\ A double use 8 bytes, and a mediumint use 4 bytes.\\ If you exactly know the range and the precision of the stored value, the good question to ask is: what is better between an integer scaled value (4 bytes, but division implied) and double value (8 bytes). \\ \\ **Let's try it:**\\ With 600'000 sample in a table order by sampled (old version). We choose 600'000 values because it's the worst theoretical case: 1 measure per seconde during 7 days. When we displaying more than 7 days, MySQL make an average.<code sql>select temperature from static_sample where sampled < 1473840000;
+  - Double VS smallint: \\ A double use 8 bytes, and a mediumint use 4 bytes.\\ If you exactly know the range and the precision of the stored value, the good question to ask is: what is better between an integer scaled value (4 bytes, but division implied) and double value (8 bytes). \\ \\ **Let's try it:**\\ With 600'000 sample in a table order by sampled (old version). We choose 600'000 values because it's the worst theoretical case: 1 measure per seconde during 7 days. When we displaying more than 7 days, MySQL make an average.<code mysql>select temperature from static_sample where sampled < 1473840000;
 rows in set (2.13 sec) -- Average execution time (10 requests made), on my macMini</code><code mysql>select temperature/10000 from static_sample where sampled < 1473840000;
-rows in set (2.25 sec) -- Average execution time (10 requests made), on my macMini</code>To summarise: we save 50% in the storage size for the cost of 6% in the request execution time. But this test is with **only one row**. If you use this trick with 2 or \\ But in practice, actually, the database is sorted by sensor_id. Let's try a test closer to the actual reality. The sensor run a measure all 15 minutes. The best we can do with real data is a test with all sample from a sensor (38000). <code mysql> select temperature, warning_max, warning_min, critical_max, critical_min from static_sample where static_Sensor_id=246;
+rows in set (2.25 sec) -- Average execution time (10 requests made), on my macMini</code>To summarise: we save 50% in the storage size for the cost of 6% in the request execution time. \\ But this test is with **only one row**. The result is not the same with 2 or more rows. \\ \\ **Let's try a test closer to the actual reality:** \\ In practice, actually, the database is sorted by sensor_id. The sensor run a measure all 15 minutes. The best we can do with real data is a test with all sample from a sensor (38000).<code mysql>select temperature, warning_max, warning_min, critical_max, critical_min from static_sample where static_Sensor_id=246;
 rows in set (0.085 sec) -- Average execution time (10 requests made), on server7</code><code mysql>select temperature/10000, warning_max/10000, warning_min/10000, critical_max/10000, critical_min/10000 from static_sample where static_Sensor_id=246;
 rows in set (0.115 sec) -- Average execution time (10 requests made), on server7</code>To summarise: we save 50% in the storage size for the cost of 36% in the request execution time.