#mysql

maxbube/mydumper: Official #mydumper project
▻https://github.com/maxbube/mydumper

What is mydumper? Why?

– Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
– Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
– Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc
– Manageability - supports PCRE for specifying database and tables inclusions and exclusions

When it comes to backing up and restoring MySQL database, most people usually use the very popular mysqldump. Whilst, mysqldump can be very easy to use for a smaller database, it doesn’t work well with larger databases. It’s very slow for huge databases and very error prone when used for very big MySQL databases.

▻https://dotlayer.com/extremely-fast-mysql-backup-restore-using-mydumpermyloader

Semble être utilisé par wikmedia cf :

Its speed and parallelism makes it perfect to save time and load data a lot faster.
It is easier to recover single tables or rows.
It compress nicely

▻https://wikitech.wikimedia.org/wiki/MariaDB#Dumping_tables_with_mydumper

#mysqldump_alternative

sort MySQL-ORDER BY valeurs dans IN() - Résolu
▻https://code.i-harness.com/fr/q/ea0a3#answer3

utiliser une liste pour ordonner les résultats d’une requete (typiquement faire un
id_truc IN (1, 3, 5, 8)
et avoir les résultats triés selon le même ordre
ce qui se fait avec :
SELECT * FROM spip_truc WHERE id_truc IN (1, 3, 5, 8) ORDER BY FIELD (id_truc, 1, 3, 5, 8)
Ce qui donne en requête SPIP :
<BOUCLE_t(TRUCS){id_truc IN  1, 3, 5, 8}{par FIELD (id_truc, 1, 3, 5, 8)}>
ça fonctionne très bien comme ça...
...sauf qu’avec la valeur de la liste en dynamique (#SET/ #GET) ça ne veut rien savoir (même avec tous les bricolages possibles...) Dommage ! :-(

#mysql #FIELD #trier #SPIP

John’s Observation Loft : Adding or removing individual SQL modes in MySQL’s sql_mode variable
▻http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html

La syntaxe des requêtes MySQL pour ajouter/modifier/supprimer à la volée des valeurs aux sql_mode (défini dans la configuration du serveur)

#sql_mode #MySQL #configuration

#MariaDB #Memory Allocation - MariaDB Knowledge Base
▻https://mariadb.com/kb/en/library/mariadb-memory-allocation

Allocating RAM for MySQL - The short answer

If using just MyISAM, set key_buffer_size to 20% of available RAM. (Plus innodb_buffer_pool_size=0)

If using just InnoDB, set innodb_buffer_pool_size to 70% of available RAM. (Plus key_buffer_size = 10M, small, but not zero.)

Rule of thumb for tuning mysql:

– Start with released copy of my.cnf / my.ini.
– Change key_buffer_size and innodb_buffer_pool_size according to engine usage and RAM.
– Slow queries can usually be ’fixed’ via indexes, schema changes, or SELECT changes, not by tuning.
– Don’t get carried away with the query cache until you understand what it can and cannot do.
– Don’t change anything else unless you run into trouble (eg, max connections).
– Be sure the changes are under the [mysqld] section, not some other section.

The 20%/70% assumes you have at least 4GB of RAM. If you have a tiny antique, or a tiny VM, then those percentages are too high.

Now for the gory details. (NDB Cluster is not discussed here.)

#mysql

How to change a MySQL database’s table prefix | Rich Jenks
▻https://richjenks.com/change-mysql-database-table-prefix

The output will be a series of SQL queries that will rename the tables for you

#mysql #prefixe

Using #docker To Run #mysql Server In Your Development Environment
▻https://hackernoon.com/using-docker-to-run-mysql-server-in-your-development-environment-e32d523

https://cdn-images-1.medium.com/max/900/0*3nDnCayBCd9pA-8r.jpg

Today I’m going to show you how can you can use Docker to run MySQL Server in your development environment. Using Docker to run MySQL is much easier than manually installing it and for when you have multiple instances of MySQL running at the same time. I’m assuming that you already have Docker Community Edtion install on your machine. If you do not have Docker installed follow the instruction here for your operating system.Install MySQL Using DockerIf you search online for how to install MySQL using Docker, you will see many different examples on how to do this. I’m going to show how to do it so that you can connect to the database from your IDE and from the applications you are developing.Type this command at the terminal:$ docker run -p 3306:3306 —name hb-mysql-example -e (...)

#devops #java #programming

Impact on Insert Performance — Primary Keys in #mysql
▻https://hackernoon.com/impact-on-insert-performance-primary-keys-in-mysql-590ecf8ba67?source=rs

https://cdn-images-1.medium.com/max/1024/1*WTsG9lqGrPzqPbhWxlf-9Q.png

Impact on Insert Performance — Primary Keys in MySQLThe importance of Primary Keys in MySQLNote: I have used MySQL and it’s storage engine #innodb interchangeably.A couple of days ago, someone at work asked me whether inserts are faster in a table when there’s a primary key present in the table. My immediate answer was that they were faster. I had some idea about this from experience and previous reading but didn’t remember the reason why it was so.For a business user unaware about InnoDB internals or InnoDB’s existence, it might seem perfectly intuitive to assume that inserts will be faster without a primary key because a primary key is an additional constraint on the table, i.e., MySQL has to check for uniqueness, nullability. When it comes to InnoDB, that is not the case. Also, a table (...)

#database #performance-testing #database-administration

Adding #elasticsearch To Legacy Application Using #logstash
▻https://hackernoon.com/adding-elasticsearch-to-legacy-application-using-logstash-98c19ac2f0fb?s

https://cdn-images-1.medium.com/max/1024/1*nNR_e2gAr8kwLKW2ZoZOhA.png

ElasticSearch has created a boom in the market with its ability to store, scaling, performing full-text search and analytics on the humongous amount of data in near real time. ElasticSearch is open source search and analytics engine generally used in applications which have complex search features.In this tutorial, we are going to consider a scenario where we wish to add ElasticSearch to our legacy application as a Secondary Data Source. Say suppose we have a legacy application which uses #mysql database and now we have a requirement where we need to have a heavy search and we have decided to move to ElasticSearch. My idea here would still stick to Relational Databases as a Primary Data Store and use ElasticSearch as a Secondary Data Store for the requirements where we need to have the (...)

#add-elasticsearch #advanced-search

#sharding writes with #mysql and increment offsets
▻https://hackernoon.com/sharding-writes-with-mysql-and-increment-offsets-48df32379563?source=rss

https://cdn-images-1.medium.com/max/600/0*UvsNvfPreQlGvO9Z.png

Sharding Writes with MySQL and Increment OffsetsTL;DRwe shard writes and avoid conflicts using an increment offset on PKseach #data center contains local write serverswe use multi-source replication on read servers so they contain all the data from all data centers, partitioned by monthAt the beginning, things were simpleWe were using MySQL: one master for writing, and a few slaves for reading. Besides replication lag (more on that later), it worked fine. And then you scale. And then it fails! The master is a SPOF — “Single Point Of Failure”.Whether the process failed, the VM crashed, or we had to do maintenance on the server, having only one “master” to write to meant that the service was down for minutes. And this was not acceptable.The first thing we implemented was sharding based on the (...)

#data-replication #database

#activerecord on #mysql— Iterating over large tables with conditions
▻https://hackernoon.com/activerecord-on-mysql-iterating-over-large-tables-with-conditions-453bd8

https://cdn-images-1.medium.com/max/738/1*ZVxd0g85DK_AiHSym6CtNA.png

In this article, I’m going to demonstrate performance differences between two ways of iterating over the records in a MySQL database table with millions of records. In a high volume analytics system, tables with millions of records are quite common and iterating over the full table or a subset of these tables becomes often necessary — whether it’s to perform computations, run a migration, or create parallelized background jobs on the records. At AirPR, we have many database tables with 100s of millions of records, and it becomes important to write efficient code for iterations because there is often an order of magnitude difference between a good and not-so-good approach.Find Each MethodThe standard approach provided natively by ActiveRecord is the find_eachmethod.For the purposes of this (...)

#rails #programming #activerecord-on-mysql

MySQL — Wikilivres
▻https://fr.wikibooks.org/wiki/MySQL

Documentation de MySQL en français (puisque Oracle a supprimée le français de la doc officielle...)

#mysql #documentation

Elephant in the Room : #database #backup
▻https://hackernoon.com/elephant-in-the-room-database-backup-574da50e6d88?source=rss----3a8144ea

https://cdn-images-1.medium.com/max/1024/1*Tm3culOnUMproMU2I6j58w.jpeg

Photo by Alexandre ChambonWe’ve seen it many times. Yet it sill occurs on a regular basis, as if there’s no permanent cure for this chronic problem.About 9 years ago, Sidekick, a popular T-Mobile smartphone at the time, lost all of user data in the cloud, leaving 800,000 users without access to their own personal data such as email, notes, contacts, calendars and photos.Later on, Microsoft decided to discontinue the cloud service and eventually, sales of the device.As a result, a massive business and its ecosystem ceased to exist. It was the biggest disaster in cloud computing history.GitLab also suffered from broken recovery procedures when an engineer had accidentally deleted a directory on the wrong server, by running rm -rf /important-data on their primary database.Side note: Kudos to (...)

#mysql #cloud-computing #database-backup

Showdown : #mysql 8 vs PostgreSQL 10
▻https://hackernoon.com/showdown-mysql-8-vs-postgresql-10-3fe23be5c19e?source=rss----3a8144eabfe

https://cdn-images-1.medium.com/max/1024/1*eojT2CVjYsQNtaLFxvKUuw.jpeg

Photo by David MarcuNow that MySQL 8 and PostgreSQL 10 are out, it’s a good time to revisit how the two major open source relational databases compete against each other.Before these versions, the general perception has been that while #postgres is superior in feature sets and its pedigree, MySQL is more battle tested at scale with massive concurrent reads /writes.But with the latest releases, the gap between the two has gotten significantly narrowed.Feature ComparisonLet’s take a look at the “trendy” features that we all love to talk about.It used to be easy to say that MySQL works best for online transactions, and PostgreSQL works best for analytical processes. But not anymore.Common Table Expressions (CTEs) and window functions have been the main reason to choose PostgreSQL. But now, (...)

#database #programming

bash - How to output MySQL query results in CSV format ? - Stack Overflow
▻https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format#356605

Plein de méthodes pour sortir le résultat d’une requête MySQL dans un fichier CSV (même si le serveur est configuré en mode paranoïaque (—secure-file-priv option) qui l’empêche de sortir le résultat dans un fichier.

On peut aussi utiliser (élégamment !) pager : ▻https://www.zen-tech.info/2010/02/enregistrer-resultat-dun-requete-mysql-dans-un-fichier

#csv #MySQL #pager #batch #sed

MariaDB : déplacer le répertoire /var/lib/mysql | memo-linux.com
▻https://memo-linux.com/mariadb-deplacer-le-repertoire-var-lib-mysql

Un mémo sur comment déplacer le répertoire /var/lib/mysql vers la partition /home

#mariadb #mysql #linux #serveur

Convertir une base de données SQLite vers MySQL/MariaDB - PAB’s blog
▻https://blog.bandinelli.net/index.php?post/2014/03/27/sqlite3-to-mysql

LE script miraculeux pour la conversion sqlite > sql
NB : ne pas oublier de faire un rechercher/remplacer pour virer les chaînes COLLATE NOCASE et COLLATE BINARY avant de lancer l’import du fichier SQL obtenu.
...et ne pas oublier de remettre le AUTO_INCREMENT sur tous les champs id_<objet> qui sont les clés primaire des tables spip_<objet>

#conversion #sqlite #mysql #mariadb #python #utilitaire #SPIP

#macOS 10.12 Sierra #Apache Setup : Multiple #PHP Versions | Grav
▻https://getgrav.org/blog/macos-sierra-apache-multiple-php-versions

mysql - #1071 - Specified key was too long ; max key length is 767 bytes - Stack Overflow
▻https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/38675910#38675910

Les 2 modifications à apporter à une table MySQL (innodb) pour la rendre compatible (format Baracuda) avec les clés à rallonge de Moodle et éviter l’erreur « 1071 Specified key was too long ; max key length is 767 bytes »

#mysql #moodle #barracuda #innodb

What’s the difference between utf8 and utf8mb4 in MySQL?
▻http://www.eversql.com/mysql-utf8-vs-utf8mb4-whats-the-difference-between-utf8-and-utf8mb4

“If you’re using MySQL in any flavor (MySQL, MariaDB, PerconaDB, etc.), make sure you know your encodings. I would recommend anyone to set the MySQL encoding to utf8mb4. Never use utf8 in MySQL, there is no good reason to do that (unless you like tracing encoding related bugs).”

#MySQL_utf8_encoding_clevermarks

#mysqldumpslow - MariaDB Knowledge Base

▻https://mariadb.com/kb/en/mariadb/mysqldumpslow

mysqldumpslow is a tool to examine the #slow_query_log.

It parses the slow query log files, printing a summary result. Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It “abstracts” these values to N and ´S´ when displaying summary output. The -a and -n options can be used to modify value abstracting behavior.

Installer Apache, MySQL et PHP sous OSX sans passer par MAMP - Elliptips : Elliptips
▻http://elliptips.info/installer-apache-mysql-et-php-sous-osx-sans-passer-par-mamp

http://elliptips.info/wp-content/uploads/2012/11/apache_php_mysql.jpg

Venant tout juste de changer de Mac, je découvre Mountain Lion et je me suis dit qu’il était temps de mettre un peu d’ordre dans mon environnement de développement. J’avais l’habitude d’utiliser MAMP pour le développement web, car celui-ci a l’avantage indéniable de fournir un package prêt à l’emploi, le tout en un clic. Revers de la médaille, il nous rend tributaire des versions de logiciels qu’il propose et s’intègre parfois mal avec d’autres environnements. Ainsi, nous verrons dans ce tutoriel comment mettre en place un stack AMP (#Apache, #MySQL et #PHP) sous Mac #OSX simplement et rapidement, le tout en utilisant au maximum les outils déjà intégrés au système.

Relancer mysql

Relancer apache

Perte du mot de passe root #MySQL / #MariaDB
▻http://www.dsfc.net/logiciel-libre/mysql-logiciel-libre/perte-mot-de-passe-root-mysql-mariadb

http://www.images-booknode.com/book_cover/28/mot-de-passe,-francais-maitrise-de-la-langue,-ce1-cycle-2-28029-250-400.jpg

Il n’y a pas de lieu de paniquer lorsque vous avez perdu ou oublié le mot de passe associé au compte root de votre instance MySQL / MariaDB !

#Formateur_MariaDB #Formateur_MySQL #Formateur_SQL #Formation_MariaDB #Formation_MySQL #SQL

Debezium
▻http://debezium.io

Debezium is an open source distributed platform for change data capture. Start it up, point it at your databases, and your apps can start responding to all of the inserts, updates, and deletes that other apps commit to your databases. Debezium is durable and fast, so your apps can respond quickly and never miss an event, even when things go wrong.

#mysql #updates un #outil dont je ne vais pas du tout avoir besoin mais qui a l’air intéressant

Y’aura du monde au #Fosdem 2017 ce WE ?
▻https://fosdem.org/2017/schedule
#Bruxelles #Brussels #logiciel #libre #Architectures #Building #Cloud #Documentation #Security #Encryption #Kubernetes #Lua #Ruby #Python #Qt #Go #Valgrind #Perl #Linux #BSD #Java #MySQL #Mozilla #GNU

#Bibisco #politique #anarchisme

▻https://seenthis.net/messages/559565

La popularité des #Moteurs_de_bases_de_données en 2017
▻http://www.dsfc.net/infrastructure/base-de-donnees-infrastructure/popularite-moteurs-bases-donnees

http://www.dsfc.net/wp-content/uploads/2017/01/db-engines-ranking-2017-01.jpg

Les leaders des moteurs de bases de données restent #Oracle_Database, #MySQL/MariaDB et #Microsoft_SQL_Server en 2017.

#Base_de_données #Elastic_Search #ELK #Formateur_Bases_de_données #Formateur_MariaDB #Formateur_MySQL #Formateur_Oracle #Formateur_PostgreSQL #Formateur_SQL #Kibana #LogTash #MariaDB #MongoDB #PostgreSQL #SQL_Server