How to get macOS Mojave brew MySQL 5.7 working for large Drupal drush dumps

xet7.org In English > How to get macOS Mojave brew MySQL 5.7 working for large Drupal drush dumps

2018-10-26

xet7 uses mostly Linux, but also provides support for macOS, Windows, mobile etc.

Normally macOS uses MySQL 8.0 that does not yet support, so installing 5.7 with brew is required.

1) Reinstall MySQL


1.1 First uninstall mysql

brew uninstall [email protected]

1.2 Delete the folders/files that were not removed

rm -rf /usr/local/var/mysql

rm /usr/local/etc/my.cnf

1.3 Reinstall mysql and link it

brew install [email protected]

brew link --force [email protected]

1.4 Enable and start the service

brew services start [email protected]

1.5 Login to MySQL CLI, example commands

mysql -uroot -p

Password: (empty password, press Enter)

SHOW DATABASES;

USE mydb;

SHOW TABLES;

1.6 Or dump database to file

mysqldump -uroot -p mydb > mydb.sql

1.7 Or restore database

mysql -uroot -p mydb < mydb.sql

1.8 Or create new user and database

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

FLUSH PRIVILEGES; 


2) Find MySQL config and log files


2.1 Update Mac file location databaseSource

sudo /usr/libexec/locate.updatedb

2.2 Find MySQL log files and see last 200 lines to see errors. Source

locate local.err | xargs tail -200

2.3 Find MySQL config files.

locate my.cnf

2.4 Find other MySQL files, and leave out not searched with grep -v

locate mysql | grep -v .gz | grep -v drush | grep -v SDK | grep -v backup


3) Fix max_allowed_packet_size so big dumps to MySQL works. Source

3.1 Temporary live fix

mysql -uroot -p

Password: (empty password, press Enter)

SET GLOBAL max_allowed_packet=1073741824;

3.2.1 Permanent fix, saving to config file. Not tested. Source

locate my.cnf

locate my.ini

3.2.2 Add to all those files (mysqld=server, mysql=client)

[mysqld]
max_allowed_packet=1073741824

[mysql]
max_allowed_packet=1073741824






Comments