Bolay's Wiki

User Tools

Site Tools

Trace:
programming:mysql:mysql

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
programming:mysql:mysql [2017/10/04 12:34] – kroduitprogramming:mysql:mysql [2019/10/11 09:34] (current) – [Export database] maferreira
Line 4: Line 4:
   Enter password:    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)   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';   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   $ 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 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> <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. 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   - 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 mysql>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;
 600005 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; 600005 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;
programming/mysql/mysql.1507120482.txt.gz · Last modified: 2017/10/04 12:34 by kroduit