Install and configure MySQL

Database

Databases store data in a structured and easily accessible manner, serving as the foundation for hundreds of web and server applications. A variety of open source database platforms exist to meet the needs of applications running on your Linux VPS. This section will help you get started with MySQL, one of the most popular database platforms. For more information about MySQL and other databases, see our database reference manuals.

Installing MySQL

Here’s how to install and configure MySQL:

Install MySQL by entering the following command. Your Linode will download, install, and start the MySQL database server.

sudo apt-get install mysql-server

You will be prompted to enter a password for the MySQL root user. Enter a password.

Secure MySQL by entering the following command to open mysql_secure_installation utility:

sudo mysql_secure_installation

The mysql_secure_installation utility appears. Follow the instructions to remove anonymous user accounts, disable remote root login, and remove the test database.

That’s it! MySQL is now installed and running on your Linode.

Optimizing MySQL for a Linode 1GB

MySQL consumes a lot of memory when using the default configuration. To set resource constraints, you’ll need to edit the MySQL configuration file. Here’s how to optimize MySQL for a Linode 1GB:

These guidelines are designed to optimize MySQL for a Linode 1GB, but you can use this information for any size Linode. If you have a larger Linode, start with these values and modify them while carefully watching for memory and performance issues.

Open the MySQL configuration file for editing by entering the following command:

sudo nano /etc/mysql/my.cnf

Make sure that the following values are set:

max_connections = 75

key_buffer = 32M

max_allowed_packet = 1M

thread_stack = 128K

table_cache = 32

Save the changes to MySQL’s configuration file by pressing Control + x and then pressing y.

Restart MySQL to save the changes. Enter the following command:

sudo service mysql restart

Now that you’ve edited the MySQL configuration file, you’re ready to start creating and importing databases.

Creating a Database

The first thing you’ll need to do in MySQL is create a database. (If you already have a database that you’d like to import, skip to Importing a Database.) Here’s how to create a database in MySQL:

Log in to MySQL by entering the following command and then entering the MySQL root password:

mysql -u root -p

Create a database and grant a user permission to it by entering the following command. Replace exampleDB with your own database name:

create database exampleDB;

Create a new user in MySQL and then grant that user permission to access the new database by issuing the following command. Replace example_user with your username, and 5t1ck with your password:

grant all on exampleDB.* to 'example_user' identified by '5t1ck';

MySQL usernames and passwords are only used by scripts connecting to the database. They do not need to represent actual user accounts on the system.

Tell MySQL to reload the grant tables by issuing the following command:

flush privileges;

Now that you’ve created the database and granted a user permissions to the database, you can exit MySQL by entering the following command:

quit

Now you have a new database that you can use for your website. If you don’t need to import a database, go ahead and skip to PHP.