spirit posteets tagged mysql  [ Profile ]

Sort by: Date / Title /

  1. 6 years ago
    1. mysqldump –add-drop-table –extended-insert –force –log-error=error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost "mysql -uUSER -pPASS NEW_DB_NAME"
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/2167"></script>
  2. 8 years ago
    Create a specific user for backups purpose with read-only permissions
    1. GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost IDENTIFIED BY 'password';
    2. FLUSH PRIVILEGES;
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/2082"></script>
  3. 9 years ago
    Il faut avoir un backup de base tel qu'il y ait un dossier par base de donnée, un fichier SQL compressé par table. "-P 4" désigne le nombre de core.
    1. find -print0 | xargs -0 -n 1 -P 4 -I {} sh -c "zcat '{}' | mysql mydatabase"
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/2071"></script>
  4. sponsorised links
  5. 9 years ago
    Scripted install of MySQL
    1. echo mysql-server mysql-server/root_password select PASSWORD | debconf-set-selections
    2. echo mysql-server mysql-server/root_password_again select PASSWORD | debconf-set-selections
    3.  
    4. aptitude -y install mysql-server libmysqlclient15-dev
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/2060"></script>
  6. 10 years ago
    Great circle distance
    Great circle distance
    Find the distance in kilometres between two points on the surface of the earth. This is just the sort of problem stored functions were made for. For a first order approximation, ignore deviations of the earth's surface from the perfectly spherical. Then the distance in radians is given by a number of trigonometric formulas. ACOS and COS behave reasonably:
    
                 COS(lat1-lat2)*(1+COS(lon1-lon2)) - COS(lat1+lat2)*(1-COS(lon1-lon2))
    rads = ACOS( --------------------------------------------------------------------- )
                                                  2
    
    We need to convert degrees latitude and longitude to radians, and we need to know the length in km of one radian on the earth's surface, which is 6378.388. The function:
    
    set log_bin_trust_function_creators=TRUE;
    
    DROP FUNCTION IF EXISTS GeoDistKM;
    DELIMITER |
    CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
    BEGIN
      DECLARE pi, q1, q2, q3 FLOAT;
      DECLARE rads FLOAT DEFAULT 0;
      SET pi = PI();
      SET lat1 = lat1 * pi / 180;
      SET lon1 = lon1 * pi / 180;
      SET lat2 = lat2 * pi / 180;
      SET lon2 = lon2 * pi / 180;
      SET q1 = COS(lon1-lon2);
      SET q2 = COS(lat1-lat2);
      SET q3 = COS(lat1+lat2);
      SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) ); 
      RETURN 6378.388 * rads;
    END;
    |
    DELIMITER ;
    
    -- toronto to montreal (505km):
    select geodistkm(43.6667,-79.4167,45.5000,-73.5833);
    +----------------------------------------------+
    | geodistkm(43.6667,-79.4167,45.5000,-73.5833) |
    +----------------------------------------------+
    |                           505.38836669921875 |
    +----------------------------------------------+
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1555"></script>
  7. 10 years ago
    You can check and compare sort orders provided by these two collations here:
    
    http://www.collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html
    http://www.collation-charts.org/mysql60/mysql604.utf8_unicode_ci.european.html
    
    utf8_general_ci is a very simple collation. What it does - it just
    - removes all accents
    - then converts to upper case
    and uses the code of this sort of "base letter" result letter to compare.
    
    For example, these Latin letters: ÀÁÅåāă (and all other Latin letters "a" with any accents and in any cases) are all compared as equal to "A".
    
    utf8_unicode_ci uses the default Unicode collation element table (DUCET).
    
    The main differences are:
    
    1. utf8_unicode_ci supports so called expansions and ligatures, for example: German letter ß (U+00DF LETTER SHARP S) is sorted near "ss" Letter Œ (U+0152 LATIN CAPITAL LIGATURE OE) is sorted near "OE".
    
    utf8_general_ci does not support expansions/ligatures, it sorts all these letters as single characters, and sometimes in a wrong order.
    
    2. utf8_unicode_ci is *generally* more accurate for all scripts. For example, on Cyrillic block: utf8_unicode_ci is fine for all these languages: Russian, Bulgarian, Belarusian, Macedonian, Serbian, and Ukrainian. While utf8_general_ci is fine only for Russian and Bulgarian subset of Cyrillic. Extra letters used in Belarusian, Macedonian, Serbian, and Ukrainian
    are sorted not well.
    
    +/- The disadvantage of utf8_unicode_ci is that it is a little bit slower than utf8_general_ci.
    
    So when you need better sorting order - use utf8_unicode_ci, and when you utterly interested in performance - use utf8_general_ci.
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1340"></script>
  8. 10 years ago
    By default, MySQL's datadir is placed in the /var/lib/mysql directory. However, if you are planning on using MySQL tables to store a lot of data and your /var partition is small, it might cause you problem at a later stage. In such a scenario, it is better to move the MySQL's datadir to another partition
    Steps:
       1. Stop your mysql server before starting this operation
       2. Create the directories that will be new datadir
       3. chown the directory to the mysql:mysql user
       4. copy the files from the old datadir to the new location (cp -p : preserves ownership). However, make sure that the files
           named ib_arch_log_0000000000, ib_logfile0 etc. are not copied to the newer location
       5. Make sure that the files and directories are owned by mysql user
       6. Make changes in the my.cnf to point the new datadir (datadir = /my/new/dir/)
       7. Restart the MySQL database
       8. Create a new database and verify that the files for this database are getting created in the new datadir (create database test)
       9. After the server is running for a few days properly, get rid of the old data.
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1035"></script>
  9. 10 years ago
    1. # If you have never set a root password for MySQL
    2. mysqladmin -u root password MYPASS
    3.  
    4. # if you want to change (or update) a user password
    5. mysqladmin -u MYUSER -p OLDPASS NEWPASS
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/1033"></script>
  10. 11 years ago
    1. # MySQL has 2 methods for handling this:
    2.  
    3. REPLACE INTO people(id,name,email) VALUES (in_id, in_name, in_email);
    4.  
    5. INSERT INTO people(id,name,email) VALUES (in_id, in_name, in_email) ON DUPLICATE KEY UPDATE name='$in_name', email='$in_email';
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/826"></script>
  11. 11 years ago and saved by 1 other
    1. mysqldump --add-drop-table -uroot -p NOM_DE_LA_BASE  | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p NOM_DE_LA_BASE
    Paste this in your website: <script type="text/javascript" src="http://www.posteet.com/embed/800"></script>

First / Previous / Next / Last / Page 1 of 2 (18 posteets)