MySQL Best Practices

Installation

$ brew info mysql
$ brew install mysql

To start the MySQL run:

$ mysql.server start
mysql.server `start|stop|restart|reload|force-reload|status`

Or to start it as a service when the computer starts run:

$ brew services start mysql

This will install MySQL without a root passwoord.
- u: root
- p: -

To setup a secure installation run:

$ mysql_secure_installation

MySQL is configured to only allow connections from localhost by default, to connect run:

$ mysql -uroot

Type quit or exit to return to the command prompt.

GUI

Sequel Pro is a Free and open source MySQL GUI, however this does not currently support MySQL 8. Since we should use the latest MySQL has to offer the

$ brew cask install sequel-pro

MySQL Workbench is the official GUI for MySQL, probably not as intuitive to use as Sequel Pro, but it will connect to the last MySQL version.

$ brew cask install sequel-pro

ORM

Sequelize

MYSQL FAILS killall -9 mysqld Please see - https://help.ubuntu.com/community/ApacheMySQLPHP 02. Check that it is running $ sudo netstat -tap | grep mysql 03. To stop / start or restart use the following command $ sudo service mysql restart $ sudo service mysql start $ sudo service mysql stop 05. The Data Directory should be placed onto the attached EBS Storage Stop the MySQL $ sudo service mysql stop $ sudo mkdir /data/mysql/etc /data/mysql/lib /data/mysql/log $ sudo mv /etc/mysql /data/mysql/etc/ $ sudo mv /var/lib/mysql /data/mysql/lib/ $ sudo mv /var/log/mysql /data/mysql/log/ OR $ sudo rm -R /etc/mysql $ sudo rm -R /var/lib/mysql $ sudo rm -R /var/log/mysql ---- $ sudo mkdir /etc/mysql $ sudo mkdir /var/lib/mysql $ sudo mkdir /var/log/mysql $ echo "/data/mysql/etc/mysql /etc/mysql none bind" | sudo tee -a /etc/fstab $ sudo mount /etc/mysql $ echo "/data/mysql/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab $ sudo mount /var/lib/mysql $ echo "/data/mysql/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab $ sudo mount /var/log/mysql $ sudo service mysql start 06. Copy data directory to the main folder $ sudo mv /datadold/mysql/data/wiki_triangle/ /data/mysql/lib/mysql $ chown -R mysql:mysql wiki_triangle 07. Optimise MySQL Edit the cnf file. $ sudo nano /data/mysql/etc/mysql/my.cnf mysql -u root -p SHOW VARIABLES LIKE 'character_set%'; SHOW STATUS; InnoDB Tuning innodb_buffer_pool_size innodb_log_file_size innodb_log_buffer_size innodb_flush_log_at_trx_commit=2 innodb_thread_concurrency innodb_flush_method innodb_file_per_table # Set up UTF8 System by setting character sets # Set up InnoDB a bit better Extend Data Pool to 2GB Increase Pool Size to 1G [client] default-character-set = utf8 default-collation = utf8_unicode_ci port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] basedir = /usr bind-address = 127.0.0.1 character_set_server = utf8 character_set_client = utf8 default-collation = utf8_unicode_ci datadir = /var/lib/mysql #skip-innodb #innodb_buffer_pool_size = 512M #innodb_additional_mem_pool_size = 10M user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 tmpdir = /tmp key_buffer = 128M read_buffer_size = 1M sort_buffer_size = 1M join_buffer_size = 1M read_rnd_buffer_size = 768K max_allowed_packet = 16M max_connections = 200 myisam-recover = BACKUP table_cache = 512 thread_stack = 192K thread_cache_size = 128 thread_concurrency = 2 query_cache_limit = 1M query_cache_size = 32M skip-external-locking [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] default-collation = utf8_unicode_ci default-character-set = utf8 [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/
About

DryKISS is a full service internet and mobile digital production house.

Our core services span consulting, strategy; planning; development; testing and analytics. Using cutting edge technologies within disciplined processes.

What We Do

DryKISS develops hybrid mobile apps (iOS & Android); responsive internet sites; administration systems and social applications.

Using our platform we tune for SEO, accessibility and conversion at the very start.

Social

We are experts in creating social media apps and pages.

Contact

If you have a brief, and want to discuss how we would approach it.

Wimbledon, London, UK