MySQL

SQL

Clients

Docker

Quickstart docker run for development: docker run -d -e MYSQL_ROOT_PASSWORD= --rm --name mysql -p 3306:3306 mysql:5.7
Quickstart docker phpmyadmin: docker run --name myadmin -d -e PMA_HOST=172.17.0.1 -p 8080:80 phpmyadmin/phpmyadmin

Backups

Use search in JSON field in UPDATE statement

1
2
3
4
5
6
UPDATE membership
  SET account = CASE
    WHEN(JSON_EXTRACT(payload, "$.pack.account") IS NOT NULL)
    THEN JSON_EXTRACT(payload, "$.pack.account")
    ELSE account
  END

Importing large dumps

The first step is remove every CREATE DATABASE and USE db_name instruction. If the dump is very large the best way to achieve this is using sed since opening a large dump in a text editor is a very bad idea.

Example:

1
2
sed -i '/CREATE DATABASE.*/c\' dump.sql
sed -i '/USE whatever.*/c\' dump.sql

Sometimes when you are importing large dumps you get errors like Can't connect to the server and ERROR 2047 (HY000): Wrong or unknown protocol.
To try to mitigate this issue you can set some mysql parameters:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql -u root -p

set global net_buffer_length=1000000; --Set network buffer length to a large byte number

set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number

SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour

-- Create new schema to import the dump
CREATE DATABASE my_schema;

-- Select the new created database
USE my_schema;

source file.sql --Import your sql dump file

SET foreign_key_checks = 1; --Remember to enable foreign key checks when procedure is complete!

Run MySQL in tmpfs for better performance (Usefull for CI testing)

1
2
3
4
5
6
7
8
# Setup tmpfs for testing
mkdir /var/tmpfs
mount -t tmpfs -o size=1G tmpfs /var/tmpfs
cp -Rfv /var/lib/mysql /var/tmpfs/
chown -R mysql:mysql /var/tmpfs/mysql
sed -i '/^datadir.*/c\datadir = /var/tmpfs/mysql' /etc/mysql/my.cnf

/etc/init.d/mysql start

Comments

⬆︎TOP