Install MySQL on Ubuntu 14.04

Published over 6 years ago

Getting Started

Let's check our hostname

hostname
hostname -f

The first command shows short hostname, and the second shows your fully qualified domain name (FQDN).

Next, let's make sure we have the most current packages.

sudo apt-get update
sudo apt-get upgrade

Installing MySQL

sudo apt-get install mysql-server mysql-client libmysqlclient-dev

Make sure to chose a secure MySQL root user password when prompted. MySQL will bind to localhost (127.0.0.1) by default.  You should not bind it to a public ip, but if you wish to change it, you may do so in /etc/my.cnf. You should create firewall rules that only allow connections from certain IP addresses if you decide to use a public ip.

Securing MySQL Server

To see if there are any security concerns, run the following after installing.

sudo mysql_secure_installation

This will ask you a serious of possible questions to remove databases, restrict access to localhost, and change passwords.

Root Login

To log in to MySQL as the root user, use the following.

mysql -u root -p

To generate a list of commands type \h.

Creating a New User and Database

In the example below, devdb is the name of the database, devuser is the user, and password is the user’s password.

create database devdb;
create user 'devuser'@'localhost' identified by 'password';

grant all on devdb.* to 'devuser';

create database devdb;

grant all on devdb.* to 'devuser' identified by 'password';

Exit MySQL.

exit

Creating a Users Table

Log in as devuser.

mysql -u devuser -p

use devdb;

create table users (users_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name STRING, last_name STRING);

Then exit MySQL.

exit

Resetting MySQL's Root Password

If you forget your MySQL root password, you can reset it by doing the following steps.

sudo service mysql stop

We will run dpkg which will take us through the original configuration process.

sudo dpkg-reconfigure mysql-server-5.5

Then start MySQL:

sudo service mysql start

You should now be able to login again with mysql -u root -p.

Fine Tuning MySQL

If you want to fine tune your MySQL server, you can use this perl script after having it up and running for at least 24 hours. Let's first install the script.

sudo apt-get install mysqltuner

Then run it:

mysqltuner

After running it, it will give your general recommendations and any variables that would be prudent to adjust.

See Current Connections

If you want to see the amount of connections currently opened to your database run either of the following commands.

mysql -u root -p
show processlist;
show status where `variable_name` = 'Threads_connected';

If you need to kill one of these connections, find the id of the process and type the following.

kill #{id};

Saving Database Records Only Rails

Through the command line, type the following:

mysqldump --no-create-info --complete-insert --ignore-table=database_name.schema_migrations --ignore-table=database_name.ar_internal_metadata -u root database > dump.sql

Then to import the records in a fresh db use:

mysql -u root database_name < dump.sql

Additional Resources

MySQL 5.5 Reference Manual PHP MySQL Manual Perl DBI examples for DBD::mysql MySQLdb User’s Guide