Installing MySQL Server on CentOS

MySQL is an open-source relational database. For those unfamiliar with these terms, a database was where an application keeps it data, and relational refers to how the Data is organized and accessed within the database. SQL refers to the language used by application queries to retrieve and store data:structured Query language.

MySQL is free and widely used, meaning so can find a large amount of application support, tools, and community help For it. MySQL is a safe choice if you know so need a database but don ' t know much on all of the available the options.

This article describes a basic installation of a MySQL database server on the CentOS Linux, just enough to get you started. Remember that's might need to install the other packages to let applications use MySQL, like extensions for PHP. Check your application documentation for details.

Install MySQL

Install the MySQL server through the CentOS Package Manager by running the following commands at a command prompt:

sudo yum install mysql-serversudo /sbin/service mysqld start

Then, run the following command:

sudo /usr/bin/mysql_secure_installation

Press ENTER to give no password for root when this program asks for it. To apply some reasonable security to your new MySQL server answer ' yes ' to all the ' questions that ' program asks. In order, those questions enable you set the root password, remove anonymous users, disable remote root logins, delete the Test database, the installer included, and then reload the privileges so, your changes would take effect.

Allow access from other machines

If you had iptables enabled and want to connect to the MySQL database from another machine, you need to open a port in yo ur server ' s firewall (the default port is 3306). You don't need to does this if the application using MySQL are running on the same machine.

If you do need to open a port, you can use the following rules in iptables to open port 3306:

-I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT-I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

Note: The command is deliberately left out of the iptables iptables rules in the instructions above. Some people using distributions that does not has their own iptables service might instead have a rules file they can Impor T using iptables-restore . The format of the lines in that file would is similar to the format used above:iptables options without the iptables command In front of them. For this reason, the instructions with this article represent a compromise. It's easy-to-paste the lines into a rules file, and they can was used with the iptables command instead.

Launch MySQL

Now the MySQL is installed and you can verify that it's running by trying to launch it:

sudo /sbin/service mysqld start

If MySQL is already running, you'll receive a message to that effect.

Launch at restart

To ensure that the MySQL server would launch when the machine is restarted, run the following command:

sudo chkconfig mysqld on

That's makes sure your machine would launch the MySQL server when it reboots.

The MySQL Shell

There is more than one-to-work with a MySQL server, it's article focuses on the most basic and compatible approach : The mysql shell. At the command prompt, run the following command to launch the mysql shell and enter it as the root user:

/usr/bin/mysql -u root -p

When you're prompted for a password, enter the one, and set at installation or, if you haven ' t set one, just press ENT Er to submit no password. The following mysql shell prompt should appear:

mysql>

Set the root password

Since you had just installed your MySQL database server, the root account within MySQL had no password set yet. You should to running the following commands:

/usr/bin/mysqladmin -u root password ‘new-password‘/usr/bin/mysqladmin -u root --password=‘new-password‘ -h hostname-of-your-server ‘new-password‘

Note: This article shows SQL commands in all capitals, but can also type them in lowercase. The commands is shown capitalized by convention, to make them stand off from field names and other data that ' s being mani Pulated.

Find Database Users

As mentioned in the preceding sections, MySQL stores the user information in its own database. The name of the database is "MySQL". Inside that database, the user information was in a "table", a dataset, named "User". If you want to see what users is set up in MySQL table, or dataset, named "User".

SELECT User, Host, Password FROM mysql.user;

Following is descriptions of the parts of that command:

• The SELECT command tells MySQL that is asking for data.

• The User, Host, Password part tells MySQL "What's the want it to look in." Fields is categories for the data in a table. In this case, you is looking for the username, the host associated with the username, and the encrypted password entry.

• The from Mysql.user part of the command tells MySQL to get the data from the MySQL database and the user table.

• The command ends with a semicolon.

Ending SQL Queries with a semicolon

All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.

This means the can break up queries onto multiple lines to make them easier to read. For example, the preceding command also works if you enter it on multiple lines mysql in the shell, as follows:

mysql> SELECT User, Host, Password    -> FROM mysql.user;

When you press ENTER after the Password part, you get a new line and so can keep typing. The > symbol indicates that is still in the middle of a statement. You can type a semicolon by itself to end a command if you forget to type it in the same line as the command.

User hosts

Following is example output for the preceding query:

  SELECT User, Host, Password from mysql.user;+------------------+-----------+---------------------------- ---------------+| User | Host | Password |+------------------+-----------+-------------------------------------------+| Root | localhost | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 | | Root | Demohost | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 | | Root | 127.0.0.1 |                  *2470c0c06dee42fd1618bb99005adca2ec9d1e19 | | |                                           %         | |+------------------+-----------+-------------------------------------------+

Users is associated with a host and specifically the host to which they connect. The "root" user in this example are defined for localhost, for the IP address of localhost, and the hostname of the server ("Demohost" in this example). You usually need to set a user for only one host, and the one from which you typically connect.

