Preparing MySQL For Applications
- Install and start MySQL.
-
Create a MySQL "root" user.
-
Create a regular MySQL user that the application will use to access the database.
-
Perform some basic tests of your database structure.
Install and start MySQL.
With Fedora / Redhat the packages to install would be mysql-server and mysql and with Debian / Ubuntu the packages are mysql-server and mysql-client.
Starting MySQL
# chkconfig mysqld on
# service mysqld start
# service mysqld stop
# service mysqld restart
To determine whether mysqld is running you can issue either of these two commands.
# service mysqld status
# pgrep mysql
For Ubuntu / Debian
With these flavors of Linux the commands are different. Try installing the sysv-rc-conf and sysvinit-utils DEB packages as they provide commands that simplify the process.
You can use the sysv-rc-conf command to get mysqld configured to start at boot:
#sysv-rc-conf mysql on
#service mysql start
#service mysql stop
#service mysql restart
The /etc/my.cnf File
You can define most of MySQL’s configuration parameters in the my.cnf file which may be located in either the /etc or /etc/mysql directory depending on your version of Linux.
Your Linux distribution may only allow MySQL server to listen on the 127.0.0.1 localhost address. This is sufficient if the database and the application that uses it is on the same server. To allow access from remote clients you will have to let it listen to the IP address of your network interface. The bind-address directive in the [mysqld] section of the my.cnf file governs this action. In this case MySQL is listening on the address 192.168.1.100.
# File: my.cnf
#[mysqld]
bind-address = 192.168.1.100"
The Location of MySQL Databases
According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.
Create a MySQL "root" user
The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password.
Only two steps are necessary for a brand new MySQL installation.
-
Make sure MySQL is started.
-
Use the mysqladmin command to set the MySQL root password. The syntax is as follows:
# mysqladmin -u root password new-password
Accessing The MySQL Command Line
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.58
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Creating and Deleting MySQL Databases
mysql> create database salesdata;
Query OK, 1 row affected (0.00 sec)
mysql>
delete the database
mysql> drop database salesdata;
Query OK, 0 rows affected (0.00 sec)
mysql>
Create a regular MySQL user that the application will use to access the database
Granting Privileges to Users
On many occasions you will not only have to create a database, but also have to create a MySQL username and password with privileges to access the database.
MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command, which has the syntax.
sql> grant all privileges on database.* to username@"servername" identified by 'password';
So you can create a user named mysqluser with a password to have full access to the database named salesdata on the local server (localhost) with the grant command.If the database application's client resides on another server, then you'll want to replace the localhost address with the actual IP address of that client.
sql> grant all privileges on salesdata.* to mysqluser@"localhost" identified by 'pinksl1p';
The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.
sql> flush privileges;
Running MySQL Scripts To Create Data Tables
# mysql -u root -p salesdata < create_mysql.script
Enter password:
#
Perform some basic tests of your database structure
Viewing Your New MySQL Databases
mysql> show databases;
+-----------+
| Database |
+-----------+
| salesdata |
+-----------+
1 row in set (0.00 sec)
mysql>
Listing The Data Tables In Your MySQL Database
mysql> use salesdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_salesdata |
+---------------------+
| test |
+---------------------+
1 row in set (0.00 sec)
mysql>
Viewing Your MySQL Database's Table Structure
mysql> describe test;
+---------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+------------+----------------+
| num | int(11) | | PRI | NULL | auto_increment |
| date_modified | date | | MUL | 0000-00-00 | |
| name | varchar(50) | | MUL | | |
| description | varchar(75) | YES | | NULL | |
+---------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)
mysql>
