Where Is Mysql Database Stored Linux
Linux Home Server HOWTO
Chapter 17 - MySQL Server
| Versions: | - mysql-server 5.0.21 - mysql-administrator 1.1.6 - phpMyAdmin-2.8.1 |
Initial Configuration
Setting Root Password
phpMyAdmin Web Administrator
Databases are a great way to store information, they can store your personal contact list, your financial records, your household inventory, or even a listing of your favourite websites. Databases are able to store large amounts of useful information in a logical structure that allows for quick retrieval in any user defined format. Community libraries are renowned for their use of large database systems to store the mass amounts of usable information that they collect and share.
Databases also play a major role in todays web applications by storing complete inventories of products and services and making these accessible through a programmed web frontend. A database has the ability to provide dynamic content to a web shop by providing the content to web pages as an online shopper browses through the catalogue; a simple adjustment in the database can provide a 10% discount across the whole product line, instantly. Many Linux distributions implement PHP, MySQL, and Apache as a perfect combination for full featured dynamic web content.
This chapter will provide guidance to establish your MySQL (http://www.mysql.com) database server and also the steps necessary to configure a web based administration tool (phpMyAdmin) for remote management. This chapter will not be an introduction to the Structured Query Language (SQL), there are already many tutorials available on the Internet.
Initial Configuration
Configuring the server is not too difficult but one point to remember is there are three levels of configuration files, each having precedence over the following file. The main configuration file (/etc/my.cnf) contains all of the global options for the server, what ever is defined here will dominate the subordinate files. Likewise the server-specific configuration will dominate any user specified options, if the files exist.The following table details the configuration files.
| Order: | File Location: | Description: |
| 1. | /etc/my.cnf | For setting global options |
| 2. | /var/lib/mysql/my.cnf | For setting server-specific options |
| 3. | ~/my.cnf | For setting user-specific options (if applicable) |
Depending on your Linux distribution your MySQL server may already be configured with a minimum configuration file and is ready to run, otherwise you may need to create a configuration file before the server can be activated. This is not a big problem as some sample configuration files have been provided that are already tuned to a particular purpose depending on the role of the server.
| Sample File: | Used For: |
| my-huge.cnf | Large site, fully dedicated mysql server, 1-2GB RAM |
| my-large.cnf | Large site, mostly dedicated mysql server, ~512MB RAM |
| my-medium.cnf | Medium site, shared server running mysql, > 64MB RAM |
| my-small.cnf | Small site, shared server running mysql, < 64MB RAM |
| File Locations: /usr/share/doc/mysql-server-?.??.?? | |
You need to check if you have the global options file (/etc/my.cnf) installed, if you don't have the file you should copy one of the sample files that best suits your requirements into that position, if you do have the global file then you should copy one of the sample files into the server-specific options location. Remember that we should backup the main configuration first if it exists.
[bash]# cp /etc/my.cnf /etc/my.cnf.original
[bash]# cp /usr/share/doc/mysql-server- ?.??.?? /my-small.cnf /var/lib/mysql/my.cnf
 | Copy the sample file that best suits your system and requirements. A configuration that is over tuned for your system may waste resources unnecessarily. |
Now that the configuration files are in place, the runlevels should be configured and checked. You should ensure that the runlevels match those of Apache if you are using the MySQL server as a backend to a dynamic website.
[bash]# chkconfig --level 345 mysqld on
[bash]# chkconfig --list mysqld
The service can now be started and checked for initialisation errors.
[bash]# /etc/init.d/mysqld restart
[bash]# tail /var/log/mysqld.log
If this is the first time that you have started your server, it will automatically create some standard databases in the /var/lib/mysql directory. If these database directories do not exist or you received an error, then they can be manually created by typing "mysql_install_db" at the command prompt.
| [bash]# ls -l /var/lib/mysql |
| drwx------ 2 mysql mysql 4096 Dec 22 04:41 mysql srwxrwxrwx 1 mysql mysql 0 Dec 22 04:41 mysql.sock drwx------ 2 mysql mysql 4096 Dec 22 04:41 test |
You can check the integrity of your new databases with the following command.
| [bash]# mysqlcheck -A |
| mysql.columns_priv OK mysql.db OK . . *** Output Trimmed *** . mysql.time_zone_transition_type OK mysql.user OK |
Setting Root Password
Your sql server should now be configured and running, however the initial setup creates a root user with a blank password which should be changed to protect your databases.The system log also confirms that the server is running, and warns the user to change their root password.
| [bash]#/etc/init.d/mysqld restart |
| PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! galaxy mysql_install_db: This is done with: galaxy mysql_install_db: /usr/bin/mysqladmin -u root password 'new-password' galaxy mysql_install_db: /usr/bin/mysqladmin -u root -h galaxy.example.com password 'new-password' galaxy mysql_install_db: See the manual for more instructions. |
The sql root account should not be confused with the Linux system superuser account, it is only for access to the sql server so the passwords can be different if you need them to be. You should now select a suitable password and use the following commands to secure the root account. Remember to substitute "NEW_ROOT_PASSWORD" for your chosen password.
| [bash]# mysql -u root |
SET PASSWORD FOR 'root'@'localhost' = PASSWORD(' NEW_ROOT_PASSWORD <-- change MySQL root password mysql> SET PASSWORD FOR 'root'@'galaxy.example.com' NEW_ROOT_PASSWORD <-- change MySQL root password mysql> quit |
Now that the passwords have been configured for the root account, you will need to add the following command options to all your commands when using the mysql client, the client will then ask you for your password before granting access. See "man mysql" for more details.
| Now Need: "-u root -p" |
| [bash]# mysqladmin -u root -p variables Enter password: |
To view a list of all the user accounts within the "mysql" database table, use the following command, note that the passwords are stored as a message digest hash (secure algorithm) to prevent someone from simply "dumping" them out with this command.
| [bash]#echo "select USER,HOST,PASSWORD from user" | mysql -u root -p -D mysql Enter password: |
| USER HOST PASSWORD root localhost 31218f0c48d3e60f root galaxy.example.com 31218f0c48d3e60f galaxy.example.com localhost pma localhost 3cf4e95402cfe1cd <-- added duing phpMyAdmin config |
Graphical Administrator
The extras yum respository has a "mysql-administrator" RPM that can be installed to assist in administering your new MySQL server through a graphical user interface. Type the following command at the prompt to install the administrator (this requires X Windows and a desktop manager to be installed).| [bash]# yum install mysql-admin* mysql-gui* |
| [bash]#mysql-administrator &<-- To execute GUI administrator |
phpMyAdmin Web Administrator
One of the easiest ways to interface and administer your SQL database is through a web application thats running on the local SQL server. phpMyAdmin is an opensource PHP based web application designed specifically to allow remote management of MySQL using nothing more that a standard web browser. The phpMyAdmin application provides an easy to use graphic interface for users that are not too familiar with SQL commands by providing easy to follow instructions. The package can be downloaded from the phpMyAdmin site (http://www.phpmyadmin.net) and quickly installed on your local web/SQL server.Firstly we need to download the latest archive of phpMyAdmin and save it somewhere on the local server, the following example uses the gzip format. Use the following commands to extract the archive into the "/var/www" directory, remember to replace ?.?.? with the version number you have downloaded.
[bash]# tar -xzvf phpMyAdmin- ?.?.? .tar.gz -C /var/www/
[bash]# chown -R root.root /var/www/phpMyAdmin- ?.?.?
 | Replace phpMyAdmin-?.?.? with the version number that you are installing. |
The application has now been extracted and needs to be configured with the settings for the local MySQL server. phpMyAdmin will also interface into one of its own databases using an account called "pma" which we will create a little further on, however you will need to place a password into the configuration file for the pma account; remember it for later use.
When you configured your Apache web server you created an SSL certificate and used the rewrite module to force SSL connections. It is recommended that you also force SSL on your phpMyAdmin application so that any logon details and database queries are executed confidentially. Ensure the 'PmaAbsoluteUri' directive uses the HTTPS protocol if you intend using SSL, otherwise substitute it for the standard URL path.
| [bash]# vi /var/www/phpMyAdmin- ?.?.? /config.inc.php |
| <?php $cfg['PmaAbsoluteUri'] = 'https://www.example.com/mysql'; /* Start of servers configuration */ /* Server localhost (config:root) [1] */ /* End of servers configuration */ ?> |
| | Replace "PMA_PASSWORD" and "NEW_ROOT_PASSWORD" with the appropriate passwords for those accounts. |
The local MySQL server now needs to be configured with the tables and access rights so phpMyAdmin can connect to it. These steps can vary depending upon the version of MySQL you are running, and may result in error if the wrong commands are issued. Use the following mysqladmin command below to confirm the version of MySQL you are using before continuing to configure the server.
| [bash]# mysqladmin -u root -p version |
| Server version 5.0.21 |
You can now create the tables needed for phpMyAdmin depending on the server version you are running. You will need to enter your 'root' password when prompted (substituting ?.?.? where needed).
WARNING>>>> ONLY FOR SQL VERSIONS ABOVE 4.1.2
[bash]# mysql -u root -p < /var/www/phpMyAdmin- ?.?.? /scripts/create_tables_mysql_4_1_2+.sql
If the above command was successful you will now see the new database structure located in the servers data directory.
| [bash]# ls -l /var/lib/mysql/ |
| drwx------ 2 mysql mysql 4096 Dec 22 02:50 mysql srwxrwxrwx 1 mysql mysql 0 Dec 22 06:02 mysql.sock drwx------ 2 mysql mysql 4096 Dec 22 06:17 phpmyadmin drwx------ 2 mysql mysql 4096 Dec 22 02:50 test |
We now need to use the mysql command line client to configure the correct access controls for the pma control account and database. The following commands can be cut and pasted into the mysql client when the "mysql>" prompt is available. Remember to replace "PMA_PASSWORD" with the correct password.
| WARNING>>>> ONLY FOR SQL VERSIONS ABOVE 4.1.2 [bash]# mysql -u root -p mysql> _ | |
| GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'PMA_PASSWORD';<-- change PMA password GRANT SELECT ( Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv ) ON mysql.user TO 'pma'@'localhost'; GRANT SELECT ON mysql.db TO 'pma'@'localhost'; GRANT SELECT ON mysql.host TO 'pma'@'localhost'; GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO 'pma'@'localhost'; quit |
| | Replace "PMA_PASSWORD" with the appropriate password for the pma control account. |
The tarball archive for the phpMyAdmin application was originally extracted into the "/var/www/phpMyAdmin-?.?.?", while the Apache web server has its "DocumentRoot" directive set to "/var/www/html" which means the phpMyAdmin application is located outside of the "DocumentRoot" and the contents are not accessible directly by the web server.
We can create a configuration file for the phpMyAdmin application so Apache can access the resources that are required. The configuration below is using the AuthType directive ensuring that the access is restricted to only those users that have a valid username and password.
| [bash]# vi /etc/httpd/conf.d/phpMyAdmin.conf |
| Alias /mysql "/var/www/phpMyAdmin-?.?.?" <Location "/mysql"> |
If you have created SSL certificates for your Apache web server, then you should force the phpMyAdmin application into SSL mode to keep it secure. This configuration uses the rewrite module configuration we created in Chapter 13.
| [bash]# vi /etc/httpd/conf.d/mod-rewrite.conf |
| RewriteRule ^/mysql/(.*) https://%{SERVER_NAME}/mysql/$1 [R,L] |
The Apache web server needs to be restarted before the settings will be used.
[bash]# /etc/init.d/httpd restart
If everything has gone well you should now be able to access the phpMyAdmin application on the local server at: https://localhost/mysql.
Where Is Mysql Database Stored Linux
Source: https://www.brennan.id.au/17-MySQL_Server.html