docker mysql

docker run --name my-mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 -d mysql

Import 2G sql file to mysql database

edit my.cnf
my.cnf

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
innodb_buffer_pool_size=2048M (50% of your ram)
max_connections=100
key_buffer_size=256M
max_allowed_packet=256M

innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

then

mysql -uroot -p dbname < /var/www/mydb.sql

source: https://dba.stackexchange.com/a/83385/86553

Reload MySql

This should work on ubuntu 18.04

sudo service mysql restart

If not you can try other commands:

sudo systemctl restart mysqld

sudo systemctl restart mysql

or

sudo service mysqld restart

sudo service mysql restart

or

sudo /etc/init.d/mysql reload

or

sudo /etc/init.d/mysql force-reload

Change Auto Increment counter in MySQL

ALTER TABLE tbl AUTO_INCREMENT=310;

The proper way of doing SQL "INSERT ON DUPLICATE UPDATE"

$sql = 'INSERT INTO table_name (column1, column2, column3, column4) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE column1=VALUES(column1), column2=VALUES(column2)';

Ubuntu Mysql Import file.sql into database FROM cmd

$ mysql   -u  root   -p

then enter password and

mysql> use DATABASE_NAME;

mysql> source path/to/file.sql;

SQL Select where column1 is not equal column2

SELECT *
FROM my_table
WHERE NOT column_a <=> column_b

Tips for (My)SQL tables, attributes, indexes

First of all if you have hard time to get what is better to do - read official docs about attribute sizes, indexes and so on.
Second if you still can't make decision install wordpress or other popular cms and check how they do some stuff like indexes.

As I have installed wordpress and checked all the stuff there were I come to conclusion:
Collation:

utf8mb4_unicode_ci

index tip1: make indexes on every attribute you are going to make where statements, max or group by. And please read it on indexes official  documentation

index tip2: if you think you need uniq index and it is only one - make it primary.

Simple many to many relations
article (id, date, text); id primary. Anything else you need to use where, max, group by etc - simple index

tags (id, name, slug), id - primary, name and slug simple index (as we need them a lot we will use them from indexes data and will not need to get data from the disc

article_tags (article_id, tag_id). Primary index on 2 columns (article_id, tag_id) and simple index on tag_id.

Remember when using index on more than 1 column the order is very important. Read more in official docs. But basically if you have index on columns a, b, c you can benefit from index only when searching a, or a + b or a+b+c. If you search on b or c or b+c value it will not use index.
Learn more about this as usually at official docs http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

If you don't need -number (for example -12345 with minus sign), on number types make them unsigned

export database without phpmyadmin

Hi! sometimes you need to get database, but you haven't phpmyadmin/sqladminer nor or you just want to do it faster.
So you can do it loggining via ssh and use some mysql command or create script with

<?php
exec('mysqldump --user=MYUSERNAME --password=MYPASSWORD --host=localhost MYDATABASENAME > backup.sql');
exit('done');

and run it

you have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transaction

PROBLEM

Your mysql don't work saying smth like:

you have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transaction

or

No connection could be made because the target machine actively refused it.

You have probably shutdown pc when there were some mysql transactions or whatever it was doing.

SOLUTION

Go to mysql bin folder open cmd and type:

mysqld --tc-heuristic-recover=ROLLBACK

Don't forget to restart mysql after this command finished