Friday, August 8, 2014

Install and configure MySQL

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:

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.

No comments:

Post a Comment