ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';  

database.php

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            'options' => [
                \PDO::ATTR_PERSISTENT => true
            ]
        ],

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size 
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables 
WHERE engine='InnoDB') A, 
(SELECT 2 PowerOf1024) B; 

After version 5.7.11 password expires in 360 days

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

cp $(brew --prefix mysql)/support-files/my-default.cnf /usr/local/etc/my.cnf

1. LEFT JOIN/IS NULL

DELETE b FROM tbl
  LEFT JOIN rel ON rel.id = tbl.fileid 
      WHERE rel.id IS NULL

2. NOT EXISTS

DELETE FROM tbl 
 WHERE NOT EXISTS(SELECT NULL
                    FROM rel
                   WHERE rel.id = tbl.fileid)

3. NOT IN

DELETE FROM tbl
 WHERE tbl.fileid NOT IN (SELECT rel.id 
                        FROM rel)

У нас должно быть 2 mysql сервера: master и slave В my.cnf на master пишем и рестартим mysql

[mysqld]
bind-address = 0.0.0.0
log_bin
server_id = 1
expire_logs_days = 5
max_binlog_size = 1000M
binlog_do_db = our_database1
binlog_do_db = our_database2

Затем заходим в mysql консоль на master и задаем доступ для slave

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password_for_slave';
FLUSH PRIVILEGES;

Теперь нужно сделать дамп master Заходим в mysql консоль и блокируем таблицы

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

В ответ должны получить примерно следующее

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      102 | our_database1, our_database2 |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Теперь спокойно снимаем дамп

mysqldump -u root  > db.sql

Заходим в консоль mysql и снимаем блокировку

mysql> UNLOCK TABLES;

Теперь загрузим дамп на slave

rsync --inplace -arvlt --stats --progress -e 'ssh -p 22' /home/db.sql  root@slave_ip:/home/db.sql

На этом действия с master закончились, переходим к slave. На slave сервере редактируем my.cnf

[mysqld]
server_id = 2

Перезапускаем slave, заливаем дамп с мастера

mysql -u root < db.sql

Включаем репликацию (MASTER_LOG_FILE и MASTER_LOG_POS берутся из команды SHOW MASTER STATUS на мастере после блокировки таблиц)

mysql> CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='slave', MASTER_PASSWORD='password_for_slave', MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=102;
mysql> START SLAVE;

Проверка репликации

mysql> SHOW SLAVE STATUS\G

Необходим о создать файл ~/.my.cnf

[client]
user=root
password="pass"

[mysql]
user=root
password="pass"

[mysqldump]
user=root
password="pass"

[mysqldiff]
user=root
password="pass"

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;