If you ' re running your application on the same machine as the MySQL server the host it connects to by default is "Localhos T ". Any new users so you create must has "localhost" in their "host" field.

If your application connects remotely, the "host" entry that MySQL looks for is the IP address or DNS hostname of the Remo Te Machine (the one from which, the client is coming).

A special value for % the host are, as can see in the preceding output for the blank, or anonymous, user (see the F Ollowing section). The % symbol is a wildcard this applies to any host value. You usually don ' t want the use of that because it's more secure to the limit access specifically to trusted hosts.

Anonymous Users

In the example output, one entry have a host value but no username or password. That's an "anonymous user". When a-client connects with no username specified, it's trying to connect as a anonymous user.

You usually don ' t want any anonymous users, but some MySQL installations include one by default. Should either delete the user (refer to the username with empty quotes, like ") or set a password for It. Both tasks is covered later in the this series of articles.

Create a Database

There is a difference between database server and an actual database, even though those terms are often used Interchangeab Ly. MySQL is a database server, meaning this it keeps track of databases and controls access to them. An actual database was where all the data goes is stored, and it's the database that applications be trying to access whe N They interact with MySQL.

Some applications create a database as part of the their setup process, but others require you to create a database and tell T He application about it. Fortunately, creating a database is simple.

To create a database, log in mysql to the shell and run the following command, replacing Demodb with the name of the Datab ASE. Want to create:

CREATE DATABASE demodb;

The database is created. You can verify it creation by running a and a query to the list all databases. The following example shows the query and example output:

SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || demodb             || mysql              |+--------------------+3 rows in set (0.00 sec)

ADD a database user

When applications connect to the database using the root user, they usually has more privileges than they need. You can create a new user this applications can use to connect to the new database. In the following example, a user named DemoUser is created.

To create a new user, run the following command in the mysql shell:

CREATE USER ‘demouser‘@‘localhost‘ IDENTIFIED BY ‘demopassword‘;

You can verify the user is created by running that "select" Query again:

SELECT User, Host, Password FROM mysql.user;+------------------+-----------+-------------------------------------------+| User | Host | Password                                                   |+------------------+-----------+-------------------------------------------+| root     | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19         || root     | demohost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19         || root     | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19         || demouser | localhost | *0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6         |+------------------+-----------+-------------------------------------------+

Grant Database user Permissions

Right after you create a new user, it has no privileges. The user can be used to log in to MySQL, but it can ' t is used to do any database changes. Give the user permissions for your new database by running the following commmand:

GRANT ALL PRIVILEGES ON demodb.* to [email protected];

Then, flush the privileges-to-make the change take effect.

FLUSH PRIVILEGES;

To verify the privileges were set, run the following command:

SHOW GRANTS FOR ‘demouser‘@‘localhost‘;

MySQL returns the commands needed to reproduce that user's permissions if you were to rebuild the server. The "USAGE on ." section basically means that the user gets no privileges on anything by default. That command was overridden by the second command, which are the grant you ran for the new database.

+-----------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                   |+-----------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘demouser‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6‘ || GRANT ALL PRIVILEGES ON `demodb`.* TO ‘demouser‘@‘localhost‘                                                    |+-----------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

Revoking privileges

Sometimes might need to revoke (remove) privileges form a user, for different reason. For example:you were granting ALL privileges to ' demouser ' @ ' localhost ', but by accident (can happen to the best of us a NY time!) Instead of granting them only on the DEMODB database, you granted them to all other databases too:

+-----------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                   |+-----------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘demouser‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6‘ || GRANT ALL PRIVILEGES ON *.* TO ‘demouser‘@‘localhost‘                                                           |+-----------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

After the realizing your mistake, you decided to does something to correct it. REVOKEthe easiest-on-statement, followed by statement to GRANT apply correct privileges.

REVOKE ALL ON *.* FROM [email protected];GRANT ALL PRIVILEGES ON demodb.* to [email protected];SHOW GRANTS FOR ‘demouser‘@‘localhost‘;+-----------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                   |+-----------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘demouser‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6‘ || GRANT ALL PRIVILEGES ON *.* TO ‘demouser‘@‘localhost‘                                                           |+-----------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

Now your user have correct permission, and therefore your database server is slightly more secure (granting privileges like Is ALL on *.* deemed as a very bad practice). should also read official MySQL documentation regarding possible privilege choices, to grant only those privileges Tru Ly needed, rather than using ALL .

Summary

If you ' re just creating a database and a user, you're done. The concepts covered here should give your a solid grounding from which to learn more.

The next article covers some basic security and stability checks by looking at the MySQL server ' s configuration files and A few key tools.

Connection:

Http://www.rackspace.com/knowledge_center/article/installing-mysql-server-on-centos

Installing MySQL Server on CentOS