1. The short version of the installation is simple: update your package index, install the mysql-server package, and then run the included security and database initialization scripts.
MySQL 5.7: 3x Faster
The latest version for Ubuntu 14.04 is 5.5 but you can always update to a later version later. However, this is how I updated to 5.7:
note: I first had to removed the old mysql installation. This should not apply on the first installlation:
sudo apt-get remove mysql-server sudo apt-get autoremove
Install mysql 5.7:
$ sudo apt-get update
$ sudo wget http://dev.mysql.com/get/mysql-apt-config_0.7.3-1_all.deb $ sudo dpkg -i mysql-apt-config_0.7.3-1_all.deb
You'll see a prompt that asks you which MySQL product you want to configure. The MySQL Server option, which is highlighted, should say mysql-5.7. If it doesn't, press ENTER, then scroll down to mysql-5.7 using the arrow keys, and press ENTER again.
Once the option says mysql-5.7, scroll down on the main menu to Apply and press ENTER again. Now, update your package index.
NOTE: I have had problems where the prompts would not let me proceed with the installation when I selected mysql 5.7. One solution is that when you select ‘OK' on the first screen to exit and you return to the command line, just continue with the code below. This will install 5.7. It may be because the previous mysql install was not completely eliminated, thus keeping the same root password which is asked in the later steps below.
$ sudo apt-get update $ sudo apt-get install mysql-server
$ sudo mysql_secure_installation
You'll be prompted to create a root password during the installation. Choose a secure one and make sure you remember it, because you'll need it later.
To check the mysql version:
$ mysql --version
Prerequisites: https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-14, also located at DigitalOceanAccountInfo
sudo service mysql status
You'll see the following output (with a different PID):
mysql start/running, process 2689
If MySQL isn't running, you can start it with:
sudo service mysql start
For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect to MySQL as root (-u root), prompt for a password (-p), and return the version.
mysqladmin -p -u root version
You should see output similar to this:
* MySQL Community Server 5.7.14 is running vagrant@data-science-toolbox:~$ mysqladmin -p -u root version Enter password: mysqladmin Ver 8.42 Distrib 5.7.14, for Linux on x86_64 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.7.14 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 16 min 43 sec Threads: 1 Questions: 10 Slow queries: 0 Opens: 40 Flush tables: 1 Open tables: 33 Queries per second avg: 0.009
note: if you see any errors when logging in to phpmyadmin and clicking the ‘Databases' menu like:
#1146 - Table 'data_dictionary.CHARACTER_SETS' doesn't exist
sudo mysql_upgrade -u root -p --force sudo service mysql restart
solution: http://stackoverflow.com/questions/19121383/mysql-issue-table-data-dictionary-character-sets-doesnt-exist, however you may still get the error in your browser. When I changed browsers (to Chrome) and logged in, the error was gone. Just Ctrl+f5 to reload the page.
Actually I have managed to resolve the issue. It is quite a strange one. It seems that Google Chrome [Firefox] default language is causing the issue since phpMyAdmin takes that language and translates itself to it. I have changed the language to English and logged out from my google account and the issue was magically resolved.
2. Tune MySQL
MySQL Tuner is a Perl script that connects to a running instance of MySQL and provides configuration recommendations based on workload. Ideally, the MySQL instance should have been operating for at least 24 hours before running the tuner. The longer the instance has been running, the better advice MySQL Tuner will give.
Install MySQL Tuner from Ubuntu’s repositories:
sudo apt-get install mysqltuner
To run it:
You will be asked for the MySQL root user’s name and password. The output will show two areas of interest: General recommendations and Variables to adjust.
MySQL Tuner is an excellent starting point to optimize a MySQL server, but it would be prudent to perform additional research for configurations tailored to the application(s) utilizing MySQL on your Linode.
Securely Administer MySQL with an SSH Tunnel